2.5. Регрессионный анализ

Режим работы "Регрессия" служит для расчета параметров уравнения линейной регрессии и проверки его адекватности исследуемому процессу.

Для решения задачи регрессионного анализа в MS Excel выбираем в меню Сервис команду Анализ данных и инструмент анализа "Регрессия".

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

• Входной интервал Y - это диапазон данных по результативному признаку. Он должен состоять из одного столбца.

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

• Флажок Метки, устанавливается втом случае, если в первой строке диапазона стоит заголовок.

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

• Константа ноль. Данный флажок необходимо установить, если линия регрессии должна пройти через начало координат (а0=0).

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

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

После нажатия кнопки ОК в выходном диапазоне получаем отчет.

Построим уравнение зависимости ВВП от показателей, имеющих r > 0,7, полученных в предыдущем анализе. Результаты выполнения инструмента Регрессия представлены ниже:

Регрессионная статистика
Множественный R		0,999175
R-квадрат		0,998351
Нормированный R-квадрат		0,996867
Стандартная ошибка		3,091748
Наблюдения		20
Дисперсионный анализ
	df		SS		MS	F	Значимость F
Регрессия	9		57879,05		6431,005	672,7762	1,04E-12
Остаток	10		95,58908		9,558908
Итого	19		57974,64

	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение	Нижние 95%	Верхние 95%
Y-пересечение	165,9428	211,6086	0,7842	0,4511	-305,5506	637,4362
x5	-0,0649	0,0381	-1,7027	0,1195	-0,1498	0,0200
x8	0,0003	0,0008	0,3936	0,7021	-0,0015	0,0022
x9	-0,2459	0,5524	-0,4451	0,6657	-1,4767	0,9849
x13	1,2764	0,5491	2,3247	0,0424	0,0530	2,4997
x14	-0,8366	0,5861	-1,4275	0,1839	-2,1424	0,4692
x15	5,5956	3,7738	1,4828	0,1690	-2,8128	14,0041
x16	10,2395	9,9726	1,0268	0,3287	-11,9809	32,4598
x17	0,0674	0,0649	1,0384	0,3236	-0,0772	0,2121
x19	2,7203	5,1439	0,5288	0,6085	-8,7412	14,1817

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

.

EXCEL автоматически рассчитал коэффициенты множественной корреляции (множественный R) и детерминации (R-квадрат), а также скорректированный коэффициент детерминации (нормированный R-квадрат)

Мы получили следующие показатели тесноты связи: R²=0,998 ,R=0,99.

Между коэффициентом детерминации и скорректированным коэффициентом существуют незначительные различия, значит можно использовать R² и R для оценки тесноты связи. Множественный коэффициент корреляции (R = 0,99) свидетельствует о прямой связи между факторами и результатом, множественный коэффициент детерминации показывает, что 99,8% вариации ВВП связано с включенными в модель факторами.

Дадим оценку значимости уравнения в целом, условного начала и коэффициентов чистой регрессии.

Оценка значимости уравнения в целом проводится на основе дисперсионного анализа.

Предположим, что уравнение не значимо для генеральной совокупности (Н₀) в качестве альтернативной гипотезы выдвинем предположение о значимости уравнения (Н_А). Проверим эти гипотезы на 5% уровне значимости. В качестве критерия выберем критерий F-Фишера, его фактическое значение равно 672,77. Сравним его с критическим значением ,которое можно найти, используя встроенную функцию FРАСПОБР().

В нашем случае: =FРАСПОБР(0,05;9;10)=3,02.

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