Разработка сценария и работа с диспетчером сценариев

В Excel предусмотрена возможность проведения услов­ного анализа данных. Это можно делать с помощью сценариев. Сценарий представляет собой некоторое множество исходных значений, на основе которых программа Диспетчер сценариев создает отчет. Отчет содержит как исходные, так и полученные после подстановки в таблицу и произ­ведения необходимых вычислений итоговые значения, что позволяет прослеживать зависимости между данными в таблице. Сценарии можно сохранять, редактировать и удалять в случае необходимости.

Ранее была создана таблица, с помо­щью которой решена проблема оптимальной загрузки оборудования предприятия. Воспользуемся этой таблицей еще раз, чтобы проследить, как влияет увеличение значений в столбце Маржа (ячейки Е4 — E9) на объем продаж. В качестве сценариев будем использовать несколько различных наборов значений.

Поскольку Диспетчер сценариев заменяет исходные значе­ния в рабочем листе, целесообразно предварительно сделать копию листа для сохранения исходных значений.

Запустим Диспетчер сценариев с помощью команды Сценарии из меню Сервис. В результате на экране будет открыто диалоговое окно, в котором можно создавать и обрабатывать сценарии. Если необходимо создать сценарий, с исходными значениями, нажмите кнопку Добавить и укажите имя сценария, например Исходный.

Поскольку в нашем примере следует изменить значения маржи для различных товаров, в поле ввода Изменяемые ячейки необходимо указать адреса ячеек Е4 — Е9. Для этого можно воспользоваться уже известным способом: сначала перевести курсор мыши в поле ввода, а затем выделить нужные ячейки в рабочем листе. В поле Примечание диалогового окна Добавление сценария Вы можете ввести текст комментария к сценарию или отредактировать уже существующий. Программа по умолчанию пред­лагает в этом поле указать дату создания сценария и имя пользователя.

Рис. 33. Диалоговое окно Добавление сценария

Если для рабочего листа установлена защита с помощью пароля, то посредством активизации опции Запретить изменения в области Защита диалогового окна Добавление сценария пользователь может запретить редактирование сценария для других пользователей. При установке в том же поле опции Скрыть имя сценария не будет представлено в окне Диспетчера сценариев.

После нажатия кнопки ОК в диалоговом окне Добавление сценария исходные значения будут представлены в открывшемся диалоговом окне Значения ячеек сценария. Поскольку исходные значения должны быть сохранены в качестве сценария, то в диалоговом окне Значения ячеек сценария следует нажать кнопку OK — тем самым будет заново создан сценарий. Имя сценария теперь будет представлено в диалоговом окне Диспетчер сценариев. Создадим еще один сценарий. Откроем диалоговое окно создания сценария нажмем кнопку Добавить и зададим имя сценария, например Увеличение 25%.

В новом сценарии мы увеличим значение маржи для каждого товара на 25%. Изменяемые ячейки останутся прежними (Е4 — Е9). Нажатием кнопки ОК в этом диалоговом окне можно перейти в диалоговое окно задания значений сценария. Наряду с числовыми значениями в поля можно вводить формулы и текстовые значения. В нашем случае это значительно облегчит решение задачи. Умножим значения в полях ввода на 1,25, указывая в полях ввода формулу, например =0,05*1,25. После нажатия ОК на экране появится сообщение о том, что программа произвела указанные вычисления.

Рис. 34. Значения для нового сценария после преобразования

Имя нового сценария будет представлено в диалоговом окне Диспет­чера сценариев.

Анализ данных с помощью сценария не представляет трудностей. Для этого следует выделить имя желаемого сценария в списке в окне Диспет­чера сценариев и нажать кнопку Вывести. Входные значения сценария будут подставлены в изменяемые ячейки. Поскольку в нашем примере изменяемым ячейкам присвоен процентный стиль, значения в них пред­ставлены в виде процентов. Если Вы хотите изменить представление значе­ний, выберите другой числовой формат в диалоговом окне Формат ячеек.

Выделите имя сценария Исходный и нажмите кнопку Вывести. В таблице будут представлены исходные значения. В окне Диспетчера сценариев можно выбирать различные сценарии. Для того чтобы закрыть окно Диспетчера сценариев, необходимо активизировать кнопку Закрыть. Сценарии, созданные программой Поиск решения, всегда доступны в окне Диспетчера сценариев.