Расчёт с помощью таблиц, выполненных средствами Microsoft Excel.

Для проведения расчётов, данные целесообразно расположить в виде таблицы 2, используя средства табличного процессора MicrosoftExcel.

Таблица 2.

	A	B	C	D	E	F	G	H	I
3	0,56	0,77	0,5929	0,4312	0,456533	0,35153041	0,332024	-0,57982	-0,44646
4	2,08	1,45	2,1025	3,016	3,048625	4,42050625	4,3732	0,732368	1,061933
5	3,04	1,76	3,0976	5,3504	5,451776	9,59512576	9,416704	1,111858	1,956869
6	2,76	2,23	4,9729	6,1548	11,08957	24,7297344	13,7252	1,015231	2,263964
7	3,65	2,65	7,0225	9,6725	18,60963	49,3155063	25,63213	1,294727	3,431027
8	7,06	2,76	7,6176	19,4856	21,02458	58,0278298	53,78026	1,954445	5,394268
9	14,98	3,45	11,9025	51,681	41,06363	141,669506	178,2995	2,706716	9,33817
10	15,98	3,89	15,1321	62,1622	58,86387	228,98045	241,811	2,771338	10,7805
11	23,22	4,87	23,7169	113,0814	115,5013	562,491346	550,7064	3,145014	15,31622
12	26,12	5,04	25,4016	131,6448	128,0241	645,241283	663,4898	3,262701	16,44401
13	28,76	5,54	30,6916	159,3304	170,0315	941,974311	882,6904	3,358986	18,60878
14	30,76	5,81	33,7561	178,7156	196,1229	1139,47429	1038,338	3,426215	19,90631
15	45,76	6,98	48,7204	319,4048	340,0684	2373,67738	2229,446	3,82341	26,6874
16	50,87	7,34	53,8756	373,3858	395,4469	2902,58028	2740,652	3,929273	28,84087
17	60,45	7,86	61,7796	475,137	485,5877	3816,71898	3734,577	4,101817	32,24028
18	65,87	8,12	65,9344	534,8644	535,3873	4347,3451	4343,099	4,187683	34,00399
19	11,85	8,87	78,6769	105,1095	697,8641	6190,05459	932,3213	2,472328	21,92955
20	86,09	9,45	89,3025	813,5505	843,9086	7974,93651	7688,052	4,455393	42,10347
21	101,65	10,87	118,1569	1104,936	1284,366	13961,053	12010,65	4,621536	50,23609
22	124,37	11,23	126,1129	1396,675	1416,248	15904,4635	15684,66	4,823261	54,16522
23	130,75	11,89	141,3721	1554,618	1680,914	19986,0707	18484,4	4,873287	57,94338
24	149,56	12,56	157,7536	1878,474	1981,385	24886,1983	23593,63	5,007698	62,89668
25	172,45	13,43	180,3649	2316,004	2422,301	32531,4972	31103,93	5,150107	69,16594
26	175,51	13,55	183,6025	2378,161	2487,814	33709,878	32224,07	5,167696	70,02228
27	196,77	14,76	217,8576	2904,325	3215,578	47461,9339	42867,84	5,282036	77,96284
28	1530,92	177,13	1689,517	16895,37	18556,16	219852,679	201299,9	82,0953	732,2536
	С У М М Ы

Аппроксимируем функцию линейной функцией . Для определения коэффициентов и воспользуемся системой (4). Используя итоговые суммы таблицы 2, расположенные в ячейках A26, B26, C26 и D26, запишем систему (4) в виде

(11)

решив которую, получим и .

Систему решали методом Крамера. Суть которого состоит в следующем. Рассмотрим систему nалгебраических линейных уравнений сnнеизвестными:

(12)

Определителем системы называется определитель матрицы системы:

(13)

Обозначим - определитель, который получится из определителя системы Δ заменойj-го столбца на столбец

(14)

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

(15)

Решение системы (11) проводим, пользуясь средствами Microsoft Excel. Результаты представлены в таблице 3.

Таблица 3.

	A	B	C	D	E
30	25	177,13	1530,92
31	177,13	1689,517	16895,37
32
33	Обратная матрица
34	0,155531	-0,01631		a1=	-37,3899
35	-0,01631	0,002301		a2=	13,9201

Далее аппроксимируем функцию квадратичной функцией . Для определения коэффициентов a₁, a₂ и a₃ воспользуемся системой (5). Используя итоговые суммы таблицы 2, расположенные в ячейках A26, B26, C26 , D26, E26, F26, G26 запишем систему (5) в виде

(16)

решив которую, получим a₁=4,4898, и

Таким образом, квадратичная аппроксимация имеет вид

(17)

Решение системы (16) проводим, пользуясь средствами Microsoft Excel. Результаты представлены в таблице 4.

Таблица 4.

	A	B	C	D	E	F
37	25	177,13	1689,517	1530,92
38	177,13	1689,517	18556,16	16895,37
39	1689,517	18556,16	219852,7	201299,9
40
41	Обратная матрица
42	0,425827	-0,11923	0,006791		a1=	4,48980388
43	-0,11923	0,041491	-0,00259		a2=	-2,02647293
44	0,006791	-0,00259	0,000171		a3=	1,05214944

