Аппроксимация функции заданной большой таблицей. Аппроксимация данных

Как и предыдущие, этот урок с аналогичным текстом лучше смотреть не листе Excel (см. Уроки аппроксимации.xls, Лист1)

Аппроксимация в Excel проще всего реализуется с помощью программы построения трендов. Для выяснения особенностей аппроксимации возьмем какой-либо конкретный пример. Например, энтальпию насыщенного пара по книге С.Л.Ривкина и А.А.Александрова "Теплофизические свойства воды и водяного пара", М., "Энергия", 1980г. В колонке P поместим значения давления в кгс/см2, в колонке i" - энтальпию пара на линии насыщения в ккал/кг и построим график с помощью опции или кнопки "Мастер диаграмм".

Щелкнем правой кнопкой по линии на рисунке, затем левой кнопкой по опции "Добавить линию тренда" и смотрим - какие услуги предлагаются нам этой опцией в части реализации аппроксимации в Excel.

Нам предлагается на выбор пять типов аппроксимации: линейная, степенная, логарифмическая, экспоненциальная и полиноминальная. Чем они хороши и чем могут нам помочь? - Нажимаем кнопку F1, затем щелкаем по опции "Мастер ответов" и в появившееся окошко вводим нужное нам слово "аппроксимация", после чего щелкаем по кнопке "Найти". Выбираем в появившемся списке раздел "Формулы для построения линий тренда".

Получаем следующую информацию в несколько измененной нами

редакции:

Линейная:

где b - угол наклона и a - координата пересечения оси абсцисс (свободный член).

Степенная:

Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:

где c и b - константы.

Логарифмическая:

Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:

где a и b - константы.

Экспоненциальная:

Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:

где b и k - константы.

Полиноминальная:

Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:

y=a+b1*x+b2*x^2+b3*x^3+...b6*x^6

где a, b1, b2, b3,... b6 - константы.

Снова щелкаем по линии рисунка, затем по опции "Добавить линию тренда", далее по опции "Параметры" и ставим флажки в окошках слева от записей: "показывать уравнение на диаграмме" и "поместить на диаг- рамму величину достоверности аппроксимации R^2, после чего щелкаем по кнопке OK. Пробуем все варианты аппроксимации по порядку.

Линейная аппроксимация дает нам R^2=0.9291 - это низкая достоверность и плохой результат.

Для перехода к степенной аппроксимации щелкаем правой кнопкой по линии тренда, затем левой кнопкой - по опции "Формат линии тренда", далее по опциям "Тип" и "Степенная". На этот раз получили R^2=0.999.

Запишем уравнение линии тренда в виде, пригодном для расчетов на листе Excel:

y=634.16*x^0.012

В результате имеем:

Максимальная погрешность аппроксимации получилась на уровне 0.23 ккал/кг. Для аппроксимации экспериментальных данных такой результат был бы чудесным, но для аппроксимации справочной таблицы это не слишком хороший результат. Поэтому попробуем проверить другие варианты аппроксимации в Excel посредством программы построения трендов.

Логарифмическая аппроксимация дает нам R^2=0.9907 - несколько хуже, чем по степенному варианту. Экспоненнта в том варианте, который предлагает программа построения трендов, вообще не подошла - R^2=0.927.

Полиноминальная аппроксимация со степенью 2 (это y=a+b1*x+b2*x^2) обеспечила R^2=0.9896. При степени 3 получили R^2=0.999, но с явным искажением аппроксимируемой кривой, в особенности при P>0.07 кгс/см2. Наконец, пятая степень нам дает R^2=1 - это, как утверждается, максимально тесная связь между исходными данными и их аппроксимацией.

Перепишем уравнение полинома в пригодном для расчетов на листе Excel виде:

y=1E+07*x^5-4E+06*x^4+469613*x^3-27728*x^2+1020.8*x+592.44

и сравним результат аппроксимации с исходной таблицей:

Оказалось, что R^2=1 в данном случае лишь блестящая ложь. Реально, самый лучший результат полиноминальной аппроксимации дал самый простой полином вида y=a+b1*x+b2*x^2. Но его результат хуже, чем в варианте степенной аппроксимации y=634.16*x^0.012, где максимальная погрешность аппроксимации находилась на уровне 0.23 ккал/кг. Это все, что мы можем выжать из программы построения трендов. Посмотрим, что мы можем выжать из функции Линейн. Для нее попробуем вариант степенной аппроксимации.

Примечание. Обнаруженный дефект связан с работой программы построения трендов, но не с методом МНК.

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

С помощью построения интерполяционного многочлена n-степени, который проходит непосредственно через все точки заданного массива данных. В данном случае аппроксимирующая функция представляется в виде: интерполяционного многочлена в форме Лагранжа или интерполяционного многочлена в форме Ньютона.

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

Метод наименьших квадратов (в англоязычной литературе Ordinary Least Squares, OLS) - математический метод, основанный на определении аппроксимирующей функции, которая строится в ближайшей близости от точек из заданного массива экспериментальных данных. Близость исходной и аппроксимирующей функции F(x) определяется числовой мерой, а именно: сумма квадратов отклонений экспериментальных данных от аппроксимирующей кривой F(x) должна быть наименьшей.

Аппроксимирующая кривая, построенная по методу наименьших квадратов

Метод наименьших квадратов используется:

Для решения переопределенных систем уравнений, когда количество уравнений превышает количество неизвестных;

Для поиска решения в случае обычных (не переопределенных) нелинейных систем уравнений;

Для аппроксимации точечных значений некоторой аппроксимирующей функцией.

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

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

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

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

В зависимости от условий задачи аппроксимирующая функция представляет собой многочлен степени m

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

∙ В случае если степень аппроксимирующей функции m=1, то мы аппроксимируем табличную функцию прямой линией (линейная регрессия).

∙ В случае если степень аппроксимирующей функции m=2, то мы аппроксимируем табличную функцию квадратичной параболой (квадратичная аппроксимация).

∙ В случае если степень аппроксимирующей функции m=3, то мы аппроксимируем табличную функцию кубической параболой (кубическая аппроксимация).

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

- неизвестные коэффициенты аппроксимирующего многочлена степени m;

Количество заданных табличных значений.

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

Преобразуем полученную линейную систему уравнений: раскроем скобки и перенесем свободные слагаемые в правую часть выражения. В результате полученная система линейных алгебраических выражений будет записываться в следующем виде:

Данная система линейных алгебраических выражений может быть переписана в матричном виде:

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

Аппроксимация исходных данных линейной зависимостью

(линейная регрессия)

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

Координаты узловых точек таблицы;

Неизвестные коэффициенты аппроксимирующей функции, которая задана в виде линейной зависимости.

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

Преобразуем полученную линейную систему уравнений.

Решаем полученную систему линейных уравнений. Коэффициенты аппроксимирующей функции в аналитическом виде определяются следующим образом (метод Крамера):

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

Алгоритм реализации метода наименьших квадратов

1. Начальные данные:

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

Задана степень аппроксимирующего многочлена (m)

2. Алгоритм вычисления:

2.1. Определяются коэффициенты для построения системы уравнений размерностью

Коэффициенты системы уравнений (левая часть уравнения)

- индекс номера столбца квадратной матрицы системы уравнений

Свободные члены системы линейных уравнений (правая часть уравнения)

- индекс номера строки квадратной матрицы системы уравнений

2.2. Формирование системы линейных уравнений размерностью .

2.3. Решение системы линейных уравнений с целью определения неизвестных коэффициентов аппроксимирующего многочлена степени m.

2.4.Определение суммы квадратов отклонений аппроксимирующего многочлена от исходных значений по всем узловым точкам

Найденное значение суммы квадратов отклонений является минимально-возможным.

