Корелляционно-регрес.анализ (Excel)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Регрессионный анализ данных средствами Microsoft Excel

[expert_bq id=»1570″]Анализ данных — область информатики, занимающаяся построением и исследованием наиболее общих математических методов и вычислительных алгоритмов извлечения знаний из экспериментальных в широком смысле данных. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] R-квадрат – коэффициент детерминации. Он получается возведением в квадрат коэффициента корреляции – R 2 =0,98. Он показывает, что урожайность зерновых культур на 98% зависит от качества почвы, а на долю других факторов приходится 0,02%.