Регрессионный анализ в среде Excei

 

2) Проверка значимости коэффициентов  регрессии. Так как выполнены неравенства

то делаем вывод о том, что  коэффициенты b₁, b₀ значимы.

 

3) В ячейку Е15, Е16 мы получили  границы доверительного интервала для генерального коэффициента регрессии β_yx с уровнем значимости α = 0,05:

 

0,177 < β_yx < 1,020.

 

4) Проверка значимости уравнения регрессии (ячейки Е12, Е13):

 

F = 8,47 > F_крит = 4,2.

 

Уравнение значимо с уровнем  доверия 1 – 0,05 = 0,95 = 95%.

5) Построим доверительные границы  для уравнения регрессии. Для  этого построим таблицу значений  для нижней и верхней границ  по формулам (9.11) и (9.12).

Мы будем применять эти формулы  к значениям переменной  x = 0; 0,1; …, 1. Таблицу для вычисления доверительных границ построим в диапазоне F1:M12 (табл. 9.5).

В диапазоне F1:M1 введите обозначения x, Syx, Sy0, Yx, Y01, Yx1, Yx2, Y02.

 

В F2, F3 введем числа 0 и 0,1, затем выделим ячейки  F2:F3 и маркером заполнения протянем вниз до F12.

В G2 введем формулу

 

=E$6*КОРЕНЬ((1/30+(F2-E$4)^2/(29*ДИСП(A$2:A$31))))

 

и маркером заполнения протянем ячейку G2 вниз до G12.

В H2 введем формулу

 

=E$6*КОРЕНЬ((1+1/30+(F2-E$4)^2/(29*ДИСП(A$2:A$31))))

 

и маркером заполнения протянем ячейку H2 вниз до H12.

В ячейках диапазона I2:M2 введем формулы, указанные в табл. 9.4. Затем выделим диапазон  I2:M2 и маркером заполнения протянем вниз до строки 12.

Таблица 9.4

	I	J	K	L	M
1	Yx	Y01	Yx1	Yx2	Y02
2	=E$3+E$2*F2	=I2-E$11*H2	=I2-E$11*G2	=I2+E$11*G2	=I2+E$11*H2

 

В результате получим таблицу 9.5 для  построения доверительных границ.

Чтобы построить график уравнения  регрессии и графики доверительных границ, выделим диапазон F1:F12, затем, удерживая нажатой клавишу Ctrl, выделим диапазон I1:M12 и с помощью «Мастера диаграмм» построим диаграмму «Точечная» (рис.9.1).

Здесь Yx1 и Yx2 обозначают нижнюю и верхнюю доверительные границы для M_x(Y), вычисляемые по формуле (9.11), а Y01 и Y02 обозначают нижнюю и верхнюю доверительные границы для индивидуальных значений y₀, вычисляемые по формуле (9.12).

                                                                Таблица 9.5

	F	G	H	I	J	K	L	M
1	x	Syx	Sy0	Yx	Y01	Yx1	Yx2	Y02
2	0,00	0,036	0,089	3,470	3,288	3,397	3,543	3,652
3	0,10	0,030	0,087	3,530	3,352	3,468	3,592	3,708
4	0,20	0,025	0,085	3,590	3,415	3,538	3,642	3,765
5	0,30	0,021	0,084	3,650	3,477	3,607	3,693	3,822
6	0,40	0,017	0,083	3,710	3,539	3,674	3,745	3,880
7	0,50	0,015	0,083	3,769	3,600	3,738	3,801	3,939
8	0,60	0,015	0,083	3,829	3,659	3,798	3,860	3,999
9	0,70	0,017	0,083	3,889	3,719	3,854	3,925	4,060
10	0,80	0,021	0,084	3,949	3,777	3,906	3,992	4,122
11	0,90	0,025	0,085	4,009	3,834	3,957	4,061	4,184
12	1,00	0,030	0,087	4,069	3,891	4,007	4,131	4,247

 

 

 

