Logiss.ru

Ваша компьютерная помощь
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Скользящая средняя в Excel

Скользящее среднее в Excel

Здравствуйте. Скользящая средняя (по-английски – moving average) активно используется в трейдинге для определения трендов, точек входа в рынок и выхода из него, и т.д. Однако, этот метод применяют и в оценке бизнес-процессов. Он позволяет отсеять факторы случайности и оценить реальную динамику процессов.

Суть в скользящего среднего в том, что для каждого периода (например, месяца) рассчитывается некий средний показатель, который учитывает предыдущие периоды и отчетный. Количество периодов, которые участвуют в расчете – называют интервалом сглаживания. Чем больше интервал сглаживания, тем более плавный результат мы получим, но будет увеличиваться отставание тренда от реальности. Чуть позже мы увидим это на примерах.

В этой статье я опишу три популярнейших способа реализации Moving Average: простое скользящее среднее, экспоненциальное и взвешенное

Простая скользящая средняя величина

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

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

Пример. 15 месяцев назад мы ввели на рынок новый продукт. Сейчас имеем данные о ежемесячных продажах.

диаграмма продаж

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

среднее арифметическое

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

простое скользящее среднее

Видите, мы получили некую гладкую линию, которая «менее охотно колеблется» и больше похожа на динамику спроса. Давайте так же построим кривые для периодов сглаживания 2 и 4 мес.

простое скользящее по трем интервалам

Имеем три кривые:

  1. Красная – шаг в 2 месяца;
  2. Зеленая – в 3 месяца
  3. Фиолетовая – 4 месяца

Легко заметить, что фиолетовая линия – наиболее гладкая и больше похожа на тренд. Однако, она медленнее реагирует на изменение продаж, что может вызывать вашу запоздалую реакцию.

Экспоненциальное скользящее среднее

Такой метод – это частный случай взвешенной скользящей средней. Его идея состоит в том, чтобы давать различный вес каждому периоду внутри выбранного интервала. Формула имеет вид:

α – весовой коэффициент, характеризующий скорость старения данных прошлых периодов

Pt – значение исследуемой величины в поточном интервале

EMAt-1 – величина экспоненциального скользящего среднего в предыдущий период времени.

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

Весовой коэффициент рассчитывают так, чтобы статистическая ошибка вычислений была минимальной. Но я на практике использую упрощенный вариант, подсмотренный на сайте allfi.biz:

весовой коэффициент

N – интервал сглаживания

Теперь для нашего примера построим экспоненциальную скользящую с интервалом в 3 месяца:

расчет экспоненциального среднего

График получится таким:

экспоненциальное среднее

Я редко использую этот подход, т.к. считаю его слишком чувствительным для моих задач. Если же вам он подходит – пользуйтесь.

Взвешенное скользящее среднее

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

n – интервал сглаживания,

Pti – значение исследуемой величины в период t-i

Формула достаточно простая, хотя и громоздкая. В числителе – сумма произведений величин продаж на каждом интервале и весового коэффициента для данного периода. Чем старше период, тем ниже коэффициент. В знаменателе – арифметическая прогрессия числа n.

Читайте так же:
ТОП-5 бесплатных архиваторов-альтернатив WinRAR

Например, при интервале сглаживания в 3 месяца, формула для третьего периода будет такой:

Вот расчет в Экселе взвешенного скользящего согласно этой формулы:

Диаграмма для периодов 2, 3 и 4 месяца – такая:

взвешенное скользящее среднее

Мы рассмотрели три наиболее популярных способа. Заметьте, все они легко реализуются. Теперь давайте посмотрим, как будут выглядеть на одной диаграмме кривые, построенные с помощью этих способов:

продажи и тренды

Красная линия – простая скользящая, зеленая линия – экспоненциальная – фиолетовая – взвешенная.

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

На этом всё, спасибо, что прочли статью и поделились с друзьями. Жду ваших вопросов и комментариев!

Скользящие средние Excel

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

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

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

Общий средний показатель за 12 месяцев продажи составляет 184 .

Но скользящая средняя здесь немного другая. Во-первых, скользящее среднее значение excel рассчитает среднее значение за первые три месяца, т.е. за январь, февраль и март.

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

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

В целом среднее показало тенденцию как 184, но скользящее среднее показало тенденцию как 216, основываясь на данных последних месяцев.

Типы скользящих средних

Доступны различные типы скользящих средних, такие как экспоненциальная, переменная, треугольная, взвешенная и простая скользящая средняя. Наиболее часто используемая техника — простая скользящая средняя.

Где найти скользящее среднее в Excel?

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

После того, как вы раскроете пакет Data Analysis Tool, вы увидите это на вкладке DATA в Excel.

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

Как рассчитать скользящие средние в Excel?

Рассчитать скользящие средние в Excel очень просто и легко. Давайте разберемся с вычислением скользящих средних в excel на нескольких примерах.

Читайте так же:
Как узнать какой процессор стоит на компьютере с Windows

Вы можете скачать этот шаблон Excel Скользящих средних здесь — Шаблон Excel Скользящих средних

Пример № 1

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

Шаг 1: Я возьму те же данные, что и выше.

Шаг 2: Перейдите в Данные и нажмите Анализ данных .

Шаг 3: Откроется диалоговое окно «Анализ данных». Прокрутите вниз и выберите опцию Moving Average и нажмите OK.

Шаг 4: В качестве диапазона ввода выберите данные о продажах от B2 до B13 . Мы не выбрали заголовок, поэтому не устанавливайте флажок Метки в первой строке.

Шаг 5: В разделе « Интервал» мы должны указать, на сколько месяцев нам нужно вычесть среднее значение. Я упомяну интервал как 3.

Шаг 6: Выберите ячейку Output Range в качестве ячейки C2 .

Шаг 7: Выберите Выход графика, если вам нужен график, чтобы показать тренд. Нажмите OK, чтобы завершить расчет скользящей средней.

Шаг 8: Теперь мы перешли от C2 к C13. Первые 2 ячейки показывают # N / A, потому что мы выбрали интервал 3 из 3- й ячейки и далее, у нас есть результаты.

Пример № 2 — Создание графика скользящей средней

Теперь мы поняли концепцию скользящих средних. Мы можем создать график скользящих средних без расчета скользящих средних.

Возьмите те же данные для создания диаграммы.

Шаг 1: Выберите данные и вставьте столбчатую диаграмму.

Шаг 2: Диаграмма будет выглядеть следующим образом:

Шаг 3: Выберите график, чтобы перейти к Макету > Линия тренда > Дополнительные параметры линии тренда .

Шаг 4: Справа вы увидите параметры TrendLine . Выберите Скользящее среднее и установите период равным 3 .

Шаг 5: Теперь у нас есть линия скользящего среднего на графике.


Шаг 6: Сделайте линию сплошной и измените цвет.

Что нужно помнить о скользящих средних в Excel

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

Рекомендуемые статьи

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

Скользящее среднее в MS EXCEL

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

Метод скользящего среднего состоит в вычислении средних значений на основе предшествующих значений исследуемого числового ряда.

В случае усреднения за 3 периода скользящее среднее равно:
Y скол.i =(Y i + Y i-1 + Y i-2 )/3
На картинке ниже показано как вычислить в MS EXCEL скользящее среднее путем усреднения значений за три периода (за два предыдущих и один текущий).

Примечание : В англоязычной литературе для скользящего среднего используется термин Moving Average (MA) или Simple Moving Average (SMA) , а также rolling average, running average, moving mean.

Читайте так же:
Программы для блокировки программ

Скользящее среднее в надстройке MS EXCEL Пакет анализа

Получить ряд, сглаженный методом скользящего среднего, можно с помощью надстройки MS EXCEL Пакет анализа (Analysis ToolPak) . Надстройка доступна из вкладки Данные, группа Анализ (EXCEL 2010).


Разместим исходный числовой ряд (26 значений) в диапазоне B7:B32.

Для наглядности пронумеруем каждое значение ряда (столбец А).
Вызовем надстройку Пакет анализа, выберем инструмент Скользящее среднее

и нажмем ОК.

В появившемся диалоговом окне в поле Входной интервал введите ссылку на диапазон с данными ряда, т.е. на B7:B32.
Если диапазон включает и заголовок, то нужно установить галочку в поле Метки в первой строке. В нашем случае устанавливать галочку не требуется, т.к. заголовок не входит в диапазон B7:B32.

В поле Интервал установим значение 3 – будем усреднять значения ряда за 3 периода. В поле Выходной интервал достаточно ввести ссылку на левую верхнюю ячейку диапазона с результатами (укажем ячейку D7).
Также поставим галочки в поле Вывод графика и Стандартные погрешности (будет выведен столбец с расчетами погрешностей, англ. Standard Errors).

