Метод скользящего среднего в excel

Обновлено: 05.07.2024

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Скользящее среднее используется для сглаживания краткосрочных колебаний с целью определения долгосрочного тренда. Вычислим скользящее среднее с помощью надстройки 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 Пакет анализа (Analysis ToolPak) . Надстройка доступна из вкладки Данные, группа Анализ (EXCEL 2010).

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

Нажмем ОК.

Диаграмма

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

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

Значения 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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример № 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Расчет скользящего / скользящего среднего с помощью функции среднего в Excel
Расчет скользящей средней с помощью инструмента анализа скользящей средней в Excel
Добавить линию тренда скользящего среднего для существующей диаграммы в Excel

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

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

1. Выделите третью ячейку помимо исходных данных, говорит Ячейка C4 в нашем примере, и введите формулу = СРЕДНИЙ (B2: B4) (B2: B4 - это первые три данных в серии данных) и перетащите дескриптор автозаполнения этой ячейки в нужный диапазон. Смотрите скриншот:
Примечание: Нам нужно рассчитать скользящую среднюю с интервалом в 3 данных, поэтому вводим формулу в третью ячейку.

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

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

Kutools for Excel предоставляет симпатичное обходное решение Авто Текст Утилита для сохранения диапазона в виде записи автотекста, в которой могут оставаться форматы ячеек и формулы в диапазоне. И затем вы можете повторно использовать этот диапазон одним щелчком мыши в любой книге. Полнофункциональная бесплатная 30-дневная пробная версия!

Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

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

Excel 2010 и 2013 годов Анализ данных Команда поддерживает групповые инструменты анализа финансовых и научных данных, включая инструмент Moving Average, который может помочь вам вычислить среднее значение определенного диапазона и легко создать график скользящего среднего.

1, Нажмите Файл > Параметры.

2. В диалоговом окне Параметры Excel щелкните значок Надстройки в левой панели, Keep Надстройки Excel выбранный в Управление и нажмите Go кнопку.

3. В открывшемся диалоговом окне надстройки установите флажок Анализ ToolPak в Доступны надстройки и нажмите OK кнопку.

4. Теперь вы вернетесь к основному интерфейсу Excel. Щелкните значок Данные > Анализ данных.

5. Во всплывающем диалоговом окне Анализ данных щелкните, чтобы выделить Скользящее среднее в Инструменты анализа и нажмите OK кнопку.

6. Теперь в диалоговом окне "Скользящее среднее", пожалуйста:

(1) Поместите курсор в Диапазон ввода поле, а затем выберите диапазон, в котором вы хотите рассчитать скользящие средние. В нашем случае мы выбираем Range B2: B13.
(2) В Интервал в поле введите интервал, на основе которого вы хотите рассчитать скользящие средние. В нашем случае мы вводим в него 3.
(3) Поместите курсор в Выходной диапазон поле, а затем выберите диапазон, в котором вы хотите вывести скользящие средние. В нашем случае мы выбираем Range C2: C13.
(4) Проверьте Выход графика и нажмите OK кнопку.

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

Примечание: Этот метод не работает в Excel 2007, поскольку Анализ ToolPak недоступно в Excel 2007.

Добавить линию тренда скользящего среднего для существующей диаграммы в Excel

Если вы ранее создавали столбчатую диаграмму в Excel, вы можете легко добавить линию тренда скользящего среднего для этой существующей диаграммы следующим образом:

1. Выберите диаграмму, а затем щелкните значок Дизайн > Добавить элемент диаграммы > Trendline > Дополнительные параметры линии тренда в Excel 2013.

Ноты:
(1) Если вам не нужно указывать интервал скользящих средних, названия скользящих средних и т. Д., Вы можете нажать кнопку Дизайн > Добавить элемент диаграммы > Trendline > Скользящее среднее непосредственно.
(2) В Excel 2007 и 2010 вы можете щелкнуть макет > Trendline > Дополнительные параметры линии тренда.

2. В открывшемся диалоговом окне / панели «Форматировать линию тренда» установите флажок Скользящее среднее и укажите Интервал скользящей средней в период коробка. Смотрите снимки экрана:

Excel 2013 и более поздняя версия:

Excel 2007 и 2010:

3. Закройте диалоговое окно / панель «Линия тренда». Теперь вы увидите, что линия тренда скользящей средней добавлена в столбчатую диаграмму. См. Снимок экрана ниже:

Читайте также: