Сценарии в excel - IT Справочник
Llscompany.ru

IT Справочник
12 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Сценарии в excel

Сценарии в Excel

Сценарии в Excel

Добрый день, уважаемые читатели и подписчики блога!

Сегодня мы поговорим о такой вещи как сценарии в Excel. Что, зачем и как.

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

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

Для строки сумма с учётом инвестиций воспользуемся функцией БС (будущая стоимость). Разделим процент годовых (8%) на количество периодов, чтобы узнать ежемесячную ставку, укажем количество периодов — 12, сумму ежемесячного пополнения и первоначальную стоимость.

Теперь посчитаем сумму вложенных средств.

Самое интересное — чтобы вручную не делать те же самые действия для всех остальных объектов, создадим сценарий. Переходим на вкладку «Данные», блок кнопок «Работа с данными», далее «Анализ что если».

В выпадающем списке выбираем «Диспетчер сценариев». Нажимаем «Добавить».

В появившемся окне нужно указать:

  • название сценария;
  • изменяемые ячейки;
  • примечание;
  • настройки защиты.

После нажимаем «ОК». В этом коне появятся значения для проекта TON.

Останется нажать «Добавить» и заполнить поля сценария для остальных проектов, получится вот такой список.

Нам останется нажимать кнопку «Вывести», чтобы в диапазон ячеек B9:B12 выводились соответствующие значения. Очень удобно.

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

Когда все данные внесены и заполнены все поля, можно перейти к созданию отчёта. В окне «Диспетчера сценариев» нажимаем кнопку «Отчёт». Предлагается два варианта: сводная таблица и структура. В наш документ будут добавляться соответствующие листы. Нажимаем «ОК», выбрав первый вариант — «Структура».

На новом листе появится скомпонованная таблица с включённой группировкой ячеек, что крайне удобно для анализа.

В сводной таблице не будет ничего нового — можно посмотреть вот эту статью. Таким образом мы проводим первоначальную аналитику по своим проектам. Всем удачи!

Вас может заинтересовать статья — пользовательские форматы.

Сценарии в EXCEL

Сценарии — это инструмент 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) из отдельного диапазона с исходными данными.

Сравним рассмотренный нами подход с работой Диспетчера сценариев.

Для создания сценария сделайте следующее:

  • Вызовите Диспетчер сценариев ( Данные/ Работа с данными/ Анализ «что-если» );

  • Нажмите кнопку Добавить;
  • Введите название сценария и диапазон ячеек, в которые должны подставляться значения исходных данных (см. Файл примера лист Сценарии );
Читать еще:  Панель инструментов стандартная excel

  • Нажмите ОК, откроется еще одно диалоговое окно для ввода данных;

  • Нажмите Добавить, чтобы ввести еще один сценарий или ОК, чтобы вернуться в окно Диспетчера сценариев.

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

Нажмите кнопку Отчет. чтобы вывести на отдельный лист все сценарии и полученные результаты. Результаты могут быть выведены в виде Структуры или Сводной таблицы .

Не забудьте указать ячейки результата (в которые выводятся результаты вышей модели). Для указания несвязных диапазонов используйте клавишу CTRL .

Автоматически созданный отчет в виде Сводной таблицы , конечно, требует доработки.

Основным недостатком инструмента Сценарии является тот факт, что значения исходных данных не находятся на листе, а скрыты в Диспетчере сценариев. Конечно, их можно вывести в отчете, но менять, по-моему, их в нем не очень удобно.

Сценарии в excel

На этом шаге мы рассмотрим диспетчер сценариев.

Анализ данных с помощью таблиц подстановки является весьма эффективным. Однако он имеет несколько недостатков:

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

С помощью средства Диспетчер сценариев можно достаточно просто автоматизировать процесс выполнения анализа «что-если» для различных моделей. С его помощью Вы можете создать несколько наборов данных вводимых значений (в терминологии средства Диспетчер сценариев они называются изменяемыми ячейками) для любого количества переменных и присвоить имя каждому набору. Затем по имени можно выбрать определенный набор данных, и Excel покажет результаты анализа этих данных на рабочем листе. Кроме того, можно создать итоговый отчет по сценариям, в котором будет показан результат подстановки различных комбинаций входных параметров. Итоговый отчет может быть представлен в виде обычного структурированного списка или сводной таблицы.

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

Чтобы Вы смогли получить общее представление о средстве Диспетчер сценариев, начнем с простого примера — производственной модели (рис. 1).

Рис. 1. Пример производственной модели

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

Доступ к средству Диспетчер сценариев можно получить с помощью команды Сервис | Сценарии. При выборе этой команды будет вызвано диалоговое окно Диспетчер сценариев, показанное на рисунке 2.

Рис. 2. Диалоговое окно Диспетчер сценариев

Когда Вы впервые вызываете это диалоговое окно, в нем имеется сообщение, в котором сказано, что ни один сценарий не определен, и это не удивительно, ведь Вы только начали. Как только будут добавлены новые сценарии, их названия появятся в диалоговом окне. Чтобы добавить сценарий, в диалоговом окне Диспетчер сценариев щелкните на кнопке Добавить. Появится диалоговое окно Добавление сценария, которое показано на рисунке 3. Это диалоговое окно разделено на четыре части:

Рис. 3. Диалоговое окно Добавление сценария

  • Название сценария. В этом поле можно указать любое имя сценария, но желательно, чтобы оно что-то обозначало.
  • Изменяемые ячейки. Ячейки, в которых находятся исходные данные для сценария. В это поле можно ввести абсолютный адрес ячейки или ее имя. Разрешается выбирать несколько ячеек, причем все они не обязательно должны быть смежными. В каждом сценарии, которому присвоено имя, можно использовать одни и те же наборы изменяемых ячеек или разные изменяемые ячейки. Количество изменяемых ячеек для одного сценария ограничено числом 32.
  • Примечание. По умолчанию в это поле Excel помещает информацию о том, кто создал сценарий, а также дату его создания. Однако Вы можете отредактировать этот текст, добавить к нему новый текст или вовсе удалить его.
  • Защита. Две опции, которые позволяют защитить сценарий от изменений и скрыть его, можно активизировать только в том случае, если рабочий лист защищен и в диалоговом окне Защитить лист активизирована опция Сценарии. Защита сценария предотвращает модификацию его кем-либо другим, а скрытый сценарий вообще не появляется в диалоговом окне Диспетчер сценариев.

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

Рис. 4. Диалоговое окно Значение ячеек сценария

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

  • Выполнить команду Сервис | Настройка.
  • В диалоговом окне Настройка выбрать вкладку Команды.
  • Выбрать категорию Сервис.
  • Выделить инструмент Сценарий и перетащить его на любую панель инструментов.
  • Щелкнуть на кнопке Закрыть.

На следующем шаге мы рассмотрим отображение, изменение и объединение сценариев.

Сценарии в excel

  • Главная Страница
  • Поиск По Сайту
  • Контакты Обратная связь
  • Главная
  • Информатика
  • Курс занятий на Оператора компьютерного набора
  • Создание сценариев. Отчет по сценарию Excel

Создание сценариев. Отчет по сценарию Excel

Практическая работа №12

Тема занятия: Работа со сценариями.

Цель занятия : Научиться использовать сценарии для нахождения оптимального решения задач имеющих несколько условий.

План занятия:

Создание сценариев. Отчет по сценарию.

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

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

Задача:

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

Решение:

Подготовка таблицы сценариев.

• Создайте новую рабочую книгу.

• Лист1 переименуйте в Покупка автомобиля.

• На листе Покупка автомобиля Создайте таблицу следующего вида:

В ячейку С9 введите формулу =СУММ(C4:C8). Эта формула будет считать общую цену всех комплектующих.

• Формат ячеек столбца Цена — Денежный.

Создание нескольких сценариев

• Выполните Сервис→Сценарии.

• В окне Диспетчер сценариев Нажмите кнопку Добавить.

• В окне Добавление сценария В поле Название сценария Впишите Ланос. В поле Изменяемые ячейки Введите $C$4:$C$8;$C$1. Нажмите ОК.

Примечание: Для указания двух несмежных диапазонов ячеек выделите один диапазон, затем зажмите клавишу CTRL и выделите второй диапазон или одну ячейку.

• Теперь заполним значение изменяемых ячеек. В окне Значение ячеек сценария Вы увидите поля для ввода значений для каждой изменяемой ячейки. Напротив каждого поля можно увидеть адрес ячейки, куда будет подставлено введенное вами значение. Например: Если в поле 1: $C$4 Ввести значение 1000 то это будет означать что в ячейку С4 будет подставлена цена на двигатель равная тысячи ден. едениц.

Задание: Заполните значения изменяемых ячеек так чтобы они выглядели правдоподобно. Ячейка С1 должна содержать значение «Ланос», Так как в нее будет выводиться название автомобиля

• После ввода всех изменяемых ячеек нажмите ОК.

• Добавьте еще три сценария с такими значениями:

Диспетчер сценариев для анализа прогнозной модели

Признаком качественно выполненной прогнозной модели является наличие анализа чувствительности параметров модели. Как результирующий итог модели (например, внутренняя норма доходности – 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 находятся в несмежных диапазонах, их следует перечислить через точку с запятой. Также несколько диапазонов ячеек можно выбрать и внести при нажатой клавише . Установите переключатель Тип отчета в положение структура, и нажмите кнопку OK. В книге Excel будет создан отчет Структура сценария (рис. 7).

Рис. 5. Диспетчер сценариев

Рис. 6. Диалоговое окно Отчет по сценарию для выбора в отчет ячеек с результатами

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

Обратите внимание, что в отчет включен столбец, помеченный как Текущие значения, для изначально указанных на листе значений. В наименее благоприятном сценарии компания несет убытки (в размере 13 346 долларов), в наиболее благоприятном — получает прибыль (в размере 226 893 долларов). Так как в наименее благоприятном сценарии цена ниже переменных затрат, компания теряет деньги каждый год.

Некоторые замечания

При установке в диалоговом окне Отчет по сценарию переключателя в положение сводная таблица результаты по сценариям представляются в формате сводных таблиц (рис. 8). На мой взгляд, такое представление менее интересно.

Рис. 8. Отчет по сценариям в виде сводной таблицы

Если в диалоговом окне Диспетчер сценариев выбрать один из сценариев и нажать кнопку Вывести, на листе с моделью (рис. 9) появятся значения входных ячеек для выбранного сценария, и все формулы будут автоматически пересчитаны для выбранного сценария. Этот инструмент отлично подходит для подготовки презентации. Ctrl+Z отменяет работу сценария, и возвращает лист в исходное состояние.

Рис. 9. На лист с моделью выведены расчет для наиболее благоприятного сценария

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

Как и в любой структуре данных при нажатии на знак «минус» (–) в строках 5 и 9 отчета Структура сценария (см. рис. 7) строки с предполагаемыми значениями скрываются, а отображаются только результаты. При нажатии на знак «плюс» (+) отчет восстанавливается в полном объеме.

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

Ссылка на основную публикацию
ВсеИнструменты 220 Вольт
Adblock
detector