U аппроксимация зависимостей

↑

▷ Содержание

Стр 1 из 7Следующая ⇒

u АППРОКСИМАЦИЯ ЗАВИСИМОСТЕЙ

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

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

A

B

C

D

E

F

G

H

I

J

Yф

Yп

3,7

5,3

7,0

6,7

5,7

4,0

Рис.1-1

1. Скользящее среднее. Это самый простой метод сглаживания. Он заключается в том, что прогнозное значение вычисляется как среднее арифметическое какого-то количества прошлых значений. На рис. 1-1 первая строка – это фактические значения, вторая – прогнозные. Среднее значение возьмем за три предыдущих периода (например, три года) F2=СУММ(B1:D1)/3. Первоначально известны только три точки (значения 2; 3; 6). Прогнозное значение составило Yп=3,7. По истечении 4-го года появилось новое значение (7) и мы передвинули функцию вправо. Теперь Yп=5,3 и т. д. Конечно, это очень грубый метод и он дает только одну новую точку. Постройте диаграмму для обеих строк (тип График с маркерами).

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

Разность (ошибка) между известным значением Y₁ точки М₁(Y₁,X₁) и значением Y(X₁), вычисленным по уравнению прямой для того же значения X₁, составит

D₁ = Y₁ – A – BŸX₁.

Такая же разность для X=X₂ будет D₂=Y₂-A-BŸX₂; для X₃: D₃=Y₃-A-BŸX₃;  для X₄: D₄=Y₄-A-BŸX₄.

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

Ф(A,В)=(Y₁–A–BX₁)²+(Y₂–A–BX₂)²+(Y₃–A–BX₃)²+(Y₄–A–BX₄)² или   Ф(B,A)=å(Yi –A–BXi)².

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

¶Ф(A,B)

=

и

¶Ф(A,B)

=

Y                                                 Рис. 2-1

Познавательные статьи:



Техника прыжка в длину с разбега

Тактические действия в защите

История Олимпийских игр

История развития права интеллектуальной собственности