Подбор аппроксимирующей функции по графику

Эмпирических формул

Порядок определения коэффициентов

 

1. Загрузите программу Excel.

2. Составьте таблицу:

 

Xi	Yi	Xi2	Xi3	Xi 4	XiYi	XiYi2
...
...
S Xi	SYi	S Xi2	S Xi3	S Xi 4	S XiYi	S XiYi2

 

3. Занесите в два первых столбца экспериментальные значения X и Y.

5. Отсортируйте данные с помощью команды Данные, Сортировка по возрастанию значения аргумента.

6. Занесите в первые ячейки последующих столбцов соответствующие формулы. Скопируйте формулы в последующие строки таблицы.

7. После заполнения таблицы просуммируйте все полученные значения по столбцам, используя кнопку Автосуммирование панели инструментов Стандартная или функцию СУММ мастера функций.

Таким способом находятся все коэффициенты, которые необходимы для решения систем (6.13) и (6.15).

8. Решите полученные системы уравнений одним из известных способов.

9. Составьте новую таблицу. Перекопируйте в эту таблицу значения х_i и y_i. Вычислите значения вновь полученных функций (линейной f₁(x_i) и квадратичной f₂(x_i)), отклонения значений функций от соответствующих значений y_i, а также квадраты этих отклонений (f₁(x_i)-y_i)² и (f₂(x_i)-y_i)² при соответствующих значениях аргумента x_i.

Xi	Yi	f1(xi)	f2(xi)	f1(xi)-yi	f2(xi)-yi	(f1(xi)-yi )2	(f2(xi)-yi)2
...
...
						S(f1(xi)-yi)2	S(f2(xi)-yi )2

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

Из приведенного алгоритма видно, что нахождение коэффициентов аппроксимирующей функции достаточно трудоемкая задача. К счастью электронная таблица Excel имеет ряд технологий для решения подобных задач: это использование линий тренда на графиках функций и использование встроенных функций ЛИНЕЙН, ЛГРФПРИБЛ, ТЕНДЕНЦИЯ и РОСТ.

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

Листинг 6.11. Определение коэффициентов эмпирических формул с помощью линий тренда
X	Y
0,1	2,56
0,2	3,45
0,3	2,78
0,4	2,12
0,5	2,98
0,6	3,65
0,7	4,01
0,8	5,8

- составьте таблицу значений экспериментальных данных X и Y (Листинг 6.11);

- постройте график функции (точечный или график);

- щелкните правой кнопкой мыши по линии графика – открывается контекстное меню;

- выберите в этом меню команду Добавить линию тренда. Открывается окно диалога Линия тренда (рис. 6.14);

- выберите в этом окне подходящую функцию, в данном примере полиномиальную, и выберите степень функции таким образом, чтобы она наиболее точно описывала экспериментальные данные. Достоверность аппроксимации оценивается коэффициентом R². R^{2 -} коэффициент детерминированности является квадратом коэффициента корреляции (r). Он может принимать значения от 0 до 1. Чем больше этот коэффициент, тем ближе располагаются точки линии тренда к экспериментальным точкам на графике. Приближение считается хорошим, если R² больше 0,9. Если R²=1, то это означает полное совпадение прогнозируемых и фактических данных;

- откройте закладку Параметры в окне диалога Линия тренда и установите флажки Показывать уравнения на диаграмме и Поместить на диаграмму величину достоверности аппроксимации (R^2).