3. Расчет коэффициентов аппроксимации в Microsoft Excel

Таблица 1

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

i	xi	yi
1	0,1	0,56
2	0,28	1,05
3	0,87	2,87
4	1,65	6,43
5	1,99	8,96
6	2,08	8,08
7	2,34	9,11
8	2,65	16,86
9	2,77	17,97
10	2,83	18,99
11	3,06	23,75
12	3,33	29,43
13	3,41	37,45
14	3,55	42,44
15	3,85	56,94
16	4,01	75,08
17	4,23	82,44
18	4,53	90,85
19	4,92	99,06
20	5,14	120,45
21	5,23	139,65
22	5,55	167,54
23	6,32	200,45
24	6,66	212,97
25	7,13	275,74

Функция y=f(x) задана таблицей 1

Задача.

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

Решение.

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

Линейная аппроксимация:

Вычисления производим по следующим инструкциям:

1. В ячейки B2:B26 заносим значения .

2. В ячейки C2:C26 заносим значения .

3. В ячейку D2 вводим формулу «=B2^2».

4. В ячейки D3:D26 копируем эту формулу.

5. В ячейку E2 вводим формулу «=B2*C2».

6. В ячейки E3:E26. копируем эту формулу.

7. В ячейку F2 вводим формулу «=B2^3».

8. В ячейки F3:F26 копируем эту формулу.

9. В ячейку G2 вводим формулу «=B2^4»

10. В ячейки G3:G26 копируем эту формулу.

11. В ячейку H2 вводим формулу «=D2*C2».

12. В ячейки H3:H26 копируем эту формулу.

11. В ячейку I2 вводим формулу =LN(C2).

12. В ячейки I3:I26 копируем эту формулу.

13. В ячейку J2 вводим формулу =B2*LN(C2).

14. В ячейки I3:I26 копируем эту формулу.

15. В ячейку B27 вводим формулу =СУММ(B2:B26).

16. В ячейку C27 вводим формулу =СУММ(C2:C26).

17. В ячейку D27 вводим формулу =СУММ(D2:D26).

18. В ячейку E27 вводим формулу =СУММ(E2:E26).

19. В ячейку F27 вводим формулу =СУММ(F2:F26).

20. В ячейку G27 вводим формулу =СУММ(G2:G6).

21. В ячейку H27 вводим формулу =СУММ(H2:H26).

22. В ячейку I27 вводим формулу =СУММ(I2:I26).

23. В ячейку J27 вводим формулу =СУММ(J2:J26).

П

Таблица 2

олучаем данные:

Результаты вычислений линейной аппроксимации

