Использование функций регрессии
В Excel имеется 5 функций для линейной регрессии (ЛИНЕЙН(...), ТЕНДЕНЦИЯ(...), ПРЕДСКАЗ(...), НАКЛОН(...), СТОШУХ(...)) и 2 функции дня экспоненциальной регрессии - ЛГРФПРИБЛ(...) и POCT(...).
Рассмотрим основные из них.
1Функция
=ЛИНЕЙН(изв._знач._y;изв. _знач.x;конст;стат)(5)
вычисляет коэффициентm и постоянную b для уравнения прямой (1).
Её аргументы:
Известные_значения_y и известные_значения_x - это множество значений y и необязательное множество значений x (их вводить необязательно), которые уже известны для соотношения (1).
Константа-это логическое значение , которое указывает требуется ли, чтобы константа b была равна 0 (т.е. линия проходит через начало координат и при х=0 у тоже равно 0). Если константа имеет значение ИСТИНА или опущено,то b ≠ 0 и вычисляется обычным образом.
Статистика -это логическое значение, которое указывает, требуется ли вывести дополнительную статистику по регрессии.
Результат выполнения функции зависит от значения аргумента статистика. Если статистика имеет значение ЛОЖЬ(или 0),то функция ЛИНЕЙН возвращает только значения коэффициентов m и b, в противном случае выводится дополнительная регрессионная статистика в виде табл.2:
Таблица 2
| mn | mn-1 | ... | m2 | m1 | b |
| sen | sen-1 | ... | se2 | se1 | seb |
| r2 | sev | ... | #Н/Д | #Н/Д | #Н/Д |
| F | df | ... | #Н/Д | #Н/Д | #Н/Д |
| ssreg | ssresid | ... | #Н/Д | #Н/Д | #Н/Д |
где
se1,se2,...,sen - стандартные значения ошибок для коэффициентов m1,m2,...,mn.
seb - стандартное значение ошибки для постоянной b (seb равно #Н/Д, т.е. «нет допустимого значения», если конст. имеет значение ЛОЖЬ).
r2 - коэффициент детерминированности. Сравниваются фактические значения y и значения, получаемые из уравнения прямой; по результатам сравнения вычисляется коэффициент детерминированности, нормированный от 0 до 1. Если он равен 1, то имеет место полная корреляция с моделью, т.е. нет различия между фактическим и оценочным значениями y. В противоположном случае, если коэффициент детерминированности равен 0, то уравнение регрессии неудачно для предсказания значений y.
sey - стандартная ошибка для оценки y(предельное отклонение для у).
F - F-статистика, или F-наблюдаемое значение. F-статистика используется для определения того, является ли наблюдаемая взаимосвязь между зависимой и независимой переменными случайной или нет.
df - cтепени свободы. Степени свободы полезны для нахождения F-критических значений в статистической таблице. Для определения уровня надежности модели нужно сравнить значения в таблице с F-статистикой, возвращаемой функцией ЛИНЕЙН.
ssreg - регрессионая сумма квадратов.
ssresid - oстаточная сумма квадратов.
#Н/Д – ошибка, означающая “Нет доступного значения”.
Точность аппроксимации с помощью прямой, вычисленной функцией ЛИНЕЙН, зависит от степени разброса данных. Чем ближе данные к прямой, тем более точными являются модель, используемая функцией ЛИНЕЙН, и значения, получаемые из уравнения прямой.
2 Функция
=ЛГРФПРИБЛ(изв_знaч_у; изв_ знач_х; конст;стат),(6)используется в случае экспоненциальной регрессии, является аналогом функции (5) и отличается лишь тем, что вычисляет коэффициенты mи bдля экспоненциальной кривой (2).
3 Функция
=ТЕНДЕНЦИЯ(изв_знач_y; изв_знач_x;нов_знач_x;конст)(7)
возвращает числовые значения, лежащие на прямой линии, наилучшим образом аппроксимирующей известные табличные данные.
Новые_значения_x -это те, для которых необходимо вычислить соответствующие значения y.
Если параметр новые_значения_xпропущен,то считается,что он совпадает с известными x. Назначение остальных параметров функции ТЕНДЕНЦИЯ совпадает с описанными выше.
4. В случае экспоненциальной регрессии аналогом функции (7) является функция =РОСТ(изв_знач_y; изв_знач_x; конст)(8)
Правила ввода функций:
Формулы (5)- (8) являются табличными, т.е. они заменяют собой несколько обычных формул и возвращают не один результат, а массив результатов. Поэтому необходимо соблюдать следующие правила:
1) перед вводом одной из формул (5)- (8) выделите блок ячеек, точно совпадающей по размеру с величиной возвращаемого формулой массива результатов. Например, при использовании функции ЛИНЕЙН с выводом статистики нужно выделить массив ячеек, равный табл. 1, если параметр статистика равен ЛОЖЬ, достаточно выделить одну строку табл.1;
2) наберите функцию в строке формул. При этом слова на русском языке можно набирать строчными буквами, т.к. они являются ключевыми и при вводе Excel автоматически переведёт их в заглавные. Имена ячеек обязательно вводятся латинским шрифтом. Вместо слова ИСТИНА можно вводить числа от 1 до 9 (не 0), а вместо слова ЛОЖЬ – число 0. Если в результате выполнения функции выводится одно число, можно вводить формулы не вручную, а использовать аппарат Мастера функций.
3) Функция запускается не клавишей Enter., а одновременным нажатиием клавиш Shift+Ctri+Enter. Результаты вычислений заполнят выделенные ячейки.
Линия тренда
Линия тренда в Excel позволяет наглядно отображать тенденцию изменения данных, она представляет собой интерполяционную кривую, описывающую отложенные на диаграмме данные.
Для того, чтобы дополнить диаграмму исходных данных линией тренда, необходимо выполнить следующие действия :
1) выделить на диаграмме ряд данных, для которого требуется построить линию тренда;
2) в меню Диаграмма выбрать командуДобавить линию тренда,
3) в открывшемся окне задать метод интерполяции (линейный, полиномиальный, логарифмический, экспоненциальный и т.д.), а также через вкладку Параметры - другие параметры (например, вывод уравнения кривой тренда, коэффициента детерминированности r2, направление и количество периодов для экстраполяции (прогноза) и др.);
4) нажать кнопку ОК.
Чтобы отобразить на графике (гистограмме и др.) новые, прогнозируемые в результате регрессионного анализа данные, нужно:
1) определить их с помощью функций ТЕНДЕНЦИЯ, РОСТ или другим способом,
2) выделить на диаграмме нужную кривую, щелкнув по ней мышью,
3) в меню Диаграмма выбрать команду Добавить данные..., в появившемся окне ввести диапазон ячеек с новыми данными вручную или протащив по ним курсор при нажатой левой клавише мыши, нажать ОК.
На диаграмме появится продолжение кривой, построенной по новым данным.
Рассмотрим применение функций регрессии: