Построить модель парной линейной регрессии y = a + bx +e. Изобразить на графике исходные и модельные значения.

1. Сначала для наглядности построим поле корреляции (диаграмму рассеяния).
Для создания диаграммы рассеяния нужно выделить два столбца данных со значениями показателей, включая их названия (метки) в первой строке матрицы данных, и выполнить следующие действия: на вкладке «Вставка» в группе «Диаграммы» выбрать тип диаграммы «Точечная», диаграмма добавится на лист.
Для того чтобы Microsoft Excel правильно определил переменные, объясняемая переменная Y должна быть расположена в правом из двух выделенных столбцов, а объясняющая переменная Х – в левом столбце.
Построим график диаграммы рассеяния зависимой переменной с экзогенным фактором.
Вывод: Вытянутость облака точек на диаграмме рассеяния вдоль наклонной прямой позволяет сделать предположение, что существует некоторая объективная тенденция прямой линейной связи между значениями переменных x и y, т.е. в среднем, с увеличением значения х, в среднем увеличивается значение переменной у.
Оценку параметров модели парной регрессии y = a + bx проведем несколькими способами:
1) Анализ данных, используя инструмент Регрессия:
Получено уравнение
2) по формулам: ,
Составим расчетную таблицу:
№ х у 1974854826000
1098555588000
1143004572000
106680762000
1 24,9 48 -13,45 -24,54 329,948 180,799
2 36,9 57 -1,45 -15,54 22,471 2,091
3 35,1 56 -3,25 -16,54 53,686 10,538
4 26,6 49 -11,75 -23,54 276,486 137,972
5 45,1 97 6,75 24,46 165,209 45,614
6 28 42 -10,35 -30,54 315,956 107,043
7 43,2 87 4,85 14,46 70,194 23,560
8 27,4 54 -10,95 -18,54 202,925 119,818
9 31 52 -7,35 -20,54 150,879 53,966
10 48,2 96 9,85 23,46 231,186 97,098
11 49,2 97 10,85 24,46 265,502 117,806
12 50,9 102 12,55 29,46 369,856 157,599
13 52 106 13,65 33,46 456,879 186,428
Сумма 498,5 943     2911,177 1240,332
среднее 38,35 72,54
,
Получено уравнение
3) с помощью функции ЛИНЕЙН:
Получено уравнение
В результате применения всех инструментов Excel были получены одинаковые параметры модели регрессии.
2 . График построенной линейной регрессии на фоне корелляционного поля:
3. Оценивание качества модели
Проверим статистическую значимость регрессии в целом.
При анализе качества модели регрессии, в первую очередь, используется коэффициент детерминации, который определяется следующим образом:
0,934
Данные рассчитаны в таблице:
304990518796000383222518859500
№ х у 2686052349500
229235-1143000 -3810-4826000
1 24,9 48 40,979 7,021 602,136 49,3 996,0
2 36,9 57 69,144 -12,144 241,444 147,5 11,5
3 35,1 56 64,919 -8,919 273,521 79,6 58,0
4 26,6 49 44,969 4,031 554,059 16,2 760,1
5 45,1 97 88,390 8,610 598,367 74,1 251,3
6 28 42 48,255 -6,255 932,598 39,1 589,7
7 43,2 87 83,931 3,069 209,136 9,4 129,8
8 27,4 54 46,847 7,153 343,675 51,2 660,1
9 31 52 55,296 -3,296 421,828 10,9 297,3
10 48,2 96 95,666 0,334 550,444 0,1 534,9
11 49,2 97 98,013 -1,013 598,367 1,0 649,0
12 50,9 102 102,004 -0,004 867,982 0,0 868,2
13 52 106 104,585 1,415 1119,675 2,0 1027,0
Сумма 498,5 943 943,00 0,00 7313,23 480,4 6832,8
Коэффициент детерминации показывает долю вариации (дисперсии) ре- зультативного признака, находящегося под воздействием изучаемых факторов, то есть определяет, какая доля вариации признака y учтена в модели и обуслов- лена влиянием на него факторов, включенных в модель