Здавалка
Главная | Обратная связь

Лабораторная работа № 2. Модель парной регрессии. Метод наименьших квадратов

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

Основные формулы и понятия:

у = a + b×х + u — модели парной линейной регрессии;

y = а + b×x — уравнение линейной регрессии;

— значение наблюдений;

остаток в i-м наблюдении;

расчетное значение у в i-м наблюдении (точечный прогноз);

) — суммы квадратов остатков;

уравнения для параметров регрессии;

— общая сумма квадратов отклонений;

объясненная сумма квадратов отклонений;

необъясненная (остаточная) сумма квадратов отклонений;

— коэффициент детерминации.

 

Для парного регрессионного анализа выполняется условие: коэффициент детерминации R2 равен квадрату коэффициента корреляции, то есть

Электронная таблица MS Excel

Ранее изученных нами статистических функций вполне достаточно для непосредственного вычисления коэффициентов регрессии. Для нахождения значения параметра b достаточно уметь вычислять значение ковариации и дисперсии, а для значения a необходимы также средние значения. Эти параметры можно легко найти самостоятельно, однако в электронной таблице Excel имеется много достаточно разнородных инструментов для определения параметров регрессии. Среди них, что совершенно очевидно, имеются статистические функции, а также дополнительные средства — это надстройка и средства точечных диаграмм. Начнем рассмотрение со статистических функций.

Функция НАКЛОНвозвращает наклон (коэффициент b в уравнении линейной регрессии). При этом аргументами являются два массива, в первом из которых задаются значения зависимой переменной y, а во втором значения регрессора x.

Значение коэффициента a может быть найдено либо по соответствующей формуле, либо при помощи функции ОТРЕЗОК, которая имеет подобные аргументы.

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

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

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

b= = КОВАР(C2:C16;B2:B16)/ДИСПР(B2:B16)
b= =НАКЛОН(C2:C16;B2:B16)
a= = СРЗНАЧ(C2:C16)- НАКЛОН(C2:C16;B2:B16)* СРЗНАЧ(B2:B16)
a= =ОТРЕЗОК(C2:C16;B2:B16)
R2= =КОРРЕЛ(C2:C16;B2:B16)* КОРРЕЛ(C2:C16;B2:B16)
Прогноз при x=17 =ПРЕДСКАЗ(17;C2:C16;B2:B16)

 

В данном случае предлагаются два способа вычисления параметров: на основании формул НАКЛОН и ОТРЕЗОК и через исходные формулы для параметров регрессии.

Вычисленные на основании этих формул значения будут равны:

b = –7,703

a = 239,96

R2 = 0,7868.

При цене, равной 17, прогнозируемый спрос будет равен 109,014.

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

Поскольку b = –7,703, то можно предполагать, что увеличение цены на единицу в среднем уменьшает спрос на –7,703 тысячи штук, аналогично уменьшение цены на единицу увеличит спрос на –7,703 тысячи штук.

Значение константы в регрессионной модели равно 239,96, следовательно, именно такой должен быть спрос при цене равной нулю. Однако данное значение является во многом теоретическим и показывает только точку пересечения линии регрессии с осью oy.

Регрессионная модель имеет вид: y = 239,96 – 7,703x.

Прогнозируемый спрос при цене равной 17 будет составлять 109,014 тысячи единиц.

Коэффициент детерминации равен 0,7868. Данное значение может быть интерпретировано следующим образом: изменение зависимой переменной, в данном случае y на 78 %, описывается изменением независимой переменной (регрессора) x, что говорит о достаточной обоснованности использования данной модели.

Замечание. Описанные выше функции возвращают один параметр линейной регрессии. Однако имеется функция, которая одновременно возвращает оба параметра. Это функция ЛИНЕЙН(). Более подробно с данной функцией можно ознакомится по справочной системе.

Кроме указанных функций в Excel имеется возможность построить на диаграмме линию регрессии, которая называется линией линейного тренда. Для этого необходимо задать точечную диаграмму (диаграмма обязательно должна быть точечной), и выбрав произвольную точку в контекстном меню, можно выбрать пункт Добавить линию тренда. Хотя термин «тренд» имеет несколько другой смысл, применительно к временным рядам, в данном случае термины «тренд» и «линия регрессии» будем отождествлять друг с другом. Выбор пункта Добавить линию тренда приведет к появлению диалогового окна, у которого имеются две закладки — Тип и Параметры (рисунок 6).


 

       
 
   
 

 


