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

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

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

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

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

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

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

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

Таблица 2

где

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статочная сумма квадратов.

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

Любую прямую можно задать ее наклоном mи y-пересечением:

Наклон (m):

Для того, чтобы определить наклон прямой, обычно обозначаемый через m, нужно взять две точки прямой (x₁,y₁) и (x₂,y₂); тогда наклон равен m = (y₂ - y₁)/(x₂ - x₁).

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) в открывшемся окне задать метод интерполяции (линейный, полиномиальный, логарифмический и т.д.), а также через вкладку Параметры - другие параметры (например, вывод уравнения кривой тренда, коэффициента детерминированности r², направление и количество периодов

для экстраполяции (прогноза) и др.);

4) нажать кнопку ОК.

Чтобы отобразить на графике (гистограмме и др.) новые, прогнозируемые в результате регрессионного анализа данные, нужно:

• определить их с помощью функций ТЕНДЕНЦИЯ, РОСТ или другим способом,

• выделить на диаграмме нужную кривую, щелкнув по ней мышью,

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

На диаграмме появится продолжение кривой, построенной по новым данным.

Простая линейная регрессия

Пример 1 Функция ТЕНДЕНЦИЯ.

а) Предположим, что фирма желает приобрести земельный участок в июле. Фирма собирает информацию о ценах за последние 12 месяцев, начиная с марта, на типичный земельный участок. Номера месяцев с 1 по 12 (известные значения х) записаны в ячейки А2...А13. Известные значения y содержат множество известных значений (133 890 руб., 135 000 руб., 135 790 руб., 137 300 руб., 138 130 руб., 139 100 руб., 139900 руб., 141 120 руб., 141 890 руб., 143 230 руб., 144 000 руб., 145 290 руб.), которые находятся в ячейках В2:В13 соответственно (данные условные). Новые значения х, т.е. числа 13, 14, 15, 16, 17 введём в ячейки А14...А18. Для того чтобы определить ожидаемые значения цен на март, апрель, май, июнь, июль, выделим любой интервал ячеек, например, С2:С6 (по одной ячейке для каждого месяца) и в строке формул введем функцию:

=ТЕНДЕНЦИЯ (В2:В13;А2:А13;А14:А18;С2:С6)(10)

После нажатия клавиш Ctrl+Shift+Enter данная функция будет введена как формула вертикального массива, а в ячейках С2:С6 появится результат: {146172:147190:148208:149226:150244}.

Таким образом, в июле фирма может ожидать цену около 150 244 руб.

б) Тот же результат будет получен, если вводить в формулу не все массивы переменных х и у, а использовать часть массивов, которые предусматриваются автоматически по умолчанию. Тогда формула (10) примет вид:

=ТЕНДЕНЦИЯ (В2:В13;;{13:14:15:16:17}).(11)

В формуле (11) используется массив по умолчанию (1:2:3:4:5:6:7:8:9: 10:11:12) для аргумента известные _значения_х, соответствующий 12 месяцам, для которых имеются данные по продажам. Он должен был бы быть помещен в формуле(11) между двумя знаками ;;.

Массив (13:14:15:16:17) соответствует следующим 5 месяцам, для которых и получен массив результатов (146172:147190:148208:149226: 150244).

Элементы массивов разделяет знак " : " , который указывает на то, что они расположены по столбцам.

в) Аргумент новые значения х можно задать другим массивом ячеек, например, В14:В18, в которые предварительно записаны те же номера месяцев 13, 14, 15, 16, 17. Тогда вводимая в строку формул функция примет вид =ТЕНДЕНЦИЯ (В2:В13;;В14:В18).

Пример 2 а) Функция ЛИНЕЙН. Дана таблица изменения температуры в течение шести часов, введенная в ячейки D2:E7 (табл. 3). Требуется определить температуру во время восьмого часа.

Таблица 3

Выделим ячейки D8:E12 для вывода результата (в соответствии с табл. 1), введём в строку ввода формулу =ЛИНЕЙН(E2:E7;D2:D7;1;1), нажмём клавиши Ctrl+Shift+Enter, в выделенных ячейках появится результат (табл. 4):

Таблица 4

Таким образом, коэффициент m = 3,143 со стандартной ошибкой 0,541, а свободный член b = -3,333 со стандартной ошибкой 2,106, т.е. функция, описывающая данные табл. 2, имеет вид

у = 3,143∙х - 3,333 (12)

Стандартные ошибки показывают максимально возможное отклонение параметра от рассчитанной величины . Для у оно составляет 2,263, т.е. реальное значение у может лежать в пределах у±2,263.

Точность приближения к табличным данным (коэффициент детерминированности r²) cоставляет 0,894 или 89,4%, что является высоким

показателем. При х = 8 получим: у = 3,143∙8 – 3,333 = 21,81 град.

б)Тот же результат можно получить, использовав функцию =ТЕНДЕНЦИЯ (E2:E7;;G2:G5) для, например, следующих четырёх часов, предварительно введя в ячейки G2:G5 числа с 7 до 10. Выделив ячейки Н2:Н5, введя в строку формул эту функцию и нажав Ctrl+Shift+Enter, получим в выделенных ячейках массив {18,667; 21,80952; 24,95238; 28,09524}, т.е. для восьмого часа значение у = 21,809 ≈21,81град.

в)Функция ПРЕДСКАЗ – позволяет предсказывать значение у для нового значения х по известным значениям хиу, используя линейное приближение зависимости у = f(x). Синтаксис функции:

=ПРЕДСКАЗ(нов­,_знач._х; изв._знач._ у;изв._ знач._х)

Для данных примера 2 ввод формулы =ПРЕДСКАЗ(8;E2:E7;D2:D7) выводит в заранее выделенной ячейке результат 21,809. Новое значение х может быть задано не числом, а ячейкой, в которую записано это число.

Отличие функции ПРЕДСКАЗ от функции ТЕНДЕНЦИЯ заключается в том, что ПРЕДСКАЗ прогнозирует значение функции линейного приближения только для одного нового значения х.

Экспоненциальная регрессия

Пример 3 а) Функция ЛГРФПРИБЛ.

Условие примера 2.

Поскольку функция в табл. 2 носит явно нелинейный характер, целесообразно искать её приближение в виде не прямой линии, как в примере 2, а в виде нелинейной кривой. Из всех видов нелинейности (гипербола, парабола и др.) Excel реализует только экспоненциальное приближение вида у = b∙m^x c помощью функции ЛГРФПРИБЛ, которая рассчитывает для этого уравнения значения b и m.

Выделим для результата блок ячеек F8:G12, введём в строку формул

Функцию =ЛГРФПРИБЛ(E2:E7;D2:D7;1;1), нажмём клавиши Ctrl + Shift + Enter, в выделенных ячейках появится результат (таблица 5):

Таблица 5

Таким образом, коэффициент m = 1,556, а b = 1,197, т.е. уравнение приближающей кривой имеет вид :

у = 1,197∙(1,556 ^х) (13)

со стандартными ошибками для m, b и y равными 0,02, 0,07 и 0,08 соответственно. Коэффициент детерминированности r² = 0,992, т.е.

полученное уравнение даёт совпадение с табличными данными с вероятностью 99,2%.

Поскольку интерполяция табл. 2 экспоненциальной кривой даёт более точное приближение (99,2%) и с меньшими стандартными ошибками для m,b и y, в качестве приближающего уравнения принимаем уравнение (13).

При х = 8 получим у = 1,197∙34,363 = 41,131 град.

б) Функция РОСТ вычисляет прогнозируемое по экспоненциальному приближению значения удля новых значений х, имеет формат:

=РОСТ(изв_знач_у;изв_знач_х;нов_знач_х;константа).

Выделим блок ячеек F14:F17, введём формулу

=РОСТ(E2:E7;D2:D7;G2:G5;ИСТИНА), в выделенных ячейках появится массив чисел {27,6696434; 43,3384133; 67,8800967; 106,319248}, т.е. при х=8 значение функции у = 43,34град. Это значение немного отличается от вычисленного в п. а), поскольку функция РОСТ использует для расчетов линию экспоненциального тренда.

Примечание.При выборе экспоненциальной приближающей кривой следует учитывать, что интерполировать ею можно только участки, где функция монотонно возрастает или убывает (при отрицательном аргументе х), т.е. функцию, имеющую точки перегиба (например, параболу, синусоиду , кривую рис. 2 – т.А и др.) следует разбить на участки монотонного изменения от одной точки перегиба до другой и каждый участок интерполировать отдельно. Для рис. 2 функцию нужно разбить на 2 участка – от начала до т.А и от т.А до конца кривой.