Выполнение задания 2 в ППП MS Excel

Линейная множественная регрессия имеет вид:

где Y – признак-результат;

X₁, X₂, ...,X_m – признаки-факторы;

a, b₁, …, b_m – коэффициенты регрессии;

u – случайная составляющая.

Параметры множественной регрессии могут быть определены по МНК через построение системы нормальных уравнений.

Сложность анализа многофакторной регрессии в том, что взаимосвязь между результатом и набором факторов надо исследовать на фоне взаимосвязей факторов между собой. Для возможности такого анализа переходят от регрессии в естественных масштабах к регрессии в стандартных масштабах.

Уравнение регрессии в стандартном масштабе связывает стандартизованные значения признаков-факторов и признака-результата:

, ;

где Хj_i - значение переменной Хj_i в i-ом наблюдении.

При этом: и s_tx²=s_ty²=1, а , .

Линейная связь между переменными в естественном масштабе трансформируется в линейное соотношение в стандартных масштабах:

,

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

Корреляционная матрица – это квадратная матрица размером (m+1;m+1). Ее размер определяется числом признаков, участвующих в анализе: m признаков-факторов и один признак-результат, а элементами являются соответствующие парные коэффициенты корреляции.

1. Корреляционная матрица

Корреляционную матрицу можно получить, рассчитав парные коэффициенты корреляции (см. пункт 7 задания1) или с помощью Пакета анализа: вкладка Данные – Анализ данных –– Корреляция.В окне Корреляция:

Входной интервал– это столбцы значений признака-результата и признаков-факторов (выделить единым массивом);

Группирование - по столбцам (ставим метку);

Выходной интервал – левая верхняя ячейка для будущих результатов.

Корреляционная матрица для признаков выручка (Y), численность персонала (Х₁), число отправленных туристов (Х₂) представлена в табл. 12.

Таблица 12. Корреляционная матрица

	Y	Х1	Х2
Y
Х1	0,948915
Х2	0,73794	0,73142

Коэффициенты в табл. 12 показывают тесноту связи между признаками попарно. Их необходимо сравнить друг с другом по абсолютной величине, обратив особое внимание на межфакторные связи. Если межфакторная связь сильнее, чем связь фактора с результатом, такой фактор следует исключить из уравнения регрессии, т.к. это свидетельствует о сильной коллинеарности (взаимосвязи) факторов. Для качественной регрессионной модели недопустим уровень коллинеарности, превышающий 0,8.

2. b - коэффциенты

Данные корреляционной матрицы используются для определения b - коэффциентов. Последние могут оцениваться с помощью МНК путем решения системы нормальных уравнений:

r_x1y=b₁+r_x1x2b₂+…+ r_x1xmb_m

r_x2y= r_x2x1b₁+b₂+…+ r_x2xmb_m

…

r_xmy= r_xmx1b₁+r_xmx2b₂+…+b_m

Расчет характеристик:

b – коэффициенты показывают, на какую часть своего среднего квадратического отклонения изменится признак-результат Y с изменением соответствующего фактора Хj на величину своего среднего квадратического отклонения (s_хj) при неизменном влиянии прочих факторов, входящих в уравнение.

b – коэффициенты интерпретируются как показатели прямого (непосредственного) влияния j-ого фактора (X_j) на результат (Y). Косвенное влияние измеряется величиной:

,

где m – число факторов в модели.

Таким образом, коэффициент линейной парной корреляции фактора (X_j) и результата (Y), r_xj,y, характеризует полное влияние j-ого фактора на результат, которое равно сумме прямого и косвенного влияний:

.

3. Двухфакторная линейная регрессия

Параметры b_j могут быть определены через b - коэффициенты:

, j=1;m; .

Коэффициент регрессии b_j при факторе Х_j измеряет среднее по совокупности отклонение признака-результата от его средней величины при отклонении признака-фактора Х_j на единицу, при условии, что все прочие факторы модели не изменяются (зафиксированы на своих среднихуровнях).

Для построения уравнения регрессии можно воспользоваться Пакетом анализа: вкладка Данные – Анализ данных – Регрессия. В окне Регрессия:

Входной интервал Х – это столбцы значений признаков-факторов (выделить единым массивом);

Входной интервал Y – это столбец значений признака-результата;

Выходной интервал – левая верхняя ячейка для будущих результатов.

Результаты расчета MS Excel:

Таблица 13. «Вывод итогов»

Регрессионная статистика
Множественный R	0,951095
R-квадрат	0,904581
Нормированный R-квадрат	0,899423
Стандартная ошибка	48,03045
Наблюдения

Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия		809183,6	404591,8	175,3815	0,0000
Остаток		85356,19	2306,924
Итого		894539,8

	Коэффициенты	Стандартная ошибка
Y-пересечение	-119,85	22,53753
Переменная x1	5,376951	0,455078
Переменная х2	0,012999	0,010258

Расчет параметров линейной двухфакторной регрессии (см.табл.14):

Таблица 14. Расчет параметров линейной двухфакторной регрессии

№	Y	X1	X2	f(Х1,Х2)	y- f(Х1,Х2)	[y- f(Х1,Х2)]2
				11,36	38,64	1493,12
				50,73	2,27	5,13
				52,03	5,97	35,58