Рис. 9.1

 

Решение с помощью  процедуры «Регрессия» из  пакета «Анализ данных».

Выполните команду меню «Сервис—Анализ данных», выберите процедуру «Регрессия» и в появившемся окне (рис.9.2) в строке «Входной интервал Y:» введите диапазон В1:В31, в строке «Входной интервал X:» введите диапазон А1:А31, поставьте флажок в строках «Метки» и «Уровень надежности», в «Параметрах выхода» укажите «Новый рабочий лист» и нажмите «Ок».

 

Рис.9.2

 

Результаты будут выведены на отдельный  лист. Ниже на рис.9.3 приведены результаты.

 

Рис.9.3

Проверка результатов.

Выборочные коэффициенты регрессии b₀, b₁ на рис.9.3 приведены в ячейках В17, В18. Они совпадают со значениями в Е2, Е3.

Остаточная дисперсия (квадрат  стандартной ошибки ) приведена в ячейке D13. Это значение совпадает со значением в Е6.

Стандартные ошибки коэффициентов  регрессии приведены в ячейках  С17, С18. Они совпадают со значениями в Е7, Е8. Значения t-критерия для коэффициентов регрессии приведены в ячейках D17, D18, и совпадают со значениями в Е9, Е10.

Доверительный интервал с уровнем значимости 0,05 для генерального коэффициента регрессии приведен в ячейках F18, G18. Эти границы совпадают со значениями в ячейках Е15, Е16.

Упражнение для самостоятельного выполнения. Проверьте правильность значений R и R², вычислив их значения по формулам (9.21), (9.22).

Пример 9.2. По данным таблицы (табл. 9.7)

1) построить уравнение множественной  линейной регрессии ;

      

                                               Таблица 9.7

i	X1	X2	X3	Y	i	X1	X2	X3	Y
1	1,8	1	3,4	20,5	16	3,4	0,4	3,3	21,3
2	2,3	2,6	0,3	14,1	17	4,5	1,2	4,2	29,7
3	4,6	2	4,3	32,6	18	2,4	2,2	3,6	25,9
4	0,6	1,3	3	17,4	19	0,2	3,3	3,6	24,8
5	0,9	0,2	4,4	20,3	20	2,8	4	4,5	35,7
6	0,4	2,1	0,8	10,7	21	2,8	4,3	3,6	33
7	0,2	1,2	2	12,3	22	3,4	3,8	4,5	36,5
8	0,5	4,6	3,5	28,9	23	2,3	3,7	2	24,1
9	1,4	4,9	0,8	20,8	24	4	3,6	0,3	20,4
10	4,9	0,8	4,1	28,9	25	2,3	0,4	0,5	8
11	4,6	2,5	2,1	25,5	26	4,5	1,4	1,2	18,4
12	0,8	3,8	0,1	13,6	27	2,7	3	0,9	18,3
13	4,7	1,5	4,9	34	28	3,6	4,2	2,9	31,7
14	0,9	3,9	0,2	14,4	29	2,9	2,8	2,9	25,9
15	3,9	1,7	4,9	32,8	30	3,9	3,4	3	30,3

 

2) Проверить для уровня α = 0,05 значимость уравнения регрессии;

3) Проверить значимость коэффициентов регрессии для уровня α =0,05.

Решение. Введите данные из табл.9.7 в программе Excel в диапазоне А1:D31: в ячейках А1, В1, С1 и D1 введите метки переменных X1, X2, X3 и Y, в соответствующих столбцах введите значения переменных.

Выполните команду меню «Сервис — Анализ данных» и в открывшемся окне выберите процедуру «Регрессия». В окне «Регрессия» в строке «Входной интервал Y:» укажите диапазон D1:D31, в строке «Входной интервал X:» введите диапазон А1:С31, в строке «Метки» поставьте флажок (рис.9.4), задайте уровень надежности 95% (1 – 0,05 = 0,95), и нажмите «Ок».

 

