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

АППРОКСИМАЦИЯ

ЗАВИСИМОСТЕЙ

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

10.1. Линейная регрессия

Самый простой и популярной является аппроксимация прямой линией – линейная регрессия.

Пусть мы имеем фактическую информацию об уровнях прибыли Y в зависимости от размера X капиталовложений – Y(X). На рис. 10.1-1 показаны четыре такие точки М(Y,X). Пусть также у нас имеются основания предполагать, что зависимость эта линейная, т.е. имеет вид  Y=А+ВX. Если бы нам удалось найти коэффициенты A и B и по ним построить прямую (например, такую, как на рисунке), в дальнейшем мы могли бы сделать осознанные предположения о динамике бизнеса и возможном коммерческом состоянии предприятия в будущем. Очевидно, что нас бы устроила прямая, находящаяся как можно ближе к известным точкам М(Y,X), т.е. имеющая минимальную сумму отклонений или сумму ошибок (на рисунке отклонения показаны пунктирными линиями). Известно, что существует только одна такая прямая.

Рис. 10.1-1

Для решения этой задачи используют метод наименьших квадратов ошибок. Разность (ошибка) между известным значением Y1 точки М1(Y1,X1) и значением Y(X1), вычисленным по уравнению прямой для того же значения X1, составит

                                               D1 = Y1 – A – B•X1.

Такая же разность

для        X=X2     составит      D2 = Y2 – A – B•X2;

для        X=X3                                 D3 = Y3 – A – B•X3;

и для     X=X4                                  D4 = Y4 – A – B•X4.

Запишем выражение для суммы квадратов этих ошибок

Ф(A,В)=(Y1–A–B•X1)²+(Y2–A–B•X2)²+(Y3–A–B•X3)²+(Y4–A–B•X4)²

                                                      4

или сокращенно           Ф(B,A) = å(Yi – A – BXi)².

                                                       i=1

Здесь нам известны все X и Y и неизвестны коэффициенты A и B. Проведем искомую прямую так (т.е. выберем A и B такими), чтобы эта сумма квадратов ошибок Ф(A,B) была минимальной. Условиями минимальности являются известные соотношения

              ¶Ф(A,B)/¶A=0   и   ¶Ф(A,B)/¶B=0.

Выведем эти выражения (индексы при знаке суммы опускаем):

                        ¶[å(Yi–A–B•Xi)²]/¶A = å(Yi–A–B•Xi)(–1)

                        ¶[å(Yi–A–B•Xi)²]/¶B = å(Yi–A–B•Xi)(–Xi).

Преобразуем полученные формулы и приравняем их нулю

                        2å(–Yi +B•Xi +A) = 0