Использование функций регрессии

В Excel имеется 5 функций для линейной регрессии (ЛИНЕЙН(...), ТЕНДЕНЦИЯ(...), ПРЕДСКАЗ(...), НАКЛОН(...), СТОШУХ(...)) и 2 функции дня экспоненциальной регрессии - ЛГРФПРИБЛ(...) и POCT(...). Рассмотрим некоторые из них.

1 Функция =ЛИНЕЙН( изв._знач. _y; изв. _знач. x;конст;стат) (5)

вычисляет коэффициент m и постоянную b для уравнения прямой (1).

Известные_значения_ y и известные_значения_ x - это множество значений y и необязательное множество значений x (их вводить необязательно), которые уже известны для соотношения (1).

Константа -это логическое значение, которое указывает требуется ли, чтобы константа b была равна 0. Если константа имеет значение ИСТИНА или опущено,то b вычисляется обычным образом.

Статистика- это логическое значение, которое указывает, требуется ли вывести дополнительную статистику по регрессии.

Если статистика имеет значение ЛОЖЬ(или 0),то функция ЛИНЕЙН возвращает только значения коэффициентов m и b, в противном случае выводится дополнительная регрессионная статистика в виде табл.1:

Таблица 1

где

se₁,se₂,...,se_n - стандартные значения ошибок для коэффициентов m₁,m₂,...,m_n.

se_b - стандартное значение ошибки для постоянной b (se_b равно #Н/Д, т.е. «нет допустимого значения», если конст. имеет значение ЛОЖЬ).

r² - коэффициент детерминированности. Сравниваются фактические значения y и значения, получаемые из уравнения прямой; по результатам сравнения вычисляется коэффициент детерминированности, нормированный от 0 до 1. Если он равен 1, то имеет место полная корреляция с моделью, т.е. нет различия между фактическим и оценочным значениями y. В противоположном случае, если коэффициент детерминированности равен 0, то уравнение регрессии неудачно для предсказания значений y.

se_y - стандартная ошибка для оценки y (предельное отклонение для у).

F - F-статистика, или F-наблюдаемое значение. F-статистика используется для определения того, является ли наблюдаемая взаимосвязь между зависимой и независимой переменными случайной или нет.

df - cтепени свободы. Степени свободы полезны для нахождения F-критических значений в статистической таблице. Для определения уровня надежности модели нужно сравнить значения в таблице с F-статистикой, возвращаемой функцией ЛИНЕЙН.

ss_reg - регрессионая сумма квадратов.

ss_resid - oстаточная сумма квадратов.

#Н/Д – ошибка, означающая “Нет доступного значения”.

2 В случае экспоненциальной регрессии аналогом функции (5) является

функция =ЛГРФПРИБЛ(изв_знaч_у; изв_ знач_х; конст;стат), (6)которая отличается лишь тем, что вычисляет коэффициенты m и b для экспоненциальной кривой (2).

3 Функция

= ТЕНДЕНЦИЯ( изв_знач _y; изв_знач _x; нов_знач _x;конст) (7)

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

Новые_значения_ x -это те, для которых необходимо вычислить соответствующие значения y.

Если параметр новые_значения_ x пропущен,то считается,что он совпадает с известными x. Назначение остальных параметров функции ТЕНДЕНЦИЯ совпадает с описанными выше.

6. В случае экспоненциальной регрессии аналогом функции (7) является функция =РОСТ(изв_знач_y; изв_знач_x; конст) (8)

Правила ввода функций:

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

1) перед вводом одной из формул (5)-(8) выведите блок ячеек, точно совпадающей по размеру с величиной возвращаемого формулой массива результатов. Например, при использовании функции ЛИНЕЙН с выводом статистики нужно выделить массив ячеек, равный табл. 1, если параметр статистика равен ЛОЖЬ, достаточно выделить одну строку табл.1;

2) наберите функцию в строке формул. При этом слова на русском языке можно набирать строчными буквами, т.к. они являются ключевыми и при вводе Excel автоматически переведёт их в заглавные. Имена ячеек обязательно вводятся латинским шрифтом. Вместо слова ИСТИНА можно вводить числа от 1 до 9 (не 0), а вместо слова ЛОЖЬ – число 0. Если в результате выполнения функции выводится одно число, можно вводить формулы не вручную, а использовать аппарат Мастера функций.

3) одновременно нажмите клавиши Shift+Ctri+Enter. Результаты вычислений заполнят выделенные ячейки.