Таблица данных или таблица чувствительности позволяет представить результаты формул в зависимости от значений одной или двух переменных, которые используются в этих формулах. С помощью команды ДАННЫЕ/ТАБЛИЦА ПОДСТАНОВКИ можно создать два типа таблиц данных: таблицу для одной переменной, которая проверяет воздействие этой переменной на несколько формул, или таблицу для двух переменных, которая проверяет их влияние на одну формулу.
Можно включить любое количество выходных формул при создании таблицы данных с одной переменной. Можно использовать разные формулы для различных столбцов (или строк), но все они должны использовать одни и те же входные ячейки. В таблице с двумя переменными допускается использование только одной формулы.
Таблица данных является хорошим средством в относительно простых ситуациях, когда используются только одна или две переменных, но реальные задачи обычно имеют значительно больше неизвестных величин. При моделировании более сложных задач, имеющих до 32 переменных, можно обратиться к диспетчеру сценариев.
Введем некоторые термины. Модель «что-если» - это любой рабочий лист, в котором можно подставлять различные значения для переменных, чтобы увидеть их влияние на другие величины, которые вычисляются по формулах, зависящим от этих переменных.
Изменяемые ячейки – это ячейки, содержащие значения, которые используются в качестве переменных.
Сценарий – это именованные комбинации значений, заданных для одной или нескольких изменяемых ячеек в модели «что-если».
Вызвать диспетчер сценариев можно командой СЕРВИС/СЦЕНАРИИ. При работе с диспетчером сценариев можно:
1). Создать несколько сценариев для одной модели «что-если», каждый из которых может иметь свое собственное множество переменных.
2). Распространить модель «что-если» между членами рабочей группы, чтобы они моли добавить свои сценарии. Затем можно собрать версии и объединить все сценарии в отдельном листе.
3). Без труда отслеживать варианты сценария, т.к. диспетчер сценариев сохраняет дату и имя пользователя при каждом изменении сценария.
4). Использовать пароль для защиты сценариев от изменений и даже скрыть их.
5). Воспользоваться отчетом СВОДНАЯ ТАБЛИЦА и сравнить между собой сценарии с разными множествами переменных, созданные несколькими пользователями.
6). Определить сценарий с помощью кнопки СЦЕНАРИИ на пользовательской панели инструментов.
Технология создания сценария:
- выполнить команду СЕРВИС/СЦЕНАРИИ;
- в окне диалога ДИСПЕТЧЕР СЦЕНАРИЕВ нажать кнопку ДОБАВИТЬ;
- в окне ДОБАВЛЕНИЕ СЦЕНАРИЯ ввести имя сценария;
- в поле ИЗМЕНЯЕМЫЕ ЯЧЕЙКИ указать ячейки, которые будут изменяться. По умолчанию в этом поле выводится ссылка на ячейку или диапазон, которые был выделен перед выполнением команды, но его можно изменить;
- нажать кнопку ОК, чтобы создать первый сценарий;
- откроется окно диалога ЗНАЧЕНИЯ ЯЧЕЕК СЦЕНАРИЯ с полями для каждой изменяемой ячейки. Эти поля содержат значения, которые в данный момент выведены на рабочем листе. Эти значения можно изменить. В каждое поле можно ввести константу или формулу, например увеличить значение переменной на какую-либо величину или в несколько раз;
- чтобы создать другой сценарий, нажать кнопку ДОБАВИТЬ для возвращения в окне ДОБАВЛЕНИЕ СЦЕНАРИЯ. Так можно создать сколько угодно сценариев. После окончания создания сценариев нажать кнопку ОК для возврата в окно диспетчера сценариев, а затем ЗАКРЫТЬ для возврата в рабочий лист.
Лучше определить начальные значения переменных в качестве сценария, прежде чем изменять любую из переменных. В противном случае начальные значения будут потеряны при вводе новых значений в изменяемые ячейки.
Для запуска какого-либо сценария следует выполнить команду СЕРВИС/СЦЕНАРИИ и в появившемся диалоговом окне выбрать имя сценария и нажать кнопку ВЫВЕСТИ. Диспетчер сценариев заменит значения переменных на рабочем листе значениями, заданными при создании выбранного сценария. Окно диалога диспетчера сценариев при использовании кнопки ВЫВЕСТИ остается на экране, поэтому результаты других сценариев можно просмотреть без возврата в рабочий лист. Прежде чем экспериментировать с кнопкой ВЫВЕСТИ диспетчера сценариев, стоит сохранить рабочий лист. Нажатие кнопки ВЫВЕСТИ заменяет текущие значения на рабочем листе значениями из выбранного сценария. В любом случае с помощью команды ПРАВКА/ОТМЕНИТЬ можно отменить воздействие нескольких последних сценариев.
При использовании команды ФАЙЛ/СОХРАНИТЬ сценарии сохраняются вместе со всеми остальными данными рабочего листа. После загрузки книги становятся доступными все сценарии, связанные с рабочими листами.
Excel 97 позволяет объединить несколько сценариев, созданных разными пользователями в разных рабочих книгах в основной лист. Для этого следует открыть все книги, содержащие нужные сценарии, активизировать лист, который будет содержать объединенные сценарии, и нажать кнопку ОБЪЕДИНИТЬ в диалоговом окне ДИСПЕТЧЕРА СЦЕНАРИЕВ. После объединения можно вывести на экран разные сценарии одновременно и наблюдать затем, как комбинации переменных влияют на значение в результирующей ячейке.
Excel 97 позволяет создавать достаточно сложные модели, включающие в себя любое необходимое количество сценариев и до 32 переменных в каждом сценарии. Делается это с помощью создания отчетов по сценариям. Отчет показывает значения, которые каждый сценарий назначает изменяемым ячейкам. Кроме того, отчет может представить воздействие каждого сценария на одну или несколько ячеек результата. Диспетчер сценариев предлагает два типа отчетов по сценариям:
1). Отчет СТРУКТУРА СЦЕНАРИЯ – это средство, позволяющее отслеживать все варианты. В этом отчете приводятся имя изменяемых и результирующих ячеек (если имена назначены этим ячейкам). Имена сценариев выводятся в качестве заголовков столбцов. А столбцы появляются в порядке определения сценариев. Изменяемые ячейки для каждого сценария выделяются серым цветом, что позволяет с первого взгляда определить, какими сценариями контролируются конкретные ячейки в модели «что-если». Выше и левее итогового отчета выводятся символы структуры, позволяющие отображать и скрывать детальные данные.
2). Отчет СВОДНАЯ ТАБЛИЦА ПО СЦЕНАРИЮ предоставляет дополнительные инструменты анализа «что-если», позволяя работать с элементами самого отчета. Сводная таблица является инструментом, позволяющим с помощью мыши выполнять прямые действия с ячейками для смешивания и сравнения различных сценариев в отчете и наблюдать получаемые при этом значения в ячейках результата. Этот инструмент особенно полезен для анализа сложных моделей, которые включают в себя сценарии с различными множествами изменяемых ячеек, созданных разными пользователями.
Вывод отчетов по сценариям и выбор типа отчета происходит при нажатии кнопки ОТЧЕТ в окне диалога ДИСПЕТЧЕРА СЦЕНАРИЕВ. В появившемся диалоговом окне ОТЧЕТ ПО СЦЕНАРИЮ можно также дополнительно указать ячейки результата, которые следует включить в отчет (они разделяются точкой с запятой).
