Содержание
- Применение скользящей средней
- Способ 1: Пакет анализа
- Способ 2: использование функции СРЗНАЧ
- Вопросы и ответы
Метод скользящей средней – это статистический инструмент, с помощью которого можно решать различного рода задачи. В частности, он довольно часто используется при прогнозировании. В программе Excel для решения целого ряда задач также можно применять данный инструмент. Давайте разберемся, как используется скользящая средняя в Экселе.
Применение скользящей средней
Смысл данного метода состоит в том, что с его помощью происходит смена абсолютных динамических значений выбранного ряда на средние арифметические за определенный период путем сглаживания данных. Этот инструмент применяется для экономических расчетов, прогнозирования, в процессе торговли на бирже и т.д. Применять метод скользящей средней в Экселе лучше всего с помощью мощнейшего инструмента статистической обработки данных, который называется Пакетом анализа. Кроме того, в этих же целях можно использовать встроенную функцию Excel СРЗНАЧ.
Способ 1: Пакет анализа
Пакет анализа представляет собой надстройку Excel, которая по умолчанию отключена. Поэтому, прежде всего, требуется её включить.
- Перемещаемся во вкладку «Файл». Делаем щелчок по пункту «Параметры».
- В запустившемся окне параметров следует перейти в раздел «Надстройки». В нижней части окна в поле «Управление» должен быть выставлен параметр «Надстройки Excel». Щелкаем по кнопке «Перейти».
- Мы попадаем в окно надстроек. Устанавливаем галочку около пункта «Пакет анализа» и щелкаем по кнопке «OK».
После этого действия пакет «Анализ данных» активирован, и соответствующая кнопка появилась на ленте во вкладке «Данные».
А теперь давайте рассмотрим, как непосредственно можно использовать возможности пакета Анализ данных для работы по методу скользящей средней. Давайте на основе информации о доходе фирмы за 11 предыдущих периодов составим прогноз на двенадцатый месяц. Для этого воспользуемся заполненной данными таблицей, а также инструментами Пакета анализа.
- Переходим во вкладку «Данные» и жмем на кнопку «Анализ данных», которая размещена на ленте инструментов в блоке «Анализ».
- Открывается перечень инструментов, которые доступны в Пакете анализа. Выбираем из них наименование «Скользящее среднее» и жмем на кнопку «OK».
- Запускается окно ввода данных для прогнозирования методом скользящей средней.
В поле «Входной интервал» указываем адрес диапазона, где расположена помесячно сумма выручки без ячейки, данные в которой следует рассчитать.
В поле «Интервал» следует указать интервал обработки значений методом сглаживания. Для начала давайте установим значение сглаживания в три месяца, а поэтому вписываем цифру «3».
В поле «Выходной интервал» нужно указать произвольный пустой диапазон на листе, где будут выводиться данные после их обработки, который должен быть на одну ячейку больше входного интервала.
Также следует установить галочку около параметра «Стандартные погрешности».
При необходимости, можно также установить галочку около пункта «Вывод графика» для визуальной демонстрации, хотя в нашем случае это и не обязательно.
После того, как все настройки внесены, жмем на кнопку «OK».
- Программа выводит результат обработки.
- Теперь выполним сглаживание за период в два месяца, чтобы выявить, какой результат является более корректным. Для этих целей опять запускаем инструмент «Скользящее среднее» Пакета анализа.
В поле «Входной интервал» оставляем те же значения, что и в предыдущем случае.
В поле «Интервал» ставим цифру «2».
В поле «Выходной интервал» указываем адрес нового пустого диапазона, который, опять же, должен быть на одну ячейку больше входного интервала.
Остальные настройки оставляем прежними. После этого жмем на кнопку «OK».
- Вслед за этим программа производит расчет и выводит результат на экран. Для того, чтобы определить, какая из двух моделей более точная, нам нужно сравнить стандартные погрешности. Чем меньше данный показатель, тем выше вероятность точности полученного результата. Как видим, по всем значениям стандартная погрешность при расчете двухмесячной скользящей меньше, чем аналогичный показатель за 3 месяца. Таким образом, прогнозируемым значением на декабрь можно считать величину, рассчитанную методом скольжения за последний период. В нашем случае это значение 990,4 тыс. рублей.
Способ 2: использование функции СРЗНАЧ
В Экселе существует ещё один способ применения метода скользящей средней. Для его использования требуется применить целый ряд стандартных функций программы, базовой из которых для нашей цели является СРЗНАЧ. Для примера мы будем использовать все ту же таблицу доходов предприятия, что и в первом случае.
Как и в прошлый раз, нам нужно будет создать сглаженные временные ряды. Но на этот раз действия будут не настолько автоматизированы. Следует рассчитать среднее значение за каждые два, а потом три месяца, чтобы иметь возможность сравнить результаты.
Прежде всего, рассчитаем средние значения за два предыдущих периода с помощью функции СРЗНАЧ. Сделать это мы можем, только начиная с марта, так как для более поздних дат идет обрыв значений.
- Выделяем ячейку в пустой колонке в строке за март. Далее жмем на значок «Вставить функцию», который размещен вблизи строки формул.
- Активируется окно Мастера функций. В категории «Статистические» ищем значение «СРЗНАЧ», выделяем его и щелкаем по кнопке «OK».
- Запускается окно аргументов оператора СРЗНАЧ. Синтаксис у него следующий:
=СРЗНАЧ(число1;число2;…)Обязательным является только один аргумент.
В нашем случае, в поле «Число1» мы должны указать ссылку на диапазон, где указан доход за два предыдущих периода (январь и февраль). Устанавливаем курсор в поле и выделяем соответствующие ячейки на листе в столбце «Доход». После этого жмем на кнопку «OK».
- Как видим, результат расчета среднего значения за два предыдущих периода отобразился в ячейке. Для того, чтобы выполнить подобные вычисления для всех остальных месяцев периода, нам нужно скопировать данную формулу в другие ячейки. Для этого становимся курсором в нижний правый угол ячейки, содержащей функцию. Курсор преобразуется в маркер заполнения, который имеет вид крестика. Зажимаем левую кнопку мыши и протягиваем его вниз до самого конца столбца.
- Получаем расчет результатов среднего значения за два предыдущих месяца до конца года.
- Теперь выделяем ячейку в следующем пустом столбце в строке за апрель. Вызываем окно аргументов функции СРЗНАЧ тем же способом, который был описан ранее. В поле «Число1» вписываем координаты ячеек в столбце «Доход» с января по март. Затем жмем на кнопку «OK».
- С помощью маркера заполнения копируем формулу в ячейки таблицы, расположенные ниже.
- Итак, значения мы подсчитали. Теперь, как и в предыдущий раз, нам нужно будет выяснить, какой вид анализа более качественный: со сглаживанием в 2 или в 3 месяца. Для этого следует рассчитать среднее квадратичное отклонение и некоторые другие показатели. Для начала рассчитаем абсолютное отклонение, воспользовавшись стандартной функцией Excel ABS, которая вместо положительных или отрицательных чисел возвращает их модуль. Данное значение будет равно разности между реальным показателем выручки за выбранный месяц и прогнозируемым. Устанавливаем курсор в следующий пустой столбец в строку за май. Вызываем Мастер функций.
- В категории «Математические» выделяем наименование функции «ABS». Жмем на кнопку «OK».
- Запускается окно аргументов функции ABS. В единственном поле «Число» указываем разность между содержимым ячеек в столбцах «Доход» и «2 месяца» за май. Затем жмем на кнопку «OK».
- С помощью маркера заполнений копируем данную формулу во все строки таблицы по ноябрь включительно.
- Рассчитываем среднее значение абсолютного отклонения за весь период с помощью уже знакомой нам функции СРЗНАЧ.
- Аналогичную процедуру выполняем и для того, чтобы подсчитать абсолютное отклонение для скользящей за 3 месяца. Сначала применяем функцию ABS. Только на этот раз считаем разницу между содержимым ячеек с фактическим доходом и плановым, рассчитанным по методу скользящей средней за 3 месяца.
- Далее рассчитываем среднее значение всех данных абсолютного отклонения с помощью функции СРЗНАЧ.
- Следующим шагом является подсчет относительного отклонения. Оно равно отношению абсолютного отклонения к фактическому показателю. Для того чтобы избежать отрицательных значений, мы опять воспользуемся теми возможностями, которые предлагает оператор ABS. На этот раз с помощью данной функции делим значение абсолютного отклонения при использовании метода скользящей средней за 2 месяца на фактический доход за выбранный месяц.
- Но относительное отклонение принято отображать в процентном виде. Поэтому выделяем соответствующий диапазон на листе, переходим во вкладку «Главная», где в блоке инструментов «Число» в специальном поле форматирования выставляем процентный формат. После этого результат подсчета относительного отклонения отображается в процентах.
- Аналогичную операцию по подсчету относительного отклонения проделываем и с данными с применением сглаживания за 3 месяца. Только в этом случае для расчета в качестве делимого используем другой столбец таблицы, который у нас имеет название «Абс. откл (3м)». Затем переводим числовые значения в процентный вид.
- После этого высчитываем средние значения для обеих колонок с относительным отклонением, как и ранее используя для этого функцию СРЗНАЧ. Так как для расчета в качестве аргументов функции мы берем процентные величины, то дополнительную конвертацию производить не нужно. Оператор на выходе выдает результат уже в процентном формате.
- Теперь мы подошли к расчету среднего квадратичного отклонения. Этот показатель позволит нам непосредственно сравнить качество расчета при использовании сглаживания за два и за три месяца. В нашем случае среднее квадратичное отклонение будет равно корню квадратному из суммы квадратов разностей фактической выручки и скользящей средней, деленной на количество месяцев. Для того, чтобы произвести расчет в программе, нам предстоит воспользоваться целым рядом функций, в частности КОРЕНЬ, СУММКВРАЗН и СЧЁТ. Например, для расчета среднего квадратичного отклонения при использовании линии сглаживания за два месяца в мае будет в нашем случае применяться формула следующего вида:
=КОРЕНЬ(СУММКВРАЗН(B6:B12;C6:C12)/СЧЁТ(B6:B12))Копируем её в другие ячейки столбца с расчетом среднего квадратичного отклонения посредством маркера заполнения.
- Аналогичную операцию по расчету среднего квадратичного отклонения выполняем и для скользящей средней за 3 месяца.
- После этого рассчитываем среднее значение за весь период для обоих этих показателей, применив функцию СРЗНАЧ.
- Произведя сравнение расчетов методом скользящей средней со сглаживанием в 2 и 3 месяца по таким показателям, как абсолютное отклонение, относительное отклонение и среднеквадратичное отклонение, можно с уверенностью сказать, что сглаживание за два месяца дает более достоверные результаты, чем применение сглаживания за три месяца. Об этом говорит то, что вышеуказанные показатели по двухмесячному скользящему среднему, меньше, чем по трехмесячному.
- Таким образом, прогнозируемый показатель дохода предприятия за декабрь составит 990,4 тыс. рублей. Как видим, это значение полностью совпадает с тем, которое мы получили, производя расчет с помощью инструментов Пакета анализа.
Урок: Мастер функций в Экселе
Мы произвели расчет прогноза при помощи метода скользящей средней двумя способами. Как видим, данную процедуру намного проще выполнить с помощью инструментов Пакета анализа. Тем не менее некоторые пользователи не всегда доверяют автоматическому расчету и предпочитают для вычислений использовать функцию СРЗНАЧ и сопутствующие операторы для проверки наиболее достоверного варианта. Хотя, если все сделано правильно, на выходе результат расчетов должен получиться полностью одинаковым.
Еще статьи по данной теме:
Помогла ли Вам статья?
Практическое моделирование экономических ситуаций подразумевает разработку прогнозов. С помощью средств Excel можно реализовать такие эффективные способы прогнозирования, как: экспоненциальное сглаживание, построение регрессий, скользящее среднее. Рассмотрим подробнее использование метода скользящего среднего.
Использование скользящих средних в Excel
Метод скользящей средней – один из эмпирических методов для сглаживания и прогнозирования временных рядов. Суть: абсолютные значения ряда динамики меняются на средние арифметические значения в определенные интервалы. Выбор интервалов осуществляется способом скольжения: первые уровни постепенно убираются, последующие – включаются. В результате получается сглаженный динамический ряд значений, позволяющий четко проследить тенденцию изменений исследуемого параметра.
Временной ряд – это множество значений X и Y, связанных между собой. Х – интервалы времени, постоянная переменная. Y – характеристика исследуемого явления (цена, например, действующая в определенный период времени), зависимая переменная. С помощью скользящего среднего можно выявить характер изменений значения Y во времени и спрогнозировать данный параметр в будущем. Метод действует тогда, когда для значений четко прослеживается тенденция в динамике.
Например, нужно спрогнозировать продажи на ноябрь. Исследователь выбирает количество предыдущих месяцев для анализа (оптимальное число m членов скользящего среднего). Прогнозом на ноябрь будет среднее значение параметров за m предыдущих месяца.
Задача. Проанализировать выручку предприятия за 11 месяцев и составить прогноз на 12 месяц.
Сформируем сглаженные временные ряды методом скользящего среднего посредством функции СРЗНАЧ. Найдем средние отклонения сглаженных временных рядов от заданного временного ряда.
- По значениям исходного временного ряда строим сглаженный временный ряд методом скользящего среднего по данным за 2 предыдущих месяца. Формула скользящей средней в Excel. Используя маркер автозаполнения, копируем формулу на диапазон ячеек С6:С14.
- Аналогично строим ряд значений трехмесячного скользящего среднего. Формула:
- По такому же принципу формируем ряд значений четырехмесячного скользящего среднего.
- Построим график заданного временного ряда и рассчитанные относительно его значений прогнозы по данному методу. На рисунке видно, что линии тренда скользящего среднего сдвинуты относительно линии исходного временного ряда. Это объясняется тем, что рассчитанные значения сглаженных временных рядов запаздывают по сравнению с соответствующими значениями заданного ряда. Ведь расчеты базировались на данных предыдущих наблюдений.
- Рассчитаем абсолютные, относительные и средние квадратичные отклонения по сглаженным временным рядам. Абсолютные отклонения:
Относительные отклонения:
Средние квадратичные отклонения:
При расчете отклонений брали одинаковое число наблюдений. Это необходимо для того, чтобы провести сравнительный анализ погрешностей.
После сопоставления таблиц с отклонениями стало видно, что для составления прогноза по методу скользящей средней в Excel о тенденции изменения выручки предприятия предпочтительнее модель двухмесячного скользящего среднего. У нее минимальные ошибки прогнозирования (в сравнении с трех- и четырехмесячной).
Прогнозное значение выручки на 12 месяц – 9 430 у.е.
Применение надстройки «Пакет анализа»
Для примера возьмем ту же задачу.
На вкладке «Данные» находим команду «Анализ данных». В открывшемся диалоговом окне выбираем «Скользящее среднее»:
Заполняем. Входной интервал – исходные значения временного ряда. Интервал – число месяцев, включаемое в подсчет скользящего среднего. Так как сначала будем строить сглаженный временной ряд по данным двух предыдущих месяцев, в поле вводим цифру 2. Выходной интервал – диапазон ячеек для выведения полученных результатов.
Установив флажок в поле «Стандартные погрешности», мы автоматически добавляем в таблицу столбец со статистической оценкой погрешности.
Точно так же находим скользящее среднее по трем месяцам. Меняется только интервал (3) и выходной диапазон.
Сравнив стандартные погрешности, убеждаемся в том, что модель двухмесячного скользящего среднего больше подходит для сглаживания и прогнозирования. Она имеет меньшие стандартные погрешности. Прогнозное значение выручки на 12 месяц – 9 430 у.е.
Скачать расчет скользящей средней в Excel
Составлять прогнозы по методу скользящего среднего просто и эффективно. Инструмент точно отражает изменения основных параметров предыдущего периода. Но выйти за пределы известных данных нельзя. Поэтому для долгосрочного прогнозирования применяются другие способы.
history 8 января 2021 г.
- Группы статей
Скользящее среднее используется для сглаживания краткосрочных колебаний с целью определения долгосрочного тренда. Вычислим скользящее среднее с помощью надстройки 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)
Т.е. данная погрешность вычисляется по формуле:
Значения 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<$D$8;НД();СРЗНАЧ(СМЕЩ(B11;-$D$8+1;;$D$8)))
в ячейке D8 содержится количество периодов усреднения. Про функцию
СМЕЩ()
можно прочитать в
этой статье
.
Погрешности вычисляются по формуле:
=
КОРЕНЬ(СУММКВРАЗН(СМЕЩ(B11;-$D$8+1;;$D$8);СМЕЩ(C11;-$D$8+1;;$D$8))/$D$8)
Выбор количества периодов усреднения для удобства осуществляется с помощью
элемента управления Счетчик
.
Вычисление скользящего среднего с помощью линии тренда (на диаграмме)
На диаграмме с помощью линии тренда можно построить график Скользящего среднего с заданным количеством периодов усреднения.
Используем данные
файла примера на листе Формулы
. Сначала построим ряд скользящего среднего с 5-ю периодами усреднения с помощью формул.
Теперь построим линию тренда, которая совпадет с красным графиком «Сглаженный ряд». Для этого:
• Щелкните диаграмму, чтобы выделить ее.
• Выберите ряд данных, к которому нужно добавить график скользящего среднего (синий график).
• На вкладке Макет в группе Анализ нажмите кнопку Линия тренда и выберите пункт Дополнительные параметры линии тренда.
• В открывшемся окне выберите Линейная фильтрация и установите в поле Точки значение 5.
После закрытия окна будет выведен график скользящего среднего, полностью совпадающий с красным графиком, ранее построенным с помощью формул.
Примечание
: У метода Скользящее среднее есть несколько модификаций, которые
рассмотрены в одноименной статье.
17 авг. 2022 г.
читать 2 мин
В анализе временных рядов скользящая средняя — это просто среднее значение определенного количества предыдущих периодов.
Экспоненциальное скользящее среднее — это тип скользящего среднего, который придает больший вес недавним наблюдениям, что означает, что он может быстрее фиксировать последние тенденции.
В следующем пошаговом примере показано, как рассчитать экспоненциальное скользящее среднее в Excel.
Шаг 1: введите данные
Во-первых, давайте введем следующий набор данных, который показывает общий объем продаж компании за 10 последовательных периодов продаж:
Шаг 2: Рассчитайте экспоненциальную скользящую среднюю
Далее мы рассчитаем экспоненциальную скользящую среднюю (EMA) по следующей формуле:
EMV = [Latest Value - Previous EMA] * (2/n+1) + Previous EMA
В формуле n представляет количество периодов, используемых для расчета экспоненциальной скользящей средней. Это единственный номер, который вы должны указать.
Для нашего примера мы рассчитаем 3-дневную EMA.
Во-первых, мы введем значение EMA в ячейку B2 , чтобы оно было равно значению в ячейке A2 :
Далее мы будем использовать следующую формулу для расчета первого значения 3-дневной EMA:
=( A3 - B2 )*(2/( $E$1 +1))+ B2
На следующем снимке экрана показано, как использовать эту формулу на практике:
Затем наведите указатель мыши на правый нижний угол ячейки B3 , пока не появится крошечный крестик « + ». Дважды щелкните крестик, чтобы скопировать и вставить формулу в оставшиеся ячейки столбца:
Столбец B теперь показывает 3-дневную экспоненциальную скользящую среднюю продаж.
Чтобы рассчитать экспоненциальное скользящее среднее, используя другое количество периодов, просто измените значение в ячейке E1 .
Например, вместо этого мы могли бы рассчитать 4-дневную экспоненциальную скользящую среднюю продаж, просто изменив значение в ячейке E1 на 4:
Столбец B теперь показывает 4-дневную экспоненциальную скользящую среднюю продаж.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Как рассчитать взвешенное скользящее среднее в Excel
Как рассчитать кумулятивное среднее в Excel
В школьной жизни вы узнали о среднем значении и методе его расчета. Формула для расчета среднего очень проста. Вам просто нужно сложить все значения в данных и разделить результат на общее количество значений в данных. Скользящее среднее — это еще один тип среднего, который имеет практическое применение. Он имеет приложения во многих областях, таких как фондовый рынок, продажи и т. Д.
Скользящее среднее бывает трех типов: простое взвешенное и экспоненциальное. В этой статье мы объясним методы расчета всех трех типов скользящих средних в Excel.
Прочтите: Как использовать функции Excel MIN, Max и AVERAGE.
Мы рассчитаем скользящую среднюю в Excel двумя способами. В одном методе мы будем использовать встроенный инструмент MS Excel, а в другом — формулы.
1]Использование пакета инструментов анализа данных
В этом методе мы будем использовать пакет инструментов анализа данных в Excel для вычисления скользящего среднего. В MS Excel есть инструмент для расчета простых скользящих средних. Пакет инструментов анализа данных — это надстройка, что означает, что у вас может не быть его по умолчанию. Мы перечисляем здесь шаги, чтобы получить этот инструмент.
1]Щелкните на опции «Файл».
2]Выберите «Параметры».
3]Откроется диалоговое окно. Нажмите «Надстройки».
4]На правой панели диалогового окна вы увидите раскрывающееся меню внизу. В раскрывающемся меню выберите «Надстройки Excel» и нажмите кнопку «Перейти».
5]Теперь выберите «Пакет инструментов анализа» и нажмите кнопку «ОК».
6]Вышеупомянутые шаги установят инструмент анализа данных в MS Excel. Посмотреть его можно во вкладке «Данные».
Читайте: Как рассчитать процентную разницу между двумя числами в Excel.
Теперь вы готовы рассчитать простую скользящую среднюю. Чтобы показать вам метод расчета, мы создали образцы данных о различных температурах за первые 10 дней месяца.
Выполните следующие шаги, чтобы рассчитать простую скользящую среднюю с помощью Data Analysis Tool Pack.
1]Щелкните вкладку «Данные» и выберите параметр «Анализ данных», как показано на скриншоте выше (см. Шаг 6).
2]Откроется небольшое диалоговое окно, в котором вам нужно выбрать опцию «Скользящее среднее» и нажать OK. Это простая скользящая средняя.
3]Вы должны ввести диапазон ввода данных, для которого вы хотите вычислить простую скользящую среднюю. В нашем примере данных у нас есть диапазоны данных от ячейки B2 до ячейки B11. Таким образом, мы вошли в диапазон B2: B11. После этого введите «Интервал». В выходном диапазоне вы должны ввести адрес ячейки, в которой вы хотите получить выходной диапазон. Мы выбрали E2. Когда вы закончите, нажмите ОК.
4]Вы получите результат. Вы можете видеть на скриншоте ниже, первые три строки показывают ошибку # N / A. Это потому, что мы ввели 4 в «Интервал», что означает, что это 4-дневная SMA.
Прочтите: Как рассчитать простой процент в Excel.
В методе 1 выше мы рассчитали скользящую среднюю с помощью инструмента в Excel. Но используя метод 1, мы можем рассчитать только простую скользящую среднюю. В методе 2 мы рассчитаем все три типа скользящих средних по формулам.
2]Расчет простой скользящей средней (SMA) с использованием формулы
Здесь мы возьмем те же образцы данных.
1]Если вы хотите рассчитать 4-дневную SMA, вы должны ввести следующую формулу в ячейку, которая находится в строке 5 (день 4). После ввода формулы нажмите «Enter».
= СРЕДНИЙ (B2: B5)
2]Теперь наведите курсор на правый нижний угол выбранной ячейки. Когда ваш курсор изменится на значок «Плюс», нажмите и удерживайте левую кнопку мыши и перетащите ее в ячейку E11. Это скопирует формулу в другие ячейки.
3]Вы получите свой результат.
Читайте: Как рассчитать площадь прямоугольника, треугольника или круга в Excel.
3]Расчет взвешенной скользящей средней (WMA) с использованием формулы
Рассчитаем взвешенную скользящую среднюю. Опять же, мы берем те же образцы данных. Чтобы вычислить WMA, вам нужно присвоить веса конкретным значениям. Формула для расчета средневзвешенной скользящей средней:
WMA = [(Latest value * weight) + (Previous value * weight) + …] / (Сумма всех весов)
Мы вычисляем здесь 3-точечный WMA с весом 70%, который присваивается последнему значению, 20% — значению непосредственно перед ним и 10% — значению перед вторым. Согласно этим данным, вы должны ввести следующую формулу в ячейку E4.
= (0,7 * B4 + 0,2 * B3 + 0,1 * B2) / (0,7 + 0,2 + 0,1)
Первые две ячейки пусты, потому что мы вычисляем трехточечный WMA. Теперь перетащите ячейку в последнюю ячейку, как мы делали раньше при вычислении SMA. Вы получите свой результат.
Читайте: Как рассчитать медианное значение в Excel.
4]Расчет экспоненциальной скользящей средней (EMA) с использованием формулы
Чтобы вычислить EMA, у нас должно быть первое значение EMA, которое мы получаем, вычисляя SMA и весовой множитель или константу сглаживания (K). Основная формула для расчета EMA выглядит следующим образом:
EMA = Последнее значение или сегодняшнее значение * K + Вчерашнее значение EMA * (1 — K)
1]Возьмем ту же таблицу, в которой мы рассчитали SMA. В этом примере мы взяли K = 0,3. Первое значение EMA равно первому значению SMA. Здесь мы рассчитываем значения 4-дневной SMA и EMA.
2]Теперь примените следующую формулу к ячейке F6.
= B6 * $ G $ 1 + F5 * (1- $ G $ 1)
Обратите внимание, что мы заблокировали ячейку G в приведенной выше формуле, потому что нам нужно одно и то же значение K для всех вычислений EMA.
3]Теперь перетащите ячейку, чтобы скопировать формулу в оставшиеся ячейки.
Вот и все, мы рассчитали все три типа скользящих средних в MS Excel. Надеемся, вам понравилась статья.
Вам также может понравиться: Как рассчитать средний балл или средний балл в Excel.
.