Построение линейных и нелинейных уравнений регрессии и линий тренда

Лабораторная работа № 1

ПОСТРОЕНИЕ РЕГРЕССИОННЫХ МОДЕЛЕЙ (4 часа)

Цель выполнения лабораторной работы

Целью работы является изучение методов решения задач регрессионного анализа с применением возможностей офисной программы MS Excel. Развитие навыков использования команды Сервис/Подбор параметра, встроенных статистических функций, построения линейных и нелинейных уравнений регрессии и линий тренда.

Построение линейных и нелинейных уравнений регрессии и линий тренда

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

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

Значения элементов рядов представлены в табл.1.

Табл.1.

х
y (значение наблюдаемой величины)	2,01	2,12	2,77	3,62	3,80	-	-	-

Требуется построить линейную и нелинейную регрессионную модели yt=at+b, yt=b*exp(at). Параметры a и b подбираются так, чтобы минимизировать сумму квадратов разностей между наблюдаемым рядом y и теоретическими значениями yt, т.е. так, чтобы величина всех отклонений отвечала условию U=Σ(y_i – at_i – b)² →min для i=1, 2, 3,…, n.

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

Ø Подготовим начальный рабочий лист с исходными данными как показано на рис.1.

Рис. 1. Начальный рабочий лист с исходными данными

В диапазон ячеек А2:А9 введены значения из ряда t, в диапазон ячеек В2:В9 – значения ряда из табл.1. Под переменные a и b поиска решения отведены ячейки D2, Е2 соответственно. В ячейку F2 введена формула для минимизируемой функции цели:

=СУММКВРАЗН(B2:B9;E2+D2*A2:A9)(1)

В этой формуле использована функция СУММКВРАЗН(), вычисляющая сумму квадратов разностей соответствующих элементов двух массивов.

Ø Выберем команду Сервис/Поиск решения. Откроется диалоговое окно Поиск решения.

Ø Заполним диалоговое окно Поиск решения.

При заполнении окна Поиск решения введем абсолютную ссылку на ячейку с целевой функцией F2, в группе Равной выберем минимальному значению, так как требуется найти минимальное значение целевой функции, в поле Изменяя ячейки укажем диапазон ячеек D2:Е2.

Ø Далее установим параметры поиска решения, получим решение и далее повторим его с большей точностью и с меньшим допустимым отклонением и создадим отчет Excel по результатам (рис.2.).

Ø

Microsoft Excel 12.0 Отчет по результатам
Рабочий лист: Лаб_1.xlsx]Лист1
Отчет создан: 01.02.2017 14:50:32
Целевая ячейка (Минимум)
	Ячейка	Имя	Исходное значение	Результат
	$F$2	Целевая функция:		2,869047619
Изменяемые ячейки
	Ячейка	Имя	Исходное значение	Результат
	$D$2	a:		6,011904191
	$E$2	b:		7,071428467
Ограничения
	НЕТ

Рис. 2.

Ø Найдем параметры а и b в линейной регрессионной модели с помощью статистических функций НАКЛОН() и ОТРЕЗОК(). Функция НАКЛОН() определяет коэффициент наклона линейного тренда. Ее формат записи – НАКЛОН(<массив у>;<массив t>), функция ОТРЕЗОК() определяет точку пересечения линейного тренда с осью ординат. Ее синтаксис – ОТРЕЗОК(<массив у>;<массив t>).

Аргументы этих функций:

<массив t> –это массивзначений независимой наблюдаемой величины. Если аргумент <массив t> опущен, то по умолчанию полагается, что это массив из натурального ряда чисел того же ряда, как и аргумент <массив у>;

<массив у>- это массив известных значений зависимой наблюдаемой величины.

Введем формулы:

=НАКЛОН(B2:B9;A2:A9) (2)

=ОТРЕЗОК(B2:B9;A2:A9) (3)

в ячейки D4 и Е4 соответственно и сравним результаты с содержимым ячеек D2 и Е2.

Ø Найдем параметры а и b линейной регрессионной модели, используя команду Добавить линию тренда. Для этого:

- построим точечный график по данным диапазона ячеек А2:В9, выделим точки графика двойным щелчком, затем щелкнем на них правой кнопкой мыши. Раскроется контекстное меню, в нем выберем команду Добавить линию тренда;

- в раскрывшемся диалоговом окне Линия трендана вкладке Тип выберем Линейная, затем на вкладке Параметры установим флажки Показать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации. Щелкнем кнопку ОК. (Рис.3.)

Рис.3.

Рис.4. Диаграмма с линией тренда типа Линейная

Ø Вычислим теоретическое значение наблюдаемой величины yt при t из ячейки А2. Для этого в ячейку С2 введем формулу:

=D2*A2+E2 (4)

Сравним результат с содержимым ячейки В2.

Ø Вычислим теоретическое значение yt при t из ячейки А4 с помощью функции ПРЕДСКАЗ(). Ее синтаксис - ПРЕДСКАЗ(ti;<массив y>;<массив t>).Аргумент ti - это точка данных из массива t, для которой предсказывается теоретическое значение yti. Теоретическое значение в ячейке С4 вычислим по формуле:

=ПРЕДСКАЗ(A4;B2:B9;A2:A9) (5)

Буксировкой формулы (5) вниз заполним диапазон С5:С9 новыми yt.

Сравним значения в диапазонах В4:В12 и С4:С12.

Ø Вычислим значения уравнения линейной регрессии для целого диапазона значений независимой переменной с помощью функции ТЕНДЕНЦИЯ(). Ее синтаксис – ТЕНДЕНЦИЯ(<массив y>;<массив t>;<новые значения t>;[<конст>]).

Аргумент <новые значения t> - это массив значений t, для которых функция ТЕНДЕНЦИЯ() возвращает соответствующие значения yt.

Новое значение зависимой переменной вычислим в ячейке В10 по формуле:

=ТЕНДЕНЦИЯ(B2:B6;A2:A6;A7) (6)

Буксировкой формулы (6) вниз заполним диапазон В8:В9 новыми значениями у.

Ø Найдем параметры а и b линейной регрессионной модели с помощью статистической функции ЛЕНЕЙН(). Эта функция возвращает массив значений параметров уравнения многомерной регрессии, для двумерной регрессии – параметры а и b. Ее синтаксис – ЛЕНЕЙН(<массив у>;<массив t>;[<конст>];[<статистика>]), где <статистика> - это логическое значение.

Введем в ячейки D6:Е6 формулу: =ЛИНЕЙН(B2:B9;A2:A9)(7)

Результаты, полученные в диапазонах ячеек D2:Е2, D4:Е4, D6:Е6 ина диаграмме с линией тренда типа Линейная, сравним между собой.

Ø Построим нелинейную экспоненциальную модель. Она описывается уравнением yt=b*exp(a*t).

Значения параметров b, m степенной модели вида yt=b*m^t определяется с помощью функции ЛГРФПРИБЛ(<массив у>;<массив t>;[<конст>];[<статистика>]), где <статистика> - это логическое значение, которое указывает, требуется ли вывести дополнительную статистику по регрессии, например корреляции.

ü Для определения параметров нелинейной экспоненциальной модели в ячейки D8:Е8 введем формулу:

=ЛГРФПРИБЛ(B2:B9;A2:A9) (8)

а в ячейку Е9 – формулу: LN(D8) (9)

ü Значения экспоненциального тренда предсказывает функция РОСТ. Для построения экспоненциального тренда в ячейку G2 введем формулу:

=РОСТ(B2:B9;A2:A9;A2) (10)

и отбуксируем ее на диапазон G3:G12.

ü вычислим теоретическое значение yt (нелин) наблюдаемой величины, используя экспоненциальную модель.

Для этого введем в ячейку F4 формулу:

=E8*EXP(E9*A4)(11)

Буксировкой формулы (11) заполним диапазон F5:F12 результатами вычислений. Сравним значения в ячейках B4:B12, С4:С12, F4:F12, G4:G12.

ü Построим точечный график «Динамика изменения наблюдаемой величины» по данным диапазона ячеек А2:В9, затем, используя команду Добавить линию тренда, построим экспоненциального типа линию тренда (описание выше). Рис.5.

Рис.5. График с линией тренда типа Экспоненциальная

ü Сравним между собой линейную и экспоненциальную модели по коэффициенту корреляции. Коэффициент корреляции в линейной и экспоненциальной модели различается на 0,0212 (R²_лин > R²_экс).

Рис.6. Результаты решения задания