Выполнение

Выявление структуры ряда

Построим график зависимости уровня водохранилища от номера замера, соответствующего году. В ячейку A1 введём заголовок Номер и заполним диапазон A2: A33 числами 1, 2, …, 32 В ячейку В1 введём заголовок Уровень и в диапазон В2: B33 введём экспериментальные значения уровня y_i. Перейти на «Лист 2», вызвать Мастер диаграмм и построить график ряда, выполнив следующие действия: на вкладке Вставка в панели Диаграммы выбрать тип создаваемой диаграммы График, вариант графика График с маркерами. Отредактировать полученную диаграмму (рис.1).

Из графика видно, что данные имеют явно выраженную линейную трендовую составляющую и циклическую компоненту. Для более полного анализа ряда построим коррелограмму. Построение коррелограммы будем выполнять на Листе 2. В ячейку A1 Листа 2введём заголовок Лаг, а в ячейку В1 - заголовок Корреляция. Заполним диапазон A2: A9 числами 1, 2, …, 9. В ячейку В2 введём формулу =PEARSON (Функция вычисляет парные коэффициенты корреляции, категория «Статистические»). Аргументы функции: Массив 1: ссылка на данные Уровень, кроме последнего значения (ссылка на В2: B32, лист 1); Массив 2 - эти же данные ног без первого аргумента (ссылка на В3: B33). Далее аналогично находим коэффициенты автокорреляции, но со сдвигом (лагом) на 2, 3, …, 8 значений.

Рис. 1

Заполняем ячейки В3 - B9в соответствии с таблицей:

Построим коррелограмму (рис. 2).

Рис.2

По коэффициенту автокорреляции можно судить о наличии линейной или близкой к линейной тенденции. Если наиболее высоким оказывается коэффициент автокорреляции первого порядка, исследуемый ряд содержит только тенденцию. Если наиболее высоким оказался коэффициент автокорреляции порядка k, исследуемый ряд содержит циклические (сезонные) колебания с периодичностью в k моментов времени.

Если при анализе ряда установлено, что ряд содержит сезонные или циклические колебания, то при моделировании сезонных колебаний используют следующий подход: рассчитывают значения сезонной компоненты методом скользящей средней и строят аддитивную или мультипликативную модель ряда.

Из графика видно, что имеются всплески при четвёртом и восьмом лаге (коэффициенты автокорреляции равны 0, 903 и 0, 875, соответственно).Это говорит о наличии циклической составляющей с периодом 4. Проведём моделирование временного ряда, выделив в нём тренд, циклическую и случайную компоненты: Y = T + S + E.

Построение модели включает в себя следующие пункты:

1) Выравнивание исходного ряда методом скользящей средней.

2) Расчёт значений сезонной компоненты S.

3) Устранение сезонной компоненты из исходных уровней ряда и получение выровненных уровней ряда (T + S).

4) Аналитическое выравнивание уровней (T + S) и расчёт значений T с использованием полученного уравнения тренда.

5) Расчёт полученных по модели значений(T + S).

6) Расчёт абсолютных или относительных ошибок.

Модель тенденции и циклической компоненты ряда

Перейдём на Лист 1. Все дальнейшие вычисления будут проводиться на этом листе. Проведём в третьем столбце (C) сглаживание данных скользящей средней. В ячейку C1 введём заголовок Сглаживание, а в C2 формулу =(B2+B3+B4+B5)/4 и протягиваем её маркером заполнения до ячейки C30. Посчитаем в столбце 4 центрированное скользящее среднее. Введём в D1 заголовок Центрирование, а в D4 формулу =(C2+C3)/2. Протягиваем формулу до ячейки D31. Вычисляем оценку сезонной компоненты S. В пятом столбце вводим разность между показателем (столбец 2) и сглаженным значением (столбец 4). Вводим в E1 заголовок Оценка S, а в E4 вводим формулу =B4-D4 и протягиваем её до E31. Теперь моделируем циклическую компоненту S. Для этого выводим оценку сезонной компоненты. Вводим в A35 заголовок номер, в ячейки B35-E35 числа 1, 2, 3 и 4, а в ячейки B36-E43 ссылки в соответствии с таблицей 2.

Таблица 2

Вводит в A43 текст Среднее и в B43 функцию СРЗНАЧ. В поле аргумента Число 1 дать ссылку на B36: B42. Протянуть формулу на B43: E43. Вводим в F42 заголовок Сумма, а в F43 формулу =СУММ(B43: E43). Видно, что сумма среднего сезонного воздействия отличается от нуля, однако суммарное воздействие циклической компоненты должно быть нулевым.

Для расчёта циклической компоненты рассчитаем её поправку, которую отнимем от полученных средних данных. Для этого в G42 вводим текст Поправка и в G43 формулу = F43/4, в A44 вводим S =, а в B44 вводим = B43-$G$43, протягиваем формулу на B44: E44. Получили циклическую компоненту за 4 года: -0, 0191; -0, 2674; -0, 0567; 0, 3433. Вводим эти числа в ячейки F2: F5, введя ссылки: в F2 ссылка на =$ B$44, в F3 ссылка на =$ C$44, в F4 ссылка на =$ D$44, в F5 ссылка на =$ E$44. Выделить эти четыре ячейки и скопировать формулы на F2: F33. При этом в ячейку F1 введём заголовок Циклическое S. Исключим циклическую компоненту из временного ряда. Вводим в G1 заголовок T+E=y-S, а в G2 формулу =B2-F2, копируем формулу в ячейки G2: G33.

Вычисляем теперь трендовую компоненту (тенденцию временного ряда). Введём в столбец H трендовую компоненту в виде линейной функции , где t – номер замера. Введём в H1 текст Тренд, а в H2: H33 вводим функцию =ТЕНДЕНЦИЯ(B2: B33; A2: A33; A2: A33; 1).

Для получения аналитического выражения для тенденции вводим в диапазон H36: I40 функцию =ЛИНЕЙН(B2: B33; A2: A33; 1; 1). Числа в первой строке таблицы - коэффициенты уравнения линейного тренда: 3, 027 и 8, 171. Уравнение линейного тренда имеет вид: .

В следующем столбце I будет находиться модель временного ряда, состоящая из суммы циклической компоненты S и тренда T. Введём в I1 текст Модель ряда, а в I2 вводим формулу =H2+F2 и протягиваем её до I33. Получим график значений временного ряда, тренда и его модели.

Видно, что уровни ряда (точки) практически лежат на модели ряда. Пунктирная линия – трендовая составляющая ряда.

Получим теперь случайную составляющую временного ряда - остатки Е. В ячейку J1введём текст Остатки, а в J2 формулу =B2-I2 и протягиваем её до J33. По полученным данным можно построить график остатков.

График остатков говорить о случайном их расположении.

Для проверки качества модели рассчитаем остаточную сумму квадратов остатков E ² и остаточную дисперсию (дисперсия адекватности). Для этого в K1 вводим текст E^2, а в K2 вводим формулу =J2*J2 и протягиваем её до K33. Вычисляем оценку дисперcии адекватности. Вводим в J35 текст Da=, а в K35 формулу =СУММ(K2: K33)/32. Результат: 0, 03066. Вычисляем теперь оценку полной дисперсии показателя. Вводим в ячейку J36 текст Dy=, а в K36 вводим функцию =ДИСПР(B2: B33). Результат: 0, 405. Видно, что оценка дисперсии адекватности намного меньше оценки полной дисперсии , что говорит о хорошем качестве модели.

Оценка парного коэффициента корреляции вычисляется по формуле: . Для его получения вводим в J37 текст Корреляция, а в K37 формулу =КОРЕНЬ(1-K35/K36). Результат близок к единице, что еще раз подтверждает хорошее качество модели.

Таким образом, построена модель временного ряда, показано её высокое качество, поэтому модель можно использовать для прогнозирования. Составим прогноз уровня водохранилища на следующие два года: Расчёты представить в таблице: