8.2. Надстройка
Построим скользящие средние с помощью надстройки «Анализ данных». Вызываем в верхнем меню
Data Analysis Data Analysis Moving Average
Настраиваем параметры сглаживания (рис. 8.7).
Диапазон ячеек исходного ряда динамики:
Input Input Range
Интервал сглаживания, который мы уже подобрали в предыдущем разделе:
Interval 4
Адрес ячейки, начиная с которой будут выводиться сглаженные значения:
Output Options Output Range
В заголовке нового столбца указываем, что здесь находится, например:
MA 4
Это означает «скользящее среднее с периодом 4 отсчёта по времени» (4 periods Moving Average).
Рис. 8.7. Параметры сглаживания
Задание. Вызовите надстройку «Анализ данных» и постройте две скользящие средние с выбранными ранее параметрами: 1) для удаления случайности и 2) для удаления случайности и сезонности. Укажите период сглаживания в заголовках столбцов.
Рассмотрим результаты сглаживания (рис. 8.8). Мы выбрали в этом примере скользящую среднюю по 4 точкам (периодам или моментам времени). После вызова надстройки в ячейках таблицы появились не только значения. Можно щёлкнуть по любой ячейке и обнаружить формулы.
Например, значение в ячейке G7 определяется по формуле
AVERAGE (E4:E7)
Рис. 8.8. Результаты сглаживания
Другими словами, первое сглаженное значение это средняя арифметическая из первых четырёх уровней исходного ряда (рис. 8.9).
Рис. 8.9. Формулы сглаживания
Получается, что значение скользящей средней привязано к концу интервала сглаживания. Например, среднее на интервале из первых четырёх значений (t=03) приписано к моменту t=3. То же самое происходит и при сглаживании встроенными средствами графика. Кстати, чуть позже мы увидим, как этот подход работает в биржевой торговле.
Задание. Рассмотрите результаты сглаживания для обеих скользящих средних. Обратите внимание на формулы в ячейках. Подсчитайте, сколько значений пропадает после сглаживания. Как это связано с периодом сглаживания?
Наносим сглаженный ряд на наш график (рис. 8.10). Поскольку первые три значения пропадают, мы указываем ряд значений покороче время меняется от 3 до 50. Можно видеть, что результаты встроенного сглаживания графика и сглаживания через надстройку совпадают. Теперь становится понятно, по каким формулам это было сделано.
Рис. 8.10. Сравнение средних
Задание. Нанесите обе скользящие средние на соответствующие графики. Сравните результаты.
8.3. Функции
Готовые функции Excel добавляют нам немного гибкости при сглаживании.
Как мы уже видели в предыдущем разделе, для сглаживания можно использовать функцию нахождения среднего значения:
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. Такой строки у нас пока нет, но мы её легко можем сформировать: