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

 Для этого  введем в ячейку О2 формулу  =(K2-C2)^2, в ячейку Р2 – формулу =(L2-C2)^2, в ячейку Q2 – формулу =(М2-С3)^2.

Эту формулу копируем в нижележащие ячейки для всех значений х.

 

Вычисления  полученных остаточных сумм и теоретических значений показаны в таблице 5, где Sлин, Sквадр и Sэксп – остаточные суммы, а Yi – теоретические значения.

 

 

 

 

 

                                                                                                   Таблица 5

Yiлин	Yiквадр	Yiэксп	Sлин	Sквадр	Sэксп
-103,551	-0,205	3,583	3,572	1111,91	4,390
-64,584	-9,418	5,1365	45,697	508,963	261,754
6,474	-9,204	9,901	95,844	10,993	360,788
75,813	12,191	18,787	255,360	358,067	14,35
153,175	60,763	38,389	298,809	970,357	37,126
178,389	82,222	48,456	592,92	1028,97	27,486
216,784	120,215	69,082	1496,18	892,512	4,431
261,482	172,528	104,393	2818,05	875,531	21,509
285,55	204,298	130,383	355,82	938,713	244,871
300,449	225,229	149,62	483,82	649,116	28,723
325,091	261,966	187,861	659,97	466,405	26,9
350,305	302,293	237,128	771,84	527,086	604,37
356,035	311,845	250,018	9027,2	3089789	129,850
371,508	338,347	288,428	1173,4	838,002	17,745
377,238	348,427	304,106	1432,4	1,467	901,343
393,857	378,468	354,56	159912	36,393	458,891
405,891	400,972	396,245	18145	403,553	625,387
438,555	465,232	535,79	2179,3	802,855	2,748
458,612	506,991	644,839	2391,4	922,209	324,398
464,915	520,477	683,499	2501	1239,345	414,406
490,129	576,15	862,748	3085,5	426,098	429,323
499,871	598,401	943,980	3352,8	625,105	377,188
499,871	598,401	943,980	3886,5	1526,15	625,932
502,164	603,697	964,180	3978,2	1653,93	732,407
545,715	708,659	1441,66	4706,4	1968,23	512,522
1075,21	2533,3	11,572	35797	15263,7	7188,85
ΣYiлин	ΣYiквадр	ΣYiэксп	ΣSлин	ΣSквадр	ΣSэксп

 

 

 

 Вычисление  статистических оценок для случайных  величин, а также коэффициент  корреляции вычисляются по формулам:

                    

,       

         =

,   =

   r=

=

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

Для вычисления статистических оценок случайных величин  введем в ячейку D53 следующую формулу: =В27/А26,

в ячейку D54 – формулу =С27/А26 для вычисления средних - ,;

перед тем, как  вычислять среднее квадратичное отклонение, вычислим сумму квадратов  значений Y: в ячейку N2 введем формулу =С2^2 и скопируем формулу для всех значений i;

в ячейке N27 получим сумму квадратов по формуле =СУММ(N2:N27).

 В ячейку  D56 введем формулу =(D27/A26-D53^2)^(1/2);

в ячейку D57 введем формулу =(N27/А26-D51)^(1/2);

после этого  вычисляем коэффициент корреляции по приведенной выше формуле: в ячейку D58 введем формулу =(Е27/А26-D53*D54)/(D56*D57).

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

 

                                                                        Таблица 6

Статистические оценки случайных  величин
x	7,617
y	283,648
Среднее квадратичное отклонение
x	3,386
y	378,028
Коэффициент корреляции
	0,513

 

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

Где - является либо линейным, либо квадратичным, либо экспоненциальным теоретическим значением;   - выборочная средняя величина.

Для оценки регрессионных сумм в ячейку R2 введем формулу: =(К2-$D$54)^2, а в ячейку Т2 формулу: =(M2-$D$54)^2.

Для вычисления регрессионных сумм введем в ячейку О27 формулу: =СУММ(О2:О26) и скопируем ее в ячейки Р27:Т27.

Результаты  вычислений представлены в таблице 7.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Таблица 7

Sреглин	Sрегкв	Sрегэкс
1499,4	8057,77	7843,89
1212,5	8588,17	7756,61
7682,19	8576,54	7493,88
4319,11	7368,49	7015,99
1702,04	4967,65	6015,96
1107,27	4057,13	5531,99
4470,749	2671,36	4603,18
491,3153	1234,69	3213,07
36,19625	629,365	2348,97
282,3029	341,734	1796,37
1717,53	470,0843	917,017
4443,203	347,671	216,045
5240,004	795,081	113,956
7719,426	299,036	22,856
8759,234	419,399	418,5679
1214,08	899,881	5028,618
1494,44	1376,98	1267,3
2399,27	3297,75	6357,67
3061,44	4988,12	13045,4
3285,97	5608,96	15988,2
4263,79	8555,5	33535
4675,74	9906,67	43603,4
4675,74	9906,67	43603,4
4774,25	10243,3	46312,1
6867,61	18063,3	134100
81956,3	120219	393228
ΣSреглин	ΣSрегкв	ΣSрегэкс

 

 

 

Коэффициент детерминации расчитаем по следующей  формуле:

При этом, Sполн складывается из суммы остаточной и регрессионной суммы для каждого вида функции , то есть: 

_{При этом необходимо учитывать полученные ранее результаты остаточных и регрессионных  сумм по формулам:}

 

 Таким образом, мы  высчитываем коэффициент детерминированности   для линейной, квадратичной и  экспоненциальной функции. Необходимые данные берем из таблицы 6 и 7: значение регрессионных и остаточных сумм. Значение данного коэффициента должно совпадать с его значением на графике соотвотствующей функции.

Для вычисления коэффициента детерминированности линейной аппроксимации введем  в ячейку К53 формулу: =(1-(N27/R27+R27)). При этом, в ячейке К53 получаем значение коэффициента детерминированности, равное 0,88. Это значение совпадает со значением, указанным на графике линейной функции. Для вычисления коэффициента детерминации квадратичной аппроксимации введем в ячейку К54 следующую формулу:  

=(1-((O27/S27+S27)). Таким образом, получаем в ячейке К54 значение коэффициента детерминированности квадратичной аппроксимации. Введем в ячейку К55 следующую формулу, чтобы найти коэффициент детерминированности экспоненциальной аппроксимации: =(1-(Р27/Т27+Р27)). Получаем значение данного коэффициента. Результаты вычислений представлены в таблице 8.

 

Таблица 8

Коэффициенты детерминированности
Rлин	0,88
Rкв	0,994
Rэксп	0,873

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

5 Построение графиков и вывод уравнений регрессии с помощью встроенных средств MS Excel

 

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

1. Построение прямой линии тренда

Строим  график исходной эмпирической функции:

Для этого выделяем диапазон B2:C26 и строим точечную диаграмму:

Выделяем  на диаграмме ряд полученных точек  и правой кнопкой мыши вызываем контекстное  меню, выбираем команду   - Добавить линию тренда. В диалоговом окне команды выбираем тип Линейная и  параметры Показывать уравнение  на диаграмме  и Поместить на диаграмме  значение достоверности аппроксимации;

2. Построение квадратичной линии тренда.

Выполняется так же как построение прямой линии, но в диалоговом окне Линия тренда выбираем тип Полиномиальная и степень  аппроксимирующего полинома равной 2. Параметры установить такие же, как в пункте 1

3. Построение экспоненциальной линии тренда.

Выполняется аналогично пункту 1, но на вкладке Тип  выбираем тип Экспоненциальная.

 

 

1). График  эмпирической функции

 

 

 

 

 

 

 

 

 

 

 

 

 

2). Построение  квадратичной линии тренда

 

 

 

 

 

3). Построение  экспоненциальной линии тренда

 

 

 

 

 

 

6 Выполнение расчетов в программе Mathcad

Mathcad — система компьютерной алгебры из класса систем автоматизированного проектирования, ориентированная на подготовку интерактивных документов с вычислениями и визуальным сопровождением, отличается легкостью использования и применения для коллективной работы.

При решении  задачи аппроксимации функции одной  переменной можно использовать ранее  приведенные расчетные формулы.

Решение в  данной программе можно начать с  вычисления систем (6), (8), (11).

Уравнение линейной, квадратичной, экспоненциальной аппроксимации  мы можем вычислить с помощью  функции «lsolve».

 

1. Находим параметры линейной аппроксимации:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2). Аналогично  отыщим параметры квадратичной  аппроксимации:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3). Таким  же образом вычисляем и экспоненциальную  аппроксимацию: