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)”. После этого в подписи к тренду появится эта самая величина достоверности. Чем она больше, тем лучше построенный тренд.
Получаем тип тренда полиномиальная аппроксимация со степенью 2
Y= -0,6367x2 +20,931х+7,0255
R2=0,1318