Рисунок 6 Построение линий тренда

 

На закладке Тип необходимо выбрать один из возможных видов уравнения регрессии. Если на диаграмме имеется несколько рядов точек, то линию регрессии можно построить для любой, задав значение соответствующего параметра — Построить на ряде.

На закладке Параметры можно задать дополнительную информацию, которая будет присутствовать на диаграмме. Во-первых, это возможность прогнозирования, что позволит построить линии тренда вперед или назад на соответствующее число единиц. Опция Показывать уравнение на диаграмме позволяет выдавать вид уравнения, а опция Поместить на диаграмму величину достоверности аппроксимации (R^2) выводит значение коэффициента детерминации. Построив точечную диаграммы для данных, заданных в таблице 1, и линию тренда, можно получить диаграмму, которая изображена на рисунке 7.

 

 

Рисунок 7 Линия тренда

 

В данном случае результаты полностью совпадают с полученными ранее посредством статистических функций.

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

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

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

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

 

Рисунок 4 Окно Регрессия

 

Если Входной интервал Y определить как C1:C16, а Входной интервал X B1:B16, задать некоторым образом параметры вывода, а также установить опцию Метки, то автоматически на новом листе будет сгенерированна таблица 4.

 


 

Таблица 4 Итоговая таблица

ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R 0,887036
R-квадрат 0,786833
Нормированный R-квадрат 0,770435
Стандартная ошибка 2,264609
Наблюдения

Дисперсионный анализ        
df SS MS F Значимость F
Регрессия 246,0889 246,0889 47,985 1,04E–05
Остаток 66,66991 5,128455    
Итого 312,7588      
           
Коэффициенты Стандартная ошибка t- статистика P- значение Нижние 95 % Верхние 95 %
Y-пересечение 240,142 17,70861 13,56075 4,76E–09 201,8849 278,3991
Цена x (т.) –7,71453 1,113671 –6,92712 1,04E–05 –10,1205 –5,30859

 

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

¾ регрессионная статистика

¾ дисперсионный анализ

¾ коэффициенты.

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

Рассмотрим раздел дисперсионный анализ.

В столбце SS выдаются все виды сумм квадратов отклонений. В данном случае в первой строке, которая соответствует надписи Регрессия, выдается объясненная сумма квадратов отклонений RSS,

В строке — Остаток — выдается необъясненная (остаточная) сумма квадратов отклонений ESS,

В строке — Итого — выдается общая сумма квадратов отклонений TSS.

В последнем разделе, который не имеет названия, будет интерпретироваться как раздел — коэффициенты, содержится полная информация по коэффициентам. Рассмотрим значения, полученные в столбце Коэффициенты. Пункт Y-пересечение выдает значение коэффициента a. Пункт Цена x (т.) выдает значение коэффициента b.

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

В диалоговом окне Регрессияимеется целый раздел переключателей для получения дополнительной информации по остаткам. Например,указав опцию Остатки, наряду со стандартной таблицей регрессии будет выдана дополнительная таблица (табл. 5) следующего вида:

Таблица 5 Дополнительная таблица

 

ВЫВОД ОСТАТКА  
Наблюдение Предсказанное Спрос y (тыс. шт.) Остатки
123,7511 1,426776
122,7896 1,019821
122,2914 –1,11646
120,6462 –3,7319
120,2544 –0,39014
119,6494 –1,5813
119,0288 4,559903
117,4316 –0,34387
117,2931 –1,12322
117,0864 1,257187
114,353 1,847847
114,1298 –2,67328
112,0989 3,003645
111,4176 –1,31194
110,8662 –0,84306

 

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

Использование трех описанных нами инструментов исследования можно рассматривать как последовательные шаги в изучении парной регрессионной модели. При использовании статистических функций можно получить только уравнение регрессии и некоторый прогноз. Использование точечной диаграммы позволяет сразу увидеть уравнение регрессии, а также получить значение коэффициента детерминации. Точечная диаграмма может позволить и визуально оценить точность построенной модели. И, наконец, надстройка — Регрессия. Используя данный инструмент можно получить полную информацию относительно регрессионной модели. Данная таблица достаточно громоздкая, могут появиться затруднения с интерпретацией полученных результатов. Поэтому рекомендуется начинать исследование модели с использования статистических функций и линии тренда на точечной диаграмме.

 

 

1. Для начальных данных, представленных в таблице 1, найти значение параметров регрессии между y и x1, используя функции дисперсии, ковариации и среднего.

2. Найдите коэффициент корреляции, а также полную информацию по регрессионной модели между значениями y и x1, y и x2, y и x3 (данные взять из таблицы для лабораторной работы № 1—8);

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

Варианты заданий

Вариант 1

Номер наблюдения Цена x1 (т.) Цена на первый подобный товар x2 (т.) Цена на второй подобный товар x3 (т.) Средний доход населения x4 (т. т.) Спрос y (тыс.шт.)
15,09т. 24,30т. 12,85т. 5,09 125,1779
15,21т. 26,65т. 12,26т. 5,03 123,8094
15,28т. 25,22т. 13,42т. 4,80 121,175
15,49т. 26,59т. 12,05т. 4,95 116,9143
15,54т. 26,88т. 12,70т. 4,88 119,8643
15,62т. 24,74т. 12,41т. 4,96 118,0681
15,70т. 24,42т. 13,83т. 5,10 123,5887
15,91т. 25,79т. 13,10т. 4,90 117,0877
15,92т. 24,14т. 13,07т. 4,72 116,1699
15,95т. 26,70т. 12,40т. 4,81 118,3436
16,31т. 24,66т. 12,82т. 4,95 116,2008
16,33т. 24,04т. 12,48т. 4,88 111,4565
16,60т. 25,15т. 13,20т. 5,02 115,1026
16,69т. 24,10т. 12,40т. 4,80 110,1056
16,76т. 24,49т. 12,01т. 4,85 110,0231

Вариант 2

Номер наблюдения Цена x1 (т.) Цена на первый подобный товар x2 (т.) Цена на второй подобный товар x3 (т.) Средний доход населения x4 (т. т.) Спрос y (тыс.шт.)
93,82т. 75,98т. 124,14т. 5,093176 135,5503
91,01т. 74,26т. 130,59т. 5,031956 171,1037
95,96т. 75,51т. 119,98т. 4,79949 106,8294
98,99т. 72,00т. 117,02т. 4,95135 82,65364
98,85т. 71,31т. 124,77т. 4,877776 107,9813
99,58т. 73,48т. 119,30т. 4,961544 87,08134
90,14т. 72,40т. 119,50т. 5,098437 140,1648
94,07т. 77,10т. 116,61т. 4,899322 107,1392
98,63т. 70,25т. 121,64т. 4,722132 96,99346
91,39т. 79,16т. 133,30т. 4,810111 175,2817
92,45т. 72,07т. 121,98т. 4,948579 134,0831
90,45т. 75,34т. 112,02т. 4,884198 111,3925
90,32т. 72,00т. 116,68т. 5,01854 128,979
91,64т. 70,07т. 118,36т. 4,801321 124,8081
92,20т. 74,12т. 121,55т. 4,848674 132,8335

Вариант 3

Номер наблюдения Цена x1 (т.) Цена на первый подобный товар x2 (т.) Цена на второй подобный товар x3 (т.) Средний доход населения x4 (т. т.) Спрос y (тыс.шт.)
31,91т. 22,99т. 43,98т. 5,09т. 74,73
30,50т. 22,13т. 44,91т. 5,03т. 77,73
32,98т. 22,76т. 42,49т. 4,80т. 66,26
34,50т. 21,00т. 41,38т. 4,95т. 55,45
34,42т. 20,65т. 44,15т. 4,88т. 51,00
34,79т. 21,74т. 42,22т. 4,96т. 53,51
30,07т. 21,20т. 42,30т. 5,10т. 84,14
32,04т. 23,55т. 41,24т. 4,90т. 76,03
34,32т. 20,13т. 43,14т. 4,72т. 54,02
30,69т. 24,58т. 44,98т. 4,81т. 86,40
31,23т. 21,03т. 43,27т. 4,95т. 73,19
30,23т. 22,67т. 40,28т. 4,88т. 82,31
30,16т. 21,00т. 41,27т. 5,02т. 77,21
30,82т. 20,04т. 41,86т. 4,80т. 67,53
31,10т. 22,06т. 43,11т. 4,85т. 74,15

Вариант 4

