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

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

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

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

Существует три типа скользящих средних, а именно простая скользящая средняя, ​​взвешенная скользящая средняя и экспоненциальная скользящая средняя в Excel.

# 1 — Простая скользящая средняя в Excel

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

Простая скользящая средняя = [P1+P2+………….+Pn]/ п

# 2 — Взвешенная скользящая средняя в Excel

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

Средневзвешенное скользящее среднее = (Цена * весовой коэффициент) + (Цена предыдущего периода * весовой коэффициент-1)

# 3 — Экспоненциальная скользящая средняя в Excel

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

Экспоненциальная скользящая средняя = (K x (C — P)) + P

Где,

  • K = константа экспоненциального сглаживания
  • C= текущая цена
  • п= экспоненциальная скользящая средняя за предыдущие периоды (простая скользящая средняя, ​​используемая для расчета первых периодов)

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

Ниже приведены примеры скользящей средней в Excel.

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

Пример # 1 — Простая скользящая средняя в Excel

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

Пример скользящей средней 1

Шаг 1: Скользящее среднее за январь, февраль и март рассчитывается путем деления суммы продаж за месяцы на 3.

Пример скользящей средней 1-1

Пример скользящей средней 1-2

Шаг 2: Выбор в углу ячейки D5, а затем просто перетаскивание вниз даст скользящее среднее для оставшихся периодов. Это функция инструмента заливки в Excel.

Пример скользящей средней 1-3

Прогноз продаж на январь 2019 года — 10456,66667.

Пример скользящей средней 1-4

Шаг 3: Теперь мы нанесем на линейный график показатель продаж и скользящее среднее, чтобы понять разницу в тенденции. Это можно сделать на вкладке вставки. Сначала мы выбрали ряд данных, а затем из раздела «Диаграммы» под вставкой мы использовали линейный график.

Пример скользящей средней 1-5

Пример скользящей средней 1-6

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

Пример скользящей средней 1-7

Пример # 2 — Простое скользящее среднее на вкладке анализа данных в Excel

  • На вкладке «Данные» в группе «Анализ» мы должны нажать «Анализ данных». Ниже приведен снимок экрана.

Пример скользящей средней 2

  • Из анализа данных можно получить доступ к скользящей средней.

Пример скользящей средней 2-1

  • После щелчка по скользящей средней мы выбрали показатель продаж в качестве диапазона ввода.

Пример скользящей средней 2-2

  • Метки в первой строке нажимаются, чтобы Excel понял, что первая строка имеет имя метки.

Пример скользящей средней 2-3

  • Интервал 3 выбран, потому что нам нужно трехлетнее скользящее среднее.

Пример скользящей средней 2-4

  • Мы выбрали диапазон выпуска с привязкой к объему продаж.

Пример скользящей средней 2-5

  • Мы также хотим увидеть результат диаграммы, на котором мы сможем увидеть разницу между фактическим и прогнозируемым.

Пример скользящей средней 2-6

На этом графике показана разница между фактической и прогнозируемой скользящей средней.

Пример скользящей средней 2-7

Пример № 3 — Взвешенная скользящая средняя в Excel

Пример скользящей средней 3

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

Пример скользящей средней 3-1

После использования формулы мы получили скользящую среднюю за период.

Пример скользящей средней 3-2

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

Пример 3-3

Прогноз на январь 2019 г., т. Е. 10718,33

Пример 3-4

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

Пример 3-5

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

Пример 3-6

Пример 3-7

Пример # 4 — Экспоненциальная скользящая средняя в Excel

Формула экспоненциальной скользящей средней: Sт= α.Yт-1+ (1- α) Sт-1…… (1)

Где,

  • Ит-1 = фактическое наблюдение в t-1-м периоде
  • Sт-1= простая скользящая средняя за период t-1
  • а = коэффициент сглаживания, и он варьируется от 0,1 до 0,3. Чем больше значение α приближает график к фактическим значениям, и чем меньше значение α, тем более гладким будет график.

Сначала мы вычисляем простую скользящую среднюю, как показано ранее. После этого применяем формулу, приведенную в уравнении (1). Чтобы зафиксировать значение α для всех следующих значений, мы нажали F4.

MA Пример 4

MA, пример 4-1

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

MA, пример 4-2

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

MA, пример 4-3

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

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

  1. Простое скользящее среднее можно рассчитать с помощью функции СРЕДНЕЕ в Excel.
  2. Скользящее среднее помогает сглаживать данные
  3. Средние сезонные значения часто называют сезонным индексом.
  4. Экспоненциальная скользящая средняя в Excel придает больший вес последним данным, чем простая скользящая средняя. Следовательно, сглаживание в случае экспоненциальной скользящей средней в Excel больше, чем у простой скользящей средней.
  5. В таких компаниях, как фондовый рынок, скользящая средняя помогает трейдеру легче определить тренд.

УЗНАТЬ БОЛЬШЕ >>

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован.