Как рассчитать среднюю ошибку аппроксимации в excel

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

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

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

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

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

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

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

Урок: Как построить линию тренда в Excel

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

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

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

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

Построение диаграммы в Microsoft Excel

График построен.

График построен в Microsoft Excel

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

Добавление линии тренда через контекстное меню в Microsoft Excel

Существует ещё один вариант её добавления. В дополнительной группе вкладок на ленте «Работа с диаграммами» перемещаемся во вкладку «Макет». Далее в блоке инструментов «Анализ» щелкаем по кнопке «Линия тренда». Открывается список. Так как нам нужно применить линейную аппроксимацию, то из представленных позиций выбираем «Линейное приближение».

Добавление линии тренда через блок инструментов на ленте в Microsoft Excel

Если же вы выбрали все-таки первый вариант действий с добавлением через контекстное меню, то откроется окно формата.

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

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

После того, как провели все вышеуказанные настройки. Жмем на кнопку «Закрыть», размещенную в нижней части окна.

Включение линейной аппроксимации в Microsoft Excel

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

Линия тренда построена с помощью линейной аппроксимации в Microsoft Excel

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

y=ax+b

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

y=-0,1156x+72,255

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

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

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

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

Переход в формат лини тренда в Microsoft Excel

После этого запускается уже знакомое нам окно формата. В блоке выбора типа аппроксимации устанавливаем переключатель в положение «Экспоненциальная». Остальные настройки оставим такими же, как и в первом случае. Щелкаем по кнопке «Закрыть».

Построение экспоненциальной линии тренда в Microsoft Excel

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

Экспоненциальная линия тренда построена в Microsoft Excel

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

y=be^x

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

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

y=6282,7*e^(-0,012*x)

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

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

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

Включение логарифмической аппроксимации в Microsoft Excel

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

Логарифмическая линия тренда построена в Microsoft Excel

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

y=a*ln(x)+b

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

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

y=-62,81ln(x)+404,96

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

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

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

Включение полиномиальной аппроксимации в Microsoft Excel

Линия тренда с использованием данного метода построена. Как видим, она ещё более изогнута, чем при использовании экспоненциальной аппроксимации. Уровень достоверности выше, чем при любом из использованных ранее способов, и составляет 0,9724.

Полиномиальная линия тренда в Microsoft Excel

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

y=a1+a1*x+a2*x^2+…+an*x^n

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

y=0,0015*x^2-1,7202*x+507,01

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

Включение полиномиальной аппроксимации в шестой степени в Microsoft Excel

Как видим, после этого наша линия тренда приняла форму ярко выраженной кривой, у которой число максимумов равно шести. Уровень достоверности повысился ещё больше, составив 0,9844.

Полиномиальная линия тренда в шестой степени в Microsoft Excel

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

y=8E-08x^6-0,0003x^5+0,3725x^4-269,33x^3+109525x^2-2E+07x+2E+09

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

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

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

Полиномиальная линия тренда в шестой степени в Microsoft Excel

Программа формирует линию тренда. Как видим, в нашем случае она представляет собой линию с небольшим изгибом. Уровень достоверности равен 0,9618, что является довольно высоким показателем. Из всех вышеописанных способов уровень достоверности был выше только при использовании полиномиального метода.

Степенная линия тренда построена в Microsoft Excel

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

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

y=bx^n

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

y = 6E+18x^(-6,512)

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

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

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

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

Помогла ли вам эта статья?

ДА НЕТ

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

Таблица 3.1 – Исходные данные

Область Средний размер назначенных ежемесячных пенсий, у.д.е., у Прожиточный минимум в среднем на одного пенсионера в месяц, у.д.е., х
Орловская
Рязанская
Смоленская
Тверская
Тульская
Ярославская

Эмпирические коэффициенты регрессии b, b1 будем определять с помощью инструмента «Регрессия» надстройки «Анализ данных» табличного процессораMS Excel.

Алгоритм определения коэффициентов состоит в следующем.

1. Вводимисходные данные в табличный процессор MS Excel.

2. Вызываемнадстройку Анализ данных(рисунок 2).

3.Выбираем инструмент анализа Регрессия(рисунок 3).

4. Заполняем соответствующие позиции окна Регрессия (рисунок 4).

5. Нажимаем кнопку ОК окна Регрессия и получаем протокол решения задачи (рисунок 5)

image019.gif

Рисунок 2 – Активизация надстройки Анализ данных

image020.gif