Нажмем ОК.

В результате работы надстройки, MS EXCEL разместил значения ряда, полученного методом скользящего среднего, в столбце D (см. файл примера лист Пакет анализа ).
В первых двух ячейках D7 и D8 содержатся текстовые значения ошибки #Н/Д, т.к. нами был выбран вариант усреднения за 3 периода (для получения первого значения скользящего среднего требуется 2 предыдущих и 1 текущее значение). Первое значение ряда, точнее формула =СРЗНАЧ(B7:B9) , содержится в ячейке D9. Второе значение вычисляется с помощью формулы =СРЗНАЧ(B8:B10) и т.д.

Диаграмма

Для отображения рядов MS EXCEL создал диаграмму типа график. Сглаженный ряд на диаграмме называется «Прогноз» (ряд красного цвета), хотя он, по большому счету, прогнозом не является.
Первые 2 значения сглаженного ряда, которые равны ошибке #Н/Д, отражаются как 0, и могут ввести в заблуждение (особенно, если последующие значения ряда близки к 0). Поэтому их лучше удалить в столбце D.
Примечание : Значения #Н/Д, которые вернула надстройка в ячейках D7 и D8, являются просто текстовыми значениями, что принципиально отличается от результата возвращаемого формулами, например, функцией НД() или ВПР() , когда данные не найдены. Если Формат ячейки указан как Общий, то их можно различить визуально: текстовое значение будет выравнено по левому краю, а значение ошибки выравнивается по центру. Кроме того, другие инструменты MS EXCEL не воспринимают #Н/Д, которое вернула надстройка, как ошибку. Например, Условное форматирование не выделит ячейку с текстовым #Н/Д как ячейку содержащую ошибку.
Примечание : При построении диаграммы текстовые значения всегда отображаются как 0. Но, если ошибка #Н/Д является результатом формулы, то воспринимается диаграммой как пустая ячейка и на ней не отображается. Это показано в следующем разделе «Скользящее среднее с настраиваемым количеством периодов усреднения».
Диаграмма позволяет визуально определить «выбросы», т.е. значения исходного ряда, которые существенно отличаются от средних значений. Такие «выбросы» могут быть следствием ошибки, но они оказывают существенное влияние на вид сглаженного ряда.

Вычисление погрешности

В столбце E, начиная с ячейки Е11, MS EXCEL разместил формулы для вычисления погрешностей (англ. Standard Errors):
=КОРЕНЬ(СУММКВРАЗН(B9:B11;D9:D11)/3)
Т.е. данная погрешность вычисляется по формуле:

Читайте так же:
Как в Windows 10 удалить «Магазин приложений»?

Значения y i – это значения исходного ряда в период i. Значения «yi с крышечкой» — значения ряда, полученного методом скользящего среднего, в тот же в период i. Значение n равно 3, т.к. в нашем случае усреднение производится 3 периода.
Формула погрешности совпадает с выражением среднеквадратичной ошибки (англ. RMSE – Root Mean Squared Errors, квадратный корень из среднего значения квадратов ошибок), но вычисленной не для всей выборки (ряда), а только на интервале сглаживания (в нашем случае за 3 периода).
Обычно рассчитывается 2 типа ошибок: ошибка сглаживания (ошибка подгонки модели; англ. fitting errors или residuals) и ошибка прогнозирования (forecast errors).
Погрешности, вычисленные надстройкой Пакет анализа, являются ошибками прогнозирования. Эту погрешность можно использовать, чтобы рассчитать интервал предсказания (prediction interval). Про вычисление прогнозного значения и его интервала предсказания см. статью Прогнозирование методом скользящего среднего .
Отметим, что MS EXCEL вычисляет целый массив погрешностей (столбец Е), но для построения интервала предсказания необходимо только последнее значение.

Скользящее среднее с настраиваемым количеством периодов усреднения (формулы)

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

