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

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

Таблица 2.

	A	B	C	D	E	F	G	H	I
1	0,28	1,05	0,0784	0,294	0,021952	0,006147	0,08232	0,04879	0,013661
2	0,87	2,87	0,7569	2,4969	0,658503	0,572898	2,172303	1,054312	0,917251
3	1,65	6,43	2,7225	10,6095	4,492125	7,412006	17,50568	1,860975	3,070608
4	1,99	8,96	3,9601	17,8304	7,880599	15,68239	35,4825	2,19277	4,363613
5	2,08	8,08	4,3264	16,8064	8,998912	18,71774	34,95731	2,089392	4,345935
6	2,34	9,11	5,4756	21,3174	12,8129	29,9822	49,88272	2,209373	5,169932
7	2,65	16,86	7,0225	44,679	18,60963	49,31551	118,3994	2,824944	7,486101
8	2,77	17,97	7,6729	49,7769	21,25393	58,87339	137,882	2,888704	8,001709
9	2,83	18,99	8,0089	53,7417	22,66519	64,14248	152,089	2,943913	8,331272
10	3,06	23,75	9,3636	72,675	28,65262	87,677	222,3855	3,167583	9,692803
11	3,33	29,43	11,0889	98,0019	36,92604	122,9637	326,3463	3,382015	11,26211
12	3,41	37,45	11,6281	127,7045	39,65182	135,2127	435,4723	3,623007	12,35445
13	3,55	42,44	12,6025	150,662	44,73888	158,823	534,8501	3,748091	13,30572
14	3,85	56,94	14,8225	219,219	57,06663	219,7065	843,9932	4,041998	15,56169
15	4,01	75,08	16,0801	301,0708	64,4812	258,5696	1207,294	4,318554	17,3174
16	4,23	86,44	17,8929	365,6412	75,68697	320,1559	1546,662	4,459451	18,86348
17	4,83	90,85	23,3289	438,8055	112,6786	544,2376	2119,431	4,50921	21,77948
18	4,92	99,06	24,2064	487,3752	119,0955	585,9498	2397,886	4,595726	22,61097
19	5,14	120,45	26,4196	619,113	135,7967	697,9953	3182,241	4,791235	24,62695
20	5,23	139,65	27,3529	730,3695	143,0557	748,1811	3819,832	4,939139	25,8317
21	5,55	187,54	30,8025	1040,847	170,9539	948,794	5776,701	5,233992	29,04866
22	6,32	200,45	39,9424	1266,844	252,436	1595,395	8006,454	5,300565	33,49957
23	6,66	212,97	44,3556	1418,38	295,4083	1967,419	9446,412	5,361151	35,70527
24	7,13	275,74	50,8369	1966,026	362,4671	2584,39	14017,77	5,619458	40,06674
25	7,25	321,43	52,5625	2330,368	381,0781	2762,816	16895,16	5,77278	41,85265
26	95,93	2089,99	453,3105	11850,65	2417,568	13982,99	71327,34	90,97713	415,0797
	С У М М Ы

Поясним как таблица 2 составляется.

Шаг 1.В ячейки А1:A25 заносим значения x_i.

Шаг 2.В ячейки B1:B25 заносим значения у_i.

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

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

Шаг 5.В ячейку D1 вводим формулу=А1*B1.

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

Шаг 7.В ячейку F1 вводим формулу=А1^4.

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

Шаг 9.В ячейку G1 вводим формулу=А1^2*B1.

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

Шаг 11.В ячейку H1 вводим формулу=LN(B1).

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

Шаг 13.В ячейку I1 вводим формулу=А1*LN(B1).

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

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

Шаг 15. В ячейку А26 вводим формулу=СУММ(А1:А25).

Шаг 16. В ячейку В26 вводим формулу=СУММ(В1:В25).

Шаг 17. В ячейку С26 вводим формулу=СУММ(С1:С25).

Шаг 18. В ячейку D26 вводим формулу=СУММ(D1:D25).

Шаг 19. В ячейку E26 вводим формулу=СУММ(E1:E25).

Шаг 20. В ячейку F26 вводим формулу=СУММ(F1:F25).

Шаг 21. В ячейку G26 вводим формулу=СУММ(G1:G25).

Шаг 22. В ячейку H26 вводим формулу=СУММ(H1:H25).

Шаг 23. В ячейку I26 вводим формулу =СУММ(I1:I25).

Аппроксимируем функцию линейной функцией . Для определения коэффициентов и воспользуемся системой (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
28	25	95,93	2089,99
29	95,93	453,3105	11850,65
30
31	Обратная матрица
32	0,212802	-0,04503		a1=	-88,92081
33	-0,04503	0,011736		a2=	44,95997

В таблице 3 в ячейках A32:B33 записана формула {=МОБР(А28:В29)}.

В ячейках Е32:Е33 записана формула {=МУМНОЖ(А32:В33),(C28:С29)}.

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

(16)

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

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

(17)

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

Таблица 4.

	A	B	C	D	E	F
36	25	95,93	453,3105	2089,99
37	95,93	453,3105	2417,568	11850,655
38	453,3105	2417,568	13982,99	71327,345
39
40	Обратная матрица
41	0,632687	-0,31439	0,033846		a1=	10,663624
42	-0,31439	0,184534	-0,021712		a2=	-18,924512
43	0,033846	-0,02171	0,002728		a3=	8,0272305

В таблице 4 в ячейках А41:С43 записана формула {=МОБР(А36:С38)}.

В ячейках F41:F43 записана формула {=МУМНОЖ(А41:C43),(D36:D38)}.

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

(18)

где .

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

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

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

(19)

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

Таблица 5.

	B	C	D	E	F
46	25	95,93	90,97713
47	95,93	453,3105	415,0797
48
49	Обратная матрица			с=	0,667679
50	0,212802	-0,04503		а2=	0,774368
51	-0,04503	0,011736		а1=	1,949707

В ячейках А50:В51 записана формула {=МОБР(А46:В47)}.

В ячейках Е49:Е50 записана формула {=МУМНОЖ(А50:В51),(С46:С47)}.

В ячейке Е51 записана формула=EXP(E49).

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

; .

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

Таблица 6.

	B	C
54	Xср=	3,8372
55	Yср=	83,5996

В ячейке В54 записана формула=А26/25.

В ячейке В55 записана формула=В26/25

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

Таблица 7.

	A	B	J	K	L	M	N	O
1	0,28	1,05	293,6454	12,65367	6814,436	5987,976	24,44408	1,881775
2	0,87	2,87	239,5409	8,804276	6517,268	2774,722	6,733461	0,910717
3	1,65	6,43	168,7853	4,783844	5955,147	448,0357	26,39582	0,320737
4	1,99	8,96	137,8743	3,412148	5571,07	70,73588	17,36822	0,020626
5	2,08	8,08	132,703	3,087752	5703,21	12,13871	4,203942	2,824782
6	2,34	9,11	111,5258	2,241608	5548,701	51,48821	1,498588	7,995842
7	2,65	16,86	79,23325	1,409444	4454,174	178,573	0,00062	2,833825
8	2,77	17,97	70,03991	1,138916	4307,244	311,4631	3,477709	1,730596
9	2,83	18,99	65,07479	1,014452	4174,4	373,491	5,791436	2,382273
10	3,06	23,75	46,51511	0,60404	3581,975	620,3441	17,37549	8,423061
11	3,33	29,43	27,47482	0,257252	2934,346	983,8198	52,24621	13,94466
12	3,41	37,45	19,71511	0,1825	2129,786	725,9091	4,090409	102,2541
13	3,55	42,44	11,82104	0,082484	1694,113	797,8984	4,861044	143,3219
14	3,85	56,94	-0,34124	0,000164	710,7343	741,75	0,023142	342,3946
15	4,01	75,08	-1,47219	0,02986	72,58358	265,3212	126,0007	996,9257
16	4,23	86,44	1,115709	0,154292	8,067872	219,6288	148,7578	1214,778
17	4,83	90,85	7,198197	0,985652	52,5683	1397,703	245,6958	76,64891
18	4,92	99,06	16,74052	1,172456	239,024	1103,718	163,9776	121,868
19	5,14	120,45	48,0087	1,697288	1357,952	471,9084	25,17881	258,6007
20	5,23	139,65	78,067	1,939892	3141,647	43,16294	70,45155	769,9408
21	5,55	187,54	178,0291	2,933684	10803,61	725,3842	1200,529	1951,06
22	6,32	200,45	290,1162	6,164296	13654,02	27,28786	126,2827	3577,409
23	6,66	212,97	365,1868	7,9682	16736,7	6,038755	767,7885	15795,87
24	7,13	275,74	632,6799	10,84253	36917,93	1944,475	65,14693	44766,92
25	7,25	321,43	811,6676	11,6472	56563,3	7121,842	677,9664	45516,8
26	95,93	2089,99	3830,945	85,2079	199644	27404,82	3786,286	115678,1
	С у м м ы					Остаточные суммы
	X	Y				линейн.	квадр.	экспон.

Поясним как она составляется .

Ячейки А1:А26 и В1:В26 уже заполнены .

Далее делаем следующие шаги.

Шаг 1.В ячейку J1 вводим формулу=(А1-$B$54)*(B1-$B$55).

Шаг 2.В ячейки J2:J25 эта формула копируется.

Шаг 3.В ячейку K1 вводим формулу=(А1-$B$54)^2.

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

Шаг 5.В ячейку L1 вводим формулу=(B1-$B$55)^2.

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

Шаг 7.В ячейку M1 вводим формулу=($E$32+$E$33*A1-B1)^2.

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

Шаг 9.В ячейку N1 вводим формулу=($F$41+$F$42*A1+$F$43*A1^2-B1)^2.

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

Шаг 11.В ячейку O1 вводим формулу=($E$51*EXP($E$50*A1)-B1)^2.

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

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

Шаг 13.В ячейку J26 вводим формулу =CУММ(J1:J25).

Шаг 14.В ячейку K26 вводим формулу =CУММ(K1:K25).

Шаг 15.В ячейку L26 вводим формулу =CУММ(L1:L25).

Шаг 16.В ячейку M26 вводим формулу =CУММ(M1:M25).

Шаг 17.В ячейку N26 вводим формулу =CУММ(N1:N25).

Шаг 18.В ячейку O26 вводим формулу =CУММ(O1:O25).

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

Таблица 8.

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

В ячейке E57 записана формула=J26/(K26*L26)^(1/2).

В ячейке E59 записана формула=1-M26/L26.

В ячейке E61 записана формула=1-N26/L26.

В ячейке E63 записана формула=1-O26/L26.

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

Схема алгоритма.

Промежуточные расчёты (согл. таблице 2)

Аппроксимируем функцию y=f(x) линейной функцией (находим коэффициенты а₁ и а₂)

Аппроксимируем функцию y=f(x) квадратичной функцией (находим коэффициенты а₁, а₂ и а₃)

Аппроксимируем функцию y=f(x) экспоненциальной функцией (находим коэффициенты а₁ и а₂)

Находим среднее арифметическое и

Промежуточные расчёты (согл. таблице 7)

Находим коэффициент корреляции r

(для линейной аппроксимации)

Находим коэффициент детерминированности r²

для линейной, квадратичной и экспоненциальной аппроксимации

Рис.1. Схема алгоритма для программы расчёта.

Расчет в программе MathCad.

Линейная регрессия

• line (x, y) – вектор из двух элементов (b, a) коэффициентов линейной регрессии b+ax;

• x – вектор действительных данных аргумента;

• y – вектор действительных данных значений того же размера.

Полиномиальная регрессия

Полиномиальная регрессия означает приближение данных (х1, у1) полиномом k-й степени

При k=i полином является прямой линией, при k=2 – параболой, при k=3 – кубической параболой и т. д. Как правило, на практике применяются k<5.

• regress (x,y,k) – вектор коэффициентов для построения полиномиальной регрессии данных;

• interp (s,x,y,t) – результат полиномиальной регрессии;

• s=regress(x,y,k);

• x - вектор действительных данных аргумента, элементы которого расположены в порядке возрастания;

• y – вектор действительных данных значений того же размера;

• k – степень полинома регрессии (целое положительное число);

• t – значение аргумента полинома регрессии.

Кроме рассмотренных, в Mathcad встроено еще несколько видов трехпараметрической регрессии, их реализация несколько отличается от приведенных выше вариантов регрессии тем, что для них, помимо массива данных, требуется задать некоторые начальные значения коэффициентов a, b, c. Используйте соответствующий вид регрессии, если хорошо представляете себе, какой зависимостью описывается ваш массив данных. Когда тип регрессии плохо отражает последовательность данных, то ее результат часто бывает неудовлетворительным и даже сильно различающимся в зависимости от выбора начальных значений. Каждая из функций выдает вектор уточненных параметров a, b, c.