Как сделать сценарий в excel

Обновлено: 03.07.2024

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

Варьируется до 32 входных наборов.
Объединение сценариев из нескольких различных рабочих листов или рабочих книг.

Сценарии

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

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

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

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

Это включает в себя информацию из всех сценариев.
Это позволяет сравнивать сценарии бок о бок.

Менеджер сценариев

Чтобы создать отчет анализа с помощью Scenario Manager, вам необходимо выполнить следующие шаги:

Шаг 1 — Определите набор начальных значений и определите входные ячейки, которые вы хотите изменить, называемые изменяющимися ячейками.

Шаг 2 — Создайте каждый сценарий, назовите сценарий и введите значение для каждой изменяющейся входной ячейки для этого сценария.

Шаг 3 — Выберите выходные ячейки, называемые ячейками результата, которые вы хотите отслеживать. Эти ячейки содержат формулы в начальном наборе значений. В формулах используются изменяющиеся входные ячейки.

Менеджер сценариев создает отчет, содержащий входные и выходные значения для каждого сценария.

Начальные значения для сценариев

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

Шаги для установки начальных значений для Сценариев:

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

Рассмотрим предыдущий пример кредита. Теперь действуйте следующим образом —

Определите ячейку для суммы кредита.

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

Назовите ячейку Loan_Amount.

Укажите значение как 5 000 000.

Определите ячейки для процентной ставки, количества платежей и типа (платеж в начале или конце месяца).

Эти входные значения будут меняться в зависимости от сценария.

Назовите ячейки Interest_Rate, NPER и Type.

Укажите начальные значения для анализа в этих ячейках как 12%, 360 и 0 соответственно.

Определите ячейку для EMI.

Это значение результата.

Назовите ячейку EMI.

Поместите формулу в эту ячейку как —

= PMT (Interest_Rate / 12, NPER, Loan_Amount, 0, Type)

Определите ячейку для суммы кредита.

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

Назовите ячейку Loan_Amount.

Укажите значение как 5 000 000.

Определите ячейки для процентной ставки, количества платежей и типа (платеж в начале или конце месяца).

Эти входные значения будут меняться в зависимости от сценария.

Назовите ячейки Interest_Rate, NPER и Type.

Укажите начальные значения для анализа в этих ячейках как 12%, 360 и 0 соответственно.

Определите ячейку для EMI.

Это значение результата.

Назовите ячейку EMI.

Поместите формулу в эту ячейку как —

= PMT (Interest_Rate / 12, NPER, Loan_Amount, 0, Type)

Ваш рабочий лист выглядит так, как показано ниже —

Как видите, входные ячейки и результирующие ячейки находятся в столбце C с именами, указанными в столбце D.

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

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

«Сценарии не определены. Выберите Добавить в. ”

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

Создайте первый сценарий с начальными значениями следующим образом:

Имя диалогового окна изменится на Редактировать сценарий.

Отредактируйте текст в поле Комментарий как — Начальные значения .

Выберите опцию Запретить изменения в разделе Защита и нажмите кнопку ОК.

Отредактируйте текст в поле Комментарий как — Начальные значения .

Выберите опцию Запретить изменения в разделе Защита и нажмите кнопку ОК.

Сценарий 1 с начальными значениями создан.

Создайте еще три сценария с различными значениями в изменяющихся ячейках следующим образом:

В поле Имя сценария введите Сценарий 2.

Отредактируйте текст в комментарии как — Другая процентная ставка.

В поле Имя сценария введите Сценарий 2.

Отредактируйте текст в комментарии как — Другая процентная ставка.

На самом деле технология сценария - отличный инструмент Excel. В русской версии он называется Диспетчер сценариев, в английской версии - Scenario. Смысл технологии состоит в создании прогнозов на основе созданной модели. В модели должны быть ячейки, в которых находятся формулы, и ячейки в которых формул нет. Ячейки в модели между собой связаны этими формулами.

Перед тем, как запустить технологию, даем имя каждой ячейки с данными. Дело в том, что если мы этого не сделаем, то в отчете у нас будут классические имена ячеек - А1,А2 и т.д., и отчет при этом будет не читаемый. Чтобы дать имена ячейкам используем команду в русской версии Формулы- Присвоить имя, в английской версии - Formulas -Define name. Имена даем каждой ячейке, где находится цифра. Именем будет название показателя.

После этого можем запускать технологию сценария.

В русской версии это будет Где искать сценарий в русской версии, в английской:

После этих действий появляется окно технологии сценария. В этом окне жмем кнопку Добавить (ADD),чтобы добавить новый сценарий. В следующем окне нам нужно будет внести в поле Название сценария (Scenario Name) внести название первого сценария, а в поле Изменяемые ячейки (Changin Cell) вносим адреса ячеек из нашей модели, в которых нет формул. После этого жмем ОК. И у нас на экране появляется окно, в котором мы вносим изменения для наших ячеек без форму окно внесения изменений в сценарий. Если хотим добавить еще сценарий, то жмем кнопку Добавить (ADD), если это наш единственный сценарий, жмем кнопку OK. После нажатия ОК на экране появится окно диспетчера сценариев, в котором нам нужно выбрать кнопку для создания отчета - В русской версии это кнопка ОТЧЕТ, в английской - Summary. После этого на экране появится маленькое окно под названием ОТЧЕТ ПО СЦЕНАРИЮ (SCENARIO SUMMARY), в которое нам нужно внести адреса ячеек с формулами. После этого нажимаем ОК. На экране появляется отчет по результатам наших сценариев

Анализ "Что Если" в Excel позволяет попробовать различные значения (сценарии) для формул.
Следующий пример поможет Вам освоить Анализ "что если" быстро и легко.

Предположим, у вас есть книжный магазин и есть 100 книг на продажу. Вы продаете определенный % книг по самой высокой цене в $ 50 и определенный % книг по более низкой цене $ 20.

Если вы продаете 60% книг по самой высокой цене, ячейка D10 вычисляет общую прибыль в размере 60 * $ 50 + 40 * $ 20 = $ 3800.

Создание различных сценариев

Что будет, если Вы продадите 70% книг по высокой цене? А что будет, если Вы продадите 80% книг? Или 90%, или 100%? Каждый другой процент продажи книг - это различный сценарий.
Вы можете использовать "Диспетчер сценариев" для создания этих сценариев.

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

1. На вкладке Данные выберите Анализ "что если" и выберите Диспетчер сценариев из списка.
Откроется диалоговое окно Диспетчер сценариев.

2. Добавьте сценарий, нажав на кнопку Добавить.

3. Введите имя (60% книг по высокой цене), выберите ячейку C4 (% книг, которые продаются по высокой цене) для изменяемой ячейки и нажмите на кнопку OK.

4. Введите соответствующее значение 0,6 и нажмите на кнопку OK еще раз.

5. Далее, добавьте еще 4 других сценария (70%, 80%, 90% и 100% соответсвенно).

И, наконец, ваш Диспетчер сценариев должен соответствовать картинке ниже:

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

Отчет по сценариям

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

1. Кликните по кнопке "Отчет" в Диспетчере сценариев.

2. Далее, выберите ячейку C10 (итого выручка) в качестве ячейки результата и нажмите ОК.

Вывод: Если вы продаете 70% книг по высокой цене, то Вы получите общую выручку в размере $ 4100, если Вы продаете 80% книг по высокой цене, то Вы получаете общую прибыль в размере $ 4400 и т.д. Вот как легко можно использовать Анализ "что если" в Excel.

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

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

Самый простой вариант – подбор параметра ( Данные –> Работа с данными –> Подбор параметра) . В открывшемся диалоговом окне следует указать :

• Независимую ячейку с начальным значением;

• Зависимую ячейку с формулой от независимой ячейки.

• Требуемое значение зависимой переменной. Результат данного сценария –значение независимой переменной, при котором достигается требуемое значение зависимой переменной.

• Таблица данных

Обычно используют таблицы с одной независимой переменной или с двумя независимыми переменными. Используется 3 подхода – расположение значений независимой переменной в строке или в столбце.

-Расположение в столбце

Шаг 1. Исходное расположение информации. Позиция формулы строго определена относительно диапазона значений. В нашей ситуации С3:С5 – диапазон, D2 – ячейка с формулой (вправо на 1 позицию, вверх на одну позицию).

Ячейка аргумента формулы ( А3 в данном случае) располагается произвольно.

Шаг 2. Указать диапазон : Значения переменной + формула

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

Таблица с двумя входами

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

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

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

Предположим, что создана таблица

Работу со сценарием пока рассмотрим на примере одного проекта - проекта 1.

Обратить внимание, что начальные затраты (0 год) являются отрицательными значениями, а последующие доходы – положительными.

• Произвольный новый лист можно объединить с уже имеющимися сценариями.

• Вызвать сценарии на новом листе:

В ячейках В4;В7 отобразятся значения сохраненного диапазона.

• В произвольные ячейки нового листа можно вставить формулы

=ЧПС(B2;B5;B6;B7) или =СУММ(B4:B7).

Можно менять значения в диапазоне В4;В7, соответствующие ячейки будут изменяться.

• Построим отчет по имеющемуся сценарию

Отчет строится в двух вариантах - структура и сводная таблица. Рассмотрим первый вариант – структура.

Данная структура может быть сгруппирована и занимать на листе мало места.

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

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

Сценарии E xcel могут применяться для прогнозов результатов моделей расчета листа.

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

Сценарии Excel. С помощью диспетчера сценариев возможно:

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

Просмотр результатов работы сценария

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

Читайте также: