Построение графика временного ряда
Имеются поквартальные значения (t — условный номер квартала) экономического показателя — объема реализованной продукции — Y, млрд. руб.
- 1) Построить график временного ряда.
- 2) Получить аддитивную и мультипликативную модели ряда:
- — оценить сезонную компоненту (. );
- — оценить параметры линейного тренда у = a + bt, исключив из исходных уровней ряда сезонную компоненту.
1) Построим график временного ряда.
Занесем данные t и в рабочий лист Excel и построим поле корреляции (рис. 1.1).
Рассчитаем коэффициенты автокорреляции. Составим вспомогательную таблицу 1.1.
Аналогично составляем вспомогательную таблицу для расчета коэффициента автокорреляции второго порядка (табл. 1.2).
Аналогично находим коэффициенты автокорреляции более высоких порядков, и все полученные данные занесем в табл. 1.3, на основании которой построим коррелограмму (рис. 1.2).
Анализ коррелограммы (рис. 1.2) и графика (рис. 1.1) исходных уровней временного ряда позволяет сделать вывод о наличии в изучаемом временном ряде сезонных колебаний с периодичностью в четыре квартала.
- 2) Рассчитаем компоненты:
- а) аддитивной модели временного ряда.
Шаг 1. Проведем выравнивание исходных уровней ряда методом скользящей средней. Для этого заполним табл. 1.4.
Просуммируем уровни ряда последовательно за четыре квартала со сдвигом на один момент времени. Разделив полученные суммы на 4, найдем скользящие средние. Приведем полученные значения в соответствие с фактическими моментами времени, для чего найдем средние из двух последовательных скользящих средних — централизованные скользящие средние.
Шаг 2. Найдем оценки сезонной компоненты как разность между фактическими уровнями ряда и централизованными скользящими средними.
Составим табл. 1.5, разделив значения столбца F табл. 1.4 по кварталам и годам. Найдем средние за каждый квартал (по всем годам) оценки сезонной компоненты
Вычислим корректирующий коэффициент k и рассчитаем скорректированные значения сезонной компоненты = — k и занесем полученные данные в табл. 1.5.
Шаг 3. Исключим влияние сезонной компоненты, вычитая ее значение из каждого уровня исходного временного ряда. Получим величины
которые содержат только тенденцию и случайную компоненту.
Шаг 4. Определим компоненту Т аддитивной модели аналитическим выравниванием (Т + Е) с помощью линейного тренда (рис. 1.3).
В результате аналитического выравнивания линейный тренд имеет вид:
Т = 0,3256 + 1,2918t. Подставляя в это уравнение значения t = 1, 2, …, 16, найдем уровни Т для каждого момента времени (табл. 1.6).
аддитивный мультипликативный тренд автокорреляция
Шаг 5. Найдем значения уровней ряда. Для этого прибавим к уровням Т значения сезонной компоненты для соответствующих кварталов. Вычислим абсолютные ошибки
На одном графике (рис. 1.4) построим фактические значения уровней временного ряда () и теоретические (Т + S), полученные по аддитивной модели.
б) мультипликативной модели временного ряда.
Шаг 1. Проведем выравнивание исходных уровней ряда методом скользящей средней. Для этого заполним табл. 1.7.
Просуммируем уровни ряда последовательно за четыре квартала со сдвигом на один момент времени. Разделив полученные суммы на 4, найдем скользящие средние. Приведем полученные значения в соответствие с фактическими моментами времени, для чего найдем средние из двух последовательных скользящих средних — централизованные скользящие средние.
Шаг 2. Найдем оценки сезонной компоненты как частное от деления фактических уровней ряда на централизованные скользящие средние.
Составим табл. 1.8, разделив значения столбца F табл. 1.7 по кварталам и годам. Найдем средние за каждый квартал (по всем годам) оценки сезонной компоненты
Вычислим корректирующий коэффициент k и рассчитаем скорректированные значения сезонной компоненты = •k и занесем полученные данные в табл. 1.8.
Шаг 3. Исключим влияние сезонной компоненты, разделив каждый уровень исходного ряда на соответствующие значения сезонной компоненты.
Получим величины Т•Е = Y/S, которые содержат только тенденцию и случайную компоненту.
Шаг 4. Определим компоненту Т мультипликативной модели аналитическим выравниванием (Т•Е) с помощью линейного тренда (рис. 1.5).
В результате аналитического выравнивания линейный тренд имеет вид:
Т = — 1,9507 + 1,7292t. Подставляя в это уравнение значения t = 1, 2, …, 16, найдем уровни Т для каждого момента времени (табл. 1.9).
Шаг 5. Найдем значения уровней ряда. Для этого умножим значения Т на соответствующие значения сезонной компоненты. Вычислим абсолютные ошибки
На одном графике (рис. 1.6) построим фактические значения уровней временного ряда () и теоретические (Т•S), полученные по мультипликативной модели.
- 3) Оценим качество построенных моделей.
- а) Для оценки качества аддитивной модели вычислим:
Следовательно, аддитивная модель объясняет 99,7% общей вариации уровней временного ряда объема реализованной компанией продукции за 4 года.
б) Для оценки качества мультипликативной модели вычислим:
Следовательно, мультипликативная модель объясняет 48,1% общей вариации уровней временного ряда объема реализованной компанией продукции за 4 года.
- 4) Сделаем прогноз по полученным моделям на 17 и 18 кварталы.
- а) для аддитивной модели.
= 0,3256 + 1,2918•17 = 22,2862
= 0,3256 + 1,2918•18 = 23,5780
= + = 22,2862 + 1,9802 = 24,2664 млрд. руб.
= + = 23,5780 — 0,9365 = 22,6415 млрд. руб.
Таким образом, при сохранении существующей закономерности на 17-й и 18-й квартал следует ожидать объем реализованной компанией продукции 24,2664 и 22,6415 млрд. руб. соответственно.
б) для мультипликативной модели.
= — 1,9507 + 1,7292•17 = 27,4457
= — 1,9507 + 1,7292•18 = 29,1749
= •= 27,4457•1,1617 = 31,8837 млрд. руб.
= •= 29,1749•0,8958 = 26,1349 млрд. руб.
Таким образом, при сохранении существующей закономерности на 17-й и 18-й квартал следует ожидать объем реализованной компанией продукции 31,8837 и 26,1349 млрд. руб. соответственно.
Аддитивная и мультипликативная модели дают различные результаты по прогнозу. Более точным является прогноз, полученный по аддитивной модели, т.к. эта модель имеет лучшее качество (большее значение ).
Построение модели временного ряда средствами Exel 2007
Рассмотрим построение модели аддитивного ряда средствами Exel 2007 на примере изучения объемов потребления электроэнергии (млн кВТ*ч) жителями региона за 16 кварталов и на основании полученной модели спрогнозируем объем потребляемой электроэнергии на следующие полгода. Построенный пример описан в
.Пусть известный объем потребляемой электроэнергии задан таблицей 1.
Таблица 1. Потребление электроэнергии жителями региона, млн кВТ*ч
Внесем эти данные в таблицу
В главном меню выбираем «ВСТАВКА»
В главном меню выбираем «ТОЧЕЧНАЯ»
Теперь считаем сезонную компоненту и среднюю ошибку аппроксимации. Для этого открываем лист 2 и копируем в него первые два столбца. По методике, описанной в 1рассчитаем значения сезонной компоненты.
Таблица 1- Расчет оценок сезонной компоненты в аддитивной модели
Итого за четыре квартала
Центрированная скользящая средняя
Оценка сезонной компоненты
Таблица расчета оценок сезонной компоненты в аддитивной модели заполняется по следующему правилу:
1 столбец – известный номер квартала;
2 столбец – известный объем потребляемой электроэнергии(млн кВТ*ч);
3 столбец – складываем последовательно значения четырех ячеек 2 столбца и записываем их на одну клетку ниже;
4 столбец – каждое значение 3 столбца делим на 4 (период сезонных колебаний);
5 столбец – складываем последовательно значения двух ячеек 4 столбца, делим эту сумму на 2 и записываем на одну клетку ниже;
6 столбец – из элементов 2 столбца вычитаем элементы 5 столбца.
Рассчитаем значения сезонной компоненты S
Для этой цели составим следующую расчетную таблицу 3, в которую последовательно разместим данные из 6 столбца табл. 2.
Таблица 3- Расчет значений сезонной компоненты в аддитивной модели
Номер квартала
Итого за квартал
Средняя оценка сезонной компоненты (
Скорректированная сезонная компонента
Средняя оценка сезонной компоненты (
) рассчитывается как итого за квартал /3.В аддитивных моделях с сезонной компонентой предполагается , что сезонные воздействия за период взаимопогашаются. Это означает, что сумма значений сезонной компоненты по всем кварталам должна быть равна 0.
Для данной модели имеем 0,600+ (–1,958) + (–1,275) + 2,708 = 0,075
0.Определим корректирующий коэффициент k = 0,075/4 = 0,01875.
Рассчитаем скорректированные значения сезонной компоненты, как разность между ее средней оценкой и корректирующим коэффициентом k:
k.Проверим условие равенства нулю суммы значений сезонной компоненты: 0,581 – 1,977 – 1,294 + 2,690 = 0.
Подставим значения скорректированной сезонной компоненты в столбец С.
Заполняем столбец D, как разность В и С.
Пошагово выбираем ту линию тренда, где наибольшее значение имеет R 2 .
Для этого ставим курсор на точки поля корреляции и выбираем тренд, расставляя галочки в окна, «показать уравнение на диаграмме» и «поместить на диаграмму величину достоверности аппроксимации».
В столбец Е вбиваем формулу для уравнения тренда y= 0, 1864x + 5, 7155 и получаем расчетные значения для тренда.
Заполним столбец F, как сумму C и Е, и найдем ошибку аппроксимации.
Для нахождения ошибки аппроксимации заполним столбец G. Для этого в столбец G вставляем формулу
Найдем среднюю ошибку аппроксимации, заполнив столбец Н. Для этого разделим G на В и умножим на 100%.
Таким образом, заметим, что R 2 = 0,915 0,75, средняя ошибка аппроксимации равна 2,75%< 3%. Значит, данная модель является надежной.
Спрогнозируем значения потребляемой электроэнергии на следующий квартал. Для этого воспользуемся вновь электронной таблицей.
Заметим, что полученное число 8,8843 млн. кВт/ч. практически не отличается от полученного ранее значения
млн. кВт/ч.Анализ временных рядов и прогнозирование в Excel на примере
Анализ временных рядов позволяет изучить показатели во времени. Временной ряд – это числовые значения статистического показателя, расположенные в хронологическом порядке.
Подобные данные распространены в самых разных сферах человеческой деятельности: ежедневные цены акций, курсов валют, ежеквартальные, годовые объемы продаж, производства и т.д. Типичный временной ряд в метеорологии, например, ежемесячный объем осадков.
Временные ряды в Excel
Если фиксировать значения какого-то процесса через определенные промежутки времени, то получатся элементы временного ряда. Их изменчивость пытаются разделить на закономерную и случайную составляющие. Закономерные изменения членов ряда, как правило, предсказуемы.
Сделаем анализ временных рядов в Excel. Пример: торговая сеть анализирует данные о продажах товаров магазинами, находящимися в городах с населением менее 50 000 человек. Период – 2012-2015 гг. Задача – выявить основную тенденцию развития.
Внесем данные о реализации в таблицу Excel:
На вкладке «Данные» нажимаем кнопку «Анализ данных». Если она не видна, заходим в меню. «Параметры Excel» — «Надстройки». Внизу нажимаем «Перейти» к «Надстройкам Excel» и выбираем «Пакет анализа».
Подключение настройки «Анализ данных» детально описано здесь.
Нужная кнопка появится на ленте.
Из предлагаемого списка инструментов для статистического анализа выбираем «Экспоненциальное сглаживание». Этот метод выравнивания подходит для нашего динамического ряда, значения которого сильно колеблются.
Заполняем диалоговое окно. Входной интервал – диапазон со значениями продаж. Фактор затухания – коэффициент экспоненциального сглаживания (по умолчанию – 0,3). Выходной интервал – ссылка на верхнюю левую ячейку выходного диапазона. Сюда программа поместит сглаженные уровни и размер определит самостоятельно. Ставим галочки «Вывод графика», «Стандартные погрешности».
Закрываем диалоговое окно нажатием ОК. Результаты анализа:
Для расчета стандартных погрешностей Excel использует формулу: =КОРЕНЬ(СУММКВРАЗН(‘диапазон фактических значений’; ‘диапазон прогнозных значений’)/ ‘размер окна сглаживания’). Например, =КОРЕНЬ(СУММКВРАЗН(C3:C5;D3:D5)/3).
Прогнозирование временного ряда в Excel
Составим прогноз продаж, используя данные из предыдущего примера.
На график, отображающий фактические объемы реализации продукции, добавим линию тренда (правая кнопка по графику – «Добавить линию тренда»).
Настраиваем параметры линии тренда:
Выбираем полиномиальный тренд, что максимально сократить ошибку прогнозной модели.
R2 = 0,9567, что означает: данное отношение объясняет 95,67% изменений объемов продаж с течением времени.
Уравнение тренда – это модель формулы для расчета прогнозных значений.
Большинство авторов для прогнозирования продаж советуют использовать линейную линию тренда. Чтобы на графике увидеть прогноз, в параметрах необходимо установить количество периодов.
Получаем достаточно оптимистичный результат:
В нашем примере все-таки экспоненциальная зависимость. Поэтому при построении линейного тренда больше ошибок и неточностей.
Для прогнозирования экспоненциальной зависимости в Excel можно использовать также функцию РОСТ.
Для линейной зависимости – ТЕНДЕНЦИЯ.
При составлении прогнозов нельзя использовать какой-то один метод: велика вероятность больших отклонений и неточностей.
Как построить график и пометить данные временных рядов в Excel
Высококачественные графики можно довольно легко получить с помощью Excel, если у вас есть прочный фундамент для основ. В этом руководстве вы познакомитесь с основами построения графика временных рядов с помощью Excel. Я расскажу обо всем, от загрузки набора данных из Интернета до редактирования стиля вашего графика.
Примечание: В этом пошаговом руководстве используется Excel 2007, но методы очень похожи для Excel 2010.
Шаг 1. Создайте или загрузите свои данные
Вы можете создать свой собственный временной ряд или загрузить его из Интернета. Для этого урока я собираюсь построить график реального ВВП с 1950 года. Чтобы получить данные, я иду в Бюро экономического анализа.
Здесь у меня есть несколько вариантов: от момента начала сбора данных до того, как часто вы хотите получать данные (годовой или квартальный ВВП). Я собираюсь выбрать квартальные данные, начиная с 1950 года, а затем нажать кнопку "Обновить". Внизу страницы нажмите"указаны даты загрузки", чтобы загрузить выбранный вами временной ряд.
Шаг 2: очистите свои данные
Примечание: Цель этого шага — упростить работу с данными. Если вы хотите сохранить все данные, это нормально.
Следующим шагом после открытия загруженного файла будет очистка данных путем удаления ненужных данных.
- Выделите и удалите все строки, которые нам не нужны. Я нажимаю на строку с номером 10 и перетаскиваю курсор вниз до строки с номером 27 (когда я говорю номер строки, я имею в виду крайний левый номер, а не данные в Excel).
- Сейчас я щелкните правой кнопкой мыши и выберите "Удалить" из контекстного меню. Я мог бы очистить свои данные и дальше, но пока я счастлив.
- Удалите этот надоедливый "столбец строки".
- Выберите проблемные ячейки и щелкните правой кнопкой мыши. Теперь выберите "Удалить". Когда будет предложена опция, выберите "сдвинуть ячейки влево".
Шаг 3. Выберите данные и вставьте график.
- Щелкните номер строки данных, которые вы хотите отобразить на графике временных рядов. Я нажимаю номер строки ВВП, затем нажимаю и удерживаю "ctrl." С участием "ctrl" при нажатии я также могу выделить другие строки данных. Удерживая "ctrl" Теперь я нажимаю на номер строки «Расходы на государственное потребление». Кроме того, щелкните номер строки для строки с данными времени / даты.
- Выделив нужные строки, отпустите "ctrl" и перейдите в верхнюю строку меню. Нажми на "Вставлять" таб.
- Нажми на "Линия" кнопка.
- Выберите значок в верхнем левом углу.
На этом этапе вы должны получить простой график, похожий на приведенный ниже.
Примечание: Это график по умолчанию, выдаваемый Excel. На мой взгляд, это довольно некрасиво. Обратите внимание, что цветные метки справа и растягивают график, ни одна из осей не помечена, а год / кварталы кажутся случайными скачками по оси x. Посмотрим, сможем ли мы сделать его более понятным и понятным.
Шаг 4: Обозначьте график
- Добавьте заголовок: перейдите к "Макет"в верхней строке меню. Нажмите"Заголовок диаграммы" кнопка.
- Добавьте метки осей: перейдите к "Макет"в верхней строке меню. Нажмите"Название оси". На снимке экрана ниже показано, как выглядит меню, когда вы добавляете ярлыки.
- Relabel: Так же круто, как «Заголовок диаграммы» как имя вашего графика, вы, вероятно, захотите это изменить. Щелкните «Заголовок диаграммы» вверху графика. Подожди секунду. Щелкните его еще раз. Теперь у вас должна быть возможность редактировать текст. Повторите для обеих осей.
- Добавьте подписи к сериям: легенда справа от графика выглядит просто некрасиво. Давай пойдем в "Вставлять"и нажмите"текстовое окно". Нажмите и перетащите на графике, чтобы нарисовать поле, которое вы можете ввести. Введите" Реальный ВВП "в поле и настройте цвет шрифта в соответствии со строкой" Валовой внутренний продукт ". Сделайте то же самое для государственных расходов.
- Теперь вы можете удалить эту легенду справа, и на вашем графике появится больше места для дыхания. Он должен выглядеть примерно так, как на графике ниже.
Примечание: Пока вы это делаете, вы можете изменить шрифт, размер и стиль ваших заголовков при переименовании. Для этого нажмите на значок "Дом"в левом верхнем углу панели меню. Вы должны увидеть инструменты, которые напоминают вам Microsoft Word. Теперь вы можете редактировать текст, как и любой другой текстовый редактор. Мне нравится использовать все более красивым шрифтом, например Garamond или Palatino Linotype, и затем жирным шрифтом мои ярлыки.
Шаг 5: исправьте ось времени
Теперь наш график выглядит неплохо, но давайте взглянем на ось абсцисс. Как ни странно, каждые 7 кварталов он показывает год и квартал. Итак, за 1950-м кварталом следует 1-й квартал 1951-го, за 4-м кварталом 1953-го — 3-й квартал. Это, очевидно, странный способ обозначить график.
Чтобы исправить это: щелкните правой кнопкой мыши на датах оси X и выберите "ось формата". Появится окно с множеством опций. А пока посмотрите на 2 верхних варианта, которые я пометил A и B, и заполните их 20, как я сделал справа.
Примечание: 20 кратно 4, количеству кварталов в году. В частности, 20/4 = 5. Таким образом, наш новый график должен быть помечен к первому кварталу каждого пятого года. Вам следует всегда убедитесь, что эти два поля имеют смысл. В противном случае вы получите странные и часто неправильные метки осей.
Дальнейшие уточнения
Теперь, когда у вас есть основы, вы можете переходить к построению более интересных графиков. Попробуйте поиграть со всеми вариантами, которые вы получаете от щелчок правой кнопкой мыши случайная вещь. Вот две идеи, с которых можно начать:
- Щелкните правой кнопкой мыши строку ВВП и выберите "fсерия данных ormat. "Попробуйте изменить цвет линии и сделать ее толще.
- Щелкните правой кнопкой мыши вертикальную ось и выберите "ось формата". Попробуйте изменить масштаб оси и сделать ее в логарифмическом масштабе.
Не стесняйтесь задавать вопросы или просить разъяснений в комментариях.
Чтобы получить дополнительные сведения о графах, узнайте, как добавить линии спада к вашему графику.