Скользящее среднее и экспоненциальное сглаживание в MS Excel

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

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

Режим работы «Экспоненциальное сглаживание» служит для сглаживания уровней эмпирического динамического ряда на основе метода простого экспоненциального сглаживания.

В диалоговых окнах данных режимов (рисунок 2 и 3) задаются следующие параметры:

1. Входной интервал – вводится ссылка на ячейки, содержащие исходные данные.

2. Флажок Метки – устанавливается активное состояние, если первая строка (столбец) во входном диапазоне содержит заголовки. Если заголовки отсутствуют, флажок следует деактивизировать. В этом случае будут автоматически созданы стандартные названия для данных выходного диапазона.

3. Интервал (только в диалоговом окне Скользящее среднее) – вводится размер окна сглаживания р. По умолчанию р=3.

Рисунок 2 – Диалоговое окно скользящего среднего

4. Фактор затухания (только в диалоговом окне Экспоненциальное сглаживание) – вводится значение коэффициента экспоненциального сглаживания p. По умолчанию, p=0,3.

5. Выходной интервал / Новый рабочий лист / Новая рабочая книга – в положении Выходной интервал активизируется поле, в которое необходимо ввести ссылку на левую верхнюю ячейку выходного диапазона. Размер выходного диапазона будет определен автоматически, и на экране появится сообщение в случае возможного наложения выходного диапазона на исходные данные. В положении Новый рабочий лист открывается новый лист, в который начиная с ячейки А1 вставляются результаты анализа. Если необходимо задать имя в поле, расположенное напротив соответствующего положения переключателя. В положении Новая рабочая книга открывается новая книга, на первом листе которой начиная с ячейки А1 вставляются результаты анализа.

6. Вывод графика – устанавливается в активное состояние для автоматической генерации на рабочем листе графиков фактических и теоретических уровней динамического ряда.

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

Рисунок 3 – Диалоговое окно экспоненциального сглаживания

Пример 1.

Данные о реализации (млн. руб.) продуктов сельскохозяйственного производства магазинами потребительской кооперации города приведены в таблице, сформированной на рабочем листе Microsoft Excel (рисунок 4). В указанном периоде (2009 – 2012 гг.) требуется выявить основную тенденцию развития данного экономического процесса.

Рисунок 4 – Исходные данные

Для решения задачи используем режим работы «Скользящее среднее». Значения параметров, установленных в одноименном диалоговом окне, представлены на рисунке 5, рассчитанные в данном режиме показатели – на рисунке 6, а построенные графики – на рисунке 7.

Рисунок 5 – Заполнение диалогового окна

Рисунок 6 – Результаты анализа

Рисунок 7– Скользящее среднее

В столбце D (рисунок 5) вычисляются значения сглаженных уровней. Например, значение первого сглаженного уровня рассчитывается в ячейке D5 по формуле =СРЗНАЧ(С2:С5), значение второго сглаженного уровня – в ячейке D6 по формуле =СРЗНАЧ(С5:С8) и т.д.

В столбце E вычисляются значения стандартных погрешностей с помощью формулы =КОРЕНЬ (СУММАКВРАЗН (блок фактических значений; блок прогнозных значений) / размер окна сглаживания).

Например, значение в ячейке Е10 рассчитывается по формуле =КОРЕНЬ(СУММКВРАЗН(С7:С10;О7:В10)/4).

Вместе с тем, как отмечалось выше, если размер окна сглаживания является четным числом (р=2m), то рассчитанное усредненное значение нельзя сопоставить какому-либо определенному моменту времени t, поэтому необходимо применять процедуру центрирования.

Для рассматриваемого примера р=4, поэтому процедура центрирования необходима. Так, первый сглаженный уровень (265,25) записывается между II и III кв. 2009 г. и т.д. Применяя процедуру центрирования (для этого используем функцию СРЗНАЧ), получаем сглаженные уровни с центрированием. Для III кВ. 2009 г. определяется серединное значение между первым и вторым сглаженными уровнями: (265,25 + 283,25)/2 = 274,25; для IV кв. 2009 г. центрируются второй и третий сглаженные уровни: (283,25 + 292,00)/2 = 287,6 и т.д. Рассчитанные значения представлены в таблице 1. Скорректированный график скользящей средней представлен на рисунке 8.

Таблица 1 – Динамика сглаженных уровней реализации продукции

Рисунок 8 – Скорректированный график скользящего среднего

Пример 2.

Рассмотренная задача может быть решена и с помощью метода простого экспоненциального сглаживания. Для этого необходимо использовать режим работы «Экспоненциальное сглаживание». Значения параметров, установленных в одноименном диалоговом окне, представлены на рисунке 9, рассчитанные в данном режиме показатели – рисунок 10, а построенные графики – на рисунке 11.

Рисунок 9 – Заполнение диалогового окна «Экспоненциальное сглаживание»

Рисунок 10 – Результаты анализа

Рисунок 11 – Экспоненциальное сглаживание

В столбце D (рисунок 10) вычисляются значения сглаженных уровней на основе рекуррентных соотношений.

В столбце E рассчитываются значения стандартных погрешностей с помощью формулы =КОРЕНЬ(СУММКВРАЗН (блок фактических значений; блок прогнозных значений) / 3). Как легко заметить (сравните рисунок 8 и 11), при использовании метода простого экспоненциального сглаживания, в отличие от метода простой скользящей средней, сохраняются мелкие волны.