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

В Excel предлагаются средства анализа "что если" трех типов: сценарии, таблицы данных и подбор параметров. В сценариях и таблицах данных берутся наборы входных значений и определяются возможные результаты. Подбор параметров отличается от сценариев и таблиц данных тем, что при его использовании берется результат и определяются возможные входные значения для его получения.

В сценарии может быть до 32 значений переменных. Если вы хотите проанализировать больше 32 значений и эти значения представляют собой только одну или две переменных, то можно использовать таблицы данных. Хотя таблица данных ограничена только одной или двумя переменными (одна для подстановки значений по столбцам и одна — по строкам), она позволяет использовать любое количество различных значений переменных. В сценарии можно использовать не более 32 различных значений, но вы можете создать сколько угодно сценариев.

Помимо этих трех средств можно установить надстройки для анализа "что если", например надстройку Поиск решения. Эта надстройка похожа на подбор параметров, но позволяет использовать больше переменных. Вы также можете создавать прогнозы, используя маркер заполнения и различные команды, встроенные в Excel. Для более сложных моделей можно использовать надстройку Пакет анализа.

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

Предположим, например, что в худшем случае ожидается доход в 50 000 ₽, а стоимость проданной продукции составляет 13 200 ₽, в результате чего получается 36 800 ₽ валовой прибыли. Чтобы определить этот набор переменных в качестве сценария, сначала введите на лист значения, как показано на следующем рисунке:

Изменяемые ячейки содержат введенные значения, а ячейка результата — формулу, основанную на изменяемых ячейках (на этом рисунке в ячейке B4 указана формула =B2-B3).

Затем в диалоговом окне Диспетчер сценариев эти значения можно сохранить как сценарий. Выберите Данные > Анализ "что если" > Диспетчер сценариев > Добавить.

В диалоговом окне Имя сценария назовите сценарий Худший случай и укажите, что ячейки B2 и B3 являются значениями, изменяющимися между сценариями. Если выбрать изменение ячеек на листе перед добавлением сценария, диспетчер сценариев автоматически вставляет ячейки. В противном случае вы можете ввести их вручную или использовать диалоговое окно выбора ячеек справа от диалогового окна Изменение ячеек.

Защита: вы также можете защитить сценарии, выбрав нужные параметры в разделе "Защита".

• Чтобы запретить изменение сценария на защищенном листе, установите флажок запретить изменения.

• Чтобы при защите листа сценарий не отображался, установите флажок скрыть.

Теперь предположим, что в лучшем случае ожидается доход в 150 000 ₽, а стоимость проданной продукции составляет 26 000 ₽, в результате чего получается 124 000 ₽ валовой прибыли. Чтобы определить этот набор значений как сценарий, создается другой сценарий с именем "Лучший случай" и для него вводятся другие значения ячеек B2 (150 000) и B3 (26 000). Поскольку ячейка валовой прибыли (B4) представляет собой формулу — разницу между доходами (B2) и расходами (B3) — ячейка B4 для сценария "Лучший случай" не изменяется.

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

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

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

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

При получении разных сценариев из различных источников в каждой из книг необходимо использовать одинаковую структуру ячеек. Например, значение доходов всегда должно находиться в ячейке B2, а значение расходов — в ячейке B3. Если вы используете разные структуры для сценариев из различных источников, слияние будет сложно выполнить.

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

Сводный отчет по сценариям, основанный на двух приведенных выше примерах, может выглядеть так:

Как можно заметить, что Excel автоматически добавил уровни группировки, которые можно разворачивать и сворачивать.

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