Лабораторная работа № 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=Σ(yi – ati – b) 2 →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 (R2лин > R2экс).
Рис.6. Результаты решения задания
