Моделирование временного ряда

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

ПРИМЕР. Имеются данные о ценах на съемное жилье в курортной зоне за восемь сезонов.

Простроить модель временного ряда.

Часть 1. Выявление структуры ряда

Построим график зависимости цены на жилье от квартала. Для этого вводим в А1 подпись «Квартал», в ячейки А2 и А3 числа «1» и «2», затем обводим мышкой А2 и А3, выделяя их и зацепив мышью за маркер автозаполнения, опускаем его до А33. В В1 вводим подпись «Цена», а в столбец В2-В33 вводим значения строки «Цена» из таблицы данных. Переходим на «Лист 2», нажимаем на кнопку «Мастер диаграмм» и выбираем категорию «График» и тип «График с маркерами…», второй сверху левый. Нажимаем «Далее», в поле «Диапазон» обводим на листе 1 ячейки «B2-B33», переходим на закладку «Ряд», обводим в поле «Подписи оси Х» ячейки листа 1 «А2-А33». Нажимаем «Готово». Видно, что график имеет явно выраженные линейную трендовую составляющую и циклическую компоненту. Однако, для более полного анализа ряда, построим коррелограмму. Для этого в А1 (Лист 2) вводим подпись «Лаг», и в А2 и в А3 вводим «1» и «2». Обводим, выделяя, А2-А3 и автозаполнением переводим данные на А2-А9. Результат – последовательность чисел от 1 до 8. Далее ставим курсор в В1, вводим «Корреляция», переносим курсор в В2, вызываем формулу «=ПИРСОН» (вычисляет парные коэффициент корреляции, категория «Статистические»). Аргументом «Массив 1» будет служить ссылка на данные «Цена» кроме последнего значения (ссылка на В2-В32, лист 1). Аргумент «Массив 2» – эти же данные, но без первого аргумента (ссылка на В3-В33). Далее аналогично находим коэффициенты автокорреляции, но со сдвижкой (лагом) на 2, 3, …, 8 значений. Заполняем ячейки В3-В9 в соответствии с таблицей:

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

Часть 2. Модель тенденции и циклической

компоненты ряда

Перейдем на лист 1. Все дальнейшие вычисления будут проводиться на этом листе. Проводим в третьем столбце сглаживание данных скользящей средней. Для этого в ячейку С1 вводим подпись «Сглаживание» а в С2 вводим формулу «=(B2+B3+B4+B5)/4» и автозаполняем ячейку на С2-С30. Посчитаем теперь в 4 столбце центрированное скользящее среднее, вводим в D1 подпись «Центрированное», а в D4 вводим формулу «=(C2+C3)/2». Автозаполняем область D4-D31. Вычисляем теперь оценку сезонной компоненты S. Для этого в пятый столбец вводим разность между показателем (столбец 2) и сглаженным значением (столбец 4). Вводим в Е1 подпись «Оценка S», а в Е4 вводим формулу «=B4-D4» и автозаполняем ее на Е4-Е31. Для моделирования циклической компоненты S. Для этого выводим оценку сезонной компоненты по кварталам года. Вводим в А35 подпись «Квартал», в ячейки В35-Е35 числа 1,2,3 и 4, а в ячейки В36-Е43 ссылки в соответствии с таблицей:

Вводим в А43 подпись «Среднее» и в В43 функцию «СРЗНАЧ», в поле аргумента «Число 1» дать ссылку на В36-В42. Автозаполняем данные на В43-Е43. Вводим в F42 подпись «Сумма», а в F43 формулу «=СУММ(B43:E43)». Видно, что сумма среднего сезонного воздействия отличается от нуля, однако, суммарное воздействие циклической компоненты должно быть нулевым. Для расчета циклической компоненты, рассчитаем ее поправку, которую отнимем от полученных средних данных. Для этого в G42 вводим подпись «Поправка» и в G43 формулу «=F43/4», в А44 вводим «S=», а в В44 вводим «=B43-$G$43», автозаполняем на В44-E44. Получили циклическую компоненту за 4 квартала: 2,759; -8,831; -7,258; 13,33. Вводим эти числа в ячейки F2-F5, введя ссылки: в F2 ссылка на «=В44» после ввода ссылки нажимаем F4, получаем «=$B$44», аналогично, в F3 дается ссылка на «=$С$44», в F4 ссылка на «=$D$44», в F5 ссылка на «=$E$44». Обведем четыре введенные ячейки F2-F5 курсором, и, автозаполнением, скопируем эти четыре ячейки на F2-F33. При этом в ячейку F1 вводим подпись «Циклическое S». Исключим теперь Циклическую компоненту из временного ряда. Для этого вводим в G1 подпись «Т+Е=у-S», а в G2 формулу «=B2-F2» и автозаполняем ячейку на G2-G33. Вычисляем теперь трендовую компоненту (тенденцию временного ряда). Введем в столбец Н трендовую компоненту Т в виде линейной функции , для этого в Н1 вводим подпись «Тренд Т», а в Н2 вводим функцию «ТЕНДЕНЦИЯ» категория «Статистические», аргументы которой «Изв_знач_у» – ссылка на В2-В33, «Изв_знач_х» - ссылка на А2-А33, «Нов_знач_х» - вновь ссылка на А2-А33, «Константа» – единица. Далее выделяем обводя курсором ячейки Н2-Н33, и нажимаем F2 и Ctrl+Shift+Enter. В следующем столбце I будет находиться модель временного ряда, состоящая из суммы циклической компоненты S и тренда Т. Вводим в I1 заголовок «Модель ряда», а в I2 вводим формулу «=H2+F2». Автозаполняем результат на I2-I33. Получим график значений временного ряда, тренда и его модели. Для этого ставим курсор в любую свободную ячейку, вызываем мастер диаграмм и выбираем тип «График» и первый верхний график слева, нажимаем «Далее». В поле «Диапазон» вводим ссылку на В2-В33, обводя их, затем удерживая Ctrl обводим еще области G2-G33 и I2-I33. График синего цвета – временной ряд практически совпадает со своей моделью - желтая линия. В центре линия красного цвета – трендовая составляющая ряда. Получим теперь случайную составляющую временного ряда – остатки Е. Для этого в J1 вводим подпись «Остатки Е», а в J2 формулу «=B2-I2». Автозаполняем на J3-J33. По полученным данным можно построить график остатков. Обводим, выделяя, ячейки J2-J33, вызываем мастер диаграмм, выбираем тип «Точечная», верхний график, нажимаем «Готово. График остатков говорит о случайном их расположении. Для проверки качества модели, рассчитаем остаточную сумму квадратов остатков и остаточную дисперсию (дисперсию адекватности). Для этого в К1 вводим подпись «Е^2», а в К2 вводим формулу «=J2*J2». Автозаполнением переносим формулу на К2-К33. Вычисляем оценку дисперсии адекватности. Вводим в J35 подпись «Dа=», а в К35 формулу «=СУММ(K2:K33)/32». Результат 1,768711. Вычисляем теперь оценку полную дисперсии показателя. Вводим в J36 подпись «Dy», а в К36 вводим функцию ДИСПР (категория «Статистические»), аргументом которой «Число 1» является ссылка на значения признака B2:B33. Видно, что оценка дисперсия адекватности намного меньше оценки полной дисперсии , которая равна 860,7937, что говорит о хорошем качестве модели. Оценка парного коэффициента корреляции вычисляется по формуле . Для его получения вводим в J37 подпись «Корреляция», а в К37 формулу «=КОРЕНЬ(1-K35/K36)». Результат близок к единице, что еще раз подтверждает хорошее качество модели.

Задание на самостоятельную работу

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

Лабораторная работа № 10