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

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

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

 = α ₀ + α ₁ x₁ + α ₂ x₂ + …+ α _m x_m,

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

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

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

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

,

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

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

;

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

.

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

, при этом .

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

.

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

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

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

,

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

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

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

.

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

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

,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

Пример построения линейной производственной функции

 

Рассмотрим пример построения линейной производственной функции в пакете анализа Microsoft Excel в режиме «Регрессия»

Данные о прибыли предприятия , затраченный капитал  затраты на труд   и общие затраты приведены в таблице  по кварталам за 2011-2013 годы.

 

Прибыль , тыс. руб.	Затраты капитала,  тыс. руб.	Затраты на труд , тыс. руб.	Общие затраты тыс. руб.
31972	18719,5	10939,5	29659
32290	18218,4	11410,6	29629
33698	19086,6	13436,4	32523
33568	20523,1	13611,9	34135
36098	21118,7	15286,3	36405
40724	23407,8	16495,2	39903
42081	22368,4	19346,6	41715
44174	25901,7	18194,3	44096
44237	24667,7	17538,3	42206
49300	22197,4	19849,6	42047
50701	24292,3	20305,7	44598
55338	27731,4	19880,6	47612

 

По этим данным определим уравнение линейной регрессии прибыли от затрат на капитал и труд и проведем анализ уравнения.

Для решения задачи используем режим «Регрессия». На рабочем листе наберем данные:

31972	18719,5	10939,5	29659
32290	18218,4	11410,6	29629
33698	19086,6	13436,4	32523
33568	20523,1	13611,9	34135
36098	21118,7	15286,3	36405
40724	23407,8	16495,2	39903
42081	22368,4	19346,6	41715
44174	25901,7	18194,3	44096
44237	24667,7	17538,3	42206
48300	22197,4	19849,6	42047
50701	24292,3	20305,7	44598
55338	27731,4	19880,6	47612
выручка	зат. кап.	зат. Труд.	общ. Зат.

которые вводим в режим «Регрессия». Первый столбик – значения Y, второй и третий – значения X. Указываем выходной интервал. После выполнения (ОК) получаем следующие таблицы:  

 

 

ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R	0,948205
R-квадрат	0,899093
Нормированный R-квадрат	0,876669
Стандартная ошибка	2729,753
Наблюдения	12
Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия	2	5,98E+08	2,99E+08	40,09548	3,29E-05
Остаток	9	67063958	7451551
Итого	11	6,65E+08
	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение	Нижние 95%	Верхние 95%	Нижние 95,0%	Верхние 95,0%
Y-пересечение	-4138,27	6541,76	-0,63259	0,54273	-18936,8	10660,2	-18936,8	10660,2
Переменная X 1	1,002626	0,49372	2,03075	0,07284	-0,11425	2,11949	-0,11425	2,11949
Переменная X 2	1,395363	0,4363	3,1977	0,0108	0,4082	2,3824	0,4082	2,3824

 

В таблице «Регрессивная статистика» сгенерированы результаты по регрессивной статистике: множественный R коэффициент корреляции; коэффициент детерминации ; стандартная ошибка; число наблюдений n.

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

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

.

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

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

 

Лекция 9. Кластерный анализ