2 Построение трендовых моделей
1. В Microsoft Excel трендовые модели строятся на основе диаграмм, представляющих уровни динамики. Для эмпирического ряда динамики может быть построена диаграмма одного из следующих типов: гистограмма, линейчатая диаграмма, график, точечная диаграмма, диаграмма с областями.
Для построения линии тренда необходимо в построенной по исходным данным диаграмме выделить ряд динамики и выбрать в контекстном меню (вызывается щелчком правой клавиши мыши) команду Добавить линию тренда. Будет вызвано диалоговое окно Линия тренда, содержащее вкладку Тип (рисунок 11), на которой задается тип тренда:
Линейный;
Логарифмический;
Полиноминальный (от 2-ой до 6-ой степени включительно);
Степенной;
Экспоненциальный;
Скользящее среднее (с указанием периода сглаживания от 2 до 15).
Вкладка Параметры (рисунок 11) предназначена для задания параметров тренда:
Имя тренда – имя линии тренда, располагается в легенде диаграммы; возможны следующие варианты задания имени тренда:
автоматическое – Microsoft Excel именует линию тренда, основываясь на выбранном типе тренда и ряде динамики, с которым она ассоциирована, например, Линейный (ряд I);
другое – вводится уникальное имя тренда, максимальная длина составляет 256 символов.
Рисунок 11 - Меню «Линия тренда»
Прогноз вперед на – количество периодов, на которое линия тренда проектируется в будущее, то есть в направлении от оси Y.
Прогноз назад на – количество периодов, на которое линия тренда проектируется в прошлое, т.е. в направление к оси Y.
Пересечение кривой с осью Y в точке – точка, в которой линия тренда пересекает ось Y (поле не доступно в режиме скользящего среднего).
Показывать уравнение на диаграмме – на диаграмме будет показано уравнение линии тренда.
Поместить на диаграмму величину достоверности аппроксимации (R2) – на диаграмме будет показано значение коэффициента детерминации. Коэффициент детерминации - это доля дисперсии зависимой переменной, объясняемая рассматриваемой моделью зависимости, т.е. объясняющими переменными. Коэффициент детерминации показывает долю общей вариации зависимой переменной, обусловленной регрессией или изменчивостью объясняющей переменной. Чем ближе к 1, тем теснее наблюдения примыкают к линии регрессии, тем лучше регрессия описывает зависимость переменных.
2.Требуется по данным о динамике производства продукции предприятия ТЭК (таблица 2) построить трендовую модель объема производства.
Таблица 2 - Динамика производства продукции предприятия ТЭК
Год |
Производство продукции, тыс. тонн условного топлива |
2003 |
25,9 |
2004 |
26,5 |
2005 |
25,3 |
2006 |
25,8 |
2007 |
26,3 |
2008 |
26,1 |
2009 |
27 |
2010 |
27,4 |
2011 |
27,1 |
2012 |
28,2 |
2013 |
28,4 |
Для решения поставленной задачи, прежде всего в порядке первого приближения, намечаются типы функций, которые могут отобразить имеющиеся в динамическом ряду изменения. В помощь этому исходные данные, приведенные в таблице 2, изображаются графически с помощью мастера диаграмм (рисунок 12).
Рисунок 12 - Динамика производства продукции предприятия ТЭК, тыс. тонн у.т.
По характеру размещения уровней анализируемого ряда динамики можно сделать предположение о возможном аналитическом выравнивании изучаемого ряда типовой математической функцией. Это может быть и линейная функция, и полином 2-го порядка, и ряд других функций.
Для нахождения наиболее адекватного уравнения тренда используем инструмент «Подбор линии тренда» из мастера диаграмм Microsoft Excel (отметки Показать уравнение на графике и Поместить на диаграмму величину достоверности аппроксимации). Представим отдельные виды трендов на рисунках 13, 14, 15.
Рисунок 13 - Динамика эмпирических и теоретических уровней производства продукции предприятия ТЭК (линейный тренд), тыс. тонн у.т.
Рисунок 14 - Динамика эмпирических и теоретических уровней производства продукции
предприятия ТЭК (логарифмический тренд), тыс. тонн у.т.
Рисунок 15 - Динамика эмпирических и теоретических уровней производства
продукции предприятия ТЭК (степенной тренд), тыс. тонн у.т.
Результаты подбора уравнения приведены в таблице 3 (при подборе уравнения не рассматривались полиномы выше 3-го порядка).
Таблица 3 - Уравнения тренда
Вид уравнения |
Уравнение |
Коэффициент детерминации R |
Линейное |
y = 0,26x + 25,167 |
0,7556 |
Логарифмическое |
y = 0,9597Ln(x) + 25,2 |
0,5195 |
Полином 2-го порядка |
y = 0,0354x2 - 0,1652x + 26,088 |
0,8650 |
Полином 3-го порядка |
y = -0,0042x3 + 0,1103x2 - 0,5401x + 26,542 |
0,8758 |
Степенное |
y = 25,236x0,0357 |
0,5195 |
Экспоненциальное |
y = 25,207e0,0097x |
0,7534 |
Принимая во внимание физическую сущность изучаемого процесса и результаты проведенного аналитического выравнивания, в качестве аппроксимирующей модели тренда выбираем полином 3-го порядка (рисунок 16).
Рисунок 16 - Динамика эмпирических и теоретических уровней производства
продукции предприятия ТЭК (полином третьего порядка), тыс. тонн у.т.
Используя данный тренд, можно выполнить краткосрочный прогноз (в примере на рисунке 16 на три года вперед).