Сглаживание с помощью скользящей средней. Метод скользящей средней в Microsoft Excel

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

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

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

Если все значения существенны, но сегодняшнее 12-е значение наиболее значимо, а предыдущие 11-е, 10-е, 9-е и т.д. имеют все меньшую и меньшую значимость, следует найти взвешенное среднее всех 12 значений. Причем весовые коэффициенты для последних значений должны быть больше, чем для предыдущих, и сумма всех весовых коэффициентов должна равняться 1.

Первый способ называется «наивным» прогнозом и достаточно очевиден. Рассмотрим подробнее остальные способы.

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

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

Рассмотрим следующий пример. Маркетологу нужно спрогнозировать спрос на производимые его компанией станки. Данные по объемам продаж за последний год работы компании находятся в файле «ЛР6.Пример 1.Станки.xls».

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

Менеджер вычислил объем продаж на основе простого скользящего среднего за 3 и 4 месяца. Однако требуется определить, какое количество узлов даёт более точный прогноз. Для оценки точности прогнозов используются среднее абсолютных отклонений (САО) исреднее относительных ошибок , в процентах (СООП), вычисляемые по формулам (3) и (4).

где x i –i -ое реальное значение переменной вi -й момент времени, аx ’ i –i -ое спрогнозированное значение переменной вi -й момент времени, N - количество прогнозов.

Согласно результатам, полученным на листе «Простое ск. среднее» рабочей книги «ЛР6.Пример 1.Станки.xls» (см. Рисунок 56), скользящее среднее за три месяца имеет значение САО равное 12,67 (ячейка D16 ), тогда как для скользящего среднего за 4 месяца значение САО равно 15,59 (ячейка F16 ). Тогда можно выдвинуть гипотезу, что использование большего количества статистических данных скорее ухудшает, чем улучшает точность прогноза методом скользящего среднего.

Рисунок 56. Пример 1 – результаты прогнозирования методом простого скользящего среднего

На графике (см. Рисунок 57), построенном по результатам наблюдений и прогнозов с интервалом 3 месяца, можно заметить ряд особенностей, общих для всех применений метода скользящего среднего.

Рисунок 57. Пример 1 – график кривой прогноза методом простого скользящего среднего и график реального объёма продаж

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

Основной недостаток метода простого скользящего среднего возникает в результате того, что при вычислении прогнозируемого значения самое последнее наблюдение имеет такой же вес (т. е. значимость), как и предыдущие. Это происходит потому, что вес всех N последних наблюдений, участвующих в вычислении скользящего среднего, равен 1/N. Присвоение равного веса противоречит интуитивному представлению о том, что во многих случаях последние данные могут больше сказать о том, что произойдет в ближайшем будущем, чем предыдущие.

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

где - вес, с которым используется показательпри расчете.

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

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

Выберите команду Сервис -> Поиск решения.

В диалоговом окне Поиска решения установите ячейку G16 целевой (см. лист «Веса»), минимизируя её.

Изменяемыми ячейками укажите диапазон В1:В3.

Установите ограничения В4 = 1,0; В1:ВЗ ≥ 0; В1:В3 ≤ 1; B1 ≤ В2 и В2 ≤ В3.

Запустите поиск решения (результат отображает).

Рисунок 58. Пример 1 – результат поиска весов значений показателей при использовании метода взвешенного скользящего среднего

Полученные результаты показывают, что оптимальное распределение весов таково, что весь вес сосредоточен на самом последнем наблюдении, при этом значение среднего абсолютных отклонений равно 7,56 (см. также Рисунок 59). Этот результат подтверждает предположение о том, что более поздние наблюдения должны иметь больший вес.

Рисунок 59. Пример 1 – график кривой прогноза методом взвешенного скользящего среднего и график реального объёма продаж

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

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

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

1. Определяют длину интервала сглаживания g, включающего в себя g последовательных уровней ряда (g

2. Разбивают весь период наблюдений на участки, при этом интервал сглаживания как бы скользит по ряду с шагом, равным 1.

3. Рассчитывают арифметические средние из уровней ряда, образующих каждый участок.

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

При этом удобно брать длину интервала сглаживания g в виде нечетного числа: g=2p+1, т.к. в этом случае полученные значения скользящей средней приходятся на средний член интервала.

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

При нечетном значении g все уровни активного участка могут быть представлены в виде: yt-p, yt-p+1, ... , yt-1, yt, yt+1, ... , yt+p-1, yt+p,

а скользящая средняя определена по формуле:

Процедура сглаживания приводит к полному устранению периодических колебаний во временном ряду, если длина интервала сглаживания берется равной или кратной циклу, периоду колебаний.

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

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

При использовании скользящей средней с длиной активного участка g=2p+1 первые и последние p уровней ряда сгладить нельзя, их значения теряются. Очевидно, что потеря значений последних точек является существенным недостатком, т.к. для исследователя последние "свежие" данные обладают наибольшей информационной ценностью. Рассмотрим один из приемов, позволяющих восстановить потерянные значения временного ряда . Для этого необходимо:

1.Вычислить средний прирост на последнем активном участке yt-p, yt-p+1, ... , yt, ... , yt+p-1, yt+p

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

Аналогичную процедуру можно реализовать для оценивания первых уровней временного ряда.

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

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

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

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

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

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

Y i = a j + a 1 t

Y i = a o + a 1 t + a 2 t 2 +… a p t p

Параметры полинома находятся по методу наименьших квадратов.

При этом начало отсчета переносится в середину участка сглаживания, например, если длина интервалов сглаживания = 5, то индексы уровней участка сглаживания будут равны: -2, -1, 0, 1, 2.

у	t	t	t
у1	-2
у2	-1
у3
у4
у5
	t=0

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

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

Метод наименьших квадратов в этой ситуации дает следующую систему уравнений:

Для нахождения параметра а0 используют 1 и 3 уравнение

-

34-=5*34а0-10*10а0

34-=а0(170-100)

а0=

Если длина интервала сглаживания равна 7, весовые коэффициенты следующие:

Отметим важные свойства приведенных весов:

1) Они симметричны относительно центрального уровня.

2) Сумма весов с учетом общего множителя, вынесенного за скобки, равна единице.

3) Наличие как положительных, так и отрицательных весов, позволяет сглаженной кривой сохранять различные изгибы кривой тренда.

Существуют приемы, позволяющие с помощью дополнительных вычислений получить сглаженные значения для Р начальных и конечных уровней ряда при длине интервала сглаживания g=2p+1.

Весовые коэффициенты при сглаживании по полиномам второго и третьего порядка

Тема 5: Методы измерения и изучения устойчивости временного ряда.

o устойчивость уровней ряда;

o устойчивость тренда.

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

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

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

Сокращение колебаний уровней ряда – одна из главных задач при повышении устойчивости.

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

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

1) размах колеблемости - определяется как разница средних уровней за благоприятные и неблагоприятные по отношению к изучаемому явлению периоды времени:

R=y благопр – унеблагопр

К благоприятным периодам времени относятся все периоды с уровнями выше тренда, а к неблагоприятным – ниже тренда.

3)среднее линейное отклонение:

1) среднее квадратическое отклонение:

S(t)=

Уменьшение колеблемости во времени будет равнозначно устойчивости уровней.

Для характеристики устойчивости рекомендуются также следующие показатели:

1) процентный размах (PR):

Wmax/min – max/min относительный прирост.

W=

2) Скользящая средняя (МА) оценивает величину среднего отклонения от уровня скользящих средних (хt):

3) Среднее процентное изменение (АРС) оценивает среднее значение абсолютных величин, относительных приростов и квадратов относительных приростов:

АРС=

Для оценки устойчивости уровней временных рядов применяются относительные показатели колеблемости:

K=100 – V(t) – коэффициент устойчивости (в процентах или долях единиц).

Для измерения устойчивости тенденции динамики (тренда) используют следующие показатели:

1) коэффициент корреляции рангов (коэффициент Спирмена):

d - разность рангов уровней изучаемого ряда и рангов номеров периодов или моментов времени.

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

Коэффициент Спирмена может принимать значения в пределах от 0 до ±1. Если каждый уровень исследуемого периода выше, чем предыдущего, то ранги уровней ряда и номера лет совпадают – Кр=+1. Это означает полную устойчивость самого факта роста уровней ряда, то есть непрерывность роста. Чем ближе Кр к +1, тем ближе рост уровней к непрерывному, то есть выше устойчивости роста. Если Кр=0, рост совершенно неустойчив.

При отрицательных значениях чем ближе Кр к -1, тем устойчивее уменьшение изучаемого показателя.

I=

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

Число уровней ряда у двух показателей должно быть одинаково.

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

1. Показатель Каякиной определяется как отношение среднего прироста линейного тренда, т.е. параметра а1 к среднему квадратическому отклонению уровней от тренда:

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

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

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

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

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

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

Аналитический подход;

Алгоритмический подход.

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

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

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

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

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

1. Определяют длину интервала сглаживания g, включающего в себя g последовательных уровней ряда (g

2. Разбивают весь период наблюдений на участки, при этом интервал сглаживания как бы скользит по ряду с шагом, равным 1.

3. Рассчитывают арифметические средние из уровней ряда, образующих каждый участок.

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

При этом удобно брать длину интервала сглаживания g в виде нечетного числа g=2p+1, т.к. в этом случае полученные значения скользящей средней приходятся на средний член интервала.

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

При нечетном значении g все уровни активного участка могут быть представлены в виде:

а скользящая средняя определяется по формуле

,

где − фактические значение -го уровня;

− значение скользящей средней в момент ;

− длина интервала сглаживания.

Процедура сглаживания приводит к полному устранению периодических колебаний во временном ряду, если длина интервала сглаживания берется равной или кратной периоду колебаний.

Для устранения сезонных колебаний желательно использовать четырех- и двенадцатичленную скользящую среднюю.

При четном числе уровней принято первое и последнее наблюдение на активном участке брать с половинными весами:

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

,

.

Рассмотрим применение скользящей средней по данным общей площади жилых помещений, приходящихся в среднем на 1 жителя по Хабаровскому краю (таблица 2.1.1).

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

.

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

Для 1994 г. скользящая средняя составит

,

для 1995 г. , и т.д.

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

При расчете по четным скользящим средним (в нашем примере 4-членная скользящая средняя) вычисления производятся следующим образом:

Для 1994 г. ;

1995 г. ;

1996 г. .

Таблица 2.1.1 – Результаты сглаживания по методу скользящих средних

Годы	Общая пло-щадь жилых помещений, приходящаяся в среднем на 1 жителя.кв.м,	Сглаженные уровни
Простая скользящая средняя
3-член-ная,	4-член-ная,	5-член-ная,	3-член-ная	4-член-ная	5-член-ная
	15,4	-	-	-	-	-	-
	16,1	16,0	-	-	0,01	-	-
	16,5	16,4	16,3	16,3	0,01	0,026	0,040
	16,6	16,7	16,6	16,6	0,004	0,001	0,000
	16,9	16,8	16,8	16,8	0,004	0,006	0,006
	17,0	17,0	17,1	17,1		0,003	0,010
	17,1	17,3	17,4	17,4	0,05	0,083	0,102
	17,9	17,7	17,7	17,7	0,03	0,026	0,026
	18,2	18,2	18,2	18,2	0,00	0,000	0,000
	18,5	18,7	18,7	18,7	0,03	0,031	0,032
	19,3	19,1	19.1	19,0	0,04	0,056	0,068
	19,5	19,5	19,4	19,4		0,006	0,014
	19,7	19,7	-	-		-	-
	19,9	-	-	-	-	-	-
Итого	248,6	-	-	-	0,179	0,239	0,299

Как видно из таблицы 2.1.1, трехчленная скользящая средняя демонстрирует выравненный динамический ряд с однонаправленной тенденцией движения уровней. Сглаживание по трехчленной скользящей средней дало более сглаженный ряд, так как для трехчленной скользящей средней оказалась меньше сумма квадратов отклонений фактических данных () от сглаженных () ( = 0,179) (таблица 2.1.1). Иными словами, трехчленная скользящая средняя лучше всего представляет закономерность движения уровней динамического ряда.

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

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

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

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

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

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

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



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



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

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

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

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

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

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

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



4. Программа выводит результат обработки.



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

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

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

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

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



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

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

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

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

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

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



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



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

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

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

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



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



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



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



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



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



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



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



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



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



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



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



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



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



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



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



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

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

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



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



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



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



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

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

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

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

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

где у, - фактическое значение /-го уровня; m - число уровней, входящих в интервал сглаживания - текущий уровень ряда динамики; i - порядковый номер уровня в интервале сглаживания; р - при нечетном m имеет значение р = (m - 1)/2.

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

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

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

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

1 . Сопоставление значения текущей цены со скользящим средним, используемым в этом случае как индикатор тенденции. Так, если цены находятся выше 65-дневного скользящего среднего, то на рынке имеется промежуточная (краткосрочная) восходящая тенденция. В случае более долгосрочной тенденции цены должны быть выше 40-недельного скользящего среднего.

2 . Использование скользящего среднего как уровня поддержки или сопротивления. Закрытие цен выше данного скользящего среднего служит "бычьим" сигналом, закрытие ниже его - "медвежьим".

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

4 . Наблюдение за направлением наклона кривой скользящего среднего. Так, если после длительного подъема она выравнивается или поворачивает вниз, это может быть "медвежьим" сигналом.

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

Microsoft Excel располагает функцией Скользящее среднее (Moving Average), которая обычно используется для сглаживания уровней эмпирического временного ряда на основе метода простого скользящего среднего. Для вызова этой функции необходимо выбрать команду меню Tools^Data Analysis (Сервис1*Анализ данных). На экране раскроется окно Data Analysis, в котором следует выбрать значение Moving Average. В результате на экран будет выведено диалоговое окно Moving Average, представленное на рис. 11.1.

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

1. Input Range (Входные данные) - в это поле вводится диапазон ячеек, содержащих значения исследуемого параметра.

2. Labels in First Row (Метки в первой строке) - данный флажок опции устанавливается в том случае, если первая строка/столбец входного диапазона содержит заголовок. Если заголовок отсутствует, флажок следует сбросить. В этом случае для данных выходного диапазона будут автоматически созданы стандартные названия.

3. Interval (Интервал) - в это поле вводится число уровней m, входящих в интервал сглаживания. По умолчанию v = 3.

4. Output options (Параметры вывода) - в этой группе, помимо указания диапазона ячеек для выходных данных в поле Output Range (Выходной диапазон), можно также потребовать автоматически построить график, для чего нужно установить флажок опции Chart Output (Вывод графика), и рассчитать стандартные погрешности, для чего необходимо установить флажок опции Standart Errors (Стандартные погрешности).

Рассмотрим конкретный пример. Допустим, за указанный период (1999-2002 гг.) необходимо выявить основную тенденцию изменения фактического объема выпуска продукции и характер сезонных колебаний этого показателя. Данные для примера представлены на рис. 11.2. На рис. 11.3 отображены вычисленные с помощью функции Moving Average (Скользящее среднее) значения сглаженных уровней и значения m=3.