5 Создание сценариев
Создать сценарии развития ситуации по продаже продукции предприятия, представить отчет по структуре сценария:
Варианты ситуаций |
1 |
2 |
3 |
Объем реализации |
5,68 |
6,58 |
12,59 |
Цена продукции |
59 |
67 |
60 |
Выручка от реализации |
|
|
|
Для создания такого сценария сначала установим на панель инструментов раскрывающийся список сценариев. Для этого выполним команду Сервис - Настройка, в появившемся окне перейдем на вкладку Команды и выберем в левом окне элемент Сервис. В правом окне найдем элемент Сценарий и перетащим его мышью на панель инструментов.
Для создания сценария подготовим нужные данные следующим способом: в ячейку В1 введем первый объем реализации, в ячейку В2 – цену продукции, а в ячейке В6 подсчитаем выручку по формуле =В2*В3. Выделим диапазон клеток В2:В3 и воспользуемся Диспетчером сценариев с помощью команды Сервис - Сценарии:
С помощью кнопки Добавить в диалоговом окне «Добавление сценария» будем вводить по очереди имена сценариев (сценарий1, сценарий2, сценарий3) и нажимать ОК (рисунок 11).
Рисунок 11
Каждый раз после ввода имени сценария открывается окно «Значения ячеек» сценария, в котором будем изменять объем реализации и цену продукции (рисунок 12).
Рисунок 12
В результате у нас создалось 3 сценария для каждой ситуации. Переходить между ними можно с помощью созданной нами кнопки на панели инструментов (рисунок 13).
Рисунок 13
Для создания отчета по структуре сценария выполним команду СервисСценарии и нажмем на кнопку Отчет и выберем отчет в виде структуры. Добавится лист «Структура сценария» с отчетом.
Рисунок 14
6 Консолидация данных
Создайте два листа книги, присвоив им имена, «Расположение» и «Категория». Создайте на листе «Расположение», таблицу расчета заработной платы (за январь), которая приведена на рис. 15 в ячейках A3:D8. Для расчета столбца «Подоходный налог» используйте формулу =B4*0,12. Сумму к выдаче подсчитайте по формуле =B4-C4. Итоговые значения столбцов рассчитайте с помощью Автосуммирования.
Скопируйте, созданную таблицу, в другую область того же самого листа (например, в область F3:I8) и измените в ней значения заработной платы. Эта таблица будет показывать заработную плату за февраль. В ячейку А10 введите текст «Консолидированная таблица заработной платы за январь и февраль».
Рисунок 15 Лист «Расположение»
Получите консолидированную таблицу, как показано на рисунке 16, выполняя следующие операции:
установите курсор в первую ячейку области, где будет располагаться консолидированная таблица, например в ячейку А12;
выполните команду Данные-Консолидация;
в диалоговом окне Консолидация выберите из списка функцию Сумма, включите флажки Подписи верхней строки и Значения левого столбца;
установите курсор в поле Ссылка, и введите в него диапазон ячеек A3:D8 (это можно сделать вручную или с помощью мыши, выделив нужный блок ячеек);
нажмите кнопку <Добавить>, при этом в окне Список диапазонов появится ссылка на выделенный диапазон;
вновь установите курсор в поле Ссылка, и введите в него диапазон ячеек F3:I8 (заработная плата за февраль);
нажмите кнопку <Добавить>, в окне Список диапазонов появится ссылка на выделенный диапазон;
нажмите кнопку <ОК> (в ячейках А12:D17 появится консолидированная по расположению таблица), проанализируйте полученный результат и сравните его с рис. 16.
Рисунок 16 Пример консолидации данных по расположению
Получите консолидированную таблицу, как показано на рисунке 17, выполняя следующие операции:
скопируйте обе таблицы (заработная плата за январь и за февраль) с листа Расположение на лист Категории и измените вторую таблицу в соответствии с рисунком 17. Для этого вставьте новый столбец «Премия» и строку с фамилией «Дятлов» и заполните их соответствующими формулами;
установите курсор в первую ячейку области, где будет располагаться консолидированная таблица, например в ячейку А13;
выполните команду Данные-Консолидации;
в диалоговом окне Консолидация выберите из списка функцию Сумма и установите флажки Подписи верхней строки и Значения левого столбца;
установите курсор в окне Ссылка, перейдите на лист с исходными таблицами и выделите блок ячеек А3:D8 (заработная плата за январь);
нажмите кнопку <Добавить>, в окне Список диапазонов появится ссылка на выделенный диапазон;
установите курсор в окне Ссылка, перейдите на лист с исходными таблицами и выделите блок ячеек F3:J9 (заработная плата за февраль);
нажмите кнопку <Добавить>, в окне Список диапазонов появится ссылка на выделенный диапазон;
нажмите кнопку <ОК> и сравните полученные результаты (см. рис. 17)
Рисунок 17 Пример консолидации данных по категориям