Номер наблюдения Цена x1 (т.) Цена на первый подобный товар x2 (т.) Цена на второй подобный товар x3 (т.) Средний доход населения x4 (т. т.) Спрос y (тыс.шт.)
43,22т. 75,98т. 30,90т. 5,09 166,95
44,44т. 60,87т. 38,75т. 5,03 114,21
43,87т. 69,06т. 36,93т. 4,80 141,13
44,81т. 69,51т. 33,18т. 4,95 139,50
41,19т. 62,12т. 34,06т. 4,88 131,81
41,28т. 65,40т. 46,54т. 4,96 142,16
40,48т. 73,34т. 41,61т. 5,10 178,34
40,63т. 62,76т. 35,35т. 4,90 140,91
40,95т. 64,90т. 37,86т. 4,72 145,70
43,00т. 66,99т. 47,44т. 4,81 142,50
44,27т. 66,19т. 32,51т. 4,95 133,54
41,37т. 69,24т. 30,30т. 4,88 156,54
40,41т. 76,86т. 44,63т. 5,02 183,50
40,53т. 77,57т. 48,57т. 4,80 182,00
41,13т. 73,05т. 35,98т. 4,85 167,27

Вариант 5

Номер наблюдения Цена x1 (т.) Цена на первый подобный товар x2 (т.) Цена на второй подобный товар x3 (т.) Средний доход населения x4 (т. т.) Спрос y (тыс.шт.)
38,37т. 103,23т. 16,02т. 5,093 422,9505
31,30т. 103,39т. 13,17т. 5,032 436,158
33,97т. 113,87т. 18,58т. 4,799 534,9022
49,18т. 102,34т. 17,61т. 4,951 295,2635
41,26т. 109,05т. 12,41т. 4,878 251,038
39,58т. 107,07т. 14,32т. 4,962 339,7103
47,66т. 104,95т. 18,69т. 5,098 371,124
44,20т. 106,14т. 13,16т. 4,899 234,9415
32,62т. 116,04т. 18,36т. 4,722 543,2922
42,10т. 102,21т. 17,88т. 4,810 397,1158
32,90т. 117,34т. 16,07т. 4,949 494,8871
42,54т. 117,20т. 19,98т. 4,884 458,4898
38,34т. 113,35т. 11,59т. 5,019 283,1757
34,20т. 113,15т. 15,44т. 4,801 439,8954
34,42т. 112,66т. 16,68т. 4,849 478,3711

Вариант 6

Номер наблюдения Цена x1 (т.) Цена на первый подобный товар x2 (т.) Цена на второй подобный товар x3 (т.) Средний доход населения x4 (т. т.) Спрос y (тыс.шт.)
25,73т. 51,96т. 13,98т. 5,093 132,775
21,51т. 48,51т. 14,91т. 5,032 170,105
28,95т. 51,02т. 12,49т. 4,799 100,926
33,49т. 44,00т. 11,38т. 4,951 60,666
33,27т. 42,61т. 14,15т. 4,878 60,598
34,38т. 46,96т. 12,22т. 4,962 49,481
20,22т. 44,81т. 12,30т. 5,098 188,878
26,11т. 54,20т. 11,24т. 4,899 128,269
32,95т. 40,51т. 13,14т. 4,722 69,717
22,08т. 58,31т. 14,98т. 4,810 170,243
23,68т. 44,13т. 13,27т. 4,949 153,002
20,68т. 50,68т. 10,28т. 4,884 175,995
20,49т. 44,01т. 11,27т. 5,019 177,151
22,46т. 40,15т. 11,86т. 4,801 156,506
23,29т. 48,24т. 13,11т. 4,849 152,925

Вариант 7

Номер наблюдения Цена x1 (т.) Цена на первый подобный товар x2 (т.) Цена на второй подобный товар x3 (т.) Средний доход населения x4 (т. т.) Спрос y (тыс.шт.)
13,22т. 37,99т. 8,09т. 5,093 86,71655
14,44т. 30,44т. 8,88т. 5,032 75,80126
13,87т. 34,53т. 8,69т. 4,799 81,042
14,81т. 34,76т. 8,32т. 4,951 81,18657
11,19т. 31,06т. 8,41т. 4,878 80,03161
11,28т. 32,70т. 9,65т. 4,962 82,30865432
10,48т. 36,67т. 9,16т. 5,098 99,35052
10,63т. 31,38т. 8,53т. 4,899 87,88665
10,95т. 32,45т. 8,79т. 4,722 89,09822
13,00т. 33,50т. 9,74т. 4,810 86,62127
14,27т. 33,09т. 8,25т. 4,949 82,73234
11,37т. 34,62т. 8,03т. 4,884 89,24696
10,41т. 38,43т. 9,46т. 5,019 92,89391
10,53т. 38,79т. 9,86т. 4,801 89,47333
11,13т. 36,53т. 8,60т. 4,849 88,76572

