ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 21.10.2020
Просмотров: 1564
Скачиваний: 3
Расширенные возможности Microsoft Excel 2007
51
Вкладки группы Работа со сводными диаграммами
При выделении диаграммы
автоматически
появляются вкладки,
связанные с командами
общего оформления диаграммы:
Конструктор
(Design)
–
изменить
тип диаграммы
, редактировать
источник
данных
, задать
стили
оформления, разместить элементы
диаграммы по стандартным схемам,
переместить
диаграмму на
другой лист.
Макет
(Layout)
–
вставить
в диаграмму
текстовые поля
, рисунки,
подписи
данных
на диаграмме, оформление области построения, настройка фона и
дополнительные построения для
проведения анализа
(линии
тренда, погрешности).
Формат
(Format)
– инструменты настройки выделенных частей диаграммы (
Формат
выделенного фрагмента
), а также
стили оформления
, заливка,
специальные эффекты обрамления, тени, размер и т.п.
Обновление сводных таблиц и сводных диаграмм
Сводная таблица
не обновляется автоматически
. Если данные в исходной
таблице изменились, то часто требуется обновить и сам отчет сводной таблицы.
Для обновления:
1.
Щелкнуть
в область отчета
сводной таблицы;
2.
На вкладке
Параметры
(Options)
в группе
Данные
(Data)
нажать кнопку
Обновить
(Refresh)
;
Для настройки
автоматического обновления при открытии файла
:
1.
Кликнуть правой
кнопкой
в область отчета
сводной таблицы – выбрать
Параметры сводной таблицы
(PivotTable Options)
или на вкладке
Параметры
в группе
Сводная таблица
нажать
Параметры
(Options)
;
2.
В диалоговом окне
Параметры сводной таблицы
(PivotTable Options)
перейти на вкладку
Данные
(Data)
, включить опцию
Обновить при
открытии файла
(Refresh data when opening the file).
Очистка макета сводной таблицы
,
очистка фильтров
Очистка макета
(области построения)
позволит быстро приступить к
формированию нового вида отчета сводной таблицы. Для этого: щелкнуть по
сводной таблице –
Параметры
(Options),
группа
Действия
(Actions)
выбрать
Очистить
–
Очистить все
(Clear – Clear All).
Очистка фильтрации
сводной таблицы позволит быстро отобразить все
данные в отчете. Для этого на вкладке
Параметры
(Options)
в группе
Действия
(Actions)
выбрать команду
Очистить
–
Очистить фильтры
(Clear – Clear Filters).
А
па
B7
ме
Центр
нализ
«
Сценар
Сцена
Диспе
значе
цена з
Все эт
меня
Задач
В дан
араметры,
7
,
B9
,
B11
Из
Ре
зав
Рассм
еняющими
1.
Пр
Эт
оф
2.
Вк
Ан
(Sc
Компьютерн
«
что
–
ес
ии
арий
–
аль
етчер сце
ений
(
сцен
закупки
)
и
п
то
позвол
ющихся п
ча
. Провед
получе
ной задач
в зависим
(
расходы
,
зменяемы
зультиру
висящие от
мотрим
р
ися ценами
рисвоить
то не явл
формленны
1)
выдел
A2:A11
2)
вкладк
Names)
3)
указат
кладка
Дан
нализ
«
что
cenario Man
ного Обучен
сли
»
(W
ьтернатив
енариев
п
нарии
)
в
за
переключа
ит создат
араметр
дем анали
ения Приб
е:
B2:B6
(
з
мости от
продажа
,
п
ые
ячейки
н
ующие
яч
т
изменяе
разные
и закупок,
С
озд
ячейкам
ляется обя
ый, понятн
лить
A2:B1
1
– текстов
ка
Формул
),
Создать
ть
располо
нные
(Dat
о
–
если
»
nager);
ния «Специал
What–If
вный
набо
позволяет
аранее
опр
аться
меж
ть
различн
ов
,
сохран
из таблиц
были при м
закупка
,
до
которых
прибыль
)
–
не должн
ейки обяз
емых
ячее
сценарии
разными
дание но
имена
(и
язательны
ный отчет
11
– число
вые описан
лы
(Formu
из выделе
ожение
за
ta),
группа
(What–If A
лист»
52
Analysi
ор данных
,
т
создать
ределенны
жду ними
.
ные
модел
енных в ви
цы Магази
меняющих
оставка
,
ра
меняются
–
результи
ы содерж
ательно
д
ек
.
и развит
расходами
вого сце
изменяемы
ым, но по
:
овые данн
ния, сдела
ulas),
групп
енного
фра
аголовков
а
Работа с
Analysis)
в
is)
,
хранящий
ь
,
сохрани
ых ячейка
ли расчет
иде разных
ин, рассмо
хся услови
асходы
и
за
я ответы п
ирующие
жать форм
должны
тия
дан
и, зарплата
енария
ым и резул
зволит в
ные с опи
аем их име
па
Опреде
агмента
(Cr
в
(в задаче –
с данными
выбрать
Д
йся как час
ить
разли
ах
(
наприм
тов в зав
х сценариев
трим разн
иях закупки
арплаты
)
–
по формул
ячейки.
мулы
,
–
эт
содержат
ного пр
ами.
льтирующ
итоге со
санием
(
B
нами ячее
еленные и
reate from S
–
в столбце
и
(Data Too
Диспетче
www.specia
сть листа
ичные
гру
ер
,
меняющ
исимости
в
.
ные вариа
и, доставк
–
изменяе
лам в яче
то конста
ть форм
едприятия
щим –
B2:B
оздать хор
B2:B11
– чи
ек
B2:B11
);
имена
(De
Selection)
;
е слева
),
O
ols),
из кн
ер сценар
alist.ru
а
.
уппы
щаяся
и от
анты
ки…
емые
ейках
нты
.
мулы
,
я с
B11
)
.
рошо
исла,
efined
OK
;
опки
риев
Расширенные возможности Microsoft Excel 2007
53
3.
В диалоговом окне
Диспетчер сценариев
(Scenario Manager)
нажать кнопку
Добавить
(Add), в появившемся окне
Добавление сценария
(Add Scenario)
:
1) ввести
Название сценария
(
например
,
Экономный
),
2) выбрать
Изменяемые ячейки
(
B3
–
доставка
,
B4
расходы
,
максимальное количество ячеек в
одном сценарии
=
32
)
3) ввести
Примечание
–
комментарий
(
например
,
экономия
идет за счет уменьшения доставки
...),
нажать
OK
;
4) в окне
Значения
ячеек
сценария
(Scenario
Values)
изменить
значения
на требуемые по
сценарию,
OK.
(
Добавить
позволит
сразу
перейти
к
созданию нового сценария)
.
Шаги
1)
–
4)
надо
повторить
для создания
нескольких сценариев
(
Создадим сценарии Экономный
,
Расходный
,
Стандартный
)
яч
да
Ди
сц
ра
об
Центр
Отчет
Отчет по
Отчет
Просм
чейки лист
нными
,
Ана
Любо
испетчер
Любо
ценариев
в
Отчет
азных кни
бъединить
Исп
сце
исх
Для
кот
Компьютерн
т создают
о сценари
т
составля
Вывод
мотрев отч
та надо о
ализ
«
что
–
е
ой из сце
сценарие
ой из сцен
выбрать л
т может с
гах.
Кнопк
ь их в одно
пользуйте
енарием д
ходных зна
я решения
тором буду
ного Обучен
т для
срав
T
иям
появит
яется по в
д подход
чет, можно
ткрыть ок
если
»)
, наж
енариев
м
в
, выбрать
нариев
мо
лишний сц
состоять и
ка
Объеди
ом отчете.
сценарии
анных
.
Эт
чений в ме
этой про
ут
сохран
ния «Специал
Создани
внения сце
Для
1.
(вкладка
Д
Tools), кно
2.
3.
(Scenario S
или
сводна
(
ячейки с ф
тся
на нов
всем созда
дящего с
о выбрать
кно
Диспе
жать кнопк
можно из
ь нужный
ожно уда
енарий, на
из сценари
инить
(Me
в книгах
,
с
то связан
еняемых яч
облемы ре
нены исход
лист»
54
ие отчет
енариев
с
я создания
Перейти
Данные
(Da
опка
Анали
Нажать кн
В появив
Summary)
ая таблиц
формулами
B
вом листе
анным сце
ценария
ь
оптимал
етчер сце
ку
Вывест
зменить
.
сценарий,
алить
. Для
ажать кноп
иев, наход
rge) окна
содержащ
о с тем
,
чейках
–
ри
комендуем
дные данн
та
целью выб
я отчета на
в окно
ata), группа
из
«
что
–
есл
нопку
Отч
шемся ок
выбрать
Т
ца
)
, указат
B7;
B9;
B11)
е
с именем
енариям
и
я в ячейк
льный
сцен
енариев
(
и
(Show)
.
Для этог
, нажать кн
я этого н
пку
Удали
дящихся н
Диспетче
щих резерв
что сцен
иск потер
м первым
ные без из
брать подх
адо:
Диспетче
а
Работа с
ли
»
);
чет
(Summa
не
Отчет
Тип отче
ть
Ячейк
)
,
нажать
O
Структура
и по
текущ
ки листа
нарий. Для
(
вкладка Да
го надо о
нопку
Изм
надо в окн
ить
(Delete)
на разных
ер сценари
вную копи
нарий
«
не
рять исхо
создават
зменений
.
www.specia
ходящий
.
ер сценар
данными
ary)
;
по сцена
ета
(
струк
ки резуль
OK
.
а сценария
щим данны
я его выво
анные
,
Рабо
открыть
менить
(Ed
не
Диспет
).
листах и
иев
позво
ию
изменяе
е запомина
одные дан
ть сценар
alist.ru
риев
(Data
арию
ктура
ьтата
я.
ым
.
ода в
ота с
окно
dit).
тчер
или в
оляет
емых
ает
»
нные
.
ий
,
в
Расширенные возможности Microsoft Excel 2007
55
Подбор параметра
Подбор параметра
выполняет
поиск значения
,
которое надо ввести в
формулу
для получения
известного результата
.
Задача
. Какой
вклад
нужно сделать в банк под
заданные
проценты (
13
%
годовых
), чтобы через год получить нужную сумму (
9
000
).
Решение
. Создадим таблицу, предположив, что вклад в банк (B3) = 5 000,
годовой процент (B4) = 13%. Введем формулу (B5), считающую накопление за год
по схеме
Вклад + Вклад * Процент
: =B3+B3*B4. Полученный ответ = 5
650. Ответ не
совпадает с желаемым результатом (9
000). Следовательно, предположение о вкладе
в банк ошибочно. Есть 2 пути решения, – выполнять подбор вручную или поручить
подбор программе, вызвав
Подбор параметра
:
1.
На вкладке
Данные
(Data), в
группе
Работа с данными
(Data Tools),
из
кнопки
Анализ
«
что
–
если
»
(What–If Analysis)
выбрать
Подбор параметра
(Goal Seek);
2.
В диалоговом окне
Подбор параметра
(Goal Seek):
–
Установить в ячейке
:
адрес целевой ячейки
(
B5
–
ячейка
с
формулой
!),
–
Значение
:
9
000
– число
нужного
ответа
по формуле,
–
в поле
Изменяя значение
ячейки
:
ввести
адрес
ячейки (параметр), от
которой зависит целевая ячейка (B3 – ячейка сумма вклада). Нажать
ОК
.
3.
В окне
Результат подбора параметра
(Goal Seek Status),
выбрать
способ
ввода найденного решения
в ячейку листа
(в нашей задаче это ячейка B3)
:
Кнопка
OK
–
сохранить
,
ввести
найденный параметр в ячейку листа;
Кнопка Отмена
–
не
вводить
найденный параметр в ячейку листа,
вернуть прежнее значение.