Построение эмпирических формул методом наименьших квадратов

 

 

Для линейной и экспоненциальной функции формула имеет вид:

                                                                                                                       (16)

Для параболы формула F- критерия будет:

                                                                                                                         (17)

Стандартная ошибка коэффициента регрессии  определяется по формуле:

                                                

                                                                                                                     (18)

Стандартная ошибка параметра  :

 

                                                                                                          (19)

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

 

                                                                                                   (20)

                     

                                                                                              (21)

 

 

                                                                                                 (22)

 

 

4.Таблицы, выполненные средствами Microsoft Excel, с пояснениями.

 

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

Рис.3.Вычисления вспомогательных  сумм (продолжение).

 

 

Пояснения к расчётам:

Шаг 1.В ячейки A3:A16 заносим значения у

Шаг 2.В ячейки B3:B16 заносим значения x

Шаг 3.В ячейку C3 вводим формулу =B3^2

Шаг 4.В ячейки С4:С16 эта формула копируется

Шаг 5.В ячейку D3 вводим формулу B3*A3

Шаг 6.В ячейки D4:D16 эта формула копируется

Шаг 7.В ячейку E3 вводим формулу B3^3

Шаг 8.В ячейки E4:E16 эта формула копируется

Шаг 9.В ячейку F3 вводим формулу =B3^4

Шаг 10.В ячейки F4:F16 эта формула копируется

Шаг 11.В ячейку G3 вводим формулу =B3^2*A3

Шаг 12.В ячейки G4:G16 эта формула копируется

Шаг 13.В ячейку H3 вводим формулу =LN(A3)

Шаг 14.В ячейки H4:H16 эта формула копируется

Шаг 15.В ячейку I3 вводим формулу =B3*LN(A3)

Шаг 16.В ячейки I4:I16 эта формула копируется

Шаг 17.В ячейки A3:A16 заносим значения у

Шаг 18.В ячейки B3:B16 заносим значения x

Шаг 19.В ячейку J3 вводим формулу =(B3-$B$19)*(A3-$A$19)

Шаг 20.В ячейки J4:J16 эта формула копируется

Шаг 21.В ячейки K3 вводим формулу =(B3-$B$19)^2

Шаг 22.В ячейки K4:K16 эта формула копируется

Шаг 23. В ячейку L3 вводим формулу =(A3-$A$19)^2

Шаг 24.В ячейки L4:L16 эта формула копируется

Шаг 25.В ячейку M3 вводим формулу =(A3-($E$26+$E$27*B3))^2

Шаг 26.В ячейки M4:M16 эта формула копируется

Шаг 27.В ячейку N3 вводим формулу =(A3-($F$40+$F$41*B3+$F$42*B3^2))^2

Шаг 28.В ячейки N4:N16 эта формула копируется

Шаг 29.В ячейку O3 вводим формулу ==(LN(A3)-$H$18)^2

Шаг 30.В ячейки O4:O16 эта формула копируется

Шаг 31.В ячейку P3 вводим формулу =(H3-(LN($E$56*EXP($E$55*B3))))^2

Шаг 32.В ячейки P4:P16 эта формула копируется

Последующие шаги делаем с помощью  автосуммирования

Шаг 33.В ячейку A17 вводим формулу =СУММ(A3:A16)

Шаг 34.В ячейку B17 вводим формулу =СУММ(B3:B16)

Шаг 35.В ячейку C17 вводим формулу =СУММ(C3:C16)

Шаг 36.В ячейку D17 вводим формулу =СУММ(D3:D16)

Шаг 37.В ячейку E17 вводим формулу =СУММ(E3:E16)

Шаг 38.В ячейку F17 вводим формулу =СУММ(F3:F16)

Шаг 39.В ячейку G17 вводим формулу =СУММ(G3:G16)

Шаг 40.В ячейку H17 вводим формулу =СУММ(H3:H16)

Шаг 41.В ячейку I17 вводим формулу =СУММ(I3:I16)

Шаг 42.В ячейку J17 вводим формулу =СУММ(J3:J16)

Шаг 43.В ячейку K17 вводим формулу =СУММ(K3:K16)

Шаг 44.В ячейку L17 вводим формулу =СУММ(L3:L16)

Шаг 45.В ячейку M17 вводим формулу =СУММ(M3:M16)

Шаг 46.В ячейку N17 вводим формулу =СУММ(N3:N16)

Шаг 47.В ячейку O17 вводим формулу =СУММ(O3:O16)

Шаг 48.В ячейку P17 вводим формулу =СУММ(P3:P16)

Далее вычисляем среднее значение:

Шаг 49.В ячейку B18 вводим формулу =СЧЁТ(B3:B16)

Шаг 50.В ячейку A19 вводим формулу =A17/$B$18

Шаг 51.В ячейку B19 вводим формулу =B17/$B$18

Шаг 52.В ячейку H18 вводим формулу =H17/$B$18

На рис.4 представлены расчёты для  линейной аппроксимации.

9,00	100,00	30996,00	Линейная
100,00	1596,00	313046,00	аппроксимация
			=	-0,98
Обратная матрица			=	0,95
0,365719523	-0,02		a1=	4162,47
-0,02	0,00		a2=	-64,66
Fтабл=	5,59	Fлин>Fтабл
Fлин=	143,1443593	Уравнение значимо
Doct=	14163,46249	tтабл=	2,306
Sa1=	71,97	ta1=	57,8352	ta1>tтабл	значим
Sa2=	5,40	ta2=	11,9643	ta2>tтабл	значим

 

Рис.4 Фрагмент рабочего листа  MS Excel для линейной аппроксимации.

Шаг 53.В ячейку A22 вводим формулу =$B$18

Шаг 54.В ячейку B22 вводим формулу =B17

Шаг 55.В ячейку A23 вводим формулу =B17

Шаг 56.В ячейку B23 вводим формулу =C17

Шаг 57.В ячейку C22 вводим формулу =A17

Шаг 58.В ячейку C23 вводим формулу =D17

Шаг 59.В ячейку A26:B27 вводим формулу =МОБР(A22:B23)

Шаг 60.В ячейку E26:E27 вводим формулу =МУМНОЖ(A55:B56;C51:C52)

Шаг 61.В ячейку E24 вводим формулу =J17/(K17*L17)^(1/2)

Шаг 62.В ячейку E25 вводим формулу =1-M17/17

Шаг 63.В ячейку B29 вводим число 5,59 (определено по a=0,05,df=1 и df2=7 в табл.П1 из приложения 1.)

Шаг 64.В ячейку B30 вводим формулу =E25*(B18/2)-(1-E25)

Шаг 65.В ячейку B31 вводим формулу =M17/(B18-2)