Рисунок 3 – Выбор инструмента Регрессия

image021.gif

image023.gif
Рисунок 4 – Окно Регрессия

Рисунок 5 – Протокол решения задачи

Из рисунка 5 видно, что эмпирические коэффициенты регрессии соответственно равны

b1 = 0, 0088.

Тогда уравнение парной линейной регрессии, связывающая величину ежемесячной пенсии у с величиной прожиточного минимумахимеет вид

image025.gif.(3.2)

Далее, в соответствии с заданием необходимо оценить тесноту статистической связи между величиной прожиточного минимума х и величиной ежемесячной пенсии у. Эту оценку можно сделать с помощью коэффициента корреляции image027.gif. Величина этого коэффициента на рисунке 5 обозначена как множественный R и соответственно равна 0,038. Поскольку теоретически величина данного коэффициента находится в пределахот –1 до +1, то можно сделать вывод о не существенности статистической связимежду величиной прожиточного минимума х и величиной ежемесячной пенсии у.

Параметр «R – квадрат», представленныйна рисунке 5 представляет собой квадрат коэффициента корреляции image029.gifи называется коэффициентом детерминации. Величина данного коэффициента характеризует долю дисперсии зависимой переменной у, объясненную регрессией (объясняющей переменной х). Соответственно величина 1- image029.gifхарактеризует долю дисперсии переменной у, вызванную влиянием всех остальных, неучтенных в эконометрической модели объясняющих переменных. Из рисунка 5 видно, что доля всех неучтенных в полученной эконометрической модели объясняющих переменных приблизительно составляет 1- 0,00145 = 0,998 или 99,8%.

На следующем этапе, в соответствии с заданием необходимо определить степень связи объясняющей переменной х с зависимой переменной у, используя коэффициент эластичности. Коэффициент эластичности для модели парной линейной регрессии определяется в виде:

image031.gif. (3.3)

image033.gif

Следовательно, при изменении прожиточного минимума на 1% величина ежемесячной пенсии изменяется на 0,000758%.

Далее определяем среднюю ошибку аппроксимации по зависимости

image035.gif. (3.4)

Для этого исходную таблицу 1 дополняем двумя колонками, в которых определяем значения, image037.gifрассчитанные с использованием зависимости (3.2) и значения разности image039.gif.

Таблица 3.2. Расчет средней ошибки аппроксимации.

Область Средний размер назначенных ежемесячных пенсий, у.д.е., у Прожиточный минимум в среднем на одного пенсионера в месяц, у.д.е., х image040.gif image041.gif
Орловская 0,032
Рязанская 0,045
Смоленская 0,021
Тверская 0,012
Тульская 0,028
Ярославская 0,017
S=0,155

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

image043.gif.

Из практики известно, что значение средней ошибки аппроксимации не должно превышать (12…15)%

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

если при заданном уровне значимости a = 0,05 теоретическое (расчетное) значение F-критерия больше его критического значения Fкрит (табличного), то нулевая гипотеза отвергается, и полученное уравнение регрессии принимается значимым.

Из рисунка 5 следует, что Fрасч = 0,0058. Критическое значение F-критерия определяем с помощью использования статистической функции FРАСПОБР (рисунок 6). Входными параметрами функции является уровень значимости (вероятность) и число степеней свободы 1 и 2. Для модели парной регрессии число степеней свободы соответственно равно 1 (одна объясняющая переменная) и n-2 = 6-2=4.

image044.gif

Рисунок 6 – Окно статистической функции FРАСПОБР

Из рисунка 6 видно, что критическое значение F-критерия равно 7,71.

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

Предположим, имеются два показателя X и Y. Причем Y зависит от X. Так как МНК интересует нас с точки зрения регрессионного анализа (в Excel его методы реализуются с помощью встроенных функций), то стоит сразу же перейти к рассмотрению конкретной задачи.

Итак, пусть X — торговая площадь продовольственного магазина, измеряемая в квадратных метрах, а Y — годовой товарооборот, определяемый в миллионах рублей.

Требуется сделать прогноз, какой товарооборот (Y) будет у магазина, если у него та или иная торговая площадь. Очевидно, что функция Y = f (X) возрастающая, так как гипермаркет продает больше товаров, чем ларек.

4.

Аппроксимацией

называется

процесс

подбора

эмпирической формулы для установленной из опыта

функциональной зависимости у = f (x). Эмпирические

формулы служат для аналитического представления

экспериментальных данных.

В

простейшем

случае

задача

аппроксимации

экспериментальных

данных

выглядит

следующим

образом.

Пусть какие-то данные, полученные практическим путем

(в ходе эксперимента или наблюдения), можно

представить парами чисел (х, у). Зависимость между ними

отражает таблица 2.

Таблица 2 – Зависимость экспериментальных X и Y

Как экстраполировать данные в Excel?

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

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

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

Решение.
1. Находим параметры уравнения методом наименьших квадратов. Линейное уравнение тренда имеет вид y = bt + a
Система уравнений МНК:
a0n + a1∑t = ∑y
a0∑t + a1∑t2 = ∑y•t

t y t2 y2 t•y
1 58.8 1 3457.44 58.8
2 58.7 4 3445.69 117.4
3 59 9 3481 177
4 59 16 3481 236
5 58.8 25 3457.44 294
6 58.3 36 3398.89 349.8
7 57.9 49 3352.41 405.3
8 57.5 64 3306.25 460
9 56.9 81 3237.61 512.1
45 524.9 285 30617.73 2610.4

Для наших данных система уравнений имеет вид:
9a0 + 45a1 = 524.9
45a0 + 285a1 = 2610.4
Из первого уравнения выражаем а0 и подставим во второе уравнение
Получаем a0 = -0.24, a1 = 59.5
Уравнение тренда:
y = -0.24 t + 59.5
Эмпирические коэффициенты тренда a и b являются лишь оценками теоретических коэффициентов βi, а само уравнение отражает лишь общую тенденцию в поведении рассматриваемых переменных.
Коэффициент тренда b = -0.24 показывает среднее изменение результативного показателя (в единицах измерения у) с изменением периода времени t на единицу его измерения. В данном примере с увеличением t на 1 единицу, y изменится в среднем на -0.24.
Ошибка аппроксимации.
Оценим качество уравнения тренда с помощью ошибки абсолютной аппроксимации.
prim91.png
Ошибка аппроксимации в пределах 5%-7% свидетельствует о хорошем подборе уравнения тренда к исходным данным.
prim92.png
Поскольку ошибка меньше 7%, то данное уравнение можно использовать в качестве тренда.
Однофакторный дисперсионный анализ.
Средние значения
prim93.png
prim94.png
prim95.png
Дисперсия
prim96.png
prim97.png
Среднеквадратическое отклонение
prim98.png
prim99.png
Коэффициент эластичности.
Коэффициент эластичности представляет собой показатель силы связи фактора t с результатом у, показывающий, на сколько процентов изменится значение у при изменении значения фактора на 1%.
prim910.png
prim911.png
Коэффициент эластичности меньше 1. Следовательно, при изменении t на 1%, Y изменится менее чем на 1%. Другими словами – влияние t на Y не существенно.
Эмпирическое корреляционное отношение.
Эмпирическое корреляционное отношение вычисляется для всех форм связи и служит для измерение тесноты зависимости. Изменяется в пределах [0;1].
prim912.png
где (y-yt)² = 4.4-1.08 = 3.31
В отличие от линейного коэффициента корреляции он характеризует тесноту нелинейной связи и не характеризует ее направление. Изменяется в пределах [0;1].
Связи между признаками могут быть слабыми и сильными (тесными). Их критерии оцениваются по шкале Чеддока:
0.1 0.3 0.5 0.7 0.9 Полученная величина свидетельствует о том, что изменение временного периода t существенно влияет на y.
Коэффициент детерминации.
prim914.png
prim915.png
т.е. в 75.39% случаев влияет на изменение данных. Другими словами – точность подбора уравнения тренда – высокая.

t y y(t) (y-ycp)2 (y-y(t))2 (t-tp)2 (y-y(t)) : y
1 58.8 59.26 0.23 0.21 16 0.00786
2 58.7 59.03 0.14 0.11 9 0.00557
3 59 58.79 0.46 0.0431 4 0.00352
4 59 58.56 0.46 0.2 1 0.0075
5 58.8 58.32 0.23 0.23 0 0.00813
6 58.3 58.09 0.0004 0.0452 1 0.00365
7 57.9 57.85 0.18 0.0022 4 0.000825
8 57.5 57.62 0.68 0.0137 9 0.00204
9 56.9 57.38 2.02 0.23 16 0.00847
45 524.9 524.9 4.4 1.08 60 0.0476

