Прогноз по уравнению множественной регрессии

МЕТОДИЧЕСКИЕ РЕКОМЕНДАЦИИ

Сущность метода регрессионного анализа

Одним из методов, используемых для прогнозирования, является регрессионный анализ.

Регрессия – это статистический метод, который позволяет найти уравнение, наилучшим образом описывающее совокупность данных, заданных таблицей.

X	X1	X2	…	Xi	…	Xn
Y	Y1	Y2	…	Yi	…	Yn

На графике данные отображаются точками. Регрессия позволяет подобрать к этим точкам кривую у=f(x), которая вычисляется по методу наименьших квадратов и даёт максимальное приближение к табличным данным.

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

Линейная регрессия

Линейная регрессия дает возможность наилучшим образом провести прямую линию через точки одномерного массива данных (рис.13.1 а). Уравнение с одной независимой переменной, описывающее прямую линию, имеет вид:

где:x – независимая переменная;

y – зависимая переменная;

m – характеристика наклона прямой;

b – точка пересечения прямой с осью у.

Например, имея данные о реализации товаров за год с помощью линейной регрессии можно получить коэффициенты прямой (1) и, предполагая дальнейший линейный рост, получить прогноз реализации на следующий год.

Нелинейная регрессия

Нелинейная регрессия позволяет подбирать к табличным данным нелинейное уравнение (рис. 13.1 рис. 13.1, б.) – параболу, гиперболу и др. Excel реализует нелинейность в виде экспоненты, т.е. подбирает кривую вида:

,

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

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

Множественная регрессия

Множественная регрессия представляет собой анализ более одного набора данных аргумента х и даёт более реалистичные результаты.

Множественный регрессионный анализ также может быть как линейным, так и экспоненциальным. Уравнение регрессии (1) и (2) примут соответственно вид (3) и (4):

( 3)

( 4)

С помощью множественной регрессии, например, можно оценить стоимость дома в некотором районе, основываясь на данных его площади, размерах участка земли, этажности, вида из окон и т.д.

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

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

Рассмотрим некоторые из них.

