Интерпретация результатов

Тема: КОРРЕЛЯЦИОННЫЙ И РЕГРЕССИОННЫЙ АНАЛИЗ В EXCEL

ЛАБОРАТОРНАЯ РАБОТА №1

ОПРЕДЕЛЕНИЕ КОЭФФИЦИЕНТА ПАРНОЙ КОРРЕЛЯЦИИ В ПРОГРАММЕ EXCEL

Корреляционная связь - это неполная, вероятностная зависимость между показателями, которая проявляется только в массе наблюдений.

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

Множественная корреляциявозникает от взаимодействия нескольких факторов с результативным показателем.

Необходимые условия применения корреляционного анализа:

1. Наличие достаточно большого количества наблюдений о величине исследуемых факторных и результативных показателей.

2. Исследуемые факторы должны иметь количественное измерение и отражение в тех или иных источниках информации.

Применение корреляционного анализа позволяет решить следующие задачи:

1.Определить изменение результативного показателя под воздействием одного или нескольких факторов.

2. Установить относительную степень зависимости результативного показателя от каждого фактора.

Задание 1.

Имеются данные по 20 сельскохозяйственным хозяйствам. Найти коэффициент корреляции между величинами урожайности зерновых культур и качеством земли и оценить его значимость. Данные приведены в таблице.

Таблица. Зависимость урожайности зерновых культур от качества земли

Номер хозяйства	Качество земли, балл	Урожайность, ц/га
		19,5
		20,5
		20,8
		21,4
		23,3
		24,5
		24,2
		26,8
		27,2
		30,2

 

 

1. Для нахождения коэффициента корреляции использовать функцию КОРРЕЛ.

2. Значимость коэффициента корреляции проверяется по критерию Стьюдента.

 

Для рассматриваемого примера r=0,99, n=18.

 

Для нахождения квантиля распределения Стьюдента используется функция СТЬЮДРАСПОБР со следующими аргументам: Вероятность –0,05, Степени свободы –18.

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

ПОСТРОЕНИЕ РЕГРЕССИОННОЙ МОДЕЛИ СВЯЗИ МЕЖДУ ДВУМЯ ВЕЛИЧИНАМИ

 

Задание 2.

По данным задания 1:

1) построить уравнение регрессии (линейную модель), которое характеризует прямолинейную зависимость между качеством земли и урожайностью;

2). выполнить проверку адекватности полученной модели.

 

Ый способ.

1. На листе Excel выделить массив свободных ячеек из пяти строк и двух столбцов.

2. Вызвать функцию ЛИНЕЙН.

3.Указать для функции следующие аргументы: Изв_знач_y- столбец значений показателя Урожайность, ц/га; Изв_знач_x- столбец значений показателя Качество земли, балл; Константа –1, Стат– 1 (позволяет вычислить показатели, используемые для проверки адекватности модели. Если Стат– 0, то такие показатели вычисляться не будут.

4. Нажать комбинацию клавиш Ctrl-Shift-Enter.

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

Таблица 2
a1	a0
Se1	Se0
R2	Se
F	n-k-1
QR	Qe

 

a₁, a₀ – коэффициенты модели;

S_e1 S_e0 – стандартные ошибки коэффициентов. Чем точнее модель, тем меньше эти величины.

R² – коэффициент детерминации. Чем он больше, тем точнее модель.

F – статистика для проверки значимости модели.

n-k-1– число степеней свободы (n-объем выборки, k- количество входных переменных; в данном примере n=20, к=1)

Q_R – сумма квадратов, обусловленная регрессией;

Q_e – сумма квадратов ошибок.

 

5. Для проверки адекватности модели найти квантиль распределения Фишера F_f. с помощью функции FРАСПОБР. Для этого в любой свободной ячейке ввести функцию FРАСПОБР со следующими аргументами: Вероятность – 0,05, Степени_свободы_1–1, Степени_свободы_2–18. Если F> F_f, то модель адекватна исходным данным

6. Проверить адекватность построенной модели, используя расчетный уровень значимости (P). Ввести функцию FРАСП со следующими аргументами: X– значение статистики F, Степени_свободы_1–1, Степени_свободы_2– 18. Если расчетный уровень значимости P<α =0,05, то модель адекватна исходным данным.

Й способ.

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

 

1. Выбрать команду Сервис/Анализ данных/Регрессия. В диалоговом окне установить: Входной интервал Y – значения показателя Урожайность, ц/га,Входной интервал X – значения показателя Качество земли, балл.

2.Установить флажок Метки. В области Параметры вывода выбрать переключатель Выходной интервал и указать ячейку, с которой будет начинаться вывод результатов. Для получения результатов нажать кнопку ОК.

Интерпретация результатов.

Искомые коэффициенты модели находятся в столбце Коэффициенты:

а=2,532579627

в=0,501391759

Для данного примера уравнение модели имеет вид:

Y=2,53+0,5X

В данном примере с увеличением качества почвы на один балл, урожайность зерновых культур повышается в среднем на 0,5 ц/га.

 

Проверка адекватности модели выполняется по расчетному уровню значимости P, указанному в столбце Значимость F. Если расчетный уровень значимости меньше заданного уровня значимости α =0,05, то модель адекватна.

Проверка статистической значимости коэффициентов модели выполняется по расчетным уровням значимости P, указанным в столбце P-значение. Если расчетный уровень значимости меньше заданного уровня значимости α =0,05, то соответствующий коэффициент модели статистически значим.

Множественный R – коэффициент корреляции. Чем ближе его величина к 1, тем более тесная связь между изучаемыми показателями. Для данного примера
R= 0,99. Это позволяет сделать вывод, что качество земли – один из основных факторов, от которого зависит урожайность зерновых культур.

R-квадрат – коэффициент детерминации. Он получается возведением в квадрат коэффициента корреляции – R²=0,98. Он показывает, что урожайность зерновых культур на 98% зависит от качества почвы, а на долю других факторов приходится 0,02%.

3-ий способ. ГРАФИЧЕСКИЙ СПОСОБ ПОСТРОЕНИЯ МОДЕЛИ.

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

Получить линейную модель зависимости урожайности зерновых культур от качества земли.