Главная » Excel » Вы здесь

Метод скользящей средней в Microsoft Excel

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

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

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

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

Способ 1: Пакет анализа

Пакет анализа представляет собой надстройку Excel, которая по умолчанию отключена. Поэтому, прежде всего, требуется её включить.

  1. Перемещаемся во вкладку «Файл». Делаем щелчок по пункту «Параметры».
  2. Переход в параметры в Microsoft Excel

  3. В запустившемся окне параметров следует перейти в раздел «Надстройки». В нижней части окна в поле «Управление» должен быть выставлен параметр «Надстройки Excel». Щелкаем по кнопке «Перейти».
  4. Переход в надстройки в Microsoft Excel

  5. Мы попадаем в окно надстроек. Устанавливаем галочку около пункта «Пакет анализа» и щелкаем по кнопке «OK».

Окно надстроек в Microsoft Excel

После этого действия пакет «Анализ данных» активирован, и соответствующая кнопка появилась на ленте во вкладке «Данные».

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

  1. Переходим во вкладку «Данные» и жмем на кнопку «Анализ данных», которая размещена на ленте инструментов в блоке «Анализ».
  2. Переход к инструментам Анализа данных в Microsoft Excel

  3. Открывается перечень инструментов, которые доступны в Пакете анализа. Выбираем из них наименование «Скользящее среднее» и жмем на кнопку «OK».
  4. Список инструментов Пакета анализа в Microsoft Excel

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

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

    В поле «Интервал» следует указать интервал обработки значений методом сглаживания. Для начала давайте установим значение сглаживания в три месяца, а поэтому вписываем цифру «3».

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

    Также следует установить галочку около параметра «Стандартные погрешности».

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

    После того, как все настройки внесены, жмем на кнопку «OK».

  6. Окно инструмента Анализа данных Скользящее среднее в Microsoft Excel

  7. Программа выводит результат обработки.
  8. Результат обработки сглаживания за 3 месяца в Microsoft Excel

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

    В поле «Входной интервал» оставляем те же значения, что и в предыдущем случае.

    В поле «Интервал» ставим цифру «2».

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

    Остальные настройки оставляем прежними. После этого жмем на кнопку «OK».

  10. Окно инструмента Анализа данных Скользящее среднее в программе Microsoft Excel

  11. Вслед за этим программа производит расчет и выводит результат на экран. Для того, чтобы определить, какая из двух моделей более точная, нам нужно сравнить стандартные погрешности. Чем меньше данный показатель, тем выше вероятность точности полученного результата. Как видим, по всем значениям стандартная погрешность при расчете двухмесячной скользящей меньше, чем аналогичный показатель за 3 месяца. Таким образом, прогнозируемым значением на декабрь можно считать величину, рассчитанную методом скольжения за последний период. В нашем случае это значение 990,4 тыс. рублей.

Результат обработки сглаживания за 2 месяца в Microsoft Excel

Способ 2: использование функции СРЗНАЧ

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

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

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

  1. Выделяем ячейку в пустой колонке в строке за март. Далее жмем на значок «Вставить функцию», который размещен вблизи строки формул.
  2. Переход в Мастер функций в Microsoft Excel

  3. Активируется окно Мастера функций. В категории «Статистические» ищем значение «СРЗНАЧ», выделяем его и щелкаем по кнопке «OK».
  4. Переход к аргументам функции СРЗНАЧ в Microsoft Excel

  5. Запускается окно аргументов оператора СРЗНАЧ. Синтаксис у него следующий:

    =СРЗНАЧ(число1;число2;…)

    Обязательным является только один аргумент.

    В нашем случае, в поле «Число1» мы должны указать ссылку на диапазон, где указан доход за два предыдущих периода (январь и февраль). Устанавливаем курсор в поле и выделяем соответствующие ячейки на листе в столбце «Доход». После этого жмем на кнопку «OK».

  6. аргументы функции СРЗНАЧ в Microsoft Excel

  7. Как видим, результат расчета среднего значения за два предыдущих периода отобразился в ячейке. Для того, чтобы выполнить подобные вычисления для всех остальных месяцев периода, нам нужно скопировать данную формулу в другие ячейки. Для этого становимся курсором в нижний правый угол ячейки, содержащей функцию. Курсор преобразуется в маркер заполнения, который имеет вид крестика. Зажимаем левую кнопку мыши и протягиваем его вниз до самого конца столбца.
  8. Маркер заполнения в Microsoft Excel

  9. Получаем расчет результатов среднего значения за два предыдущих месяца до конца года.
  10. Среднее значение за 2 предыдущих месяца в Microsoft Excel

  11. Теперь выделяем ячейку в следующем пустом столбце в строке за апрель. Вызываем окно аргументов функции СРЗНАЧ тем же способом, который был описан ранее. В поле «Число1» вписываем координаты ячеек в столбце «Доход» с января по март. Затем жмем на кнопку «OK».
  12. Аргументы функции СРЗНАЧ для 3 месяцев в Microsoft Excel

  13. С помощью маркера заполнения копируем формулу в ячейки таблицы, расположенные ниже.
  14. Применение маркера заполнения в Microsoft Excel

  15. Итак, значения мы подсчитали. Теперь, как и в предыдущий раз, нам нужно будет выяснить, какой вид анализа более качественный: со сглаживанием в 2 или в 3 месяца. Для этого следует рассчитать среднее квадратичное отклонение и некоторые другие показатели. Для начала рассчитаем абсолютное отклонение, воспользовавшись стандартной функцией Excel ABS, которая вместо положительных или отрицательных чисел возвращает их модуль. Данное значение будет равно разности между реальным показателем выручки за выбранный месяц и прогнозируемым. Устанавливаем курсор в следующий пустой столбец в строку за май. Вызываем Мастер функций.
  16. Вставить функцию в Microsoft Excel

  17. В категории «Математические» выделяем наименование функции «ABS». Жмем на кнопку «OK».
  18. Переход к аргументам функции ABS в Microsoft Excel

  19. Запускается окно аргументов функции ABS. В единственном поле «Число» указываем разность между содержимым ячеек в столбцах «Доход» и «2 месяца» за май. Затем жмем на кнопку «OK».
  20. Аргументы функции ABS в Microsoft Excel

  21. С помощью маркера заполнений копируем данную формулу во все строки таблицы по ноябрь включительно.
  22. Абсолютные отклонения в Microsoft Excel

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

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

  27. Далее рассчитываем среднее значение всех данных абсолютного отклонения с помощью функции СРЗНАЧ.
  28. Среднее значение абсолютного отклонения за 3 месяца в Microsoft Excel

  29. Следующим шагом является подсчет относительного отклонения. Оно равно отношению абсолютного отклонения к фактическому показателю. Для того чтобы избежать отрицательных значений, мы опять воспользуемся теми возможностями, которые предлагает оператор ABS. На этот раз с помощью данной функции делим значение абсолютного отклонения при использовании метода скользящей средней за 2 месяца на фактический доход за выбранный месяц.
  30. Относительное отклонение в Microsoft Excel

  31. Но относительное отклонение принято отображать в процентном виде. Поэтому выделяем соответствующий диапазон на листе, переходим во вкладку «Главная», где в блоке инструментов «Число» в специальном поле форматирования выставляем процентный формат. После этого результат подсчета относительного отклонения отображается в процентах.
  32. Изменение формата в Microsoft Excel

  33. Аналогичную операцию по подсчету относительного отклонения проделываем и с данными с применением сглаживания за 3 месяца. Только в этом случае для расчета в качестве делимого используем другой столбец таблицы, который у нас имеет название «Абс. откл (3м)». Затем переводим числовые значения в процентный вид.
  34. Относительное отклонение для линии скольжения в 2 месяца в Microsoft Excel

  35. После этого высчитываем средние значения для обеих колонок с относительным отклонением, как и ранее используя для этого функцию СРЗНАЧ. Так как для расчета в качестве аргументов функции мы берем процентные величины, то дополнительную конвертацию производить не нужно. Оператор на выходе выдает результат уже в процентном формате.
  36. Средние значения для относительного отклонения в Microsoft Excel

  37. Теперь мы подошли к расчету среднего квадратичного отклонения. Этот показатель позволит нам непосредственно сравнить качество расчета при использовании сглаживания за два и за три месяца. В нашем случае среднее квадратичное отклонение будет равно корню квадратному из суммы квадратов разностей фактической выручки и скользящей средней, деленной на количество месяцев. Для того, чтобы произвести расчет в программе, нам предстоит воспользоваться целым рядом функций, в частности КОРЕНЬ, СУММКВРАЗН и СЧЁТ. Например, для расчета среднего квадратичного отклонения при использовании линии сглаживания за два месяца в мае будет в нашем случае применяться формула следующего вида:

    =КОРЕНЬ(СУММКВРАЗН(B6:B12;C6:C12)/СЧЁТ(B6:B12))

    Копируем её в другие ячейки столбца с расчетом среднего квадратичного отклонения посредством маркера заполнения.

  38. Расчет среднего квадратичного отклонения в Microsoft Excel

  39. Аналогичную операцию по расчету среднего квадратичного отклонения выполняем и для скользящей средней за 3 месяца.
  40. Расчет среднего квадратичного отклонения по скользящей средней за 3 месяца в Microsoft Excel

  41. После этого рассчитываем среднее значение за весь период для обоих этих показателей, применив функцию СРЗНАЧ.
  42. Среднее значение среднего квадратичного отклонения в Microsoft Excel

  43. Произведя сравнение расчетов методом скользящей средней со сглаживанием в 2 и 3 месяца по таким показателям, как абсолютное отклонение, относительное отклонение и среднеквадратичное отклонение, можно с уверенностью сказать, что сглаживание за два месяца дает более достоверные результаты, чем применение сглаживания за три месяца. Об этом говорит то, что вышеуказанные показатели по двухмесячному скользящему среднему, меньше, чем по трехмесячному.
  44. Сопоставление показателей в Microsoft Excel

  45. Таким образом, прогнозируемый показатель дохода предприятия за декабрь составит 990,4 тыс. рублей. Как видим, это значение полностью совпадает с тем, которое мы получили, производя расчет с помощью инструментов Пакета анализа.

Прогнозируемый показатель дохода в Microsoft Excel

Урок: Мастер функций в Экселе

Мы произвели расчет прогноза при помощи метода скользящей средней двумя способами. Как видим, данную процедуру намного проще выполнить с помощью инструментов Пакета анализа. Тем не менее некоторые пользователи не всегда доверяют автоматическому расчету и предпочитают для вычислений использовать функцию СРЗНАЧ и сопутствующие операторы для проверки наиболее достоверного варианта. Хотя, если все сделано правильно, на выходе результат расчетов должен получиться полностью одинаковым.

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

Помогла ли вам эта статья?

ДА НЕТ


Еще статьи по данной теме:


Поделиться статьей в социальных сетях:
1 комментарий

при копировании формулы =КОРЕНЬ(СУММКВРАЗН(B6:B12;C6:C12)/СЧЁТ(B6:B12)) буду смещаться диапазоны и в расчет браться пустые ячейки. Разве это верно?

Задайте вопрос или оставьте свое мнение

Примечание: если отправленный комментарий не появился на странице, значит он ушел на проверку и скоро будет опубликован. Пожалуйста, не дублируйте вопрос. Мы ответим Вам в ближайшее время.