На этом шаге мы рассмотрим диспетчер сценариев.
Анализ данных с помощью таблиц подстановки является весьма эффективным. Однако он имеет несколько недостатков:
- Одновременно можно анализировать расчетные данные только при изменении одного или двух исходных параметров.
- Процесс создания таблицы подстановки интуитивно не всегда понятен.
- При использовании таблицы подстановки с двумя входами можно проанализировать результаты расчетов, проведенных только по одной формуле. Для других формул нужно создать дополнительные таблицы подстановки.
- Очень часто бывает необходимо просмотреть результаты расчетов, проведенных только для нескольких определенных комбинаций входных параметров, а не всю таблицу подстановки.
С помощью средства Диспетчер сценариев можно достаточно просто автоматизировать процесс выполнения анализа "что-если" для различных моделей. С его помощью Вы можете создать несколько наборов данных вводимых значений (в терминологии средства Диспетчер сценариев они называются изменяемыми ячейками) для любого количества переменных и присвоить имя каждому набору. Затем по имени можно выбрать определенный набор данных, и Excel покажет результаты анализа этих данных на рабочем листе. Кроме того, можно создать итоговый отчет по сценариям, в котором будет показан результат подстановки различных комбинаций входных параметров. Итоговый отчет может быть представлен в виде обычного структурированного списка или сводной таблицы.
В качестве примера рассмотрим процесс прогнозирования объема продаж товаров на текущий год. Объем продаж может зависеть от многих факторов, поэтому создается три сценария - по одному для лучшего, худшего и наиболее вероятного случаев. После того как сценариям будет присвоено имя, Вы сможете легко переключаться между ними, выбирая соответствующее название из списка. При этом Excel будет автоматически подставлять нужные исходные данные в рабочий лист и пересчитывать формулы.
Определение сценариев
Чтобы Вы смогли получить общее представление о средстве Диспетчер сценариев, начнем с простого примера - производственной модели (рис. 1).
Рис. 1. Пример производственной модели
В этом примере определены три сценария, которые приведены в таблице 1. В сценарии для лучшего случая тариф почасовой оплаты и стоимость материалов наименьшие. в сценарии для худшего случая эти значения самые большие. В третьем сценарии, или в наиболее вероятном случае, используются промежуточные значения этих параметров (они определяются менеджером с учетом ряда факторов). Менеджеры должны быть готовы к худшему случаю, однако им также необходимо знать, что будет, если сработает сценарий лучшего случая.
| Сценарий | Почасовая оплата | Стоимость единицы материала |
|---|---|---|
| В лучшем случае | 30 | 57 |
| В худшем случае | 38 | 62 |
| Наиболее вероятно | 34 | 59 |
Доступ к средству Диспетчер сценариев можно получить с помощью команды Сервис | Сценарии. При выборе этой команды будет вызвано диалоговое окно Диспетчер сценариев, показанное на рисунке 2.
Рис. 2. Диалоговое окно Диспетчер сценариев
Когда Вы впервые вызываете это диалоговое окно, в нем имеется сообщение, в котором сказано, что ни один сценарий не определен, и это не удивительно, ведь Вы только начали. Как только будут добавлены новые сценарии, их названия появятся в диалоговом окне. Чтобы добавить сценарий, в диалоговом окне Диспетчер сценариев щелкните на кнопке Добавить. Появится диалоговое окно Добавление сценария, которое показано на рисунке 3. Это диалоговое окно разделено на четыре части:
Рис. 3. Диалоговое окно Добавление сценария
- Название сценария. В этом поле можно указать любое имя сценария, но желательно, чтобы оно что-то обозначало.
- Изменяемые ячейки. Ячейки, в которых находятся исходные данные для сценария. В это поле можно ввести абсолютный адрес ячейки или ее имя. Разрешается выбирать несколько ячеек, причем все они не обязательно должны быть смежными. В каждом сценарии, которому присвоено имя, можно использовать одни и те же наборы изменяемых ячеек или разные изменяемые ячейки. Количество изменяемых ячеек для одного сценария ограничено числом 32.
- Примечание. По умолчанию в это поле Excel помещает информацию о том, кто создал сценарий, а также дату его создания. Однако Вы можете отредактировать этот текст, добавить к нему новый текст или вовсе удалить его.
- Защита. Две опции, которые позволяют защитить сценарий от изменений и скрыть его, можно активизировать только в том случае, если рабочий лист защищен и в диалоговом окне Защитить лист активизирована опция Сценарии. Защита сценария предотвращает модификацию его кем-либо другим, а скрытый сценарий вообще не появляется в диалоговом окне Диспетчер сценариев.
После того как вы заполнили диалоговое окно Добавление сценария, щелкните на кнопке OK. Появится диалоговое окно Значение ячеек сценария, которое показано на рисунке 4. В этом диалоговом окне перечислены поля всех изменяемых ячеек, которые были определены в предыдущем диалоговом окне. Введите значения для каждой ячейки сценария. Если щелкните на кнопке OK, то Вы вернетесь к диалоговому окну Диспетчер сценария. В нем теперь будет находиться имя созданного Вами сценария. Если необходимо создать еще сценарии, щелкните на кнопке Добавить и повторите описанную выше последовательность действий.
Рис. 4. Диалоговое окно Значение ячеек сценария
Примечание. Для удобства использования сценариев необходимо поместить на любую панель инструмент Сценарий:
- Выполнить команду Сервис | Настройка.
- В диалоговом окне Настройка выбрать вкладку Команды.
- Выбрать категорию Сервис.
- Выделить инструмент Сценарий и перетащить его на любую панель инструментов.
- Щелкнуть на кнопке Закрыть.
На следующем шаге мы рассмотрим отображение, изменение и объединение сценариев.
