Вопрос 8. Анализ данных в Excel 2007.
При обработке данных часто возникает необходимость в прогнозировании результата на основе известных исходных значений или, наоборот, определении того, какими должны быть исходные значения для получения исходного результата. В Excel 2007 для этого используется команда Подбор параметра.
Продемонстрируем применение операции подбора параметра при начислении сложных процентов. Предположим, что известен размер вклада, который будет помещен на определенный срок под заданный процент. Требуется вычислить коэффициент наращения (т.е. значение, показывающее, во сколько раз увеличится вклад за указанный срок) и сумму выплат в конце периода. Впоследствии мы определим также, исходя из заданной суммы выплат, необходимый размер вклада при неизменном сроке и процентной ставке.
Рис. 10.27 Исходные данные для примера (применение операции подбора параметра при начислении сложных процентов)
В таблице начисления сложных процентов указаны значения размера вклада (В1), срока вклада (В2) и процентной ставки (В3). Коэффициент наращения определен в ячейке В4 с помощью формулы: =(1+В3)^В2.
В этой формуле к 1 прибавляется значение ячейки В3, и результат возводится в степень, показатель которой равен количеству лет (В2). Сумма выплат (на момент истечения срока вклада) в ячейке В5 вычисляется по формуле: =В4*В1 (коэффициент наращения умножается на размер вклада). Из приведенного рисунка ясно, что при изменении значений размера вклада, срока вклада или процентной ставки соответственно изменяется и сумма выплат. Пользуясь этой моделью, можно установить, как влияют исходные значения на конечный результат.
Задача усложняется, если требуется определить значения исходных данных, исходя из заданной суммы выплат. Решить такую задачу можно только опытным путем, изменяя значения исходной величины, пока не будет получен желаемый результат. В Excel для такого класса задач можно использовать команду Подбор параметра[4].
В приведенном примере сумма выплат составила почти 400 тысяч. Предположим, что через 20 лет мы хотим получить ровно полмиллиона. В этом случае можно либо увеличить размер вклада, либо «выторговать» у банка более выгодную процентную ставку. Поскольку второй вариант сразу отпадает, увеличим размер вклада.
Рис. 10.28 Окно операции Подбор параметра (слева) и результат применения этой операции при начислении сложных процентов
Выделим ячейку, в которой должен быть представлен желаемый результат (В5), и откроем с помощью команды Подбор параметра соответствующее диалоговое окно. При этом адрес выделенной ячейки будет автоматически представлен в поле Установить в ячейке. Укажем в поле Значение целевое значение – 500000.
В нашем примере изменяется только размер вклада, а срок вклада и процентная ставка остаются неизменными. Поместим курсор ввода в поле Изменяя значение ячейки и выделим ячейку В1, после чего адрес выделенной нами ячейки будет представлен в вышеуказанном поле.
После задания всех необходимых данных нажатием кнопки ОК следует запустить поиск нужного значения. Результат вычисления будет представлен в следующем диалоговом окне. Таким образом, с помощью Excel мы определили, какой должна быть сумма вклада, чтобы при процентной ставке в 5% годовых через 20 лет получить полмиллиона.
В задачах прогнозирования, решаемых с использованием метода регрессионного анализа активно используется такой инструмент как построение тренда. Линии тренда позволяют графически отображать тенденции данных и прогнозировать их дальнейшие изменения. Подобный анализ называется также регрессионным анализом. Используя регрессионный анализ, можно продлить линию тренда в диаграмме за пределы реальных данных для предсказания будущих значений.
Применительно к линиям тренда регрессионный анализ позволяет:
· оценить степень связи между переменными на основании известных значений;
· выбрать механизм вычисления значений неизвестной переменной;
· продолжить линию в любом направлении, экстраполировать за пределы известных значений и показать тенденцию их изменения;
· построить линию скользящего среднего, которая сглаживает случайные флуктуации, более наглядно демонстрирует модель и прослеживает тенденцию изменения данных.
Рассмотрим пример. Требуется, используя линию тренда, для ряда данных 3, 4, 8, 17, 23 спрогнозировать следующее значение.
1. Построим диаграмму, выбрав тип диаграммы – график:
2. Щелкнем по линии ряда данных правой кнопкой мыши и в контекстном меню выберем команду Добавить линию тренда. Добавить линию тренда на диаграмму Excel можно также с помощью меню Работа с диаграммами.
3. В диалоговом окне Линия тренда перейдем на вкладку Параметры. В этом окне щелкнем на последней строке, позволяющую поместить на диаграмму величину достоверности аппроксимации R2.
4. Перейдем на вкладку Тип и выберем один из возможных типов регрессионной линии.
5. Щелкнем на кнопке ОК и оценим выведенную на диаграмме величину достоверности аппроксимации R2.
6. Поскольку величина достоверности аппроксимации меньше 1, щелкнем правой кнопкой по линии тренда и в контекстном меню выберем команду Формат линии тренда. На вкладке Тип укажем следующий тип линии.
7. Если величина достоверности аппроксимации равна 1, тип регрессионной линии можно считать установленным. В противном случае выбирается следующий тип линии и т.д. Если ни в одном из вариантов не получена единица, то выбирается тот тип, для которого величина достоверности аппроксимации максимальна.
Рис. 10.29 Добавление линии тренда на диаграмму (вид контекстного меню в MS Excel 2007)
Рис. 10.30. Вид фрагмента меню Работа с диаграммами в MS Excel 2007
Рис. 10.31. Выбор параметров линии тренда
8. Поскольку величина достоверности аппроксимации меньше 1, щелкнем правой кнопкой по линии тренда и в контекстном меню выберем команду Формат линии тренда. На вкладке Тип укажем следующий тип линии.
9. Если величина достоверности аппроксимации равна 1, тип регрессионной линии можно считать установленным. В противном случае выбирается следующий тип линии и т.д. Если ни в одном из вариантов не получена единица, то выбирается тот тип, для которого величина достоверности аппроксимации максимальна.
10. Допустим, мы остановили свой выбор на полиномиальном типе с величиной достоверности аппроксимации 0,9911. Перейдем на вкладку Параметры, на которой:
· Зададим прогноз на один период (правильнее было бы сказать – на шаг) вперед.
· Отменим вывод на диаграмме величины достоверности аппроксимации и зададим вывод уравнения.
11. Щелкнем по кнопке ОК. В результате регрессионного анализа получено уравнение, описывающее зависимость (представлено на диаграмме).
12. Численное значение у можно определить, подставив в уравнение функции, интересующее нас значение аргумента значение х.
|
Рис. 10.32 График значений исходного ряда, линия тренда и уравнение, описывающее зависимость (меню сверху открыто с помощью Панели быстрого доступа
Расчет линий тренда в Excel производится путем аппроксимации данных по методу наименьших квадратов в соответствии с уравнениями, которые приведены в таблице 2.
Таблица 2.