ПОСТРОЕНИЕ РЕГРЕССИОННЫХ МОДЕЛЕЙ средствами excel
Цель работы.
Целью работы является изучение методов решения задач регрессионного анализа в Excel. Развитие навыков использования команды Сервис/Подбор параметра, встроенных статистических функций, построения линейных и нелинейных уравнений регрессии и линий тренда.
Исходные данные.
Пусть имеются ряды наблюдаемых величин t и у. Пусть ряд у представляет наблюдаемую величину продаж некоторым предприятием товара определенного вида за каждую неделю.
Значения элементов рядов представлены в табл.1.
Табл.1.
t(неделя) | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 |
у(кол-во продаж) | 13 | 19 | 26 | 30 | 37 | 44 | 49 | 55 | - | - | - |
Требуется построить линейную и нелинейную регрессионную модели yt=at+b, yt=b*exp(at). Параметры a и b подбираются так, чтобы минимизировать сумму квадратов разностей между наблюдаемым рядом y и теоретическими значениями yt, т. е. так, чтобы величина всех отклонений отвечала условию U=Σ(yi – ati – b)2 →min для i=1,2,3,…,n.
2.Последовательность выполнения задания
v Подготовим начальный рабочий лист с исходными данными как показано на рис.1.
Рис.1.
В диапазон ячеек А2:А9 введены значения из ряда t, в диапазон ячеек В2:В9 – значения ряда из табл.1. Под переменные a и b поиска решения отведены ячейки D2, Е2 соответственно. В ячейку F2 введена формула для минимизируемой функции цели:
=СУММКВРАЗН(B2:B9;E2+D2*A2:A9) (1)
В этой формуле использована функция СУММКВРАЗН(), вычисляющая сумму квадратов разностей соответствующих элементов двух массивов.
v Выберем команду Сервис/Поиск решения. Откроется диалоговое окно Поиск решения.
v Заполним диалоговое окно Поиск решения.
При заполнении окна Поиск решения введем абсолютную ссылку на ячейку с целевой функцией F2, в группе Равной выберем минимальному значению, так как требуется найти минимальное значение целевой функции, в поле Изменяя ячейки укажем диапазон ячеек D2:Е2.
v Далее установим параметры поиска решения, получим решение и далее повторим его с большей точностью и с меньшим допустимым отклонением и создадим отчет Excel по результатам (Рис.2.)
Microsoft Excel 12.0 Отчет по результатам | ||||
Рабочий лист: [работа3.xlsx]Лист1 | ||||
Отчет создан: 07.04.2010 14:50:32 | ||||
Целевая ячейка (Минимум) | ||||
Ячейка | Имя | Исходное значение | Результат | |
$F$2 | Целевая функция: | 10837 | 2,869047619 | |
Изменяемые ячейки | ||||
Ячейка | Имя | Исходное значение | Результат | |
$D$2 | a: | 0 | 6,011904191 | |
$E$2 | b: | 0 | 7,071428467 | |
Ограничения | ||||
НЕТ |
Рис.2.
v Найдем параметры а и b в линейной регрессионной модели с помощью статистических функций НАКЛОН() и ОТРЕЗОК(). Функция НАКЛОН() определяет коэффициент наклона линейного тренда. Ее формат записи – НАКЛОН(<массив у>;<массив t>), функция ОТРЕЗОК() определяет точку пересечения линейного тренда с осью ординат. Ее синтаксис – ОТРЕЗОК(<массив у>;<массив t>).
Аргументы этих функций:
<массив t> – это массив значений независимой наблюдаемой величины. Если аргумент <массив t> опущен, то по умолчанию полагается, что это массив из натурального ряда чисел того же ряда, как и аргумент <массив у>;
<массив у> - это массив известных значений зависимой наблюдаемой величины.
Введем формулы:
=НАКЛОН(B2:B9;A2:A9) (2)
=ОТРЕЗОК(B2:B9;A2:A9) (3)
в ячейки D4 и Е4 соответственно и сравним результаты с содержимым ячеек D2 и Е2.
v Найдем параметры а и b линейной регрессионной модели, используя команду Добавить линию тренда. Для этого:
ü построим точечный график по данным диапазона ячеек А2:В9, выделим точки графика двойным щелчком, затем щелкнем на них правой кнопкой мыши. Раскроется контекстное меню, в нем выберем команду Добавить линию тренда;
ü в раскрывшемся диалоговом окне Линия тренда на вкладке Тип выберем Линейная, затем на вкладке Параметры установим флажки Показать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации. Щелкнем кнопку ОК. (Рис.3.)
Рис.3.
Рис.4. Диаграмма с линией тренда типа Линейная
v Вычислим теоретическое значение наблюдаемой величины yt при t из ячейки А2. Для этого в ячейку С2 введем формулу:
=D2*A2+E2 (4)
Сравним результат с содержимым ячейки В2.
v Вычислим теоретическое значение yt при t из ячейки А4 с помощью функции ПРЕДСКАЗ(). Ее синтаксис - ПРЕДСКАЗ(ti;<массив y>;<массив t>). Аргумент ti - это точка данных из массива t, для которой предсказывается теоретическое значение yti. Теоретическое значение в ячейке С4 вычислим по формуле:
=ПРЕДСКАЗ(A4;B2:B9;A2:A9) (5)
Буксировкой формулы (5) вниз заполним диапазон С5:С9 новыми yt.
Сравним значения в диапазонах В4:В12 и С4:С12.
v Вычислим значения уравнения линейной регрессии для целого диапазона значений независимой переменной с помощью функции ТЕНДЕНЦИЯ(). Ее синтаксис – ТЕНДЕНЦИЯ(<массив y>;<массив t>;<новые значения t>;[<конст>]).
Аргумент <новые значения t> - это массив значений t, для которых функция ТЕНДЕНЦИЯ() возвращает соответствующие значения yt.
Новое значение зависимой переменной вычислим в ячейке В10 по формуле:
=ТЕНДЕНЦИЯ(B2:B9;A2:A9;A10) (6)
Буксировкой формулы (6) вниз заполним диапазон В11:В12 новыми значениями у.
v Найдем параметры а и b линейной регрессионной модели с помощью статистической функции ЛЕНЕЙН(). Эта функция возвращает массив значений параметров уравнения многомерной регрессии, для двумерной регрессии – параметры а и b. Ее синтаксис – ЛЕНЕЙН(<массив у>;<массив t>;[<конст>];[<статистика>]), где <статистика> - это логическое значение.
Введем в ячейки D6:Е6 формулу:
=ЛИНЕЙН(B2:B9;A2:A9) (7)
Результаты, полученные в диапазонах ячеек D2:Е2, D4:Е4, D6:Е6 и на диаграмме с линией тренда типа Линейная, сравним между собой.
v Построим нелинейную экспоненциальную модель. Она описывается уравнением 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,042 (R2лин > R2экс).
ü Результаты решения задания представлены на Рис.6.
Рис.6.
Построение полиномиальной регрессионной модели.
Исходные данные.
Требуется построить полиномиальные модели различных степеней вида 
и сравнить оценки их погрешностей. Определение коэффициентов таких уравнений осуществить средством Поиск решения.
Табл.2.
t(неделя) | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
у(продажи) | 30 | 30 | 40 | 40 | 60 | 50 | 50 | 90 | 100 |
Порядок выполнения работы.
v Подготовим начальный рабочий лист кА показано на рис.7. Заданные ряды Y и t помещены в диапазоны С7:С16 и А7:А16 соответственно. В диапазонах С7:С16, D7:D16, E7:E16 будем отображать квадраты погрешности между фактическим значением Yi и полученным из полиномов второй, третьей и четвертой степени уравнений регрессии соответственно.
Рис.7.
v Введем следующие формулы:
С7 =($B7-($B$3+$C$3*$A7+$D$3*$A7^2))^2 (12)
D7=($B7-($B$4+$C$4*$A7+$D$4*$A7^2+$E$4*$A7^3))^2 (13)
E7=($B7-($B$5+$C$5*$A7+$D$5*$A7^2+$E$5*$A7^3+$F$5*$A7^4))^2 (14)
v Cкопируем эти формулы в диапазон С8:С15, D8:D15, E8:E15 соответственно. Полученные результаты сравним между собой ряды квадратов погрешностей полиномов.
v В ячейке С16 вычислим сумму квадратов погрешностей для приближения полиномом 2-й степени, введя формулу:
С17 = СУММ (С7:С16) (15)
Скопируем эту формулу методом буксировки вправо на диапазон D17:Е17, чтобы вычислить сумму квадратов погрешностей приближений полиномами 3-й и 4-й степени. Сравним результаты в ячейках С17:Е17 между собой.
v Для вычисления коэффициентов а, b, c полинома второй степени выберем команду Сервис/Поиск решения. В диалоговом окне Сервис/Поиск решения установим целевую ячейку С17, в поле Равной установим минимальному значению, в поле Изменяя ячейки – диапазон B3:D3. После щелчка Выполнить, результаты поиска – значения коэффициентов а, b, с появляются в ячейках B3:D3.
v Аналогично предыдущему пункту, используя средство Поиск решения, определим коэффициенты а, b, c, d в ячейках В4:Е4 для приближения полиномом 3-й степени, затем то же самое для приближения полиномом 4-й степени в ячейках В5:F5 определим коэффициенты а, b, c, d, e.
v Сделаем нелинейный регрессионный анализ, используя средства деловой графики Excel, не прибегая к вычислениям, сначала для модели 2-й степени. Для этого построим график Y(t), используя ряды t и Y в ячейках А7:А16, В7:В16. Затем кликнув щелчком на этом графике правой кнопкой мыши и в появившемся контекстном меню выберем пункт Добавить линию тренда. В появившемся окне Линия тренда, в котором выберем тип уравнения аппроксимации Полиномиальная и его степень; на вкладке Параметры установим флажки Показывать уравнение на диаграмме, Поместить на диаграмму величину достоверности аппроксимации. Будет выведен график уравнения регрессии и само уравнение с числовыми значениями коэффициентов и квадрат коэффициента корреляции R^2.
v Аналогично выполним графическое построение линий тренда для полиномов 3-й, 4-й степеней с показом уравнений на графике. Сравним регрессионные модели полиномами
Результаты решения показаны на рис.8.
Рис.8.
Заключение.
В данной работе я изучила метод решения задач регрессионного анализа в Excel. Развила навыки использования команды Сервис/Подбор параметра, встроенных статистических функций, построения линейных и нелинейных уравнений регрессии и линий тренда.
С помощью команды Сервис/ Подбор параметра, функций НАКЛОН(), ОТРЕЗОК() и ЛИНЕЙН() я разными способами рассчитала значения а и b линейной регрессионной модели. Затем сравнила полученные значения, и они получились одинаковыми. По полученным данным я построила диаграмму с линией тренда типа Линейная. Также вычислила теоретическое значение yt при t с помощью функции ПРЕДСКАЗ() и новые значения у с помощью функции ТЕНДЕНЦИЯ().
Для определения параметров нелинейной экспоненциальной модели использовала формулу ЛГРФПРИБЛ, LN. Для построения экспоненциального тренда – функцию РОСТ. Затем вычислила теоретические значения yt (нелин) наблюдаемой величины, используя экспоненциальную модель и построила диаграмму с линией тренда типа Экспоненциальная.
Коэффициент корреляции в линейной и экспоненциальной модели различается на 0,0303 (R2лин > R2экс).
Во второй части данной работы я построила полиномиальные модели различных степеней. Для этого были использованы формулы . Для вычисления коэффициентов а, b, c полинома различных степеней использовала команду Сервис/Поиск решения. По полученным данным построила график Excel для модели 2-й, 3-й, 4-й степени. Сравнивая оценки погрешностей полиномонов 2-й, 3-й, 4-й степеней можно сделать вывод, что погрешность уменьшается с увеличением степени, особенно это наглядно представлено из вычисленных сумм квадратов разностей.