i	xi	yi	xi^2	xiyi	xi^3	xi^4	xi^2yi	ln yi	xiln yi
1	0,1	0,56	0,010	0,056	0,001	0,000	0,006	-0,580	-0,058
2	0,28	1,05	0,078	0,294	0,022	0,006	0,082	0,049	0,014
3	0,87	2,87	0,757	2,497	0,659	0,573	2,172	1,054	0,917
4	1,65	6,43	2,723	10,610	4,492	7,412	17,506	1,861	3,071
5	1,99	8,96	3,960	17,830	7,881	15,682	35,482	2,193	4,364
6	2,08	8,08	4,326	16,806	8,999	18,718	34,957	2,089	4,346
7	2,34	9,11	5,476	21,317	12,813	29,982	49,883	2,209	5,170
8	2,65	16,86	7,023	44,679	18,610	49,316	118,399	2,825	7,486
9	2,77	17,97	7,673	49,777	21,254	58,873	137,882	2,889	8,002
10	2,83	18,99	8,009	53,742	22,665	64,142	152,089	2,944	8,331
11	3,06	23,75	9,364	72,675	28,653	87,677	222,386	3,168	9,693
12	3,33	29,43	11,089	98,002	36,926	122,964	326,346	3,382	11,262
13	3,41	37,45	11,628	127,705	39,652	135,213	435,472	3,623	12,354
14	3,55	42,44	12,603	150,662	44,739	158,823	534,850	3,748	13,306
15	3,85	56,94	14,823	219,219	57,067	219,707	843,993	4,042	15,562
16	4,01	75,08	16,080	301,071	64,481	258,570	1207,294	4,319	17,317
17	4,23	82,44	17,893	348,721	75,687	320,156	1475,091	4,412	18,663
18	4,53	90,85	20,521	411,551	92,960	421,107	1864,324	4,509	20,427
19	4,92	99,06	24,206	487,375	119,095	585,950	2397,886	4,596	22,611
20	5,14	120,45	26,420	619,113	135,797	697,995	3182,241	4,791	24,627
21	5,23	139,65	27,353	730,370	143,056	748,181	3819,832	4,939	25,832
22	5,55	167,54	30,803	929,847	170,954	948,794	5160,651	5,121	28,423
23	6,32	200,45	39,942	1266,844	252,436	1595,395	8006,454	5,301	33,500
24	6,66	212,97	44,356	1418,380	295,408	1967,419	9446,412	5,361	35,705
25	7,13	275,74	50,837	1966,026	362,467	2584,390	14017,767	5,619	40,067
n	88,480	1745,120	397,950	9365,168	2016,772	11097,046	53489,458	84,464	370,990
	xi	yi	xi^2	xiyi	xi^3	xi^4	xi^2yi	ln yi	xiln yi

Аппроксимируем функцию линейной функцией . Для определения коэффициентов и воспользуемся системой

Расчет параметров линейной аппроксимации						Таблица 3
Матрица системы		Правая часть			Решение
25,000	88,480	1745,120			a1	-63,282
88,480	397,950	9365,168			a2	37,604

В таблице 3 в ячейках G31:G32 записана формула {=МУМНОЖ(МОБР(C31:D32); E31:E32)}

Таким образом, линейная аппроксимация имеет вид y = 37,604x – 63,282.

Квадратичная аппроксимация:

Аппроксимируем функцию квадратичной функцией . Для определения коэффициентов , и воспользуемся системой

Таблица 4

Расчет параметров квадратичной аппроксимации
Матрица системы			Правая часть	Решение
25,000	88,480	397,950	1745,120	a1	4,119
88,480	397,950	2016,772	9365,168	a2	-13,446
397,950	2016,772	11097,046	53489,458	a3	7,116

В таблице 4 в ячейках H38:H40 записана формула {=МУМНОЖ(МОБР(C38:E40); F38:F40)}.

Таким образом, квадратичная аппроксимация имеет вид y = 7,116x2 – 13.446x + 4,119.

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

Аппроксимируем функцию экспоненциальной функцией . Для определения коэффициентов , и воспользуемся системой

Таблица 5

Расчет параметров экспоненциальной аппроксимации
Матрица системы		Правая часть		Решение
25,000	88,480	84,464		с	0,371
88,480	397,950	370,990		a2	0,850
				a1	1,450

В таблице 5 в ячейках G46:G47 записана формула {=МУМНОЖ(МОБР(C46:D47);E46:E47)}.В ячейке G48 записана формула {=EXP(G46)}.

Таким образом, экспоненциальная аппроксимация имеет вид y = .

Вычисление коэффициентов корреляции и детерминированности:

Таблица 6

