ЛАБОРАТОРНАЯ РАБОТА № 7.1
ПРОГНОЗИРОВАНИЕ В EXCEL С ПОМОЩЬЮ РЕГРЕССИОННОГО АНАЛИЗА
Цель работы: научиться выполнять прогнозирование экономических параметров с помощью одномерного и многомерного регрессионного анализа
Содержание работы:
1. Линейный одномерный регрессионный анализ.
2. Экспоненциальный одномерный регрессионный анализ.
3. Линейный многомерный регрессионный анализ
Порядок выполнения работы:
1. Изучить методические указания.
2. Выполнить задания.
3. Оформить отчет и ответить на контрольные вопросы.
МЕТОДИЧЕСКИЕ РЕКОМЕНДАЦИИ
Одним из методов, используемых для прогнозирования, является регрессионный анализ.
Регрессия – это статистический метод, который позволяет найти уравнение, наилучшим образом описывающее совокупность данных, заданных таблицей.
| X | X1 | X2 | … | Xi | … | Xn |
| Y | Y1 | Y2 | … | Yi | … | Yn |
На графике данные отображаются точками. Регрессия позволяет подобрать к этим точкам кривую у=f(x), которая вычисляется по методу наименьших квадратов и даёт максимальное приближение к табличным данным.
Рисунок 26
По полученному уравнению можно вычислить (сделать прогноз) значение функции у для любого значения х, как внутри интервала изменения х из таблицы(интерполяция), так и вне его(экстраполяция).
Линейная регрессия
Линейная регрессия дает возможность наилучшим образом провести прямую линию через точки одномерного массива данных. Уравнение с одной независимой переменной, описывающее прямую линию, имеет вид:
y=mx+b, (1)
где:
x - независимая переменная;
у - зависимая переменная;
m - характеристика наклона прямой;
b - точка пересечения прямой с осью у.
Например, имея данные о реализации товаров за год с помощью линейной регрессии можно получить коэффициенты прямой (1) и, предполагая дальнейший линейный рост, получить прогноз реализации на следующий год.
Нелинейная регрессия
Нелинейная регрессия позволяет подбирать к табличным данным нелинейное уравнение – параболу, гиперболу и др. Excel реализует нелинейность в виде экспоненты, т.е. подбирает кривую вида:
, (2)
которая позволяет наилучшим образом провести экспоненциальную кривую по точкам данных, которые изменяются нелинейно.
Так, например, данные о росте населения почти всегда лучше описываются не прямой линией, а экспоненциальной кривой. При этом нужно помнить, что достоверное прогнозирование возможно только на участках подъёма или спуска кривой (при отрицательных значениях х), т.к. сама кривая (2) изменяется монотонно, без точек перегиба. Например, делать экспоненциальный прогноз для функции, изменяющейся синусоидально, можно только на участках подъёма или спуска функции, для чего её разбивают на соответствующие интервалы.
Множественная регрессия
Множественная регрессия представляет собой анализ более одного набора данных аргумента х и даёт более реалистичные результаты.
Множественный регрессионный анализ также может быть как линейным, так и экспоненциальным. Уравнение регрессии (1) и (2) примут соответственно вид (3) и (4):
y=m1x1+m2x2+…+mnxn + b (3)
y=b*m1x1*m2x2*…*mnxn (4)
где:
х1,х2, …, хn – независимые переменные.
С помощью множественной регрессии, например, можно оценить стоимость дома в некотором районе, основываясь на данных его площади, размерах участка земли, этажности, вида из окон и т.д.
Использование функций регрессии
В Excel имеется 5 функций для линейной регрессии (ЛИНЕЙН(…), ТЕНДЕНЦИЯ(…), ПРЕДСКАЗ(…), НАКЛОН(…), СТОШУХ(…)) и 2 функции для экспоненциальной регрессии – ЛГРФПРИБЛ(…) и РОСТ(…).
Рассмотрим некоторые из них.
Функция =ЛИНЕЙН(изв._знач._у;изв._знач._х;конст;стат) (5)
вычисляет коэффициент m и постоянную b для уравнения прямой (1).
Известные_значения_у и известные_значения_х – это множество значений у и необязательное множество значений х (их вводить необязательно), которые уже известны для соотношения (1).
Константа – это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если константа имеет значение ИСТИНА или опущено, то b вычисляется обычным образом.
Статистика – это логическое значение, которое указывает требуется ли вывести дополнительную статистику по регрессии.
Если статистика имеет значение ЛОЖЬ (или 0), то функция ЛИНЕЙН возвращает только значения коэффициентов m и b, в противном случае выводится дополнительная регрессионная статистика в виде табл.1:
Таблица 1
| mn | mn-1 | … | m2 | m1 | b |
| sen | sen-1 | … | se2 | se1 | seb |
| r2 | sey | … | #Н/Д | #Н/Д | #Н/Д |
| F | df | … | #Н/Д | #Н/Д | #Н/Д |
| ssreg | ssresid | … | #Н/Д | #Н/Д | #Н/Д |
где
se1, se2,…,sen – стандартные значения ошибок для коэффициентов m1,m2,…,mn
seb – стандартное значение ошибки для постоянной b (seb равно #Н/Д, т.е. «нет допустимого значения», если конст. имеет значение ЛОЖЬ).
r2 – коэффициент детерминированности.
Сравниваются фактические значения у и значения, получаемые из уравнения прямой; по результатам сравнения вычисляется коэффициент детерминированности, нормированный от 0 до 1. Если он равен 1, то имеет место полная корреляция с моделью, т.е. нет различия между фактическим и оценочным значениями у. В противоположном случае, если коэффициент детерминированности равен 0, то уравнение регрессии неудачно для предсказания значений у.
sey – стандартная ошибка для оценки у (предельное отклонение для у).
F – F-cтатистика, или F-наблюдаемое значение. Она используется для определения того, является ли наблюдаемая взаимосвязь между зависимой и независимой переменными случайной или нет.
df – степени свободы. Степени свободы полезны для нахождения F-критических значений в статистической таблице. Для определения уровня надёжности модели нужно сравнить значения в таблице с F-статистикой, возвращаемой функцией ЛИНЕЙН.
ssreg – регрессионная сумма квадратов.
ssresid – остаточная сумма квадратов.
#Н/Д – ошибка, означающая «нет доступного значения»
Любую прямую можно задать её наклоном m и у-пересечением:
Наклон (m):
Для того, чтобы определить наклон прямой, обычно обозначаемый через m, нужно взять 2 точки прямой (х1,у1) и (х2,у2); тогда наклон равен m=(y2-y1)/(x2-x1).
у-пересечение (b):
у-пересечение прямой, обычно обозначаемым через b, является значение у для точки, в которой прямая пересекает ось у.
Уравнение прямой имеет вид: у=mx+b. Если известны значения m и b, то можно вычислить любую точку на прямой, подставляя значения у или х в уравнение. Можно также использовать функцию ТЕНДЕНЦИЯ (см. ниже).
Если для функции у имеется только одна независимая переменная х, можно получить наклон и у -пересечение непосредственно, используя следующие формулы:
2. Наклон m:
ИНДЕКС(ЛИНЕЙН(изв_знач_у;изв_знач_х); 1)
3. у-пересечение b:
ИНДЕКС(ЛИНЕЙН(изв_знач_у;изв_знач_х); 2)
Точность аппроксимации с помощью прямой, вычисленной функцией ЛИНЕЙН, зависит от степени разброса данных. Чем ближе данные к прямой, тем более точными являются модель, используемая функцией ЛИНЕЙН, и значения, получаемые из уравнения прямой.
4. В случае экспоненциальной регрессии аналогом функции (5) является функция
=ЛГРФПРИБЛ(изв_знач_у;изв_знач_х;конст;стат), (6)
которая отличается лишь тем, что вычисляет коэффициенты m и b для экспоненциальной кривой (2).
5. Функция
=ТЕНДЕНЦИЯ(изв_знач_у;изв_знач_х;нов_знач_х;конст) (7)
возвращает числовые значения, лежащие на прямой линии, наилучшим образом аппроксимирующие известные табличные данные.
Новые_значения_х – это те, для которых необходимо вычислить соответствующие значения у.
Если параметр новые_значения_х пропущен, то считается, что он совпадает с известными х. Назначение остальных параметров функции ТЕНДЕНЦИЯ совпадает с описанными выше.
6. В случае экспоненциальной регрессии аналогом функции (7) является функция
=РОСТ(изв_знач_у;изв_знач_х;конст) (8)
7. Функция =СТОШУХ(изв_знач_у;изв_знач_х) (9)
возвращает стандартную погрешность регрессии – меру погрешности предсказываемого значения у для заданного значения х.
Правила ввода функций:
Формулы(5)-(8) являются табличными, т.е. они заменяют собой несколько обычных формул и возвращают не один результат, а массив результатов. Поэтому необходимо соблюдать следующие правила:
1. перед вводом одной из формул (5)-(8) выведите блок ячеек, точно совпадающей по размеру с величиной возвращаемого формулой массива результатов. Например, при использовании функции ЛИНЕЙН с выводом статистики нужно выделить массив ячеек, равный табл. 1, если параметр статистики равен ЛОЖЬ, достаточно выделить одну строку таб.1;
2. наберите функцию в строке формул. При этом слова на русском языке можно набирать строчными буквами, т.к. они являются ключевыми и при вводе Exсel автоматически переведет их в заглавные. Имена ячеек автоматически вводятся латинским шрифтом. Вместо слова ИСТИНА можно вводить числа от 1 до 9(не 0), в вместо слова ЛОЖЬ – число 0. Если в результате, выполнения функции выводится одно число, можно вводить формулы не вручную, а использовать аппарат Мастера функций.
3. одновременно нажмите клавиши Shift+Ctrl+Enter. Результаты вычислений заполнят выделенные ячейки.
Линия тренда
Excel позволяет наглядно отображать тенденцию данных с помощью линии тренда, которая представляет собой интерполяционную кривую, описывающую отложенные на диаграмме данные.
Для того, чтобы дополнить диаграмму исходных данных линией тренда, необходимо выполнить следующие действия:
§ выделить на диаграмме ряд данных, для которого требуется построить линию тренда;
§ в меню Диаграмма выбрать команду Добавитьлинию тренда;
§ в открывшемся окне задать метод интерполяции (линейный, полиномиальный, логарифмический и т. д.), а также через команду Параметры – другие параметры (например, вывод уравнения кривой тренда, коэффициента детерминированности r2, направление и количество периодов для экстраполяции (прогноза) и др.);
§ нажать кнопку ОК.
Чтобы отобразить на графике (гистограмме и др.) новые, прогнозируемые в результате регрессионного анализа данные, нужно:
§ определить их с помощью функции ТЕНДЕНЦИЯ, РОСТ или другим способом,
§ выделить на диаграмме нужную кривую, щелкнув по ней мышью,
§ в меню Диаграмма выбрать команду Добавить данные…, в появившемся окне выбрать диапазон ячеек с новыми данными вручную или протащив по ним курсор при нажатой левой клавише мыши, нажать ОК.
На диаграмме появится продолжение кривой, построенной по новым данным.
