Регрессионный анализ с использованием средств MS Excel

 

 

 

 

 

 

 

 

Расчетная работа №2 по дисциплине «Эконометрика»

Тема: «Регрессионный анализ с использованием средств MS Excel»

 

Вариант 10

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Содержание.

 

1. Руководство по подключению необходимого для работы средства MS Excel  - надстройки «Пакет анализа»;
2. Задание 2.1;
3. Выполнение задания 2.1;
4. Задание 2.2;
5. Выполнение задания 2.2;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1. Руководство по подключению необходимого для работы средства MS Excel  - надстройки «Пакет анализа».

 

Для того чтобы воспользоваться  возможностями Excel при построении линейных регрессионных моделей, необходимо подключить надстройку «Пакет анализа». Чтобы сделать это, заходим в меню «Параметры».

 

 

В меню «Параметры Excel» переходим на вкладку «Надстройки» и выбираем из списка неактивных надстройку «Пакет анализа». Нажимаем кнопку «Перейти».

 

В открывшемся меню ставим галочку  напротив строки «Пакет анализа» и  нажимаем ОК.

 

 

Теперь на вкладке «Данные» находится  кнопка «Анализ данных»

 

 

Для того чтобы воспользоваться  средствами пакета, нажмем на кнопку. В открывшемся меню выберем строку «Регрессия» и нажмем ОК.

 

 

Откроется окно для входящих данных. Остается лишь внести сами данные и  нажать кнопку ОК. Теперь можно приступать к выполнению задания.

 

2. Задание 2.1.

 

Задание:

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

а) государственных расходов на образование (Y) от валового внутреннего продукта (X);

б) среднедушевых расходов на образование (Y/P) от валового внутреннего продукта на душу населения (X/P).

вариант индивидуального заданиясодержатся в таблице 1.  

 

Таблица 1

Номер варианта	Номера стран
10	19-33

 

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

 

Таблица 2

№ п/п	Страна	Y	X	Y/P	X/P
19	Саудовская Аравия	6,4	115,97	765	13855
20	Бельгия	7,15	119,49	725	12119
21	Швеция	11,22	124,15	1350	14940
22	Австралия	8,66	140,98	592	9643
23	Аргентина	5,56	153,85	205	5686
24	Нидерланды	13,41	169,38	948	11979
25	Мексика	5,46	186,33	81	2765
26	Испания	4,79	211,78	128	5658
27	Бразилия	8,92	249,72	73	2030
28	Канада	18,9	261,41	789	10919
29	Италия	15,95	395,52	280	6934
30	Великобритания	29,9	534,97	534	9562
31	Франция	33,59	655,29	625	12201
32	ФРГ	38,62	815	627	13239
33	Япония	61,61	1040,45	528	8909

 

Y – государственные расходы на образование (млрд. $)

Х – валовой внутренний продукт (млрд. $)

Y/P – среднедушевые государственные  расходы на образование (долл.)

X/P-вВП  на душу населения (долл.)

 

 

 

 

 

 

 

 

3. Выполнение задания 2.1.

 

Выполнение:

 

Перенесем исходные данные из таблицы в MS Excel.

 

 

Используем  «Регрессию».

 

Для пункта а) задания 2.1 выбираем в качестве входного интервала Y столбец Y с шапкой (государственные расходы на образование). В качестве входного интервала X – столбец X, тоже с шапкой (валовой внутренний продукт). Для пункта б) задания 2.1 выбираем в качестве входного интервала Y/P столбец Y/P с шапкой (среднедушевые государственные  расходы на образование). В качестве входного интервала X/P – столбец X/P, тоже с шапкой (вВП  на душу населения). Напротив «Метки» ставим галочку. В качестве выходного интервала можно выбрать любую ячейку – это будет верхняя левая ячейка интервала, в который будут помещаться результаты вычислений. Теперь мы можем рассматривать полученные результаты и анализировать их.

 

Результат выполнения пункта а):

 

 

Результат выполнения пункта б):

 

 

 

 

 

 

 

 

 

 

 

4. Задание 2.2.

 

Задание:

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

Y- урожайность  зерновых культур (ц/га);

X1 – число  колесных тракторов на 100 га;

X2 – число  зерноуборочных комбайнов на  100 га;

X3 – число  орудий поверхностной обработки  почвы на 100 га;

X4 – количество  удобрений, расходуемых на гектар(т/га);

X5 – количество  химических средств защиты растений, расходуемых на гектар (ц/га)

 

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

 

	Y	X1	X2	X3	X4	X5
1	9,7	1,59	0,26	2,05	0,32	0,14
2	8,4	0,34	0,28	0,46	0,59	0,66
3	9	2,53	0,31	2,46	0,3	0,31
4	9,9	4,63	0,4	6,44	0,43	0,59
5	9,6	2,16	0,26	2,16	0,39	0,16
6	8,6	2,16	0,3	2,69	0,37	0,17
7	12,5	0,68	0,29	0,73	0,42	0,23
8	7,6	0,35	0,26	0,42	0,21	0,8
9	6,9	0,52	0,24	0,49	0,2	0,8
11	9,7	1,78	0,3	3,19	0,73	0,17
12	10,7	2,4	0,32	3,3	0,25	0,14
13	12,1	9,36	0,4	11,51	0,39	0,38
14	9,7	1,72	0,28	2,26	0,82	0,17
15	7	0,59	0,29	0,6	0,13	0,35
16	7,2	0,28	0,26	0,3	0,09	0,15
17	8,2	1,64	0,29	1,44	0,2	0,08
18	8,4	0,09	0,22	0,05	0,43	0,2
19	13,1	0,08	0,25	0,03	0,73	0,2
20	8,7	1,36	0,26	0,17	0,99	0,42

 

Требуется:

1. Провести корреляционный анализ: проанализировать связи между результирующей переменной и факторными признаками по корреляционной матрице, выявить мультиколлинеарность.
2. Построить уравнения регрессии со значимыми коэффициентами, используя пошаговый алгоритм регрессионного анализа.
3. Выбрать лучшую из полученных регрессионных моделей, основываясь на анализе значений коэффициентов детерминации, остаточных дисперсий, а также с учетом результатов экономической интерпретации моделей.

 

5. Выполнение задания 2.2.

 

Выполнение:

 

Перенесем исходные данные из таблицы в MSExcel.

 

 

Одним из основных препятствий эффективного применения множественного регрессионного анализа является мультиколлинеарность. Она связана с линейной зависимостью между аргументами . В результате мультиколлинеарности матрица парных коэффициентов корреляции становится слабо обусловленной, т.е. ее определитель близок к нулю. Это приводит к неустойчивости оценок коэффициентов регрессии, завышению дисперсии, оценок этих коэффициентов. Кроме того, мультиколлинеарность приводит к завышению значения множественного коэффициента корреляции.

На практике о наличии мультиколлинеарности обычно судят по матрице парных коэффициентов корреляции. Если один из элементов матрицы R больше 0,8, т.е. > 0,8, то считают, что имеет место мультиколлинеарность, и в уравнение регрессии следует включать один из показателей — или .

Чтобы избавиться от этого негативного  явления, обычно используют алгоритм пошагового регрессионного анализа или строят уравнение регрессии на главных  компонентах.

 

С целью предварительного анализа  взаимосвязи показателей построим  средствами Excel матрицу парных коэффициентов корреляции. Для этого выберем в «Анализе данных» пункт «Корреляция»:

 

В открывшемся окне выбираем в качестве входного интервала всю область  значений с шапками таблицы, отмечаем «Метки в первой строке», и определяем выходной интервал:

 

 

Нажимаем кнопку ОК и получаем вот  такую корреляционную матрицу:

 

 

Анализ матрицы парных коэффициентов  корреляции показывает, что результативный признак наиболее тесно связан с  показателем — числом орудий поверхностной обработки почвы на 100 га (= 0,42).

В то же время связь между аргументами  достаточно тесная. Так, существует практически  функциональная связь между числом колесных тракторов () и числом орудий поверхностной обработки почвы () = 0,98.

О наличии мультиколлинеарности свидетельствуют также коэффициенты корреляции = 0,85 и = 0,88:

 

 

Это означает, что полное уравнение  регрессии неприемлемо.  Реализуем  алгоритм пошагового регрессионного анализа  с исключением переменных. Так как мультиколлинеарны, то разобьем их исходное признаковое пространство на 3 модели:

 

1. ;
2. ;
3. ;

 

И построим по каждой модели матрицу  регрессионного анализа:

 

 

 

 

Используем  «Регрессию»:

 

 

Полученная модель значима по F-критерию (α=0,08), но фактор не значим, он исключается из модели:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Полученная модель значима по F-критерию (α=0,08), но фактор не значим, он исключается из модели:

 

 

 

 

 

 

Полученная модель значима по F-критерию (α=0,08), но фактор не значим, он исключается из модели:

 

В ходе анализа данного этапа было получено 3 уравнения:

 

1. ;
2. ;
3. ;

 

Проанализируем  каждое из них.

 

 

 

 

Модель значима по F-критерию, и  оба коэффициента так же значимы. Следовательно, модель стабильна, и  мы можем переходить к следующей  модели.

 

 

 

Модель значима по F-критерию, и  оба коэффициента так же значимы. Переходим к последней модели.

 

 

 

 

 

Модель значима по F-критерию, и  оба коэффициента значимы.

 

Основываясь на анализе значений коэффициентов детерминации, остаточных дисперсий, а также с учетом результатов экономической интерпретации моделей необходимо выбрать лучшую из полученных регрессионных моделей. Лучшей будет признана модель с наивысшим значением коэффициента детерминации, и, при этом, самом низком значении стандартной ошибки. Для того чтобы выяснить, какая же модель лучшая, обратимся к данным пунктов «Регрессионная статистика» полученных моделей: