Использование функций регрессии
В Excel имеется 5 функций для линейной регрессии (ЛИНЕЙН(...), ТЕНДЕНЦИЯ(...), ПРЕДСКАЗ(...), НАКЛОН(...), СТОШУХ(...)) и 2 функции дня экспоненциальной регрессии - ЛГРФПРИБЛ(...) и POCT(...). Рассмотрим некоторые из них.
1 Функция=ЛИНЕЙН(изв._знач._y;изв. _знач. x;конст;стат)(5)
вычисляет коэффициентm и постоянную b для уравнения прямой (1).
Известные_значения_y и известные_значения_x - это множество значений y и необязательное множество значений x (их вводить необязательно), которые уже известны для соотношения (1).
Константа - это логическое значение , которое указывает, требуется ли, чтобы константа b была равна 0. Если константа имеет значение ИСТИНА или опущено, то b вычисляется обычным образом.
Статистика -это логическое значение, которое указывает, требуется ли вывести дополнительную статистику по регрессии.
Если статистика имеет значение ЛОЖЬ(или 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статочная сумма квадратов.
#Н/Д – ошибка, означающая “Нет доступного значения”.
Любую прямую можно задать ее наклоном mи y-пересечением:
Наклон (m):
Для того, чтобы определить наклон прямой, обычно обозначаемый через m, нужно взять две точки прямой (x1,y1) и (x2,y2); тогда наклон равен m = (y2 - y1)/(x2 - x1).
y-пересечение (b):
y-пересечением прямой, обычно обозначаемым через b, является значение y для точки, в которой прямая пересекает ось y.
Уравнение прямой имеет вид y = mx + b. Если известны значения m и b, то можно вычислить любyю точку на прямой, подставляя значения y или x в уравнение. Можно также использовать функцию ТЕНДЕНЦИЯ (см. ниже).
Если для функции уимеется только одна независимая переменная x, можно получить наклон и y-пересечение непосредственно, используя следующие формулы:
2 Наклон m:
ИНДЕКС(ЛИНЕЙН(изв_знач_y;изв_знач_x);1)
3 y-пересечение b:
ИНДЕКС(ЛИНЕЙН(изв_знач_y;изв_знач_x);2)
Точность аппроксимации с помощью прямой, вычисленной функцией ЛИНЕЙН, зависит от степени разброса данных. Чем ближе данные к прямой, тем более точными являются модель, используемая функцией ЛИНЕЙН, и значения, получаемые из уравнения прямой.
4 Функция ЛГРФПРИБЛ. В случае экспоненциальной регрессии аналогом функции (5) является функция
=ЛГРФПРИБЛ(изв_знaч_у; изв_ знач_х; конст;стат), (6)
которая отличается лишь тем, что вычисляет коэффициенты mи bдля
экспоненциальной кривой (2).
5 Функция ТЕНДЕНЦИЯ
=ТЕНДЕНЦИЯ(изв_знач_y; изв_знач_x;нов_знач_x;конст)(7)
возвращает числовые значения, лежащие на прямой линии, наилучшим образом аппроксимирующей известные табличные данные.
Новые_значения_x-это те, для которых необходимо вычислить соответствующие значения y.
Если параметр новые_значения_xпропущен, то считается, что он совпадает с известными x. Назначение остальных параметров функции ТЕНДЕНЦИЯ совпадает с описаными выше.
6 В случае экспоненциальной регрессии аналогом функции (7) является функция =РОСТ(изв_знач_y; изв_знач_x; конст)(8)
7 функция =CTOШУX(изв_знач_y;изв_знач_х)(9)
возвращает стандартную погрешность регрессии - меру погрешности
предсказываемого значения у для заданного значения х.
3.1 Правила ввода функций:
Формулы (5)-(8) являются табличными, т.е. они заменяют собой несколько обычных формул и возвращают не один результат, а массив результатов. Поэтому необходимо соблюдать следующие правила:
1 Перед вводом одной из формул (5)-(8) выведите блок ячеек, совпадающей по размеру (или больший) с величиной возвращаемого формулой массива результатов. Например, при использовании функции ЛИНЕЙН с выводом статистики нужно выделить массив ячеек, равный табл. 1, если параметр статистика равен ЛОЖЬ, достаточно выделить одну строку табл.1;
2 Наберите функцию в строке формул. При этом слова на русском языке можно набирать строчными буквами, т.к. они являются ключевыми и при вводе Excel автоматически переведёт их в заглавные. Имена ячеек обязательно вводятся латинским шрифтом. Вместо слова ИСТИНА можно вводить числа от 1 до 9 (не 0), а вместо слова ЛОЖЬ – число 0. Если в результате выполнения функции выводится одно число, можно вводить формулы не вручную, а использовать аппарат Мастера функций.
3) одновременно нажмите клавиши Shift+Ctri+Enter. Результаты вычислений заполнят выделенные ячейки.
Линия тренда
Excel позволяет наглядно отображать тенденцию изменения данных с помощью линии тренда, которая представляет собой интерполяционную кривую, описывающую отложенные на диаграмме данные.
Для того, чтобы дополнить диаграмму исходных данных линией тренда, необходимо выполнить следующие действия :
1) выделить на диаграмме ряд данных, для которого требуется построить линию тренда;
2) в меню Диаграмма выбрать командуДобавить линию тренда,
3) в открывшемся окне задать метод интерполяции (линейный, полиномиальный, логарифмический и т.д.), а также через вкладку Параметры - другие параметры (например, вывод уравнения кривой тренда, коэффициента детерминированности r2, направление и количество периодов
для экстраполяции (прогноза) и др.);
4) нажать кнопку ОК.
Чтобы отобразить на графике (гистограмме и др.) новые, прогнозируемые в результате регрессионного анализа данные, нужно:
• определить их с помощью функций ТЕНДЕНЦИЯ, РОСТ или другим способом,
• выделить на диаграмме нужную кривую, щелкнув по ней мышью,
• в меню Диаграмма выбрать команду Добавить данные..., в появившемся окне ввести диапазон ячеек с новыми данными вручную или протащив по ним курсор при нажатой левой клавише мыши, нажать ОК.
На диаграмме появится продолжение кривой, построенной по новым данным.
Дата добавления: 2016-02-02; просмотров: 627;