Сценарии — это инструмент MS EXCEL из группы Анализ «что-если» (
). Диспетчер сценариев позволяет создавать и подставлять различные значения исходных данных в модель, а также составлять автоматические отчеты, отображающие результаты вычислений.
Рассмотрим инструмент Диспетчер сценариев на конкретном примере.
Задача
Определить, на какой срок можно получить кредит для следующих вариантов:
Необходимо составить Сценарии для следующих вариантов:
размер кредита – $8 000, ежегодн. выпл. – $1 500, годовая проц. ставка – 16%;размер кредита – $12 000, ежегодн. выпл. – $2 000, годовая проц. ставка – 15%;размер кредита – $15 000, ежегодн. выпл. – $3 000, годовая проц. ставка – 14%;размер кредита – $20 000, ежегодн. выпл. – $3 500, годовая проц. ставка – 13%;размер кредита – $25 000, ежегодн. выпл. – $4 000, годовая проц. ставка – 12%.
Необходимо также создать итоговый отчет по сценариям в виде структуры на отдельном рабочем листе.
Решение
Создадим простую модель для расчета количества периодов выплаты.
Наша «Модель» состоит из одной формулы
=КПЕР(B17;B18;B16)
и 3-х значений аргументов (исходных данных).
Всего необходимо рассчитать модель для 5-и различных сценариев. В каждом сценарии задаются различные значения суммы Кредита, Ставки и суммы Ежегодных выплат.
Сначала рассмотрим создание сценариев без использования Диспетчера сценариев.
Недостатком этого подхода является, то что нам пришлось создать 5 одинаковых моделей. В случае более сложных моделей, такой подход является очень затратным (время создания копий модели и дополнительные вычислительные ресурсы для одновременного расчета 5-и моделей), а также не гибким: при изменении модели ее придется переделывать в нескольких экземплярах (переписывать функции 5 раз).
Но, не будем сдаваться сразу — усовершенствуем ввод наших исходных данных в модель. Будем выбирать нужный сценарий с помощью
Выпадающего списка
(см.
Файл примера лист Без сценариев2
).
Теперь мы избавились от лишних копий модели. Исходные данные вводятся в модель не непосредственно в предназначенные для этого ячейки, а выбираются с помощью формулы
=СМЕЩ(D16;;ПОИСКПОЗ($B$15;$D$15:$H$15;0)-1)
из отдельного диапазона с исходными данными.
Сравним рассмотренный нами подход с работой Диспетчера сценариев.
Для создания сценария сделайте следующее:
-
Вызовите Диспетчер сценариев
();
- Нажмите кнопку Добавить;
-
Введите название сценария и диапазон ячеек, в которые должны подставляться значения исходных данных (см.
Файл примера лист Сценарии
);
- Нажмите ОК, откроется еще одно диалоговое окно для ввода данных;
- Нажмите Добавить, чтобы ввести еще один сценарий или ОК, чтобы вернуться в окно Диспетчера сценариев.
Если нажать кнопку Вывести, то в указанные ячейки будут вставлены данные из выбранного сценария (окно при этом не закроется).
Нажмите кнопку Отчет…, чтобы вывести на отдельный лист все сценарии и полученные результаты. Результаты могут быть выведены в виде Структуры или
Сводной таблицы
.
Не забудьте указать ячейки результата (в которые выводятся результаты вышей модели). Для указания несвязных диапазонов
используйте клавишу
CTRL
.
Автоматически созданный отчет в виде
Сводной таблицы
, конечно, требует доработки.
Основным недостатком инструмента Сценарии является тот факт, что значения исходных данных не находятся на листе, а скрыты в Диспетчере сценариев. Конечно, их можно вывести в отчете, но менять, по-моему, их в нем не очень удобно.
При работе в Excel часто приходится сталкиваться с большими и сложными отчетами, которые содержат много длинных формул с разными функциями в аргументах. К тому же формулы могут быть трехмерными и могут ссылаться на другие листы или даже книги.
Создание сценариев в Excel
Например, при составлении бюджета предприятия нужно учитывать множество показателей. Но чтобы проверить эффективность бюджета следует его проанализировать при различных условиях. Ведь в процессе бюджетирования нас интересует, на сколько будет чувствительна эффективность выделенных финансовых средств при следующих сценариях развития событий влияющих на предприятие:
- банки повысят процентные ставки по кредитам;
- существенно измениться уровень инфляции;
- поднимут налоги и т.п.
Тестировать план бюджета в оригинальном файле крайне не рекомендуется. Создавая новые копии документов для теста можно нарушить адресации во множестве трехмерных ссылок формул и функций. Наиболее рациональное решения для данной ситуации – это использование сценариев Excel.
Пример сценариев в Excel
Для примера применения сценариев в практике, будем использовать простые задачи. Допустим нам нужно накопить 13 800$ за 10 лет на банковском депозите с определенной процентной ставкой. Нам нужно узнать какой будем делать ежегодный взнос на депозит. И какая процентная ставка нас устроит для накопления денежных средств.
- Составьте таблицу так как указано на рисунке:
- Выделите диапазон ячеек B1:B2 и выберите инструмент: «Данные»-«Работа с данными»-«Анализ что если»-«Диспетчер сценариев».
- В диспетчере нажмите на кнопку «Добавить».
- В окне «Добавление сценария» укажите имя «Макс.ставка%» и ссылку на диапазон изменяемых ячеек. И нажмите ОК.
- Появится окно «Значения ячеек сценария», в нем введите новое значение 7% для ячейки B1, а в B2 не изменяйте как выше указано на рисунке. И нажмите ОК.
- Повторите выше указанные пункты с 3 по 5. Только на этот раз в 4-ом пункте укажите имя «Макс.взнос»; в 5-том пункте укажите новое значение взноса -1100 для ячейки B2, а B1 оставьте без изменений как ниже на рисунке:
- Теперь в диспетчере сценариев нажмите на кнопку отчет.
- Ничего не меняя жмем ОК.
Готово!!!
Теперь мы получили отчет, по которому можно сравнивать как будут развиваться события при различных возможных сценариях, чтобы заранее предвидеть результат при разных экономических условиях.
-
Диспетчер Сценариев
Наборы
входных значений, порождающих различные
результаты, можно создавать и хранить
как сценарии.
Сценарий
— это группа входных значений, называемых
изменяемыми ячейками, которая сохраняется
под заданным именем.
Сценарий
— именованные комбинации значений,
заданных для одной или нескольких
изменяемых значений в модели «что
если»
Модель
«что если»
— это любой рабочий лист, в котором можно
подставлять различные значения для
переменных, чтобы выяснить их влияние
на другие величины. Каждый набор
изменяемых значений представляет
множество «что если» предположений,
применяемых к модели рабочей книги для
просмотра результата воздействия на
другие части модели.
Можно
определить до 32 изменяемых ячеек на
сценарий.
Таблицы
данных
— хорошее средство в относительно простых
ситуациях, при использовании одной или
двух переменных. При моделировании
сложных задач, использующих до 32
переменных, используется Диспетчер
Сценариев
Использование
сценариев для анализа нескольких
различных переменных
Сценарий —
это набор значений, которые сохраняются
в приложении Excel и могут автоматически
подставляться в ячейки листа. Можно
создать и сохранить различные группы
значений на листе и затем переключаться
на любые их этих новых сценариев для
просмотра различных результатов.
Предположим,
что существуют два сценария бюджетирования —
для наихудшего и наилучшего случаев. С
помощью диспетчера сценариев можно
создать оба сценария на одном листе и
переключаться между ними. Для каждого
сценария можно указать изменяемые
ячейки и используемые значения. При
переключении между сценариями значение
в ячейке результата будет изменяться,
отражая различные значения изменяющихся
ячеек.
Сценарий
наихудшего случая
Изменяемые
ячейки
Ячейка
результата
Сценарий
наилучшего случая
Изменяемые
ячейки
Ячейка
результата
Если
несколько пользователей хранят
определенные сведения в отдельных
книгах, которые необходимо использовать
в сценариях, можно собрать эти книги и
объединить их сценарии.
После
создания или сбора всех необходимых
сценариев можно создать итоговый отчет
по сценариям, включающий сведения из
них. В отчете по сценариям все сведения
сценариев отображаются в одной таблице
на новом листе.
Сводный
отчет по сценариям
Примечание. Перерасчет
отчетов по сценариям автоматически не
выполняется. При изменении значений
сценария эти изменения не отображаются
в существующем отчете по сценариям.
Вместо этого необходимо создать новый
отчет по сценариям.
Совет.
-
Перед
использованием Диспетчера сценариев
присвоить имена изменяемым ячейкам и
ячейкам с формулами, зависящими от
изменяемых ячеек. -
Перед
изменением значений первому сценарию
надо присвоить имя (например, Начальные
значения), иначе стартовые данные будут
потеряны.
Создание
сценария
-
Выполнить
команду Сервис-Сценарии
и кнопкаДобавить. -
Ввести
имя сценария в поле
Название сценария. -
В
поле
Изменяемые ячейки
указать изменяемые ячейки и ОК. -
В
каждом поле окна Значения
сценария
указать либо константу, либо формулу
(например, умножить текущую величину
на коэффициент). -
Для
создания другого сценария — кнопка
Добавить.
Для возврата в окно Диспетчерсценариев
— кнопка ОК.
Для возврата на рабочий лист —
кнопкаЗакрыть.
Просмотр
сценариев
При
нажатии кнопкиВывести
диспетчер сценариев заменит значения
переменных на рабочем листе, значениями
выбранного сценария
Правка
и удаление сценариев
Если
после внесения изменений в сценарий он
будет сохранен с первоначальным именем,
то новые значения изменяемых ячеек
заменят значения в исходном сценарии.
-
Выполнить
команду Сервис-Сценарии. -
Указать
имя изменяемого или удаляемого сценария
и нажать кнопку
Изменить
или Удалить. -
Исправить
необходимое и ввести значения для
изменяемых ячеек в диалоге Значения
ячеек сценария. -
Для
сохранения изменений нажать OK,
для возврата в диалог Диспетчер
сценариев,
не изменяя текущий сценарий, нажать
Отмена.
Создание
итогового отчета по сценариям
-
На
вкладке Данные
в группе Работа
с данными
выберите команду Диспетчер
сценариев. -
Установить
переключатель
в положение Структура
или Сводная
таблица -
В
поле
Ячейки
результата ввести ссылки на ячейки,
значения которых были изменены с помощью
сценариев. В качестве разделителя
ссылок используется запятая.
Не
обязательно указывать ячейки результата
в итоговый отчет, но они потребуются в
отчете со сводной таблицей
Отчет
Структура сценария
В
отчете приводятся имена изменяемых и
результирующих ячеек. Имена сценариев
— заголовки столбцов. Значения изменяемых
ячеек выделены серым цветом.
Отчет
Сводная таблица
Этот
инструмент анализа используется для
сложных моделей с множеством изменяемых
ячеек, которые создаются разными
пользователями.
Объединение
сценариев
Сценарии
объединять проще, если все модели что
если в книгах идентичны
-
Открыть
все книги, содержащие объединяемые
сценарии -
Выполнить
команду Сервис-Сценарии
и нажать кнопкуОбъединить -
Из
списка Книга
выбрать название книги и в поле Лист
указать названия листов, содержащих
объединяемые сценарии.
Все
изменяемые ячейки выбранных листов
должны ссылаться на соответствующие
изменяемые ячейки активного листа.
Выполняется копирование всех сценариев
выбранных листов на активный лист.
Пример
структуры сценария (рис. 56)
Рисунок
56. Структура сценария
Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]
- #
- #
- #
- #
- #
- #
- #
- #
- #
- #
- #
Признаком качественно выполненной прогнозной модели является наличие анализа чувствительности параметров модели. Как результирующий итог модели (например, внутренняя норма доходности – 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) строки с предполагаемыми значениями скрываются, а отображаются только результаты. При нажатии на знак «плюс» (+) отчет восстанавливается в полном объеме.
Предположим, что вы отправили файл нескольким сотрудникам, и каждый из них добавил собственный сценарий. После того как каждый сотрудник вернет файл со сценариями, можно объединить все сценарии в одной (например, исходной) книге. Откройте версию книги каждого сотрудника, нажмите в исходной книге в диалоговом окне Диспетчер сценариев кнопку Объединить и затем выберите книги, содержащие сценарии, которые требуется объединить. Выбранные сценарии будут автоматически объединены в исходной книге.
[1] Заметка написана на основе материалов из книги Уэйн Л. Винстон. Microsoft Excel 2013. Анализ данных и бизнес-моделирование, глава 18.
8 апреля 2022 г.
Microsoft Excel позволяет вам управлять, анализировать и сравнивать данные, чтобы помочь вам продвинуться по карьерной лестнице. Некоторые функции Excel, такие как менеджер сценариев, могут позволить вам принимать важные бизнес-решения и прогнозы на основе различных ситуаций. Понимание того, как использовать эту функцию, может помочь вам продвинуться по карьерной лестнице в бизнесе по мере развития навыков анализа данных. В этой статье мы определяем, что такое диспетчер сценариев в Excel, и даем инструкции по эффективному использованию этой функции.
Менеджер сценариев Excel представляет собой набор цифровых инструментов, которые позволяют пользователю создавать, анализировать и сравнивать результаты данных в различных бизнес-ситуациях. Вы можете хранить несколько версий данных в одной ячейке, а затем изменять их в зависимости от цели сценария. Если у вас есть несколько потенциальных наборов данных, эта функция может помочь определить окончательное значение метрики. Например, компания может создать бюджет на основе сценария с высоким или низким доходом, а затем сравнить результаты, чтобы понять, как прогнозируемый доход может повлиять на планы бюджета.
Некоторые другие функции диспетчера сценариев включают сохранение различных групп значений, объединение различных сценариев и создание сводок по каждой ситуации. Использование этого инструмента может помочь бизнесу понять и предсказать важную информацию о процессе принятия решений. Это также может помочь заинтересованным сторонам эффективно сотрудничать в проекте, основанном на данных.
Как использовать диспетчер сценариев в Excel
Вот шесть шагов, которые вы можете предпринять, чтобы перемещаться по диспетчеру сценариев:
1. Найдите диспетчер сценариев
Чтобы начать использовать диспетчер сценариев, найдите его в Excel. В верхней части экрана вы можете выбрать «Данные». Оттуда найдите значок «Что, если анализ» и щелкните раскрывающееся меню рядом с ним. В меню выберите опцию «Менеджер сценариев».
2. Введите данные в электронную таблицу
Чтобы превратить набор значений в сценарий, введите свои данные в электронную таблицу. Сценарий может иметь не более 32 меняющихся ячеек, поэтому часто бывает полезно подготовить эту информацию перед началом этого проекта. Вы можете начать с ввода меняющихся ячеек, которые представляют значения, составляющие сценарий. Например, если вы создаете сценарий для описания потенциальных затрат на бизнес-операции, вы можете ввести такие значения, как трудозатраты или материальные затраты.
После того, как вы ввели эти изменяющиеся ячейки, вы можете добавить формулу, отражающую, как изменяющиеся ячейки соотносятся друг с другом. Чтобы ввести формулу, выберите ячейку, в которую вы хотите вставить свои вычисления, и введите знак равенства. Затем вы можете добавить остальную часть формулы. Например, если вы хотите использовать формулу, которая складывает значения затрат на рабочую силу в одной ячейке с пометкой A1 и стоимость материала в другой ячейке с пометкой A2, вы можете ввести «=A1+A2». После этого вы можете нажать «Enter», чтобы применить формулу.
3. Создайте сценарий
Выберите опцию «Добавить» в диспетчере сценариев. В некоторых версиях Excel этот параметр может выглядеть как знак «плюс». Когда откроется экран «Добавить», вы можете ввести имя своего сценария в разделе «Имя сценария». Придумайте описательное имя, которое вы сможете легко распознать при анализе каждого набора значений. Например, если вы хотите создать сценарий для высоких эксплуатационных расходов, вы можете назвать свой сценарий «Сценарий наихудшего случая» или «Максимально возможные эксплуатационные расходы».
После того, как вы назвали свой сценарий, вы можете ввести имена изменяемых ячеек в разделе «Изменение ячеек», разделенные запятой. В зависимости от предпочтений компании вы также можете выбрать «Предотвратить изменения», чтобы защититься от редактирования сценария. Сделав это, нажмите кнопку «ОК», чтобы сгенерировать сценарий.
4. Добавьте еще один сценарий
Создав один сценарий, вы можете выполнить аналогичные шаги для создания другого сценария. Для начала вы можете добавить новые значения в изменяющиеся ячейки из предыдущего. Например, вы можете заменить самые высокие эксплуатационные расходы на самые низкие. Поскольку ячейка формулы останется неизменной для каждого набора значений, ее можно не редактировать.
Оттуда вы можете открыть диспетчер сценариев, добавить и назвать новый сценарий. Например, вы можете назвать сценарий с низкими эксплуатационными расходами, используя более описательный язык, например «Благоприятные результаты с низкими затратами». Вы также можете добавить любые комментарии, которые могут добавить контекст. После того как вы создали этот новый сценарий, он обычно появляется в функции менеджера вместе с любыми предыдущими дополнениями.
5. Слияние сценариев
С помощью диспетчера сценариев вы можете объединять несколько сценариев из разных источников для сравнения и анализа данных. Например, компания может решить сравнить данные об операционных расходах из различных отделов бизнеса. Сбор информации о наилучших и наихудших сценариях операционных расходов для каждого отдела может помочь организации лучше подготовиться к будущим результатам бизнеса.
Для начала соберите все соответствующие электронные таблицы, содержащие сценарии. Убедитесь, что все участники написали сценарии в одном и том же формате ячеек, чтобы упростить сравнение данных. Например, если один отдел вводит стоимость рабочей силы в ячейку A1, может быть полезно, если другой отдел поместит свои затраты в ту же ячейку. После. вы можете открыть диспетчер сценариев и выбрать опцию «Объединить». Когда появится список возможных сценариев, выберите те, которые вы хотите объединить, и нажмите кнопку «ОК». Это действие помещает все сценарии на один рабочий лист.
6. Создайте сводный отчет по сценарию
Вы также можете создать сводный отчет по сценариям для параллельного сравнения результатов нескольких сценариев. Это может создать четкое представление о потенциальных результатах каждого сценария. Сводная информация может помочь организации визуализировать влияние различных значений или переменных.
Для этого откройте менеджер сценариев и выберите «Сводка». Оттуда вы можете выбрать «Сводную таблицу сценария» или «Сводную таблицу», которая позволяет размещать большие наборы данных в строках или столбцах одной и той же ячейки. Следующим шагом является проверка того, что раздел «Ячейки результатов» содержит ячейки формулы из вашего сценария, и нажмите «ОК», чтобы создать отчет.
Обратите внимание, что ни одна из компаний, упомянутых в этой статье, не связана с компанией Indeed.
Scenario Manager — это инструмент анализа «что, если», доступный в Excel, который работает с разными сценариями, предоставленными ему, он использует группу диапазонов, которые влияют на определенный результат, и может использоваться для создания различных сценариев, таких как хорошие и средние, в зависимости от значения, присутствующие в диапазоне, которые влияют на результат.
- Диспетчер сценариев в Excel является частью трех встроенных в Excel инструментов анализа «что если». Проще говоря, вы можете увидеть влияние изменения входных значений без изменения фактических данных. Как и в случае с таблицей данных в Excel, теперь вы вводите значения, которые должны измениться для достижения определенной цели.
- Диспетчер сценариев в Excel позволяет изменять или заменять входные значения для нескольких ячеек (максимум до 32). Таким образом, вы можете одновременно просматривать результаты различных входных значений или разных сценариев.
- Например: Что, если я сократю ежемесячные командировочные расходы? Сколько я сэкономлю? Здесь можно сохранять сценарии, чтобы их можно было применять одним щелчком мыши.
Как использовать инструмент анализа диспетчера сценариев в Excel?
Scenario Manager очень прост и удобен в использовании в Excel. Давайте разберемся с работой инструмента Scenario Manager в Excel на некоторых примерах.
Вы можете скачать этот шаблон Excel диспетчера сценариев здесь — шаблон Excel диспетчера сценариев
Менеджер сценариев в Excel — Пример №1
Простым примером может быть ваш ежемесячный семейный бюджет. Вы потратите на еду, путешествия, развлечения, одежду и т. Д.… И посмотрите, как это повлияет на ваш общий бюджет.
Шаг 1: В таблице ниже представлен список ваших расходов и источников дохода.
- В камере B5, у вас есть общий доход.
- В камере B17, у вас есть общие расходы за месяц.
- В камере B19, всего осталось денег.
После всех расходов у вас останется только 5 550. Итак, вам нужно сократить расходы, чтобы сэкономить больше на будущее …
Шаг 2: В верхней части Excel щелкните меню Данные > В меню «Данные» найдите панель «Инструменты для работы с данными». > Щелкните элемент «Что если-Анализ» и выберите в меню «Диспетчер сценариев» в Excel.
Шаг 3: Когда вы нажимаете на Менеджер сценария ниже откроется диалоговое окно.
Шаг 4: Вам нужно создать новый сценарий. Так что нажмите на Добавить кнопка. После этого вы получите диалоговое окно, показанное ниже.
По умолчанию отображается ячейка C10, что означает, что это текущая активная ячейка. Сначала введите имя сценария в поле как Фактический бюджет.
Теперь вам нужно указать, в каких ячейках ваш лист Excel будет изменяться. В этом первом сценарии ничего не изменится, потому что это мой фактический бюджет на месяц. Тем не менее, нам нужно указать, что ячейки будут изменяться.
Теперь попробуйте сократить свои расходы на еду и одежду. Это в камерах B15 и B13, соответственно. Теперь ваше диалоговое окно добавления сценария должно выглядеть так.
Нажмите, ОК, и Excel запросит у вас некоторые значения. Поскольку мы не хотим никаких изменений в этом сценарии, просто нажмите ОК.
Теперь вы вернетесь в окно диспетчера сценариев. Теперь окно будет выглядеть так.
Теперь один сценарий готов и вычищен. Создайте второй сценарий, в котором вам нужно внести изменения в свои расходы на еду и одежду.
Щелкните значок Добавить кнопку еще раз и дайте сценарию имя как «План 2». Замена ячейки будет B15 и B13 (расходы на еду и одежду).
Теперь ниже снова открывается диалоговое окно «Значения сценария». На этот раз мы действительно хотим изменить значения. Введите такие же, как на изображении ниже:
Это новые ценности для нашего нового сценария, План 2. Нажмите OK, и теперь вы вернетесь в окно диспетчера сценариев. Теперь у нас уже есть два сценария, названные в честь Фактический бюджет и план 2.
Щелкните значок Добавить кнопку еще раз и дайте сценарию имя как «План 3». Замена ячейки будет B15 и B13 (расходы на еду и одежду).
Теперь ниже снова открывается диалоговое окно «Значения сценария». На этот раз мы действительно хотим изменить значения. Введите такие же, как на изображении ниже:
Это новые ценности для нашего нового сценария, План 3. Нажмите ОК, и теперь вы вернетесь в окно диспетчера сценариев. Теперь у вас есть три сценария, названные в честь Фактический бюджет, план 2 и план 3.
Как видите, у нас есть фактический бюджет, план 1 и план 2. Выбрав план 2, нажмите кнопку «Показать» внизу. Значения в вашем листе Excel изменятся, и будет рассчитан новый бюджет. На изображении ниже показано, как это выглядит.
Нажми на Фактический бюджет, затем нажмите на шоу кнопку, чтобы увидеть различия. Будут отображены начальные значения.
Сделайте то же самое для Плана 2, чтобы увидеть изменения.
Таким образом, диспетчер сценариев в Excel позволяет вам устанавливать различные значения и позволяет определять на их основе существенные изменения.
Как создать сводный отчет в Excel?
После того, как мы закончим добавление различных сценариев, мы можем создать сводный отчет в Excel из этого менеджера сценариев в Excel. Чтобы создать сводный отчет в Excel, выполните следующие действия.
- Нажми на Данные вкладка в строке меню Excel.
- Нажмите на Что-если-анализ.
- Под анализом «что, если» нажмите Менеджер сценариев в Excel.
- Теперь нажмите на Резюме.
- Нажмите ОК, чтобы создать сводный отчет в Excel.
- Резюме будет создано на новом листе, как показано на рисунке ниже.
- Он показывает изменение экономии в трех различных сценариях. В первом сценарии экономия составила 5 550 единиц. Во втором сценарии экономия увеличивается до 20 550 за счет сокращения расходов в разделе «Продукты питания и одежда», и, наконец, третий сценарий показывает другой сценарий.
- Хорошо, теперь мы использовали простой планировщик семейного бюджета. Выглядит достаточно хорошо, чтобы понять. Возможно, этого достаточно, чтобы убедить вашу семью изменить свой образ жизни.
- Менеджер сценариев в Excel — отличный инструмент, когда вам нужно провести анализ чувствительности. Вы можете мгновенно создать сводный отчет в Excel, чтобы сравнить один план с другим и выбрать лучший альтернативный план для получения лучшего результата.
Диспетчер сценариев в Excel. Пример №2. Возьмите указанные ниже данные и создайте новые сценарии.
Возьмите приведенную ниже таблицу данных и создайте новые сценарии.
- «Если эксплуатационные расходы снизятся на 10%»
- «Если эксплуатационные расходы снизятся на 15%»
- «Если цена за единицу увеличится на 5, а все остальные останутся прежними».
Формула, используемая в ячейке B4 = B2 * B3 & в камере B11 = B4 — B9
Кроме того, ваши сценарии будут выглядеть так, как показано ниже.
УЗНАТЬ БОЛЬШЕ >>
Post Views: 1 143
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Еще…Меньше
Автоматизируйте повторяющиеся задачи с помощью сценариев Office в Excel для Интернета, Windows и Mac. Создавайте скрипты и воспроизводите их при желании. Делитесь своими сценариями в организации, чтобы помочь другим пользователям сделать свои рабочие процессы быстрыми и согласованными. Измените скрипты по мере изменения рабочего процесса и разрешите облачному обновлению решений по всей организации.
Создание скрипта Office
Создать новый сценарий Office можно двумя способами.
-
Запишите свои действия с помощью средства записи действий (только Excel в Интернете). Это здорово, когда вы выполняете согласованные действия, выполняемые с книгами. Для записи и совместного использования скриптов Office не требуется знание кода. Приступая к записи с помощью записи действий в качестве скриптов Office — служба поддержки Майкрософт.
-
Используйте редактор кода для работы с кодом TypeScript для расширенных сценариев. Чтобы узнать, как начать с средства записи действий и редактировать скрипты в соответствии с вашими потребностями, см. учебник Запись, изменение и создание скриптов Office в Excel в Интернете — Сценарии Office | Microsoft Learn.
Запуск скрипта Office
-
Все скрипты, к которых у вас и вашей книги есть доступ, находятся в разделе Автоматизация > все скрипты. В коллекции скриптов отображаются последние скрипты.
-
Выберите скрипт, который требуется запустить. Он будет отображаться в редакторе кода. Нажмите кнопку Выполнить , чтобы запустить сценарий. Вы увидите краткое уведомление о выполнении скрипта, которое исчезает по завершении скрипта.
-
Дополнительные параметры . Выберите многоточие (…) в правой части области редактора кода , чтобы увидеть контекстное меню. Здесь вы можете:
-
Переименование скрипта
-
Создание копии скрипта
-
Предоставление общего доступа к скрипту
-
Интеграция скрипта с Power Automate с помощью потока создания
-
Удаление скрипта
-
Возможные ошибки
-
Некоторые действия могут быть в порядке при первой записи скрипта, но при повторном его воспроизведении происходит сбой. Например, в предыдущем примере, где мы отформатовали некоторые примеры данных в виде таблицы, наш код завершится ошибкой, если мы попытаемся запустить его в обновленной таблице, так как Excel не позволяет таблицам перекрывать друг друга. На этом этапе редактор кода отображает сообщение об ошибке.
Нажмите кнопку Просмотреть журналы , чтобы отобразить краткое объяснение ошибки в нижней части области редактора кода.
-
Неподдерживаемые функции . Мы постоянно работаем над добавлением поддержки дополнительных функций, но в настоящее время поддерживается не все. В этом случае вы увидите заметку в области Действия записи. Такие действия не добавляются в скрипт и будут игнорироваться.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
Запишите свои действия как скрипты Office
Техническая документация по сценариям Office
Запись, изменение и создание сценариев Office в Excel в Интернете
Устранение неполадок со сценариями Office
Примеры скриптов для сценариев Office в Excel в Интернете
Создание кнопки для запуска сценария Office