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

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

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

X1, X2, ...,Xm – признаки-факторы;

a, b1, …, bm – коэффициенты регрессии;

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

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

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

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

, ;

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

При этом: и stx2=sty2=1, а , .

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

,

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

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

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

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

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

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

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

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

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

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

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

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

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

rx1y=b1+rx1x2b2+…+ rx1xmbm

rx2y= rx2x1b1+b2+…+ rx2xmbm

rxmy= rxmx1b1+rxmx2b2+…+bm

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

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

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

,

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

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

.

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

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

, j=1;m; .

Коэффициент регрессии bj при факторе Х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(Х12) y- f(Х12) [y- f(Х12)]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(Х12) = -119.85 + 5.37·х1 + 0.013·х2

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

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

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

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

.

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

.

Данный показатель аналогичен линейному парному коэффициенту корреляции - rx,y, используемому в парном регрессионном анализе, но Ry(x1,...,xm) принимает значения от нуля до единицы, следовательно, не может служить характеристикой направления связи. Чем плотнее фактические значения Yi располагаются относительно линии регрессии, тем меньше остаточная дисперсия и, следовательно, больше величина Ry(x1,...,xm). Таким образом, при значении Ry(x1,...,xm) близкомк единице уравнение регрессии лучше описывает фактические данные, и факторы сильнее влияют на результат; при значении Ry(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. Исходные данные

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