Что заполнить в окне значение ячеек сценария
Сценарий — это набор значений, которые Excel сохраняет и может автоматически подставлять на листе. Вы можете создать и сохранить различные группы значений в виде сценариев, а затем переключаться на любой из них, чтобы просматривать различные результаты.
Если у нескольких пользователей есть определенные данные, которые вы хотите использовать в сценариях, то можно добавить их в отдельные книги и объединить сценарии из нескольких книг.
После подготовки всех нужных сценариев можно создать сводный отчет, в который включаются данные из всех сценариев.
Управление сценариями выполняется с помощью диспетчера сценариев в группе Анализ «что если» на вкладке Данные.
В Excel предлагаются средства анализа «что если» трех типов: сценарии, таблицы данных и подбор параметров. В сценариях и таблицах данных берутся наборы входных значений и определяются возможные результаты. Подбор параметров отличается от сценариев и таблиц данных тем, что при его использовании берется результат и определяются возможные входные значения для его получения.
В сценарии может быть до 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 или попросить помощи в сообществе Answers community.
Диспетчер сценариев для анализа прогнозной модели
Признаком качественно выполненной прогнозной модели является наличие анализа чувствительности параметров модели. Как результирующий итог модели (например, внутренняя норма доходности – IRR или объем инвестиций), поведет себя при том или ином изменении исходных посылок? Если итог получен в результате сложных вычислений, то влияние отдельных параметров очень удобно оценивать с помощью анализа что если. Однако, этот инструмент удобен, когда нужно проанализировать влияние на результат одного или двух параметров. Если одновременно необходимо изучить влияние более чем двух параметров, воспользуйтесь диспетчером сценариев.[1] Диспетчер сценариев позволяет выполнить анализ чувствительности с возможностью изменения до 32 значений в ячейках с исходными данными.
Рис. 1. Данные, на которых основаны сценарии
Скачать заметку в формате Word или pdf, примеры в формате Excel
Допустим, необходимо создать для компании наиболее благоприятный, наименее благоприятный и наиболее вероятный сценарии продаж модели автомобиля в масштабе 1:43 (рис. 1), изменяя значения объема продаж за первый год, продажной цены в первый год и годового роста продаж. Для каждого сценария требуется отследить прибыль за каждый год после уплаты налогов и чистую приведенную стоимость проекта. Модель (рис. 2) построена так, что она не относится ни к одному из сценариев (хотя для модели можно использовать и данные одного из сценариев).
Рис. 2. Модель, на которой основаны сценарии
Для определения наиболее благоприятного сценария откройте вкладку ДАННЫЕ и в группе Работа с данными в раскрывающемся списке Анализ «что если» выберите инструмент Диспетчер сценариев. Нажмите кнопку Добавить и заполните поля в диалоговом окне Добавление сценария (рис. 3). Введите имя сценария и выберите ячейки В2:В4, как ячейки с исходными данными, содержащие определяющие сценарий значения. Нажмите кнопку OK и в открывшемся диалоговом окне Значения ячеек сценария заполните поля входными значениями, определяющими наиболее благоприятный вариант (рис. 4).
Рис. 3. Исходные данные для наиболее благоприятного сценария
Рис. 4. Определение исходных значений для наиболее благоприятного сценария
В диалоговом окне Значение ячеек сценария нажмите кнопку Добавить, и аналогичным образом введите данные для наиболее вероятного и наименее благоприятного сценариев. После ввода данных для всех трех сценариев в диалоговом окне Значение ячеек сценария нажмите ОК. Вы вернетесь в окно Диспетчер сценариев (рис. 5). Сейчас в нем отражены все три сценария. Нажмите кнопку Отчет. Выберите ячейки с конечными результатами, которые должны отображаться в отчетах по сценариям (рис. 6). Для отслеживания выбраны значения прибыли за каждый год после уплаты налогов (ячейки B18: F18) и значение чистой приведенной стоимости (ячейка B20). Так как ячейки с результатами B18:F18 и B20 находятся в несмежных диапазонах, их следует перечислить через точку с запятой. Также несколько диапазонов ячеек можно выбрать и внести при нажатой клавише <Ctrl>. Установите переключатель Тип отчета в положение структура, и нажмите кнопку OK. В книге Excel будет создан отчет Структура сценария (рис. 7).
Рис. 5. Диспетчер сценариев
Рис. 6. Диалоговое окно Отчет по сценарию для выбора в отчет ячеек с результатами
Рис. 7. Отчет по сценариям
Обратите внимание, что в отчет включен столбец, помеченный как Текущие значения, для изначально указанных на листе значений. В наименее благоприятном сценарии компания несет убытки (в размере 13 346 долларов), в наиболее благоприятном — получает прибыль (в размере 226 893 долларов). Так как в наименее благоприятном сценарии цена ниже переменных затрат, компания теряет деньги каждый год.
Некоторые замечания
При установке в диалоговом окне Отчет по сценарию переключателя в положение сводная таблица результаты по сценариям представляются в формате сводных таблиц (рис. 8). На мой взгляд, такое представление менее интересно.
Рис. 8. Отчет по сценариям в виде сводной таблицы
Если в диалоговом окне Диспетчер сценариев выбрать один из сценариев и нажать кнопку Вывести, на листе с моделью (рис. 9) появятся значения входных ячеек для выбранного сценария, и все формулы будут автоматически пересчитаны для выбранного сценария. Этот инструмент отлично подходит для подготовки презентации. Ctrl+Z отменяет работу сценария, и возвращает лист в исходное состояние.
Рис. 9. На лист с моделью выведены расчет для наиболее благоприятного сценария
С помощью инструмента Диспетчер сценариев трудно создать много сценариев, поскольку приходится вводить значения для каждого сценария отдельно. Большое количество сценариев можно создать с помощью моделирования по методу Монте-Карло. При использовании метода Монте-Карло можно найти, например, вероятность того, что чистая приведенная стоимость денежных потоков проекта является неотрицательной. Это важный показатель, поскольку такая вероятность показывает, повышает ли проект стоимость компании.
Как и в любой структуре данных при нажатии на знак «минус» (–) в строках 5 и 9 отчета Структура сценария (см. рис. 7) строки с предполагаемыми значениями скрываются, а отображаются только результаты. При нажатии на знак «плюс» (+) отчет восстанавливается в полном объеме.
Предположим, что вы отправили файл нескольким сотрудникам, и каждый из них добавил собственный сценарий. После того как каждый сотрудник вернет файл со сценариями, можно объединить все сценарии в одной (например, исходной) книге. Откройте версию книги каждого сотрудника, нажмите в исходной книге в диалоговом окне Диспетчер сценариев кнопку Объединить и затем выберите книги, содержащие сценарии, которые требуется объединить. Выбранные сценарии будут автоматически объединены в исходной книге.
Что заполнить в окне значение ячеек сценария
На этом шаге мы рассмотрим диспетчер сценариев.
Анализ данных с помощью таблиц подстановки является весьма эффективным. Однако он имеет несколько недостатков:
- Одновременно можно анализировать расчетные данные только при изменении одного или двух исходных параметров.
- Процесс создания таблицы подстановки интуитивно не всегда понятен.
- При использовании таблицы подстановки с двумя входами можно проанализировать результаты расчетов, проведенных только по одной формуле. Для других формул нужно создать дополнительные таблицы подстановки.
- Очень часто бывает необходимо просмотреть результаты расчетов, проведенных только для нескольких определенных комбинаций входных параметров, а не всю таблицу подстановки.
С помощью средства Диспетчер сценариев можно достаточно просто автоматизировать процесс выполнения анализа "что-если" для различных моделей. С его помощью Вы можете создать несколько наборов данных вводимых значений (в терминологии средства Диспетчер сценариев они называются изменяемыми ячейками ) для любого количества переменных и присвоить имя каждому набору. Затем по имени можно выбрать определенный набор данных, и Excel покажет результаты анализа этих данных на рабочем листе. Кроме того, можно создать итоговый отчет по сценариям, в котором будет показан результат подстановки различных комбинаций входных параметров. Итоговый отчет может быть представлен в виде обычного структурированного списка или сводной таблицы.
В качестве примера рассмотрим процесс прогнозирования объема продаж товаров на текущий год. Объем продаж может зависеть от многих факторов, поэтому создается три сценария — по одному для лучшего, худшего и наиболее вероятного случаев. После того как сценариям будет присвоено имя, Вы сможете легко переключаться между ними, выбирая соответствующее название из списка. При этом Excel будет автоматически подставлять нужные исходные данные в рабочий лист и пересчитывать формулы.
Чтобы Вы смогли получить общее представление о средстве Диспетчер сценариев , начнем с простого примера — производственной модели (рис. 1).
Рис. 1. Пример производственной модели
В этом примере определены три сценария, которые приведены в таблице 1. В сценарии для лучшего случая тариф почасовой оплаты и стоимость материалов наименьшие. в сценарии для худшего случая эти значения самые большие. В третьем сценарии, или в наиболее вероятном случае, используются промежуточные значения этих параметров (они определяются менеджером с учетом ряда факторов). Менеджеры должны быть готовы к худшему случаю, однако им также необходимо знать, что будет, если сработает сценарий лучшего случая.
Доступ к средству Диспетчер сценариев можно получить с помощью команды Сервис | Сценарии . При выборе этой команды будет вызвано диалоговое окно Диспетчер сценариев , показанное на рисунке 2.
Рис. 2. Диалоговое окно Диспетчер сценариев
Когда Вы впервые вызываете это диалоговое окно, в нем имеется сообщение, в котором сказано, что ни один сценарий не определен, и это не удивительно, ведь Вы только начали. Как только будут добавлены новые сценарии, их названия появятся в диалоговом окне. Чтобы добавить сценарий, в диалоговом окне Диспетчер сценариев щелкните на кнопке Добавить . Появится диалоговое окно Добавление сценария , которое показано на рисунке 3. Это диалоговое окно разделено на четыре части:
Рис. 3. Диалоговое окно Добавление сценария
- Название сценария. В этом поле можно указать любое имя сценария, но желательно, чтобы оно что-то обозначало.
- Изменяемые ячейки. Ячейки, в которых находятся исходные данные для сценария. В это поле можно ввести абсолютный адрес ячейки или ее имя. Разрешается выбирать несколько ячеек, причем все они не обязательно должны быть смежными. В каждом сценарии, которому присвоено имя, можно использовать одни и те же наборы изменяемых ячеек или разные изменяемые ячейки. Количество изменяемых ячеек для одного сценария ограничено числом 32.
- Примечание. По умолчанию в это поле Excel помещает информацию о том, кто создал сценарий, а также дату его создания. Однако Вы можете отредактировать этот текст, добавить к нему новый текст или вовсе удалить его.
- Защита. Две опции, которые позволяют защитить сценарий от изменений и скрыть его, можно активизировать только в том случае, если рабочий лист защищен и в диалоговом окне Защитить лист активизирована опция Сценарии . Защита сценария предотвращает модификацию его кем-либо другим, а скрытый сценарий вообще не появляется в диалоговом окне Диспетчер сценариев .
После того как вы заполнили диалоговое окно Добавление сценария , щелкните на кнопке OK . Появится диалоговое окно Значение ячеек сценария , которое показано на рисунке 4. В этом диалоговом окне перечислены поля всех изменяемых ячеек, которые были определены в предыдущем диалоговом окне. Введите значения для каждой ячейки сценария. Если щелкните на кнопке OK , то Вы вернетесь к диалоговому окну Диспетчер сценария . В нем теперь будет находиться имя созданного Вами сценария. Если необходимо создать еще сценарии, щелкните на кнопке Добавить и повторите описанную выше последовательность действий.
Рис. 4. Диалоговое окно Значение ячеек сценария
Примечание . Для удобства использования сценариев необходимо поместить на любую панель инструмент Сценарий :
- Выполнить команду Сервис | Настройка .
- В диалоговом окне Настройка выбрать вкладку Команды .
- Выбрать категорию Сервис .
- Выделить инструмент Сценарий и перетащить его на любую панель инструментов.
- Щелкнуть на кнопке Закрыть .
На следующем шаге мы рассмотрим отображение, изменение и объединение сценариев.
Способ. Диспетчер сценариев
Средства Microsoft Excel позволяют создавать и сохранять в виде сценариев наборы входных значений, приводящих к различным результатам.
Сценарий — это множество входных значений, называемых изменяемыми ячейками, которое можно сохранить под указанным именем, а затем применить к модели рабочего листа, чтобы проследить, как значения изменяемых ячеек влияют на другие значения модели. Для каждого сценария можно определить до 32 изменяемых ячеек.
Чтобы создать сценарий следует:
1. В меню Сервисвыбрать команду Сценарии (рис. 19).
2. Щелкнуть по кнопке Добавить. Откроется окно Добавление сценария (рис. 20).
Рисунок 19. Диспетчер сценариев
3. В поле Название сценария ввести имя сценария.
4. В поле Изменяемые ячейки ввести ссылки на изменяемые ячейки. Несколько ссылок отделяются друг от друга точками с запятыми. Ссылки можно ввести с клавиатуры или выделить их на рабочем листе. Несмежные ячейки добавляются при нажатой клавише <Ctrl>.
Рисунок 20. Диалоговое окно Добавление сценария
5. Щелкнуть по кнопке ОК.
6. В открывшемся диалоговом окне Значения ячеек сценария ввести значения каждой изменяемой ячейки (рис. 21).
7. Для создания других сценариев щелкнуть по кнопке Добавить (откроется диалоговое окно Добавление сценария) и повторить пункты 3 — 6.
Для завершения работы с Диспетчером сценариев щелкнуть по кнопке ОК, а затем — по кнопке Закрыть.
Рисунок 21. Диалоговое окно Значения ячеек сценария
Рекомендуется сохранить в качестве сценария первоначальные значения изменяемых ячеек, чтобы потом можно было быстро восстановить эти значения.
Для просмотра сценария нужно:
1. В меню Сервис выбрать команду Сценарии.
2. В поле Сценарии выделить имя сценария, который необходимо просмотреть.
3. Щелкнуть по кнопке Вывести.
Вместо пунктов 2 и 3 можно дважды щелкнуть по имени нужного сценария.
Чтобы отредактировать сценарий, надо:
1. В меню Сервис выбрать команду Сценарии.
2. В поле Сценарии выделить имя сценария, который необходимо отредактировать.
3. Щелкнуть по кнопке Изменить.
4. Внести необходимые изменения: можно изменить имя сценария, изменяемые ячейки, значения изменяемых ячеек.
5. Для завершения работы с Диспетчером сценариев щелкнуть по кнопке ОК, а затем — по кнопке Закрыть.
Для создания итогового отчета по сценариям следует:
1. В меню Сервисвыбрать команду Сценарии.
2. Щелкнуть по кнопке Отчет.
3. Выбрать тип отчета: Структура или Сводная таблица.
В отчете типа Структура перечислены все сценарии с определенными для них значениями ячеек. Этот тип отчета полезен тогда, когда каждый пользователь определяет сценарий со своими данными.
Отчет типа Сводная таблица предоставляет возможность эмпирического анализа сценариев. Этот тип отчета полезен тогда, когда сценарий имеет несколько наборов значений изменяющихся ячеек, заданных различными пользователями; с помощью сводных таблиц можно выполнить анализ для разных комбинаций сценариев.
4. В поле Ячейки результата ввести ссылки на ячейки, значения которых надо представить в отчете. В качестве разделителя ссылок используется запятая. Ссылки можно ввести с клавиатуры или выделить их на рабочем листе. Несмежные ячейки добавляются при нажатой клавише <Ctrl>. Итоговые отчеты создаются на отдельных листах.