Закрыть ... [X]

Сценарии - это инструмент MS EXCEL из группы Анализ "что-если" (Вкладка Данные/ Группа Работа с данными). Диспетчер сценариев позволяет создавать и подставлять различные значения исходных данных в модель, а также составлять автоматические отчеты, отображающие результаты вычислений.

Рассмотрим инструмент Диспетчер сценариев на конкретном примере.

Задача

Определить, на какой срок можно получить кредит для следующих вариантов:

Необходимо составить Сценарии для следующих вариантов:

размер кредита – 000, ежегодн. выпл. – 500, годовая проц. ставка – 16%;
размер кредита – 000, ежегодн. выпл. – 000, годовая проц. ставка – 15%;
размер кредита – 000, ежегодн. выпл. – 000, годовая проц. ставка – 14%;
размер кредита – 000, ежегодн. выпл. – 500, годовая проц. ставка – 13%;
размер кредита – 000, ежегодн. выпл. – 000, годовая проц. ставка – 12%.

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

Решение

Создадим простую модель для расчета количества периодов выплаты.

Наша "Модель" состоит из одной формулы =КПЕР(B17;B18;B16) и 3-х значений аргументов (исходных данных).

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

Сначала рассмотрим создание сценариев без использования Диспетчера сценариев. 

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

Но, не будем сдаваться сразу - усовершенствуем ввод наших исходных данных в модель. Будем выбирать нужный сценарий с помощью Выпадающего списка (см. Файл примера лист Без сценариев2).

Теперь мы избавились от лишних копий модели. Исходные данные вводятся в модель не непосредственно в предназначенные для этого ячейки, а выбираются с помощью формулы =СМЕЩ(D16;;ПОИСКПОЗ($B;$D:$H;0)-1) из отдельного диапазона с исходными данными.

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

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

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

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

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

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

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

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

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

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

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


Источник: http://excel2.ru/articles/scenarii-v-ms-excel


Поделись с друзьями



Рекомендуем посмотреть ещё:



Использование анализа "что если" в Excel на примере - ExcelGuide Поздравление для родителей с юбилеем совместной жизни

Диспетчер сценариев в excel примеры Сценарии Excel. С помощью диспетчера сценариев возможно
Диспетчер сценариев в excel примеры Таблица 1. Три сценария производственной модели Excel
Диспетчер сценариев в excel примеры Сценарии в MS EXCEL - совместимо с Microsoft Excel 2007, Excel 2010
Диспетчер сценариев в excel примеры Сценарный подход к анализу средствами Excel 2010
Диспетчер сценариев в excel примеры Функция «Сценарии» MS Excel Уроки Excel
Диспетчер сценариев в excel примеры Microsoft Excel для Windows в примерах
Диспетчер сценариев в excel примеры Cоздание сценариев в Excel
Диспетчер сценариев в excel примеры Fasting natural health healing raw food - m
Диспетчер сценариев в excel примеры Голосовые открытки и поздравления: песенные смс поздравления
Городской конкурс чтецов Цена Победы К дню рождения поздравления, в прозе поздравления Немного лирики. Я хочу подарить тебе ДОЧКУ! красиво так Объявлен областной конкурс "Молодой ученый - 2017" - Новости Освежитель воздуха Air Wick ароматический спрей Подарок на медную свадьбу. Чем порадовать супругов? Поздравления c днем рождения, стихи в день рождение

ШОКИРУЮЩИЕ НОВОСТИ