Вариант 8

Номер наблюдения Цена x1 (т.) Цена на первый подобный товар x2 (т.) Цена на второй подобный товар x3 (т.) Средний доход населения x4 (т. т.) Спрос y (тыс.шт.)
161,46т. 229,91т. 115,93т. 5,093 236,742
153,02т. 221,28т. 119,66т. 5,032 238,461
167,89т. 227,55т. 109,97т. 4,799 216,445
176,97т. 209,99т. 105,51т. 4,951 175,139
176,54т. 206,54т. 116,60т. 4,878 165,085
178,75т. 217,40т. 108,89т. 4,962 178,676
150,43т. 212,02т. 109,21т. 5,098 240,345
162,22т. 235,49т. 104,97т. 4,899 243,789
175,90т. 201,27т. 112,57т. 4,722 164,415
154,16т. 245,78т. 119,92т. 4,810 276,114
157,35т. 210,33т. 113,08т. 4,949 216,811
151,36т. 226,69т. 101,11т. 4,884 252,094
150,97т. 210,02т. 105,07т. 5,019 227,378
154,92т. 200,37т. 107,43т. 4,801 199,538
156,59т. 220,59т. 112,43т. 4,849 229,503

Вариант 9

Номер наблюдения Цена x1 (т.) Цена на первый подобный товар x2 (т.) Цена на второй подобный товар x3 (т.) Средний доход населения x4 (т. т.) Спрос y (тыс.шт.)
10,51т. 9,29т. 21,20т. 5,093 1028,181
10,31т. 9,78т. 18,17т. 5,032 1033,412
10,92т. 9,55т. 19,81т. 4,799 981,1834
11,00т. 9,92т. 19,90т. 4,951 994,5438
10,15т. 8,48т. 18,42т. 4,878 1018,348
10,16т. 8,51т. 19,08т. 4,962 1026,376
11,96т. 8,19т. 20,67т. 5,098 958,8783
11,48т. 8,25т. 18,55т. 4,899 959,321
11,87т. 8,38т. 18,98т. 4,722 925,0467
11,86т. 9,20т. 19,40т. 4,810 938,6315
11,49т. 9,71т. 19,24т. 4,949 970,6546
11,49т. 8,55т. 19,85т. 4,884 958,5316
10,72т. 8,16т. 21,37т. 5,019 1005,377
10,42т. 8,21т. 21,51т. 4,801 997,5462
10,91т. 8,45т. 20,61т. 4,849 981,2018

Вариант 10

Номер наблюдения Цена x1 (т.) Цена на первый подобный товар x2 (т.) Цена на второй подобный товар x3 (т.) Средний доход населения x4 (т. т.) Спрос y (тыс.шт.)
192,56т. 299,70т. 96,02т. 5,093 152,0249
181,94т. 300,17т. 93,17т. 5,032 250,91
185,95т. 331,60т. 98,58т. 4,799 232,7626
208,78т. 297,01т. 97,61т. 4,951 8,0859
196,90т. 317,14т. 92,41т. 4,878 45,77114
194,37т. 311,22т. 94,32т. 4,962 110,6561
206,49т. 304,86т. 98,69т. 5,098 19,61478
201,30т. 308,41т. 93,16т. 4,899 3,427142
183,93т. 338,12т. 98,36т. 4,722 247,5139
198,14т. 296,62т. 97,88т. 4,810 86,62103
184,35т. 342,02т. 96,07т. 4,949 238,8057
198,80т. 341,61т. 99,98т. 4,884 93,84997
192,52т. 330,05т. 91,59т. 5,019 110,0219
186,30т. 329,45т. 95,44т. 4,801 204,7953
186,63т. 327,97т. 96,68т. 4,849 209,9875

 





©2015 arhivinfo.ru Все права принадлежат авторам размещенных материалов.