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

10 11 12 13 14 15 16

2. По исходным данным выполнить регрессионный анализ:

2.1. Рассчитать параметры уравнения линейной парной регрессии для чего использовать:

2.1.1. расчет в Excel по формулам, реализующим метод наименьших квадратов;

2.1.2. расчет с помощью функции ЛИНЕЙН с расшифровкой полученных результатов;

2.1.3. графическое построения линии тренда;

2.1.4. инструмент «Регрессия».

2.2. Оценить статистическую надежность результатов регрессионного моделирования для чего построить доверительные интервалы коэффициентов регрессии по t-критерию Стьюдента

2.3. Оценить статистическую значимости коэффициентов регрессии и всего уравнения регрессии с помощью t-критерия Стъюдента и F-критерия Фишера.

2.4. Оценить качество уравнения регрессии с помощью средней ошибки аппроксимации.

2.5. Дать с помощью общего (среднего) коэффициента эластичности сравнительную оценку силы связи фактора с результатом.

2.6. Сделать итоговые выводы.

Численность безработных граждан, тысяча человек	Число заpегистpиpованных пpеступлений в pасчете на 100 тыс. чел. населения, единица
21,7
22,1
18,9
17,8
17,6
15,7
13,8
13,2
11,5
23,9
16,3

1.1.1 расчет в Excel по формулам, реализующим метод наименьших квадратов;

Решение задач с использованием формул

Параметры a и b линейной регрессии

рассчитываются с помощью метода наименьших квадратов. Для этого составим систему нормальных уравнений (1).

По исходным данным определим , , , , в расчетной таблице 1.

Таблица 1 Расчет показателей парной линейной регрессии и корреляции

№	x	y	x*y	2	2
	21,7		3602,2	470,89		162,246879	0,022609162	14,08592	17,64
	22,1		4088,5	488,41		161,569093	0,126653552	549,0074	21,16
	18,9		2664,9	357,21		166,991384	0,184336057	675,552	1,96
	17,8		3008,2	316,84		168,855297	0,000856233	0,020939	0,09
	17,6			309,76		169,19419	0,132337488	665,9398	0,01
	15,7		3375,5	246,49		172,413675	0,19807593	1813,595	3,24
	13,8		3049,8	190,44		175,633161	0,205279817	2058,15	13,69
	13,2		2059,2	174,24		176,64984	0,13237077	426,4159	18,49
	11,5		1713,5	132,25		179,530432	0,20490223	932,1073
	23,9		3178,7	571,21		158,519054	0,191872586	651,2221	40,96
	16,3		2167,9	265,69		171,396996	0,288699215	1474,329	1,44
итого	192,5		32340,4	3523,43			1,68799304	9260,426	154,68
Сред нее	17,5	169,3636	2940,036	320,3118	29566,27273	169,363636	0,153453913

Режим проверки формул

№	x	y	x*y	x2	y2
	21,7		=B2*C2	=B2*B2	=C2*C2	=$F$25+$F$24*B2	=ABS((C2-G2)/C2)	=(C2-G2)^2	=(B2-$B$14)^2
	22,1		=B3*C3	=B3*B3	=C3*C3	=$F$25+$F$24*B3	=ABS((C3-G3)/C3)	=(C3-G3)^2	=(B3-$B$14)^2
	18,9		=B4*C4	=B4*B4	=C4*C4	=$F$25+$F$24*B4	=ABS((C4-G4)/C4)	=(C4-G4)^2	=(B4-$B$14)^2
	17,8		=B5*C5	=B5*B5	=C5*C5	=$F$25+$F$24*B5	=ABS((C5-G5)/C5)	=(C5-G5)^2	=(B5-$B$14)^2
	17,6		=B6*C6	=B6*B6	=C6*C6	=$F$25+$F$24*B6	=ABS((C6-G6)/C6)	=(C6-G6)^2	=(B6-$B$14)^2
	15,7		=B7*C7	=B7*B7	=C7*C7	=$F$25+$F$24*B7	=ABS((C7-G7)/C7)	=(C7-G7)^2	=(B7-$B$14)^2
	13,8		=B8*C8	=B8*B8	=C8*C8	=$F$25+$F$24*B8	=ABS((C8-G8)/C8)	=(C8-G8)^2	=(B8-$B$14)^2
	13,2		=B9*C9	=B9*B9	=C9*C9	=$F$25+$F$24*B9	=ABS((C9-G9)/C9)	=(C9-G9)^2	=(B9-$B$14)^2
	11,5		=B10*C10	=B10*B10	=C10*C10	=$F$25+$F$24*B10	=ABS((C10-G10)/C10)	=(C10-G10)^2	=(B10-$B$14)^2
	23,9		=B11*C11	=B11*B11	=C11*C11	=$F$25+$F$24*B11	=ABS((C11-G11)/C11)	=(C11-G11)^2	=(B11-$B$14)^2
	16,3		=B12*C12	=B12*B12	=C12*C12	=$F$25+$F$24*B12	=ABS((C12-G12)/C12)	=(C12-G12)^2	=(B12-$B$14)^2
