51)Сценарии. Редактирование сценария. Создание итогового отчета.

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

Если у нескольких пользователей есть определенные данные, которые необходимо использовать в сценариях, то можно собрать эти данные в отдельные книги и объединить сценарии из нескольких книг в одной.

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

Общие сведения

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

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

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

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

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

ОБЩИЕ СВЕДЕНИЯ О СЦЕНАРИЯХ

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

ПРИМЕЧАНИЕ. В этом разделе описано использование сценариев, а также приведены примеры данных и результаты применения сценариев с использованием этих данных. Пошаговые инструкции см. ниже, в разделе Создание сценария.

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

Изменяемые ячейки с введенными значениями.

В ячейке результата находится формула со значениями из изменяющихся ячеек (на данном рисунке — =B1-B2).

Затем с помощью диалогового окна Диспетчер сценариев эти значения сохраняются как сценарий под именем "Худший случай", и указывается, что ячейки B1 и B2 — значения, которые изменяются в сценариях.

ПРИМЕЧАНИЕ. Хотя в этом примере только две изменяющихся ячейки (B1 и B2), в сценарии может быть до 32 ячеек.

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

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

Изменяемые ячейки

Ячейка результата

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

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

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

СОВЕТ. Рекомендуется сначала создавать сценарий, а затем рассылать коллегам копию книги с этим сценарием. Это позволяет обеспечить одинаковую структуру всех сценариев.

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

ПРИМЕЧАНИЕ. По умолчанию для определения изменяющихся ячеек и ячеек результатов в сводном отчете используются ссылки на ячейки. В данном примере этим ячейкам присвоены имена, что упрощает прочтение сводного отчета. Если перед заполнением сводного отчета для ячеек создать имена, вместо ссылок на ячейки в отчете будут находиться имена.

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

Дополнительные сведения о создании сводных отчетов по сценариям см. в разделе Создание сводного отчета по сценариям.

Создание сценария

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

На вкладке Данные в группе Средства обработки данных выберите команду Анализ условия, а затем выберите в списке пункт Диспетчер сценариев.

Нажмите кнопку Добавить.

Введите в поле Название сценария название сценария.

В поле Изменяемые ячейки введите ссылки на ячейки, которые требуется задать в сценарии. Например, если нужно узнать, как изменение значений ячеек B1 и B2 повлияет на результат формулы, в которой используются эти ячейки, введите B1;B2.

ПРИМЕЧАНИЕ. Для сохранения исходных значений изменяемых ячеек перед созданием сценария, использующего другие значения, создайте сценарий, который использует исходные значения.

Установите необходимые флажки в группе Защита.

ПРИМЕЧАНИЕ. Эти параметры применяются только для защищенных листов.

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

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

Нажмите кнопку ОК.

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

Чтобы создать сценарий, нажмите кнопку ОК.

Если требуется создать дополнительные сценарии, повторите действия 2 — 8. После завершения создания сценариев нажмите кнопку ОК, а затем кнопку Закрыть в диалоговом окне Диспетчер сценариев.

Просмотр сценария

При просмотре сценария изменяются ячейки, сохраненные как часть этого сценария. Значения сценариев отображаются в ячейках, которые изменяются от сценария к сценарию, как и ячейки результатов. Например, если при использовании приведенных выше сценариев вывести сценарий "Лучший случай", в ячейке B1 будет отображено значение 150000, в ячейке B2 — 26000, а в ячейке B3 — 124000.

На вкладке Данные в группе Средства обработки данных выберите команду Анализ условия, а затем выберите в списке пункт Диспетчер сценариев.

Выберите имя сценария, который требуется просмотреть.

Нажмите кнопку Показать.

ПРИМЕЧАНИЕ. После закрытия диалогового окна Диспетчер сценариев на листе останутся значения сценария, выведенного последним. Если исходные значения сохранены как сценарий, их можно вывести перед закрытием диалогового окна Диспетчер сценариев.

Объединение сценариев

Выберите лист, в котором будут сохранены результаты объединенных сценариев.

На вкладке Данные в группе Средства обработки данных выберите команду Анализ условия, а затем выберите в списке пункт Диспетчер сценариев.

Нажмите кнопку Объединить.

В диалоговом окне Объединение сценариев щелкните стрелку возле поля Книга и выберите книгу со сценариями, которые требуется объединить в результатах.

В поле Лист щелкните имя листа со сценариями, которые требуется объединить.

Чтобы объединить сценарии выбранного листа в текущий лист, нажмите кнопку ОК.

Диалоговое окно Объединение сценариев закроется, и объединенные сценарии будут отображены в диалоговом окне Диспетчер сценариев.

При необходимости повторите предыдущие четыре действия, пока не будут объединены все нужные сценарии.

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

Создание итогового отчета по сценариям

На вкладке Данные в группе Средства обработки данных выберите команду Анализ условия, а затем выберите в списке пункт Диспетчер сценариев.

Нажмите кнопку Отчет.

Выберите параметр структура или сводная таблица.

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

ПРИМЕЧАНИЯ

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

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