Функция ЛИНЕЙН((LINEST) вычисляет коэффициент m и постоянную b для уравнения прямой (1). Синтаксис функции:

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

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

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

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

Таблица 13.1. Общий вид выводимого массива статистических показателей при использовании функции ЛИНЕЙН((LINEST)

mn	mn-1	…	m2	m1	b
sen	sen-1	…	se2	se1	seb
r 2	sey	…	#Н/Д	#Н/Д	#Н/Д
F	df	…	#Н/Д	#Н/Д	#Н/Д
ssreg	ssresid	…	#Н/Д	#Н/Д	#Н/Д

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

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

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

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

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

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

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

ss_resid – остаточная сумма квадратов;

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

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

Наклон ( m ). Для того, чтобы определить наклон прямой, обычно обозначаемый через m , нужно взять 2 точки прямой (х1,у1) и (х2,у₂); тогда наклон равен m=(y2-y1)/(x2-x1 ).

у-пересечение ( b ) прямой, обычно обозначаемое через b , является значение у для точки, в которой прямая пересекает ось у.

Уравнение прямой имеет вид: у=mx+b. Если известны значения m и b , то можно вычислить любую точку на прямой, подставляя значения у или х в уравнение. Можно также использовать функцию ТЕНДЕНЦИЯ ( TREND ) (см. ниже).

Если для функции у имеется только одна независимая переменная х, можно получить наклон и у-пересечение непосредственно, используя следующие формулы:

Точность аппроксимации с помощью прямой, вычисленной функцией ЛИНЕЙН, зависит от степени разброса данных. Чем ближе данные к прямой, тем более точными являются модель, используемая функцией ЛИНЕЙН, и значения, получаемые из уравнения прямой.

В случае экспоненциальной регрессии аналогом функции (5) является функция ЛГРФПРИБЛ(LOGEST):

которая отличается лишь тем, что вычисляет коэффициенты m и b для экспоненциальной кривой (2).

Функция ТЕНДЕНЦИЯ(TREND) имеет вид:

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

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

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

В случае экспоненциальной регрессии аналогом функции (7) является функция РОСТ(GROWTH):

возвращает стандартную погрешность регрессии – меру погрешности предсказываемого значения у для заданного значения х.

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

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

1. Перед вводом одной из формул (5)-(8) выведите блок ячеек, точно совпадающей по размеру с величиной возвращаемого формулой массива результатов. Например, при использовании функции ЛИНЕЙН с выводом статистики нужно выделить массив ячеек, равный табл. 13.1, если параметр статистики равен ЛОЖЬ, достаточно выделить одну строку табл. 13.1.
2. Наберите функцию в строке формул. При этом слова на русском языке можно набирать строчными буквами, т.к. они являются ключевыми и при вводе Exсel автоматически переведет их в заглавные. Имена ячеек автоматически вводятся латинским шрифтом. Вместо слова ИСТИНА можно вводить числа от 1 до 9 (не 0), а вместо слова ЛОЖЬ – число 0. Если в результате, выполнения функции выводится одно число, можно вводить формулы не вручную, а использовать аппарат Мастера функций.
3. Одновременно нажмите клавиши Shift+Ctrl+Enter . Результаты вычислений заполнят выделенные ячейки.

Линия тренда

Excel позволяет наглядно отображать тенденцию данных с помощью линии тренда, которая представляет собой интерполяционную кривую, описывающую отложенные на диаграмме данные.

Для того, чтобы дополнить диаграмму исходных данных линией тренда, необходимо выполнить следующие действия:

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

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

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

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

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

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

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

После нажатия клавиш Ctrl+ Shift+Enter данная функция будет выделена как формула вертикального массива, а в ячейках B14:B18 появится результат: .

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

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

В формуле (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 (табл. 13.2 таблица 13.2).

Требуется определить температуру во время восьмого часа.

Таблица 13.2. Данные для примера 1

…	D	E
1	х-№часа	у-t о , град.
2	1	2
3	2	3
4	3	4
5	4	7
6	5	12
7	6	18

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

3,142857	-3,3333333
0,540848	2,106302
0,894088	2,2625312
33,76744	4
172,8571	20,47619

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

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

Точность приближения к табличным данным (коэффициент детерминированности r 2 ) составляет 0,894 или 89,4%, что является высоким показателем. При х=8 получим: у=3,143*8-3,333=21,81 град.

б) Тот же результат можно получить, использовав функцию =ТЕНДЕНЦИЯ(Е2:Е7;;G2:G5) для, например, следующих четырёх часов, предварительно введя в ячейки G2 :G5 числа с 7 до 10. Выделив ячейки Н2:Н5, введя в строку формул эту функцию и нажав Сtrl+Shift+Enter, получим в выделенных ячейках массив , т.е. для восьмого часа значение град.

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

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

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

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

Пример 3

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

Рассмотрим условие примера 2.

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

Выделим для результата блок ячеек F8:G12 , введём в строку формул Функцию =ЛГРФПРИБЛ(Е2:Е7;D2:D7;1;1), нажмем клавиши Сtrl+Shift+Enter, в выделенных ячейках появится результат:

1,56628015	1,196513
0,02038299	0,07938
0,99181334	0,085268
484,599687	4
3,52335921	0,029083

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

со стандартными ошибками для m, b , и у равными 0,02, 0,079 и 0,085 соответственно. Коэффициент детерминированности r 2 =0,992, т.е. полученное уравнение даёт совпадение с табличными данными с вероятностью 99,2%.

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

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

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

Выделим блок ячеек F14: F17 , введём формулу =РОСТ(Е2:Е7;D2:D7;G2:G5;ИСТИНА), в выделенных ячейках появится массив чисел , т.е. при х=8 значение функции у=43,34 град. Это значение немного отличается от вычисленного в п. а), поскольку функция РОСТ использует для расчетов линию экспонециального тренда.

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

Множественная линейная регрессия

Пример 4

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

у – оценочная цена здания под офис;

х₁ – общая площадь в квадратных метрах;

х₂ – количество офисов;

х₃ – количество входов;

х₄ – время эксплуатации здания в годах.

Агент наугад выбирает 11 зданий из имеющихся 1500 и получает следующие данные:

А	В	С	D	Е
1	х1— площадь, м2	х2 – офисы	х3 – входы	х4 – срок, лет	у – цена, у.е.
2	2310	2	2	20	42000
3	2333	2	2	12	144000
4	2356	3	1,5	33	151000
5	2379	3	2	43	151000
6	2402	2	3	53	139000
7	2425	4	3	23	169000
8	2448	2	1,5	99	126000
9	2471	2	2	34	142000
10	2494	3	3	23	163000
11	2517	4	4	55	169000
12	2540	2	3	22	149000

«Пол-входа» означает вход только для доставки корреспонденции.

В этом примере предполагается, что существует линейная зависимость между каждой независимой переменной (х₁,х₂,х₃,х₄) и зависимой переменной (у), т.е. ценой зданий под офис в данном районе.

• выделим блок ячеек А14:Е18 (в соответствии с табл. 13.1 таблица 13.1),
• введём формулу =ЛИНЕЙН(Е2:Е12;А2:D12;ИСТИНА;ИСТИНА), —
• нажмём клавиши Ctrl+Shift+Enter ,
• в выделенных ячейках появится результат:

А	В	С	D	E
14	-234,237	2553,210	12529,7682	27,6413	52317,83
15	13,2680	530,6691	400,066838	5,42937	12237,36
16	0,99674	970,5784	#Н/Д	#Н/Д	#Н/Д
17	459,753	6	#Н/Д	#Н/Д	#Н/Д
18	1732393319	5652135	#Н/Д	#Н/Д	#Н/Д

Уравнение множественной регрессии теперь может быть получено из строки 14:

Теперь агент может определить оценочную стоимость здания под офис в том же районе, которое имеет площадь 2500 м 2 , три офиса, два входа, зданию 25 лет, используя следующее уравнение:

Это значение может быть вычислено с помощью функции ТЕНДЕНЦИЯ:

При интерполяции с помощью функции

для получения уравнения множественной экспоненциальной регрессии выводится результат:

0,99835752	1,0173792	1,0830186	1,0001704	81510,335
0,00014837	0,0065041	0,0048724	6,033Е-05	0,1365601
0,99158875	0,0105158	#Н/Д	#Н/Д	#Н/Д
176,832548	6	#Н/Д	#Н/Д	#Н/Д
0,07821851	0,0006635	#Н/Д	#Н/Д	#Н/Д
#Н/Д	#Н/Д	#Н/Д	#Н/Д	#Н/Д

Коэффициент детерминированности здесь составляет 0,992 (99,2%), т.е. меньше, чем при линейной интерполяции, поэтому в качестве основного следует оставить уравнение множественной регрессии (14).

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

ЗАДАНИЕ

Вариант задания к данной лабораторной работе включает две задачи. Для каждой из них необходимо составить и определить:

1. Таблицу исходных данных, а также значений, полученных методами линейной и экспоненциальной регрессии.
2. Коэффициенты в уравнениях прямой и экспоненциальной кривой (функции ЛИНЕЙН и ЛГРФПРИБЛ), напишите уравнения прямой и экспоненциальной кривой для простой и множественной регрессии.
3. Погрешности (ошибки) прямой и экспоненциальной кривой, вычислений для коэффициентов и функций, коэффициенты детерминированности. Оценить, какой тип регрессии наилучшим образом подходит для вашего варианта задания.
4. Прогноз изменения данных, выполненный с использованием линейной и экспоненциальной регрессии (функции ТЕНДЕНЦИЯ, ПРЕДСКАЗ, РОСТ).
5. Построить гистограмму (или график) исходных данных для задачи 1 (одномерная регрессия), отобразить на ней линию тренда, а также соответствующее ей уравнение и коэффициент детерминированности.

Варианты заданий (номер варианта соответствует номеру компьютера).

1. На рынке наблюдается стойкое снижение цен на компьютеры. Сделать прогноз, на сколько необходимо будет снизить цену на компьютеры в следующем месяце в вашей фирме, чтобы как минимум сравнять её с ценой на аналогичные компьютеры в конкурирующей фирме, если известна динамика изменения цен на них в конкурирующей фирме за последние 12 месяцев.

Для выполнения задания нужно ввести ряд из 12 ячеек с ценами конкурирующей фирмы, сделать прогноз цены на следующий месяц и др. (см. Задание).

1. Известна структура расходов фирмы на рекламу в газетах, на радио, в журналах, на телевидении, на наружную рекламу (в процентах от общей суммы), а также оборот фирмы в каждом за последние 6 месяцев. Какой оборот можно ожидать в следующем месяце, если предполагается следующая структура расходов на рекламу: газеты-40%, журналы-40%, радио-5%, телевидение-14%, наружная реклама-1%.

Для выполнения задания нужно составить таблицу со столбцами вида:

Месяц	х1-газеты,%	х2-журн.,%	х3-рад.,%	х4-телев.,%	х5-нар. рекл.,%	Оборот, $
1	37	34	12	10	5	410000
2	38	37	10	11	6	411500
3	39	38	9	13	7	413700
4	40	39	8	15	8	417050
5	41	40	7	16	9	420000
6	42	42	5	17	10	425000

и сделать множественный регрессионный прогноз (см. Задание).

1. Имеются данные об объеме продаж в расчете на душу населения по хлебу и молоку и данные по годовым доходам на душу за 10 лет. По каждому товару построить модели регрессии для объемов продаж и функции размера доходов. Сделать прогноз о продажах и доходах на следующий год.

Для выполнения задания нужно составить таблицу вида:

Годы	1	2	3	4	5	6	7	8	9	10	11
х1-хлеб, кг	23,5	26,7	27,9	30,1	31,5	35,7	38,3	40,1	41,5	42,8
х2-молоко, л	20,45	22	23,8	25,9	27,4	29	33,5	36,8	38,1	39,5
У-доход, р.	6600	7200	8400	10500	12750	14730	16240	17000	18050	18250

и получить два уравнения – у=f(x1) и у=f(x2), сделать прогноз на следующий год для рядов х₁, х₂, у и др. (см. Задание).

1. Руководство фирмы провело оценку качеств пяти рекламных агентов по следующим признакам: х₁ – эрудиция, х₂ – знание предметной области. Полученные средние оценки, нормированные от 0 до 1, были сопоставлены с оценками эффективности деятельности агентов (% успешных сделок от количества возможных). Определить эффективность для агента с усреднёнными качествами. Сравнить её со средней эффективностью упомянутых 5 агентов.

Исходные данные нужно ввести в таблицу вида:

А	В	С	D	E	F	G
1	х1-эрудиция	х2-энергичность	х3-люди	х4-внешность	х5-знания	Эффективность
2	Агент 1	0,8	0,2	0,4	0,6	1,0	76%
3	Агент 2	0,74	0,3	0,39	0,58	0,95	78%
4	Агент 3	0,67	0,41	0,35	0,5	0,83	79%
5	Агент 6	0,59	0,59	0,33	0,47	0,8	80%
6	Агент 5	0,5	0,7	0,3	0,4	0,74	81%
7	Средняя эффективность пяти агентов
8	Средний агент	0,5	0,5	0,5	0,5	0,5

Массив ячеек В2-F6 заполняется произвольными числами от 0 до 1, столбец G2 -G6 – процентами удачных сделок по принципу «Чем выше уровень качеств агента, тем выше эффективность его работы», в ячейке G7 должна быть формула для вычисления среднего значения ячеек G2:G6 , в ячейке G8 нужно вычислить значение эффективности для среднего агента по формуле, полученной в результате множественного регрессионного анализа работы пяти агентов. Остальные пункты – см. Задание.

1. Автосалон имеет данные о количестве проданных автомобилей «Мерседес» и «БМВ» за последние 4 квартала. Учитывая тенденцию изменения объёма продаж, определить, каких автомобилей нужно закупить больше («Мерседес» или «БМВ») в следующем квартале?

Для выполнения задания нужно составить и заполнить таблицу вида:

Х	1	2	3	4	5
Мерседес ( Y1 )	10	12	15	18
БМВ ( Y2 )	9	10	14	17

сделать прогноз продаж на новый квартал и выполнить другие пункты задания.

1. Известны следующие данные о 5 недавно проданных подержанных автомобилях: у – стоимость продажи, х₁ – стоимость аналогичного нового автомобиля, х₂ – год выпуска, х₃ – пробег, х₄ – количество капитальных ремонтов, х₅ – экспертные заключения о состоянии кузова и техническом состоянии автомобилей (по 10-бальной шкале). Определить, сколько может стоить автомобиль с соответствующими характеристиками: 340 000, 1998г., 140000км., 1, 6 (см. пример 4).

1. Определить минимально необходимый тираж журнала и возможный доход от размещения в нём рекламы в следующем месяце, если известны данные об объёмах продаж этого журнала и доходах от размещения рекламы за последние 12 месяцев (считать, что расценки на рекламу не менялись).

Для выполнения задания нужно составить таблицу вида:

Месяц	1	2	3	4	5	6	7	8	9	10	11	12
Тираж,тыс.	100	120	121,7	124,2	128	130,1	133,45	136	141	142,1	143,8	145
Доход,тыс. руб.	128	135	138	142	147	154	159	161	163	168	170,5	172

и заполнить ячейки за 12 месяцев условными данными. По этим данным нужно сделать линейный и экспоненциальный прогноз и др. (см. Задание).

1. В целях привлечения покупателей и увеличения оборота фирма проводит стратегию ежемесячного снижения цен на свой товар. На основании данных о динамике изменения цен, объемов продаж в данной фирме и ещё в 3 конкурирующих фирмах за последние 12 месяцев сделать прогноз о том, возрастает ли объём продаж у данной фирмы при очередном снижении цен в следующем месяце, если предположить, что цены и объёмы у конкурентов в следующем месяце будут средние за рассматриваемый период.

Для выполнения задания нужно составить таблицу вида:

Мес.	Фирма		Конкурент 1		Конкурент 2		Конкурент 3
1	У-объём	х1-цена	х2-объём	х3-цена	х4-объём	х5-цена	х6-объём	х7-цена
2	10000	1875	12000	1720	12500	1740	11970	1700
3	11000	1850	12340	1705	12620	1735	12100	1690
4	11570	1810	12750	1675	12740	1710	12350	1645
5	11850	1750	12910	1630	12960	1695	12500	1615
6	12100	1685	13100	1615	13000	1674	12630	1580
7	12340	1630	13570	1600	13210	1625	12920	1545
8	12750	1615	13820	1575	13320	1610	13150	1520
9	12910	1600	13980	1515	13460	1560	13300	1500
10	13100	1575	14000	1500	13600	1525	13610	1490
11	13230	1530	14070	1495	13780	1500	13850	1485
12	13470	1510	14120	1488	13900	1460	14000	1475
13

1. На основании данных о курсе американского доллара и немецкой марки в первом полугодии сделать прогноз о соотношении данных валют на второе полугодие. Во что будет выгоднее вкладывать деньги в конце года?

Для выполнения задания нужно составить таблицу вида:

Месяц	1	2	3	4	5	6	7	8	9	10	11	12
Доллар	24,5	24,9	25,7	26,9	28,0	28,8	29,3	29,7	30,5	30,9	31,8
Марка	72,1	76,3	79,6	85,3	89,7	90,9	93,2	96,4	100,2	101,6	104,9

и сделать линейный прогноз на следующие 6 месяцев и др. (см. Задание).

1. Известны данные за последние 6 месяцев о том, сколько раз выходила реклама фирмы, занимающейся недвижимостью, на телевидении – х₁, радио – х₂, в газетах и журналах – х₃, а также количество звонков –у₁ и количество совершённых сделок – у₂. Какое соотношение количества совершённых сделок к количеству звонков у (в %) можно ожидать в следующем месяце, если известно, сколько раз выйдет реклама в каждом из перечисленных средств массовой информации.

Для выполнения задания нужно составить и заполнить таблицу вида:

A	B	C	D	E
1	месяц	х1	х2	х3	y=у2/у1*100%
2	1	15	10	24	78%
3	2	16	11	23	80%
4	3	18	12	22	81%
5	4	19	12	22	84%
6	5	21	13	21	85%
7	6	22	14	20	89%
8	7

и выполнить применительно к таблице пункты Задания.

1. Для некоторого региона известен среднегодовой доход населения, а также данные о структуре расходов (тыс. руб. в год) за последние 5 лет по следующим статьям: питание – х₁, жильё – х₂, одежда – х₃, здоровье – х₄, транспорт – х₅, отдых – х₆, образование – х₇. На основании известных данных провести анализ потребительского кредита (или накопления) в следующем 6 году.

Для выполнения задания нужно составить и заполнить таблицу вида

Годы	х1	х2	х3	х4	х5	х6	х7	Расход	Доход	Кредит(Y)
1	5	2	1,3	1	0,3	5	4	18,6	21,4	3,1
2	5,2	2,2	1,2	1,2	0,4	4,8	4,5	19,5	22	2,5
3	5,5	2,5	1,1	1,4	0,6	4,6	4,9	20,6	23,4	2,8
4	5,8	2,7	0,9	1,6	1	4,2	5,6	21,8	25,8	4
5	7	3	0,8	2	1,2	4	6,5	24,7	26,2	1,5
6	7,5	3,3	0,7	2,2	1,5	3,8	7	26,5	27,5

В ячейках столбца ) должны быть записаны формулы, вычисляющие суммы всех расходов х₁+х₂+…+х₇ в каждом году, в ячейках столбца Доход – соответствующие среднегодовые доходы, в ячейках столбца Кредит – формулы разности содержимого ячеек с ежегодными доходами и затратами, т.е. Кредит = Доход- . Затем для столбца Кредит нужно выполнить регрессионный прогноз на следующий год и другие пункты Задания.

1. Для 10 однокомнатных квартир, расположенных в одном районе, известны следующие данные: общая площадь – х₁, жилая площадь – х₂, площадь кухни – х₃, наличие балкона – х₄, телефона – х₅, этаж – х₆, а также стоимость – y . Определить, сколько может стоить однокомнатная квартира в этом районе без балкона, без телефона, расположенная на 1-ом этаже, общей площадью 28 м 2 , жилой – 16 м 2 , с кухней 6 м 2 .

Квартиры	X1	X2	X3	X4	X5	Стоимость ( y )
1	41	33	7	1	2	42000
2	40	30	7,7	2	3	40000
3	45	37	8	0	5	47000
4	46,3	34	9	1	6	49500
5	50	36	9	1	4	51000
6	53	40	9,5	1	7	55000
7	56	41	10	0	9	62000
8	60	47	12	2	10	62300
9	65	49	14	2	12	69000
10	70	58	14,5	2	14	72000
11	28	16	6	0	1

1. Определить возможный прирост населения (кол-во человек на 1000 населения) в 2011 году, если известны данные о кол-ве родившихся и умерших на 1000 населения в 1997-2006 годах.

Годы	1997	1998	1999	2000	2001	2002	2003	2004	2005	2006	2011
Родились	100	110	130	155	170	174	180	185	190	200
Умерли	108	115	135	160	178	180	186	190	197	205

1. После некоторого спада наметился рост объёмов продаж матричных принтеров. Используя данные об объёмах продаж, ценах на матричные, струйные и лазерные принтеры, а также на их расходные материалы за последние 6 месяцев, определить возможный спрос на матричные принтеры в следующем месяце.

Проанализируйте, связано ли увеличение спроса на матричные принтеры с уменьшением спроса на струйные и лазерные.

Матричные принтеры			Струйные принтеры			Лазерные принтеры
Спрос у1	Цена х1	Рас.мат. z1	Спрос у2	Цена х2	Рас.мат. z/2	Спрос у3	Цена х3	Рас.мат. z3
1	56	4172	174	26	2384	558	13	12517	1558
2	58	4250	179	24	2398	570	11	12984	1612
3	60	4289	182	23	2401	598	9	13259	1789
4	65	4297	194	20	2456	649	8	13687	1865
5	69	4305	205	19	2512	722	7	14013	1998
6	75	4318	213	18	2543	768	6	14587	2200
7	4456	220	17	2601	779	5	14789	2245

Необходимо сделать прогноз на седьмой месяц по уравнению у₁=f(x₁,z₁), получить уравнение y=(у₂,x₂, z₂, у₃, x₃, z₂ ) и проанализировать его. Если слагаемые у₂ и у₃ входят в регрессионное уравнение со знаком «-«, то уменьшение спросов у₂ и у₃ ведёт к увеличению спроса у₁.

1. Построить прогноз развития спроса населения на телевизоры, если известна динамика продаж телевизоров (тыс. шт.) и динамика численности населения (тыс. чел.) за 10 лет. По данным таблицы сделать прогноз по обоим рядам на следующий год. Выполнить другие пункты задания.

Годы	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011
Динамика населения (тыс. чел)	21,5	26,1	31,5	34,9	45,1	50,8	56	59,4	63,9	67,1
Динамика продаж (тыс. шт.)	2,5	2,9	3,4	3,9	4,1	4,8	5	5,6	5,9	6,2

1. Размещая рекламу в 4-х изданиях, фирма собрала сведения о поступивших на нее откликов – у и сопоставила их с данными об изданиях: х₁ – стоимость издания, х₂ – стоимость одного блока рекламы, х₃ – тираж, х₄ – объём аудитории, х₅ – периодичность, х₆ – наличие телепрограммы. Какое количество откликов можно ожидать на рекламу в издании со следующими характеристиками: 15000 руб., 10$, 1000 экз., 25000 чел., 4 раза в месяц, без телепрограммы.

Пользуясь данными таблицы

Издания	х1	х2	х3	х4	х5	х6	Отклики, у
1	10000	13	700	15000	4	1	108
2	12500	12	850	22000	8	1	115
3	15890	11,8	960	28000	10	0	120
4	17850	11	1200	32000	26	1	128
5	15000	10	1000	25000	4	0

необходимо сделать прогноз при заданных характеристиках.

1. Размещая свою рекламу в 2-х печатных изданиях одновременно, фирма собрала сведения о количестве поступивших звонков и количестве заключенных сделок по объявлениям в каждом из указанных изданий за последние 12 месяцев. Определить, в каком из изданий и насколько эффективность размещения рекламы в следующем месяце будет больше?

Месяцы	Издание 1		Издание 2
Звонки	Сделки	Звонки	Сделки
1	98	66	112	79
2	105	72	143	85
3	105	75	150	90
4	110	80	130	100
5	125	90	120	75
6	140	100	115	80
7	136	95	128	82
8	137	87	132	78
9	145	102	138	88
10	123	75	143	92
11	130	79	150	97
12	139	88	155	97
13

Эффективность определяется как сделки/звонки. Сделать линейный и экспоненциальный прогнозы по обоим изданиям.

1. Пусть комплект мягкой мебели (диван + 2 кресла) характеризуется стоимостью комплектующих: х₁— деревянные подлокотники, х₂ – велюровое покрытие, х₃ – кресло-кровать, х₄ – угловой диван, х₅ – раскладывающийся диван, х₆ – место для хранения белья. По данным о стоимости 5 комплектов сделать вывод о возможной стоимости комплекта с обычным раскладывающимся диваном, с местом для белья, без деревянных подлокотников и велюрового покрытия, с креслом кроватью.

Пользуясь данными таблицы

Признаки	х1	х2	х3	х4	х5	х6	У -стоимость
Комплект 1	250	540	2500	4300	6400	800	13850 руб.
Комплект 2	320	650	3000	4800	7000	980	15770 руб.
Комплект 3	400	730	3900	6000	8500	1100	16730 руб.
Комплект 4	452	1300	4300	7500	9200	2050	24350 руб.
Комплект 5	550	1750	6400	12450	16700	4300	42150 руб.
Комплект 6	670	800	2750	6700	8800	1000

сделать прогноз и выполнить другие пункты задания.

1. Для 2-х радиостанций известны данные об изменении объёма аудитории и динамике роста цен за 1 минуту эфирного времени за последние 12 месяцев. Определить, для какой радиостанции стоимость одного контакта со слушателем будет меньше?

Месяц	Радиостанция 1		Радиостанция 2
Аудитория	Цена 1 мин.	Аудитория	Цена 1 мин.
1	250000	8000	300000	7560
2	540000	6500	450000	6340
3	580000	6460	490000	6250
4	650000	6300	550000	6000
5	730000	6060	610000	5730
6	750000	6000	690000	5300
7	800000	5400	750000	5100
8	840000	5320	780000	5000
9	890000	5130	870000	4700
10	950000	5000	900000	4650
11	1000000	4800	940000	4600
12	1108000	4700	1025000	4540
13
Контакт

В строке «Контакт» в ячейках С8 и D8 должны быть записаны формулы = С7/В7 и =Е7/D7 соответственно, вычисляющие стоимость 1 мин. Эфира для одного слушателя в прогнозируемом месяце. Прогноз нужно выполнить для линейного и экспоненциального приближений и выбрать более достоверный, а также сделать другие пункты Задания.

1. На основании данных ежемесячных исследований известна динамика рейтинга банка (в условных единицах) за последние 6 месяцев в следующих сферах:
2. менеджмент и технология – х₁;
3. менеджеры и персонал – х₂;
4. культура банковского обслуживания – х₃;
5. имидж банка на рынке финансовых услуг – х₄;
6. реклама банка – х₅.

Определить возможное изменение количества вкладчиков данного банка в следующем месяце, если известны значения сфер рейтинга и количество вкладчиков в каждом из рассматриваемых 6 месяцев.

Множественная регрессия в EXCEL

history 26 января 2019 г.

Группы статей
• Статистический анализ

Рассмотрим использование MS EXCEL для прогнозирования переменной Y на основании нескольких переменных Х, т.е. множественную регрессию.

Перед прочтением этой статьи рекомендуется освежить в памяти простую линейную регрессию – прогнозирование на основе значений только одного фактора.

Disclaimer : Данную статью не стоит рассматривать, как пересказ главы из учебника по статистике. Статья не обладает ни полнотой, ни строгостью изложения положений статистической науки. Эта статья – о применении MS EXCEL для целей Множественного регрессионного анализа. Теоретические отступления приведены лишь из соображения логики изложения. Использование данной статьи для изучения Регрессии – плохая идея.

Статья про Множественный регрессионный анализ получилась большая, поэтому ниже для удобства приведены ее разделы:

Прогнозирование единственной переменной Y на основании значений 2-х или более переменных Х называется множественной регрессией .

Множественная линейная регрессионная модель (Multiple Linear Regression Model) имеет вид Y=β ₀ +β ₁ *X ₁ +β ₂ *X ₂ +…+β _k *X _k +ε. В этом случае переменная Y зависит от k поясняющих переменных Х, т.е. регрессоров . ε — случайная ошибка . Модель является линейной относительно неизвестных параметров β.

Оценка неизвестных параметров

В этой статье рассмотрим модель с 2-мя регрессорами. Сначала введем необходимые обозначения и понятия множественной регрессии.

Для описания зависимости Y от 2-х переменных линейная модель имеет вид:

Параметры этой модели β _i нам неизвестны, но их можно оценить, используя случайную выборку (измеренные значения переменной Y от заданных Х). Оценки параметров модели (β ₀ , β ₁ , β ₂ ) обычно вычисляются методом наименьших квадратов (МНК) , который минимизирует сумму квадратов ошибок прогнозирования (критерий минимизации в англоязычной литературе обозначают как SSE – Sum of Squared Errors).

Ошибка ε имеет случайную природу и имеет свою функцию распределения со средним значением =0 и дисперсией σ 2 .

Оценки b ₁ и b ₂ называются коэффициентами регрессии , они определяют влияние соответствующей переменной X, когда все остальные независимые переменные остаются неизменными .

Сдвиг (intercept) или постоянный член b ₀ , определяет прогнозируемое значение Y, когда все поясняющие переменные Х равны 0 (часто сдвиг не имеет физического смысла в рамках модели и обусловлен лишь математическими вычислениями МНК ).

Вычислив оценки, полученные методом МНК, позволяют прогнозировать значения переменной Y:

Примечание : Для случая 2-х регрессоров, все спрогнозированные значения переменной Y будут лежать в плоскости (в плоскости регрессии ).

В качестве примера рассмотрим технологический процесс изготовления нити:

Инженер, на основе имеющегося опыта, предположил, что прочность нити Y зависит от концентрации исходного раствора (Х ₁ ) и температуры реакции (Х ₂ ), и соответствует модели линейной регрессии. Для нахождения комбинации переменных Х, при которых Y принимает максимальное значение, необходимо определить коэффициенты регрессии, сделав выборку.

В MS EXCEL коэффициенты множественной регрессии удобнее всего вычислить с помощью функции ЛИНЕЙН() . Это сделано в файле примера на листе Коэффициенты . Чтобы вычислить оценки:

• выделите 3 ячейки в одной строке (т.к. мы рассматриваем случай 2-х регрессоров, то будут вычислены 2 коэффициента регрессии + величина сдвига = 3 значения, для вывода которых понадобится 3 ячейки). Пусть это будет диапазон С8:Е8 ;
• в Строке формул введите = ЛИНЕЙН(D20:D50;B20:C50) . Предполагается, что в столбце В содержатся прогнозируемые значения Y (в нашей модели это Прочность нити), в столбцах С и D содержатся значения контролируемых параметров Х (Х1 – Концентрация в столбце С и Х2 – Температура в столбце D).
• нажмите CTRL+SHIFT+ENTER (т.к. это формула массива ).

В левой ячейке будет рассчитано значение коэффициента регрессии b 2 для переменной Х2, в средней ячейке — значение коэффициента регрессии b 1 для переменной Х1, в правой – сдвиг . Обратите внимание, что порядок вывода коэффициентов регрессии обратный по отношению к расположению столбцов с данными соответствующих переменных Х (вычисленный коэффициент b 2 располагается левее по отношению к b 1 , тогда как значения переменной Х2 располагаются правее значений переменной Х1). Это может привести к путанице, поэтому лучше разместить коэффициенты над соответствующими столбцами с данными, как это сделано в строке 17 файла примера .

Примечание : В принципе без функции ЛИНЕЙН() можно обойтись, записав альтернативные формулы. Для этого в файле примера на листе Коэффициенты в столбцах I : K вычислены отклонения значений переменных Х _1i , Х _2i , Y _i от их средних значений , т.е.:

Далее коэффициенты регрессии рассчитываются по следующим формулам (эти формулы справедливы только при прогнозировании по 2-м независимым переменным Х):

При прогнозировании по 3-м и более независимым переменным Х формулы для вычисления коэффициентов регрессии значительно усложняются, поэтому следует использовать матричный подход.

В файле примера на листе Матричная форма выполнены расчеты коэффициентов регрессии с помощью матричного подхода.

Расчет можно произвести как пошагово, так и одной формулой массива :

Коэффициенты регрессии (вектор b ) в этом случае вычисляются по формуле b =(X T X) -1 (X T Y) или в другом виде записи b =(X ’ X) -1 (X ’ Y)

Под Х подразумевается матрица, состоящая из столбцов значений переменной Х с дополнительным столбцом единиц, а под Y – вектор-столбец значений Y.

Диаграмма рассеяния

В случае простой линейной регрессии (один регрессор, т.е. одна переменная Х) для визуализации связи между прогнозируемым значением Y и переменной Х строят диаграмму рассеяния (двумерную).

В случае множественной линейной регрессии двумерную диаграмму рассеяния можно построить только для анализа влияния каждого отдельного регрессора на Y (при этом остальные Х не меняются), т.е. так называемую Матричную диаграмму рассеивания (См. файл примера лист Диагр расс (матричная) ).

К сожалению, такую диаграмму трудно интерпретировать.

Более того, матричная диаграмма может вводить в заблуждение (см. Introduction to linear regression analysis / D . C . Montgomery , E . A . Peck , G . G . Vining , раздел 3.2.5 ), демонстрируя наличие или отсутствие линейной взаимосвязи между отдельным регрессором X _i и Y.

Для случая с 2-мя регрессорами можно предложить альтернативный вид матричной диаграммы рассеяния . В стандартной диаграмме рассеяния строятся проекции на координатные плоскости Х1;Х2, Y;X1 и Y;X2. Однако, если взглянуть на точки относительно плоскости регрессии , то картину, на мой взгляд, будет проще интерпретировать.

Сравним две матричные диаграммы рассеяния (см. файл примера на листе «Диагр расс (в плоск регрессии)» , построенные для одних и тех же наблюдений. Первая – стандартная,

вторая представляет собой вид сверху на плоскость регрессии и 2 вида вдоль плоскости.

На второй диаграмме становится очевидно, что разброс точек относительно плоскости регрессии совсем не большой и поэтому, скорее всего, построенная модель является полезной, а выбранные 2 переменные Х позволяют прогнозировать Y (конечно, для подтверждения этой гипотезы нужно провести процедуру F-теста ).

Несколько слов о построении альтернативной матричной диаграммы рассеяния:

• Перед построением необходимо нормировать значения наблюдений (для каждой переменной вычесть среднее и разделить на стандартное отклонение ). В этом случае практически все точки на диаграммах будут находится в диапазоне +/-3 (по аналогии со стандартным нормальным распределением , 99% значений которого лежат в пределах +/-3 сигма). В этом случае, на диаграмме можно фиксировать мин/макс значений осей, чтобы EXCEL автоматически не модифицировал масштаб осей при изменении данных (это не всегда удобно);
• Теперь координаты точек необходимо рассчитать в системе отсчета относительно плоскости регрессии (в которой плоскость Оху’ совпадает с плоскостью регрессии). Для этого необходимо найти матрицу вращения , например, через вращение приводящее к совмещению нормали к плоскости регрессии и вектора оси Z (0;0;1);
• Новые координаты позволяют построить альтернативную матричную диаграмму. Кроме того, для удобства можно вращать систему координат вокруг новой оси Z, чтобы нагляднее представить себе распределение точек относительно плоскости регрессии (для этого использована Полоса прокрутки в ячейках Q31:S31 ).

Вычисление прогнозных значений Y (отдельное наблюдение и среднее значение) и построение доверительных интервалов

После того, как нами были найдены тем или иным способом коэффициенты регрессии можно приступать к вычислению прогнозных значений Y на основе заданных значений переменных Х.

Уравнение прогнозирования или уравнение регрессии в случае 2-х независимых переменных (регрессоров) записывается в виде:

Примечание: В MS EXCEL прогнозное значение Y для заданных Х ₁ и Х ₂ можно также предсказать с помощью функции ТЕНДЕНЦИЯ() . При этом 2-й аргумент будет ссылкой на столбцы, содержащие все значения переменных Х ₁ и Х ₂ , а 3-й аргумент функции должен быть ссылкой на диапазон ячеек, содержащий 2 значения Х (Х _1i и Х _2i ) для выбранного наблюдения i (см. файл примера, лист Коэффициенты, столбец G ). Функция ПРЕДСКАЗ() , использованная нами в простой регрессии, не работает в случае множественной регрессии .

Найдя прогнозное значение Y, мы, таким образом, вычислим его точечную оценку. Понятно, что фактическое значение Y, полученное при наблюдении, будет, скорее всего, отличаться от этой оценки. Чтобы ответить на вопрос о том, на сколько хорошо мы можем предсказывать новые значения Y, нам потребуется построить доверительный интервал этой оценки, т.е. диапазон в котором с определенной заданной вероятностью, скажем 95%, мы ожидаем новое значение Y.

Доверительные интервалы построим при фиксированном Х для:

• нового наблюдения Y;
• среднего значения Y (интервал будет уже, чем для отдельного нового наблюдения)

Как и в случае простой линейной регрессии , для построения доверительных интервалов нам потребуется сначала вычислить стандартную ошибку модели (standard error of the model) , которая приблизительно показывает насколько велика ошибка предсказания значений переменной Y на основании значений переменных Х.

Для вычисления стандартной ошибки оценивают дисперсию ошибки ε, т.е. сигма^2 (ее часто обозначают как MS Е либо MSres ) . Затем, вычислив из полученной оценки квадратный корень, получим Стандартную ошибку регрессии (часто обозначают как SEy или sey ).

где SSE – сумма квадратов значений ошибок модели ei=yi — ŷi ( Sum of Squared Errors ). MSE означает Mean Square of Errors (среднее квадратов ошибок, точнее остатков).

Величина n-p – это количество степеней свободы ( df – degrees of freedom ), т.е. число параметров системы, которые могут изменяться независимо (вспомним, что у нас в этом примере есть n независимых наблюдений переменной Y, р – количество оцениваемых параметров модели). В случае простой множественной регрессии с 2-мя регрессорами число степеней свободы равно n-3, т.к. при построении плоскости регрессии было оценено 3 параметра модели b (т.е. на это было «потрачено» 3 степени свободы ).

В MS EXCEL стандартную ошибку SEy можно вычислить формулы (см. файл примера, лист Статистика ):

Стандартная ошибка нового наблюдения Y при заданных значениях Х (вектор Хi) вычисляется по формуле:

x _i — вектор-столбец со значениями переменных Х (с дополнительной 1) для заданного наблюдения i.

Соответствующий доверительный интервал вычисляется по формуле:

где α (альфа) – уровень значимости (обычно принимают равным 0,05=5%)

р – количество оцениваемых параметров модели (в нашем случае = 3)

n-p – число степеней свободы

– квантиль распределения Стьюдента (задает количество стандартных ошибок , в +/- диапазоне которых вероятность обнаружить новое наблюдение равно 1-альфа). Т.е. если квантиль равен 2, то диапазон шириной +/- 2 стандартных ошибок относительно прогнозного значения Y будет с вероятностью 95% содержать новое наблюдение Y (для каждого заданного Хi). В MS EXCEL вычисления квантиля производят по формуле = СТЬЮДЕНТ.ОБР.2Х(0,05;n-p) , подробнее см. в статье про распределение Стьюдента .

– прогнозное значение Yi вычисляемое по формуле Yi= b 0+ b 1* Х1i+ b 2* Х2i (точечная оценка).

Стандартная ошибка среднего значения Y при заданных значениях Х (вектор Хi) будет меньше, чем стандартная ошибка отдельного наблюдения. Вычисления производятся по формуле:

x _i — вектор-столбец со значениями переменных Х (с дополнительной 1) для заданного наблюдения i.

Соответствующий доверительный интервал вычисляется по формуле:

Прогнозное значение Yi (точечная оценка) используется тоже, что и для отдельного наблюдения.

Стандартные ошибки и доверительные интервалы для коэффициентов регрессии

В разделе Оценка неизвестных параметров мы получили точечные оценки коэффициентов регрессии . Так как эти оценки получены на основе случайных величин (значений переменных Х и Y), то эти оценки сами являются случайными величинами и соответственно имеют функцию распределения со средним значением и дисперсией . Но, чтобы перейти от точечных оценок к интервальным , необходимо вычислить соответствующие стандартные ошибки (т.е. стандартные отклонения ) коэффициентов регрессии .

Стандартная ошибка коэффициента регрессии b _j (обозначается se ( b _j ) ) вычисляется на основании стандартной ошибки по следующей формуле:

где C _jj является диагональным элементом матрицы (X ’ X) -1 . Для коэффициента сдвига b ₀ индекс j=1 (верхний левый элемент), для b ₁ индекс j=2, b ₂ индекс j=3 (нижний правый элемент).

SEy – стандартная ошибка регрессии (см. выше ).

В MS EXCEL стандартные ошибки коэффициентов регрессии можно вычислить с помощью функции ЛИНЕЙН() :

Примечание : Подробнее о функции ЛИНЕЙН() см. статью Функция MS EXCEL ЛИНЕЙН() .

Применяя матричный подход стандартные ошибки можно вычислить и через обычные формулы (точнее через формулу массива , см. файл примера лист Статистика ):

= КОРЕНЬ(СУММКВРАЗН(E13:E43;F13:F43) /(n-p)) *КОРЕНЬ (ИНДЕКС (МОБР (МУМНОЖ(ТРАНСП(B13:D43);(B13:D43)));j;j))

При построении двухстороннего доверительного интервала для коэффициента регрессии его границы определяются следующим образом:

где t – это t-значение , которое можно вычислить с помощью формулы = СТЬЮДЕНТ.ОБР.2Х(0,05;n-p) для уровня значимости 0,05.

В результате получим, что найденный доверительный интервал с вероятностью 95% (1-0,05) накроет истинное значение коэффициента регрессии b _j . Здесь мы считаем, что коэффициент регрессии b _j имеет распределение Стьюдента с n-p степенями свободы (n – количество наблюдений, т.е. пар Х и Y).

Проверка гипотез

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

Единственный вариант, когда Y не зависит X, возможен, когда все коэффициенты регрессии β равны 0.

Чтобы убедиться, что вычисленная нами оценка коэффициентов регрессии не обусловлена лишь случайностью (они не случайно отличны от 0), используют проверку гипотез . В качестве нулевой гипотезы Н ₀ принимают, что линейной связи нет, т.е. ВСЕ β=0. В качестве альтернативной гипотезы Н ₁ принимают, что ХОТЯ БЫ ОДИН коэффициент β 0.

Процедура проверки значимости множественной регрессии, приведенная ниже, является обобщением дисперсионного анализа , использованного нами в случае простой линейной регрессии (F-тест) .

Если нулевая гипотеза справедлива, то тестовая F -статистика имеет F-распределение со степенями свободы k и n — k -1 , т.е. F _{k, n-k-1} :

Проверку значимости регрессии можно также осуществить через вычисление p -значения . В этом случае вычисляют вероятность того, что случайная величина F примет значение F ₀ (это и есть p-значение ), затем сравнивают p-значение с заданным уровнем значимости α (альфа) . Если p-значение больше уровня значимости , то нулевую гипотезу нет оснований отклонить, и регрессия незначима.

В MS EXCEL значение F ₀ можно вычислить на основании значений выборки по вышеуказанной формуле или с помощью функции ЛИНЕЙН() :

В MS EXCEL для проверки гипотезы через p -значение используйте формулу =F.РАСП.ПХ(F ₀ ;k;n-k-1) файл примера лист Статистика , где показано эквивалентность обоих подходов проверки значимости регрессии).

В MS EXCEL критическое значение для заданного уровня значимости F _{1-альфа, k, n-k-1} можно вычислить по формуле = F.ОБР(1- альфа;k;n-k-1) или = F.ОБР.ПХ(альфа;k; n-k-1) . Другими словами требуется вычислить верхний альфа- квантиль F -распределения с соответствующими степенями свободы .

Таким образом, при значении статистики F ₀ > F _{1-альфа, k, n-k-1} мы имеем основание для отклонения нулевой гипотезы.

В программах статистики результаты процедуры F -теста выводят с помощью стандартной таблицы дисперсионного анализа . В файле примера такая таблица приведена на листе Надстройка , которая построена на основе результатов, возвращаемых инструментом Регрессия надстройки Пакета анализа MS EXCEL .

Генерация данных для множественной регрессии с помощью заданного тренда

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

Для решения этой задачи нам потребуется:

• задать значения регрессоров в нужном диапазоне (значения переменных Х);
• задать коэффициенты регрессии ( b );
• задать тренд (вычислить значения Y= b₀ +b₁ * Х ₁ + b₂ * Х ₂ );
• задать величину разброса Y вокруг тренда (варианты: случайный разброс в заданных границах или заданная фигура, например, круг)

Все вычисления выполнены в файле примера, лист Тренд для случая 2-х регрессоров. Там же построены диаграммы рассеяния .

Коэффициент детерминации

Коэффициент детерминации R 2 показывает насколько полезна построенная нами линейная регрессионная модель .

По определению коэффициент детерминации R 2 равен:

R 2 = Изменчивость объясненная моделью ( SSR ) / Общая изменчивость ( SST ).

Этот показатель можно вычислить с помощью функции ЛИНЕЙН() :

При добавлении в модель новой объясняющей переменной Х, коэффициент детерминации будет всегда расти. Поэтому, рост коэффициента детерминации не может служить основанием для вывода о том, что новая модель (с дополнительным регрессором) лучше прежней.

Более подходящей статистикой, которая лишена указанного недостатка, является нормированный коэффициент детерминации (Adjusted R-squared):

где p – число независимых регрессоров (вычисления см. файл примера лист Статистика ).

Уравнение множественной регрессии

Назначение сервиса . С помощью онлайн-калькулятора можно найти следующие показатели:

• уравнение множественной регрессии, матрица парных коэффициентов корреляции, средние коэффициенты эластичности для линейной регрессии;
• множественный коэффициент детерминации, доверительные интервалы для индивидуального и среднего значения результативного признака;

Кроме этого проводится проверка на автокорреляцию остатков и гетероскедастичность.

• Шаг №1
• Шаг №2
• Видеоинструкция
• Оформление Word

Отбор факторов обычно осуществляется в два этапа:

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

Факторы, включаемые во множественную регрессию, должны отвечать следующим требованиям:

1. Они должны быть количественно измеримы. Если необходимо включить в модель качественный фактор, не имеющий количественного измерения, то ему нужно придать количественную определенность.
2. Каждый фактор должен быть достаточно тесно связан с результатом (т.е. коэффициент парной линейной корреляции между фактором и результатом должен быть существенным).
3. Факторы не должны быть сильно коррелированы друг с другом, тем более находиться в строгой функциональной связи (т.е. они не должны быть интеркоррелированы). Разновидностью интеркоррелированности факторов является мультиколлинеарность — тесная линейная связь между факторами.

Пример . Постройте регрессионную модель с 2-мя объясняющими переменными (множественная регрессия). Определите теоретическое уравнение множественной регрессии. Оцените адекватность построенной модели.
Решение.
К исходной матрице X добавим единичный столбец, получив новую матрицу X

1	5	14.5
1	12	18
1	6	12
1	7	13
1	8	14

Матрица Y

9

13

16

14

21

Транспонируем матрицу X, получаем X T :

1	1	1	1	1
5	12	6	7	8
14.5	18	12	13	14

Умножаем матрицы, X T X =

5 38 71,5 38 318 563,5 71,5 563,5 1043,25

В матрице, (X T X) число 5, лежащее на пересечении 1-й строки и 1-го столбца, получено как сумма произведений элементов 1-й строки матрицы X T и 1-го столбца матрицы X

Умножаем матрицы, X T Y =

73 563 1032,5

Находим обратную матрицу (X T X) -1

13.99	0.64	-1.3
0.64	0.1	-0.0988
-1.3	-0.0988	0.14

Вектор оценок коэффициентов регрессии равен

(X T X) -1 X T Y = y(x) =

13,99 0,64 -1,3 0,64 0,1 -0,0988 -1,3 -0,0988 0,14

*

73 563 1032,5

=

34,66 1,97 -2,45

Получили оценку уравнения регрессии: Y = 34.66 + 1.97X₁-2.45X₂
Оценка значимости уравнения множественной регрессии осуществляется путем проверки гипотезы о равенстве нулю коэффициент детерминации рассчитанного по данным генеральной совокупности. Для ее проверки используют F-критерий Фишера.
R 2 = 1 — s 2 _e/∑(y_i — y_ср) 2 = 1 — 33.18/77.2 = 0.57
F = R 2 /(1 — R 2 )*(n — m -1)/m = 0.57/(1 — 0.57)*(5-2-1)/2 = 1.33
Табличное значение при степенях свободы k₁ = 2 и k₂ = n-m-1 = 5 — 2 -1 = 2, Fkp(2;2) = 19
Поскольку фактическое значение F = 1.33 Пример №2 . Приведены данные за 15 лет по темпам прироста заработной платы Y (%), производительности труда X₁ (%), а также по уровню инфляции X₂ (%).

Год	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
X1	3,5	2,8	6,3	4,5	3,1	1,5	7,6	6,7	4,2	2,7	4,5	3,5	5,0	2,3	2,8
X2	4,5	3,0	3,1	3,8	3,8	1,1	2,3	3,6	7,5	8,0	3,9	4,7	6,1	6,9	3,5
Y	9,0	6,0	8,9	9,0	7,1	3,2	6,5	9,1	14,6	11,9	9,2	8,8	12,0	12,5	5,7

Решение. Подготовим данные для вставки из MS Excel (как транспонировать таблицу для сервиса см. Задание №2) .

Включаем в отчет: Проверка общего качества уравнения множественной регрессии (F-статистика. Критерий Фишера, Проверка на наличие автокорреляции),

После нажатия на кнопку Дале получаем готовое решение.
Уравнение регрессии (оценка уравнения регрессии):
Y = 0.2706 + 0.5257X₁ + 1.4798X₂
Скачать.

Качество построенного уравнения регрессии проверяется с помощью критерия Фишера (п. 6 отчета).

Пример №3 .
В таблице представлены данные о ВВП, объемах потребления и инвестициях некоторых стран.

ВВП	16331,97	16763,35	17492,22	18473,83	19187,64	20066,25	21281,78	22326,86	23125,90
Потребление в текущих ценах	771,92	814,28	735,60	788,54	853,62	900,39	999,55	1076,37	1117,51
Инвестиции в текущих ценах	176,64	173,15	151,96	171,62	192,26	198,71	227,17	259,07	259,85

Решение:
Для проверки полученных расчетов используем инструменты Microsoft Excel «Анализ данных» (см. пример).

Пример №4 . На основе данных, приведенных в Приложении и соответствующих Вашему варианту (таблица 2), требуется:

1. Построить уравнение множественной регрессии. При этом признак-результат и один из факторов остаются теми же, что и в первом задании. Выберите дополнительно еще один фактор из приложения 1 (границы наблюдения должны совпадать с границами наблюдения признака-результата, соответствующего Вашему варианту). При выборе фактора нужно руководствоваться его экономическим содержанием или другими подходами. Пояснить смысл параметров уравнения.
2. Рассчитать частные коэффициенты эластичности. Сделать вывод.
3. Определить стандартизованные коэффициенты регрессии (b-коэффициенты). Сделать вывод.
4. Определить парные и частные коэффициенты корреляции, а также множественный коэффициент корреляции; сделать выводы.
5. Оценить значимость параметров уравнения регрессии с помощью t-критерия Стьюдента, а также значимость уравнения регрессии в целом с помощью общего F-критерия Фишера. Предложить окончательную модель (уравнение регрессии). Сделать выводы.

Решение. Определим вектор оценок коэффициентов регрессии. Согласно методу наименьших квадратов, вектор получается из выражения:
s = (X T X) -1 X T Y
Матрица X

1	3.9	10
1	3.9	14
1	3.7	15
1	4	16
1	3.8	17
1	4.8	19
1	5.4	19
1	4.4	20
1	5.3	20
1	6.8	20
1	6	21
1	6.4	22
1	6.8	22
1	7.2	25
1	8	28
1	8.2	29
1	8.1	30
1	8.5	31
1	9.6	32
1	9	36

Матрица Y

7
7
7
7
7
7
8
8
8
10
9
11
9
11
12
12
12
12
14
14

Матрица X T

1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
3.9	3.9	3.7	4	3.8	4.8	5.4	4.4	5.3	6.8	6	6.4	6.8	7.2	8	8.2	8.1	8.5	9.6	9
10	14	15	16	17	19	19	20	20	20	21	22	22	25	28	29	30	31	32	36

Умножаем матрицы, (X T X)

Умножаем матрицы, (X T Y)

Находим определитель det(X T X) T = 139940.08
Находим обратную матрицу (X T X) -1

Уравнение регрессии
Y = 1.8353 + 0.9459X ₁ + 0.0856X ₂
Для несмещенной оценки дисперсии проделаем следующие вычисления:
Несмещенная ошибка e = Y — X*s

0.62
0.28
0.38
0.01
0.11
-1
-0.57
0.29
-0.56
0.02
-0.31
1.23
-1.15
0.21
0.2
-0.07
-0.07
-0.53
0.34
0.57

se 2 = (Y — X*s) T (Y — X*s)
Несмещенная оценка дисперсии равна

Оценка среднеквадратичного отклонения равна

Найдем оценку ковариационной матрицы вектора k = σ*(X T X) -1

k(x) = 0.36

0,619 -0,0262 -0,0183 -0,0262 0,126 -0,0338 -0,0183 -0,0338 0,0102

=

0,222 -0,00939 -0,00654 -0,00939 0,0452 -0,0121 -0,00654 -0,0121 0,00366

Дисперсии параметров модели определяются соотношением S 2 _i = K_ii, т.е. это элементы, лежащие на главной диагонали
С целью расширения возможностей содержательного анализа модели регрессии используются частные коэффициенты эластичности, которые определяются по формуле

Тесноту совместного влияния факторов на результат оценивает индекс множественной корреляции (от 0 до 1)

Связь между признаком Y факторами X сильная
Частные коэффициенты (или индексы) корреляции, измеряющие влияние на у фактора х_i при неизменном уровне других факторов определяются по стандартной формуле линейного коэффициента корреляции — последовательно берутся пары yx1,yx2. , x1x2, x1x3.. и так далее и для каждой пары находится коэффициент корреляции

Коэффициент детерминации
R 2 = 0.97 2 = 0.95, т.е. в 95% случаев изменения х приводят к изменению y. Другими словами — точность подбора уравнения регрессии — высокая

Значимость коэффициента корреляции

По таблице Стьюдента находим Tтабл: T_табл (n-m-1;a) = (17;0.05) = 1.74
Поскольку Tнабл Fkp, то коэффициент детерминации статистически значим и уравнение регрессии статистически надежно

Построение парной регрессионной модели

Рекомендации к решению контрольной работы.

Статистические данные по экономике можно получить на странице Россия в цифрах.
После определения зависимой и объясняющих переменных можно воспользоваться сервисом Множественная регрессия. Регрессионную модель с 2-мя объясняющими переменными можно построить используя матричный метод нахождения параметров уравнения регрессии или метод Крамера для нахождения параметров уравнения регрессии.

Пример №3 . Исследуется зависимость размера дивидендов y акций группы компаний от доходности акций x₁, дохода компании x₂ и объема инвестиций в расширение и модернизацию производства x₃. Исходные данные представлены выборкой объема n=50.

Тема I. Парная линейная регрессия
Постройте парные линейные регрессии — зависимости признака y от факторов x₁, x₂, x₃ взятых по отдельности. Для каждой объясняющей переменной:

1. Постройте диаграмму рассеяния (поле корреляции). При построении выберите тип диаграммы «Точечная» (без отрезков, соединяющих точки).
2. Вычислите коэффициенты уравнения выборочной парной линейной регрессии (для вычисления коэффициентов регрессии воспользуйтесь встроенной функцией ЛИНЕЙН (функция находится в категории «Статистические») или надстройкой Пакет Анализа), коэффициент детерминации, коэффициент корреляции (функция КОРЕЛЛ), среднюю ошибку аппроксимации.
3. Запишите полученное уравнение выборочной регрессии. Дайте интерпретацию найденным в предыдущем пункте значениям.
4. Постройте на поле корреляции прямую линию выборочной регрессии по точкам .
5. Постройте диаграмму остатков.
6. Проверьте статистическую значимость коэффициентов регрессии по критерию Стьюдента (табличное значение определите с помощью функции СТЬЮДРАСПОБР) и всего уравнения в целом по критерию Фишера (табличное значение F_табл определите с помощью функции FРАСПОБР).
7. Постройте доверительные интервалы для коэффициентов регрессии. Дайте им интерпретацию.
8. Постройте прогноз для значения фактора, на 50% превышающего его среднее значение.
9. Постройте доверительный интервал прогноза. Дайте ему экономическую интерпретацию.
10. Оцените полученные результаты — сделайте выводы о качестве построенной модели, влиянии рассматриваемого фактора на показатель.

Тема II. Множественная линейная регрессия
1. Постройте выборочную множественную линейную регрессию показателя на все указанные факторы. Запишите полученное уравнение, дайте ему экономическую интерпретацию.
2. Определите коэффициент детерминации, дайте ему интерпретацию. Вычислите среднюю абсолютную ошибку аппроксимации и дайте ей интерпретацию.
3. Проверьте статистическую значимость каждого из коэффициентов и всего уравнения в целом.
4. Постройте диаграмму остатков.
5. Постройте доверительные интервалы коэффициентов. Для статистически значимых коэффициентов дайте интерпретации доверительных интервалов.
6. Постройте точечный прогноз значения показателя y при значениях факторов, на 50% превышающих их средние значения.
7. Постройте доверительный интервал прогноза, дайте ему экономическую интерпретацию.
8. Постройте матрицу коэффициентов выборочной корреляции между показателем и факторами. Сделайте вывод о наличии проблемы мультиколлинеарности.
9. Оцените полученные результаты — сделайте выводы о качестве построенной модели, влиянии рассматриваемых факторов на показатель.

📺 Видео