Excel, аппроксимация, страница 7

Рис. 10.2-1

В общем случае это выражение вида

(<Функция Y(x)> – <Функция регрессии>)2.

Для уравнения первого порядка (прямой)

(<Функция Y(x)> – (a+b(x))2.

Таким образом, для первой клетки погрешности Прямой

C8=($B8–($B$3+$C$3*$A8))^2.

Аналогичные формулы заносятся (копируются) во все нижележащие клетки (область С9:С17). В клетке С18 вычисляется сумма погрешностей для всех точек С18=СУММ(С8:С17).

Нашей целью является приведение этой погрешности к минимуму путем изменения значений коэффициентов уравнения прямой (клеток В3 и С3). В исходном состоянии они пустые. Для поиска оптимальных значений в окне Поиск решения в качестве целевой ячейки следует установить клетку С18, а в качестве изменяемых параметров – область В3:С3 (рис. 10.2-2). Результаты, полученные в изменяемых ячейках, соответствуют уравнению вида  y = 1,8+0,6364x.

Рис. 10.2-2

Общая погрешность приближения (клетка С18) составила 30,69.

Аналогичным образом заполняется столбец D8:D18 погрешностей для полинома второй степени (параболы). Здесь

D8=($B8–($B$4+$C$4*$A8+$D$4*$A8^2))^2.

В окне Поиск решения целевая ячейка – D18, изменяемые параметры – область В4:D4. Полученный результат соответствует уравнению

y = 4,05 – 0,4886x + 0,1023x2.

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

E8=($B8–($B$5+$C$5*$A8+$D$5*$A8^2+$E$5*A8^3))^2.

В окне Поиск решения целевая ячейка – E18, изменяемые параметры – область B4:E5. Результат описывается уравнением

y = – 2,0333 + 4,907x – 1,0676x2 + 0,0709x3.

Точно так же (в таблице не показано) может быть сформировано уравнение четвертой степени

y = – 10,083 + 15,227x – 4,844x2 + 0,5869x3 – 0,0235x4.

С повышением порядка уравнения регрессии погрешность приближения все время уменьшается

30,6909 Þ 25,1682 Þ 9,6408 Þ 0,5775.

Графическое отображение результатов вычислений приведено на рис. 10.2-3 (исходные точки обозначены прямоугольниками). Оно также подтверждает этот вывод – линии уравнений более высокой степени находятся ближе к исследуемым точкам.

Рис. 10.2-3

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

Отметим, что средства деловой графики позволяют найти уравнения регрессии (до 6 степени включительно) и не прибегая к вычислениям. Если, после того как была построена кривая функции Y(X), щелкнуть на ней правой кнопкой мыши, в появившемся контекстном меню можно выбрать пункт Добавить линию тренда, который предъявляет окно Линия тренда. Здесь можно выбрать вид уравнения аппроксимации и его степень, а если во вкладке Параметры установить флаг Показывать уравнение на диаграмме, то на графике мы увидим не только линию тренда, но и его уравнение. Здесь же можно визуально оценить поведение анализируемого процесса в будущем/прошлом, если установить Прогноз вперед/назад на заданное число единиц независимого аргумента Х. К сожалению, предъявляемая функция отображается как текст, и не может быть непосредственно использована в вычислениях.