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

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

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

В этой статье я опишу три популярнейших способа реализации Moving Average: простое скользящее среднее, экспоненциальное и взвешенное

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

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

  1. В Excel 2013 кликните в любом месте диаграммы и затем нажмите иконку с символом плюс (+) рядом с диаграммой, чтобы открыть меню Элементы диаграммы (Chart elements). Другой вариант: нажмите кнопку Добавить элемент диаграммы (Add Chart Elements), которая находится в разделе Макеты диаграмм (Chart Layouts) на вкладке Конструктор (Design).
  2. Отметьте галочкой параметр Линия тренда (Trendline).
  3. Чтобы настроить тип линии тренда, кликните направленную вправо стрелку и выберите один из предложенных вариантов (линейная, экспоненциальная, линейный прогноз, скользящее среднее и т.д.).

Добавляем линию тренда на диаграмму Excel

Чаще всего используются обычный линейный тренд и линия скользящего среднего. Линейный тренд – это прямая линия, расположенная таким образом, чтобы расстояние от неё до любой из точек графика было минимальным. Эта линия полезна в том случае, если есть уверенность, что последующие данные будут соответствовать тому же шаблону.

Очень полезна линия скользящего среднего по нескольким точкам. Такая линия, в отличие от линейного тренда, показывает усреднённую тенденцию по заданному числу точек на графике, которое можно изменить. Линию скользящего среднего используют, если формула, предоставляющая данные для построения графика, изменяется со временем, и тренд должен быть построен только по нескольким предшествующим точкам. Чтобы построить такую линию, выполните шаги 1 и 2 из описанных выше, а затем сделайте вот что:

  1. Кликните направленную вправо стрелку в строке Линия тренда (Trendline) и выберите вариант Скользящее среднее (Moving average).
  2. Проделайте шаги 1 и 2 из предыдущего примера ещё раз и нажмите Дополнительные параметры (More options).Добавляем линию тренда на диаграмму Excel
  3. В открывшейся панели Формат линии тренда (Format Trendline) убедитесь, что флажком отмечен вариант Линейная фильтрация (Moving Average).Добавляем линию тренда на диаграмму Excel
  4. Справа от параметра Линейная фильтрация (Moving Average) находится поле Точки (Period). Здесь задаётся количество точек, которое нужно использовать для вычисления средних значений для построения линии тренда. Установите такое количество точек, которое, по Вашему мнению, будет оптимальным. Например, если Вы считаете, что определённая тенденция в данных сохраняется неизменной только для последних 4 точек, то введите число 4 в данном поле.

Линии тренда в Excel – это отличный способ получить больше информации об имеющемся наборе данных и о том, как они изменяются со временем. Линейный тренд и скользящее среднее – два типа линий тренда, наиболее распространённых и полезных для бизнеса.

Недостатки метода

  1. Первые и последние уровни ряда теряются (не сглаживаются).
  2. Метод применим лишь для рядов, имеющих линейную тенденцию.

Пример . Произвести сглаживание ряда динамики трехквартальной скользящей средней.
Решение.

t y ys Формула
1 1065
2 851 815.67 (1065 + 851 + 531)/3
3 531 768 (851 + 531 + 922)/3
4 922 849.33 (531 + 922 + 1095)/3
5 1095 1001 (922 + 1095 + 986)/3
6 986 967.67 (1095 + 986 + 822)/3
7 822 981.67 (986 + 822 + 1137)/3
8 1137 1086.67 (822 + 1137 + 1301)/3
9 1301 1158.67 (1137 + 1301 + 1038)/3
10 1038 1039.67 (1301 + 1038 + 780)/3
11 780 1084.33 (1038 + 780 + 1435)/3
12 1435 1269.33 (780 + 1435 + 1593)/3
13 1593 1562 (1435 + 1593 + 1658)/3
14 1658 1538 (1593 + 1658 + 1363)/3
15 1363 1586 (1658 + 1363 + 1737)/3
16 1737 1606.33 (1363 + 1737 + 1719)/3
17 1719 1659 (1737 + 1719 + 1521)/3
18 1521 1429.67 (1719 + 1521 + 1049)/3
19 1049 1453.33 (1521 + 1049 + 1790)/3
20 1790 1618.33 (1049 + 1790 + 2016)/3
21 2016

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

t y ys Формула
1994 800
1995 864 878 (800 + 864 + 970)/3
1996 970 946.67 (864 + 970 + 1006)/3
1997 1006 1003.67 (970 + 1006 + 1035)/3
1998 1035 1071.67 (1006 + 1035 + 1174)/3
1999 1174 1165.33 (1035 + 1174 + 1287)/3
2000 1287 1267.33 (1174 + 1287 + 1341)/3
2001 1341 1367.67 (1287 + 1341 + 1475)/3
2002 1475 1451.67 (1341 + 1475 + 1539)/3
2003 1539 1575.33 (1475 + 1539 + 1712)/3
2004 1712

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

Как рассчитать дисперсию в Excel?

Дисперсия — квадрат среднеквадратического отклонения и отражает разброс данных относительно среднего.

Рассчитаем дисперсию: дисперсия

дисперсия расчет в Excel

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

Точных вам прогнозов!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast - прогноз в Excel - точно, легко и быстро!

  • Novo Forecast Lite — автоматический расчет прогноза в Excel .
  • 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

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

Как рассчитать прогноз по методу экспоненциального сглаживания в Excel?

Формула расчета прогноза проста:

  • Ŷt+1 – прогноз на следующий период t+1;
  • Yt – данные для прогноза за текущий период t (например, продажи по месяцам);
  • k – коэффициент сглаживания ряда , k задается вами вручную и находится в диапазоне от 0 до 1, 0 < k < 1
  • Ŷ t – значение прогноза на текущий период t. Причем в первый период (месяц, день…) Ŷ1=Y1, т.е. Ŷt в первый период равны продажам в этот период.

Прогноз по методу экспоненциального сглаживания = коэффициент сглаживания * последнее фактическое значение продаж + (1- коэффициент сглаживания)*предыдущий прогноз по методу экспоненциального сглаживания.

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

Задачи о генеральной доле

На вопрос «Накрывает ли доверительный интервал заданное значение p?» — можно ответить, проверив статистическую гипотезу H:p=p. При этом предполагается, что опыты проводятся по схеме испытаний Бернулли (независимы, вероятность p появления события А постоянна). По выборке объема n определяют относительную частоту p * появления события A: где m — количество появлений события А в серии из n испытаний. Для проверки гипотезы H используется статистика, имеющая при достаточно большом объеме выборки стандартное нормальное распределение (табл. 1).
Таблица 1 – Гипотезы о генеральной доле

Пример №1 . С помощью случайного повторного отбора руководство фирмы провело выборочный опрос 900 своих служащих. Среди опрошенных оказалось 270 женщин. Постройте доверительный интервал , с вероятностью 0.95 накрывающий истинную долю женщин во всем коллективе фирмы.
Решение. По условию выборочная доля женщин составляет (относительная частота женщин среди всех опрошенных). Так как отбор является повторным, и объем выборки велик (n=900) предельная ошибка выборки определяется по формуле
(относительная частота женщин среди всех опрошенных). Так как отбор является повторным, и объем выборки велик (n=900) предельная ошибка выборки определяется по формуле

Значение uкр находим по таблице функции Лапласа из соотношения 2Ф(uкр)=γ, т.е. Функция Лапласа (приложение 1) принимает значение 0.475 при uкр=1.96. Следовательно, предельная ошибка Функция Лапласа (приложение 1) принимает значение 0.475 при uкр=1.96. Следовательно, предельная ошибка и искомый доверительный интервал
(p – ε, p + ε) = (0.3 – 0.18; 0.3 + 0.18) = (0.12; 0.48)
Итак, с вероятностью 0.95 можно гарантировать, что доля женщин во всем коллективе фирмы находится в интервале от 0.12 до 0.48.

Пример №2 . Владелец автостоянки считает день «удачным», если автостоянка заполнена более, чем на 80 %. В течение года было проведено 40 проверок автостоянки, из которых 24 оказались «удачными». С вероятностью 0.98 найдите доверительный интервал для оценки истинной доли «удачных» дней в течение года.
Решение. Выборочная доля «удачных» дней составляет
По таблице функции Лапласа найдем значение uкр при заданной
доверительной вероятности
По таблице функции Лапласа найдем значение uкр при заданной
доверительной вероятности
Ф(2.23) = 0.49, uкр = 2.33.
Считая отбор бесповторным (т.е. две проверки в один день не проводилось), найдем предельную ошибку:
где n=40 , N = 365 (дней). Отсюда
где n=40 , N = 365 (дней). Отсюда
и доверительный интервал для генеральной доли: (p – ε, p + ε) = (0.6 – 0.17; 0.6 + 0.17) = (0.43; 0.77)
С вероятностью 0.98 можно ожидать, что доля «удачных» дней в течение года находится в интервале от 0.43 до 0.77.

Читайте также:  Функция СУММПРОИЗВ для вычисления произведения суммы в Excel

Пример №3 . Проверив 2500 изделий в партии, обнаружили, что 400 изделий высшего сорта, а n–m – нет. Сколько надо проверить изделий, чтобы с уверенностью 95% определить долю высшего сорта с точностью до 0.01 ?
Решение ищем по формуле определения численности выборки для повторного отбора.

Ф(t) = γ/2 = 0.95/2 = 0.475 и этому значению по таблице Лапласа соответствует t=1.96
Выборочная доля w = 0.16; ошибка выборки ε = 0.01

Пример №4 . Партия изделий принимается, если вероятность того, что изделие окажется соответствующим стандарту, составляет не менее 0.97. Среди случайно отобранных 200 изделий проверяемой партии оказалось 193 соответствующих стандарту. Можно ли на уровне значимости α=0,02 принять партию?
Решение. Сформулируем основную и альтернативную гипотезы.
H:p=p=0,97 — неизвестная генеральная доля p равна заданному значению p=0,97. Применительно к условию — вероятность того, что деталь из проверяемой партии окажется соответствующей стандарту, равна 0.97; т.е. партию изделий можно принять.
H1:p<0,97 – вероятность того, что деталь из проверяемой партии окажется соответствующей стандарту, меньше 0.97; т.е. партию изделий нельзя принять. При такой альтернативной гипотезе критическая область будет левосторонней.
Наблюдаемое значение статистики K (таблица) вычислим при заданных значениях p=0,97, n=200, m=193

Критическое значение находим по таблице функции Лапласа из равенства

По условию α=0,02 отсюда Ф(Ккр)=0,48 и Ккр=2,05. Критическая область левосторонняя, т.е. является интервалом (-∞;-Kkp)= (-∞;-2,05). Наблюдаемое значение Кнабл=-0,415 не принадлежит критической области, следовательно, на данном уровне значимости нет оснований отклонять основную гипотезу. Партию изделий принять можно.

Пример №5 . Два завода изготавливают однотипные детали. Для оценки их качества сделаны выборки из продукции этих заводов и получены следующие результаты. Среди 200 отобранных изделий первого завода оказалось 20 бракованных, среди 300 изделий второго завода — 15 бракованных.
На уровне значимости 0.025 выяснить, имеется ли существенное различие в качестве изготавливаемых этими заводами деталей.
Решение. Это задача о сравнении генеральных долей двух совокупностей. Сформулируем основную и альтернативную гипотезы.
H:p1=p2 — генеральные доли равны. Применительно к условию — вероятность появления бракованного изделия в продукции первого завода равна вероятности появления бракованного изделия в продукции второго завода (качество продукции одинаково).
H:p1≠p2 — заводы изготавливают детали разного качества.
Для вычисления наблюдаемого значения статистики K (таблица) рассчитаем оценки по выборке.

Наблюдаемое значение равно

Так как альтернативная гипотеза двусторонняя, то критическое значение статистики K≈ N(0,1) находим по таблице функции Лапласа из равенства
Так как альтернативная гипотеза двусторонняя, то критическое значение статистики K≈ N(0,1) находим по таблице функции Лапласа из равенства
По условию α=0,025 отсюда Ф(Ккр)=0,4875 и Ккр=2,24. При двусторонней альтернативе область допустимых значений имеет вид (-2,24;2,24). Наблюдаемое значение Kнабл=2,15 попадает в этот интервал, т.е. на данном уровне значимости нет оснований отвергать основную гипотезу. Заводы изготавливают изделия одинакового качества.

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

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

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

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

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

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


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

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

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

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

Способ 1. С помощью Мастера функций

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

  1. Левой клавишей манипулятора на рабочем листе программы выделить ячейку, в которой будет отображаться результат вычисления.
  2. Кликнуть по кнопке «Вставка функции», расположенной с левой стороны от строки ввода.
  1. После выполнения предыдущего действия откроется окошко Мастера функций. Здесь пользователю потребуется найти строку «СТАНДОТКЛОН.В», выделить ее ЛКМ и нажать на «ОК» внизу меню. В списке функций также есть записи «СТАНДОТКЛОН.Г» и просто «СТАНДОТКЛОН». На них нажимать не нужно!
  1. В следующем окне аргументов функции необходимо прописать числа совокупности в каждом поле. Если эти значения уже написаны на рабочем листе Excel, то можно указать координаты соответствующих ячеек.
  2. Нажать на «ОК» внизу меню «Аргументы функции», когда все строчки будут заполнены.
  1. Проверить результат. В выделенной на первом этапе ячейке будет прописано число. Это и есть среднее квадратичное отклонение.

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

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

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

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

Данный метод в Excel применяется через использование функции пакета анализа и непосредственно через саму встроенную функцию, которая получила название «СРЗНАЧ».

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

1. Пакета анализа в стандартном наборе функций нет, поэтому его необходимо включить. Делается это через параметры документа – «Файл» — «Параметры» — «Надстройки». Внизу диалогового окна есть вкладка «Надстройки». Именно она нам и нужна.

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

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

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

2. Чтобы понять, каким образом работает метод скользящей средней, попробуем получить данные за 12 месяц на основе тех, которые мы уже получили за 11 прошлых – сделаем прогноз. Заполняем исходные значения таблицы.

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

3. В ранее добавленном функционале «Анализ данных» на рабочей панели с параметров надстроек документа, выбираем искомую «Скользящую среднюю» функцию и нажимаем «Ок».

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

4. В появившемся диалоговом окне заполним все значения. «Входной интервал» — все наши показатели за 11 месяцев без искомой ячейки. «Интервал» — показатель сглаживания, касаемо наших исходных данных, установим «3». «Выходной интервал» — ячейки, куда будут выводиться полученные данные методом скользящей средней. Включаем «Стандартные погрешности» и получаем все искомые значения.

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

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

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

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

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

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


Рассмотрим второй способ — функцию СРЗНАЧ:

1. Если пакет анализа делает практически все операции автоматизированными, то использование функции СРЗНАЧ требует применения нескольких стандартных функций Excel. Используем те же исходные данные по 11 месяцам. Вставим функцию.

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

2. В диалоговом окне Мастера функций перейдем во вкладку «Статистические» и выберем нашу искомую функцию «СРЗНАЧ».

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

3. Функция «СРЗНАЧ» имеет очень простой синтаксис – «=СРЗНАЧ(число1;число2;число3;. ). Укажем в аргументе «число 1» диапазон за «Январь» и «Февраль».

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

4. Рассчитаем показатель для оставшихся периодов времени путем протягивания маркера заполнения формулы по столбцу вниз.

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

5. Проведем эту же операцию, но с разницей в период за 3 месяца.

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

6. Но какие данные в нашем случае верны, на основе двух месяцев или трех? Для получения правильного ответа применим расчет абсолютного отклонения, среднего квадратического и еще пары других показателей. За абсолютное отклонение отвечает функция «ABS».

В диалоговом окне функции указываем разность между доходом и скользящей средней за два месяца.

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

7. Маркером заполнения заполним столбец и рассчитаем «СРЗАНЧ» за все время.

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

8. Проведем аналогичную операцию для поиска абсолютного отклонения и среднего значения за период в три месяца.

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

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

Все данные представим в процентах.

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

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

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

Пропишем нашу функцию «КОРЕНЬ(СУММКВРАЗН(B6:B12;C6:C12)/СЧЁТ(B6:B12))», заполним столбцы маркерами заполнения и найдем среднее значение по полученным данным.

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

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

Среднеквадратичное отклонение – что это

Стандартное (или среднеквадратичное) отклонение – это квадратный корень из дисперсии. В свою очередь, под последним термином подразумевается степень разброса значений. Для получения дисперсии, и, как следствие, ее производного в виде стандартного отклонения, существует специальная формула, которая, впрочем, нам не так важна. Она довольно сложная по своей структуре, но при этом ее можно полностью автоматизировать средствами Excel. Главное – знать, какие параметры нужно передавать функции. В целом как для вычисления дисперсии, так и стандартного отклонения, аргументы используются одинаковые.

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

Получив разницу между одним значением и средним арифметическим всей выборки, мы можем узнать расстояние к нему от определенной точки на координатной прямой. Начинающему человеку вся логика понятна равно до третьего шага. Зачем возводить значение в квадрат? Дело в том, что иногда разница может быть отрицательной, а нам нужно получить положительное число. И, как известно, минус на минус дает плюс. А далее нам нужно определить среднее арифметическое из получившихся значений. Дисперсия имеет несколько свойств:

  1. Если выводить дисперсию из одного числа, то она всегда будет равняться нулю.
  2. Если случайное число умножить на константу А, то дисперсия увеличится в количество раз, равное А в квадрате. Проще говоря, константу можно вынести за знак дисперсии и возвести его во вторую степень.
  3. Если к произвольному числу добавить константу А или же отнять ее, то дисперсия от этого не поменяется.
  4. Если два случайных числа, обозначаемых, к примеру переменными X и Y не зависят друг от друга, то в таком случае для них справедлива формула. D(X+Y) = D(X) + D(Y)
  5. Если же в предыдущую формулу внести изменения и пытаться определить дисперсию разницы этих значений, то она также будет составлять сумму этих дисперсий.

Среднеквадратическое отклонение – это математический термин, являющийся производным от дисперси. Получить его очень просто: достаточно извлечь квадратный корень из дисперсии.

Разница между дисперсией и стандартным отклонением находится сугубо в плоскости единиц измерения, если можно так выразиться. Стандартное отклонение является значительно более простым для считывания показателем, поскольку оно показывается не в квадратах числа, а непосредственно в значениях. Простыми словами, если в числовой последовательности 1,2,3,4,5 средним арифметическим является 3, то соответственно, стандартным отклонением будет число 1,58. Это говорит о том, что в среднем одно число отклоняется от среднего числа (которым является тройка в нашем примере), на 1,58.

Дисперсия же будет тем же самым числом, только возведенным в квадрат. В нашем примере – чуть меньше, чем 2,5. В принципе, можно использовать как дисперсию, так и стандартное отклонение для статистических расчетов, только надо четко знать, с каким именно показателем пользователь работает.

Рассмотрим ВПР в подробностях.

По ходу статьи мы:

  1. Рассчитаем коэффициенты сезонности к 3-м месяцам по товарной группе;
  2. Рассчитаем скользящую среднюю к 3-м месяцам по позициям;
  3. Скорректируем скользящую среднюю сезонностью по группе. Коэффициенты сезонности подтянем с помощью ВПР и разберем функция по частям.

1. Рассчитаем коэффициенты сезонности к 3-м месяцам по товарной группе;

Рассчитаем коэффициенты сезонности к 3-м месяцам по товарной группе 1 и 2 с помощью Forecast4AC PRO (Как самостоятельно рассчитать коэффициенты сезонности к 3-м месяцам можете прочитать в статье «Расчет прогноза по методу скользящей средней!»)

Для этого установим курсор в начало продаж по товарным группам:

впр +в excel

Выберите в настройках «Сезонность» «к 3-м месяцам»:

функция впр

Нажимаем кнопку «Рассчитать». Получаем в продолжении ряда коэффициенты сезонности к 3-м месяцам:

функция впр +в excel

Копируем сезонность на отдельный лист «к 3-м» получаем табличку, в которой в первом столбце названия товарных групп, а в столбцах со 2-го по 13-й — коэффициенты сезонности для 1 — 12 месяцев:

впр пример

2. Рассчитаем скользящую среднюю к 3-м месяцам по позициям.

Используем стандартную функцию =срзнач(продажи за 3 последних месяца):

впр +в excel пример

Протянем среднюю на все позиции на 24 месяца вперед:

функция впр примеры

3. Скорректируем скользящую среднюю сезонностью по группе и разберем ВПР.

Теперь средние продажи умножим на коэффициент сезонности по товарной группе, который подтянем с помощью функции ВПР.

В ВПР передаем (искомое значение (название товарной группы); таблицу, в которой ищем искомое значение; номер столбца, из которого возвращаем коэффициент сезонности для соответствующего месяца; и интервальный просмотр (ставим «0» — т.к. нам важно точно совпадения названия товарной группы))

1. В искомое значение передаем название товарной группы и фиксируем столбец:

=СРЗНАЧ(BD3:BF3)*впр($C3 ( передаем название товарной группы и фиксируем столбец с помощью значка «$» );’к 3-м’!$A$3:$M$4;данные!BG$2+1;0)

Подробнее о фиксировании ссылок читайте в статье «Как зафиксировать ссылку в Excel».

впр формула

2. В таблицу передаем таблицу с коэффициентами сезонности для товарных групп и фиксируем таблицу:

=СРЗНАЧ(BD3:BF3)*впр($C3;’к 3-м’!$A$3:$M$4( передаем таблицу с товарными группами и фиксируем таблицу с помощью значка «$» );данные!BG$2+1;0)

функция впр +в excel примеры

В первом столбце таблицы содержатся искомые значения — названия товарных групп. Фиксируем таблицу, чтобы формула имела такой вид ‘к 3-м’!$A$3:$M$4 и ссылки не поехали, когда мы будем протягивать формулу.

3. Далее в ВПР передаем номер столбца, в котором содержится искомый коэффициент сезонности соответствующего месяца сезонности в прогнозе

=СРЗНАЧ(BD3:BF3)*впр($C3;’к 3-м’!$A$3:$M$4;данные!BG$2+1( передаем номер столбца в котором содержится искомый коэффициент сезонности для соответствующего месяца и фиксируем строку с номерами столбцов месяца с помощью значка «$» );0)

эксель впр

впр +в экселе

Т.к. номер столбца в таблице с сезонностью для первого месяца будет вторым, то прибавляем «1»

=СРЗНАЧ(BD3:BF3)*впр( $ C3;’к 3-м’!$A$3:$M$4;данные!BG$2 +1 ( прибавляем 1, т.к. номер столбца в таблице с сезонностью для первого месяца 2, в первом столбце название товарных групп );0)

формула впр +в excel

4. =СРЗНАЧ(BD3:BF3)*впр($C3;’к 3-м’!$A$3:$M$4;данные!BG$2+1;0 ( ищем точное соответствие названий товарных групп ))

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

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

Данный подход может значительно увеличить точность расчета прогноза по товарным позициям внутри группы. Попробуйте рассчитать прогноз по методу скользящей средней к 2-м и 4-м месяцам, используя функцию ВПР и Forecast4AC PRO на текущий год, и сравните прогнозы с фактическими продажами. Выберите модель, которая была максимально близка к факту.

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

Точных вам прогнозов!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast - прогноз в Excel - точно, легко и быстро!

  • Novo Forecast Lite — автоматический расчет прогноза в Excel .
  • 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

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

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

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

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

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

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

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

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

  1. Рассчитать коэффициенты сезонности ;
  2. Выбрать период для расчета среднего значения;
  3. Рассчитать прогноз , т.е. среднее значение умножить на коэффициент сезонности;
  4. Учесть дополнительные факторы , которые значительно влияют на продажи;

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

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

Для этого рассчитываем коэффициенты сезонности очищенные от роста , как описано в статье «Как рассчитать коэффициенты сезонности, очищенные от роста?» . Затем определяем коэффициенты сезонности к предыдущим периодам , к 1 месяцу, к 2-м месяца, к 3-м месяцам и т.д. в зависимости от того, за какой период берем среднее значение для прогнозирования продаж. Например, рассчитаем месячные коэффициенты сезонности (см. вложенный файл лист «Расчет коэффициентов»)

коэффициент января — отношение январского коэффициента сезонности очищенного от роста к декабрьскому;

февраля — февральского коэффициента к январскому;

марта — март к февралю;

к 2-м месяцам:

для января — отношение январского коэффициента сезонности к среднему значению декабря и ноября

для февраля — февраль делим на среднее значение коэффициентов января и декабря

для марта — март к среднему февральского и январского коэффициентов

к 3-м месяцам:

для определения январского коэффициента сезонности к 3-м месяцам мы январский коэффициент сезонности, очищенный от роста, делим на среднее значение коэффициентов сезонности, очищенных от роста, за декабрь, ноябрь, октябрь;

для февраля — коэффициент февраля делим на среднее значение коэффициентов ноября, декабря и января;

Для марта — отношение марта к среднему значению коэффициентов сезонности очищенных от роста декабря, января и февраля;

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

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

Для этого делаем прогноз для последнего и предпоследнего периодов, данные за который нам известны, тремя или более способами для определения подходящего периода расчета средней (см. вложенный файл лист «Выбор периода»). И смотрим, какой из вариантов делает более точный прогноз:

  1. Рассчитаем прогноз продаж по методу скользящей средней к 1-му месяцу :
Читайте также:  Функция ЧСТРОК возвращает количество строк в диапазоне Excel

Декабрь = объём продаж ноября умножим на декабрьский коэффициент сезонности к предыдущему месяцу.

  1. Рассчитаем прогноз продаж по методу скользящей средней к 2-ум месяцам:

Декабрь = средний объём продаж за октябрь и ноябрь умножим на декабрьский коэффициент сезонности к 2-м месяцам.

  1. Рассчитываем прогноз по методу скользящей средней к 3-ем месяцам:

Декабрь = средний объём продаж за сентябрь, октябрь и ноябрь умножим на декабрьский коэффициент сезонности к 3-м месяцам.

Сейчас мы рассчитали прогноз тремя способами на декабрь. Аналогичным образом рассчитаем на ноябрь.

Теперь сравниваем фактические значения за ноябрь и декабрь с прогнозными рассчитанными 3-мя способами . Мы видим, что в нашем примере наиболее точно прогноз рассчитан по методу скользящей средней к 2-м месяцам , возьмём его за базу. В вашем случае более точный прогноз может оказаться к предыдущему периоду, к 3-м предыдущим или к 4-м предыдущим периодам.

3. Рассчитаем прогноз продаж по методу скользящей средней.

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

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

Следуя данной логике, мы продлеваем расчет прогноза до конца года. Расчет прогноза продаж на год готов.

4. Дополнительные факторы, которые стоит учесть при расчете прогноза продаж.

Для повышения точности прогноза важно:

  1. Из прошлых периодов вычесть факторы , которые значительно повлияли на объем продаж , но в прогнозных месяцах повторяться не будут (акции по стимулированию сбыта, разовая отгрузка крупного нерегулярного клиента, вывод из крупной розничной сети и т.д.).
  2. К прогнозируемым месяцам прибавить факторы , которые значительно повлияют на продажи — начало работы с крупными сетями, проведение крупных акций по стимулированию сбыта, вывод новых товаров, рекламные компании и т.д.

Часть 2

Цель работы : Приобрести навыки прогнозирования экономической деятельности предприятия с применением статистического программного пакета MS Excel.

Краткая теория

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

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

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

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

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

Если для прогноза наиболее значимыми являются последние результаты наблюдений, то используют метод экспоненциального сглаживания. В методе экспоненциального сглаживания каждое значение участвует в формировании прогнозируемых значений с переменным весом, который убывает по мере «устаревания» данных. В инструменте анализа MS Excel « Экспоненциальное сглаживание» весовой коэффициент, или параметр сглаживания, определяется параметром Фактор затухания . Обычно для временных рядов в экономических задачах величину параметра сглаживания задают в интервале от 0,1 до 0,3. Начальное расчетное значение в процедуре Экспоненциальное сглаживание пакета Анализа MS Excel принимается равным уровню первого члена ряда. Метод обеспечивает хорошее согласование исходных и расчетных данных для первых значений ряда. Если конечные вычисленные значения значительно отличаются от соответствующих исходных, то целесообразно изменить величину параметра сглаживания. Оценить величины расхождений можно на основе стандартных погрешностей и графика, которые пакет Анализа позволяет вывести вместе с расчетными значениями ряда.

Рассмотрим возможности прогнозирования показателей деятельности предприятия, занимающихся предоставлением услуг связи.

Задание к лабораторной работе (часть 2)

Задание 1 : Вычислить прогнозируемое значение величины объема продукции (услуг) предприятия методом скользящего среднего.

Порядок выполнения задания:

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

Для вычислений воспользуемся способом прямого введения формулы. Чтобы получить трехлетнее скользящее среднее объема выполненных услуг для нашего примера, введем в ячейку B 5 формулу для вычисления =СРЗНАЧ( A 2: A 4). Скопируем формулу в интервал B 6: B 11.

Рисунок 1 – Вычисление простого скользящего среднего

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

Рисунок 2 – График тенденции изменения показателя объема услуг, полученной методом простого скользящего среднего

Другим способом решения является использование для определения скользящего целого Пакета анализа . Пакет анализа является надстройкой MS Excel (выберите пункт меню Сервис/ Надстройки и установите флажок Пакет анализа ).

  1. Выполнить команду Сервис/Анализ данных и выбрать из списка инструментов анализа Скользящее среднее.
  2. В диалоговом окне укажите параметры для вычисления скользящего среднего:
  • В качестве входного интервала выделите блок ячеек, содержащий данные об объеме услуг.
  • Укажите Интервал- 3 (по умолчанию используется 3), в качестве выходного интервала – любую ячейку рабочего листа (просто щелкните на ячейке рабочего листа, с которой должны выводиться результаты);

Excel сам выполнит работу по внесению значений в формулу для вычислений скользящего среднего. Из-за недостаточного количества данных при вычислении среднего значения для первых результатов наблюдений в начальных ячейках выходного диапазона будет выведено значение ошибки #Н/Д. Учтите, что первое полученное значение ряда является прогнозным не на третий, а на четвертый период. Поэтому, если указанная для вывода ячейка соответствует началу столбца наблюдений, то нужно столбец рассчитанных значений переместить вниз на одну ячейку. Это действие присоединит прогнозы именно к тем периодам, для которых они рассчитаны.

Проанализируйте используемые расчетные формулы и полученные результаты.

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

Задание 2: Вычислить прогнозируемое значение величины объема продукции (услуг) предприятия методом экспоненциального сглаживания.

  1. На листе MS Excel создайте список, содержащий данные о численности сотрудников фирмы за последние 10 лет. Данные введите произвольно, но так, чтобы прослеживалась тенденция.
  2. Проведите сглаживание временного ряда с использованием экспоненциальной средней с параметрами сглаживания 0,1 и затем 0,3. По результатам расчетов постройте график и определите, какой из полученных временных рядов носит более гладкий характер.

Воспользуйтесь командой Сервис/Анализ данных и выберите из списка инструментов анализа Экспоненциальное сглаживание. Укажите параметры для вычисления скользящего среднего:

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

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

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

Таблица данные курса доллара

Переходим в Excel на вкладку Вставка -> Диаграммы -> Точечная -> Точечная с гладкими кривыми и маркерами. Появляется пустая область, нажимаем правой клавишей мыши на эту область и появляется меню, нажимаем Выбрать данные.

Диаграмма – точечная с гладкими кривыми и маркерами Excel
Выбрать данные Excel
Выбор данных Excel
Ввод данных Excel

В итоги получается график.

График Excel
На графике жмем плюсик
плюс на графике - Excel
Выбираем Линия тренда -> Дополнительные параметры Линия тренда дополнительные параметры Excel
Затем выбираем Формат линии тренда – Скользящее среднее с периодом, для примера и наглядности возьмём период 3
форма линии тренда

В итоги получаем график

Скользящее среднее график линии тренда в Excel

Второй способ построения линии тренда скользящего среднего

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

анализ данных вкладка

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

Переходим на вкладку Данные -> Анализ данных -> Скользящее среднее

Анализ данных скользящее среднее

Далее выбираем входной интервал, указываем период интервала 3 и выходной интервал. Ставим галочки вывод графика и стандартные погрешности, жмём Ок.

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

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

Прогноз скользящее среднее Excel

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

Насколько публикация полезна?

Нажмите на звезду, чтобы оценить!

Средняя оценка 0 / 5. Количество оценок: 0

Оценок пока нет. Поставьте оценку первым.

313

Точных вам прогнозов!

Программа Forecast4AC PRO рассчитает прогноз по методу скользящей средней одновременно более чем для 1000 временных рядов одним нажатием клавиши, значительно сэкономив ваше время, одним из 4-х способов:

К среднему за два предыдущих периода

К среднему за три предыдущих периода

К среднему за 4 предыдущих периода

Двойная средняя к 3 и 4 предыдущим периодам

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast - прогноз в Excel - точно, легко и быстро!

  • Novo Forecast Lite — автоматический расчет прогноза в Excel .
  • 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Зарегистрируйтесь и скачайте решения

Статья полезная? Поделитесь с друзьями

Комментарии

Кирилл, добрый день!

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

Ссылка на основную публикацию