Сценарий — это набор значений, которые Excel сохраняет и может автоматически подставлять на лист. Вы можете создать и сохранить различные группы значений в виде сценариев, а затем переключаться на любой из них, чтобы просматривать различные результаты.
Если у нескольких пользователей есть определенные данные, которые вы хотите использовать в сценариях, то можно добавить их в отдельные книги и объединить сценарии из нескольких книг.
Разработав все необходимые сценарии, можно создать сводный отчет по сценарию, включающий сведения обо всех сценариях.
Управлять сценариями можно с помощью диспетчера сценариев из раздела "Анализ "что если " в группе "Прогноз " на вкладке " Данные ".
Типы анализа "что если"
Excel включает три вида средств анализа What-If: диспетчер сценариев, таблица данных и поиск цели. Сценарии и таблица данных берут наборы входных значений и проецируют их для определения возможных результатов. Подбор параметров отличается от сценариев и таблицы данных тем, что он принимает результат и проецирует его в обратном направлении, чтобы определить возможные входные значения, которые приведут к этому результату.
В сценарии может быть до 32 значений переменных. Если вы хотите проанализировать больше 32 значений и эти значения представляют собой только одну или две переменных, то можно использовать таблицы данных. Хотя таблица данных ограничена одной или двумя переменными (одной для ячейки ввода строки и одной для входной ячейки столбца), она может включать столько различных значений переменных, сколько необходимо. В сценарии можно использовать не более 32 различных значений, но вы можете создать сколько угодно сценариев.
Помимо этих трех средств можно установить надстройки для анализа "что если", например надстройку Поиск решения. Эта надстройка похожа на подбор параметров, но позволяет использовать больше переменных. Вы также можете создавать прогнозы, используя маркер заполнения и различные команды, встроенные в Excel.
Создание сценариев
Предположим, вам требуется создать бюджет, но доходы точно не известны. С помощью сценариев можно определить различные возможные значения дохода, а затем переключаться между сценариями для выполнения анализа "что если".
Предположим, например, что в худшем случае ожидается доход в 50 000 ₽, а стоимость проданной продукции составляет 13 200 ₽, в результате чего получается 36 800 ₽ валовой прибыли. Чтобы определить этот набор переменных в качестве сценария, сначала введите на лист значения, как показано на следующем рисунке:
Ячейки "Изменяемый" содержат значения, которые вы вводите, а ячейка "Результат" содержит формулу, основанную на ячейках "Изменяемые" (на этом рисунке ячейка B4 содержит формулу =B2-B3).
Затем в диалоговом окне Диспетчер сценариев эти значения можно сохранить как сценарий. Перейдите на вкладку " Данные ", выберите "Анализ "что если", выберите "Диспетчер сценариев" и нажмите кнопку "Добавить".
В диалоговом окне Имя сценария назовите сценарий Наихудший вариант и укажите, что ячейки B2 и B3 являются значениями, которые изменяются между сценариями. Если выбрать «Изменение ячеек на листе» перед добавлением сценария, диспетчер сценариев автоматически вставит ячейки. В противном случае их можно ввести вручную или использовать диалоговое окно выбора ячеек справа от диалогового окна "Изменение ячеек".
Примечание
Хотя в этом примере только две изменяющихся ячейки (B2 и B3), в сценарии может быть до 32 ячеек.
Защита — вы также можете защитить свои сценарии. В разделе «Защита» проведите проверку нужных параметров или снимите флажки, если защита не нужна.
- Выберите "Запретить изменения", чтобы предотвратить изменение сценария, когда лист защищен.
- Чтобы при защите листа сценарий не отображался, установите флажок скрыть.
Примечание
Эти параметры применяются только к защищенным листам. Дополнительные сведения о защищенных листах см. в статье Защита листа.
Теперь предположим, что ваш лучший сценарий бюджета — валовой доход в размере 150 000 долларов и затраты на проданные товары в размере 26 000 долларов, оставляя 124 000 долларов валовой прибыли. Чтобы определить этот набор значений как сценарий, создается другой сценарий с именем "Лучший случай" и для него вводятся другие значения ячеек B2 (150 000) и B3 (26 000). Так как валовая прибыль (ячейка B4) является формулой, т. е. разностью между выручкой (B2) и расходами (B3), ячейку B4 не следует изменять для оптимального сценария.
После сохранения сценария он становится доступным в списке сценариев, которые можно использовать для анализа "что если". С учетом значений на предыдущем рисунке, если вы решили отобразить наилучший сценарий, значения на листе изменятся, и выглядят, как на следующем рисунке:
Объединение сценариев
В одном листе или книге может быть вся необходимая информация для создания нужных сценариев. Однако может потребоваться собрать информацию о сценарии из других источников. Например, предположим, что вы пытаетесь составить бюджет компании. Для этого необходимо получить сценарии из различных отделов (финансового, производственного, маркетингового и юридического), поскольку все эти источники обладают различными данными, которые нужно использовать при создании бюджета.
Эти сценарии можно собрать на один лист с помощью команды Объединить. Каждый источник может передавать любое нужное количество изменяемых ячеек. Например, все отделы могут предоставить оценку расходов и только некоторые — оценку доходов.
При выборе слияния диспетчер сценариев загружает диалоговое окно "Сценарии слияния ", в котором перечислены все листы активной книги, а также все другие книги, открытые на данный момент. Мастер сообщит вам количество сценариев на каждом выбранном исходном листе.
При сборе различных сценариев из разных источников используйте одну и ту же структуру ячеек в каждой из книг. Например, поместите параметр "Доходы" всегда в ячейку B2, а "Расходы" — всегда в ячейку B3. Если вы используете разные структуры для сценариев из различных источников, слияние будет сложно выполнить.
Совет
Рекомендуется сначала создать сценарий, а затем разослать коллегам копию книги с ним. Такой подход упрощает одинаковую структуру всех сценариев.
Сводные отчеты по сценариям
Чтобы сравнить несколько сценариев, создайте отчет, который обобщит их на одной странице. В отчете может быть перечислены сценарии рядом друг с другом или они могут быть представлены в отчете сводной таблицы.
Сводный отчет по сценарию, основанный на двух предыдущих примерах сценариев, может выглядеть следующим образом:
Excel автоматически добавляет уровни группировки, которые разворачивают и сворачивают представление при выборе различных параметров.
В конце сводного отчета появится примечание, объясняющее, что столбец "Текущие значения " представляет значения изменяемых ячеек при создании сводного отчета по сценарию. Ячейки, измененные для каждого сценария, выделяются серым цветом.
Примечание
- По умолчанию в сводном отчете используются ссылки на ячейки для определения изменяющихся ячеек и ячеек результатов. Если перед запуском сводного отчета именованные диапазоны ячеек будут созданы в отчете, он будет содержать имена вместо ссылок на ячейки.
- Отчеты о сценариях не пересчитываются автоматически. Если изменить значения сценария, эти изменения не будут отображаться в существующем сводном отчете. Они отображаются при создании нового сводного отчета.
- Ячейки результата не требуются для создания сводного отчета по сценариям, однако они необходимы для отчета сводной таблицы по сценариям.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
См. также
Получение нескольких результатов с помощью таблицы данных
Постановка и решение задачи с помощью надстройки "Поиск решения"
Полные сведения о формулах в Excel
Рекомендации, позволяющие избежать появления неработающих формул в Excel