 |
способ. Диспетчер сценариев
|
|
|
|
Средства Microsoft Excel позволяют создавать и сохранять в виде сценариев наборы входных значений, приводящих к различным результатам.
Сценарий – это множество входных значений, называемых изменяемыми ячейками, которое можно сохранить под указанным именем, а затем применить к модели рабочего листа, чтобы проследить, как значения изменяемых ячеек влияют на другие значения модели. Для каждого сценария можно определить до 32 изменяемых ячеек.
Чтобы создать сценарий, следует:
1. В меню Данные выбрать команду Анализ «Что-если», указав Диспетчер сценариев (рис. 5.4).
Рисунок 5.4 – Выбор Диспетчера сценариев
Появится окно «Диспетчер сценариев» (рис. 5.4.1)
Рисунок 5.4.1 – Диспетчер сценариев
2. Щелкнуть по кнопке Добавить. Откроется окно Добавление сценария (рис. 5.5).
Рисунок 5.5 – Диалоговое окно Добавление сценария
3. В поле Название сценария ввести имя сценария.
4. В поле Изменяемые ячейки ввести ссылки на изменяемые ячейки. Несколько ссылок отделяются друг от друга точками с запятыми. Ссылки можно ввести с клавиатуры или выделить их на рабочем листе. Несмежные ячейки добавляются при нажатой клавише <Ctrl>.
5. Щелкнуть по кнопке ОК.
6. В открывшемся диалоговом окне Значения ячеек сценария ввести значения каждой изменяемой ячейки (рис. 5.6).
Рисунок 5.6 – Диалоговое окно Значения ячеек сценария
7. Для создания других сценариев щелкнуть по кнопке Добавить (откроется диалоговое окно Добавление сценария) и повторить пункты 3 – 6.
Для завершения работы с Диспетчером сценариев щелкнуть по кнопке ОК, а затем – по кнопке Закрыть.
Рекомендуется сохранить в качестве сценария первоначальные значения изменяемых ячеек, чтобы потом можно было быстро восстановить эти значения.
|
|
|
Для просмотра сценария нужно:
1. В меню Данные выбрать команду Анализ данных и указать Диспетчер сценариев.
2. В поле Сценарии выделить имя сценария, который необходимо просмотреть.
3. Щелкнуть по кнопке Вывести.
Вместо пунктов 2 и 3 можно дважды щелкнуть по имени нужного сценария.
Чтобы отредактировать сценарий, надо:
1. В меню Данные выбрать команду Анализ данных и указать Диспетчер сценариев.
2. В поле Сценарии выделить имя сценария, который необходимо отредактировать.
3. Щелкнуть по кнопке Изменить.
4. Внести необходимые изменения: можно изменить имя сценария, изменяемые ячейки, значения изменяемых ячеек.
5. Для завершения работы с Диспетчером сценариев щелкнуть по кнопке ОК, а затем – по кнопке Закрыть.
Для создания итогового отчета по сценариям следует:
1. В меню Данные выбрать команду Анализ данных и указать Диспетчер сценариев.
2. Щелкнуть по кнопке Отчет.
3. Выбрать тип отчета: Структура или Сводная таблица.
В отчете типа Структура перечислены все сценарии с определенными для них значениями ячеек. Этот тип отчета полезен тогда, когда каждый пользователь определяет сценарий со своими данными.
Отчет типа Сводная таблица предоставляет возможность эмпирического анализа сценариев. Этот тип отчета полезен тогда, когда сценарий имеет несколько наборов значений изменяющихся ячеек, заданных различными пользователями; с помощью сводных таблиц можно выполнить анализ для разных комбинаций сценариев.
4. В поле Ячейки результата ввести ссылки на ячейки, значения которых надо представить в отчете. В качестве разделителя ссылок используется запятая. Ссылки можно ввести с клавиатуры или выделить их на рабочем листе. Несмежные ячейки добавляются при нажатой клавише <Ctrl>. Итоговые отчеты создаются на отдельных листах.
Способ. Подбор параметра
Пусть имеется формула, которая прямо или косвенно зависит от некоторого параметра. Задача состоит в определении такого значения этого параметра, которое позволяет получить нужный результат формулы. При подборе параметра значение влияющей ячейки (параметра) изменяется до тех пор, пока формула, зависящая от этой ячейки не возвратит заданное значение.
|
|
|
Математическая суть задачи состоит в решении уравнения X = а, где функция х описывается заданной формулой, х – искомый параметр, а – требуемый результат формулы.
Для решения этой задачи необходимо выполнить следующие действия:
1. Выделить ячейку, содержащую формулу, для которой нужно найти определенное решение.
2. В меню Данные / Анализ «что-если» выбрать команду Подбор параметра. В поле Установить в ячейке ввести ссылку на ячейку, содержащую формулу (по умолчанию в это поле вводится адрес текущей ячейки).
3. В поле Значение ввести значение, которое нужно получить по заданной формуле.
4. В поле Изменяя ячейку ввести ссылку на ячейку, содержащую значение изменяемого параметра (эта ячейка называется изменяемой).
5. Щелкнуть по кнопке ОК.
Пример 3. Дано уравнение: X^2 + ЗХ – 2 = А,
где: А – требуемый результат формулы; Х – искомый параметр.
Определить такое значение параметра X, при котором А будет равно 20.
1. Занести в ячейку A1 любое значение, например, 1.
2. Ввести в ячейку А2 указанную формулу, которая примет следующий вид: =A1^2+3*A1-2. В формуле указана ссылка на ячейку А1, в которой условно находится параметр X.
3. Задать команду Данные / Анализ «что-если» /Подбор параметра (рис. 5.7).
4. В поле Установить в ячейке указать А2 (по умолчанию в это поле вводится адрес текущей ячейки).
5. В поле Значение ввести – 20.
6. В поле Изменяя значение ячейки указать адрес ячейки, в которой должен находиться параметр X, т.е. А1.
Рисунок 5.7 – Окно Подбор параметра
После выполнения команды в изменяемой ячейке появится значение параметра X, при котором результат формулы равняется заданной величине. При этом будет пересчитана вся таблица, т.е. изменятся значения, прямо или косвенно зависящие от изменяемого параметра.
Подбор параметра можно выполнять графически, перетаскивая точки данных на диаграмме.
При подборе параметра одна из ячеек обязательно должна содержать формулу.
|
|
|
Контрольные вопросы
1. Для чего предназначены функции: ПЛТ; БС; ПС; КПЕР и СТАВКА? Поясните синтаксис перечисленных функций.
2. Назначение и способы анализа «Что если»?
3. Для чего предназначена «Таблица подстановок», опишите технологию ее применение для функций с одной переменной и для функций с двумя переменными?
4. Что такое сценарий, как его создать, просмотреть, изменить, получить итоговый отчет на отдельном листе?
5. Сущность операции Подбор параметра, как она выполняется?
|
|
|
