При решении целого ряда задач часто встречаются ситуации, когда отдельные экономические показатели носят вероятностный характер. При этом удобно при решении подобных задач выполнять несколько вариантов решений (в ряде случаев выполняют решения: для наиболее благоприятных условий – «оптимистический вариант», для самых неблагоприятных условий – «пессимистический вариант» и для неких средних условий – «наиболее вероятный вариант»). При этом желательно сохранить все варианты решений для последующего сравнения. Это можно выполнить с помощью Диспетчера сценариев.
Сценарий — это множество входных значений, называемых изменяемыми ячейками, которое можно сохранить под указанным именем, а затем применить к модели рабочего листа, чтобы проследить, как значения изменяемых ячеек влияют на другие значения модели. Для каждого сценария можно определить до 32 изменяемых ячеек.
Сценарии позволяют сохранять различные варианты изменений значений параметров ячеек электронной таблицы и использовать эти результаты для поддержки принятия решений оптимизации.
Диспетчер сценариев заменяет исходные значения в рабочем листе, поэтому для сохранения исходных значений рекомендуется перед созданием сценария предварительно сделать копию рабочего листа с исходной таблицей.
Для одной и той же таблицы можно создавать несколько различных сценариев. Сценарии можно:
· создавать (добавлять);
· изменять;
· удалять;
· объединять сценарии, созданные для одной и той же таблицы и находящиеся на различных рабочих листах.
Для всех сценариев рабочего листа могут быть составлены отчеты двух типов:
· в виде структуры сценария;
· в виде сводной таблицы.
Чтобы создать сценарий, следует:
1. В меню Сервис выбрать команду Сценарии.
2. Щелкнуть по кнопке Добавить. Откроется окно Добавление сценария.
3. В поле Название сценария ввести имя сценария.
4. В поле Изменяемые ячейки ввести ссылки на изменяемые ячейки. Несколько ссылок отделяются друг от друга точками с запятыми. Ссылки можно ввести с клавиатуры или выделить их на рабочем листе. Несмежные ячейки добавляются при нажатой клавише <Ctrl>.
5. Щелкнуть по кнопке ОК.
6. В открывшемся диалоговом окне Значения ячеек сценария ввести значения каждой изменяемой ячейки.
7. Для создания других сценариев щелкнуть по кнопке Добавить (откроется диалоговое окно Добавление сценария) и повторить пункты 3—6.
Для завершения работы с Диспетчером сценариев щелкнуть по кнопке ОК, а затем — по кнопке Закрыть.
Рекомендуется сохранить в качестве сценария первоначальные значения изменяемых ячеек, чтобы потом можно было быстро восстановить эти значения.
Любой из созданных ранее сценариев можно просматривать независимо один от другого. Для просмотра сценария нужно:
1. В меню Сервис выбрать команду Сценарии.
2. В поле Сценарии выделить имя сценария, который необходимо просмотреть.
3. Щелкнуть по кнопке Вывести.
Вместо пунктов 2 и 3 можно дважды щелкнуть по имени нужного сценария.
Созданные ранее сценарии можно изменять. Чтобы отредактировать сценарий, надо:
1. В меню Сервис выбрать команду Сценарии.
2. В поле Сценарии выделить имя сценария, который необходимо отредактировать.
3. Щелкнуть по кнопке Изменить.
4. Внести необходимые изменения: можно изменить имя сценария, изменяемые ячейки, значения изменяемых ячеек.
5. Для завершения работы с Диспетчером сценариев щелкнуть по кнопке ОК, а затем — по кнопке Закрыть.
Очень важно, что можно создать комплексный отчет по всем ранее созданным сценариям для какой-либо отдельной задачи. При этом можно создать два типа отчета: отчет типа Структура и отчет типа Сводная таблица. Итоговые отчеты всегда создаются на отдельных листах.
В отчете типа Структура перечислены все сценарии с определенными для них значениями ячеек. Этот тип отчета полезен тогда, когда каждый пользователь определяет сценарий со своими данными.
Отчет типа Сводная таблица предоставляет возможность эмпирического анализа сценариев. Этот тип отчета полезен тогда, когда сценарий имеет несколько наборов значений изменяющихся ячеек, заданных различными пользователями; с помощью сводных таблиц можно выполнить анализ для разных комбинаций сценариев.
Для создания итогового отчета по сценариям следует:
1. В меню Сервис выбрать команду Сценарии.
2. Щелкнуть по кнопке Отчет.
3. Выбрать тип отчета: Структура или Сводная таблица.
4. В поле Ячейки результата ввести ссылки на ячейки, значения которых надо представить в отчете. В качестве разделителя ссылок используется запятая. Ссылки можно ввести с клавиатуры или выделить их на рабочем листе. Несмежные ячейки добавляются при нажатой клавише <Ctrl>.
Пример. Рассмотрим следующую задачу. Допустим, что у вас просят в долг 10000 руб. и обещают возвращать по 2000 руб. в течение 6 лет. Будет ли выгодна эта сделка при годовой ставке 7%?
В приводимом ниже рисунке 8 в ячейку В5 введена формула
=ПЗ(В4;В2;-В3).
В ячейку В6 введена формула =ЕСЛИ(B1<B5;"Выгодно дать деньги в долг";ЕСЛИ(B5=B1;"Варианты равносильны";"Выгоднее деньги положить в банк под проценты"))
Рис. 8 .
Функция ПЗ зависит от нескольких параметров. Некоторыми из них вы можете управлять, например, сроком и суммой ежегодно возвращаемых денег. Часто бывает удобно проанализировать ситуацию для нескольких возможных вариантов параметров. Команда Сервис ► Сценарии предоставляет такую возможность с одновременным автоматизированным составлением отчета. Рассмотрим способ применения этой команды для следующих трех комбинаций срока и суммы ежегодно возвращаемых денег: 6 и 2000; 12 и 1500; 7 и 1500.
Выберем команду Сервис ► Сценарии. В открывшемся диалоговом окне Диспетчер сценариев (см. рис. 9) для создания первого сценария нажмите кнопку Добавить
Рис. 9 .
Откроется диалоговое окно Добавление сценария (Изменение сценария) (рис.10). В этом диалоговом окне в поле Название сценария введите, например, Вариант1, а в поле Изменяемые ячейки – ссылку на ячейки В2 и В3, в которые вводятся значения параметров задачи (срок и сумма ежегодно возвращаемых денег). (После ввода данных в поле Изменяемые ячейки диалоговое окно Добавление сценария изменит свое название на Изменение сценария.). В поле Примечание можно ввести текст, поясняющий сущность или особенность данного сценария, например, можно ввести значения параметров как показано на рис. 10.
Рис. 10.
После нажатия кнопки ОК появится диалоговое окно Значения ячеек сценария, в поля которого введите значения параметров для первого сценария как показано на рис. 11.
Рис. 11.
С помощью кнопки Добавить последовательно создайте нужное число сценариев (в данном примере еще два). После этого диалоговое окно Диспетчер сценариев будет иметь вид, показанный на рис. 12.
Рис. 12.
С помощью кнопки Вывести можно вывести результаты, соответствующие выбранному сценарию.
Нажатие кнопки Отчет открывает диалоговое окно Отчет по сценарию (рис. 13). В этом окне в группе переключателей Тип отчета укажите тип отчета, а в поле Ячейки результата дайте ссылку на ячейки, где вычисляются значения результирующей функции (см рис. 13). После нажатия кнопки ОК создается отчет. На рис. 14 показан отчет по сценариям типа Структура.
