Экспоненциальное сглаживание в MS EXCEL
Экспоненциальное сглаживание используется для сглаживания краткосрочных колебаний во временных рядах, чтобы облегчить определение долгосрочного тренда, а также для прогнозирования. Произведем экспоненциальное сглаживание с помощью надстройки MS EXCEL Пакет анализа и формулами. Рассмотрим двойное и тройное экспоненциальное сглаживание для прогнозирования рядов с трендом и сезонностью.
Экспоненциальное сглаживание один из наиболее распространённых методов для сглаживания временных рядов. В отличие от метода Скользящего среднего, где прошлые наблюдения имеют одинаковый вес, Экспоненциальное сглаживание присваивает им экспоненциально убывающие веса, по мере того как наблюдения становятся старше. Другими словами, последние наблюдения дают относительно больший вес при прогнозировании, чем старые наблюдения.
Примечание: Перед прочтением этой статьи рекомендуется прочитать про Скользящее среднее.
Примечание: В англоязычной литературе для экспоненциального сглаживания используется термин Single Exponential Smoothing или Simple Exponential Smoothing (SES).
Напомним, что при усреднении методом Скользящего среднего веса, присвоенные наблюдениям, одинаковы и равны 1/n, где n – количество периодов усреднения. Например, в случае усреднения за 3 периода скользящее среднее равно:
В случае Экспоненциального сглаживания формула выглядит следующим образом:
Параметр альфа определяет степень сглаживания. При малых значениях альфа (0,1 – 0,2) имеет место сильное сглаживание. При значениях близких к 1, сглаженный ряд практически повторяет исходный ряд с задержкой (лагом) на один период. Для медленно меняющегося ряда часто берут небольшие значения альфа=0,1; а для быстро меняющегося 0,3-0,5.
Примечание: Формулы представляют собой рекуррентное соотношение – это когда последующий член ряда вычисляется на основе предыдущего.
Примечание: Существует альтернативный подход к Экспоненциальному сглаживанию: в нем в формуле вместо Yi-1 заменяют на Yi. Этот подход используется в контрольных картах экспоненциально взвешенного скользящего среднего (EWMA).
Надстройка Пакет анализа
Получить Экспоненциально сглаженный ряд можно с помощью надстройки Пакет анализа (Analysis ToolPak). Надстройка доступна из вкладки Данные, группа Анализ.
СОВЕТ: Подробнее о других инструментах надстройки Пакет анализа и ее подключении – читайте в статье.
Разместим исходный числовой ряд в диапазоне B7:B32.
Для наглядности пронумеруем каждое значение ряда (столбец А).
Вызовем надстройку Пакет анализа, выберем инструмент Экспоненциальное сглаживание.
В появившемся диалоговом окне в поле Входной интервал введите ссылку на диапазон с данными ряда, т.е. на B7:B32.
Если диапазон включает и заголовок, то нужно установить галочку в поле Метки. В нашем случае устанавливать галочку не требуется, т.к. заголовок столбца не входит в диапазон B7:B32.
Поле Фактор затухания, как и параметр альфа в вышеуказанной формуле, определяет степень сглаживания ряда. Фактор затухания равен (1- альфа). Чем больше Фактор затухания тем глаже получается ряд. Установим значение 0,8.
В поле Выходной интервал достаточно ввести ссылку на левую верхнюю ячейку диапазона с результатами (укажем ячейку D7).
Также поставим галочки в поле Вывод графика и Стандартные погрешности (будет выведен столбец с расчетами погрешностей, англ. Standard Errors).
Нажмем ОК.
В результате работы надстройки, MS EXCEL разместил значения ряда, полученного методом Экспоненциального сглаживания, в столбце D (см. файл примера лист Пакет анализа ).
В ячейке D7 содержится текстовое значение ошибки #Н/Д, т.к. для получения первого значения Экспоненциального сглаживания требуется значение исходного ряда за предыдущий период.
Первое значение сглаженного ряда, точнее формула = B7 , содержится в ячейке D8. Второе значение вычисляется с помощью формулы = 0,2*B8+0,8*D8 .
Таким образом, Фактор затухания (0,8) определяет вес (вклад) предыдущего значения сглаженного ряда. Соответственно, (1-Фактор затухания)=альфа определяет вес предыдущего значения исходного ряда.
Диаграмма
Для отображения рядов MS EXCEL создал диаграмму типа график. Сглаженный ряд на диаграмме называется «Прогноз» (ряд красного цвета).
Первое значение сглаженного ряда, которое равно ошибке #Н/Д, отражаются как 0, и может ввести в заблуждение (особенно, если последующие значения ряда близки к 0). Поэтому его лучше удалить из ячейки D7.
Примечание: Значение #Н/Д в ячейке D7 является просто текстовым значением, что принципиально отличается от результата возвращаемого формулами, например, функцией НД() , хотя визуально они неразличимы. При построении диаграммы текстовые значения всегда отображаются как 0. Но, если ошибка #Н/Д является результатом формулы, то воспринимается диаграммой как пустая ячейка и на ней не отображается.
Диаграмма позволяет визуально определить «выбросы», т.е. значения исходного ряда, которые существенно отличаются от средних значений. Такие «выбросы» могут быть следствием ошибки, но они оказывают существенное влияние на вид сглаженного ряда.
Вычисление погрешности
В столбце E, начиная с ячейки Е11, MS EXCEL разместил формулы для вычисления погрешностей (англ. Standard Errors):
Т.е. данная погрешность вычисляется по формуле:
Значения y – это значения исходного ряда в период i. Значения «y с крышечкой» — значения ряда, полученного методом Экспоненциального сглаживания, в тот же в период i. Значение n для экспоненциального сглаживания всегда равно 3, т.е. ошибка вычисляется за 3 последних периода (последние 3 значения учитываются с макимальным весом при расчете текущего значения сглаженного ряда и, соответственно, вносят более 50% вклада в его значение. Величина вклада сильно зависит от альфа).
Подробнее об этой погрешности см. соответствующий раздел в статье про Скользящее среднее.
Почему сглаживание называется экспоненциальным?
Как было показано в статье про Взвешенное скользящее среднее веса значений исходного ряда берутся в зависимости от их удаленности от текущего периода. Например, для 3-х периодов усреднения для Взвешенного скользящего среднего можно использовать формулу:
Экспоненциальное сглаживание по сути является модификацией Взвешенного скользящего среднего – при расчете значения сглаженного ряда используются ВСЕ предыдущие значения исходного ряда с весами уменьшающимися в геометрической прогрессии по мере удаления от текущего периода.
Чтобы это показать воспользуемся формулой
и вычислим Yэксп.5, т.е. значения сглаженного ряда для 5-го периода. После очевидных преобразований получим:
Таким образом, вес 4-го (предыдущего) члена ряда =(1-альфа) 0 , а вес 3-го =(1-альфа) 1 и т.д. Пусть t – текущий период (в нашем случае =5). Вес (t-i)-го члена ряда =(1-альфа) t-1-i . Т.к. (1-альфа)<1, то с ростом i растет и вес, и для члена t-1 достигает максимума =1.
Как известно, экспоненциальный рост y=a*EXP(b*x) в случае дискретной области определения с равными интервалами x называют геометрическим ростом (значения экспоненциальной функции y=a*EXP(b*x) являются в этом случае членами геометрической прогрессии m^x).
В нашем случае, приравняв i-й вес (1-альфа) t-1-i соответствующему значению экспоненциальной функции a*EXP(b*i) получим уравнение, которое позволит вычислить коэффициенты a и b (понадобится еще одно уравнение, например, для i-1 веса).
Решив систему из 2-х уравнений получим, a= EXP((t-1)*LN(1-альфа)) и b= LN(1-альфа) .
В файле примера для 26-го члена сглаженного ряда (t=26) вычислены веса всех предыдущих членов. На диаграмме ниже показано, что веса уменьшаются с ростом i в геометрической прогрессии, что соответствует экспоненциальной функции y=0,0038*exp(0,2231*x), где x=i. Вычисления параметров экспоненциальной кривой сделаны с помощью надстройки Поиск решения.
Экспоненциальное сглаживание с настраиваемым Фактором затухания
Недостатком формул, получаемых с помощью Пакета анализа, является то, что при изменении Фактора затухания (1-альфа) приходится перезапускать расчет. В файле примера на листе Формулы создана форма для быстрого пересчета Экспоненциального сглаживания в зависимости от значения Фактора затухания (полученный результат, естественно, полностью совпадает с расчетами надстройки Пакет анализа).
Значения ряда вычисляются с помощью формулы:
в ячейке В6 содержится значение Фактора затухания.
Выбор значения Фактора затухания для удобства осуществляется с помощью элемента управления Счетчик с шагом 0,1.
Как сделать изогнутый график в Excel
Когда вы создаёте линейный график в Excel, по умолчанию линии расположены под углом и имеют жёсткие края. Вы можете легко изменить это на изогнутый график с красивыми, плавными линиями для более отточенного вида. Мы проведём вас шаг за шагом, чтобы преобразовать ваш график.
В этом примере мы хотим создать кривую линейную диаграмму из этих данных о продажах кофе.
Выберите и выделите диапазон A1:F2, а затем нажмите «Вставка» → «Вставить график или диаграмму с областями» → «График».
Линейный график вставляется с прямыми линиями, соответствующими каждой точке данных.
Чтобы отредактировать его до изогнутой линии, щёлкните правой кнопкой мыши линию графика и выберите кнопку «Формат ряда данных…» во всплывающем меню.
Выберите категорию «Заливка и границы», разверните содержимое «Линии», а затем установите флажок «Сглаженная линия».
Использование сглаженной линии может помочь сделать ваши линейные графики более функциональными и профессиональными.
Сглаженные линии также могут быть умным способом отличить один ряд данных от другого. Например, поставленные цели от фактических данных или данные с прошлого года и этот год.
Эффективный способ сгладить график в Excel 2016
Графики являются важным инструментом для визуализации данных в Excel. Однако, бывает, что график получается слишком шумным и сложным для анализа. В статье рассмотрим, как сделать сглаживание графика в Excel 2016.
Excel является одним из наиболее популярных инструментов для визуализации данных. Графики помогают визуализировать и проанализировать различные типы данных, что особенно важно для бизнеса и научных исследований.
Однако, графики не всегда получаются идеальными. Иногда они слишком сложны для понимания и могут содержать множество шума. В таких случаях полезно использовать сглаживание графика.
Сглаживание графика — это метод, при котором плавно уменьшается количество точек графика. Это позволяет сделать график более ясным и простым для анализа. В Excel 2016 есть несколько способов сделать сглаживание графика.
1. Сглаживание графика с помощью инструментов анализа данных
Шаг 1: Выберите данные для построения графика.
Шаг 2: Нажмите на вкладку «Анализ данных» в верхней части экрана.
Шаг 3: Выберите функцию «Сглаживание» из списка и нажмите «ОК».
Шаг 4: Введите параметры сглаживания, такие как порядок сглаживания и ширину окна сглаживания.
Шаг 5: Нажмите «OK» и получите сглаженный график.
2. Сглаживание графика с помощью формулы AVERAGE
Шаг 1: Выберите данные для построения графика.
Шаг 2: Добавьте новый столбец на листе.
Шаг 3: Напишите формулу AVERAGE для каждой строки, используя предыдущие и последующие значения.
Шаг 4: Скопируйте формулу на весь столбец.
Шаг 5: Выберите новые значения и постройте график.
3. Сглаживание графика с помощью инструмента графиков сглаживания
Шаг 1: Выберите данные для построения графика.
Шаг 2: Выберите инструмент «График сглаживания» на вкладке «Вставка».
Шаг 3: Выберите тип графика и количество точек, которые вы хотите сохранить.
Шаг 4: Нажмите «ОК» и получите сглаженный график.
Сглаживание графика помогает улучшить визуализацию данных в Excel 2016. Но не забывайте, что сглаживание может спрятать некоторые важные детали данных. Используйте сглаживание с умом и только в случае необходимости.
Excel works!
Сглаживание графика в Excel. Настройка линии
Чуть ранее мы уже писали , как красиво оформить нулевые/пустые значения на графике, чтобы диаграмма не получалась «зубчатой». Помимо этого для лучшей визуализации информации иногда нужно сделать сглаживание графика в Excel. Как это сделать? Читайте ниже
Сразу хотел бы написать, где можно почитать, как создавать графики — тут и тут . Далее разберем как сделать линию графика более красивой.
Сглаживание графика в Excel. Как быстро сделать?
Часто соединения узлов графика выглядят некрасиво, если линии на графике расположены под острыми углами. Как сделать плавную линию? Правой кнопкой мыши нажимаем на сам график — выплывает окно —
Формат ряда данных (см. первую картинку) выбираем — пункт Тип линии -ставим галочку — Сглаженная линия
Теперь линия сгладилась.
Экспоненциальное сглаживание в Excel
В Excel можно подключить пакет анализа для сглаживания самих данных.
Такое сглаживание это метод применяемый для сглаживания временных рядом — статья википедии
Зайдите в меню — Параметры Excel — Надстройки — Пакет анализа (в правом окне) и в самом низу нажимайте Перейти
В открывшемся окне находим Экспоненциальное сглаживание.
Как найти прямую приближенных значений
Всегда можно построить линию приближенных значений — линию тренда — она покажет, куда идет динамика графика, какое направление имеют события графика