Теперь аппроксимируем функцию экспоненциальной функцией . Для определения коэффициентов и прологарифмируем значения и, используя итоговые суммы таблицы 2, расположенные в ячейках A26, C26, H26 и I26, получим систему

(18)

где .

Решив систему (18), получим и .

После потенцирования получим .

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

(19)

Решение системы (18) проводим, пользуясь средствами Microsoft Excel. Результаты представлены в таблице 5.

Таблица 5.

	B	C	D	E	F
46	25	177,13	82,0953
47	177,13	1689,517	732,2536
48
49	Обратная матрица			c=	0,828262
50	0,155531	-0,01631		a2=	0,346575
51	-0,01631	0,002301		a1=	2,289336

Вычислим среднее арифметическое и по формулам:

; .

Результаты расчета и средствами Microsoft Excel представлены в таблице 6.

Таблица 6.

	A	B
54	Xcp=	7,0852
55	Ycp=	61,2368

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

Таблица 7.

	J	K	L	M	N	O	P	Q	R
3	383,1861	39,88175	3681,674	741,5492	8,95948	5,902732	-0,57982	14,92764196	2,805433
4	333,3604	31,75548	3499,527	371,9396	2,834382	2,903787	0,732368	6,509867922	0,358115
5	309,9096	28,35776	3386,868	253,781	1,304963	1,376503	1,111858	4,717387045	0,106521
6	283,9166	23,57297	3419,536	82,95674	5,968265	4,833835	1,015231	5,146462002	0,34327
7	255,409	19,671	3316,24	17,23593	8,170279	4,349538	1,294727	3,956459168	0,204265
8	234,3255	18,70736	2935,126	36,36581	0,022025	1,213448	1,954445	1,76721695	0,028777
9	168,1527	13,21468	2139,692	18,88371	24,58493	54,93603	2,706716	0,333040021	0,466177
10	144,6045	10,2093	2048,178	0,607314	11,91593	51,33948	2,771338	0,262629842	0,353907
11	84,21482	4,907111	1445,277	51,56675	13,28891	117,5062	3,145014	0,01926494	0,395558
12	71,82088	4,182843	1233,19	44,18814	26,18717	168,7066	3,262701	0,000445669	0,472936
13	50,18315	2,387643	1054,743	120,2853	10,27014	172,7692	3,358986	0,005651034	0,372955
14	38,86402	1,626135	928,8353	161,9483	6,388469	185,2965	3,426215	0,020278606	0,341471
15	1,628159	0,011067	239,5313	196,3475	17,25435	401,5154	3,82341	0,291166187	0,331843
16	-2,64146	0,064923	107,4705	193,5894	20,87873	472,1833	3,929273	0,416620139	0,310421
17	-0,60961	0,600315	0,619054	133,9133	47,42941	653,0676	4,101817	0,669131201	0,301927
18	4,794435	1,070811	21,46654	95,47864	71,61076	766,449	4,187683	0,816982653	0,297282
19	-88,1456	3,185511	2439,056	5510,299	3299,91	1419,036	2,472328	0,658506782	2,045045
20	58,77285	5,592279	617,6816	65,04499	46,11483	652,5318	4,455393	1,372602239	0,123905
21	152,9559	14,32471	1633,227	150,5918	26,32469	6,810057	4,621536	1,7895038	0,000676
22	261,6745	17,17937	3985,801	29,56291	98,96011	148,0771	4,823261	2,369902664	0,010602
23	333,997	23,0861	4832,085	6,916448	2,593334	105,856	4,873287	2,526430545	0,005738
24	483,5519	29,97344	7800,988	146,7356	20,6328	803,3296	5,007698	2,97178113	0,030117
25	705,6255	40,25649	12368,38	524,0878	29,21293	4632,069	5,150107	3,483057639	0,110657
26	738,7534	41,79364	13058,36	589,6423	28,10735	5656,957	5,167696	3,549018345	0,1272
27	1040,19	58,90256	18369,25	823,646	49,39019	34067,72	5,282036	3,992896591	0,437804
28	6048,494	434,5152	94562,8	10367,16	3878,315	50556,74	82,0953	62,57394507	10,3826
	С У М М Ы

	P
29	3,283812

Теперь проведем расчеты коэффициента корреляции по формуле (8) (только для линейной аппроксимации) и коэффициента детерминированности по формуле (10). Результаты расчетов средствами Microsoft Excel представлены в таблице 8.

Таблица 8.

	A	B
57	Коэффициент корреляции	0,943593
58	Коэффициент детерминированности ( линейная аппроксимация )	0,890367
59
60	Коэффициент детерминированности ( квадратичная аппроксимация )	0,958987
61
62	Коэффициент детерминированности ( экспоненциальная аппроксимация )	0,465363
63

Сравнивая результаты, полученные при помощи функции ЛИНЕЙН видим, что они полностью совпадают с вычислениями, проведенными выше. Это указывает на то , что вычисления верны.

Примечание: Полученное при построении линии тренда значение коэффициента детерминированности не совпадает с истинным значением(это значение было сосчитано вручную выше) ибо при преобразовании в линейную форму рассчитывается линейный коэффициент корреляции между Х и логарифмомY, т.е. заменяется наизаменяется наВычисление уточненного коэффициента детерминированности для экспоненциальной зависимости приведено в таблице 9.

Таблица 9.

	A	B
61	Уточненный	0,834075