Статистическая значимость коэффициентов

Ход работы

Имеются следующие исходные данные:

Таблица 1

Исходные данные

Y	X
12,110	3,650
12,300	3,820
13,820	3,760
14,840	5,240
15,860	5,030
16,410	5,520
17,800	5,620
18,610	6,980
19,570	6,910
21,260	7,950
21,080	7,240
22,990	9,270
23,430	8,460
24,630	10,300
25,410	10,720
	10,050

Где  - прогнозное значение фактора x.

Исходные данные формируются в первых двух столбцах A3:B17. Блок промежуточный данных – C3:Q17. Прогнозные значения вычисляются в 18 ряде.

Для нахождения средних значений столбцов отводим 21 ряд. Используем встроенную функцию СРЗНАЧ.

Для нахождения сумм столбцов отводим 24 ряд и используем встроенную функцию СУММ.

Нахождение оценок параметров

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

 необходимо вычислить параметры модели – а,b.

Параметры модели вычисляются по формулам:

Значение параметра а находится в ячейке В31. Формула имеет вид =(B21*A21-C21)/(B21^2-E21)

a= 1,868585;

Значение параметра b находится в ячейке В32. Формула имеет вид =A21-B21*B31

b= 6,158884

Также найдем оценки параметров с помощью функции ЛИНЕЙН(A3:A17;B3:B17;1;1). 

Таблица 2

Результат работы функции ЛИНЕЙН

	S
	Степени  свободы
SST	SSE

Таблица 3

Результат работы функции ЛИНЕЙН для данной задачи

 

1,868585	6,158884
0,106409	0,751154
0,959548	0,918571
308,3679	13
260,1921	10,96903

 

Расчет теоретических значений У

Рассчитываем теоретические значения У по формуле в ячейках F3:F17. В ячейку F3 вводим формулу =$B$31*B3+$B$32.

Для прогнозного значения  х₁₆= 10,050 найдем значение y по формуле у_р=а* х_р+b

у_р= 24,938.

 

Выяснение тесноты связи между у и х

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

-построение точечной даиграммы:

Рис.1 – Точечная диаграмма

- использование коэффициента корреляции r

(-1<=r<=1)

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

- Встроенная функция Excel КОРРЕЛ(A3:B17);

r₁= 0,979565

- Линейный коэффициент корреляции:

а)

r₂= 0,979565

б)

r₃= 0,979565

Так как коэффициент корреляции при вычислении всеми тремя способами равен r=0,979565, то между у и х наблюдается тесная линейная связь.

Нахождение коэффициента детерминации

Для парной линейной регрессии:  

Значение коэффициента детерминации находится в ячейке В34 и В35.

=0,959548 при вычислении первым и вторым способом.

Индекс корреляции , R=0,97956 находится в ячейке B36.

Статистическая значимость коэффициентов

Рассчитывается t-статистика Стьюдента:

Значение t_расч находится в ячейке В38.

t_расч =17,56041

В ячейку В37 t_крит находится при помощи встроенной функции СТЬЮДРАСПОБР (0,025;13)

t_крит = 2,532638;

Если ,то параметр а статистически значимый.