Регрессионный анализ в среде Excei

Автор работы: Пользователь скрыл имя, 24 Августа 2013 в 13:11, курсовая работа

Краткое описание

Пример 9.3. Из теоретических соображений сделано предположениие, что зависимость показателя Y от переменной X (табл. 9.8) имеет форму многочлена третьей степени.
1) построить уравнение нелинейной регрессии ;
2) оценить значимость уравнения регрессии с уровнем 0,01;
3) оценить значимость коэффициентов уравнения регрессии с уровнем

Содержание

1. §1. Парная регрессия………………………………………………………3
2. §2. Множественная линейная регрессия………………………………….5
3. §3. Нелинейные модели…………………………………………………...7
4. Примеры построения уравнений регрессии……………………………...8

Скачать полностью (205.57 Кб) Сколько стоит заказать работу?
Прикрепленные файлы: 1 файл

Регрессионный анализ в среде Excel.doc

— 546.50 Кб (Скачать документ)

Содержание

  1. §1.  Парная регрессия………………………………………………………3

  1. §2. Множественная линейная регрессия………………………………….5

  1. §3.  Нелинейные модели…………………………………………………...7

  1. Примеры построения уравнений регрессии……………………………...8

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Регрессионный анализ

§1.  Парная регрессия

Пусть при изучении зависимости  показателя Y от значений показателя X в результате наблюдений получена таблица парных значений , i = 1, …, n.

Линейное уравнение регрессии имеет вид

 

                                                 (1.1)

 

Коэффициенты b1 и b0  уравнения (9.1) вычисляются по формулам:

 

,        (1.2)

где

 

Для проверки значимости модели парной регресии используется F-критерий:

                                        (1.3)

Уравнение (9.1) значимо, если где значение F-критерия Фишера-Снедекора с уровнем значимости α с k1 = 1 и k2 = n – 2 степенями свободы.

Значимость коэффициентов регрессии проверяется с помощью t- критерия Стьюдента:

,    ,                                          (1.4)

,      ,                                           (1.5)

  .                              (1.6)

 

Параметр модели признается статистически  значимым, если  
t tкр(α; n – 2), где α — уровень значимости; n – 2 — число степеней свободы.

Доверительные интервалы для коэффициентов  регрессии βi, (i = 0, 1) имеют вид:

,                      (1.7)

.                     (1.8)

 

Значение tкр(α; n – 2) определяется по таблице распределения Стьюдента, или с помощью функции СТЬЮДРАСПОБР(α;n-2) программы Excel.

Доверительный интервал для условного  математического ожидания зависимой переменной Mx(Y) имеет вид

 

,                  (1.9)

 

где — оценка дисперсии групповой средней yx, вычисляется по формуле

.

 

Доверительный интервал для индивидуального значения зависимой переменной y0, соответствующего значению x0 имеет вид

 

,                  (1.10)

 

где вычисляется по формуле

.

 

Если уравнение  парной регрессии имеет вид  , то   доверительный интервал для условного математического ожидания зависимой переменной Mx(Y) имеет вид

 

,                  (1.11)

где вычисляется по формуле

.

                                              

 

§2. Множественная линейная регрессия

Пусть при изучении зависимости  переменной Y от нескольких переменных X1, X2, …, Xm получены наблюденные значения yi и xi,1, xi,2, …, xi,m, i = 1, 2, …, n; m — число объясняющих переменных.

Уравнение множественной линейной регрессии имеет вид:

 

                                            (2.1)

 

Для определения коэффициентов b0, b1, …, bm нужно решить систему линейных уравнений (систему нормальных уравнений метода наименьших квадратов):

         (2.2)

 

В программе Excel в процедурах и функциях регрессионного анализа вычисляются следующие характеристики регрессионной зависимости:

Сумма квадратов  отклонений фактических значений зависимой переменной от её выборочного среднего и её число степеней свободы:

  .                             (2.3)

 

Сумма квадратов  отклонений теоретических значений зависимой переменной от её выборочного среднего и её число степеней свободы:

,   .                           (2.4)

 

Сумма квадратов  отклонений фактических значений зависимой переменной от её теоретических значений и её число степеней свободы:

,   .                         (2.5)

 

Справедливо равенство: .

Средний квадрат регрессии:

.                                              (2.6)

Средний квадрат остатков:

.                                              (2.7)

Множественный коэффициент корреляции:

.                                              (2.8)

 

 

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

.                                              (2.9)

 

Нормированный R2:

.                                              (2.10)

 

Здесь m — число факторов, включенных в модель (т.е. оставленных после исключения несущественных факторов).

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

                                        (2.11)

Уравнение (2.3) значимо, если где — значение критерия Фишера-Снедекора с уровнем значимости α с k1 = m – 1 и k2 = n – m степенями свободы.

Стандартная ошибка:

.                                              (2.12)

Стандартные ошибки SE0, SE1, …, SEm     выборочных коэффицинтов регрессии b0, b1, …, bm вычисляются по формулам:

 

,  l = 0, 1, …, m,                          (2.13)

где обозначает l-й диагональный элемент матрицы, являющейся обратной к матрице коэффициентов системы уравнений (9.17):

 

.              (2.14)

§3. Нелинейные модели

 

Рассмотрим некоторые из наиболее часто встречающихся видов нелинейной регрессии:

  1. степенная
  2. логарифмическая
  3. экспоненциальная
  4. полиномиальная
  5. гиперболическая
  6. степенная множественная .

 

Первые четыре вида уравнений регрессии  можно построить в программе Excel с помощью функции «Диаграмма–Добавить линию тренда», если уже построена диаграмма (типа «Точечная», «Графики» и «Гистограмма») по имеющейся на рабочем листе таблице исходных данных.