Интервальный прогноз.
Определим среднеквадратическую ошибку прогнозируемого показателя.
prim916.png
m = 1 – количество влияющих факторов в уравнении тренда.
Uy=yn+L±K
где prim918.png
L – период упреждения; уn+L – точечный прогноз по модели на (n + L)-й момент времени; n – количество наблюдений во временном ряду; Sy – стандартная ошибка прогнозируемого показателя; Tтабл – табличное значение критерия Стьюдента для уровня значимости α и для числа степеней свободы, равного n-2.
По таблице Стьюдента находим Tтабл
Tтабл (n-m-1;α/2) = (7;0.025) = 2.365
Точечный прогноз, t = 10: y(10) = -0.24*10 + 59.5 = 57.15
prim919.png
57.15 – 1.08 = 56.07 ; 57.15 + 1.08 = 58.23
Интервальный прогноз:
t = 10: (56.07;58.23)
Точечный прогноз, t = 11: y(11) = -0.24*11 + 59.5 = 56.91
prim920.png
56.91 – 1.14 = 55.77 ; 56.91 + 1.14 = 58.05
Интервальный прогноз:
t = 11: (55.77;58.05)

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

Стандартная ошибка (погрешность) рассчитывается по формуле:
prim921.png
где i = (t-m-1, t)

3. Построим прогноз численности с использованием экспоненциального сглаживания. Важным методом стохастических прогнозов является метод экспоненциального сглаживания. Этот метод заключается в том, что ряд динамики сглаживается с помощью скользящей средней, в которой веса подчиняются экспоненциальному закону.
Эту среднюю называют экспоненциальной средней и обозначают St.
Она является характеристикой последних значений ряда динамики, которым присваивается наибольший вес.
Экспоненциальная средняя вычисляется по рекуррентной формуле:
St = α*Yt + (1- α)St-1
где St – значение экспоненциальной средней в момент t;
St-1 – значение экспоненциальной средней в момент (t = 1);
Что касается начального параметра S0, то в задачах его берут или равным значению первого уровня ряда у1, или равным средней арифметической нескольких первых членов ряда.
Yt – значение экспоненциального процесса в момент t;
α – вес t-ого значения ряда динамики (или параметр сглаживания).
Последовательное применение формулы дает возможность вычислить экспоненциальную среднюю через значения всех уровней данного ряда динамики.
Наиболее важной характеристикой в этой модели является α, по величине которой практически и осуществляется прогноз. Чем значение этого параметра ближе к 1, тем больше при прогнозе учитывается влияние последних уровней ряда динамики.
Если α близко к 0, то веса, по которым взвешиваются уровни ряда динамики убывают медленно, т.е. при прогнозе учитываются все прошлые уровни ряда.
В специальной литературе отмечается, что обычно на практике значение α находится в пределах от 0,1 до 0,3. Значение 0,5 почти никогда не превышается.
Экспоненциальное сглаживание применимо, прежде всего, при постоянном объеме потребления (α = 0,1 – 0,3). При более высоких значениях (0,3 – 0,5) метод подходит при изменении структуры потребления, например, с учетом сезонных колебаний.
В качестве S0 берем первое значение ряда, S0 = y1 = 58.8

t y St Формула
1 58.8 58.8 (1 – 0.1)*58.8 + 0.1*58.8
2 58.7 58.71 (1 – 0.1)*58.7 + 0.1*58.8
3 59 58.97 (1 – 0.1)*59 + 0.1*58.71
4 59 59 (1 – 0.1)*59 + 0.1*58.97
5 58.8 58.82 (1 – 0.1)*58.8 + 0.1*59
6 58.3 58.35 (1 – 0.1)*58.3 + 0.1*58.82
7 57.9 57.95 (1 – 0.1)*57.9 + 0.1*58.35
8 57.5 57.54 (1 – 0.1)*57.5 + 0.1*57.95
9 56.9 56.96 (1 – 0.1)*56.9 + 0.1*57.54

Прогнозирование данных с использованием экспоненциального сглаживания.
Методы прогнозирования под названием “сглаживание” учитывают эффекты выброса функции намного лучше, чем способы, использующие регрессивный анализ.
Базовое уравнение имеет следующий вид:
F(t+1) = F(t)(1 – α) + αY(t)
F(t) – это прогноз, сделанный в момент времени t; F(t+1) отражает прогноз во временной период, следующий непосредственно за моментом времени t
Стандартная ошибка (погрешность) рассчитывается по формуле:
prim922.png
где i = (t – 2, t)

