Построение множественного линейного уравнения регрессии в Excel

В пакете анализа Microsoft Excel в режиме «Регрессия» реализованы следующие этапы множественной линейной регрессии:

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

= α₀ + α₁x₁ + α₂x₂ + …+ α_mx_m,

где - теоретические значения результативного признака, полученные путем подстановки соответствующих значений факторных признаков в уравнении регрессии; x₁, x₂,…, x_m – значение факторных признаков; α₀, α₁,…, α_m –параметры уравнения (коэффициенты регрессии).

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

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

– общую дисперсию результативного признака , отображающую влияние как основных, так и остаточных факторов:

,

где – среднее значение результативного признака ;

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

;

– остаточную дисперсию результативного признака , отображающую влияние только остаточных факторов:

.

При корреляционной связи результативного признака и факторов выполняется соотношение

, при этом .

Для анализа общего качества уравнение линейной многофакторной регрессии используют множественный коэффициент детерминации (квадрат коэффициента множественной корреляции ), которые рассчитываются по формуле

.

Этот коэффициент определяет долю вариации результативного признака, обусловленную изменению факторных признаков, входящих в многофакторную регрессивную модель.

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

В математической статистике доказывается, что если гипотеза :=0 выполняется, то величина

,

имеет распределение (Фишера) с числом степеней свободы .

При значениях >обусловлена в основном влиянием включенных в регрессионную модель факторов .

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

.

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

В математической статистике доказывается, что если гипотеза :=0 выполняется, то величина

,

имеет распределение Стьюдента с числом степеней свободы , где .

Гипотеза :можно найти границы доверительных интервалов для коэффициентов регрессии (; ).

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

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

В диалоговом окне режима работы «регрессии» задаются следующие параметры:

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

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

3. Метки в первой строке/метки в первом столбце – устанавливаются в активное состояние, если первая строка (столбец) в обходном диапазоне содержит заголовки.

4. Уровень надежности – устанавливается в активное состояние, если необходимо ввести уровень надежности отличный от уровня 95 %, применяемого по умолчанию.

5. Константа – ноль – флажок устанавливается в активное состояние, если требуется чтобы линия регрессии прошла через начало координат ().

6. Выходной интервал/Новый рабочий лист/Новая рабочая книга – указывается, куда необходимо вынести результаты исследования.

7. Остатки – флажок устанавливается в активное состояние, если требуется включить выходной диапазон в столбец остатков.

8. Стандартизованные остатки – флажок устанавливается в активное состояние, если требуется включить выходной диапазон столбец стандартизованных остатков.

9. График остатков – флажок устанавливается в активное состояние, если требуется вывести на рабочий лист точечные графики зависимости остатков от факторных признаков .

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

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