Примеры построения уравнений регрессии

Пример 9.1.  1) Построить уравнение регрессии по данным из табл. 9.1.

                                             Таблица 9.1

i

xi

yi

i

xi

yi

i

xi

yi

1

0,43

3,61

11

0,32

3,55

21

0,52

3,65

2

0,25

3,34

12

0,79

4,42

22

0,38

3,87

3

0,52

3,41

13

0,70

3,54

23

0,32

3,83

4

0,63

4,29

14

0,95

4,43

24

0,34

3,35

5

0,01

3,43

15

0,60

3,98

25

0,28

3,87

6

0,91

4,42

16

0,98

4,17

26

0,91

3,70

7

0,41

3,81

17

0,75

4,08

27

0,90

3,61

8

0,45

3,39

18

0,69

3,71

28

0,60

3,90

9

0,05

4,00

19

0,46

3,83

29

0,45

3,44

10

0,44

3,92

20

0,63

3,72

30

0,84

3,72


 

 

2) Проверить значимость коэффициентов регрессии для уровня  
α = 0,05.

3) Построить доверительный интервал  для генерального коэффициента регрессии βyx с уровнем значимости α = 0,05.

4) Проверить значимость уравнения  регрессии с уровнем значимости α = 0,05.

5) Построить доверительные границы  для уравнения регрессии с уровнем значимости α = 0,05 и построить соответствующие графики.                                      

Решение (номер пункта решения соответствует номеру пункта задачи).

1) Запишите исходные данные в диапазон А1:В31 (табл. 9.3). В ячейках А1:С1 и D2:D13 введите для наглядности указанные обозначения.

В ячейку Е2 введите формулу

 

=(СУММ(A2:A31)*СУММ(B2:B31)-30*СУММПРОИЗВ(A2:A31;B2:B31))/ 
(СУММ(A2:A31)^2-30*СУММКВ(A2:A31))

 

В ячейку Е3 введите формулу

 

=СРЗНАЧ(B2:B31)-E2*СРЗНАЧ(A2:A31).

 

В ячейках Е2, Е3 мы получим коэффициенты уравнения регрессии  
b1 = 0,5989;  b0 = 3,47. Уравнение регрессии имеет вид

 

.

 

Чтобы выполнить задания 2) и 3) в  ячейки E4:E16 введите формулы, как показано в табл. 9.2, являющейся частью табл. 9.3.

После ввода формул в ячейку Е7 и  Е12 нажимайте комбинацию клавиш Ctrl + Shift + Enter, так используются формулы массива.

Для просмотра правильности ввода  формул в программе Excel  выполните команду «Сервис–Параметры» и поставьте флажок в строке «формулы».

 

Таблица 9.2

 

 

D

E

4

xср=

=СРЗНАЧ(A2:A31)

5

yср=

=СРЗНАЧ(B2:B31)

6

s2ост=

=СУММКВРАЗН(C2:C31;B2:B31)/28

7

sb1=

=КОРЕНЬ(E6/СУММ((A2:A31-E4)^2))

8

sb0=

=КОРЕНЬ(E6*СУММКВ(A2:A31)/(30*СУММКВ(A2:A31)-СУММ(A2:A31)^2))

9

tb1=

=ABS(E2)/E7

10

tb0=

=ABS(E3)/E8

11

tкрит=

=СТЬЮДРАСПОБР(0,05;28)

12

F=

=28*СУММ((C2:C31-E5)^2)/СУММ((C2:C31-B2:B31)^2)

13

Fкрит=

=FРАСПОБР(0,05;1;28)

14

Дов.инт. для betayx

  

15

нижн.гран.

=E2-E11*E7

16

верх.гран.

=E2+E11*E7


 

 

Результаты вычислений приведены  в табл.9.3.

Таблица 9.3

 

A

B

C

D

E

1

x

y

yтеор

    

2

0,43

3,61

3,727561

b1=

0,598907286

3

0,25

3,34

3,619757

b0=

3,47

4

0,52

3,41

3,781462

xср=

0,55

5

0,63

4,29

3,847342

yср=

3,80

6

0,01

3,43

3,47602

s2ост=

0,081533115

7

0,91

4,42

4,015036

sb1=

0,205669646

8

0,41

3,81

3,715582

sb0=

0,124615553

9

0,45

3,39

3,739539

tb1=

2,911986764

10

0,05

4,00

3,499976

tb0=

27,84588604

11

0,44

3,92

3,73355

tкрит=

2,048407115

12

0,32

3,55

3,661681

F=

8,479666915

13

0,79

4,42

3,943167

Fкрит=

4,195971707

14

0,70

3,54

3,889266

Дов.инт. для betayx

15

0,95

4,43

4,038992

нижн.гран.

0,177612121

16

0,60

3,98

3,829375

верх.гран.

1,020202452

17

0,98

4,17

4,05696

    

18

0,75

4,08

3,919211

    

19

0,69

3,71

3,883277

    

20

0,46

3,83

3,745528

    

21

0,63

3,72

3,847342

    

22

0,52

3,65

3,781462

    

23

0,38

3,87

3,697615

    

24

0,32

3,83

3,661681

    

25

0,34

3,35

3,673659

    

26

0,28

3,87

3,637725

    

27

0,91

3,70

4,015036

    

28

0,90

3,61

4,009047

    

29

0,60

3,90

3,829375

    

30

0,45

3,44

3,739539

    

31

0,84

3,72

3,973113

    

Информация о работе Регрессионный анализ в среде Excei