Средняя ошибка аппроксимации в excel

Обновлено: 06.01.2025

Расчет абсолютных и/или относительных ошибок. Часто рассчитывают: среднюю абсолютную процентную ошибку (Mean Absolute Percentage Error):

Если модель подогнана с высокой точностью MAPE<10%, хорошей - 10% < MAPE < 20%, удовлетворительной - 20% < MAPE < 50%, неудовлетворительной - MAPE > 50%.
Целесообразно пропускать значения ряда, для которых y_i=0.
Средняя процентная ошибка (Mean Percentage Error) и средняя ошибка (Mean Error). Средняя процентная ошибка не определена при нулевых данных и не должна превышать 5% для хорошо подогнанной модели:

Средняя ошибка:

случайная компонента получается как разность E=y-y(x)

Пример . 1. Находим параметры уравнения методом наименьших квадратов .
Линейное уравнение тренда имеет вид y=at+b (Тренд — систематическая составляющая временного ряда).
Система уравнений

Для наших данных система уравнений имеет вид

Поскольку ошибка больше 15%, то данное уравнение не желательно использовать в качестве тренда.
Средние значения:

y y(x) e i = y-y(x) e 2 (e i - e i-1 ) 2 80 74.44 5.56 30.93 0 79 70.31 8.69 75.54 9.8 75 66.18 8.82 77.82 0.02 70 62.05 7.95 63.23 0.76 65 57.92 7.08 50.15 0.76 60 53.79 6.21 38.58 0.76 39 49.66 -10.66 113.6 284.6 35 45.53 -10.53 110.85 0.02 30 41.4 -11.4 129.92 0.76 25 37.27 -12.27 150.51 0.76 20 33.14 -13.14 172.61 0.76 10 29.01 -19.01 361.31 34.46 13 24.88 -11.88 141.09 50.84 19 20.75 -1.75 3.06 102.62 29 16.62 12.38 153.31 199.66 14 12.49 1.51 2.29 118.16 20 8.36 11.64 135.53 102.62 25 4.23 20.77 431.47 83.36 2241.81 990.67
Критические значения d₁ и d₂ определяются на основе специальных таблиц для требуемого уровня значимости a, числа наблюдений n и количества объясняющих переменных m.
Не обращаясь к таблицам, можно пользоваться приблизительным правилом и считать, что автокорреляция остатков отсутствует, если 1.5 < DW < 2.5. Для более надежного вывода целесообразно обращаться к табличным значениям.
d₁ < DW и d₂ < DW < 4 - d₂.

Пример №2 . Исследовать динамику экономического показателя на основе анализа одномерного временного ряда.
В течение девяти последовательных недель фиксировался спрос Y(t) (млн руб.) на кредитные ресурсы финансовой компании. Временной ряд Y(t) этого показателя (повариантно) приведен ниже в таблице.

Ошибка аппроксимации в пределах 5%-7% свидетельствует о хорошем подборе уравнения тренда к исходным данным.

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

Выполнение аппроксимации

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

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

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

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

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

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

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

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

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

В блоке параметров «Построение линии тренда (аппроксимация и сглаживание)» устанавливаем переключатель в позицию «Линейная».
При желании можно установить галочку около позиции «Показывать уравнение на диаграмме». После этого на диаграмме будет отображаться уравнение сглаживающей функции.

Также в нашем случае для сравнения различных вариантов аппроксимации важно установить галочку около пункта «Поместить на диаграмму величину достоверной аппроксимации (R^2)». Данный показатель может варьироваться от 0 до 1. Чем он выше, тем аппроксимация качественнее (достовернее). Считается, что при величине данного показателя 0,85 и выше сглаживание можно считать достоверным, а если показатель ниже, то – нет.

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

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

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

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

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

Для того, чтобы изменить тип линии тренда, выделяем её кликом правой кнопки мыши и в раскрывшемся меню выбираем пункт «Формат линии тренда…».

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

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

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

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

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

Тем же способом, что и в предыдущий раз через контекстное меню запускаем окно формата линии тренда. Устанавливаем переключатель в позицию «Логарифмическая» и жмем на кнопку «Закрыть».

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

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

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

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

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

Переходим в окно формата линии тренда, как уже делали не раз. В блоке «Построение линии тренда» устанавливаем переключатель в позицию «Полиномиальная». Справа от данного пункта расположено поле «Степень». При выборе значения «Полиномиальная» оно становится активным. Здесь можно указать любое степенное значение от 2 (установлено по умолчанию) до 6. Данный показатель определяет число максимумов и минимумов функции. При установке полинома второй степени описывается только один максимум, а при установке полинома шестой степени может быть описано до пяти максимумов. Для начала оставим настройки по умолчанию, то есть, укажем вторую степень. Остальные настройки оставляем такими же, какими мы выставляли их в предыдущих способах. Жмем на кнопку «Закрыть».

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

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

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

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

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

Перемещаемся в окно «Формат линии тренда». Устанавливаем переключатель вида сглаживания в позицию «Степенная». Показ уравнения и уровня достоверности, как всегда, оставляем включенными. Жмем на кнопку «Закрыть».

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

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

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

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Решение проводим при помощь онлайн калькулятора Линейное уравнение регрессии.
а) линейное уравнение регрессии;
Использование графического метода.
Этот метод применяют для наглядного изображения формы связи между изучаемыми экономическими показателями. Для этого в прямоугольной системе координат строят график, по оси ординат откладывают индивидуальные значения результативного признака Y, а по оси абсцисс - индивидуальные значения факторного признака X.
Совокупность точек результативного и факторного признаков называется полем корреляции.

x y x 2 y 2 x • y y(x) (y i -y cp ) 2 (y-y(x)) 2 |y - y x |:y 45,1 68,8 2034,01 4733,44 3102,88 61,28 119,12 56,61 0,1094 59 61,2 3481 3745,44 3610,8 56,47 10,98 22,4 0,0773 57,2 59,9 3271,84 3588,01 3426,28 57,09 4,06 7,9 0,0469 61,8 56,7 3819,24 3214,89 3504,06 55,5 1,41 1,44 0,0212 58,8 55 3457,44 3025 3234 56,54 8,33 2,36 0,0279 47,2 54,3 2227,84 2948,49 2562,96 60,55 12,86 39,05 0,1151 55,2 49,3 3047,04 2430,49 2721,36 57,78 73,71 71,94 0,172 384,3 405,2 21338,41 23685,76 22162,34 405,2 230,47 201,71 0,5699

Примечание: значения y(x) находятся из полученного уравнения регрессии:
y(45.1) = -0.35*45.1 + 76.88 = 61.28
y(59) = -0.35*59 + 76.88 = 56.47
. . .

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

Поскольку ошибка меньше 15%, то данное уравнение можно использовать в качестве регрессии.

F-статистики. Критерий Фишера.
Проверка значимости модели регрессии проводится с использованием F-критерия Фишера, расчетное значение которого находится как отношение дисперсии исходного ряда наблюдений изучаемого показателя и несмещенной оценки дисперсии остаточной последовательности для данной модели.
Если расчетное значение с k1=(m) и k2=(n-m-1) степенями свободы больше табличного при заданном уровне значимости, то модель считается значимой.

б) степенная регрессия;
Решение проводится с помощью сервиса Нелинейная регрессия. При выборе укажите Степенная y = ax b
в) показательная регрессия;
г) модель равносторонней гиперболы.
Система нормальных уравнений.

Метод наименьших квадратов позволяет получить такие оценки параметров, при которых сумма квадратов отклонений фактических значений результативного признака y от расчетных y(x) минимальна: ∑( y - y_x ) 2 → min
Система нормальных уравнений для линейной регрессии:

Пример №1 . Функция задана таблицей

X	-2	-1	0	1	2
Y	3.1	1.7	0.9	0.7	1.05

Применяя метод наименьших квадратов, приблизить ее многочленами 1-й и 2-й степени. Для каждого приближения определить величину среднеквадратичной погрешности, построить график.

Рекомендации к решению . На первом шаге в калькуляторе необходимо выбрать Вид сглаживани: по прямой . Чтобы получить приближение 2-й степени необходимо выбрать Вид сглаживани: по параболе .

Пример №2 . По 10 парам наблюдений получены следующие результаты: ∑x_i = 100; ∑y_i = 200; ∑x_iy_i = 21000; ∑y_i 2 = 12000; ∑y_i 2 = 45000. По МНК оцените коэффициенты линейных уравнений регрессии Y на X и X на Y . Оцените коэффициент корреляции и детерминации. Проинтерпретируйте результаты.
Решение.

Уравнение регрессии X на Y: y = a + bx
Найдем средние значения.
x – = 100/10 = 10; y – = 200/10 = 20; xy – = 21000/10 = 2100;
b = (2100-10×20)/(12000/10-10 2 ) = 1.727
a = 20 – 1.727×10 = 2.727
y = 2.727 + 1.727x

Уравнение регрессии Y на X: x = a + by = (y-2.727)/1.727 = 0,579x – 1.579
Дисперсии
σ_x 2 = 12000/10 – 10 2 = 1100
σ_y 2 = 45000/10 – 20 2 = 4100
Среднеквадратические отклонения
σ_x = (1100) 1/2 = 33.17
σ_y = (4100) 1/2 = 64.03

Коэффициент корреляции r_xy = b σ_x/σ_y = 1.727×33.17/64.03 = 0.895
Коэффициент детерминации: R 2 = 0,895 2 = 0.8. Следовательно, в 80% случаев изменения х приводят к изменению y . Другими словами, точность подбора уравнения регрессии - высокая.

Пример №3 . В задачах результаты измерений величин x и y даются таблицей. Предполагая, что между переменными x и y существует линейная функциональная зависимость y = ax + b, найти, пользуясь способом наименьших квадратов эту функцию. Вычислить с помощью полученной формулы приближенные значения y при x = 2.5 и x=6.

Сглаживание ряда методом наименьших квадратов

Задание.
1. Постройте прогноз численности наличного населения города Б на 2010-2011 гг., используя методы: скользящей средней, экспоненциального сглаживания, наименьших квадратов.
2. Постройте график фактического и расчетных показателей.
3. Рассчитайте ошибки полученных прогнозов при использовании каждого метода.
4. Сравните полученные результаты, сделайте вывод.

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

t	y	ys	Формула
1	58.8	58.75	(58.8 + 58.7)/2
2	58.7	58.85	(58.7 + 59)/2
3	59	59	(59 + 59)/2
4	59	58.9	(59 + 58.8)/2
5	58.8	58.55	(58.8 + 58.3)/2
6	58.3	58.1	(58.3 + 57.9)/2
7	57.9	57.7	(57.9 + 57.5)/2
8	57.5	57.2	(57.5 + 56.9)/2
9	56.9	-	-

Стандартная ошибка (погрешность) рассчитывается по формуле:


где i = (t-m-1, t)

Пример №3 . Функция y=y(x) задана таблицей своих значений:
x: -2 -1 0 1 2
y: -0,8 -1,6 -1,3 0,4 3,2
Применяя метод наименьших квадратов, приблизить функцию многочленами 1-ой и 2-ой степеней. Для каждого приближения определить величину среднеквадратичной погрешности. Построить точечный график функции и графики многочленов.

Читайте также:

  
• Настройки плейлиста в fl studio
  
• Настройка льгот в жкх в 1с
  
• Как вырезать волосы в фотошопе
  
• Как заработать на знании excel
  
• Как удалить shareit с телефона lenovo