Значения сглаженного ряда вычисляются с помощью формулы:
= ЕСЛИ(A11
в ячейке D8 содержится количество периодов усреднения. Про функцию СМЕЩ() можно прочитать в этой статье .
Погрешности вычисляются по формуле:
= КОРЕНЬ(СУММКВРАЗН(СМЕЩ(B11;-$D$8+1;;$D$8);СМЕЩ(C11;-$D$8+1;;$D$8))/$D$8)
Выбор количества периодов усреднения для удобства осуществляется с помощью элемента управления Счетчик .

Вычисление скользящего среднего с помощью линии тренда (на диаграмме)

На диаграмме с помощью линии тренда можно построить график Скользящего среднего с заданным количеством периодов усреднения.
Используем данные файла примера на листе Формулы . Сначала построим ряд скользящего среднего с 5-ю периодами усреднения с помощью формул.

Теперь построим линию тренда, которая совпадет с красным графиком «Сглаженный ряд». Для этого:
• Щелкните диаграмму, чтобы выделить ее.
• Выберите ряд данных, к которому нужно добавить график скользящего среднего (синий график).
• На вкладке Макет в группе Анализ нажмите кнопку Линия тренда и выберите пункт Дополнительные параметры линии тренда.

• В открывшемся окне выберите Линейная фильтрация и установите в поле Точки значение 5.

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

Примечание : У метода Скользящее среднее есть несколько модификаций, которые рассмотрены в одноименной статье.

Расчет скользящей средней в Excel и прогнозирование

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

Использование скользящих средних в Excel

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

Читайте так же:
Скачать Avast Uninstall Utility 18.1.3800.0 бесплатно

Временной ряд – это множество значений X и Y, связанных между собой. Х – интервалы времени, постоянная переменная. Y – характеристика исследуемого явления (цена, например, действующая в определенный период времени), зависимая переменная. С помощью скользящего среднего можно выявить характер изменений значения Y во времени и спрогнозировать данный параметр в будущем. Метод действует тогда, когда для значений четко прослеживается тенденция в динамике.

Например, нужно спрогнозировать продажи на ноябрь. Исследователь выбирает количество предыдущих месяцев для анализа (оптимальное число m членов скользящего среднего). Прогнозом на ноябрь будет среднее значение параметров за m предыдущих месяца.

Задача. Проанализировать выручку предприятия за 11 месяцев и составить прогноз на 12 месяц.

Выручка.

Сформируем сглаженные временные ряды методом скользящего среднего посредством функции СРЗНАЧ. Найдем средние отклонения сглаженных временных рядов от заданного временного ряда.

  1. По значениям исходного временного ряда строим сглаженный временный ряд методом скользящего среднего по данным за 2 предыдущих месяца. Формула скользящей средней в Excel. Используя маркер автозаполнения, копируем формулу на диапазон ячеек С6:С14. Формула.
  2. Аналогично строим ряд значений трехмесячного скользящего среднего. Формула: СРЗНАЧ.
  3. По такому же принципу формируем ряд значений четырехмесячного скользящего среднего. Ряд значений.
  4. Построим график заданного временного ряда и рассчитанные относительно его значений прогнозы по данному методу. На рисунке видно, что линии тренда скользящего среднего сдвинуты относительно линии исходного временного ряда. Это объясняется тем, что рассчитанные значения сглаженных временных рядов запаздывают по сравнению с соответствующими значениями заданного ряда. Ведь расчеты базировались на данных предыдущих наблюдений. График.
  5. Рассчитаем абсолютные, относительные и средние квадратичные отклонения по сглаженным временным рядам. Абсолютные отклонения:

Относительные отклонения.

Средние квадратичные отклонения:

Средние квадратичные отклонения.

При расчете отклонений брали одинаковое число наблюдений. Это необходимо для того, чтобы провести сравнительный анализ погрешностей.

После сопоставления таблиц с отклонениями стало видно, что для составления прогноза по методу скользящей средней в Excel о тенденции изменения выручки предприятия предпочтительнее модель двухмесячного скользящего среднего. У нее минимальные ошибки прогнозирования (в сравнении с трех- и четырехмесячной).

Прогнозное значение выручки на 12 месяц – 9 430 у.е.

Применение надстройки «Пакет анализа»

Для примера возьмем ту же задачу.

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

На вкладке «Данные» находим команду «Анализ данных». В открывшемся диалоговом окне выбираем «Скользящее среднее»:

Анализ данных.

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

Параметры.

Установив флажок в поле «Стандартные погрешности», мы автоматически добавляем в таблицу столбец со статистической оценкой погрешности.

Точно так же находим скользящее среднее по трем месяцам. Меняется только интервал (3) и выходной диапазон.

Пример.

Сравнив стандартные погрешности, убеждаемся в том, что модель двухмесячного скользящего среднего больше подходит для сглаживания и прогнозирования. Она имеет меньшие стандартные погрешности. Прогнозное значение выручки на 12 месяц – 9 430 у.е.

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

Ссылка на основную публикацию
Adblock
detector