Пример выполнения аппроксимации полиномами

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

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

Пример 2.1. Функция задана таблично:

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

Решение

Вычисление коэффициентов систем оформим в виде таблицы.

Тогда система уравнений для нахождения коэффициентов линейной регрессии

Решаем систему методом Гаусса.

Получаем .

Ответ: аппроксимирующая функция .

Система уравнений для нахождения коэффициентов полиномиальной регрессии

Решаем систему методом Гаусса.

Получаем .

Ответ: аппроксимирующая функция .

Построим полученные зависимости и отметим заданные точки.

Рис. 2.1

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

Отсюда , а , то есть можно считать многочлен второго порядка аппроксимирующей функцией с точностью до ε.

Выполнение регрессии в Ехсеl

Выполнение линейной регрессии с помощью функций Ехсеl

В том случае, когда нужно вычислить угловой коэффициент а₁ и найти точку пересечения с осью ординат прямой линии а₀ очень полезными окажутся функции Ехсеl НАКЛОН() и ОТРЕЗОК(). Добавив к ним функцию КВПИРСОН(), вычисляющую квадрат коэффициента корреляции, получим простейший набор, позволяющий решить задачи по анализу данных.

Пример 2.2.

Проанализируем набор данных из рассмотренного выше примера 2.1. Это позволит ознакомиться с основными приемами проведения регрессионного анализа в Ехсеl.

1. Вводим исходные данные.

2. Для определения углового коэффициента прямой линии, лучше всего описывающей данные, предназначена функция НАКЛОН(). У функции НАКЛОН() два аргумента, расположенные в таком порядке: диапазон ячеек, содержащий значения у (значения зависимой переменной) В4:В10, и диапазон ячеек, содержащих значения х (значения независимой переменной) А4:А10 (рис. 2.2).

Рис. 2.2

Аналогично, точку пересечения прямой регрессии с осью ординат можно найти с помощью функции ОТРЕЗОК() с теми же аргументами (рис. 2.3).

Рис. 2.3

Квадрат коэффициента корреляции (R²) вычисляется с помощью функции КВПИРСОН() с теми же аргументами, что и у двух предыдущих функций (рис. 2.4).

Рис. 2.4

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