Практическая работа П3.2. Использование возможностей пакета «Анализ данных» MS Excel для моделирования прогнозов на основе множественного регрессионного анализа

Имеется 59 результатов наблюдений следующих переменных, представленных в табл. П3.2.

Таблица П3.2

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

Цена квартиры у, тыс. руб.

Площадь квартиры Х(, м2

Площадь кухни х2, м2

Удаленность от метро х3, мин

Тип дома х4

Этажность х5

1

2

3

4

5

6

3800

45,4

6

5

1

1

3950

50

5

10

0

0

4000

30

7

10

0

0

4000

40

10

2

1

1

4250

36,1

5

10

1

1

4250

46,3

5

5

1

1

4350

47

5

5

0

0

4450

45

5

15

0

0

4490

53,2

10

15

1

1

4500

45

8

5

1

1

4500

45

6

5

1

1

4500

45

7

10

0

0

4500

45

10

15

0

0

4500

44

9

3

0

0

4530

45,9

7

10

1

1

4600

55

10

5

1

1

4690

42

6

10

0

0

4750

49

8

4

1

1

4800

47,2

8

5

1

1

4850

47

7

15

1

1

4870

51

6

10

0

0

Цена квартиры у, тыс. руб.

Площадь квартиры xv м2

Площадь кухни х2, м2

Удаленность от метро х3, мин

Тип

дома х4

Этажность х5

4880

58

6

10

1

1

4930

64

12

5

0

0

4989

38

6

7

1

1

4990

66

10

5

1

1

5000

32,5

8

10

0

0

5000

61

9

10

1

1

5000

87

30

15

1

1

5000

59,2

11

15

0

0

5000

65

8

10

1

0

5150

46

6

10

0

0

5200

54,1

10

5

0

1

5200

54

10

10

0

1

5200

51,9

8

10

1

0

5300

53

9

10

0

1

5300

49,2

12

2

0

1

5400

52

9

10

1

0

5400

59,4

9

10

1

1

5400

48,7

10

10

1

1

5400

50,4

13

15

1

1

5420

48

10

10

0

1

5450

55,2

8

10

1

0

5500

70

15

10

1

0

5500

50,3

12

10

1

1

5500

37

11

5

1

1

5500

75,9

12

15

0

1

5500

47,5

7

15

1

1

5550

45,2

8

10

1

1

5570

31

5

15

0

1

5650

49

7

10

1

1

5678

58,8

9

10

1

0

5699

66

11

2

0

1

5700

57,3

10

5

1

1

5700

62,2

12

5

1

0

5700

53

11

10

1

1

5760

61

9

5

1

1

5800

50,7

8

5

0

1

Цена квартиры у, тыс. руб.

Площадь квартиры х{, м2

Площадь кухни х2, м2

Удаленность от метро х3, мин

Тип

дома х4

Этажность х5

5800

М

8

10

1

1

5800

50,1

7

5

1

1

Постройте прогнозное уравнение множественной регрессии линейного вида по имеющимся данным, для чего необходимо выполнить следующие действия:

  • • оценить качество подбора множественного уравнения регрессии к имеющимся данным;
  • • оценить стандартную ошибку и стандартные ошибки коэффициентов регрессии;
  • • проверить значимость регрессии в целом;
  • • проверить значимость коэффициентов регрессии.

Решение

Пошаговый процесс построения уравнения множественной регрессии вида Y = a + bl-xl+b2-x2 + b3'X3 + bi-xi+b5'X5 + e средствами пакета MS Excel «Анализ данных» приведен на рис. П3.10—П3.13.

ПЗ. 10. Использование Пакета анализа на шаге 1
 Рис. ПЗ. 11. Заполнение сценария расчета регрессии Пакета анализа на шаге 2

Рис. ПЗ. 10. Использование Пакета анализа на шаге 1 Рис. ПЗ. 11. Заполнение сценария расчета регрессии Пакета анализа на шаге 2

ИЗ. 12. Вывод итогов регрессионного анализа по модели

Рис. ИЗ. 12. Вывод итогов регрессионного анализа по модели

и ее анализ на шаге 3

Вывод остатков но итогам регрессионного анализа

Рис. 113.13. Вывод остатков но итогам регрессионного анализа

по модели на шаге 4

В результате построено уравнение регрессии у = 3840,05 + 18,4 • хх + + 1,77 - х2 + 10,32 • х3 + 0,72 • х4 + 225,52 • х5 средствами пакета MS Excel «Анализ данных», параметры которого могут быть интерпретированы следующим образом.

Коэффициенты регрессии означают, что при увеличении каждого из них на единицу своего измерения происходит изменение в среднем цены квартиры на тысячи рублей пропорционально полученным коэффициентам регрессии. Свободный член уравнения регрессии а = 3840,05 свидетельствует о наличии неучтенных факторов в модели у = 3840,05 + 18,4 • хх + + 1,77 • *2 + 10,32 • х3 + 0,72 • х4 + 225,52 • х5.

Коэффициент детерминации R2 = 0,18 и множественный коэффициент корреляции R = 0,42 (см. рис. П3.12) говорят о наличии слабой взаимосвязи между этими признаками. Скорректированный коэффициент R2 = 0,1 показывает, какая доля вариации (0,1) цены квартиры обусловлена вариацией объемов факторных признаков в целом с учетом степеней свободы.

Рассчитаны как стандартные ошибки уравнения множественной регрессии Y = 3840,05 + 18,4 • хх + + 1,77 • х2 + 10,32 • х3 + 0,72 • х4 + 225,52 • х5, так и стандартные ошибки коэффициентов регрессии. Ими можно воспользоваться на рис. П3.12 для интерпретации результатов расчетов, полученных в результате проведенного регрессионного анализа.

В качестве меры рассеивания фактического значения у относительно теоретического значения у (находится по уравнению множественной регрессии) используется стандартная ошибка уравнения регрессии, которая равна 5^ «511,05 тыс. руб. Любое стандартное отклонение иногда называют стандартной ошибкой соответствующего коэффициента, они равны Sb{ = 8,54; = 24,75; Sh =17,9; SbA = 153,067; = 157,28; Sa = 384,93

(см. рис. П3.12). Это достаточно много, и модель нуждается в коррекции.

Оценка статистической значимости модели в целом по критерию Фишера проведена по встроенной функции =ЕРАСПОБР (0,05; 5; 59—5—1) в Excel (см. рис. 113.12) с уровнем значимости 0,05. Построенная модель множественной регрессии у-3840,05 + 18,4*^ +1,77-дг2 + 10,32*х3 +0,72-х4 +225,52*х5 статистически значима в целом с уровнем значимости 0,05:

Для определения статистической значимости коэффициентов регрессии необходимо рассчитать ^-статистики Стьюдента, это сделано с помощью встроенной функции СТЫОДРАСПОБР (рис. П3.12). В итоге ?табл < ?факт, коэффициент регрессии (рис. П3.15) статистически значим и рассматриваемая основная гипотеза о равенстве параметров регрессии нулю отвергается (НО): bj = 0 — коэффициент незначим; (HI): bj ф 0 — коэффициент значимый с уровнем значимости 0,05.

Проведенный Е-тест и критерий Стыодента на улучшение качества оценивания модели на рис. 113.14 показали необходимость исключения из модели статистически незначимых факторов х2, х3, х4, х5 по критерию Стьюдента.

ПЗ. 14. Получение окончательного вида уравнения регрессии

Рис. ПЗ. 14. Получение окончательного вида уравнения регрессии

Проверка на значимость окончательного вида уравнения

Рис. 113.15. Проверка на значимость окончательного вида уравнения

регрессии

В итоге на рис. П3.14, П3.15 построено скорректированное парное уравнение регрессии г/ = 4035,99 + 19,6-xt средствами MS Excel «Анализ данных».

 
Посмотреть оригинал
< Пред   СОДЕРЖАНИЕ   ОРИГИНАЛ     След >