Как построить линейное уравнение регрессии в excel. Расчет коэффициента детерминации в Microsoft Excel. Мы рады, что смогли помочь Вам в решении проблемы
Оценки неизвестных параметров уравнения регрессии определяются с помощью метода наименьших квадратов. Однако существует и другой способ оценивания этих коэффициентов в случае множественной линейной регрессии. Для этого строится уравнение множественной регрессии в стандартизированном (нормированном) масштабе. Это означает, что все переменные, участвующие в регрессионной модели, стандартизируются с помощью специальных формул. Процесс стандартизации позволяет установить точкой отсчета для каждой нормированной переменной ее среднее значение по выборке. При этом единицей измерения стандартизированной переменной становится ее среднеквадратическое отклонение. Уравнение регрессии в стандартизованном масштабе:
где , - стандартизованные переменные;
Стандартизованные коэффициенты регрессии. Т.е. посредством процесса стандартизации точкой отсчета для каждой нормированной переменной устанавливается ее среднее значение по выборочной совокупности. При этом в качестве единицы измерения стандартизированной переменной принимается ее среднеквадратическое отклонение σ . β-коэффициенты показывают , на сколько сигм (средних квадратических отклонений) изменится в среднем результат за счет изменения соответствующего фактораx I на одну сигму при неизменном среднем уровне других факторов. Применяя МНК к уравнению множественной регрессии в стандартизованном масштабе, после соответствующих преобразований получим систему нормальных уравнений вида для определения стандартизованных коэфф. регрессии Коэффициенты β определяются при помощи МНК из следующей системы уравнений методом определителей:
Следует отметить что величины r yx 1 и r xixj называются парными коэфф. корреляции и определяются по формулам: r yx 1 = yxi среднее – у ср*хiср/ ǪхǪу; r xixj = хixj средние – xi ср*xjср/ǪхiǪxj. Решая систему определяем стандартизованные коэфф. регрессии. Сравнивая их друг с другом можно ранжировать факторы по силе воздействия на разультат. В этом основное достоинство стандартизованных коэфф.регрессии в отличии от коэфф. чистой регрессии, которые несравнимы между собой. Для оценки параметров нелинейных уравнений множественной регрессии предварительно осуществляется преобразование последних в линейную форму (с помощью замены переменных) и МНК применяется для нахождения параметров линейного уравнения множественной регрессии в преобразованных переменных. В случаевнутренне нелинейныхзависимостей для оценки параметров приходится применять методы нелинейной оптимизации Стандартизованные коэффициенты регрессии βi сравнимы между собой, что позволяет ранжировать факторы по силе их воздействия на результат. Большее относительное влияние на изменение результативной переменнойy оказывает тот фактор, которому соответствует большее по модулю значение коэффициентаβi .В этом основное достоинство стандартизованных коэффициентов регрессии , в отличие от коэффициентов «чистой» регрессии, которые не сравнимы между собой. коэффициентов «чистой» регрессииbi с коэффициентамиβi описывается соотношением.
В эконометрике часто используется иной подход к определению параметров множественной регрессии (2.13) с исключенным коэффициентом :
Разделим обе части уравнения на стандартное отклонение объясняемой переменной S Y и представим его в виде:
Разделим и умножим каждое слагаемое на стандартное отклонение соответствующей факторной переменной, чтобы перейти к стандартизованным (центрированным и нормированным) переменным:
где новые переменные обозначены как
.
Все стандартизованные переменные имеют нулевую среднюю величину и одинаковую дисперсию, равную единице.
Уравнение регрессии в стандартизованной форме имеет вид:
где
- стандартизованные коэффициенты
регрессии.
Стандартизованные
коэффициенты регрессии
отличаются от коэффициентов
обычной,
естественной формы тем, что их величина
не зависит масштаба измерения объясняемой
и объясняющих переменных модели. Кроме
того, между ними существует простая
взаимосвязь:
, (3.2)
которая дает другой
способ вычисления коэффициентов
по
известным значениям
,
более удобный в случае, например,
двухфакторной регрессионной модели.
5.2. Нормальная система уравнений мнк в стандартизованных
переменных
Оказывается, что
для вычисления коэффициентов
стандартизованной регрессии нужно
знать только парные коэффициенты
линейной корреляции. Чтобы показать
каким образом это делается, исключим
из нормальной системы уравнений МНК
неизвестную
с помощью первого уравнения. Умножая
первое уравнение на (
)
и складывая его почленно со вторым
уравнением, получим:
Заменяя обозначениями дисперсии и ковариаций выражения в скобках
перепишем второе уравнение в удобном для дальнейшего упрощения виде:
Разделим обе части этого уравнения на стандартное отклонение переменных S Y и ` S X 1 , а каждое слагаемое разделим и умножим на стандартное отклонение переменной, соответствующей номеру слагаемого:
Вводя характеристики линейной статистической связи:
и стандартизованные коэффициенты регрессии
,
получаем:
После аналогичных преобразований всех остальных уравнений,нормальная система линейных уравнений МНК (2.12) принимает следующий, более простой вид:
(3.3)
5.3. Параметры стандартизованной регрессии
Стандартизованные коэффициенты регрессии в частном случае модели с двумя факторами определяются из следующей системы уравнений:
(3.4)
Решая эту систему уравнений, находим:
, (3.5)
. (3.6)
Подставив найденные
значения коэффициентов парной корреляции
в уравнения (3.4) и (3.5), получим
и
.
Затем с помощью формул (3.2) нетрудно
вычислить оценки коэффициентов
и
,
а затем, при необходимости, вычислить
оценку
по формуле
6. Возможности экономического анализа на основе многофакторной модели
6.1. Коэффициенты стандартизованной регрессии
Стандартизованные
коэффициенты регрессии показывают, на
сколько стандартных отклонений
изменится в среднем объясняемая
переменнаяY
,
если соответствующая объясняющая
переменная Х
i
изменится
на величину
одного ее стандартного отклонения при
сохранении неизменным значений среднего
уровня всех остальных факторов.
В силу того, что в
стандартизованной регрессии все
переменные заданы как центрированные
и нормированные случайные величины,
коэффициенты
сравнимы между собой. Сравнивая их друг
с другом, можно ранжировать соответствующие
им факторыХ
i
по силе воздействия на объясняемую
переменную Y
.
В этом состоит основное преимущество
стандартизованных коэффициентов
регрессии от коэффициентов
регрессии в естественной форме, которые
несравнимы между собой.
Эта особенность
стандартизованных коэффициентов
регрессии позволяет использовать при
отсеве наименее значимых факторов Х
i
с близкими к нулю значениями их выборочных
оценок
.
Решение об исключении их из модельного
уравнения линейной регрессии принимается
после проверки статистических гипотез
о равенстве нулю его средней величины.
Задание.
- Для заданного набора данных постройте линейную модель множественной регрессии. Оцените точность и адекватность построенного уравнения регрессии.
- Дайте экономическую интерпретацию параметров модели.
- Рассчитайте стандартизованные коэффициенты модели и запишите уравнение регрессии в стандартизованном виде. Верно ли утверждение, что цена блага оказывает большее влияние на объем предложения блага, чем заработная плата сотрудников?
- Для полученной модели (в естественной форме) проверьте выполнение условия гомоскедастичности остатков, применив тест Голдфельда-Квандта .
- Проверьте полученную модель на наличие автокорреляции остатков с помощью теста Дарбина-Уотсона .
- Проверьте, адекватно ли предположение об однородности исходных данных в регрессионном смысле. Можно ли объединить две выборки (по первым 8 и остальным 8 наблюдениям) в одну и рассматривать единую модель регрессии Y по X ?
1. Оценка уравнения регрессии. Определим вектор оценок коэффициентов регрессии с помощью сервиса Уравнение множественной регрессии . Согласно методу наименьших квадратов, вектор s
получается из выражения: s = (X T X) -1 X T Y
Матрица X
| 1 | 182.94 | 1018 |
| 1 | 193.45 | 920 |
| 1 | 160.09 | 686 |
| 1 | 157.99 | 405 |
| 1 | 123.83 | 683 |
| 1 | 152.02 | 530 |
| 1 | 130.53 | 525 |
| 1 | 137.38 | 418 |
| 1 | 137.58 | 425 |
| 1 | 118.78 | 161 |
| 1 | 142.9 | 242 |
| 1 | 99.49 | 226 |
| 1 | 116.17 | 162 |
| 1 | 185.66 | 70 |
Матрица Y
| 4.07 |
| 4 |
| 2.98 |
| 2.2 |
| 2.83 |
| 3 |
| 2.35 |
| 2.04 |
| 1.97 |
| 1.02 |
| 1.44 |
| 1.22 |
| 1.11 |
| 0.82 |
Матрица X T
| 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 182.94 | 193.45 | 160.09 | 157.99 | 123.83 | 152.02 | 130.53 | 137.38 | 137.58 | 118.78 | 142.9 | 99.49 | 116.17 | 185.66 |
| 1018 | 920 | 686 | 405 | 683 | 530 | 525 | 418 | 425 | 161 | 242 | 226 | 162 | 70 |
Умножаем матрицы, (X T X)
Находим обратную матрицу (X T X) -1
| 2.25 | -0.0161 | 0.00037 |
| -0.0161 | 0.000132 | -7.0E-6 |
| 0.00037 | -7.0E-6 | 1.0E-6 |
Вектор оценок коэффициентов регрессии равен
| Y(X) = |
| * |
| = |
|
Уравнение регрессии (оценка уравнения регрессии)
Y = 0.18 + 0.00297X 1 + 0.00347X 2
2. Матрица парных коэффициентов корреляции R. Число наблюдений n = 14. Число независимых переменных в модели равно 2, а число регрессоров с учетом единичного вектора равно числу неизвестных коэффициентов. С учетом признака Y, размерность матрицы становится равным 4. Матрица, независимых переменных Х имеет размерность (14 х 4).
Матрица, составленная из Y и X
| 1 | 4.07 | 182.94 | 1018 |
| 1 | 4 | 193.45 | 920 |
| 1 | 2.98 | 160.09 | 686 |
| 1 | 2.2 | 157.99 | 405 |
| 1 | 2.83 | 123.83 | 683 |
| 1 | 3 | 152.02 | 530 |
| 1 | 2.35 | 130.53 | 525 |
| 1 | 2.04 | 137.38 | 418 |
| 1 | 1.97 | 137.58 | 425 |
| 1 | 1.02 | 118.78 | 161 |
| 1 | 1.44 | 142.9 | 242 |
| 1 | 1.22 | 99.49 | 226 |
| 1 | 1.11 | 116.17 | 162 |
| 1 | 0.82 | 185.66 | 70 |
Транспонированная матрица.
| 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 4.07 | 4 | 2.98 | 2.2 | 2.83 | 3 | 2.35 | 2.04 | 1.97 | 1.02 | 1.44 | 1.22 | 1.11 | 0.82 |
| 182.94 | 193.45 | 160.09 | 157.99 | 123.83 | 152.02 | 130.53 | 137.38 | 137.58 | 118.78 | 142.9 | 99.49 | 116.17 | 185.66 |
| 1018 | 920 | 686 | 405 | 683 | 530 | 525 | 418 | 425 | 161 | 242 | 226 | 162 | 70 |
Матрица A T A.
| 14 | 31.05 | 2038.81 | 6471 |
| 31.05 | 83.37 | 4737.04 | 18230.79 |
| 2038.81 | 4737.04 | 307155.61 | 995591.55 |
| 6471 | 18230.79 | 995591.55 | 4062413 |
Полученная матрица имеет следующее соответствие:
| ∑n | ∑y | ∑x 1 | ∑x 2 |
| ∑y | ∑y 2 | ∑x 1 y | ∑x 2 y |
| ∑x 1 | ∑yx 1 | ∑x 1 2 | ∑x 2 x 1 |
| ∑x 2 | ∑yx 2 | ∑x 1 x 2 | ∑x 2 2 |
Найдем парные коэффициенты корреляции.
| Признаки x и y | ∑{x i } | ∑{y i } | ∑{x i y i } | |||
| Для y и x 1 | 2038.81 | 145.629 | 31.05 | 2.218 | 4737.044 | 338.36 |
| Для y и x 2 | 6471 | 462.214 | 31.05 | 2.218 | 18230.79 | 1302.199 |
| Для x 1 и x 2 | 6471 | 462.214 | 2038.81 | 145.629 | 995591.55 | 71113.682 |
| Признаки x и y | ||||
| Для y и x 1 | 731.797 | 1.036 | 27.052 | 1.018 |
| Для y и x 2 | 76530.311 | 1.036 | 276.641 | 1.018 |
| Для x 1 и x 2 | 76530.311 | 731.797 | 276.641 | 27.052 |
Матрица парных коэффициентов корреляции R:
| - | y | x 1 | x 2 |
| y | 1 | 0.558 | 0.984 |
| x 1 | 0.558 | 1 | 0.508 |
| x 2 | 0.984 | 0.508 | 1 |
Для отбора наиболее значимых факторов x i учитываются следующие условия:
- связь между результативным признаком и факторным должна быть выше межфакторной связи;
- связь между факторами должна быть не более 0.7. Если в матрице есть межфакторный коэффициент корреляции r xjxi > 0.7, то в данной модели множественной регрессии существует мультиколлинеарность.;
- при высокой межфакторной связи признака отбираются факторы с меньшим коэффициентом корреляции между ними.
В нашем случае все парные коэффициенты корреляции |r| Модель регрессии в стандартном масштабе Модель регрессии в стандартном масштабе предполагает, что все значения исследуемых признаков переводятся в стандарты (стандартизованные значения) по формулам:
где х ji - значение переменной х ji в i-ом наблюдении.
Таким образом, начало отсчета каждой стандартизованной переменной совмещается с ее средним значением, а в качестве единицы изменения принимается ее среднее квадратическое отклонение S .
Если связь между переменными в естественном масштабе линейная, то изменение начала отсчета и единицы измерения этого свойства не нарушат, так что и стандартизованные переменные будут связаны линейным соотношением:
t y = ∑β j t xj
Для оценки β-коэффициентов применим МНК. При этом система нормальных уравнений будет иметь вид:
r x1y =β 1 +r x1x2 β 2 + ... + r x1xm β m
r x2y =r x2x1 β 1 + β 2 + ... + r x2xm β m
...
r xmy =r xmx1 β 1 + r xmx2 β 2 + ... + β m
Для наших данных (берем из матрицы парных коэффициентов корреляции):
0.558 = β 1 + 0.508β 2
0.984 = 0.508β 1 + β 2
Данную систему линейных уравнений решаем методом Гаусса : β 1 = 0.0789; β 2 = 0.944;
Стандартизированная форма уравнения регрессии имеет вид:
y 0 = 0.0789x 1 + 0.944x 2
Найденные из данной системы β–коэффициенты позволяют определить значения коэффициентов в регрессии в естественном масштабе по формулам:
Стандартизированные частные коэффициенты регрессии
. Стандартизированные частные коэффициенты регрессии - β-коэффициенты (β j) показывают, на какую часть своего среднего квадратического отклонения S(у) изменится признак-результат y
с изменением соответствующего фактора х j на величину своего среднего квадратического отклонения (S хj) при неизменном влиянии прочих факторов (входящих в уравнение).
По максимальному β j можно судить, какой фактор сильнее влияет на результат Y.
По коэффициентам эластичности и β-коэффициентам могут быть сделаны противоположные выводы. Причины этого: а) вариация одного фактора очень велика; б) разнонаправленное воздействие факторов на результат.
Коэффициент β j может также интерпретироваться как показатель прямого (непосредственного) влияния j
-ого фактора (x j) на результат (y). Во множественной регрессии j
-ый фактор оказывает не только прямое, но и косвенное (опосредованное) влияние на результат (т.е. влияние через другие факторы модели).
Косвенное влияние измеряется величиной: ∑β i r xj,xi , где m - число факторов в модели. Полное влияние j-ого
фактора на результат равное сумме прямого и косвенного влияний измеряет коэффициент линейной парной корреляции данного фактора и результата - r xj,y .
Так для нашего примера непосредственное влияние фактора x 1 на результат Y в уравнении регрессии измеряется β j и составляет 0.0789; косвенное (опосредованное) влияние данного фактора на результат определяется как:
r x1x2 β 2 = 0.508 * 0.944 = 0.4796