Решение с помощью ППП Excel

1. Встроенная статистическая функция ЛИНЕЙН определяет параметры линейной регрессии y=a+bx. Порядок вычисления следующий:

1) введите исходные данные:

	А	В	С
	Территория региона	Прожиточный минимум, х	Среднемесячная зарплата, у

2) выделите область пустых ячеек 5´2 (5 строк, 2 столбца) для вывода результатов регрессионной статистики или область 1´2 – для получения только оценок коэффициентов регрессии;

3) активизируйте Мастер функций любым из способов:

а) в главном меню выберите Вставка/Функция;

б) на панели инструментов Стандартная щелкните по кнопке Вставка функции;

4) в окне Категория выберите Статистические, в окне Функция – ЛИНЕЙН. Щелкните по кнопке ОК;

5) заполните аргументы функции следующим образом:

Известные_значения_y – диапазон, содержащий данные результативного признака (С2:С13);

Известные_значения_x – диапазон, содержащий данные факторов независимого признака (В2:В13);

Константа – логическое значение, которое указывает на наличие или на отсутствие свободного члена в уравнении: если Константа=1, то свободный член рассчитывается обычным образом, если Константа=0, то свободный член равен 0 (указать 1);

Статистика – логическое значение, которое указывает, выводить дополнительную информацию по регрессионному анализу или нет: Статистика=1 - дополнительная информация выводится, Статистика=0 - выводятся только оценки параметров уравнения (указать 1).

Щелкните по кнопке ОК;

6) в левой верхней ячейке выделенной области появится первый элемент итоговой таблицы. Чтобы раскрыть всю таблицу, нажмите на клавишу F2, а затем – на комбинацию клавиш CTRL + SHIFT + ENTER.

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

Значение коэффициента b	Значение коэффициента a
Среднеквадратическое отклонение b	Среднеквадратическое отклонение a
Коэффициент детерминации R2	Среднеквадратическое отклонение y
F - статистика	Число степеней свободы
Регрессионная сумма квадратов	Остаточная сумма квадратов

Для вычисления параметров экспоненциальной кривой y=a×b^x в MS Excel применяется встроенная статистическая функция ЛГРФПРИБЛ. Порядок вычисления аналогичен применению функции ЛИНЕЙН.

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

1) проверьте доступ к пакету анализа. В главном меню последовательно выберите Сервис / Настройки. Установите флажок Пакет анализа (должен стоять флажок);

2) в главном меню выберите Сервис / Анализ данных / Регрессия. Щелкните по кнопке ОК;

3) заполните диалоговое окно ввода данных и параметров вывода следующим образом:

Входной интервал Y – диапазон, содержащий данные результативного признака ($C$1:$C$13);

Входной интервал X – диапазон, содержащий данные факторов независимого признака ($B$1:$B$13);

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

Константа – ноль – флажок, указывающий на наличие или отсутствие свободного члена в уравнении (без флажка);

Выходной интервал – достаточно указать левую верхнюю ячейку будущего диапазона;

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

Если необходимо получить информацию и графики остатков, установите соответствующие флажки в диалоговом окне. Щелкните по кнопке ОК.

Глава 3
Множественная регрессия и корреляция

Основные понятия и определения.

Множественная регрессия – уравнение связи с несколькими независимыми переменными:

y=f(x₁, x₂,…,x_p),

где y – зависимая переменная (результативный признак);

x₁, x₂,…,x_p – независимые переменные (факторы).

Для построения уравнения множественной регрессии чаще используются следующие функции:

· линейная y= a+b₁x₁+b₂x₂+…+b_px_p+e;

· степенная ;

· экспонента y=e ;

· гипербола .

Можно использовать и другие функции, приводимые к линейному виду.

Для оценки параметров уравнения множественной регрессии применяют метод наименьших квадратов (МНК). Для линейных уравнений и нелинейных уравнений, приводимых к линейным, строится следующая система нормальных уравнений, решение которой позволяет получить оценки параметров регрессии:

,

,

…………………………………………………

.

Для ее решения может быть применен метод определителей:

, ,…, ,

где Da, Db₁,…, Db_p – частные определители, которые получаются заменой соответствующего столбца матрицы определителя системы данными левой части системы;

- определитель системы.

Другой вид уравнения множественной регрессии – уравнение регрессии в стандартизованном масштабе: ,

где , – стандартизованные переменные;

b_i – стандартизованные коэффициенты регрессии.

К уравнению множественной регрессии в стандартизованном масштабе применим МНК. Стандартизованные коэффициенты регрессии (b -коэффициенты) определяются из следующей системы уравнений:

,

,

…………………………………..

.

Связь коэффициентов множественной регрессии b_i со стандартизованными коэффициентами b_i описывается соотношением . Параметр a определяется как .

Средние коэффициенты эластичности для линейной регрессии рассчитываются по формуле: .

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

Тесноту совместного влияния факторов на результат оценивает индекс множественной корреляции: .

Значение индекса множественной корреляции лежит в пределах от 0 до 1 и должно быть больше или равно максимальному парному индексу корреляции:

.

Индекс множественной корреляции для уравнения в стандартизованном масштабе можно записать в виде: .

При линейной зависимости коэффициент множественной корреляции можно определить через матрицу парных коэффициентов корреляции:

, где – определитель матрицы парных коэффициентов корреляции; – определитель матрицы межфакторной корреляции.

Частные коэффициенты (или индексы) корреляции, измеряющие влияние на y фактора x_i при неизменном уровне других факторов, можно определить по формуле или по рекуррентной формуле .

Частные коэффициенты корреляции изменяются в пределах от –1 до 1.

Качество построенной модели в целом оценивает коэффициент (индекс) детерминации. Коэффициент множественной детерминации рассчитывается как квадрат индекса множественной корреляции: .

Скорректированный индекс множественной детерминации содержит поправку на число степеней свободы и рассчитывается по формуле:

,

где n – число наблюдений; m – число факторов.

Значимость уравнения множественной регрессии в целом оценивается с помощью F-критерия Фишера: .

Частный F-критерий оценивает статистическую значимость присутствия каждого из факторов в уравнении. В общем виде для фактора x_i частный F-критерий определится как .

Оценка значимости коэффициентов чистой регрессии с помощью t-критерия Стъюдента сводится к вычислению значения , где – средняя квадратическая ошибка коэффициента регрессии b_i, которая может быть определена по следующей формуле: .

При построении уравнения множественной регрессии может возникнуть проблема мультиколлинеарности факторов, их тесной линейной связанности.

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

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

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

Если бы факторы не коррелировали между собой, то матрица парных коэффициентов корреляции между факторами была бы единичной матрицей, поскольку все недиагональные элементы были бы равны нулю. Так, для включающего три объясняющих переменных уравнения

матрица коэффициентов корреляции между факторами имела бы определитель, равный 1:

,

так как и .

Если же, наоборот, между факторами существует полная линейная зависимость и все коэффициенты корреляции равны 1, то определитель такой матрицы равен 0:

.

Чем ближе к 0 определитель матрицы межфакторной корреляции, тем сильнее мультиколлинеарность факторов и ненадежнее результаты множественной регрессии. И наоборот, чем ближе к 1 определитель матрицы межфакторной корреляции, тем меньше мультиколлинеарность факторов.

Проверка мультиколлинеарности факторов может быть проведена методом испытания гипотезы о независимости переменных H₀ : . Доказано, что величина имеет приближенное распределение x² с степенями свободы. Если фактическое значение x² превосходит табличное (критическое) , то гипотеза H₀ отклоняется. Это означает, что , недиагональные ненулевые коэффициенты корреляции указывают на коллинеарность факторов. Мультиколлинеарность считается доказанной.

Для применения МНК требуется, чтобы дисперсия остатков была гомоскедастичной. Это значит, что для каждого значения фактора x_j остатки e_i имеют одинаковую дисперсию. Если это условие не соблюдается, то имеет место гетероскедастичность.

При нарушении гомоскедастичности мы имеем неравенства , .

При малом объеме выборки для оценки гетероскедастичности может использоваться метод Гольдфельда – Квандта. Основная идея теста Гольдфельда – Квандта состоит в следующем:

1) упорядочение n наблюдений по мере возрастания переменной x;

2) исключение из рассмотрения C центральных наблюдений; при этом (n-C): 2>p, где p – число оцениваемых параметров:

3) разделение совокупности из (n-C) наблюдений на две группы (соответственно с малыми и с большими значениями фактора x) и определение по каждой из групп уравнений регрессии;

4) определение остаточной суммы квадратов для первой (S₁) и второй (S₂) групп и нахождение их отношения: R= S₁: S₂.

При выполнении нулевой гипотезы о гомоскедастичности отношение R будет удовлетворять F-критерию со степенями свободы ((n-C-2p)/ 2) для каждой остаточной суммы квадратов. Чем больше величина R превышает табличное значение F-критерия, тем более нарушена предпосылка о равенстве дисперсий остаточных величин.

Уравнения множественной регрессии могут включать в качестве независимых переменных качественные признаки (например, профессия, пол, образование, климатические условия, отдельные регионы и т.д.). Чтобы ввести такие переменные в регрессионную модель, их необходимо упорядочить и присвоить им те или иные значения, т.е. качественные переменные преобразовать в количественные.

Такого вида сконструированные переменные принято в эконометрике называть фиктивными переменными. Например, включать в модель фактор «пол» в виде фиктивной переменной можно в следующем виде:

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