Расчёт с помощью таблиц, выполненных средствами Microsoft Excel.
Для проведения расчётов данные целесообразно расположить в виде таблицы 2, используя средства табличного процессора Microsoft Excel.
Таблица 2.
|
A |
b |
c |
D |
e |
f |
g |
h |
i |
1 |
125,50 |
0,40 |
15750,25 |
50,20 |
1976656,38 |
248070375,06 |
6300,10 |
-0,92 |
-114,99 |
2 |
95,38 |
0,54 |
9097,34 |
51,51 |
867704,71 |
82761675,13 |
4912,57 |
-0,62 |
-58,77 |
3 |
72,49 |
0,80 |
5254,80 |
57,99 |
380920,46 |
27612924,09 |
4203,84 |
-0,22 |
-16,18 |
4 |
55,09 |
1,76 |
3034,91 |
96,96 |
167193,09 |
9210667,18 |
5341,44 |
0,57 |
31,14 |
5 |
41,87 |
2,64 |
1753,10 |
110,54 |
73402,17 |
3073348,74 |
4628,18 |
0,97 |
40,65 |
6 |
31,82 |
3,54 |
1012,51 |
112,64 |
32218,14 |
1025181,36 |
3584,29 |
1,26 |
40,22 |
7 |
24,18 |
5,31 |
584,67 |
128,40 |
14137,38 |
341841,82 |
3104,61 |
1,67 |
40,37 |
8 |
18,38 |
3,80 |
337,82 |
69,84 |
6209,21 |
114125,33 |
1283,73 |
1,34 |
24,54 |
9 |
13,97 |
5,70 |
195,16 |
79,63 |
2726,40 |
38087,78 |
1112,42 |
1,74 |
24,31 |
10 |
10,62 |
7,64 |
112,78 |
81,14 |
1197,77 |
12720,32 |
861,67 |
2,03 |
21,59 |
11 |
8,07 |
11,46 |
65,12 |
92,48 |
525,56 |
4241,25 |
746,33 |
2,44 |
19,68 |
12 |
6,13 |
15,35 |
37,58 |
94,10 |
230,35 |
1412,02 |
576,81 |
2,73 |
16,74 |
13 |
4,66 |
35,90 |
21,72 |
167,29 |
101,19 |
471,57 |
779,59 |
3,58 |
16,69 |
14 |
3,54 |
48,11 |
12,53 |
170,31 |
44,36 |
157,04 |
602,90 |
3,87 |
13,71 |
15 |
2,69 |
72,16 |
7,24 |
194,11 |
19,47 |
52,36 |
522,16 |
4,28 |
11,51 |
16 |
514,39 |
215,11 |
37277,54 |
1557,13 |
3523286,63 |
372267281,05 |
38560,63 |
24,73 |
111,22 |
17 |
Xi |
yi |
xi^2 |
xi*yi |
xi^3 |
xi^4 |
xi^2*yi |
Ln(yi) |
xi*ln(yi) |
Поясним как таблица 2 составляется.
Шаг 1.В ячейки А1:A15 заносим значения xi.
Шаг 2.В ячейки B1:B15 заносим значения уi.
Шаг 3.В ячейку С1 вводим формулу=А1^2.
Шаг 4.В ячейки С1:С25 эта формула копируется.
Шаг 5.В ячейку D1 вводим формулу=А1*B1.
Шаг 6.В ячейки D1:D15 эта формула копируется.
Шаг 7.В ячейку F1 вводим формулу=А1^4.
Шаг 8.В ячейки F1:F15 эта формула копируется.
Шаг 9.В ячейку G1 вводим формулу=А1^2*B1.
Шаг 10.В ячейки G1:G15 эта формула копируется.
Шаг 11.В ячейку H1 вводим формулу=LN(B1).
Шаг 12.В ячейки H1:H15 эта формула копируется.
Шаг 13.В ячейку I1 вводим формулу=А1*LN(B1).
Шаг 14.В ячейки I1:I15 эта формула копируется.
Последующие шаги делаем с помощью автосуммирования .
Шаг 15. В ячейку А16 вводим формулу=СУММ(А1:А15).
Шаг 16. В ячейку В16 вводим формулу=СУММ(В1:В15).
Шаг 17. В ячейку С16 вводим формулу=СУММ(С1:С15).
Шаг 18. В ячейку D16 вводим формулу=СУММ(D1:D15).
Шаг 19. В ячейку E16 вводим формулу=СУММ(E1:E15).
Шаг 20. В ячейку F16 вводим формулу=СУММ(F1:F15).
Шаг 21. В ячейку G16 вводим формулу=СУММ(G1:G15).
Шаг 22. В ячейку H16 вводим формулу=СУММ(H1:H15).
Шаг 23. В ячейку I16 вводим формулу =СУММ(I1:I15).
Аппроксимируем функцию
линейной функцией
.
Для определения коэффициентов
и
воспользуемся
системой (4). Используя итоговые суммы
таблицы 2, расположенные в ячейках A16,
B16, C16 и D16,
запишем систему (4) в виде
(11)
решив которую, получим
и
Таким образом, линейная аппроксимация имеет вид
(15)
Решение системы (11) проводим, пользуясь средствами Microsoft Excel. Результаты представлены в таблице 3.
Таблица 3.
|
a |
b |
C |
d |
e |
28 |
15 |
514,39 |
215,11 |
|
|
29 |
514,39 |
37277,54 |
1557,13 |
|
|
30 |
|
|
|
|
|
31 |
обратная матрица |
|
|
|
|
32 |
0,12655071 |
-0,0017463 |
|
a1= |
24,5031599 |
33 |
-0,0017463 |
5,0922E-05 |
|
a2= |
-0,29634596 |
В таблице 3 в ячейках A32:B33 записана формула {=МОБР(А28:В29)}.
В ячейках Е32:Е33 записана формула {=МУМНОЖ(А32:В33),(C28:С29)}.
Далее аппроксимируем функцию
квадратичной функцией
.
Для определения коэффициентов a1,
a2 и a3
воспользуемся системой (5). Используя
итоговые суммы таблицы 2, расположенные
в ячейках A16, B16, C16
, D16, E16, F16,
G16 запишем систему (5) в
виде
(16)
решив которую, получим а1=35,068 a2=-1,137 и a3=0,007
Таким образом, квадратичная аппроксимация имеет вид
(17)
Решение системы (16) проводим, пользуясь средствами Microsoft Excel. Результаты представлены в таблице 4.
Таблица 4.
|
a |
B |
C |
D |
e |
F |
||||
36 |
15 |
514,39 |
37277,54 |
215,11 |
|
|
||||
37 |
514,39 |
37277,54 |
3523286,63 |
1557,13 |
|
|
||||
38 |
37277,54 |
3523286,63 |
372267281,05 |
38560,63 |
|
|
||||
39 |
|
|
|
|
|
|
||||
40 |
обратная матрица |
|
|
|
|
|||||
41 |
0,21613275 |
-0,0088823 |
6,24226E-05 |
|
a1= |
35,06849439 |
||||
42 |
-0,0088823 |
0,00061937 |
-4,97252E-06 |
|
a2= |
-1,137969379 |
||||
43 |
6,2423E-05 |
-4,973E-06 |
4,34974E-08 |
|
a3= |
0,007362145 |
||||
В таблице 4 в ячейках А41:С43 записана формула {=МОБР(А36:С38)}.
В ячейках F41:F43 записана формула {=МУМНОЖ(А41:C43),(D36:D38)}.
Теперь аппроксимируем функцию
экспоненциальной функцией
.
Для определения коэффициентов
и
прологарифмируем значения
и, используя итоговые суммы таблицы 2,
расположенные в ячейках A16, C16,
H16 и I16,
получим систему
(18)
где
.
Решив систему (18), получим c=2,634 и a2=-0,037.
После потенцирования получим a1=18,819
Таким образом, экспоненциальная аппроксимация имеет вид
(19)
Решение системы (18) проводим, пользуясь средствами Microsoft Excel. Результаты представлены в таблице 5.
Таблица 5.
|
a |
B |
C |
D |
e |
46 |
15 |
514,39 |
24,73 |
|
|
47 |
514,39 |
37277,54 |
111,22 |
|
|
48 |
|
|
|
|
|
49 |
обратная матрица |
|
|
с= |
2,93489004 |
50 |
0,12655071 |
-0,0017463 |
|
a2= |
-0,03751473 |
51 |
-0,0017463 |
5,0922E-05 |
|
a1= |
18,8194336 |
В ячейках А50:В51 записана формула {=МОБР(А46:В47)}.
В ячейках Е49:Е50 записана формула {=МУМНОЖ(А50:В51),(С46:С47)}.
В ячейке Е51 записана формула=EXP(E49).
Вычислим среднее арифметическое
и
по формулам:
;
.
Результаты расчета и средствами Microsoft Excel представлены в таблице 6.
Таблица 6.
-
B
C
54
Xср=
34,2926667
55
Yср=
14,3406667
В ячейке В54 записана формула=А16/25.
В ячейке В55 записана формула=В16/25
Для того, чтобы рассчитать коэффициент корреляции и коэффициент детерминированности данные целесообразно расположить в виде таблицы 7, которая является продолжением таблицы 2.
Таблица 7.
|
j |
k |
L |
m |
n |
o |
1 |
-1271,491032 |
8318,77765 |
194,34219 |
171,3025 |
60,97991 |
0,052998 |
2 |
-843,0459249 |
3731,66229 |
190,4584 |
18,50994 |
49,492 |
0,000208 |
3 |
-517,2173582 |
1459,03627 |
183,34965 |
4,933024 |
90,94111 |
0,193967 |
4 |
-261,6443182 |
432,529074 |
158,27317 |
41,18381 |
49,54477 |
0,387664 |
5 |
-88,65985156 |
57,4159804 |
136,9056 |
89,39995 |
5,344094 |
1,618974 |
6 |
26,70644844 |
6,11408044 |
116,6544 |
133,02 |
7,687155 |
4,682984 |
7 |
91,32412178 |
102,266027 |
81,55294 |
144,6611 |
42,86109 |
5,231424 |
8 |
167,7301151 |
253,21296 |
111,10565 |
232,7553 |
164,8587 |
31,85363 |
9 |
175,6013884 |
413,01078 |
74,66112 |
215,0096 |
222,2444 |
29,62597 |
10 |
158,6226484 |
560,395147 |
44,898934 |
188,1277 |
261,5852 |
24,95164 |
11 |
75,53876178 |
687,628247 |
8,2982404 |
113,4576 |
222,1453 |
5,971068 |
12 |
-28,42551822 |
793,135794 |
1,0187538 |
53,8251 |
169,5045 |
0,157457 |
13 |
-638,8605382 |
878,094934 |
464,80485 |
163,2725 |
35,69548 |
403,971 |
14 |
-1038,497052 |
945,726507 |
1140,3679 |
607,9136 |
288,2409 |
1000,519 |
15 |
-1827,245118 |
998,72854 |
3343,0753 |
2347,791 |
1607,959 |
3041,196 |
16 |
-5819,563227 |
19637,7343 |
6249,7671 |
4525,163 |
3279,084 |
4550,414 |
17 |
(x-x(ср))(y-y(cp)) |
(x-x(ср))^2 |
(y-y(cp))^2 |
линейн |
квадр |
экспон |
Поясним как она составляется .
Ячейки А1:А16 и В1:В16 уже заполнены .
Далее делаем следующие шаги.
Шаг 1.В ячейку J1 вводим формулу=(А1-$B$54)*(B1-$B$55).
Шаг 2.В ячейки J2:J15 эта формула копируется.
Шаг 3.В ячейку K1 вводим формулу=(А1-$B$54)^2.
Шаг 4.В ячейки k2:K15 эта формула копируется.
Шаг 5.В ячейку L1 вводим формулу=(B1-$B$55)^2.
Шаг 6.В ячейки L2:L15 эта формула копируется.
Шаг 7.В ячейку M1 вводим формулу=($E$32+$E$33*A1-B1)^2.
Шаг 8.В ячейки M2:M15 эта формула копируется.
Шаг 9.В ячейку N1 вводим формулу=($F$41+$F$42*A1+$F$43*A1^2-B1)^2.
Шаг 10.В ячейки N2:N15 эта формула копируется.
Шаг 11.В ячейку O1 вводим формулу=($E$51*EXP($E$50*A1)-B1)^2.
Шаг 12.В ячейки O2:O15 эта формула копируется.
Последующие шаги делаем с помощью авто суммирования .
Шаг 13.В ячейку J16 вводим формулу =CУММ(J1:J15).
Шаг 14.В ячейку K16 вводим формулу =CУММ(K1:K15).
Шаг 15.В ячейку L16 вводим формулу =CУММ(L1:L15).
Шаг 16.В ячейку M16 вводим формулу =CУММ(M1:M15).
Шаг 17.В ячейку N16 вводим формулу =CУММ(N1:N15).
Шаг 18.В ячейку O16 вводим формулу =CУММ(O1:O15).
Теперь проведем расчеты коэффициента корреляции по формуле (8) (только для линейной аппроксимации) и коэффициента детерминированности по формуле (10). Результаты расчетов средствами Microsoft Excel представлены в таблице 8.
Таблица 8.
-
A
B
57
Коэффициент корреляции
-0,5253065
58
Коэффициент детерминированности ( линейная аппроксимация )
0,27594693
59
60
Коэффициент детерминированности ( квадратичная аппроксимация )
0,475327
61
62
Коэффициент детерминированности ( экспоненциальная аппроксимация )
0,27190662
63
В ячейке E57 записана формула=J16/(K16*L16)^(1/2).
В ячейке E59 записана формула=1-M16/L16.
В ячейке E61 записана формула=1-N16/L16.
В ячейке E63 записана формула=1-O16/L16.
Анализ результатов расчетов показывает, что квадратичная аппроксимация наилучшим образом описывает экспериментальные данные.