Рис.9.4

 

Полученные результаты приведены  на рис.9.5

Выводы.

1) Коэффицинты уравнения регрессии приведены в ячейках В17:В20, уравнение линейной регрессии имеет вид

.

2) В разделе «Дисперсионный анализ» проводится оценка значимости уравнения регрессии. Значение в ячейку F12 практически равно нулю, т.е. это значение меньше уровня 0,05, что говорит о том, что уравнение регрессии значимо.

3) В ячейках D17:D20 приведены значения t-статистики, а в ячейках Е17:Е20 — значимости этих статистик. Так как эти значения также близки нулю, все коэффициенты регрессии значимы с данным уровнем 0,05. Это следует также из того, что доверительные интервалы для коэффициентов не включают ноль.

 

 

 

Рис.9.5

 

Пример 9.3. Из теоретических соображений сделано предположениие, что зависимость показателя Y от переменной X (табл. 9.8) имеет форму многочлена третьей степени.

1) построить уравнение нелинейной  регрессии ;

2) оценить значимость уравнения  регрессии с уровнем 0,01;

3) оценить значимость коэффициентов  уравнения регрессии с уровнем  0,01.

                                               Таблица 9.8

i	X	Y	i	X	Y
1	0,6	5,7	16	5	89
2	3,9	56,8	17	3,4	44,6
3	3,2	40,9	18	2,3	23,8
4	2	19,1	19	1,8	16,8
5	4,1	61,9	20	4,6	76,7
6	3,6	50,1	21	2,5	26,9
7	4,9	85,4	22	0,9	7,7
8	2	19,6	23	2	19
9	2	19,9	24	3,9	56,6
10	1,6	14,2	25	4,9	85,1
11	2,7	31	26	0,8	7,5
12	2,7	30,9	27	3,3	42,7
13	4,1	62,1	28	0,7	6,6
14	2,7	30,7	29	3,9	56,5
15	1,6	14,3	30	1,9	18,5

 

Решение. В ячейках А1:D1 введите метки X, X^2, X^3 и Y.

Введите значения переменной X в диапазоне А2:А31.

В ячейке В2 введите формулу =A2^2, в ячейке С2 формулу =A2^3. Ячейки В2:С2 протяните маркером заполнения вниз до В31:С31.

Введите значения переменной Y в диапазоне D2:D31.

Выполните команду меню «Сервис — Анализ данных» и в открывшемся окне выберите процедуру «Регрессия». В окне «Регрессия» в строке «Входной интервал Y:» укажите диапазон D1:D31, в строке «Входной интервал X:» введите диапазон А1:С31, в строке «Метки» поставьте флажок, задайте уровень надежности 99% , и нажмите «Ок».

На рис. 9.6 приведены результаты. Уравнение имеет вид:

 

.

 

Значимость F-статистики меньше уровня 0,01, следовательно, уравнение значимо.

Однако  не все коэффициенты регрессии значимы. P-значение для коэффициента b₃ в строке Е20 равно 0,69, т.е. больше уровня 0,01. Кроме того, доверительный интервал для коэффициента b₃ включает ноль. Это означает, что коэффициент при x³ в уравнении не является значимым.

 

 

Рис.9.6

 

Необходимо  исключить из уравнения регрессии  слагаемое с x³.

Выполните команду меню «Сервис — Анализ данных» и в открывшемся окне выберите процедуру «Регрессия». В окне «Регрессия» в строке «Входной интервал Y:» укажите диапазон D1:D31, в строке «Входной интервал X:» введите диапазон А1:В31, в строке «Метки» поставьте флажок, задайте уровень надежности 99% , и нажмите «Ок».

На рис. 9.7 приведены результаты. Уравнение параболы имеет вид:

.

 

Значимость F-статистики меньше уровня 0,01, следовательно, уравнение значимо.

Все коэффициенты регрессии значимы, так как P-значение для каждого коэффициента меньше уровня 0,01.

 

Рис.9.7