На практике достаточно часто приходится иметь дело с функциями, заданными таблично. Предположим, что при изучении функциональной зависимости Y=f(X) двух величин X и Y был произведен ряд измерений, результаты которых занесены в таблицу:
Если аналитическое выражение указанной зависимости неизвестно, то возникает практически важная задача аппроксимации - найти функцию j(X), значения которой в известных точках по возможности мало отличалось бы от опытных данных. Функция j(X), которая с достаточной точностью определяет зависимость между величинами X и Y, называется эмпирической или аналитической.
Обычно задача аппроксимации распадается на две части. Сначала устанавливают вид зависимости Y=f(X), т.е. класс функции, к которому должна принадлежать искомая эмпирическая формула, определяют, является ли она линейной, квадратичной, логарифмической или какой-либо другой.
После выбора типа функции определяют ее параметры. Обычно определение параметров при известном виде зависимости осуществляется по методу наименьших квадратов. При этом функция φ(Xi) считается наилучшим приближением, если для нее сумма квадратов отклонений “теоретических” значений φ(Xi) найденных по эмпирической формуле, от соответствующих опытных значений Yi, имеет наименьшее значение по сравнению с другими функциями, из числа которых выбирается искомое приближение.
В Excel задачу аппроксимации можно решить путем построения точечного графика с последующим подбором подходящей аппроксимирующей функции (линии тренда).
При подборе эмпирической функции с помощью линии тренда возможны следующие варианты функций:
1. Линейная — у = а + bх, где а и b — константы,Обычно применяется в простейших случаях, когда экспериментальные данные возрастают или убывают с постоянной скоростью.
2. Полиномиальная — у = а0 + a1x + а2х2 +... + anxn, до шестого порядка включительно (п £ 6), ai — константы. Используется для описания экспериментальных данных, попеременно возрастающих и убывающих. Степень полинома определяется количеством экстремумов (максимумов или минимумов) кривой. Полином второй степени может описать только один максимум или минимум, полином третьей степени может иметь один или два экстремума, четвертой степени — не более трех экстремумов и т. д.
3. Логарифмическая — у = a ln х + b, где а и b — константы, ln — функция натурального логарифма. Функция применяется для описания экспериментальных данных, которые вначале быстро растут или убывают, а затем постепенно стабилизируются.
4. Степенная — у = b х а, где а и b — константы. Аппроксимация степенной функцией используется для экспериментальных данных с постоянно увеличивающейся (или убывающей) скоростью приращения. Данные не должны иметь нулевых или отрицательных значений.
5. Экспоненциальная — у = b e a x , где а и b — константы, е — основание натурального логарифма. Применяется для описания экспериментальных данных, которые быстро растут или убывают, а затем постепенно стабилизируются.
При решении задачи с помощью линии тренда степень близости аппроксимации экспериментальных данных выбранной функцией оценивается коэффициентом детерминации (R2). Таким образом, если есть несколько вариантов аппроксимирующих функций, нужно выбрать функцию с наибольшим коэффициентом детерминации (стремящимся к 1).
Пользуясь вышеприведёнными рекомендациями, выполним подбор коэффициентов аппроксимирующего уравнения с помощью Линий тренда, для двух - трех различных типов эмпирических уравнений, графики которых наиболее похожи на график опытных данных и выберем из них тот тип, у которого наибольший коэффициент детерминации.
Построение точечного графика
с подбором аппроксимирующей функции (линии тренда)
1. Запустите табличный процессор Excel.
2. Для того, чтобы установить вид эмпирической зависимости, рекомендуется отложить экспериментальные точки на графике, и по их расположению определить тип функции, которая даёт наилучшее приближение к экспериментальным данным.
3. Для построения графика необходимо ввести в таблицу исходные опытные данные. В ячейках А1:А8 будут располагаться значения опытных данных - независимые переменные х. В ячейки В1:В8 введите значения зависимой переменной y, приведённые в таблице опытных данных (см. свой вариант). Данные расположить вертикально!
4. Для построения графика выделите диапазон ячеек, на базе которого будет строиться график – А1:В8. На вкладке Вставка в группе Диаграммы выберите Точечная с гладкими кривыми и маркерами.
5. Наведите курсор на одну из точек на диаграмме и щелкните правой кнопкой мыши, в появившемся контекстном меню выберите Добавить линию тренда .
В окне Формат линии тренда выберите функцию, график которой наиболее похож на график опытных данных.
Установите флажки в окнах показывать уравнение на диаграмме и поместить на диаграмме величину достоверности аппроксимации (R2) и нажмите Закрыть.
На диаграмме появится линия тренда, проходящая очень близко от экспериментальных точек, и будет выведено уравнение выбранной функции и величина достоверности.
6. На вкладке Макет в группе Подписи выберите кнопку Название диаграммы, поместите название выбранной функции над диаграммой.
Повторите п.п. 4 – 6, выбрав еще два типа функций (полиномиальную функцию не выбирать!).
Таким образом, у вас получится три графика с различными линиями тренда и соответствующими уравнениями и коэффициентами детерминации. По линии тренда, у которой коэффициент детерминации ближе всего к 1, строится таблица с выбранной эмпирической формулой и вычисленными коэффициентами. Коэффициенты эмпирического уравнения a и b необходимо ввести в таблицу, вычислить по ним значения функции и оценить погрешности - отклонения от экспериментальных данных.