Это третья статья из серии из пяти частей по анализу данных в Excel. В этом разделе я покажу вам, как использовать диспетчер сценариев в Excel.
Скачать файл
Другие статьи из этой серии:
- Одна таблица переменных данных в Excel.
- Таблица двух переменных данных в Excel.
- Поиск цели в Excel.
- Решатель Excel.
Посмотреть видео - Менеджер сценариев в Excel
Диспетчер сценариев в Excel может быть предпочтительным инструментом, когда у вас есть несколько переменных, и вы хотите увидеть влияние на конечный результат изменения этих переменных.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите рассчитать значение прибыли:
Значение прибыли зависит от 3 переменных - количества продаж, цены за единицу и переменной стоимости за единицу. Вот формула, которую я использовал для расчета прибыли:
= В2 * В3-В4-В5 * В2
Идея состоит в том, чтобы увидеть, как изменится этот окончательный результат, когда мы изменим эти зависимые переменные.
Как показано в первых двух статьях этой серии, если у вас изменяются только одна или две переменные, вы можете создать таблицу данных с одной или двумя переменными. Но если у вас есть 3 или несколько переменных, которые могут меняться, тогда вам подойдет диспетчер сценариев.
Настройка диспетчера сценариев в Excel
- Перейдите на вкладку «Данные» -> «Инструменты данных» -> «Анализ« что, если »» -> «Диспетчер сценариев».
- В диалоговом окне «Диспетчер сценариев» нажмите «Добавить».
- В диалоговом окне Добавить сценарий введите следующие данные:
- Название сценария: наихудший случай
- Изменение ячеек: $ B $ 2, $ B $ 3, $ B $ 5 (вы также можете выбрать его, нажав кнопку CONTROL и щелкнув левой кнопкой мыши).
- Комментарий: любой комментарий, который вы хотите добавить. Вы также можете оставить это поле пустым.
- Щелкните ОК. Откроется диалоговое окно «Значения сценария».
- В диалоговом окне «Значения сценария» введите следующие значения (так как это наихудший сценарий, введите значения соответственно). Если вы создаете имена для каждой ячейки, это имя будет отображаться вместо адреса ячейки.:
- $ 2: 50
- $ 3: 30
- 4 доллара США: 30
- Нажмите ОК (нажмите Добавить, если хотите добавить еще один сценарий).
Это создает наихудший сценарий для этого набора данных. Вы можете аналогичным образом выполнить эти шаги и создать несколько сценариев (например, наихудший случай, реалистичный, лучший вариант).
После того, как вы создали все сценарии, вы можете просмотреть результат каждого из сценариев, просто дважды щелкнув любой из сценариев. При двойном щелчке значения будут меняться в зависимости от этого сценария.
Кроме того, вы также можете создать сводку всех сценариев.
Создать сводку всех сценариев
- Нажмите кнопку «Сводка» в диалоговом окне «Диспетчер сценариев».
- В диалоговом окне «Сводка сценария» выберите «Сводка сценария» или «Сводная таблица» (это два способа отображения сводки). Также укажите ячейки результатов (ячейка, в которой у вас есть выходные данные этого вычисления; B6 в этом примере)
- Щелкните ОК. Мгновенно создается новая вкладка со сводкой всех трех сценариев.
Менеджер сценариев в Excel - отличный инструмент, когда вам нужно провести анализ чувствительности. Просто создайте сценарии, и можно мгновенно создать сводку, которая даст вам полный сравнительный обзор.
