Вычисление средних значений X и y.
-
Xcp=
8,02
Ycp=
71,22
В ячейке F49 записана формула =A26/25.
В ячейке F50 записана формула =B26/25.
Для того, чтобы рассчитать коэффициент корреляции и коэффициент детерминированности данные целесообразно расположить в виде таблицы 7, которая является продолжением таблицы 2.
Таблица 7
Вычисление остаточных сумм.
1 |
A |
B |
J |
K |
L |
M |
N |
O |
2 |
1,08 |
200,45 |
286,1581133 |
33,67713024 |
2431,515548 |
-17,4655835 |
2,739548607 |
2,06381524 |
3 |
2,21 |
149,43 |
269,6903813 |
30,61630224 |
2375,626592 |
-14,3341016 |
2,652132095 |
2,303854826 |
4 |
3,87 |
128,98 |
263,1919493 |
29,30273424 |
2363,943296 |
-12,9423319 |
2,663464482 |
2,419315864 |
5 |
4,07 |
116,56 |
222,0622893 |
22,68807424 |
2173,461696 |
-5,40357929 |
3,261540053 |
3,153035328 |
6 |
4,76 |
87,76 |
214,1826413 |
21,74543424 |
2109,601644 |
-4,24377119 |
3,433975028 |
3,284178272 |
7 |
5,14 |
60,98 |
173,2728413 |
15,78631824 |
1901,866988 |
3,758904677 |
5,20829325 |
4,350529739 |
8 |
5,98 |
40,98 |
133,8902093 |
10,77940224 |
1663,041024 |
11,76158055 |
8,003666304 |
5,763118638 |
9 |
6,32 |
29,98 |
58,84006128 |
3,97284624 |
871,4540162 |
26,723105 |
15,96869609 |
9,749005117 |
10 |
6,99 |
24,65 |
50,52095328 |
3,50887824 |
727,4024762 |
28,11487472 |
16,89106417 |
10,2375907 |
11 |
7,43 |
19,99 |
36,36300928 |
2,25961024 |
585,1754522 |
32,40616468 |
19,92944245 |
11,90364436 |
12 |
7,54 |
15,67 |
32,14946928 |
1,67236624 |
618,0394882 |
34,84176169 |
21,78453493 |
12,96717898 |
13 |
7,84 |
13,98 |
-15,94947072 |
0,12027024 |
2115,116892 |
53,86261448 |
39,52531641 |
25,29827138 |
14 |
8,07 |
12,09 |
-1,00803072 |
0,31002624 |
3,27754816 |
56,29821149 |
42,21359478 |
27,55855291 |
15 |
8,57 |
14,97 |
-1,76124672 |
0,63489024 |
4,88586816 |
59,08175092 |
45,40172255 |
30,39004458 |
16 |
8,94 |
16,09 |
7,32464928 |
1,48060224 |
36,23558416 |
63,95294493 |
51,27819084 |
36,06305061 |
17 |
9,32 |
18,65 |
16,55014128 |
2,39259024 |
114,4814402 |
67,78031165 |
56,16081297 |
41,25394521 |
18 |
9,86 |
22,09 |
22,84748928 |
3,67412224 |
142,0768642 |
72,07160161 |
61,91299672 |
47,9675645 |
19 |
10,05 |
31,65 |
83,95030528 |
7,27273024 |
969,0519962 |
81,11810477 |
75,00108517 |
65,91605152 |
20 |
10,54 |
38,56 |
98,14256928 |
9,65220624 |
997,9028282 |
85,87331797 |
82,4039021 |
77,90270179 |
21 |
10,87 |
57,98 |
91,30130928 |
9,77687824 |
852,6166402 |
86,10527959 |
82,77423699 |
78,54021708 |
22 |
11,23 |
85,98 |
178,7345893 |
13,59249424 |
2350,271616 |
92,60020493 |
93,49190085 |
98,67343858 |
23 |
11,46 |
114,43 |
207,0798293 |
16,53886224 |
2592,805664 |
97,0074757 |
101,1476309 |
115,1999345 |
24 |
11,96 |
130,87 |
287,1930413 |
24,07668624 |
3425,714076 |
106,7498637 |
119,1697284 |
162,2237665 |
25 |
12,54 |
150,98 |
555,0412813 |
33,60289024 |
9167,9859 |
117,0721558 |
139,9156074 |
233,1447671 |
26 |
13,86 |
196,77 |
663,8685933 |
40,02839824 |
11010,22096 |
123,2191387 |
153,0769164 |
289,3506562 |
27 |
200,5 |
1780,52 |
3933,636968 |
339,162744 |
51603,7721 |
|
|
|
28 |
|
|
(X-Xср )(Y-Yср) |
(X-Xср)^2 |
(Y-Yср)^2 |
линейн. |
квадр. |
экспон. |
Поясним как таблица 7 составляется.
Ячейки A2:A27 и B2:B27 уже заполнены (см. табл. 2).
Далее делаем следующие шаги.
Шаг 1. В ячейку J2 вводим формулу =(A2-$F$49)*(B2-$F$50).
Шаг 2. В ячейки J3:J26 эта формула копируется.
Шаг 3. В ячейку K2 вводим формулу =(A2-$F$49)^2.
Шаг 4. В ячейки K3:K26 эта формула копируется.
Шаг 5. В ячейку L2 вводим формулу =(B2-$F$50)^2.
Шаг 6. В ячейки L3:L26 эта формула копируется.
Шаг 7. В ячейку M2 вводим формулу =($D$37+$D$38*A2-B2)^2.
Шаг 8. В ячейки M3:M26 эта формула копируется.
Шаг 9. В ячейку N2 вводим формулу
=($I$38+$I$39*A2+$I$40*A2^2-B2)^2.
Шаг 10. В ячейки N3:N26 эта формула копируется.
Шаг 11. В ячейку O2 вводим формулу
=($G$47*EXP($G$46*A2)-B2)^2.
Шаг 12. В ячейки O3:O26 эта формула копируется.
Последующие шаги делаем с помощью автосуммирования .
Шаг 13. В ячейку J27 вводим формулу =СУММ(J2:J26).
Шаг 14. В ячейку K27 вводим формулу =СУММ(K2:K26).
Шаг 15. В ячейку L27 вводим формулу =СУММ(L2:L26).
Шаг 16. В ячейку M27 вводим формулу =СУММ(M2:M26).
Шаг 17. В ячейку N27 вводим формулу =СУММ(N2:N26).
Шаг 18. В ячейку O27 вводим формулу =СУММ(O2:O26).
Теперь проведем расчеты коэффициента корреляции по формуле
(только для линейной аппроксимации)
и коэффициента детерминированности по формуле . Результаты расчетов средствами Microsoft Excel представлены в таблице 8.
Таблица 8
Результаты расчета.
Коэффициент корреляции |
0,940263294
|
(линейная аппроксимация) |
0,884095062
|
квадратичная аппроксимация) |
0,967312875
|
(экспоненциальная аппроксимация) |
0,808949148
|
В таблице 8 в ячейке B53 записана формула =J27/(K27*L27)^(1/2).
В ячейке B54 записана формула =1- M27/L27.
В ячейке B55 записана формула =1- N27/L27.
В ячейке B56 записана формула =1- O27/L27.
Анализ результатов расчетов показывает, что квадратичная аппроксимация наилучшим образом описывает экспериментальные данные.