Шаг 66.В ячейку B32 вводим формулу =((B31*C17)/(B18*K17)^(1/2)

Шаг 67.В ячейку B33 вводим формулу =(B3/K17)^(1/2)

Шаг 68.В ячейку C30 вводим формулу =ЕСЛИ(B30>B29;»Уравнение значимо»;»Уравнение не значимо»)

Шаг 69.В ячейку D31 вводим число 2.3060 (определено по a=0,05 df=1 и df2=8 в табл.П2 из приложения 2.)

Шаг 70.В ячейку D31 вводим формулу =ABS(E26)/B32

Шаг 71.В ячейку D33 вводим формулу = ABS(E27)/B33

Шаг 72.В ячейку F32 вводим формулу = =ЕСЛИ(D32>$D$31;"значим";"не значим")

Шаг 73.В ячейку F33 вводим формулу = =ЕСЛИ(D33>$D$31;"значим";"не значим")

Таким образом уравнение линейной регрессии имеет вид:

                                       y = -64,662x + 4162,5                                                                          (23)

При этом ,согласно критерию Фишера-Снекондера, уравнение линейной регрессии (23) значимо  и коэффициенты этого уравнения  согласно критерия Стьюдента тоже значимы.

На рис.5 представлены расчёты для  квадратичной аппроксимации.

9,000000	100,000000	1596,00	30996,00	Квадратичная
100,000000	1596,000000	28600,00	313046	аппроксимация
1596,00	28600,00	543204,00	4799274
					0,954175375
Обратная матрица
0,67	-0,12	0,00		a1=	4185,338627
-0,11937987	0,032224637	-0,001345893		a2=	-71,8129242
0,004304435	-0,001345893	6,0056E-05		a3=	0,319075677
Fтабл=	5,99	Fквадр>Fтабл
Fквадр=	62,46698409	Уравнение значимо
Doct=	16241,49966	tтабл=	2,346
Sa1=	104,6449985	ta1=	39,99559	ta1>tтабл	значим
Sa2=	22,88	ta2=	3,13903	ta2>tтабл	значим
Sa3=	0,99	ta3=	0,323074	ta3>tтабл	значим

 

Рис.5. Фрагмент рабочего листа  MS Excel для квадратичной аппроксимации.

 

Шаг 74.В ячейку A35 вводим формулу =$B$18

Шаг 75.В ячейку A36 вводим формулу =B17

Шаг 76.В ячейку A35 вводим формулу C17

Шаг 77.В ячейку A37 вводим формулу =B17

Шаг 78.В ячейку B34 вводим формулу =C17

Шаг 79.В ячейку B35 вводим формулу =B17

Шаг 80.В ячейку B36 вводим формулу =C17

Шаг 81.В ячейку B37 вводим формулу =E17

Шаг 82.В ячейку C35 вводим формулу =C17

Шаг 83.В ячейку C36 вводим формулу =E17

Шаг 84.В ячейку C37 вводим формулу =F17

Шаг 85.В ячейку D35 вводим формулу =A17

Шаг 86.В ячейку D36 вводим формулу =D17

Шаг 87.В ячейку D37 вводим формулу =G17

Шаг 88.В выделяем ячейки A40:C42 и вводим формулу {=МОБР(A35:C37)}

Шаг 89.В выделяем ячейки F40:F42 и вводим формулу {=МУМНОЖ(A40:C42;D35:D37)}.

Шаг 90.В ячейку F38 вводим формулу =1-N17/L17

Шаг 91.В ячейку B44 вводим число 5,99 (определено по a=0,05,df=1 и df=7 табл.П1 из приложения 1.)

Шаг 92.В ячейку B45 вводим формулу =F38*(B18-3)/2(2*(1-F38)).

Шаг 93.В ячейку B46 вводим формулу =N17/(B18-3)

Шаг 94.В ячейку B47 вводим формулу =((N17/($B$18-3))*A40)^(1/2)

Шаг 95.В ячейку B48 вводим формулу =((N17/($B$18-3))*A41)^(1/2)

Шаг 96.В ячейку B49 вводим формулу =((N17/($B$18-3))*A42)^(1/2)

Шаг 97.В ячейку С45 вводим формулу =ЕСЛИ(B45>B44;»Уравнение значимо»;»Уравнение не значимо»).

Шаг 98.В ячейку C45 вводим формулу

Шаг 99.В ячейку D46 вводим число 2.3460 (определено по a=0,05 и df=8 табл.П2 из приложения 2.)

Шаг 100.В ячейку D47 вводим формулу =ABS(F40)/B47

Шаг 101.В ячейку C48 вводим формулу =ABS(F41)/B48

Шаг 102.В ячейку D49 вводим формулу =ABS(F42)/B49

Шаг 103.В ячейку F47 вводим формулу = =ЕСЛИ(D47>$D$46;"значим";"не значим")

Шаг 104.В ячейку F48 вводим формулу = =ЕСЛИ(D48>$D$46;"значим";"не значим")

Шаг 105.В ячейку F49 вводим формулу = =ЕСЛИ(D49>$D$46;"значим";"не значим")

Таким образом уравнение квадратичной регрессии имеет вид:

                                           y = 0,3191x2 - 71,813x + 4185,3                                                     (24)                                           

Согласно критерию Фишера – Снедекора, уравнение квадратичной регрессии (24) значимо. Два коэффициента этого уравнения а2=-71,8129242

и а3= 0,319075677 согласно критерия Стьюдента значимы, а третий а1=4185,338627

не значим. Таким образом квадратичная аппроксимация становиться линейной.

На рис.6 представлены расчёты для  экспоненциальной аппроксимации.

9,00	100,00	73,20843291	Экспоненциальная
100,00	1596,00	804,2249287	аппроксимация
				0,949932
Обратная матрица			c=	8,3451343
0,365719523	-0,022914757		a2=	-0,0189778
-0,02291476	0,002062328		a1=	4209,6479
Fтабл=	5,59	Fэксп>Fтабл
Fэксп=	132,80996	Уравнение значимо
Doct=	0,001314926	tтабл=	2,306
Sa2=	0,001646757	ta2=	11,52432	ta2>табл	значим
Sa1=	0,021929299	ta1=	191964,5	ta1>табл	значим

Рис.6. Фрагмент листа MS Excel для экспоненциальной аппроксимации.

Шаг 106.В ячейку A51 вводим формулу =$B$18

Шаг 107.В ячейку B51 вводим формулу =B17

Шаг 108.В ячейку A52 вводим формулу =B17

Шаг 109.В ячейку B52 вводим формулу =C17

Шаг 110.В ячейку C51 вводим формулу =H17

Шаг 111.В ячейку C52 вводим формулу = I17

Шаг 112.Выделяем ячейки А55:В56 и вводим формулу{=МОБР(А51:В52)}

Шаг 113.Выделяем ячейки E54:E55 и вводим формулу{=МУМНОЖ(А55:В56;С51:С52)}

Шаг 114.В ячейку E56 вводим формулу =EXP(E54)

Шаг 115.В ячейку E53 вводим формулу =1-P17/O17

Шаг 116.В ячейку B58 вводим формулу = B29

Шаг 117.В ячейку B59 вводим формулу =E53*(B18-2)/(1-E53)

Шаг 118.В ячейку B60 вводим формулу =P17/(B18-2))

Шаг 119.В ячейку B61 вводим формулу = (P17/(($B$18-2)*K17))^(1/2).

Шаг 120.В ячейку B62 вводим формулу =((P17*C17)/(($B$18-2)*$B$*K17))^(1/2)

Шаг 121.В ячейку C59 вводим формулу = ЕСЛИ(B59>B58;"Уравнение значимо";"Уравнение не значимо")

Шаг 122.В ячейку D60 вводим формулу =D31

Шаг 123.В ячейку D61 вводим формулу = ABS(E55)/B61

Шаг 124.В ячейку D62 вводим формулу =ABS(E56)/B62

Шаг 125.В ячейку F61 вводим формулу = ЕСЛИ(D61>$D$60;"значим";"не значим")

Шаг 126.В ячейку F62 вводим формулу = ЕСЛИ(D62>$D$60;"значим";"не значим")

Таким образом, уравнение экспоненциальной регрессии имеет вид:

                                                y = 4209,6e-0,019x                                                                     (25)

Согласно критерию Фишера- Снедекора, уравнение экспоненциальной регрессии (25) значимо. Оба коэффициента этого  уравнения, согласно критерию Стьюдента, значимы.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

5.Представление  результатов в виде графиков

Построение  линии тренда

Методика проведения данных работ подробно изложена в  работе . Результаты представлены на рис. 7-9.

Рис.7.Исходные точки и  линии тренда для линейной аппроксимации.

 

Рис.8. Исходные точки  и линия тренда для квадратичной аппроксимации.

 

 

Рис.9. Исходные точки и линия  тренда для экспоненциальной аппроксимации.

 

 

Сравнивая данные результаты с результатами, полученными вручную ранее с  использованием основных расчётных формул, видим, что они полностью совпадают. Это указывает на то, что вычисления верны.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

6.Получения  числовых характеристик

с использованием функции ЛИНЕЙН И ЛГРФПРИБЛ

Методика этих функции подробно изложена в работе .Результаты расчётов представлены на рис. 10-12.

ЛИНЕЙН
-64,662236	4162,4693
5,40460056	71,971208
0,9533782	119,01035
143,144359	7
2027419,76	99144,237