Корреляционно-регрессионный анализ в Excel

Проведём корреляционно-регрессионный анализ взаимосвязи урожайности и затрат труда на 1 ц зерна. Для этого открываем лист Excel, в ячейки А1:А30 вводим значения факторного признака – урожайности зерновых культур, в ячейки В1:В30 значения результативного признака – затраттруда на 1 ц зерна. В меню Сервис выберем опцию Анализ данных. Щелкнув левой кнопкой мыши по этому пункту, откроем инструмент Регрессия. Щелкаем по кнопке OK, на экране появляется диалоговое окно Регрессия. В поле Входной интервал У вводим значения результативного признака (выделяя ячейки В1:В30), в поле Входной интервал Х вводим значения факторного признака (выделяя ячейки А1:А30). Отмечаем уровень вероятности 95%, выбираем Новый рабочий лист. Щелкаем по кнопке OK. На рабочем листе появляется таблица «ВЫВОД ИТОГОВ», в которой даны результаты вычисления параметров уравнения регрессии, коэффициента корреляции и другие показатели, позволяющие определить значимость коэффициента корреляции и параметров уравнения регрессии.

ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R	0,853301
R-квадрат	0,728123
Нормированный R-квадрат	0,718413
Стандартная ошибка	0,112121
Наблюдения
Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия		0,942676	0,942676	74,9876	2,09E-09
Остаток		0,351991	0,012571
Итого		1,294667
	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение	Нижние 95%	Верхние 95%	Нижние 95,0%	Верхние 95,0%
Y-пересечение	2,836242	0,200011	14,18042	2,64E-14	2,426538	3,245947	2,426538	3,245947
Переменная X 1	-0,06654	0,007684	-8,65954	2,09E-09	-0,08228	-0,0508	-0,08228	-0,0508

В данной таблице «Множественный R» - это коэффициент корреляции, «R-квадрат» - коэффициент детерминации. «Коэффициенты: Y-пересечение» - свободный член уравнения регрессии 2,836242; «Переменная Х1» – коэффициент регрессии -0,06654. Здесь имеются также значения F-критерия Фишера 74,9876, t-критерия Стьюдента 14,18042, «Стандартная ошибка 0,112121», которые необходимы для оценки значимости коэффициента корреляции, параметров уравнения регрессии и всего уравнения.

На основе данных таблицы построим уравнение регрессии: у_х=2,836-0,067х. Коэффициент регрессии а₁=-0,067 означает, что с повышением урожайности зерновых на 1 ц/га затраты труда на 1 ц зерна уменьшаются на 0,067 чел.-ч.

Коэффициент корреляции r=0,85>0,7, следовательно, связь между изучаемыми признаками в данной совокупности тесная. Коэффициент детерминации r²=0,73 показывает, что 73% вариации результативного признака (затрат труда на 1 ц зерна) вызвано действием факторного признака (урожайности зерновых).

В таблице критических точек распределения Фишера - Снедекора найдём критическое значение F-критерия при уровне значимости 0,05 и числе степеней свободы к₁=m-1=2-1=1 и k₂=n-m=30-2=28, оно равно 4,21. Так как рассчитанное значение критерия больше табличного (F=74.9896>4,21), то уравнение регрессии признаётся значимым.

Для оценки значимости коэффициента корреляции рассчитаем t-критерий Стьюдента:

В таблице критических точек распределения Стьюдента найдём критическое значение t-критерия при уровне значимости 0,05 и числе степеней свободы n-1=30-1=29, оно равно 2,0452. Так как расчётное значение больше табличного, то коэффициент корреляции является значимым.

ПРИЛОЖЕНИЯ

Приложение 1

МИНИСТЕРСТВО СЕЛЬСКОГО ХОЗЯЙСТВА РФ

ФГОУ ВПО САРАТОВСКИЙ ГОСУДАРСТВЕННЫЙ АГРАРНЫЙ УНИВЕРСИТЕТ ИМ. Н.И. ВАВИЛОВА

КАФЕДРА ЭКОНОМИЧЕСКОЙ КИБЕРНЕТИКИ

Дата добавления: 2014-12-07