Пример. Методом наименьших квадратов найти функции вида y=ax+b, y=ax²+bx+c, аппроксимирующие экспериментальную функцию y=f(x). В обоих случаях найти суммы квадратов невязок ∑bi². В декартовой системе координат построить экспериментальные точки и графики найденных функций y=ax+b,y=ax^2+bx+c.
Пример №5

Пример №6

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

Решение. Функция многочлена 2-ой степени имеет вид y = ax2+ bx + c.
1. Находим параметры уравнения методом наименьших квадратов. Система уравнений МНК:
a0n + a1∑x + a2∑x2= ∑y
a0∑x + a1∑x2+ a2∑x3= ∑yx
a0∑x2+ a1∑x3+ a2∑x4= ∑yx2

x y x2 y2 x y x3 x4 x2y
0 0 0 0 0 0 0 0
-2 -0.8 4 0.64 1.6 -8 16 -3.2
-1 -1.6 1 2.56 1.6 -1 1 -1.6
0 -1.3 0 1.69 0 0 0 0
1 0.4 1 0.16 0.4 1 1 0.4
2 3.2 4 10.24 6.4 8 16 12.8
0 -0.1 10 15.29 10 0 34 8.4

Для наших данных система уравнений имеет вид
6a0+ 0a1+ 10a2= -0.1
0a0+ 10a1+ 0a2= 10
10a0+ 0a1+ 34a2= 8.4
Получаем a0= 0.494, a1= 1, a2= -0.84
Уравнение: y = 0.494×2+x-0.84

Как использовать функцию Линейн в Excel?

Функция EXCEL ЛИНЕЙН()

  1. выделите 2 ячейки в одной строке,
  2. в Строке формул введите, например, = ЛИНЕЙН(C23:C83;B23:B83)
  3. нажмите CTRL + SHIFT + ENTER .

26 янв. 2019 г.

Метод аппроксимации в Microsoft Excel

Approksimatsiya-v-Microsoft-Excel.png

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

Как построить полином в Excel?

Полином — это степенная функция y=ax2+bx+c (полином второй степени) и y=ax3+bx2+cx+d (полином третей степени) и т.

Есть 3 способа расчета значений полинома в Excel:

  1. 1-й способ с помощью графика;
  2. 2-й способ с помощью функции Excel =ЛИНЕЙН;
  3. 3-й способ с помощью Forecast4AC PRO;

Аппроксимация в Excel

ishchem-formulu.jpg(Обратите внимание на дополнительный раздел от 04.06.2017 в конце статьи.)

Учет и контроль! Те, кому за 40 должны хорошо помнить этот лозунг из эпохи построения социализма и коммунизма в нашей стране.

Но без хорошо налаженного учета невозможно эффективное функционирование ни страны, ни области, ни предприятия, ни домашнего хозяйства при любой общественно-экономической формации общества! Для составления прогнозов и планов деятельности и развития необходимы исходные данные. Где их брать? Только один достоверный источник – это ваши статистические учетные данные предыдущих периодов времени.

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

«Мы, помню, 5 лет назад изготавливали до 1000 штук таких изделий в месяц, а сейчас и 700 еле-еле собираем!». Открываем статистику и видим, что 5 лет назад и 500 штук не изготавливали…

«Во сколько обходится километр пробега твоего автомобиля с учетом всех затрат?» Открываем статистику – 6 руб./км. Поездка на работу – 107 рублей. Дешевле, чем на такси (180 рублей) более чем в полтора раза. А бывали времена, когда на такси было дешевле…

«Сколько времени требуется для изготовления металлоконструкций уголковой башни связи высотой 50 м?» Открываем статистику – и через 5 минут готов ответ…

«Сколько будет стоить ремонт комнаты в квартире?» Поднимаем старые записи, делаем поправку на инфляцию за прошедшие годы, учитываем, что в прошлый раз купили материалы на 10% дешевле рыночной цены и – ориентировочную стоимость мы уже знаем…

Ведя учет своей профессиональной деятельности, вы всегда будете готовы ответить на вопрос начальника: «Когда. ». Ведя учет домашнего хозяйства, легче спланировать расходы на крупные покупки, отдых и прочие расходы в будущем, приняв соответствующие меры по дополнительному заработку или по сокращению необязательных расходов сегодня.

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

13. Теория ЗЛП

Оптимизация —

в математике, информатике и исследовании

операций задача нахождения экстремума (минимума

или

максимума) целевой