Аппроксимация с помощью других функций

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

Логарифмическая аппроксимация

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

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

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

Рис. 3.6 Метод Лагранжа

Концепция аппроксимации

Близость исходной и аппроксимирующей функций определяется числовой мерой

- критерием аппроксимации (близости). Наибольшее распространение получил квадратичный критерий, равный сумме квадратов отклонений расчетных значений от "экспериментальных" (т.е. заданных), - критерий близости в заданных точках:

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

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

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

Выделяют две основные задачи:

1) получение аппроксимирующей функции, описывающей имеющиеся данные, с погрешностью не хуже заданной;

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

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

Метод наименьших квадратов

Метод базируется на применении в качестве критерия близости суммы квадратов отклонений заданных и расчетных значений. При заданной структуре аппроксимирующей функции у i расч (х) необходимо таким образом подобрать параметры этой функции, чтобы получить наименьшее значение критерия близости, т.е. наилучшую аппроксимацию. Рассмотрим путь нахождения этих параметров на примере полиномиальной функции одной переменной:

Запишем выражение критерия аппроксимации при b i =1 (i =1, 2,…, n ) для полиномиального у i расч (х):

Искомые переменные а j можно найти из необходимого условия минимума R по этим переменным, т.е. dR / d а р = 0 (для р =0, 1,2,…,k). Продифференцируем по а р (р - текущий индекс):

После очевидных преобразований (сокращение на два, раскрытие скобок, изменение порядка суммирования) получим

Перепишем последние равенства

Получилась система n +1 уравнений с таким же количеством неизвестных а j , причем линейная относительно этих переменных. Эта система называется системой нормальных уравнений. Из ее решения находятся параметры а j аппроксимирующей функции, обеспечивающие minR , т.е. наилучшее возможное квадратичное приближение. Зная коэффициенты, можно (если нужно) вычислить и величину R (например, для сравнения различных аппроксимирующих функций). Следует помнить, что при изменении даже одного значения исходных данных (или пары значений х i , у i , или одного из них) все коэффициенты изменят в общем случае свои значения, так как они полностью определяются исходными данными. Поэтому при повторении аппроксимации с несколько изменившимися данными (например, вследствие погрешностей измерения, помех, влияния неучтенных факторов и т.п.) получится другая аппроксимирующая функция, отличающаяся коэффициентами. Обратим внимание на то, что коэффициенты а j полинома находятся из решения системы уравнений, т.е. они связаны между собой. Это приводит к тому, что если какой-то коэффициент вследствие его малости захочется отбросить, придется пересчитывать заново оставшиеся. Можно рассчитать количественные оценки тесноты связи коэффициентов. Существует специальная теория планирования экспериментов, которая

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

Рис. 3.7 Влияние степени аппроксимирующего полинома М на точность аппроксимации

В случае постановки другой задачи - найти аппроксимирующую функцию, обеспечивающую погрешность не хуже заданной, - необходимо подбирать и структуру этой функции. Эта задача значительно сложнее предыдущей (найти параметры аппроксимирующей функции заданной структуры, обеспечивающей наилучшую возможную погрешность) и решается в основном путем перебора различных функций и сравнения получающихся мер близости. Для примера на рис. 3.7 приведены для визуального сравнения исходная и аппроксимирующие функции с различной степенью полинома, т.е. функции с различной структурой. Не следует забывать, что с повышением точности аппроксимации растет и сложность функции (при полиномиальных аппроксимирующих функциях), что делает ее менее удобной при использовании.

Рассмотрим решение задачи аппроксимации и интерполяции с шумом в

программе MathCAD (рисунок 3.8).

Пример 3.1. В ходе проведения эксперимента были получены данные, представленные в таблице 3.1. Необходимо способом наименьших квадратов подобрать для заданных значений x и y квадратичную функцию . Построить на одной координатной плоскости экспериментальные данные и аппроксимирующую функцию.

Таблица 3.1 Данные эксперимента

Решение. Для определения коэффициентов квадратичной функции построим дополнительную таблицу 3.2.

Таблица 3.2 Дополнительная таблица

Строим систему уравнений

В нашем случае она будет иметь вид:

Из полученной системы уравнений находим

Искомая зависимость

Строим график экспериментальных данных и найденной зависимости.

Рис.3.8 Аппроксимация и интерполяция в задаче с помехами

Если требуется построить зависимость в виде показательной функции , то необходимо составить систему:

(3.7)

Для этого строится таблица

Среди различных методов прогнозирования нельзя не выделить аппроксимацию. С её помощью можно производить приблизительные подсчеты и вычислять планируемые показатели, путем замены исходных объектов на более простые. В Экселе тоже существует возможность использования данного метода для прогнозирования и анализа. Давайте рассмотрим, как этот метод можно применить в указанной программе встроенными инструментами.

Наименование данного метода происходит от латинского слова proxima – «ближайшая» Именно приближение путем упрощения и сглаживания известных показателей, выстраивание их в тенденцию и является его основой. Но данный метод можно использовать не только для прогнозирования, но и для исследования уже имеющихся результатов. Ведь аппроксимация является, по сути, упрощением исходных данных, а упрощенный вариант исследовать легче.

Главный инструмент, с помощью которого проводится сглаживания в Excel – это построение линии тренда. Суть состоит в том, что на основе уже имеющихся показателей достраивается график функции на будущие периоды. Основное предназначение линии тренда, как не трудно догадаться, это составление прогнозов или выявление общей тенденции.

Но она может быть построена с применением одного из пяти видов аппроксимации:

Линейной;
Экспоненциальной;
Логарифмической;
Полиномиальной;
Степенной.

Рассмотрим каждый из вариантов более подробно в отдельности.

Способ 1: линейное сглаживание

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

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

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

В конкретно нашем случае формула принимает такой вид:

y=-0,1156x+72,255

Величина достоверности аппроксимации у нас равна 0,9418 , что является довольно приемлемым итогом, характеризующим сглаживание, как достоверное.

Способ 2: экспоненциальная аппроксимация

Теперь давайте рассмотрим экспоненциальный тип аппроксимации в Эксель.

Общий вид функции сглаживания при этом такой:

где e – это основание натурального логарифма.

В конкретно нашем случае формула приняла следующую форму:

y=6282,7*e^(-0,012*x)

Способ 3: логарифмическое сглаживание

Теперь настала очередь рассмотреть метод логарифмической аппроксимации.

В общем виде формула сглаживания выглядит так:

где ln – это величина натурального логарифма. Отсюда и наименование метода.

В нашем случае формула принимает следующий вид:

y=-62,81ln(x)+404,96

Способ 4: полиномиальное сглаживание

Настал черед рассмотреть метод полиномиального сглаживания.

Формула, которая описывает данный тип сглаживания, приняла следующий вид:

y=8E-08x^6-0,0003x^5+0,3725x^4-269,33x^3+109525x^2-2E+07x+2E+09

Способ 5: степенное сглаживание

В завершении рассмотрим метод степенной аппроксимации в Excel.

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

Общая формула, описывающая данный метод имеет такой вид:

В конкретно нашем случае она выглядит так:

y = 6E+18x^(-6,512)

Как видим, при использовании конкретных данных, которые мы применяли для примера, наибольший уровень достоверности показал метод полиномиальной аппроксимации с полиномом в шестой степени (0,9844 ), наименьший уровень достоверности у линейного метода (0,9418 ). Но это совсем не значит, что такая же тенденция будет при использовании других примеров. Нет, уровень эффективности у приведенных выше методов может значительно отличаться, в зависимости от конкретного вида функции, для которой будет строиться линия тренда. Поэтому, если для этой функции выбранный метод наиболее эффективен, то это совсем не означает, что он также будет оптимальным и в другой ситуации.

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