Контрольная работа - Построение регрессионных моделей средствами excel - файл n1.doc

Контрольная работа - Построение регрессионных моделей средствами excel
скачать (366 kb.)
Доступные файлы (1):
n1.doc366kb.19.11.2012 20:48скачать

n1.doc



ПОСТРОЕНИЕ РЕГРЕССИОННЫХ МОДЕЛЕЙ средствами 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.Последовательность выполнения задания

Рис.1.

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

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

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

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

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

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

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

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

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

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

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



Рис.3.



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



=D2*A2+E2 (4)

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

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

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

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

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

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

=ТЕНДЕНЦИЯ(B2:B9;A2:A9;A10) (6)

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

Введем в ячейки D6:Е6 формулу:

=ЛИНЕЙН(B2:B9;A2:A9) (7)

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

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

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

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

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

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

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

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

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

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





Рис.6.



Построение полиномиальной регрессионной модели.

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

Требуется построить полиномиальные модели различных степеней вида


и сравнить оценки их погрешностей. Определение коэффициентов таких уравнений осуществить средством Поиск решения.

Табл.2.

t(неделя)

1

2

3

4

5

6

7

8

9

у(продажи)

30

30

40

40

60

50

50

90

100

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



Рис.7.



С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)

С17 = СУММ (С7:С16) (15)

Скопируем эту формулу методом буксировки вправо на диапазон D17:Е17, чтобы вычислить сумму квадратов погрешностей приближений полиномами 3-й и 4-й степени. Сравним результаты в ячейках С17:Е17 между собой.

Результаты решения показаны на рис.8.

Рис.8.




Заключение.

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

С помощью команды Сервис/ Подбор параметра, функций НАКЛОН(), ОТРЕЗОК() и ЛИНЕЙН() я разными способами рассчитала значения а и b линейной регрессионной модели. Затем сравнила полученные значения, и они получились одинаковыми. По полученным данным я построила диаграмму с линией тренда типа Линейная. Также вычислила теоретическое значение yt при t с помощью функции ПРЕДСКАЗ() и новые значения у с помощью функции ТЕНДЕНЦИЯ().

Для определения параметров нелинейной экспоненциальной модели использовала формулу ЛГРФПРИБЛ, LN. Для построения экспоненциального тренда – функцию РОСТ. Затем вычислила теоретические значения yt (нелин) наблюдаемой величины, используя экспоненциальную модель и построила диаграмму с линией тренда типа Экспоненциальная.

Коэффициент корреляции в линейной и экспоненциальной модели различается на 0,0303 (R2лин > R2экс).

Во второй части данной работы я построила полиномиальные модели различных степеней. Для этого были использованы формулы . Для вычисления коэффициентов а, b, c полинома различных степеней использовала команду Сервис/Поиск решения. По полученным данным построила график Excel для модели 2-й, 3-й, 4-й степени. Сравнивая оценки погрешностей полиномонов 2-й, 3-й, 4-й степеней можно сделать вывод, что погрешность уменьшается с увеличением степени, особенно это наглядно представлено из вычисленных сумм квадратов разностей.

Учебный материал
© bib.convdocs.org
При копировании укажите ссылку.
обратиться к администрации