Как построить график временного ряда в excel

Порядок

Анализ временных рядов в Excel

Рассмотрим задачу прогнозирования урожайности подсолнечника при наличии данных с 1965 по 2010 г. (за 46 лет), для этого рассмотрим ряд моделей и выберем ту из них, которая дает наименьшую ошибку в 2010 г., хотя можно в качестве критерия отбора выбрать минимальную сумму модулей ошибок точек или минимальную дисперсию для последних /:-точек и т.д. В конечном счете вид лучшего критерия мы узнаем только в 2011 г.

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

Для аналитического выравнивания и прогноза по уравнению прямой У, — а0 + bt можно использовать в категории «Статистические» следующие функции (причем для отсчета времени перейдем к условным годам t: 1,2. 46) (рис. 7.3):

  • а) ПРЕДСКАЗ (выделим диапазон D2:D52 и в ячейку D2 введем формулу массива: <=ПРЕДСКАЗ(В2:В52;С2:С47;В2:В47)>);
  • б) ТЕНДЕНЦИЯ (выделим диапазон Е48:Е52 и в ячейку Е48 введем формулу массива: <=ТЕНДЕНЦИЯ(С2:С47;В2:В47;В48:В52;1)>);
  • в) ЛИНЕЙН — позволяет получить коэффициенты уравнения регрессии с помощью МНК, которые можно использовать в формуле для выравнивания и прогноза.

Динамика урожайности подсолнечника

Рис. 7.2. Динамика урожайности подсолнечника

Результаты линейной и экспоненциальной экстраполяции

Рис. 7.3. Результаты линейной и экспоненциальной экстраполяции

Для аналитического выравнивания по уравнению экспоненты у=Ь*т х (см. рис. 7.3):

  • а) РОСТ (используется для предсказания или выравнивания по экспоненциальной кривой, выделим диапазон F2:F52 и введем формулу массива (=РОСТ(С2:С47;В2:В47;В2:В52;1)>);
  • б) ЛГРФПРИБЛ может использоваться аналогично ЛИНЕЙН. Самый простой способ прогнозирования на основе линейного или

экспоненциального тренда заключается в использовании контекстного меню. Необходимо: 1) выделить диапазон данных; 2) при нажатой правой клавише мыши протащить маркер заполнения на необходимый период прогнозирования; 3) в открывшемся контекстном меню выбрать вид приближения: Линейное, Экспоненциальное, Прогрессия (рис. 7.4).

Контекстное меню для экстраполяции

Рис. 7.4. Контекстное меню для экстраполяции

Замечание. Все приведенные выше формулы можно (даже нужно) вводить не вручную, а используя Мастер функций, категорию Статистические. Так как рассматриваемые выше формулы обрабатывают массивы данных, то после их введения необходимо нажать Ctrl + Shift + Enter.

Важным методом анализа временных рядов в Excel являются диаграммы. Выделим на рис. 7.5 щелчком левой клавиши мыши маркеры наблюдений урожайности подсолнечника по годам; с помощью правой клавиши откроем контекстное меню (см. рис. 7.5) и выберем одну из перечисленных линий трендов (рис. 7.6):

  • — Линейная;
  • — Логарифмическая;
  • — Полиномиальная;
  • — Степенная;
  • — Экспоненциальная;
  • — Линейная фильтрация (Скользящая средняя).

Контекстное меню выделенных точек наблюдений

Рис. 7.5. Контекстное меню выделенных точек наблюдений

Диалоговое окно выбора линии тренда

Рис. 7.6. Диалоговое окно выбора линии тренда

После выбора одного из трендов, например линейного, отметим «показывать уравнение на диаграмме» и «поместить на диаграмму коэффициент достоверности аппроксимации (/С2) (см. рис. 7.6). Можно выбрать название (назвать тренд самостоятельно) или оставить автоматически предлагаемое Excel; для прогноза, согласно выбранной линии тренда на пять лет вперед, выберем соответствующее значение в диалоговом окне. Далее выберем ОК. При выборе других типов линии тренда получим рис. 7.7.

Рассмотрим другие типы моделей. Прогноз по уравнению третьей степени можно получить, используя рис. 7.7: у = 0,0019х 3 — 11,025х 2 + + 21 886х — Е + 07, где Т — год. Но лучше (так как погрешность расчетов меньше) у — 0,0019/ 3 — 0,1176/ 2 + 1,7489/+ 18,624, где /— порядковый номер года (для прогноза на 2011—2015 гг. / = 47. 51).

Выравнивание и прогноз по СС — скользящим средним — проведем с использованием пакета анализа (инструмент «Скользящее среднее»).

Взвешенную скользящую среднюю (ВСС) для пяти точек определим для полинома 3 степени (модуль 4) по формуле

Рис. 7.7. Линии тренда

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

В качестве модели прогноза целесообразно выбрать модель, имеющую наименьшую ошибку в последней точке (у нас это модель третьей степени и 11-летней скользящей средней), исходя из предположения о сохранении тенденции в дальнейшем.

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

В результате применения инструмента «Корреляция» (Пакет анализа) получим корреляционную матрицу (табл. 7.7).

Прогнозирование по линиям тренда

Рис. 7.8. Прогнозирование по линиям тренда

Прогнозирование продаж в Excel с учетом сезонности

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

Из чего состоит временной ряд

Уровни временного ряда (Yt) представляют из себя сумму двух компонент:

  1. Регулярную составляющую
  2. Случайную составляющую

В свою очередь регулярная составляющая состоит из:

  1. Тренда
  2. Сезонности
  3. Циклической составляющей

Однако, в модели необязательно наличие всех этих компонент сразу.

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

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

  1. Тренд (Tt)
  2. Сезонность (St)
  3. Цикличность (Ct)
  4. Случайные возмущения (Et)

Циклическая компонента, по сравнению с сезонностью, имеет более длительный эффект и меняется от цикла к циклу. Поэтому, ее обычно объединяют с трендом.

Виды моделей временного ряда

Обычно, выделяют две модели временного ряда и третью — смешанную.

    Аддитивная модель

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

Смешанная модель

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

Амплитуда сезонной составляющей

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

Построение этих моделей сводится к расчету тренда (Tt), сезонности (St) и случайных возмущений (Et) для каждого уровня ряда (Yt).

Алгоритм построения модели

  1. Выравниваем ряд с помощью скользящей средней, то есть сглаживаем ряд и отфильтровываем высокочастотные колебания.
  2. Рассчитываем значение сезонной компоненты St.
  3. Рассчитываем значения Tt с использованием полученного уравнения тренда.
  4. Используя полученные значения St и Tt, находим прогнозные значения уровней временного ряда.
  5. Оцениваем качество модели.

Реализация на практике

Итак, мы имеем на руках данные о продажах за 2016 и 2017 год и хотим спрогнозировать продажи на 2018 год.

GeekBrains

Исходные данные

График исходных данных

Шаг 1

Следуя нашему алгоритму, мы должны сгладить временной ряд. Воспользуемся методом скользящей средней. Видим, что в каждом году есть большие пики (май-июнь 2016 и апрель 2017), поэтому возьмем период сглаживания пошире, например, месячную динамику, т.е. 12 месяцев.

Удобнее брать период сглаживания в виде нечетного числа, тогда формула для расчета уровней сглаженного ряда:

yi — фактическое значение i-го уровня ряда,

yt — значение скользящей средней в момент времени t,

2p+1 — длина интервала сглаживания.

Но так как мы решили использовать месячную динамику в виде четного числа 12, то данная формула нам не подойдет и мы воспользуемся этой:

Иными словами, мы учитываем половины от крайних уровней ряда в диапазоне, в остальном формула не претерпела больше никаких изменений. Вот ее точный вид для нашей задачи:

Сглаживаем наши уровни ряда и растягиваем формулу вниз:

Сглаживание уровней ряда

Сразу можем построить график из известных значений уровня продаж и их сглаженной. Выведем ее уравнение и значение коэффициента детерминации R^2:

Сглаженные уровни ряда

В качестве сглаженной я выбрала полином третьей степени, так как он лучше всего описывал уровни временного ряда и имел наибольший R^2.

Отображение функции тренда

Шаг 2

Так как мы рассматриваем аддитивную модель вида:

Найдем оценки сезонной компоненты как разность между фактическими уровнями ряда и значениями скользящей средней St+Et = Yt-Tt, так как Yt и Tt мы уже знаем.

Поиск сезонной и случайной составляющей

Используем оценки сезонной компоненты (St+Et) для расчета значений сезонной компоненты St. Для этого найдем средние за каждый интервал (по всем годам) оценки сезонной компоненты St.

Средняя оценка сезонной компоненты находится как сумма по столбцу, деленная на количество заполненных строк в этом столбце. В нашем случае оценки сезонной составляющей расположились в строках без пересечений, поэтому сумма по столбцам состоит из одиночных значений, следовательно и среднее будет таким же. Если бы мы располагали периодом побольше, например с 2015, у нас бы добавилась еще одна строка и мы смогли бы полноценно найти среднее, поделив сумму на 2.

В моделях с сезонной компонентой обычно предполагается, что сезонные воздействия за период взаимопогашаются. В аддитивной модели это выражается в том, что сумма значений сезонной компоненты по всем интервалам должна быть равна нулю. Поэтому найдя значение случайной составляющей, поделив сумму средних оценок сезонной составляющей на 12, мы вычитаем ее значение из каждой средней оценки и получаем скорректированную сезонную компоненту, St.

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

Добавление сезонной составляющей

Шаг 3

Теперь рассчитываем значения уровня тренда T(t) по тому уравнению, которое мы получили при построении сглаженного тренда на первом шаге.

T(t) = — 23294 + 34114 * t — 1593 *t^2 + 26,3 *t^3

Вместо t используем значения из столбца Период из соответствующей строки.

Добавление трендовой составляющей

Шаг 4

Имея рассчитанные значения S(t) и T(t) мы можем рассчитать прогнозные значения уровней ряда Y(t). Для этого накладываем уровни сезонности на тренд.

Прогнозные значения

Теперь построим график известных значений Y(t) и спрогнозированных за 2018 год.

Фактические и прогнозные значения

Вот мы и нашли спрогнозированные значения уровней продаж на 2018 год. Значения отражают возрастающую тенденцию и сезонные пики. Конечно, эти данные не дают 100% точности, ведь существует множество внешних воздействий, которые могут изменить направление тренда, поэтому к прогнозным значениям обычно строят доверительный интервал, это такой коридор, внутри которого могут колебаться прогнозные значения с заданной вероятностью (чаще всего выбирают 95%). Но об этом я расскажу в следующей статье.

Шаг 5

Осталось оценить точность модели. Для этого будем использовать среднюю ошибку аппроксимации, которая поможет рассчитать ошибку в относительном выражении. Иными словами, это среднее отклонение расчетных значений от фактических, которое вычисляется по формуле:

yi — спрогнозированные уровни ряда,

yi* — фактические уровни ряда,

n — количество складываемых элементов.

Модель может считаться адекватной, если:

Итак, рассчитываем ошибку аппроксимации для нашего случая. Так как в основе нашего тренда лежит полином третьей степени, прогнозные значения начинают хорошо повторять фактические значения к концу 2016 года, думаю, я думаю, поэтому корректнее было бы рассчитать ошибку аппроксимации для значений 2017 года.

Ошибка аппроксимации

Сложив весь столбец с ошибками аппроксимации и поделив на 12, получаем среднюю ошибку аппроксимации 4,13%. Это значение меньше 15% и можем сделать вывод об адекватности модели.

Не забывайте, что прогнозы не бывают точными на 100%. Любые неожиданные внешние воздействия могут развернуть значения уровней ряда в неизвестном направлении &#128578;

Корпорация "Центр"

Ссылка на основную публикацию