Анализ табличных данных и нахождение эмпирических зависимостей методом наименьших квадратов в Excel. Способы графического представления результатов.

Контрольная работа

В различных практических исследованиях приходится находить эмпирические функции по отдельным значениям этих функций, полученным на основании опытных данных (измерений). Один из способов получения таких функций–метод наименьших квадратов. Пусть, например, в результате наблюдений составлена таблицу отдельных значений величин x и у:

		…		…
		…		…

Необходимо установить функциональную зависимость между величинами x и у. Эта зависимость будет приближённой. Её называют эмпирической зависимостью между x и у, т.е. полученной по результатам наблюдений, эксперимента.

Наиболее просто проблема решается в том случае, когда связь между x и у разыскивают в виде линейной зависимости, выражающейся линейным уравнением

.

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

Для этого составляют сумму и подбирают параметры а и b так, чтобы функция S(a,b) принимала наименьшее значение, т.е. чтобы сумма квадратов погрешностей была наименьшей(отсюда и название метода). Так как значения линейной функции

отличаются от табличных значений , то будут отличны от нуля.

В курсе высшей математики показывается, что а и b находятся из системы уравнений

Решая эту систему, найдем а и b и подставив их в уравнение

получим эмпирическую функцию исследуемой зависимости.

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

Покажем как решается эта проблема в Excel и простые, но эффективные способы графического представления результатов на примере следующей задачи.

Задание. Изучается динамика выноса вещества из почвы с урожаем. По результатам отчётов и наблюдений получена таблица значений количества y выноса вещества со временем x.

		1,5		2,5		3,5		4,5		5,5
				10,25

 

1). Построить гистограмму количества y выноса вещества со временем x, указать тренд; указать название гистограммы и осей, сделать подписи данных и заливку.

2). Методом наименьших квадратов найти эмпирическую функцию , где - время наблюдений, - количество выноса вещества а) в виде линейной, б) в виде полиномиальной при n=2(квадратичной); изобразить график каждой функции;

3). Вычислить табличные значения по каждому из трендов а) и б).

4). Вычислить значения (интерполяция) и (экстраполяция).

Пример решения. 1). Построить гистограмму количества y выноса вещества со временем x, указать тренд; указать название гистограммы и осей, сделать подписи данных и заливку.

		1,5		2,5		3,5		4,5		5,5
				10,25

2). Методом наименьших квадратов найти эмпирическую функцию , где - время наблюдений, - количество выноса вещества а) в виде линейной, б) в виде полиномиальной при n=2(квадратичной); изобразить график каждой функции.

		1,5		2,5		3,5		4,5		5,5
				10,25

 

а) в виде линейной зависимости:

б) в виде полиномиальной при n=2(квадратичной

3). Вычислить табличные значения по каждому из трендов а) и б).

4). Вычислить значения (интерполяция) и (экстраполяция).

ВАРИАНТЫ ЗАДАНИЙ

Вариант 1	Вариант 2	Вариант 3	Вариант 4

 

 

Вариант 5	Вариант 6	Вариант 7	Вариант 8

 

Вариант 9	Вариант 10