Создание таблицы данных с двумя переменными

В таблице данных с двумя переменными используется формула, содержащая два списка входных значений, — она должна ссылаться на две разные ячейки ввода. При создании формулы используют ссылки па две ячейки ввода, значения которых должны изменяться. Например, на рис. 6.18 это ячейка с процентной ставкой и ячейка со сроком кредитования.

Рис. 6.18. Результирующая таблица данных с двумя переменными.

Таблица данных является хорошим средством в относительно простых ситуациях, когда используются только одна или две переменных, но реальные задачи обычно имеют значительно больше неизвестных величин. При моделировании более сложных задач, имеющих до 32 переменных, можно обратиться к диспетчеру сценариев.

Диспетчер сценариев. Сценарий — это именованные комбинации значений, заданных для одной или нескольких изменяемых ячеек в модели «что-если». Модель «что-если» — это любой лист, в который можно подставлять различные значения для переменных, чтобы увидеть их влияние на другие величины, которые вычисляются по формулам, зависящим от этих переменных. Изменяемые ячейки — это ячейки, содержащие значения, которые используются в качестве переменных.

Вызвать диалоговое окно Диспетчер сценариев можно командой ДАННЫЕ / Анализ «что-если». При работе с диспетчером сценариев можно:

• • создать несколько сценариев для одной модели «что-если», каждый из которых может иметь собственное множество переменных;
• • распространить модель «что-если» между членами группы, чтобы они могли добавить свои сценарии. Затем можно собрать версии и объединить все сценарии в отдельном листе;
• • без труда отслеживать варианты сценария, так как диспетчер сценариев сохраняет дату и имя пользователя при каждом изменении сценария;
• • использовать пароль для защиты сценариев от изменений и даже скрыть их;
• • воспользоваться отчетом Сводная таблица и сравнить между собой сценарии с разными множествами переменных, созданные несколькими пользователями;
• • определить сценарий с помощью кнопки Сценарии на пользовательской панели инструментов.

Для создания сценария необходимо выполнить следующие действия:

• 1) ДАННЫЕ /Анализ «что-если» / Диспечер сценариев;
• 2) в окне Диспетчер сценариев нажать кнопку Добавить;
• 3) в окне Добавление сценария ввести имя сценария;
• 4) в поле Изменяемые ячейки указать ячейки, которые будут изменяться. По умолчанию в этом поле выводится ссылка на ячейку или диапазон, которые были выделены перед выполнением команды;
• 5) нажать кнопку ОК, чтобы создать первый сценарий;
• 6) откроется диалоговое окно Значения ячеек сценария с полями для каждой изменяемой ячейки. Эти поля содержат значения, которые в данный момент выведены на листе, — их можно изменить. В каждое поле можно ввести константу или формулу, например увеличить значение переменной на какую-либо величину или в несколько раз;
• 7) чтобы создать другой сценарий, нажать кнопку Добавить для возвращения в окне Добавление сценария. Так можно создать сколько угодно сценариев. После окончания создания сценариев нажать кнопку ОК для возврата в окно диспетчера сценариев, а затем — Закрыть для возврата на лист.

Лучше определить начальные значения переменных в качестве сценария, прежде чем изменять любую из переменных. В противном случае начальные значения будут потеряны при вводе новых значений в изменяемые ячейки.

Для запуска какого-либо сценария следует выбрать ДАННЫЕ / Анализ «что-если» / Диспетчер сценариев, в появившемся диалоговом окне выбрать имя сценария и нажать кнопку Вывести. Диспетчер сценариев заменит значения переменных на листе значениями, заданными при создании выбранного сценария. Диалоговое окно диспетчера сценариев при использовании кнопки Вывести остается на экране, поэтому результаты других сценариев можно просмотреть без возврата на лист. Прежде чем экспериментировать с кнопкой Вывести диспетчера сценариев, стоит сохранить лист.

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