i	xi	yi	Xi-Xcp	Yi-Ycp	(Xi-Xcp)^2		(Yi-Ycp)^2		(Xi-Xcp)(Yi-Ycp)
1	0,1	0,56	-3,439	-69,245	11,828	4794,842		238,147
2	0,28	1,05	-3,259	-68,755	10,622	4727,223		224,086
3	0,87	2,87	-2,669	-66,935	7,125	4480,267		178,662
4	1,65	6,43	-1,889	-63,375	3,569	4016,365		119,728
5	1,99	8,96	-1,549	-60,845	2,400	3702,090		94,261
6	2,08	8,08	-1,459	-61,725	2,129	3809,951		90,069
7	2,34	9,11	-1,199	-60,695	1,438	3683,859		72,785
8	2,65	16,86	-0,889	-52,945	0,791	2803,152		47,079
9	2,77	17,97	-0,769	-51,835	0,592	2686,846		39,871
10	2,83	18,99	-0,709	-50,815	0,503	2582,144		36,038
11	3,06	23,75	-0,479	-46,055	0,230	2121,045		22,069
12	3,33	29,43	-0,209	-40,375	0,044	1630,124		8,446
13	3,41	37,45	-0,129	-32,355	0,017	1046,833		4,180
14	3,55	42,44	0,011	-27,365	0,000	748,832		-0,296
15	3,85	56,94	0,311	-12,865	0,097	165,503		-3,998
16	4,01	75,08	0,471	5,275	0,222	27,828		2,484
17	4,23	82,44	0,691	12,635	0,477	159,648		8,728
18	4,53	90,85	0,991	21,045	0,982	442,900		20,852
19	4,92	99,06	1,381	29,255	1,907	855,867		40,396
20	5,14	120,45	1,601	50,645	2,563	2564,936		81,073
21	5,23	139,65	1,691	69,845	2,859	4878,352		118,094
22	5,55	167,54	2,011	97,735	4,043	9552,169		196,526
23	6,32	200,45	2,781	130,645	7,733	17068,168		363,298
24	6,66	212,97	3,121	143,165	9,739	20496,274		446,790
25	7,13	275,74	3,591	205,935	12,894	42409,307		739,472
Среднее	3,539	69,805		Сумма:	84,802	141454,527		3188,839

Таблица 6 составляется по следующим инструкциям:

1. В ячейку L2 вводим формулу {=(B2-$D$52)*(C2-$D$53)}

2. В ячейки L3:L26 копируем эту формулу.

3. В ячейку M2 вводим формулу {=(B2-$D$52)^2}

4. В ячейки M3:M26 копируем эту формулу.

5. В ячейку N2 вводим формулу {=(C2-$D$53)^2}

6. В ячейки N3:N26 копируем эту формулу.

7. В ячейку O2 вводим формулу {=($G$31+$G$32*B2-C2)^2}

8. В ячейки O3:O26 копируем эту формулу.

9. В ячейку P2 вводим формулу {=($H$38+$G$47*B2+$H$40*D2-C2)^2}

10. В ячейки P3:P26 копируем эту формулу.

11. В ячейку Q2 вводим формулу {=($G$48*EXP($G$47*B2)-C2)^2}

12. В ячейки Q3:Q26 эта формула копируется.

13. В ячейку L27 вводим формулу =СУММ(L2:L26).

14. С помощью функции автозаполнения протягиваем эту формулу на ячейки M27-N27

15. В ячейки M27:Q27 копируем эту формулу.

16. В ячейку B2 вводим формулу =СРЗНАЧ(B2:B26)

17. В ячейку В3 вводим формулу =СРЗНАЧ(C2:C26)

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

18. В Ячейку В2 вводим формулу =H27/(КОРЕНЬ(F27)*КОРЕНЬ(G27)).

19. Для проверки полученного значения используем встроенную функцию =КОРРЕЛ(B2:B26;C2:C26)

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

Далее применим следующие формулы:

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

- регрессионная сумма квадратов.

и с помощью этой величины определим коэффициент детерминированности:

В таблице 8 приведены результаты расчетов по приведенным выше формулам.

Таблица 8

Расчет вспомогательных коэффициентов детерминированности

Sполн(лин)	Sполн(квадр)	Sполн(эксп)
242691,239	247198,715	296446,562

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

Таблица 9

Итоговая таблица результатов вычисления коэффициентов детерминированности

R^2 (лин)	0,394
R (лин)	0,627
R^2 (квад)	0,319
R (квад)	0,565
R^2 (эксп)	0,304
R (эксп)	0,552