План занятий
1. Спецификация модели парной регрессии.
2. Оценка существенности параметров парной регрессии.
3. Прогнозирование по линейному уравнению регрессии.
4. Средняя ошибка аппроксимации.
5. Нелинейная регрессия и корреляция.
Задание 1. По исходным данным, соответствующим Вашему варианту задания (стр. 13), требуется:
1. Рассчитать коэффициенты линейного уравнения вида .
2. Провести статистический и содержательный анализ качества построенного уравнения.
Задание выполнить средствами табличного процессора MS Excel в соответствии с приведенным ниже примером выполнения задания.
Методические указания по выполнению задания
1. Результаты выполнения задания оформить по образцу (рис. 1).
2. Результаты выполнения задания занести в отчет, который должен содержать:
а) исходные данные для выполнения задания;
б) систему нормальных уравнений;
в) построенное уравнение прогноза;
г) значения коэффициентов линейной корреляции и детерминации;
д) среднюю ошибку аппроксимации;
|
|
е) расчетные и критические значения t- и F-статистик;
ж) соответствующие всем полученным значениям выводы.
A | B | C | D | E | F | G | H | I | J | K | L | ||
1 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | Среднее | s^2 | s | |||
2 | y | 68,8 | 61,2 | 59,9 | 56,7 | 55 | 54,3 | 49,3 | 405,2 | 57,89 | 32,92 | 5,74 | |
3 | x | 45,1 | 59 | 57,2 | 61,8 | 58,8 | 47,2 | 55,2 | 384,3 | 54,9 | 34,33 | 5,86 | |
4 | yx | 3102,88 | 3610,8 | 3426,28 | 3504,06 | 3234 | 2562,96 | 2721,36 | 22162,34 | 3166,05 | - | - | |
5 | x2 | 2034,01 | 3481 | 3271,84 | 3819,24 | 3457,44 | 2227,84 | 3047,04 | 21338,41 | 3048,34 | - | - | |
6 | y2 | 4733,44 | 3745,44 | 3588,01 | 3214,89 | 3025 | 2948,49 | 2430,49 | 23685,76 | 3383,68 | - | - | |
7 | 61,28 | 56,47 | 57,09 | 55,499 | 56,54 | 60,55 | 57,78 | 405,2 | - | - | - | ||
8 | 7,52 | 4,73 | 2,81 | 1,20 | -1,54 | -6,25 | -8,48 | 0,0 | - | - | - | ||
9 | ()2 | 56,61 | 22,39 | 7,89 | 1,44 | 2,36 | 39,05 | 71,94 | 201,71 | - | - | - | |
10 | 0,109 | 0,077 | 0,047 | 0,021 | 0,028 | 0,115 | 0,172 | 0,569 | - | - | - | ||
11 | 96,04 | 16,81 | 5,29 | 47,61 | 15,21 | 59,29 | 0,09 | 240,34 | - | - | - | ||
12 | Ai | 10,9 | 7,7 | 4,7 | 2,1 | 2,8 | 11,5 | 17,2 | 56,9 | 8,14 | - | - | |
13 |
| ||||||||||||
14 | Матрицы | Определители | Коэффициенты: | Ошибка аппроксимации | t расчетное | ||||||||
15 | а 0= | 76,87708 | А = | 8,140901 | а 0= | 3,398608 | |||||||
16 | 7 | 384,3 | D | а 1= | -0,34593 | а 1= | 0,844345 | ||||||
17 | 384,3 | 21338,41 | 1682,38 |
| Дисперсии: | r xy= | 0,844345 | ||||||
18 | 405,2 | 384,3 | D0 | Коэффициент корреляции | а 0= | 511,6719 | |||||||
19 | 22162,34 | 21338,41 | 129336,5 | r xy= | -0,35326 | а 1= | 0,167852 | t крит | 2,571 | ||||
20 | 7 | 405,2 | D1 |
| r xy= | 0,175042 | |||||||
21 | 384,3 | 22162,34 | -581,98 | Коэффициент детерминации | F факт | 0,714 | |||||||
22 | R ^2= | 0,124791 | F крит | 6,608 | |||||||||
|
Пример выполнения задания
По семи районам некоторого региона за год зарегистрированы значения двух показателей:
|
|
Район | Расходы на покупку продовольственных товаров в общих расходах, % (y) | Среднедневная заработная плата одного работающего, руб. (x) |
1 | 68,8 | 45,1 |
2 | 61,2 | 59,0 |
3 | 59,9 | 57,2 |
4 | 56,7 | 61,8 |
5 | 55,0 | 58,8 |
6 | 54,3 | 47,2 |
7 | 49,3 | 55,2 |
1. Для определения характеристики зависимости y от x рассчитать коэффициенты линейного уравнения.
2. Провести статистический и содержательный анализ качества построенного уравнения.
Решение
1. Для расчета коэффициентов уравнения линейной регрессии решим систему из двух нормальных уравнений относительно переменных a0 и a1:
.
Для вычисления оценок коэффициентов a0 и a1 и проведения дальнейшего анализа построенного уравнения необходимо сформировать таблицу (рис.1, выделена серым цветом), занести в нее исходные данные (в таблице они выделены жирным шрифтом). Полученные в результате расчетов значения внесены в таблицу обычным шрифтом. Значения клеток, помеченных знаком «–», рассчитывать не требуется.
Для выполнения расчетов (например, вычисления значений выражений yx, х2 и т. п.) требуется установить курсор в первую клетку, соответствующую вычисляемому выражению, набрать знак равенства («=») и ввести необходимую для расчета формулу. В качестве значений переменных, входящих в формулу, указываются адреса клеток, содержащих эти значения (адрес клетки можно ввести, выделив соответствующую клетку курсором). Ввести эту же формулу в клетки, соответствующие остальным значениям переменных, входящих в выражение, можно, выделив клетку с формулой, и клетки, которые необходимо заполнить. После выделения использовать режимы меню ПРАВКА − ЗАПОЛНИТЬ − ВПРАВО.
Например, для расчета значений произведений yx необходимо установить курсор в клетку В4, набрать выражение =В2*В3 (адреса клеток В2 и В3 можно не набирать, а указать, выделив клетки курсором), нажать клавишу «ENTER», выделить клетки В4:Н4 и указать режимы ПРАВКА − ЗАПОЛНИТЬ − ВПРАВО.
Для расчета значений вида , , , и т. п. необходимо установить курсор на первое из суммируемых значений, нажав левую клавишу мыши, выделить участвующие в сложении клетки и нажать кнопку режима АВТОСУММА ().
Например, для получения значения выражения , необходимо выделить клетки В2:Н2 и нажать кнопку «».
Используя результаты вычисления значений выражений , , и , составим систему нормальных уравнений:
и найдем ее решение методом определителей:
; ,
где – определитель системы, и – частные определители.
= ; = ;
= .
Для вычисления определителя матрицы, необходимо ввести ее элементы в массив клеток размера 2х2. Установить курсор в клетке, предназначенной для размещения значения определителя, нажать кнопку «Функции» (f *), выбрать категорию МАТЕМАТИЧЕСКИЕ и выделить функцию МОПРЕД. В ссылке МАССИВ указать адрес размещения матрицы (рис. 2): нажать правый значок ссылки, выделить матрицу, вновь нажать тот же значок, проверить правильность ввода адресов клеток, содержащих матрицу и нажать кнопку «ОК».
|
Найденные значения определителей равны: 1682,38; =129336,47;
= –581,98, а коэффициенты линейного регрессионного уравнения – ; .
Построенное уравнение регрессии имеет вид:
и позволяет сделать вывод, что с ростом средней заработной платы на 1 руб. доля расходов на покупку продовольственных товаров в среднем снижается приблизительно на 0,35 %.
2. Рассчитаем коэффициент парной линейной корреляции:
или .
Для этого вычислим средние значения зависимой и независимой переменных и их среднеквадратические отклонения и :
; ; ; ;
; .
Например, рассчитаем среднеквадратическое отклонение . Для этого вначале вычислим значения и . В клетке J5 введем формулу =I5/7, а в клетке J3 – формулу =I3/7. В клетке К3 вычислим значение дисперсии : наберем в ней формулу = J5-J3^2. В клетке L3 вычислим значение : нажмем кнопку «Функции» (f *), в категории МАТЕМАТИЧЕСКИЕ выделим функцию КОРЕНЬ. В ссылке ЧИСЛО укажем адрес клетки К3. Аналогично вычислим значение .
|
|
Вычислим коэффициент парной линейной корреляции:
.
Связь между переменными умеренная обратная.
Определим коэффициент детерминации:
,
то есть изменения переменной у на 12,5 % объясняются вариацией переменной х.
Подставим в построенное уравнение регрессии фактические значения переменной х и определим прогнозные (расчетные) значения зависимой переменной. Для этого в клетку В7 введем формулу =$ХХ+$YY*B3, где ХX – адрес клетки, содержащей значение коэффициента , YY – адрес клетки, содержащей значение коэффициента (знак «$» необходимо ввести для того, чтобы при размножении данной формулы в клетки В7:Н7 выполнялась адресация только к клеткам, содержащим коэффициенты).
В данном примере в клетку B7 введена формула =$F15+$F16*B3.
После ввода формулы выделим клетки В7:Н7 и размножим введенную формулу, указав режимы ПРАВКА − ЗАПОЛНИТЬ − ВПРАВО. Используя кнопку АВТОСУММА (), вычислим сумму прогнозных значений и сравним полученную сумму с суммой фактических значений . Они должны быть одинаковыми.
Найдем величину средней ошибки аппроксимации:
.
Для этого заполним клетки В8:Н8 и клетки B10:H10. Для вычисления модуля выражения необходимо использовать функцию ABS (категория МАТЕМАТИЧЕСКИЕ). Найденная средняя ошибка аппроксимации 8,14 % означает, что в среднем расчетные значения отличаются от фактических значений y на 8,14 %.
Проведем оценку значимости коэффициентов регрессии и корреляции с помощью t-критерия Стьюдента. С этой целью для вычисленных оценок коэффициентов регрессии и для линейного коэффициента корреляции найдем расчетные значения t-статистик Стьюдента:
; ; ;
; ; .
Сравним фактические и критическое (табличное) tкрит значения t ‑статистик. Для определения критического значения t ‑статистики используется функция СТЪЮДРАСПОБР (категория СТАТИСТИЧЕСКИЕ). Зададим ее параметры: вероятность 0,05 и число степеней свободы N – 2 = 7 – 2 = 5.Cравним значения tа 0 и tа 1 с критическим значением: , то есть коэффициент а 0 является значимым;
|
|
, то есть коэффициент а1 является незначимым;
, то есть линейная связь между переменными исследуемого уравнения является статистически незначимой (выявленная зависимость между переменными является случайной).
Проведем оценку качества построенного уравнения регрессии с помощью F -критерия Фишера. Для этого рассчитаем фактическое значение F - критерия:
.
Для определения критического значения F ‑критерия Фишера необходимо использовать функцию FРАСПОБР (категория СТАТИСТИЧЕСКИЕ). Зададим ее параметры – вероятность 0,05, а также два числа степеней свободы (1 и N-2= 7-2=5).
Сравним фактическое и критическое (табличное) Fкрит значения:
.
Значит, выявленная зависимость имеет случайную природу, а коэффициенты уравнения и показатель тесноты связи статистически незначимы.
Задание 2. По исходным данным, соответствующим Вашему варианту задания (стр. 13), требуется:
1. Для определения характеристики зависимости y от x рассчитать коэффициенты следующих моделей: а) степенной ; б) показательной .
2. Оценить каждое построенное уравнение через среднюю ошибку аппроксимации, t -критерий Стьюдента и F -критерий Фишера.
3. Из трех (линейного, степенного и показательного) построенных на первом и втором практических занятиях уравнений выбрать то, которое наилучшим образом описывает взаимосвязь исходных данных.
Задание выполнить средствами табличного процессора MS Excel в соответствии с предыдущим примером выполнения задания.