Валентин Арьков - Анализ рядов динамики в электронных таблицах

AVERAGE (range_y)

СРЗНАЧ (интервал_y)

Это средняя арифметическая простая.

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

Рассмотрим пример MA 3 (рис. 8.11). В этом случае пропадают первые и последние значения сглаженного ряда.

Рис. 8.11. Сглаживание с привязкой по центру

Вводим функцию AVERAGE (E4:E6)в ячейку для момента времени t=1. Интервал адресов E4:E6включает три ячейки (рис. 8.12). Копируем выражение по всему столбцу двойным щелчком по маркеру автозаполнения. Затем вручную удаляем последнюю ячейку, ведь у нас недостаточно данных для вычисления последнего сглаженного значения при t=50.

Рис. 8.12. Сглаживание с помощью функции

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

Наносим сглаженный ряд на график. Естественно, мы выбираем моменты времени t = 1 … 49. Помним, что первое и последнее значения сглаженного ряда отсутствуют.

Для сравнения включаем встроенное сглаживание с таким же периодом в 3 точки по времени (рис. 8.13). Можно видеть, что привязка к середине интервала сглаживания располагает скользящую среднюю без смещения.

Рис. 8.13. Сравнение скользящих средних

Задание. Постройте графики. Сравните результаты сглаживания.

Теперь сравним результаты сглаживания с нашей исходной моделью. С помощью скользящей средней мы отфильтровали случайную составляющую. Должны были остаться только тренд и сезонные колебания. Наложим на наш график линию суммы T + S. Отключим встроенное сглаживание. Графики выглядят очень похоже (рис. 8.14). Значит, нам действительно удалось отфильтровать случайность. Конечно, результат фильтрации не совсем совпадает с исходным рядом T+S. Но в целом периодические изменения просматриваются очень хорошо.

Рис. 8.14. Фильтрация случайности

Задание. Наложите графики Y, T+S и MA. Убедитесь, что случайность отфильтрована.

Переходим ко второму графику. Проводим фильтрацию с периодом сглаживания 12, чтобы оставить только тренд. У нас пропадает 5 значений в начале ряда (рис. 8.15). Середина интервала от 0 до 11 должна соответствовать моменту времени t = 5,5. То есть ровно между 5 и 6. Такой строки у нас пока нет, но мы её легко можем сформировать:

начальное значение =5,5

шаг = 1

конечное значение = 44,5

Рис. 8.15. Первое сглаженное значение

В конце ряда пропадает 6 значений (рис. 8.16). Дальше просто недостаточно данных для нахождения среднего.

Рис. 8.16. Последнее сглаженное значение

Задание. Проведите сглаживание с периодом 12. Сформируйте вспомогательный столбец времени.

Сравним результаты сглаживания встроенными средствами графика и с помощью надстройки. Нанесём сглаженный ряд на график (рис. 8.17). Можно видеть, что сдвиг встроенной скользящей средней достаточно заметный. А вот наша линия, построенная «вручную», проходит «посередине».

Рис. 8.17. Сравнение результатов сглаживания

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

Фильтрация с периодом сглаживания 12 должна оставить только тренд. Сравним результаты сглаживания с исходной линией тренда. Нанесём на график линию тренда, исходный ряд динамики и сглаженный ряд (рис. 8.18). Результаты близки. Конечно, сезонные колебания и случайность даже после фильтрации немного исказили прямую линию. Но в целом всё очень хорошо.

Рис. 8.18. Отфильтрованный тренд

Задание. Постройте график исходного ряда, тренда и скользящей средней. Сравните результаты фильтрации с линией тренда.

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

MEDIAN (range)

МЕДИАНА (диапазон)

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

Рис. 8.19. Фильтрация случайности

Задание. Отфильтруйте случайную составляющую с помощью скользящей медианы. Постройте график и сравните со скользящей средней и исходными рядами T+S+E и T+S.

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