ЛАБОРАТОРНАЯ РАБОТА №3

ОБРАБОТКА ДАННЫХ СРЕДСТВАМИ ПРИЛОЖЕНИЯ MS EXCEL (ПОСТРОЕНИЕ ТРЕНДА И ПРОГНОЗИРОВАНИЕ)

1.1 Цель лабораторной работы

Научить студентов строить и использовать линии тренда для решения технических задач.

1.2 Порядок выполнения работы

1.2.1 Выполняется при самостоятельной работе:

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

1.2.2 Выполняется на лабораторном занятии:

Решить индивидуальную задачу в соответствии с приведенным заданием. Показать результат работы преподавателю. Оформить отчет по лабораторной работе и защитить его у преподавателя.

1.3 Теоретические сведения

1.3.1 Линии тренда на диаграмме

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

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

1.3.2 Добавление линии тренда к рядам данных

а) Выберите ряд данных, к которому нужно добавить линию тренда или скользящее среднее.

б) Выберите команду Добавить линию тренда в меню Диаграмма.

в) На вкладке Тип выберите нужный тип регрессионной линии тренда или линии скользящего среднего.

г) При выборе типа Полиномиальная введите в поле Степень наибольшую степень для независимой переменной.

1.3.3 Редактирование линий тренда

Данная процедура доступна только для регрессионных линий тренда.

а) Выберите изменяемую линию тренда.

б) Выберите команду Линия тренда в меню Формат.

в) На вкладке Параметры выберите нужные параметры.

1.3.4 Пример: По имеющемуся временному ряду (таблица 1.1) построим тренд пятого порядка в соответствии с рисунком 1.1.

Таблица 1.1 - Временной ряд изменения энергии излучения

Рисунок 1.1

1.4 Задание на лабораторную работу

1.4.1 Разработать на основе примера собственный ряд зависимости физического показателя, например: температуры в канале разряда от мощности разряда.

1.4.2 По полученному ряду построить тренд, используя возможности Excel. На графике показать параметры линии тренда (в окне «Формат линии тренда» использовать вкладку Параметры).

1.4.3 Осуществить прогноз на 1 или 2 шага вперёд, используя ту же вкладку. Чтобы прогноз был более точным, необходимо выбрать такой тренд, R² для которого больше 0.7.

§ 1. Постановка задачи

Любой объект (экономический, физический, производственный и др.), являясь системой взаимодействующих между собой элементов, связан с другими объектами и постоянно изменяется во времени, поэтому ни одна математическая модель не может описать его полностью. Модель объекта должна отражать самые существенные его свойства и связи, которые позволяют проводить исследования. С ее помощью можно принимать решения, достигающие определённой цели. Один и тот же объект может быть описан различными моделями.

В исследованиях взаимосвязей реальных величин приходится иметь дело с переменными, имеющими стохастическую природу. Они могут быть представлены наборами входных параметров х1ⁱ,х2ⁱ,…,хⁱn, i= 1,2,…,L, которые называют аргументами (факторами) и выходных- y1, y2,…,yn. Входные параметры являются управляемыми, выходные же параметры зависят от нас частично. Зависимость переменных, на которую накладываются воздействия случайных факторов, называется статистической зависимостью. Выбор формулы зависимости переменных называется спецификацией уравнения зависимости. Вопрос о нахождении формулы зависимости можно ставить после положительного ответа на вопрос о существовании такой зависимости. В качестве меры для степени линейной зависимости двух переменных используют коэффициент их корреляции. Выборочный коэффициент корреляции рассчитывается по формуле:

Величина коэффициента корреляции меняется от -1 до +1. Если его значение по модулю близко к единице, то между х и у существует линейная зависимость, иначе такой зависимости нет. Если же его значение по модулю близко или равно нулю, то между х и у вообще не существует зависимости. Таким образом, исследование зависимости надо начинать с вычисления значения коэффициента корреляции. Вопрос о существовании зависимости можно также решать с помощью графического представления начальных данных, называемого полем корреляции. При вытянутом облаке точек поля корреляции зависимость существует, но возможно не линейная. Параметры зависимости определяют методом наименьших квадратов или его модификацией. Нижней границей количества исходных данных, необходимых для метода наименьших квадратов является величина:

,

где m- число искомых параметров зависимости (для линейной зависимости m=2, k=8). Полученные аналитические зависимости называются уравнениями регрессии и в общем случае имеют вид:

у= f(x¹, x², x³, …,xⁿ).

Регрессия называется парной, если она описывает зависимость между функцией и одной переменной и имеет вид: y= f(x). Регрессия называется множественной, если она описывает зависимость между функцией и несколькими переменными и имеет вид:

у= f(x¹, x², x³, …,xⁿ).

Если зависимости являются линейными, то регрессия называется линейной, в противном случае называется нелинейной.

Если известна последовательность временных значений t1, t2, …,tn и соответсвующие им наборы исследуемой величины у1, у2,...уn то говорят о динамическом (временном) ряде. В составе динамического ряда можно выделить 4-е компоненты: 1) главную тенденцию, или тренд; 2) регулярные колебания относительно тренда; 3) сезонные колебания; 4) остаток, или случайную компоненту, отражающую влияние разнообразных факторов стохастического рахактера. Конкретный динамический ряд не обязательно включает все указанные компоненты. Уравнение регрессии, отражающее общие закономерности или тенденции развития динамического ряда, называется трендовой моделью или трендом. Так как важным свойством тренда является гладкость, при выборе трендовых моделей предпочтение отдаётся непрерывным и дифференцируемым функциям, отражающим главные особенности динамики исследуемого показателя и, прежде всего, тип его развития.

Выбор класса функций тренда.

Проанализируем развитие динамического ряда: Абсолютный прирост - характеризующий скорость изменения уровня: _i=y_i-y_i-1, темп прироста - характеризующий относительную скорость изменения: = _i / y_i-1

Будем подбирать типы трендовых моделей с постоянным и увеличивающимся приростом, с уменьшающимся темпом прироста. К таким типам трендовых моделей могут принадлежать все выше приведенные 8 функций.

Проанализируем несколько вариантов:

Полагаем, что зависимость между t и y_i: линейная

y^d=a0+a1*t

Используя метод наименьших квадратов строится система нормальных уравнений (используется первая таблица), затем определяются неизвестные параметры а₁ и а₀,например по методу Гаусса (или по методу Крамера), используется вторая таблица.

Таблица 2. Определение параметров а₁ и а₀, по методу Гаусса

По результатам строится график.

Для получения аппроксимационных оценок строим таблицу:

?S=(y- y^d)² ?r =(y- y_средн)² y_средн = (y₁+y₂+…+y₉)/9

Коэффициент детерминации

R²= 1-(?S/7)/( ?r/8)=0,991

Полагаем, что зависимость между t и y_i^d: гиперболическая

Yд=a0+a1*t^-1

Согласно методу наименьших квадратов строится система нормальных уравнений, (используется первая таблица), затем определяются неизвестные параметры а₁ и а₀ по методу Гаусса (используется вторая таблица).

По результатам строится график.

Для получения аппроксимационных оценок используем таблицу:

Коэффициент детерминации R²= 1-(?S/7)/( ?r/8)=0,644

Полагаем, что зависимость является пораболической.

Yд=a₀+a₁*t+а₂*t²

По методу наименьших квадратов строится система нормальных уравнений, (используется первая таблица), затем определяются неизвестные параметры а₁ и а₀ по методу гаусса (используется вторая таблица).

По результатам строится график.

Для получения аппроксимационных оценок используем таблицу:

Коэффициен детерминации

R²= 1-(?S/7)/(?r/8)=0,984

Построение всех таблиц выполняется в Microsoft Exel.

Часть 2. Определение аппроксимационных оценок построеных моделей и выбор наилучей модели для прогнозирования

Сравнивая все построенные модели между собой по сумме квадратов уклонений от описываемого процесса ?S:

?Smin=МИН(5,074; 201,8; 7.72)= 5,074

видим, что наилучшей из всех является линейная модель.

Сравнивая все построенные модели между собой по величине коэффициента детерминации R²:

R²max=МАКС(0,991; 0,644; 0,984)= 0,991

видим, что наилучшей из всех является линейная модель.

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

Для заданной системы точек (ti ; yi)выбирают две точки, по возможности далеко отстоящие друг от друга. Пусть это точки Mi (t1 ; y1) и Mn (tn ; yn). Вычислим для них среднее арифметичаеское ta p, геометрическое t геом, гармоническое tгарм значений (t1 ; tn) и Mn (y1 ; yn):

; ; t геом = ; Y геом = ;

; ;

Для вычислений значений t_ар, t_геом, t_гарм независимой переменной найдем из построненного графика или из таблицы по формуле линейной интерполяции соответствующие значения зависимой переменной y₁^*; I = 1,2,3.

t _ар будет соответствовать y₁^*

t _геом будет соответствовать y₂^*

t _гарм будет соответствовать y₃^*

Сравним найденные значения y₁^*, y₂^*, y₃^* с вычисленными y_ар, y_геом, y _гарм и оценим погрешности:

Е₁ = (y₁^* -y_ар); Е₂ = (y₁^* -y_геом); Е₃ = (y₁^* -y_гарм);

Е₄ = (y₂^* -y_ар); Е₅ = (y₂^* -y_геом); Е₆ = (y₃^* -y_ар);

Е₇ = (y₃^* -y_ар);

Выберем минимальную погрешность:

Е_min - min (Е₁, Е₂, …. Е₇)

В соответствии с нумерацией списка формул таблицы 2.2, наиментьшей абсолютной погрешности Е_i отвечает i - я формула i = 1; 2; 3…7 (функция).

Замечания:

1. Приведенный подход к отысканию вида эмпирической формулы является грубо ориентированным, так как не учитыается поведение всех промежуточных данных. (t_i, y_i).

2. В ряде случаев переменные t и y могут соответствовать некоторой закономерности, не вошедшей в список.

Все зависимости, приведенные в таблице легко преобразовываются в линейные мотодом выравнивания (см. последный столбец табл. 2.2).

Метод выравнивания состоит в вводе новых переменных:

t = (t; y) y = (t; y)

таким образом, что преобразованные точки (t_i; y_i) лежат на некоторой прямой линии плоскости toy.

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

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

Этот же результат дает критерий Демидовича

Трендовая модель:

Y^d=2,7861+3,2716*t

Проведём анализ остаточной компоненты

?s=y-y^d.

Вычислим для неё матожидание

М[?s]=0,000

Проверим её независимость, вычислив коэффициент автокорреляции:

R_i,i-1=У(y_i-y^d_i)(y_i-1-y^d_i-1) / [У(y_i-y^d_i)² ?(y_i-1-y^d_i-1)²]^1/2= -0,009017

Часть 3. Нахождение точечного прогноза на 1 и 2 периодов времени вперёд и построение доверительного интервала

Объём выборки равен n= 9<20.Для прогнозирования выбираем: коэффициент значимости равный 0.05, доверительная вероятность равна 1-0.05. Из таблицы распределения Стьюдента выбирается число стандартных отклонений t(а,K) для К= n-v = 9-2 = 7

и вычисляем точечный прогноз на i периодов времени вперёд,учитывая доверительный интервал.

Yпрог=Y_d(n+i)

Yпрог=Y_d(n+i) = Y_n+i* t(а,К)*Sост