функции в

некоторой

области

конечномерного векторного

пространства,

ограниченной

набором линейных и/или нелинейных равенств и/или неравенств.

Теорию

и

методы

решения

задачи

изучает математическое программирование.

оптимизации

Математическое программирование – это область математики,

разрабатывающая

теорию,

численные

методы

решения

многомерных задач с ограничениями. В отличие от классической

математики,

математическое

программирование

занимается

математическими методами решения задач нахождения наилучших

вариантов из всех возможных.

В процессе проектирования ставится обычно задача определения

наилучших,

в

некотором

смысле,

структуры

или

значений параметров объектов.

Такая

задача

называется

оптимизационной.

Применение надстройки поиск решения

1. Если не включили надстройку «поиск решения», то возвращаемся к пункту Как включить надстройку «поискрешения» и включаем

2. В ячейку А1 введем значение «1». Эта единица будет первым приближением к реальному значению коэффициента (k) нашей функциональной зависимости y=kx.

3. В столбце B у нас расположились значения параметра X, в столбце C — значения параметра Y. В ячейках столбца D вводим формулу: «коэффициент k умножить на значение Х». Например, в ячейке D1 вводим «=A1*B1», в ячейке D2 вводим “=A1*B2” и т.д.

vvod-formuli-y-kx.jpg

4. Мы считаем, что коэффициент к равен единице и функция f (x)=у=1*х – это первое приближение к нашему решению. Можем рассчитать сумму квадратов разностей между измеренными значениями величины Y и рассчитанными по формуле y=1*х . Можем все это сделать вручную, вбивая в формулу соответствующие ссылки на ячейки: “=(D2-C2)^2+(D3-C3)^2+(D4-C4)^2… и т.д. В конце концов ошибаемся и понимаем, что потеряли кучу времени. В Excel для расчета суммы квадратов разностей есть специальная формула, «СУММКВРАЗН», которая все за нас и сделает. Введем ее в ячейку А2 и зададим исходные данные: диапазон измеренных значений Y (столбец C) и диапазон рассчитанных значений Y (столбец D).

vvod-funktsii-dlya-mnk.jpg

4. Сумму разностей квадратов рассчитали – теперь идем во вкладку «Данные» и выбираем «Поиск решения».

5. В появившемся меню в качестве изменяемой ячейки выбираем ячейку A1 (та, что с коэффициентом k).

6. В качестве целевой выбираем ячейку A2 и задаем условие «установить равной минимальному значению». Помним, что это ячейка, где у нас производится расчёт суммы квадратов разностей расчетного и измеренного значений, и сумма эта должна быть минимальной. Нажимаем «выполнить».

okno-poisk-resheniya.jpg

7. Коэффициент k подобран. Теперь можно убедиться, что рассчитанные значения теперь очень близки к измеренным.

Как в Excel сделать прогноз?

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

16.

Практика решения ЗЛП

1 часть – Построение математических моделей линейного

программирования

.

Математические модели линейного программирования строятся

на основе содержательной постановки экономической задачи.

Пример.

Типичная задача распределения ресурсов ставится следующим

образом.

Пусть фирма выпускает продукцию четырех типов Продукт1,

Продукт2, Продукт3, Продукт4, для изготовления которой

требуются ресурсы трех видов: трудовые, сырье, финансы.

Количество ресурса каждого вида, необходимое для выпуска

единицы продукции данного типа, называется нормой расхода.

Исходные данные.

Норма расхода, а также прибыль, получаемая от реализации

единицы каждого типа продукции, приведены в таблице 1, там же

приведен объем ресурса, которым можно располагать. Требуется

определить, в каком количестве надо выпускать продукцию

каждого

типа,

чтобы

суммарная

прибыль

была

максимальной.

Высокоточная красивая замена табличных данных простым уравнением.

Вас не устраивают полученные точность аппроксимации (R 2 2 =0,9963.

28.

Дальнейшее

решение

задачи

проводится

с

использованием

надстройки

Поиск

решения

табличного процессора MS Excel.

Поиск решения является надстройкой MS Excel,

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

оптимизации.

Данная надстройка, в случае отсутствия в пунктах

меню MS Excel 2003 и более ранних версий, может быть

добавлена с помощью выбора пунктов Сервис –

Надстройки – Поиск решения.

После этого будет добавлен подпункт Поиск решения

в меню Сервис.

В MS Excel 2007 надстройка добавляется следующим

образом.

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...