Расчет скользящего среднего в Excel (простой, взвешенный и экспоненциальный)

Как и многие из моих руководств по Excel, это также вдохновлено одним из вопросов, которые я получил от друга. Она хотела рассчитать скользящее среднее в Excel, и я попросил ее поискать его в Интернете (или посмотреть видео на YouTube об этом).

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

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

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

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

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

Я уверен, что вы знаете, каково среднее значение.

Если у меня есть данные о температуре за три дня, вы можете легко сказать мне среднее значение за последние три дня (подсказка: для этого вы можете использовать функцию СРЕДНЕЕ в Excel).

Скользящее среднее (также называемое скользящим средним или скользящим средним) - это когда вы сохраняете временной период среднего одинаковым, но продолжает двигаться по мере добавления новых данных.

Например, в день 3, если я спрошу вас о 3-дневной скользящей средней температуре, вы дадите мне среднее значение температуры для дней 1, 2 и 3. А если в день 4 я спрошу вас о 3-дневной скользящей средней температуре. , вы дадите мне среднее значение для дней 2, 3 и 4.

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

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

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

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

Есть три типа скользящих средних:

• Простая скользящая средняя (SMA)
• Взвешенная скользящая средняя (WMA)
• Экспоненциальная скользящая средняя (EMA)

Простая скользящая средняя (SMA)

Это простое среднее значение точек данных за заданный период времени.

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

Это может быть достигнуто путем усреднения точек данных за заданную продолжительность. В Excel это легко сделать с помощью функции СРЕДНЕЕ (она рассматривается далее в этом руководстве).

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

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

Температура дня 10, скорее всего, будет лучшим индикатором тенденции по сравнению с днем ​​1 (поскольку температура падает с каждым днем).

Итак, нам будет лучше, если мы будем больше полагаться на ценность 10-го дня.

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

Это называется взвешенной скользящей средней.

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

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

Его также называют экспоненциально-взвешенной скользящей средней (EWMA).

Разница между WMA и EMA заключается в том, что с WMA вы можете назначать веса на основе любых критериев. Например, в 3-точечном скользящем среднем вы можете присвоить возраст 60% веса последней точке данных, 30% средней точке данных и 10% самой старой точке данных.

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

Хватит лекции по статистике.

Теперь давайте углубимся и посмотрим, как рассчитать скользящие средние в Excel.

Расчет простой скользящей средней (SMA) с использованием пакета инструментов анализа данных в Excel

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

Это называется Пакет инструментов для анализа данных.

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

Если у вас уже есть опция «Анализ данных» на вкладке «Данные», пропустите приведенные ниже шаги и просмотрите шаги по вычислению скользящих средних.

Щелкните вкладку «Данные» и проверьте, отображается ли параметр «Анализ данных». Если вы его не видите, выполните следующие действия, чтобы сделать его доступным на ленте.

1. Перейдите на вкладку Файл.
2. Нажмите на Параметры
3. В диалоговом окне «Параметры Excel» нажмите «Надстройки».
4. В нижней части диалогового окна выберите в раскрывающемся списке Надстройки Excel и нажмите «Перейти».
5. В открывшемся диалоговом окне надстройки отметьте опцию Analysis Toolpak.
6. Щелкните ОК.

Вышеупомянутые шаги позволят включить Data Analysis Toolpack, и теперь вы увидите эту опцию на вкладке «Данные».

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

Ниже приведены шаги по использованию анализа данных для расчета простого скользящего среднего:

1. Перейдите на вкладку "Данные".
2. Нажмите на опцию анализа данных
3. В диалоговом окне «Анализ данных» выберите параметр «Скользящее среднее» (возможно, вам придется немного прокрутить экран, чтобы его достичь).
4. Щелкните ОК. Откроется диалоговое окно «Скользящее среднее».
5. Во Входном диапазоне выберите данные, для которых вы хотите вычислить скользящее среднее (B2: B11 в этом примере).
6. В поле «Интервал» введите 3 (поскольку мы рассчитываем трехточечную скользящую среднюю).
7. В области вывода введите ячейку, в которой вы хотите получить результаты. В этом примере я использую C2 в качестве диапазона вывода.
8. Нажмите ОК.

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

Обратите внимание, что первые две ячейки в столбце C имеют результат как # N / A error. Это потому, что это трехточечная скользящая средняя, ​​и для получения первого результата требуется как минимум три точки данных. Таким образом, фактические значения скользящего среднего начинаются после третьей точки данных.

Вы также заметите, что весь этот пакет инструментов анализа данных применяет к ячейкам формулу СРЕДНЕГО. Так что, если вы хотите сделать это вручную без инструментария анализа данных, вы, безусловно, можете это сделать.

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

Расчет скользящих средних (SMA, WMA, EMA) с использованием формул в Excel

Вы также можете рассчитать скользящие средние по формуле СРЕДНИЙ.

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

Кроме того, Data analysis Toolpak дает только простую скользящую среднюю (SMA), но если вы хотите рассчитать WMA или EMA, вам нужно полагаться только на формулы.

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

Предположим, у вас есть набор данных, показанный ниже, и вы хотите рассчитать 3-точечный SMA:

В ячейке C4 введите следующую формулу:

= СРЕДНИЙ (B2: B4)

Скопируйте эту формулу для всех ячеек, и она даст вам SMA на каждый день.

Помните: при вычислении SMA с использованием формул необходимо убедиться, что ссылки в формуле являются относительными. Это означает, что формула может быть = СРЕДНЕЕ (B2: B4) или = СРЕДНЕЕ ($ B2: $ B4), но не может быть = СРЕДНЕЕ ($ B $ 2: $ B $ 4) или = СРЕДНЕЕ (B $ 2: B $ 4. ). Часть ссылки с номером строки должна быть без знака доллара. Вы можете узнать больше об абсолютных и относительных ссылках здесь.

Поскольку мы вычисляем 3-точечное простое скользящее среднее (SMA), первые две ячейки (для первых двух дней) пусты, и мы начинаем использовать формулу с третьего дня и далее. При желании вы можете использовать первые два значения как есть и использовать значение SMA, начиная с третьего.

Расчет взвешенного скользящего среднего с использованием формул

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

Например, предположим, что вам нужно рассчитать 3-точечный WMA для приведенного ниже набора данных, где 60% веса дается последнему значению, 30% - предыдущему и 10% - предыдущему.

Для этого введите следующую формулу в ячейку C4 и скопируйте для всех ячеек.

= 0,6 * B4 + 0,3 * B3 + 0,1 * B2

Поскольку мы вычисляем 3-точечное взвешенное скользящее среднее (WMA), первые две ячейки (для первых двух дней) пусты, и мы начинаем использовать формулу с третьего дня и далее. При желании можно использовать первые два значения как есть и использовать значение WMA, начиная с третьего.

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

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

Ниже приведена формула для расчета EMA для трехточечной скользящей средней:

EMA = [Последнее значение - Предыдущее значение EMA] * (2 / N + 1) + Предыдущая EMA

… Где N будет 3 в этом примере (поскольку мы вычисляем трехточечную EMA)

Примечание. Для первого значения EMA (если у вас нет предыдущего значения для расчета EMA) просто возьмите значение как есть и считайте его значением EMA. Затем вы можете использовать это значение в будущем.

Предположим, у вас есть следующий набор данных и вы хотите рассчитать трехпериодную EMA:

В ячейке C2 введите то же значение, что и в B2. Это потому, что нет предыдущего значения для расчета EMA.

В ячейке C3 введите приведенную ниже формулу и скопируйте для всех ячеек:

= (B3-C2) * (2/4) + C2

В этом примере я сохранил простоту и использовал последнее значение и предыдущее значение EMA для вычисления текущей EMA.

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

Добавление линии тренда скользящего среднего на столбчатый график

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

Предположим, у вас есть набор данных, как показано ниже:

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

1. Выберите набор данных (включая заголовки)
2. Щелкните вкладку Вставка
3. В группе «Диаграмма» щелкните значок «Вставить столбец или гистограмму».
4. Нажмите на опцию Clustered Column chart. Это вставит диаграмму в рабочий лист.
5. Выделив диаграмму, щелкните вкладку «Дизайн» (эта вкладка появляется только при выборе диаграммы).
6. В группе «Макеты диаграмм» нажмите «Добавить элемент диаграммы».
7. Наведите курсор на параметр «Линия тренда», а затем нажмите «Дополнительные параметры линии тренда».
8. На панели «Форматирование линии тренда» выберите параметр «Скользящее среднее» и установите количество периодов.

Вот и все! Вышеупомянутые шаги добавят движущуюся линию тренда к вашей столбчатой ​​диаграмме.

Если вы хотите вставить более одной линии тренда скользящего среднего (например, одну для 2 периодов и одну для 3 периодов), повторите шаги с 5 по 8).

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

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

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

Вот несколько вещей, которые вы можете отформатировать на линии тренда:

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

Чтобы отформатировать линию тренда скользящего среднего, щелкните ее правой кнопкой мыши и выберите параметр «Форматировать линию тренда».

Откроется панель «Форматировать линию тренда» справа. Эта панель, как и все параметры форматирования (в разных разделах - «Заливка и линия», «Эффекты» и «Параметры линии тренда»).