2014-02-02
778
Сценарии — одно из интереснейших средств автоматизации Excel, позволяющее оценивать варианты вычислений при том или ином наборе значений в ячейках. Каждый из заданных наборов параметров называется сценарием. В качестве параметров сценария можно вводить только значения. Формулы, если вы вводите их, принимаются редактором сценария, но преобразуются в числовые константы.
Группа задач «Анализ «что-если»
Анализ «что-если» – это анализ влияния изменения значений ячеек на результат формул.
Самый простой вариант – подбор параметра (Данные –> Работа с данными –> Подбор параметра). В открывшемся диалоговом окне следует указать:
• Независимую ячейку с начальным значением;
• Зависимую ячейку с формулой от независимой ячейки.
• Требуемое значение зависимой переменной. Результат данного сценария –значение независимой переменной, при котором достигается требуемое значение зависимой переменной.
• Таблица данных
Обычно используют таблицы с одной независимой переменной или с двумя независимыми переменными. Используется 3 подхода – расположение значений независимой переменной в строке или в столбце.
|
|
|
-Расположение в столбце
Шаг 1. Исходное расположение информации. Позиция формулы строго определена относительно диапазона значений. В нашей ситуации С3:С5 – диапазон, D2 – ячейка с формулой (вправо на 1 позицию, вверх на одну позицию).
Ячейка аргумента формулы (А3 в данном случае) располагается произвольно.
Шаг 2. Указать диапазон: Значения переменной + формула
Вызвать функцию Данные -> Работа с данными -> Анализ «что-если» -> Таблица
Нажать OK
Результат.
Вариант 2 – значения переменной располагаются в строке. Формула должна быть расположена на одну строку ниже, на один столбец левее.
Таблица с двумя входами
• Диспетчер сценариев
Сценарии – это именованный сохраненный набор значений, которые используются для автоматической подстановки в формулы. Существует возможность создать и сохранить в листе различные группы значений, а затем переключаться на любую из этих групп, чтобы просматривать различные результаты.
Для чего можно использовать функцию Сценарии? При решении многих задач приходится сталкиваться с множеством допустимых решений. Какое решение оптимально, хотя бы лучше других? Какой вариант выбрать? Эту проблему помогают решить Сценарии Excel.
Предположим, что создана таблица
Работу со сценарием пока рассмотрим на примере одного проекта - проекта 1.
Обратить внимание, что начальные затраты (0 год) являются отрицательными значениями, а последующие доходы – положительными.
|
|
|
| Для сравнения этих проектов будем использовать показатели Показатель 1 – ЧПС | ||||||||
| Чистый приведенный доход определяется функцией ЧПСиз категории финансовых функций (он показывает величину денежных средств, которую инвестор ожидает получить от проекта после того, как денежные притоки окупят его первоначальные инвестиционные затраты (год 0) и и периодические денежные затраты, связанные с осуществлением проекта). | ||||||||
| Рассчитаем ЧПС для всех трех проектов. | ||||||||
| ||||||||
| Число аргументов функции ЧПС произвольно: | ||||||||
| Аргум 1 - процентная ставка | ||||||||
| Аргум 2 - начальная инвестиция, | ||||||||
| Аргм 3,4,5, … - дополнит. Инвестиции в последующие годы (со знаком - или доходы со знаком +.) В нашем случае доходы, поэтому +. | ||||||||
| Показатель 2- сумма всех расходов и доходов доходов за 1.2,3 годы | ||||||||
| =СУММ(B4:B7) | =СУММ(C4:C7) | =СУММ(D4:D7) | ||||||
| 7 000 000р. | 6 000 000р. | 10 000 000р. | ||||||
| Приступаем к построению сценариев. | ||||||
| Шаг 1 В закладке «данные» находим кнопку «Анализ «что-если». Нажимаем. | ||||||
В появившемся меню выбираем «диспетчер сценариев». Откроется
диалоговое окно «диспетчер сценариев».
| ||||||
| Как можно воспользоваться этими диапазонами? | ||||||
Кнопка добавить – добавить новый сценарий, объединить - объединить текущий лист со сценариями других листов.
Шаг 2
В этом окне следует указать имя сценария, изменяемые ячейки (в данной ситуации В4:В7). Снимая флажок с параметра «Запретить изменения», разрешаем изменять значения в диапазоне В4:В7
Шаг 3. В следующем окне можно ввести новые значения параметров в ячейки сценария или оставить уже имеющиеся.
На одном листе можно создать произвольное количество сценариев, т.е. поименованных диапазонов данных.
• Произвольный новый лист можно объединить с уже имеющимися сценариями.
• Вызвать сценарии на новом листе:
Данные --> Работа с данными --> Анализ «что-если» --> Диспетчер сценариев -->
Проект1 --> кнопка «Вывести»
В ячейках В4;В7 отобразятся значения сохраненного диапазона.
• В произвольные ячейки нового листа можно вставить формулы
=ЧПС(B2;B5;B6;B7) или =СУММ(B4:B7).
Можно менять значения в диапазоне В4;В7, соответствующие ячейки будут изменяться.
• Построим отчет по имеющемуся сценарию
• Данные --> Работа с данными --> Анализ «что-если» --> Диспетчер сценариев -->
• Проект1 --> кнопка «Отчет»
Отчет строится в двух вариантах - структура и сводная таблица. Рассмотрим первый вариант – структура.
Данная структура может быть сгруппирована и занимать на листе мало места.
Сводные таблицы рассмотрим на следующих занятиях.
