Корреляционно-регрессионный анализ в 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х. Коэффициент регрессии а1=-0,067 означает, что с повышением урожайности зерновых на 1 ц/га затраты труда на 1 ц зерна уменьшаются на 0,067 чел.-ч.
Коэффициент корреляции r=0,85>0,7, следовательно, связь между изучаемыми признаками в данной совокупности тесная. Коэффициент детерминации r2=0,73 показывает, что 73% вариации результативного признака (затрат труда на 1 ц зерна) вызвано действием факторного признака (урожайности зерновых).
В таблице критических точек распределения Фишера - Снедекора найдём критическое значение F-критерия при уровне значимости 0,05 и числе степеней свободы к1=m-1=2-1=1 и k2=n-m=30-2=28, оно равно 4,21. Так как рассчитанное значение критерия больше табличного (F=74.9896>4,21), то уравнение регрессии признаётся значимым.
Для оценки значимости коэффициента корреляции рассчитаем t-критерий Стьюдента:
 |
В таблице критических точек распределения Стьюдента найдём критическое значение t-критерия при уровне значимости 0,05 и числе степеней свободы n-1=30-1=29, оно равно 2,0452. Так как расчётное значение больше табличного, то коэффициент корреляции является значимым.