3. Анализ “что-если” с помощью диспетчера сценариев
Команды Подбор параметра и Поиск решения очень полезны, однако после решения нескольких задач легко забыть, какими же были исходные значения. Еще важнее то обстоятельство, что при работе с этими командами не существует удобного способа сравнения результатов - при каждом изменении данных предыдущее решение пропадает. Чтобы устранить эти ограничения, разработчики Excel создали Диспетчер сценариев, помогающий работать с несколькими моделями “что-если”, Командой Сценарии из меню Сервис можно создавать новые и просматривать существующие сценарии для решения задач, работать с общими командами управления и отображать консолидированные отчеты.
Создание сценария
Сценарием называется именованная модель “что-если”, в которую входят переменные ячейки, связанные одной или несколькими формулами. Перед созданием сценария необходимо спроектировать лист так, чтобы на нем была хотя бы одна формула, зависящая от ячеек, которые могут принимать различные значения. Например, может возникнуть потребность в сравнении лучшего и худшего сценариев для недельных продаж кофе в кафетерии.
Перед созданием сценария необходимо создать рабочий лист с формулами, включающими переменные ячейки.
Создание сценария происходят следующим образом:
1. Выполните команду Диспетчер сценариев из меню Данные (раздел Работа с данными). Открывается диалоговое окно Диспетчер сценариев.
2. Щелкните нa кнопке Добавить, чтобы создать первый сценарий. Открывается диалоговое окно Добавление сценария.
3. Введите Лучший вариант (или любое подходящее имя) в поле Название сценария и нажмите клавишу Tab.
4. В поле Изменяемые ячейки укажите те переменные ячейки, которые изменяются в вашем сценарии. Это можно сделать несколькими способами - ввести ссылки с клавиатуры, выделить диапазон на листе или, удерживая нажатой клавишу Ctrl, щелкать мышью на отдельных ячейках (в последнем случае Excel автоматически разделяет ссылки на ячейки запятыми).
СОВЕТ. Пои задании переменных ячеек можно пользоваться именами. Это помогает распознавать их при создании сценария и при последующем вводе аргументов.
5. Добавьте сценарий к Диспетчеру сценариев, щелкнув па кнопке ОК. Открывается диалоговое окно Значения ячеек сценария, в котором вам предлагается ввести значения переменных модели. По умолчанию в полях находятся те значения, которые находились в ячейках при вызове Диспетчера сценариев.
6. Щелкните по кнопке Добавить, чтобы добавить второй сценарий и т.д.
7. После добавления последнего сценария нажмите ОК. Открывается диалоговое окно Диспетчер сценариев, в котором присутствуют сценарии Лучший вариант и Худший вариант. Теперь мы готовы к просмотру результатов.
8. Закройте диалоговое окно Диспетчер сценариев, щелкнув на кнопке Закрыть.
СОВЕТ. Щелчок на кнопке Сохранить сценарий в диалоговом окне Результаты поиска решения, открывающемся после решения задачи оптимизации, позволяет сохранить найденное решение в виде сценария. От вас потребуется только указать название, которое позднее будет использоваться для просмотра в Диспетчере сценариев.
Просмотр сценария
Excel сохраняет сценарии вместе с листом текущей книги, и просмотр их командой Сценарии из меню Сервис возможен только при открытии данного листа. Однако перед просмотром сценария бывает полезно сохранить книгу, чтобы позднее можно было при необходимости восстановить исходное состояние.
Просмотр сценария выполняется следующим образом:
1. Выполните команду Сценарии из меню Сервис. Открывается диалоговое Диспетчер сценариев.
2. Выберите из списка сценарий для просмотра.
3. Щелкните на кнопке Вывести. Excel заменяет содержимое ячеек листа значениями из сценария и отображает результаты на листе,
4. Выберите из списка другие сценарии и воспользуйтесь кнопкой Вывести для сравнения результатов моделей “что-если”. После завершения щелкните на кнопке Закрыть. Значения последнего активною сценария остаются в ячейках листа.
Создание отчетов по сценарию
Хотя можно сравнивать различные сценарии, переходя от одного к другому кнопкой Показать в диалоговом окне Диспетчер сценариев, иногда возникает необходимость в создании отчета с обобщенной информацией о различных сценариях листа. Эта задача легко выполняется с помощью кнопки Отчет и диалоговом окне Диспетчер сценариев. Созданный сводный отчет будет автоматически отформатирован и скопирован на новый лист текущей книги.
Создание отчета но сценарию происходит следующим образом:
1. Выполните команду Сценарии из меню Сервис. Открывается диалоговое окно Диспетчер сценариев.
2. Щелкните на кнопке Отчет. Открывается диалоговое окно Отчет по сценарию, в котором предлагается выбрать ячейки, входящие в отчет, а также его тип. Отчет типа Структура представляет собой форматированную таблицу, которая выводится на отдельном листе. Отчет Сводная таблица является специальной таблицей, которую можно настраивать за счет перестановки столбцов и строк.
3. Укажите ячейку результата, значение в которой необходимо включить в отчет, установите переключатель типа отчета (если не уверены — следует согласиться на установленный по умолчанию переключатель Структура) и щелкните на кнопке ОК.
Через несколько мгновений в текущей книге появляется новый ярлычок Структура сценария. Кнопки структуры на левом и верхнем поле отчета служат для свертывания и развертывания строк и столбцов отчета
СОВЕТ. При каждом щелчке на кнопке Отчет в диалоговом окне Диспетчер сценариев Excel создает в текущей книге новый лист с итоговой информацией о сценариях. Для удаления ненужного листа из книги следует щелкнуть на его ярлычке и выполнить команду Удалить лист из меню Правка.
Работа со сценариями
После того как сценарий будет создан кнопкой Добавить, он вовсе не обязан оставаться в вечной неприкосновенности. Для редактирования и удаления сценариев предназначаются кнопки Изменить и Удалить в диалоговом окне Диспетчер сценариев. Кнопка Изменить позволяет поменять название сценария, убрать существующие или добавить новые переменные ячейки или выбрать новую их группу. Если вы уверены, что этот сценарий вам больше никогда не потребуется, следует выделить его в диалоговом окне Диспетчер сценариев и щелкнуть на кнопке Удалить. Наконец, кнопка Объединить в диалоговом окне Диспетчер сценариев служит для копирования сценариев из других открытых книг на текущий лист - при щелчке на ней открывается диалоговое окно Объединение сценариев, в котором следует указать исходную книгу и лист.