Рассчитать параметры парного линейного уравнения регрессии с помощью MS Excel, а также проанализировать качество построенной модели, используя коэффициент парной корреляции, коэффициент детерминации и среднюю ошибку аппроксимации. Задание 1. Построить линейное уравнение парной регрессии y по x. (Решение → 40015)

Заказ №44282

Рассчитать параметры парного линейного уравнения регрессии с помощью MS Excel, а также проанализировать качество построенной модели, используя коэффициент парной корреляции, коэффициент детерминации и среднюю ошибку аппроксимации. Задание 1. Построить линейное уравнение парной регрессии y по x. 2. Рассчитать линейный коэффициент парной корреляции, коэффициент детерминации и среднюю ошибку аппроксимации. 3. Оценить статистическую значимость уравнения регрессии в целом и отдельных параметров регрессии и корреляции с помощью F-критерия Фишера и t-критерия Стьюдента. 4. На одном графике отложить исходные данные и теоретическую прямую. Все расчеты провести в Excel, в соответствии со своим вариантом Вариант 5 По территориям региона приводятся данные за 20ХX г. Таблица 1 № Величина прожиточного минимума трудоспособного населения, руб. X Среднедушевые денежные доходы населения по субъектам РФ, руб. Y 5 10993 24860 6 10806 28715 7 10723 24093 8 9549 26112 9 9356 28956 10 13478 42345 11 10251 23979 12 10324 24272 13 11044 24766 14 9485 26058 15 11051 24353 16 10194 27226 17 10469 27200 207 № Величина прожиточного минимума трудоспособного населения, руб. X Среднедушевые денежные доходы населения по субъектам РФ, руб. Y 18 18453 65471 19 13964 27473 44 9144 22485 45 9187 28468 46 9989 19331 47 8898 18255 48 9269 32436

Решение

1. Для определения параметров уравнения линейной регрессии строим расчетную таблицу 2. Находим параметры уравнения регрессии: 𝑏 = 𝑥𝑦̅̅̅ − 𝑥̅∙ 𝑦̅ 𝑥 ̅̅2̅ − 𝑥̅ 2 = 324824545,25 − 1083,35 ∗ 28342,7 122030373,35 − 1083,352 = 3,78; 𝑎 = 𝑦̅ − 𝑏 ∙ 𝑥̅= 122030373,35 − 3,78 ∗ 10831,35 = −12651,77. Получили уравнение регрессии: 𝑦̂ = 3,78𝑥 − 12651,77. После нахождения уравнения регрессии заполняем столбцы табл. 2. 208 Таблиц 2 № X Y x*y x² у² ŷ y-ŷ (y-y)̂² (y-yср)2 (x-xср)2 А,% (e-ei-1) 2 5 10993 24860 273285980 120846049 618019600 28954,513 -4094,513 16765033,666 12129199,29 26130,72 16,47 - 6 10806 28715 310294290 116769636 824551225 28246,755 468,245 219253,038 138607,29 642,62 1,63 20818753,84 7 10723 24093 258349239 114982729 580472649 27932,617 -3839,617 14742659,592 18059950,09 11739,72 15,94 18557672,85 8 9549 26112 249343488 91183401 681836544 23489,264 2622,736 6878743,958 4976022,49 1644421,52 10,04 41762007,38 9 9356 28956 270912336 87534736 838449936 22758,798 6197,202 38405312,879 376136,89 2176657,62 21,40 12776807,56 10 13478 42345 570725910 181656484 1793099025 38359,736 3985,264 15882327,966 196064405,3 7004756,22 9,41 4892670,46 11 10251 23979 245808729 105083001 574992441 26146,192 -2167,192 4696722,752 19041877,69 336806,12 9,04 37852717,51 12 10324 24272 250584128 106584976 589129984 26422,483 -2150,483 4624575,562 16570598,49 257404,02 8,86 279,22 13 11044 24766 273515704 121969936 613354756 29147,537 -4381,537 19197869,432 12792782,89 45220,02 17,69 4977605,08 14 9485 26058 247160130 89965225 679019364 23247,037 2810,963 7901513,284 5219854,09 1812658,32 10,79 51732061,85 15 11051 24353 269125003 122124601 593068609 29174,031 -4821,031 23242339,170 15917706,09 48246,12 19,80 58247332,06 16 10194 27226 277541844 103917636 741255076 25930,459 1295,541 1678426,822 1247018,89 406215,02 4,76 37412453,70 17 10469 27200 284756800 109599961 739840000 26971,278 228,722 52313,583 1305763,29 131297,52 0,84 1138103,85 18 18453 65471 1208136363 340513209 4286451841 57189,107 8281,893 68589748,694 1378510661 58089548,72 12,65 64853566,28 19 13964 27473 383632972 194993296 754765729 40199,148 -12726,148 161954844,758 756378,09 9813496,02 46,32 441337782,16 44 9144 22485 205602840 83612736 505575225 21956,421 528,579 279396,011 34312649,29 2847150,02 2,35 175687796,07 45 9187 28468 261535516 84400969 810427024 22119,167 6348,833 40307679,389 15700,09 2703886,92 22,30 33875352,87 46 9989 19331 193097359 99780121 373687561 25154,575 -5823,575 33914028,560 81210736,89 709553,52 30,13 148167520,27 47 8898 18255 162432990 79174404 333245025 21025,360 -2770,360 7674896,777 101761691,3 3737842,22 15,18 9322120,82 48 9269 32436 300649284 85914361 1052094096 22429,521 10006,479 100129631,250 16755104,89 2440937,52 30,85 163247637,03 Итого 216627 566854 6496490905 2440607467 1798333571 566854 0,000 567137317,142 1917162844,2 94244610,55 306,44 1326660240,87 Ср.знач. 10831,35 28342,7 324824545,25 122030373,35 899166785,5 28342,7 - 28356865,86 15,32 209 Этапы проверки адекватности: 1 Этап. Проверка общего качества уравнения регрессии Проверка общего качества уравнения регрессии производится на основе показателей качества подгонки. Показатели качества подгонки: a) остаточная дисперсия – несмещенная оценка дисперсии случайной ошибки линейной модели: 𝜎 2 = ∑(𝑦 − 𝑦̂𝑥 ) 2 𝑛 − 2 = 567137317,142 20 − 2 = 31507628,73 b) коэффициент линейной корреляции: 𝑟𝑥𝑦 = 𝑏 √𝑥 ̅̅2̅ − (𝑥̅) 2 √𝑦 ̅̅2̅ − (𝑦̅) 2 = 3,78 ∗ √122030373,35 − 1083,352 √899166785,5 − 28342,7 2 = 0,84. Значения коэффициента корреляции положительное и по шкале Чеддока находится в интервале от 0,7 до 0,9, следовательно, связь между х и у прямая высокая. c) коэффициент детерминации: 𝑅 2 = 1 − ∑(𝑦 − 𝑦̂𝑥 ) 2 ∑(𝑦 − 𝑦̅) 2 = 1 − 567137317,142 1917162844,2 = 0,7 Коэффициент детерминации 𝑅 2 = 0,7 или 70%. Это означает, что расчетные параметры модели на 70% объясняют зависимость между изучаемыми параметрами. Вариация среднедушевых денежных доходов на 70% объясняется вариаций среднедушевого прожиточного минимума, остальное – это параметры, не включенные в модель или случайная ошибка. Коэффициент детерминации высокий, что говорит о высоком качестве модели. d) ошибка аппроксимации: 𝐴 = 1 𝑛 ∑| 𝑦 − 𝑦̂𝑥 𝑦 | ∗ 100% = 15,32% Качество построенной модели оценивается как неудовлетворительное, так как A превышает 10%. e) информационные критерии: 𝐴𝐼𝐶 = ln(𝜎 2) + 2 ∗ 𝑘 𝑛 = ln(31507628,73) + 2 ∗ 1 20 = 17,37; 𝑆𝐶 = ln(𝜎 2) + 𝑘 ∗ ln(𝑛) 𝑛 = ln(31507628,73) + 1 ∗ ln(20) 20 = 17,42; 𝐻𝑄 = ln(𝜎 2 ) + 2 ∗ 𝑘 ∗ ln(ln(𝑛)) 𝑛 = ln(31507628,73) + 2 ∗ 1 ∗ ln(ln(20)) 20 = 13,47