ЛАБОРАТОРНАЯ РАБОТА №3
ОЦЕНКА ПАРАМЕТРОВ И КАЧЕСТВА
ПАРНОЙ ЛИНЕЙНОЙ РЕГРЕССИИ.
Цель лабораторной работы: построить линейную регрессию по имеющимся данным. Оценить качество модели в целом, ее статистическую значимость и статистическую значимость параметра регрессии b.
Работа выполняется средствами MS Excel.
ХОД РАБОТЫ.
- Внесите в ячейки рабочего листа MS Excel ряды данных (рис.1)
Рис. 1.
- Рассчитайте столбцы значений xy, x2, y2. Для этого введите и скопируйте по соответствующим столбцам формулы
В ячейку D2 =B2*C2
В ячейку E2 =B2*B2
В ячейку F2 =C2*C2
Рабочий лист примет вид (рис.2)
Рис. 2.
- Рассчитайте по полученным столбцам суммы и средние значения данных.
Для этого введите и скопируйте по соответствующей строке формулы
В ячейку B12 =СУММ(B2:B11)
В ячейку B13 =СРЗНАЧ(B2:B11)
- Рассчитайте параметры регрессии b и a.
В ячейке B15 вычислите значение b =(D13-B13*C13)/(E13-B13*B13)
В ячейке B16 вычислите значение a =C13-B15*B13
5. Используя полученные значения параметров b и a, рассчитайте теоретические значения результативного признака y как парную линейную регрессию. Для этого введите в ячейку G2 формулу =$B$16+$B$15*B2 и скопируйте ее в ячейки G3:G11. Рассчитайте сумму и среднее значение полученного столбца, скопировав в ячейки G12 и G13 соответственно формулы из ячеек F12 и F13. Рабочий лист примет вид (Рис.3):
Рис. 3.
6. Рассчитайте столбцы y-yx и (y-yx)2 - отклонения и квадраты отклонений. Для этого введите и скопируйте по соответствующим столбцам формулы
В ячейку H2 =G2-C2
В ячейку I2 = H2* H2
Найдите сумму и среднее значение квадратов отклонений (остаточную дисперсию), скопировав в ячейки I12 и I13 соответственно формулы из ячеек F12 и F13.
7. Рассчитайте среднюю ошибку аппроксимации А.
Для этого в ячейку J2 введите формулу =ABS(H2/C2), скопируйте ее в ячейки J3:J11. Найдите сумму и среднее значение по данным J2:J11. Рабочий лист будет иметь вид (рис.4)
Рис.4.
8. Рассчитайте дисперсии и средние квадратические значения фактора х и результата у (см. рис. 5). Для этого введите
В ячейку B18 =E13-B13*B13
В ячейку B19 =F13-C13*C13
В ячейку B20 =КОРЕНЬ(B18)
В ячейку B21 =КОРЕНЬ(B19)
Рис. 5
9. Рассчитайте показатели корреляции, детерминации, критерий Фишера для оценки качества модели. Введите следующие формулы
В ячейку Е16 =(D13-B13*C13)/B21/B20
В ячейку Е17 =E16*E16
В ячейку Е19 =E17/(1-E17)*8
10. Для оценки статистической значимости параметра b рассчитайте соответственно значение t-критерия Стьюдента, стандартную ошибку параметра и границы доверительного интервала, введя формулы:
В ячейку Е20 =КОРЕНЬ(E19)
В ячейку Е21 =КОРЕНЬ(I12/8)/B20/КОРЕНЬ(10)
В ячейку Е22 =B15-1,7*E21
В ячейку Е23 =B15+1,7*E21
В итоге имеем следующие расчетные данные (рис.6)
Рис. 6.
11. Для проверки правильности оценки параметров и расчета коэффициента детерминации выполните построение графика и линейного тренда по исходным данным. Представленные на диаграмме (используйте вкладку Параметры окна построения тренда) значения параметров линейной регрессии и коэффициента детерминации (достоверность R2) должен совпадать с рассчитанными значениями.(рис. 7).
Рис. 7 Рис.8
12. Для проверки вычисления других статистик регрессии воспользуемся Пакетом анализа. Для его активизации входим в меню «Сервис», Þ «Анализ данных», Þ «Регрессия». Замечание. Если в «Сервисе» нет пакета «Анализ данных», то входим в «Надстройки», подгружаем «Пакет анализа данных» и снова входим в «Сервис». Меню показано на рис. 8.
В результате получаем 4 таблицы и три графика. Сравните полученные результаты с результатами автоматической обработки. Сделайте выводы о качестве полученной регрессии
ТАБЛИЦЫРЕГРЕССИОННОГО АНАЛИЗА
| ВЫВОД ИТОГОВ | ||||||||
| Регрессионная статистика | ||||||||
| Множественный R | 0,868715 | |||||||
| R-квадрат | 0,754666 | |||||||
| Нормированный R-квадрат | 0,723999 | |||||||
| Стандартная ошибка | 83,94853 | |||||||
| Наблюдения | ||||||||
| Дисперсионный анализ | ||||||||
| df | SS | MS | F | Значимость F | ||||
| Регрессия | 173425,6 | 173425,6 | 24,6086 | 0,001106 | ||||
| Остаток | 56378,84 | 7047,355 | ||||||
| Итого | 229804,4 | |||||||
| Коэффициенты | Стандартная ошибка | t-статистика | P-Значение | Нижние 95% | Верхние 95% | Нижние 95,0% | Верхние 95,0% | |
| Y-пересечение | 261,8757 | 60,10855 | 4,356712 | 0,002423 | 123,2651 | 400,4862 | 123,2651 | 400,4862 |
| Переменная X 1 | 0,309384 | 0,062367 | 4,960706 | 0,001106 | 0,165566 | 0,453202 | 0,165566 | 0,453202 |
| ВЫВОД ОСТАТКА | ВЫВОД ВЕРОЯТНОСТИ | |||||||
| Наблюдение | Предсказанное Y | Остатки | Стандартные остатки | Персентиль | Y | |||
| 457,4063 | 3,593651 | 0,045405 | ||||||
| 490,2011 | 33,79895 | 0,427038 | ||||||
| 421,2084 | -123,208 | -1,5567 | ||||||
| 459,8814 | -108,881 | -1,37568 | ||||||
| 553,3154 | 70,68462 | 0,893075 | ||||||
| 536,6086 | 47,39135 | 0,598773 | ||||||
| 479,682 | -54,682 | -0,69089 | ||||||
| 489,2729 | 86,7271 | 1,095766 | ||||||
| 497,0075 | 90,9925 | 1,149658 | ||||||
| 909,4163 | -46,4163 | -0,58645 |