Прогнозирование с помощью анализа «что-если».

Анализ «что-если» позволяет прогнозировать значение какой-либо функции (математической, финансовой, статистической и др.) при изменении её аргументов. Существует четыре способа прогнозирования значений: с помощью таблиц подстановки данных, с помощью сценариев и с помощью подбора параметров и поиска решения.

1 способ. Таблица подстановки данных – это диапазон ячеек, показывающий, как изменение значений подстановки влияет на возвращаемый формулой результат. Если в какой-либо ячейке записана формула, содержащая элементы из других ячеек, то при изменении значения в какой-нибудь или нескольких ячейках изменится результат в ячейке, содержащей формулу.

Пример 2Для примера 1 определить:

- ежемесячные выплаты при процентных ставках 7%, 8% и 9% годовых,

- ежемесячные выплаты при процентной ставке 5%, 6% и 7% и сумме заема 100 000, 110 000, 120 000 и 130 000р .

1 Для определения выплат при ставках 7%, 8% и 9% годовых введём таблицу данных в виде (рисунок 5.2):

Рисунок 5.2 – Таблица данных

для изменения процентов

2 В ячейкеА9 сделаем ссылку на ячейку с формулой ПЛТ, т.е. введём знак "=" и щёлкнем на ячейке В6. В строке формул запишется =В6,а в ячейке А9 появится результат –3 006,65р.

3Выделим блок ячеек А9:В12 и запустим инструмент Таблица данных на вкладке Данные, группа Работа с данными, кнопка Анализ "что-если", пункт меню Таблица данных.

Рисунок 5.3 Окно Таблица данных

4 В появившемся окне (рисунок 5.3) введём в строкедля столбцов адрес ячейки В2(щелкнем по ячейке В2), а в строке для столбцов щелкнем поВ4и нажмём ОК. Таблица данных заполнится числами – рисунок 5.4.

Рисунок 5.4 – Заполненная Таблица данных

5 Заготовим другую таблицу подстановок – введём столбцы для изменения суммы кредита (рисунок 5.5). В ячейке А14 также сделаем ссылку на ячейку с формулой, т.е. введём =В6, затем выделим таблицу (блок А15:Е18) и запустим инструмент Таблица данных на вкладке Данные, группа Работа с данными, кнопка Анализ "что-если", пункт меню Таблица данных.Введя В2для столбцов иВ4для строк, получим:

Рисунок 5.5 –Таблица данных для варьирования ставки и кредита

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

2 способ. Сценарий – это набор значений подстановки, используемый для прогнозирования поведения модели. Если в Примере 1 изменить процентную ставку с 8,5% на 9%, то формула ПЛТ в ячейке В6 автоматически пересчитается и покажет результат -3038,75р. При этом прежний результат -3006,65р. для 8,5% пропадает. Чтобы его сохранить, применяется сценарий, в котором приводятся расчёты ПЛТ с новыми значениями и сохраняется исходный (текущий) вариант. На одном листе Excel можно создать и сохранить несколько различных сценариев и переключаться на любой из них для просмотра результатов и выбора наилучшего.

Пример 3 Оформим в виде сценария вариант подстановки данных из примера 1.

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

1 Из вкладкиДанные выберете команду Анализ «что-если»,выбратьДиспетчер сценариев.

2 В открывшемся окне Диспетчер сценариев нажмите кнопку Добавить.

3 Введите имя сценария., например «Кредит 200 000, 10%».

4 В поле Изменяемые ячейки задайте те ячейки (если они не смежные, то через Ctrl), которые Вы собираетесь изменить, в данном случае – ячейкиВ2иВ4.

5 Введите новые значения этих ячеек (рисунок 5.6). Нажмите кнопку ОК.

Рисунок 5.6 Новые значения кредита и ставки

6 Нажмите кнопку Отчёт, выберите переключатель Структура, задайте ячейки для вывода результата В2:В6 (те, которые используются в формуле ПЛТ) и нажмите ОК.

В результате на отдельном листе MSExcelСтруктура сценария появится сценарий с текущими и новыми значениями функции ПЛТ – рисунок 5.7.

Для просмотра сценария необходимо воспользоваться кнопкой Вывести в окне Диспетчер сценариев. Знаки «+»(«-«) слева и сверху позволяют разворачивать (сворачивать) отдельные разделы отчёта. Серым выделены изменяемые поля.

Рисунок 5.7 – Сценарий Кредит 20 000, 10%

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

3 способ. Подбор параметра.Подбор параметра – это обратная задача решения уравнений. Если в прямой задаче для функции y = f(x) по известному аргументу х вычисляется значение функции у, то в обратной задаче значение функции узадаётся числом, а величина хподбирается под заданное значение у.

При подборе параметра значение влияющей ячейки (параметра) изменяется до тех пор, пока формула, зависящая от этой ячейки не возвратит заданное значение.

Пример4 Условие примера 1. Компания может ежемесячно выплачивать не более 2500р. Определить, каким должен для этого быть последний платёж.

1.Выделим ячейкуВ6:

2.На вкладке Данные, группа Работа с данными, кнопка Анализ "что-если"щелкнуть по пункту менюПодбор параметра– рисунок 5.8. Появится окно Подбор параметра:

Рисунок 5.8 – Окно Подбор параметра

В окне Подбор параметра:

-в поле Установить в ячейке – введено В6,

-в поле Значение - ввести -2500

-в поле Изменяя значение ячейки – ввести В3 (ячейка последнего платежа),

-нажать ОК.

В итоге появится окно Результат подбора параметра – рисунок 5.9:

Рисунок 5.9 – Окно Результат подбора параметра

Таким образом, если выплачивать по 2500 р. В месяц, то последний платёж составит -27716 р.

При подборе параметра одна из ячеек обязательно должна содержать формулу.

4 способ. Команда Поиск решения из вкладкиДанные \ Работа с данными \ Анализ "что-если" используется для подбора одновременно нескольких параметров с целью максимизации или минимизации содержимого целевой ячейки и подробно рассматривается в лабораторной работе №7 (excel-7).

Контрольные вопросы

1 Как вывести на экран список функций MSExcelи запуститьМастер функций?

2 Какую операцию выполняет функция ПЛТ, что вводится в её поля Ставка, Кпер, ПС, Бс, Тип?

3 Назначение и способы анализа «что-если»?

4 Что такое «Таблица данных», как её создать для одно- и двухмерного массива?

5 Что такое сценарий, как его создать, просмотреть, получить итоговый отчет на отдельном листе?

6 Сущность операции Подбор параметра, как она выполняется?

Задания

1. Выполнить задание примера 1, изменив сумму кредита на 140000·n, где n- номер студента в журнале преподавателя. Выполнить то же для новой суммы кредита, изменив годовой процент с 8,5% на 5%, а срок кредита с 5 на 10 лет.

2. Создать две таблицы данныхкак в примере 2, изменив сумму заёма на 80000·n, где n- номер студента в журнале преподавателя.

3. Оформить в виде сценариев все операции из п.1 (два сценария + Текущие значения) задания.

4. Выполнить задание примера 4, изменив сумму ежемесячной выплаты на n·100.

Содержание отчёта

1Название, цель, содержание работы

2 Задание своего варианта

3 Письменные ответы на контрольные вопросы

4 Выводы по работе

На своём носителе должны быть сохранены результаты работы

Практическое занятие №6 (excel-6)

Создание макросов в MSExcel 2007

Цель работы:научиться использовать макросы для автоматизации

выполняемых задач

Содержание работы:

1 Общие сведения

2 Запуск макроса с клавиатуры сочетанием клавиш.

3Запуск макроса из окна Макрос.

Общие сведения

Если какое-то действие часто повторяется, его выполнение можно автоматизировать с помощью макроса. Макрос — это подпрограмма, которая содержит серию команд и функций, реализующих действия пользователя при выполнении операций в MSExcel. Их можно запускать всякий раз, когда необходимо выполнить данную задачу. Программа макроса при этом записывается на языке VisualBasic автоматически ихранится в модуле VisualBasic.

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

Перед тем как записать макрос, необходимо спланировать шаги и команды, которые он будет выполнять. Если при записи макроса была допущена ошибка, то и она, и ее исправление будет также запрограммировано. Каждый раз при записи макроса он сохраняется в новом модуле, присоединенном к книге.

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

Макрос может запускаться тремя способами – сочетанием клавиш на клавиатуре, с помощью кнопки на быстрой панели (или вновь созданной панели инструментов) и из окна меню Макрос.

Макрос можно записать из вкладки Вид \ Макросыили вкладки Разработчик \ Код. Если вкладкаРазработчикнедоступна, выполните следующие действия для ее отображения.

1. Щелкните вкладку Файл (значок Кнопка Office), а затем щелкнитеПараметры Excel.

2. В левой области окна Параметры Excel нажмите Основные, в правой в группеОсновные параметры работы с Excelустановите флажок Показывать вкладку "Разработчик" на ленте, а затем нажмите кнопкуОК.