Построение уравнений парной линейной регрессии

Пример 1

По территории региона приводятся данные за 1995 год.

Территория региона	Среднедневная зарплата Y, тыс. р.	Прожиточный минимум X, тыс. р.
1	133	78
2	148	82
3	134	87
4	154	79
5	162	89
6	195	106
7	139	67
8	158	88
9	152	73
10	162	87
11	159	76
12	173	115

Необходимо:

1. Выполнить регрессионный анализ в ППП Excel.

2. Построить линейное уравнение регрессии y=a+bx с помощью режима Регрессия.

3. Оценить тесноту связи с помощью показателей парной корреляции и детерминации.

4. Дать сравнительную оценку силы связи фактора с результатом с помощью коэффициента эластичности.

5. Оценить качество модели с помощью средней ошибки аппроксимации.

6. Оценить статистическую значимость уравнения регрессии и корреляции с помощью F-критерия Фишера для уровня значимости α=0,05.

7. Определить статистическую значимость коэффициентов регрессии с помощью t-критерия Стьюдента.

8. Определить случайные ошибки коэффициентов регрессии и доверительные интервалы для коэффициентов регрессии.

9. Выполнить прогноз заработной платы Y при прогнозном значении среднедушевого прожиточного минимума X, составляющем 107% от среднего уровня.

10. Определить доверительный интервал прогнозного значения заработной платы для уровня значимости α=0,05.

Решение

1. Введите исходные данные и выполните регрессионный анализ.

При выполнении команды Сервис, Анализ данных выбирается режим Регрессия, появляется одноименное диалоговое окно, в котором задаются:

Входной интервал Y – интервал результативного признака (зависимых данных), подлежащих анализу. Зависимые данные выводятся в отдельном столбце;

Входной интервал X – интервал факторов (независимых данных), подлежащих анализу. Независимые переменные упорядочены слева направо с использованием индексов 1, 2, 3 и т. д. в выходной таблице (максимальное число входных интервалов равно 16);

Метки – флажок, который указывает, содержит ли первая строка названия столбцов или нет;

Константа ноль – линия регрессии проходит через начало координат;

Уровень надежности – по умолчанию 95%;

Выходной интервал – верхняя левая ячейка интервала, в который выводятся выходные таблицы (не менее 7 столбцов для итоговой выходной таблицы);

Новый рабочий лист – можно задать произвольное имя нового листа.

Результаты регрессионного анализа размещаются в четырех таблицах: регрессионной статистики, дисперсионного анализа, параметров модели, прогнозных значений и остатков.

Таблица регрессионной статистики оценивает корреляционную связь.

Регрессионная статистика
Множественный R	0,721025214
R-квадрат	0,519877359
Нормированный R-квадрат	0,471865095
Стандартная ошибка	12,5495908
Наблюдения	12

Множественный R – коэффициент корреляции.

R-квадрат – коэффициент детерминации, характеризует тесноту связи результативного и факторного признаков. Качественная оценка степени связи случайных переменных может быть выявлена на основе оценки коэффициента детерминации по шкале Чеддока:

Коэффициент детерминации	0,1-0,3	0,3-0,5	0,5-0,7	0,7-0,9	0,9-0,99
Характеристика силы связи	слабая	умеренная	заметная	высокая	весьма высокая

Стандартная ошибка – среднеквадратическое значение отклонения регрессии от эмпирических данных.

Наблюдения – количество (n) наблюдений в массиве.

Таблица дисперсионного анализа – ANOVA

Дисперсионный анализ	df	SS	MS	F	Значимость F
Регрессия	1	1705,3277	1705,3277	10,8280	0,00814
Остаток	10	1574,92224	157,4922
Итого	11	3280,25

df – число степеней свободы; для строки Регрессия это m число переменных (рассматриваемых факторов) в уравнении регрессии, для строки Остаток – размер выборки минус число параметров в регрессии минус 1 (n-m-1), для строки Итого – размер выборки минус 1 (n-1).

SS – сумма квадратов отклонений для расчета дисперсии: для строки Регрессия – факторной, для строки Остаток – остаточной, для строки Итого – общей.

MS – дисперсия, рассчитываемая как отношение суммы квадратов отклонений к величине df.

F – статистика (F-критерий Фишера) для оценки связи между зависимой и независимыми переменными. Если Fрасчетное>Fтабличное при заданном уровне значимости α и k1=m, k2=n-m-1 (m – число независимых факторов, n – число наблюдений), то уравнение линейной регрессии и коэффициент корреляции статистически значимы.

Значимость F – значение уровня значимости α, соответствующее вычисленному значению F. Вероятность правильного прогноза 1-α..

Таблица параметров модели и их статистических оценок

	Коэффициенты	Стандартная ошибка	t-статистика	P-значение	Нижние 95%	Верхние 95%
Y- пересечение	76,9764	24,2115	3,1793	0,00983	23,0297	130,9232
X	0,9204	0,2797	3,2905	0,00814	0,2971	1,5436

Коэффициенты – значения параметров модели регрессии.

Стандартная ошибка – параметров уравнения регрессии.

t-статистика – отношение Коэффициент/Стандартная ошибка (t-критерий Стьюдента). Если tрасчетное>tтабличное при заданном уровне значимости и P-значение меньше принятого уровня значимости, то коэффициенты регрессии статистически значимы.

P-значение – уровень значимости α для значений t-статистики.

Верхние и Нижние – границы доверительного интервала для коэффициентов уравнения регрессии. Если в доверительный интервал не попадает ноль, то коэффициенты регрессии статистически значимы.

Таблица прогнозных значений по модели и остатков

Наблюдение	Предсказанное Y	Остатки	Стандартные Остатки
1	148,7700	-15,7700	-1,3179
2	152,4517	-4,4517	-0,3720
3	157,0539	-23,0539	-1,9266
4	149,6904	4,3095	0,3601
5	158,8948	3,1051	0,2595
6	174,5421	20,4578	1,7097
7	138,6453	0,3546	0,0296
8	157,9743	0,0256	0,0021
9	144,1679	7,8320	0,6545
10	157,0539	4,9460	0,4133
11	146,9292	12,0707	1,0087
12	182,8259	-9,8259	-0,8211

Предсказанное Y – расчетные значения по модели регрессии.

Остатки – разность эмпирического и предсказанного по модели регрессии значений.

2. По таблице параметров модели и их статистических оценок определите значения коэффициентов регрессии a и b.

3. По таблице регрессионной статистики определите коэффициенты парной корреляции и детерминации, оцените тесноту связи.

4. Рассчитайте коэффициент эластичности, используя статистическую функцию СРЗНАЧ, по формуле:

.

Коэффициент эластичности показывает, на сколько процентов в среднем по совокупности изменится значение результирующего фактора от своей средней величины, если независимый фактор изменится на 1% от своего среднего значения.

2. По таблице прогнозных значений по модели и остатков рассчитайте среднюю ошибку аппроксимации A по формуле:

Используйте функцию ABS для определения модулей выражений и функцию СУММ для суммирования. Средняя ошибка аппроксимации не должна превышать 10%.

2. По таблице дисперсионного анализа ANOVA определите расчетное значение F-критерия Фишера и сравните его с табличным значением для уровня значимости α=0,05. Табличное значение F-критерия Фишера определите при помощи статистической функции FРАСПОБР (α, k1, k2). Если Fрасчетное>Fтабличное при заданном уровне значимости α и k1=m, k2=n-m-1 (n – число наблюдений, m – число независимых факторов), то уравнение линейной регрессии и коэффициент корреляции статистически значимы. Сделайте выводы.

3. По таблице параметров модели и их статистических оценок определите расчетные t-статистики и сравните их с табличным значением t-критерия Стьюдента для уровня значимости α=0,05. Табличное значение t-критерия Стьюдента определите при помощи статистической функции СТЬЮДРАСПОБР (α, d.f.). Если tрасчетное>tтабличное при заданном уровне значимости и числе степеней свободы d.f.=n-m-1 (n – число наблюдений, m – число независимых факторов), то коэффициенты регрессии статистически значимы. P-значение должно быть меньше принятого уровня значимости. Сделайте выводы.

4. По таблице параметров модели и их статистических оценок определите случайные ошибки коэффициентов регрессии (по столбцу стандартные ошибки) и доверительные интервалы коэффициентов регрессии (по столбцам верхние и нижние). Если в доверительный интервал не попадает ноль, то коэффициенты регрессии статистически значимы.

5. Определите прогнозное значение среднедушевого прожиточного минимума Xпрогн., используя статистическую функцию СРЗНАЧ. Определите прогнозное значение среднедневной зарплаты Yпрогн. по формулам:

10. По таблице дисперсионного анализа ANOVA определите остаточную дисперсию и с помощью математической функции КОРЕНЬ рассчитайте среднюю стандартную ошибку прогноза m_y и предельную ошибку Δ по формулам:

где σ ост – квадратный корень из остаточной дисперсии.

где t_табл. – табличное значение t-критерия Стьюдента при заданном уровне значимости α=0,05.

Доверительный интервал прогноза рассчитывается по формуле:

К онтрольные задания

1. Рассчитайте параметры уравнения линейной регрессии.

2. Оцените тесноту связи факторов.

3. Дайте оценку силы связи фактора с результатом с помощью коэффициента эластичности.

4. Оцените качество уравнений с помощью средней ошибки аппроксимации.

5. Оцените статистическую надежность результатов регрессионного моделирования с помощью F-критерия Фишера.

6. Оцените статистическую надежность и доверительный интервал для параметров регрессии.

7. Рассчитайте прогнозное значение результата, если прогнозное значение фактора увеличится на 5% от его среднего уровня.

8. Определите доверительный интервал прогноза для уровня значимости α=0,05.

9. Распечатайте таблицы регрессионного анализа и результаты расчетов.

По территории Уральского и Западно-Сибирского районов известны данные за ноябрь 1997 года.

Район	Потребительские расходы на душу населения, тыс. р., Y	Средняя зарплата и выплаты социального характера, тыс. р., X
Респ. Башкортостан	461	912
Удмуртская респ.	524	809
Курганская обл.	298	748
Оренбургская обл.	351	847
Пермская обл.	624	1087
Свердловская обл.	584	1074
Челябинская обл.	425	1008
Респ. Алтай	277	682
Алтайский край	321	697
Кемеровская обл.	573	1251
Новосибирская обл.	576	967
Омская обл.	588	898
Томская обл.	497	1263
Тюменская обл.	863	3027

Варианты

Номер варианта	Районы, показатели которых исключены из варианта	Номер варианта	Районы, показатели которых исключены из варианта
1	Тюменская обл.	7	Курганская обл.
2	Томская обл.	8	Пермская обл.
3	Омская обл.	9	Удмуртская обл.
4	Свердловская обл.	10	Курганская обл., Алтайский край
5	Респ. Алтай	11	Пермская обл., Тюменская обл.
6	Оренбургская обл.	12	Респ. Башкортостан

Район	Потребительские расходы на душу населения, тыс. р., Y	Денежные доходы на душу населения, тыс. р., X
Респ. Башкортостан	461	632
Удмуртская респ.	524	738
Курганская обл.	298	515
Оренбургская обл.	351	640
Пермская обл.	624	942
Свердловская обл.	584	888
Челябинская обл.	425	704
Респ. Алтай	277	603
Алтайский край	321	439
Кемеровская обл.	573	985
Новосибирская обл.	576	765
Омская обл.	588	760
Томская обл.	497	830
Тюменская обл.	863	2093

Варианты

Номер варианта	Районы, показатели которых исключены из варианта	Номер варианта	Районы, показатели которых исключены из варианта
13	Тюменская обл.	20	Курганская обл.
14	Томская обл.	21	Пермская обл.
15	Омская обл.	22	Удмуртская обл.
16	Свердловская обл.	23	Курганская обл., Алтайский край
17	Респ. Алтай	24	Пермская обл., Тюменская обл.
18	Оренбургская обл.	25	Респ. Башкортостан
19	Кемеровская обл.	26	Челябинская обл.