итого	=СУММ(B2:B12)	=СУММ(C2:C12)	=СУММ(D2:D12)	=СУММ(E2:E12)	=СУММ(F2:F12)	=СУММ(G2:G12)	=СУММ(H2:H12)	=СУММ(I2:I12)	=СУММ(J2:J12)
средне	=B13/11	=C13/11	=D13/11	=E13/11	=F13/11	=G13/11	=H13/11

Система нормальных уравнений составит:

Решив систему, получим: a = 199,02; b = -1,69

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

Параметры уравнения можно определить и по следующим формулам:

-1,69447

= 169,3636+1,69447*17,5=199,0168

Величина коэффициента регрессии b = -1,69447

означает, что с ростом численности безработных граждан на тысячу человек общий коэффициент зарегистрированных преступлений в расчете на 100 тыс. чел. населения, увеличится в среднем в 1,69447 раз.

Поле корреляции

1.1.2 расчет с помощью функции ЛИНЕЙН с расшифровкой полученных результатов;

1.2.1 Параметры линейной регрессии

можно определить с помощью встроенной статистической функции ЛИНЕЙН MS Excel. Порядок вычисления следующий:

1) ввожу исходные данные (рисунок 1).

2) выделяю область пустых ячеек 5´2 (5 строк, 2 столбца) с целью вывода результатов регрессионной статистики или область 1´2 – для получения только оценок коэффициентов регрессии;

3) активизирую Мастер функций любым из способов:

а) в главном меню выбираю Вставка / Функция;

б) на панели инструментов Стандартная щелкаю по кнопке Вставка функции;

4) в окне «Категория» выбераю Статистические, в окне «Функция» – ЛИНЕЙН. Щелкаю по кнопке ОК (рисунок 2);

Рисунок 2 Диалоговое окно Мастер функций

5) заполняю аргументы функции (рисунок 3):

Рисунок 3 Диалоговое окно Аргументы функции

Щелкаю по кнопке ОК;

6) в левой верхней ячейке выделенной области появился первый элемент итоговой таблицы.

Чтобы раскрыть всю таблицу, нажимаю на клавишу <F2>, а затем – на комбинацию клавиш <CTRL> + <SHIFT> + <ENTER>.

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

Значение коэффициента b	Значение коэффициента a
Среднеквадратическое отклонение b	Среднеквадратическое отклонение a
Коэффициент детерминации R2	Среднеквадратическое отклонение y
F – статистика	Число степеней свободы
Регрессионная сумма квадратов	Остаточная сумма квадратов

Результаты вычислений функции ЛИНЕЙН представлены на рисунке 4.

Рисунок 4 Результаты вычислений функции ЛИНЕЙН

1.1.3 графическое построение линии тренда;

x	y
11,5
13,2
13,8
15,7
16,3
17,6
17,8
18,9
21,7
22,1
23,9

Построим график этой зависимости (можно График, можно Точечная)

Есть несколько базовых функций (линейная, логарифм, экспонента, полиномы,…). Если у нас есть неизвестно какая зависимость, Excel может нам сказать, на какую базовую функцию она похожа больше всего.

Для этого поступим так. Построим график этой зависимости (можно График, можно Точечная), воспользуемся пунктом Меню=>Диаграмма=>Добавить линию тренда. Выберем тип тренда. Excel построит на диаграмме еще один график, показывающий, к какой зависимости ближе всего исследуемые данные.

Например, если мы выберем тип тренда “Линейная”, то Excel построит линейную функцию, которая лучше всего описывает исследуемую зависимость. На диаграмме это будет выглядеть так: это будет такая прямая линия, что график построенной функции отклоняется от нее наименьшим образом.

Примечание: Excel использует метод наименьших квадратов. Т.е. Excel строит тренд с условием, что сумма квадратов расстояний от реальных точек до линии тренда минимальна для всех линий данного типа.

Как понять, что за тренд построен? Очень просто. Надо вывести его уравнение (“Формат линии тренда”, вкладка “Параметры”, соответствующая галка). И вы узнаете приблизительное уравнение зависимости между вашими данными.

Как понять, какой тип тренда надо использовать? На той же вкладке “параметры” окна “формат линии тренда” включаем галку “поместить на диаграмму величину достоверности аппроксимации (R^2)”. После этого в подписи к тренду появится эта самая величина достоверности. Чем она больше, тем лучше построенный тренд.