Понятие о сценариях в Microsoft Excel

При решении достаточно сложных задач иногда приходится сравнивать поведение системы, описанной формулами на листе Microsoft Excel, при подстановке нескольких разных наборов исходных данных. Например, прогнозируя прибыль компании от выполнения тех или иных работ, может быть полезно рассмотреть крайние случаи: компания получает максимум заказов, и при этом стоимость покупных комплектующих на рынке падает; компания получает минимум заказов, и при этом стоимость покупных комплектующих на рынке остается на прежнем уровне. Очевидно, в первом случае компания получит максимум прибыли, во втором – минимум.

Наглядно сравнить поведение системы в обоих случаях на одном листе затруднительно, так как изменение большого объема исходных данных может потребовать много времени. Можно скопировать таблицу с формулами на другой лист, внести в копию изменения, и затем сравнивать с оригинальным листом. Однако Microsoft Excel представляет и более удобный инструмент – сценарии.

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

Управление сценариями осуществляется через диалоговое окно «Диспетчер сценариев» (рис. 15.14), для вызова которого служит команда Диспетчер сценариев… из выпадающего меню кнопки « Анализ Что если». В окне отображается список всех сценариев, имеющихся в данной рабочей книге. Для каждого сценария при его выделении отображаются ячейки, в которые будут подставлены значения, а также сведения об авторе сценария (берутся из реестра Windows или из окна свойств документа) и дате его создания и изменения.

Нажатие кнопки «Вывести» подставляет данные выделенного сценария в соответствующие ячейки. Лист автоматически обновится, и можно сразу же увидеть изменения. Диалоговое окно при этом не закрывается.

Для создания сценария служит кнопка «Добавить…». В открывшемся диалоговом окне (рис. 15.15, а) следует указать имя сценария, выделить ячейки для подстановки данных, и, при необходимости, отредактировать комментарий. Флажок «Запретить изменения» запрещает редактирование сценария, а флажок «Скрыть» убирает сценарий из списка «Диспетчера сценариев». Оба флажка начнут работать только после того, как лист книги будет защищен паролем (см. лабораторную работу № 11). Если защита будет снята, скрытые сценарии вновь отобразятся в окне, а нередактируемый сценарий можно будет редактировать.

После нажатия кнопки «OK» отобразится второе окно (рис. 15.15, б), где можно будет последовательно задать (или изменить, если выделенные ячейки в предыдущем окне уже содержали данные) значения ячеек. Если в ячейках уже введены нужные данные, в окне можно ничего не менять и нажать кнопку «ОК». Кнопка «Добавить» возвращается к диалоговому окну рис. 15.15, а, позволяя сразу же начать создание нового сценария.

Для изменения уже созданного сценария в диалоговом окне диспетчера сценариев служит кнопка «Изменить…». Она последовательно открывает диалоговые окна, аналогичные рис. 15.15, а и б. Кнопка «Удалить» служит для удаления ненужных сценариев.

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

а)

б)

Рис. 15.15 Диалоговые окна создания сценария

Процедура копирования сценариев в Microsoft Excel называется объединением сценариев. Для этого нужно открыть лист Excel, на который нужно скопировать сценарии, открыть Диспетчер сценариев (рис. 15.14) и нажать кнопку «Объединить…». В открывшемся диалоговом окне (рис. 15.16) следует указать книгу (должна быть открыта) и лист, откуда копируются сценарии (внизу окна отобразится общее количество сценариев на выбранном листе) и нажать кнопку «OK». Сценарии будут скопированы на текущий лист, и уже для него отобразится диалоговое окно Диспетчера сценариев, позволяя удалить ненужные сценарии, а также изменить и применить нужные.

Наконец, в Microsoft Excel имеется возможность автоматически предоставить действие всех сценариев листа в едином отчете о работе сценариев. В диалоговом окне Диспетчера сценариев (рис. 15.14) нужно нажать кнопку «Отчет…». В открывшемся окне (рис. 15.17) выбирают, во-первых, форму отчета (это может быть отчет в виде структурированной таблицы или сводной таблицы, см. Лабораторную работу №14), а, во-вторых, ячейки результата – те ячейки, значения которых зависят от исходных данных и которые наиболее важны для анализа. При нажатии кнопки «OK» в книгу будет добавлен отдельный лист и на нем будет сгенерирован отчет соответствующего типа.

Средство анализа данных

Еще одной очень мощной надстройкой Microsoft Excel является так называемый Пакет анализа. Данный пакет содержит множество функций для автоматизации решения сложных задач математической статистики. Также в пакете содержатся дополнительные статистические функции, которые после подключения пакета будут доступны для выбора в диалоговом окне Мастера функций (см. рис.12.1 Лабораторной работы №12). При этом функции будут доступны, даже если надстройка не будет активирована (флажок в окне рис. 15.5 снят), достаточно, чтобы надстройка «Пакет анализа» была установлена на компьютер.

Для запуска надстройки служит кнопка « Анализ данных» в группе Анализ вкладки Данные (кнопка доступна, если данная надстройка включена). В открывшемся диалоговом окне (рис. 15.18) перечислены все типы задач, решение которых автоматизируется надстройкой.

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

Рис. 15.18 Диалоговое окно выбора задачи в надстройке «Анализ данных»

Рис. 15.19 Диалоговое окно анализа данных на примере задачи корреляционного анализа

Поскольку большинство задач, решаемых надстройкой, требует знания соответствующих разделов высшей математики и математической статистики, работа с надстройкой не будет рассматриваться в данном пособии. По мере изучения соответствующих разделов высшей математики студентам рекомендуется самостоятельно вернуться к данной надстройке. К тому же, надстройка сопровождается достаточно мощной и полной справочной системой, которую можно вызвать нажатием кнопок «Справка» в диалоговых окнах рис. 15.18 и рис. 15.19.