Построение аналитических зависимостей и анализ эмпирических данных в экосистеме

Напомним некоторые статистические оценки. Наблюдаемые значения величин , можно рассматривать как выборочные значения двух случайных величин X,Y. По выборочным данным можно найти выборочные средние и выборочные квадратичные отклонения X и Y, а также выборочный коэффициент корреляции, а именно:

_,       (18)

 

= _,   =     (19)

 

Для вычисления , можно применить и более простые формулы, которые выводятся в курсе теории вероятностей с помощью простых алгебраических преобразований:

= _,        (20)

r= ₌      (21)

 здесь   - выборочные средние величин X, Y; , - выборочные квадратичные отклонения величин X,Y; r – выборочный коэффициент корреляции.

 

Известно, что линейное уравнение (5), называемое в статистике уравнением линейной регрессии, проходит через  точку (), а коэффициент , называемый в статистике коэффициентом регрессии, связан с коэффициентом корреляции r. Имеют место следующие соотношения:

      (22)

= r      (23)

 

Коэффициент корреляции характеризует  меру линейной связи между величинами X,Y и может принимать значения в пределах от -1 до 1. Чем ближе к единице , тем теснее линейная связь между X,Y. Если = 1, то Y линейно зависит от X, т.е. выполняется соотношение:

= + ,

поэтому ошибка представления эмпирических данных равна 0.

Анализируя формулу (23), легко увидеть, что если линейная функция возрастающая, т.е. > 0, то и коэффициент корреляции положителен. Соответственно, если линейная функция убывает, то коэффициент корреляции отрицателен.

Если коэффициент корреляции равен 0 или близок к 0, то между величинами X,Y нет линейной корреляционной связи, или она является слабой, несущественной.

2.5 Оценка точности аппроксимации.

Квадратичная ошибка (погрешность) аппроксимации функции в соответствии с формулой (3) равна:

 

С целью оценки относительной погрешности  при аппроксимации функции рассматривают  величину суммарной погрешности  по отношению к общему разбросу данных. Общий разброс данных складывается из отклонений теоретических значений от среднего и эмпирических значений от теоретических значений. Вводятся обозначения:

          (24)

          (25)

          (26)

В случае линейной функции получим:

     (27)

В случае квадратичной функции:

    (28)

 

 

В случае экспоненциальной функции:

     (29)

     (30)

По аналогии легко написать формулы  для вычисления регрессионных сумм и ошибки аппроксимации функцией любого вида.

Отметим, что для аппроксимирующей функции, линейной относительно параметров, верно:

Относительная ошибка аппроксимации  есть отношение .

Величина

(31)

называется  коэффициентом детерминированности  и характеризует меру точности аппроксимации  табличных данных функцией любого вида. Если , то ошибка аппроксимации равно 0 и теоретические значения функции совпадают с эмпирическими значениями.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3 Постановка задания

Предположим, что y является функцией  x .

Требуется:

1. Аппроксимировать функцию y=f(x) линейной функцией  y=a₁+a₂x.  
2. Аппроксимировать функцию y=f(x) квадратичной функцией y=a₁+a₂x+a₃x²
3. Аппроксимировать функцию y=f(x) экспоненциальной функцией
4. определить основные статистические параметры данных случайных величин, определить коэффициент детерминированности полученных уравнений
5. построить график табличной функции и в той же системе координат график аппроксимирующей функции (для каждого вида аппроксимации отдельный рисунок)
6. Решение выполнить с помощью табличного процессора MS Excel непосредственно по формулам, а также с использованием статистических функций MS Excel.
7. Составить решение в Mathcad для выполнения указанных расчетов, сравнить результаты вычислений с результатами расчетов в MS Excel и добиться их совпадения в пределах используемой точности.

Вариант 7.

Xi	Yi
0,86	1,89
1,54	6,76
2,78	9,79
3,99	15,98
5,34	54,67
5,78	76,98
6,45	122,32
7,23	167,89
7,65	188,65
7,91	219,87
8,34	256,78
8,78	277,71
8,88	300,45
9,15	342,56
9,25	378,45
9,54	399,89
9,75	425,98
10,32	466,89
10,67	488,98
10,78	500,12

 

4 Выполнение расчетов в MS Excel по расчетным формулам

 

1. Составим  расчетную таблицу в MS Excel.

При вычислении сумм мы пользуемся формулами, входящими в системы следующие системы:

      Для решения систем можно использовать  различные методы. Но матрицы  систем  являются невырожденными  квадратными матрицами, поэтому  система уравнений имеет единственное  решение.При этом необходимо выполнять вычисления в следующей последовательности:

Введем в  первую строку таблицы в ячейки A1:J1 заголовки столбцов:

1. номер i, x_i , y_i , x_i² , x_i y_i , x_i³ , x_i⁴, x_i² y_i , ln y_i , x_i ln y_i
2. Заполним первый столбец номерами наблюдений от 1 до n . для этого в ячейку A2 введем 1 и заполним столбец с помощью арифметической прогрессии до значения n
3. Заполним следующий столбец значениями x_i
4. Заполним третий столбец значениями  y_i
5. В ячейку D2 введем формулу:   =B2*B2
6. В ячейку E2 введем формулу:   =B2*C2
7. В ячейку F2 введем формулу:   =B2*D2
8. В ячейку G2 введем формулу:   =D2*D2
9. В ячейку H2 введем формулу:   = D2*C2
10. В ячейку I2 введем формулу:   = LN(C2)
11. В ячейку J2 введем формулу:   = B2*I2
12. Формулы в ячейках D2:J2 скопируем в нижележащие ячейки для всех номеров i
13. В строке с номером n  вычислим суммы столбцов с именами от B до J. Выполним Автосуммирование во втором столбце таблицы, при этом в ячейке B27 получим формулу: =СУММ(B2:B26), а затем скопируем полученную формулу в ячейки этой строки на всю ширину таблицы.

            Результаты вычислений представлены в табл.1, скопированной из рабочей книг в MS Excel.

 

 

 

 

 

 

 

 

 

Таблица 1

i	Xi	Yi	(X^2)i	XiYi	(X^3)i	(X^4)i	(X^2)i Yi	LnYi	XiLnYi
1	0,860	1,890	0,740	1,625	0,636	0,547	1,398	0,637	0,547
2	1,540	6,760	2,372	10,410	3,652	5,624	16,032	1,911	2,943
3	2,780	9,790	7,728	27,216	21,485	59,728	75,661	2,281	6,342
4	3,990	15,980	15,920	63,760	63,521	253,450	254,403	2,771	11,058
5	5,340	54,670	28,516	291,938	152,273	813,139	1558,948	4,001	21,367
6	5,780	76,980	33,408	444,944	193,101	1116,121	2571,779	4,344	25,106
7	6,450	122,320	41,603	788,964	268,336	1730,768	5088,818	4,807	31,003
8	7,230	167,890	52,273	1213,845	377,933	2732,456	8776,097	5,123	37,042
9	7,650	188,650	58,523	1443,173	447,697	3424,883	11040,270	5,240	40,085
10	7,910	219,870	62,568	1739,172	494,914	3914,767	13756,848	5,393	42,659
11	8,340	256,780	69,556	2141,545	580,094	4837,981	17860,487	5,548	46,272
12	8,780	277,710	77,088	2438,294	676,836	5942,621	21408,220	5,627	49,401
13	8,880	300,450	78,854	2667,996	700,227	6218,016	23691,804	5,705	50,663
14	9,150	342,560	83,723	3134,424	766,061	7009,457	28679,980	5,836	53,403
15	9,250	378,450	85,563	3500,663	791,453	7320,941	32381,128	5,936	54,909
16	9,540	399,890	91,012	3814,951	868,251	8283,111	36394,629	5,991	57,156
17	9,750	425,980	95,063	4153,305	926,859	9036,879	40494,724	6,054	59,030
18	10,320	466,890	106,502	4818,305	1099,105	11342,761	49724,906	6,146	63,428
19	10,670	488,980	113,849	5217,417	1214,768	12961,572	55669,835	6,192	66,072
20	10,780	500,120	116,208	5391,294	1252,727	13504,392	58118,145	6,215	66,996
21	11,220	555,430	125,888	6231,925	1412,468	15847,889	69922,194	6,320	70,908
22	11,390	578,980	129,732	6594,582	1477,649	16830,418	75112,291	6,361	72,455
23	11,390	623,420	129,732	7100,754	1477,649	16830,418	80877,586	6,435	73,297
24	11,430	630,760	130,645	7209,587	1493,271	17068,090	82405,577	6,447	73,688
25	12,190	686,020	148,596	8362,584	1811,386	22080,801	101939,897	6,531	79,612
n	190,420	7091,200	1737,064	70440,087	16760,965	167086,032	715881,758	121,323	1075,830
	Σxi	Σyi	Σ(X^2)i	ΣXiYi	Σ(X^3)i	Σ(X^4)i	Σ(X^2)i Yi	ΣLnYi	ΣXiLnYi

 

 

 

Аппроксимируем функцию y=f(x) линейной функцией  y=a₁+a₂x.                                                                       

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

 

 

Сформируем матрицу системы  в ячейках C33:D34 по формулам:

1. В ячейку С33 введем формулу =A26 (n)
2. В ячейку D34 введем формулу =B27 (сумма x)
3. В ячейку С33 введем формулу =D34 (сумма x)
4. В ячейку D34 введем формулу =E33 (сумма x²)

Сформируем  правую часть системы (6) в ячейках  E33:E34 по формулам:

1. В ячейку E33 введем формулу =C27 (сумма y)
2. В ячейку E34 введем формулу =F27 (сумма xy)

Решим систему по формуле    с использованием матричных функций. Для этого выделим область результата, например ячейки H33:H34 и введем формулу:  {=МУМНОЖ(МОБР(C33:D34); E33:E34)}. В ячейках E33:E34 получены параметры линейной аппроксимирующей функции.

Расчеты показаны в табл. 2, скопированной  из MS Excel:

Расчет параметров линейной аппроксимации					Таблица 2
Матрица системы		Правая часть		Решение
25	190,42	7091,2		а1	-152,834
190,42	1737,064	70440,09		а2	57,30516

Уравнение линейной аппроксимации имеет вид:

y= -152,834 + 57,30516X

 

 

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

 

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

В ячейку С39 введем формулу =А26; в ячейку D39 введем формулу: =В27; в ячейку С40 введем формулу: =D39;

в ячейку D40 введем формулу =D27; в ячейку С41 введем формулу =D40;

в ячейку D41 введем формулу =F27; в ячейку E39 введем формулу =D27; в ячейку Е40 введем формулу =Е27; в ячейку Е41 введем формулу Н27. Таким образом мы формируем матрицу системы.

Формируем правую часть. В ячейку F39 введем формулу =С27; в ячейку F40 - =Е27; в ячейку F41 формулу =Н27.

Для вычисления матрицы выделим область результата Н39:Н41 и введем формулу: =МУМНОЖ(МОБР(C39:E41);F39:F41). В ячейках Н39:Н41 получим результат квадратичной аппроксимирующей функции.

Расчеты показаны в табл. 3:

Таблица 3

Расчет параметров квадратичной аппроксимации
Матрица системы			Правая часть	Решение
25	190,42	1737,064	7091,2	а1	20,912
190,42	1737,064	16760,96	70440,09	а2	-30,701
1737,064	16760,96	167086	715881,8	а3	7,146

 

Уравнение квадратичной аппроксимации  имеет вид:

y=20,912-30,701+7,146

 

Составим  и решим следующую систему:

 

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

Введем в ячейку С46 формулу: =А26; в ячейку С47 формулу: =В27;

в ячейку D46 формулу: =В27; в ячейку D47 формулу: =D27.

Формируем правую часть: в ячейку Е46 введем формулу =I27; в ячейку E47 формулу =Е27.

Выбелим область Н46:Н47 и введем формулу: =МУМНОЖ(МОБР(C46:D47);E46:E47).

 Расчеты приведены в таблице 4.

	Расчет параметров экспоненциальной аппроксимации Таблица 4
	Матрица системы				Решение
	25	190,42	121,323
	190,42	1737,064	1075,83		c	0,821
					а2	0,529
					a1	2,273

 

Найдем коэффициент a₁ по формуле a_1`= e^c , для этого в ячейку H49 введем формулу =EXP(H47)

 

Уравнение экспоненциальной аппроксимации имеет вид:

y=

 

 

Далее вычисляем  теоретические значения функции  по формулам :

 

      

 

введем в  ячейку К2 формулу: =$H$33+$H$34*B2, в ячейку L2 – формулу =$H$39+$H$40*B2+$H$41*B2^2, в ячейку M2 – формулу =$H$49*EXP($H$48*B2)

Скопируем формулу  для всех значений x.

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