…	…	…	…	…	…	…
				445,91	36,09	1302,42
				411,58	88,42	7817,86
				456,84	193,16	37309,94
ИТОГО					0,00	85356,19
СРЗНАЧ	243,175	64,200	1371,250	243,175	0,00	2133,905
СКО	149,544	24,472	1085,645
ДИСП	22363,494	598,860	1178625,938	20229,590	2133,905

Рассчитав значения коэффициентов регрессии, получаем уравнение : f(Х₁,Х₂) = -119.85 + 5.37·х₁ + 0.013·х₂

Подставив в данное уравнение регрессии значения признаков-факторов, получаем столбец регрессионных (теоретических) значений результата (f(Х₁,Х₂) в табл. 14).

4. Корреляционный анализ

После построения уравнения регрессии следует оценить его качество, для чего используется ряд показателей.

Коэффициент множественной детерминации, R²_y(x1,...,xm) – это теоретический коэффициент детерминации для случая множественной регрессии. По аналогии с парной линейной регрессией он определяется, как отношение дисперсии признака-результата, объясненной уравнением множественной регрессии – d², к общей дисперсии признака-результата – s²_y. Область допустимых значений R²_y(x1,...,xm) от нуля до единицы. Данный показатель характеризует долю вариации признака-результата, объясненную уравнением регрессии, т.е. признаками-факторами в общей вариации признака-результата. Для линейного уравнения регрессии:

.

Коэффициент множественной корреляции, R_y(x1,...,xm) - рассчитывается как корень из коэффициента множественной детерминации:

.

Данный показатель аналогичен линейному парному коэффициенту корреляции - r_x,y, используемому в парном регрессионном анализе, но R_y(x1,...,xm) принимает значения от нуля до единицы, следовательно, не может служить характеристикой направления связи. Чем плотнее фактические значения Y_i располагаются относительно линии регрессии, тем меньше остаточная дисперсия и, следовательно, больше величина R_y(x1,...,xm). Таким образом, при значении R_y(x1,...,xm) близкомк единице уравнение регрессии лучше описывает фактические данные, и факторы сильнее влияют на результат; при значении R_y(x1,...,xm) близком к 0 уравнение регрессии плохо описывает фактические данные и факторы оказывают слабое воздействие на результат.

Коэффициенты множественной детерминации и корреляции характеризуют совместное влияние всех факторов на результат.

Расчет показателей (см. табл. 14 и пункт 2):

Коэффициент множественной детерминации (сравнить с R-квадрат в табл. 13):

Коэффициент линейной корреляции (сравнить с Множественный R в табл. 13):

5. Проверка значимости уравнения регрессии

Для проверки гипотезы о надежности уравнения регрессии используют F-статистику (см. пункт 8 задания1).

Значение F-статистики можно найти в Дисперсионном анализе табл. 13.

Результаты расчета MS Excel (см. табл. 13):

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

Можно сравнить парную линейную регрессию и двухфакторную.

Результаты расчета MS Excel (см. табл. 9 и 13):

Нормированные R-квадрат:

Сравнение разных регрессионных моделей дополняется проверкой значимости отдельных коэффициентов уравнения, которую можно сделать по значению t-статистики.

Для поиска критического значения - t_кр пользуются таблицами распределения Стьюдента, задаваясь уровнем значимости a (обычно 0,05) и числом степеней свободы k=n-h.

Далее сравниваются рассчитанное значение, t_набл, и критическое, t_кр(a;k):

если t_набл < t_кр(a;k), то гипотезу о незначимости данного коэффициента регрессии не отвергают;

если t_набл > t_кр(a;k), то гипотезу отвергают и принимают альтернативную - о статистической значимости данного коэффициента регрессии с вероятностью (1-a).

Значение t-статистики можно найти в последней части таблицы «Вывод итогов», воспользовавшись построением регрессии через Пакет анализа (см. пункт 6 задания1).

Таблица 15. «Вывод итогов». t-критерий

	Коэффициенты	t-статистика	Р-значение
Y-пересечение	-119,85	-5,317811	0,000005
Переменная x1	5,376951	11,815445	0,000000
Переменная х2	0,012999	1,267226	0,212993

Результаты расчета MS Excel (см. табл. 15):

Список литературы

1. Елисеева И.И Статистика, Изд-во: Высшее образование, 2007 (Гриф УМО РФ).

2. Дубина А.Г., Орлова С.С., Шубина И.Ю., Хромов А.В. Exсel для экономистов и менеджеров. – СПб.:Питер, 2004.

3. Статистика : учеб. пособие / Е. А. Андреева, Н.Ю. Вилло, О.А. Зайцева, Г.В. Карпова, Л.И. Курова, М.В. Мироновская, И.Н. Нименья, Н.С. Фещенко- СПб. : СПбГИЭУ, 2011.

4. Статистика: учебное пособие. / Под ред. к.э.н. В.Г.Ионина. – Изд, 2-е, перераб. - М.:Инфра-М, 2006.

Приложение 1. Исходные данные

Выручка, тыс. у.е./год	Численность персонала, чел.	Число отправленных туристов, чел./год