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

Рис. 7.10. Границы размаха колебаний

Задание. Постройте границы колебаний. Сравните полученный график с зарисовкой.

Добавляем колебания к линии тренда — в новой колонке таблицы. Наносим линию на график. Настраиваем тип и цвет линии (рис. 7.11). Колебания вокруг линии тренда должны иметь постоянную амплитуду. Сравниваем с зарисовкой.

Рис. 7.11. Сумма тренда и колебаний

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

Смоделируем случайную составляющую с помощью генератора случайных чисел. Вначале построим столбец случайных чисел со стандартным нормальным распределением (нулевое среднее и единичная дисперсия). Озаглавим столбец буквой N.

Вызываем надстройку «Анализ данных»:

Data — Analysis — Data Analysis — Random Number Generation.

Настраиваем генератор случайных чисел (рис. 7.12).

Число случайных переменных (столбцов в таблице):

Number of Variables — 1

Количество случайных чисел (для моментов времени от 0 до 50):

Number of Random Numbers — 51

Выбираем стандартное нормальное распределение:

Distribution — Normal

Parameters:

Mean = 0

Standard deviation = 1

Начальное состояние генератора (любые четыре цифры):

Random Seed — 1234

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

Output options:

Output Range

Рис. 7.12. Настройки генератора

Задание. Сгенерируйте столбец случайных чисел со стандартным нормальным распределением.

Чтобы получить случайную составляющую с нужной сигмой, умножим случайные числа на наш коэффициент d. Результаты разместим в колонке E. Строим график случайной составляющей — на отдельном графике (рис. 7.13). Общая форма графика должна соответствовать нашей зарисовке — размах приблизительно плюс-минус три сигмы.

Рис. 7.13. Случайная составляющая

Задание. Сформируйте столбец значений случайной составляющей. Постройте график. Сравните с зарисовкой.

Для начала оценим размах случайного разброса в модели.

Мы уже оценивали границы случайного разброса по правилу трёх сигм. Сигма определяется коэффициентом d в задании.

Построим два вспомогательных столбца:

T + S — 3d

T + S +3d

Наносим линии на график. Настраиваем цвет и тип линий (рис. 7.14). Сравниваем с зарисовкой.

Рис. 7.14. Границы случайного разброса

Задание. Сформируйте столбцы для оценки границ случайного разброса. Нанесите линии на график. Сравните с зарисовкой.

Наконец мы складываем все три компонента и размещаем их в новой колонке y. Наносим наш ряд динамики на график. Настраиваем цвет и тип линий (рис. 7.15). Внешний вид должен соответствовать зарисовке.

Рис. 7.15. Сумма трёх компонентов

Задание. Сложите три компонента и нанесите линию на график. Сравните результат с зарисовкой.

Поздравляем! Ряд динамики, сгенерированный по аддитивной модели, готов. Как видим, практически все значения после наложения случайного разброса находятся внутри наших границ «плюс-минус три сигмы».

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

Мультипликативную модель разместим на новом листе.

Размещаем на этом листе параметры модели и уравнения модели.

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

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

Задание. Рассчитайте значения для линии тренда. Постройте график и сравните с зарисовкой.

Сезонная составляющая в данном случае — колебания вокруг единичного уровня. Период колебаний равен 12. Строим отдельный график.

Задание. Рассчитайте значения сезонных колебаний. Постройте график. Сравните с зарисовкой.

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

Задание. Рассчитайте значения случайно составляющей. Постройте график. Сравните с зарисовкой.

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

Нас интересуют следующие графики:

— размах сезонных колебаний вокруг линии тренда;

— произведение тренда и колебаний;

— размах случайного разброса вокруг произведения тренда и колебаний;

— произведение тренда, сезонности и случайности.

Все они в целом должны соответствовать нашим предварительным зарисовкам.

Рис. 7.16. Мультипликативная модель

Задание. Сделайте необходимые расчёты и постройте графики размаха сезонности вокруг тренда, произведения T * S, границ случайного разброса вокруг T * S и окончательного графика ряда динамики. Сравните с зарисовками.

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

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

Такой метод обработки данных также называют «механическим сглаживанием».

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

Ещё одна аналогия — это обработка поверхности доски с помощью рубанка. Короткий рубанок — для коротких досок. Длинный рубанок — для длинных досок.

Задание. Дайте определению выражению СКОЛЬЗЯЩАЯ СРЕДНЯЯ.

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