Работа «Аппроксимация экспериментальных данных»

Если при проведении опыта необходимо найти функцию зависимости некоторых экспериментальных данных, то используется аппроксимация данных (сглаживание экспериментальных зависимостей). Сначала устанавливается вид зависимости y=f(x) и вид эмпирической формулы (т.е.является ли она линейной, квадратичной, логарифмической или какой-либо другой). После этого определяются численные значения неизвестных параметров выбранной эмпирической формулы, для которых приближение к заданной функции оказывается наилучшим.

В Excel аппроксимация экспериментальных данных осуществляется путем построения их графика с последующим подбором подходящей аппроксимирующей функции (линии тренда). Возможны следующие варианты функций.

1. Линейная: y=ax+b (обычно применяется, когда экспериментальные данные возрастают или убывают с постоянной скоростью).
2. Полиномиальная: y=a₀+a₁x+a₂x²+…+a_nxⁿ (используется для описания экспериментальных данных, попеременно возрастающих и убывающих; степень полинома определяется количеством экстремумов (максимумов или минимумов) кривой).
3. Логарифмическая: y=a ln x+b, где а и b – константы(экспериментальные данные вначале быстро растут, а затем постепенно стабилизируются).
4. Степенная: y=bx^а, где а и b – константы (экспериментальные данные с постоянно увеличивающейся (или убывающей) скоростью роста; данные не должны иметь нулевых или отрицательных значений).
5. Экспоненциальная: y=be^ax, где а и b – константы, е – основание натурального логарифма (экспериментальные данные быстро растут или убывают, а затем постепенно стабилизируются).

Степень близости аппроксимации экспериментальных данных выбранной функции оценивается коэффициентом детерминации (R2). Необходимо выбрать функцию с наиболее большим (стремящимся к 1) коэффициентом детерминации. Для осуществления аппроксимации на диаграмме экспериментальных данных необходимо щелчком правой кнопкой мыши вызвать меню и выбрать пункт Добавить линию тренда.

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

Решение:

1. Вводим данные в рабочую таблицу. В ячейку А1 слово Год. В ячейки А2:А6 последовательно вводим годы. Столбец В заполняем продукцией.
2. Для построения диаграммы выберем График с маркерами. В качестве диапазона данных выбираем В1:В6 (переключатель в ряды в столбцах). Во вкладке ряд: диапазон подписей Х: А2:А6. Название диаграммы – Производство продукции, название осей Х и Y: Годы и условные единицы.
3. Осуществим аппроксимацию полученной кривой полиномиальной функцией второго порядка, поскольку кривая довольно гладкая и не сильно отличается от прямой линии. Устанавливаем курсор на одну из точек и щелкаем правой кнопкой мыши. Выбираем пункт Добавить линию тренда. На вкладке Тип выбираем – полиномиальная и устанавливаем степень 2. На вкладке параметры устанавливаем флажки показывать уравнение на диаграмме и поместить на диаграмме величину достоверности аппроксимации.