Сценарный подход в microsoft excel

Microsoft Excel позволяет сохранять наборы исходных значений параметров моделей в виде сценариев. Параметры модели, сохраненные как сценарий, можно многократно редактировать, выполнять подстановку параметров определенного сценария, формировать итоговые отчеты по сценариям.

Команда меню СервисСценарии выводит диалоговое окно Сценарии для работы со сценариями. Для добавления новых сценариев нажимается кнопка Добавить. Вызывается новое диалоговое окно Добавление сценария (рис. 9), в котором указывается имя сценария, адреса ячеек, значения которых будут изменяться, вводится примечание к сценарию.

Рисунок 9

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

С помощью флажка Скрыть можно скрыть сценарии после установления защиты рабочего листа. При нажатии кнопки ОК для нового сценария выводится диалоговое окно Значение ячеек сценария для ввода значений ячеек – рис. 10.

Рисунок 10

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

Кнопка Объединить позволяет объединять сценарии различных листов одной и той же или различных книг. Кнопка Отчет выводит отчет по всем сценариям. При подготовке отчета указывается адреса ячеек текущего рабочего листа, содержащие формулы, зависящие от изменяемых ячеек сценариев.

1. Установить курсор на лист Анализ.

2. Команда меню СервисСценарии. С помощью кнопки Добавить подготовить сценарии согласно табл. 7.

 В качестве изменяемых ячеек следует выбрать ячейки, содержащие значения параметров (столбец В):

• Годовая норма.

• Число периодов (пересчет числа лет в месяцы делается вручную).

• Настоящая (начальная) стоимость.

Таблица 7

3. Вывести результаты подстановки значений параметров для каждого сценария – кнопка Вывести.

4. Построить отчет в виде структурной таблицы по всем сценариям – кнопка Отчет. Указать ячейку, содержащую формулу расчета функции ППЛАТ – В6. Результирующая таблица – табл. 8.

Таблица 8

 Автоматически создается новый лист Структура сценария, выводится структурная таблица. Можно изменить адреса ячеек на названия параметров.