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

Диспетчер сценариев

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

Определим некоторые термины:

Сценарий — это именованные комбинации значений, заданных для одной или нескольких изменяемых ячеек в модели "что-если".

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

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

Ø создать несколько сценариев для одной модели «что-если», каждый из которых может иметь свое собственное множество переменных;

Ø отслеживать варианты сценария, так как диспетчер сценариев сохраняет дату и имя пользователя при каждом изменении сценария

Ø использовать пароль для защиты сценариев от изменений и даже скрыть их из виду;

Ø воспользоваться отчетом Сводная таблица и сравнить между собой сценарии с разными множествами переменных.

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

С помощью команды Подбор параметра из меню Сервис можно определить неизвестное значение, которое будет давать желаемый результат.

Например, предположим, что нужно узнать, какую максимальную ссуду под закладную можно взять на 30 лет, если процентная ставка равна 6,5 процента и вы должны ограничить свои месячные выплаты суммой 2 000 руб.

Чтобы использовать команду Подбор параметра для получения ответа на этот вопрос, нужно выполнить следующие действия:

1. Решить задачу с каким-нибудь начальным значением параметра, используя функцию ППЛАТ.

2. Сделать ячейку с формулой активной (установить курсор), в меню Сервис выбрать команду Подбор параметра.

3. В окне диалога Подбор параметра оставьте без изменения значение в поле Установить в ячейке.

4. В поле Значение ввести максимальное значение, которое нужно получить качестве значения формулы, в данном примере -2000 (- означает выплаты).

5. В поле Изменяя значение ячейки ввести ссылку на ячейку, где находится сумма (ссуда).

6.

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

Оказалось, что максимальная ссуда на 30 лет с процентной ставкой 6,5% равна 316 422 рубля.