Как проверить значимость коэффициента корреляции в excel
Перейти к содержимому

Как проверить значимость коэффициента корреляции в excel

  • автор:

Корреляционный анализ в EXCEL

Аннуитетные платежи онлайн

Расчет аннуитетных платежей по схеме постнумерандо и пренумерандо с помощью удобного калькулятора.

Профессии будущего

РБК Тренды изучили прогнозы российских и зарубежных футурологов, и составили список самых востребованных профессий в ближайшие 30 лет. Это профессии из 19 отраслей: от медицины и транспорта до культуры и космоса

Налоговый вычет на обучение

√ 120 тыс. руб. — максимальная сумма расходов на обучение
√ вычет от государства
√ вычет от работодателя

  • Задать вопрос или оставить комментарий
  • Помощь в решении
  • Поиск
  • Поддержать проект

Правила ввода данных

Задать свои вопросы или оставить замечания можно внизу страницы в разделе Disqus .
Можно также оставить заявку на помощь в решении своих задач у наших проверенных партнеров (здесь или здесь).

Поиск

Задать свои вопросы или оставить замечания можно внизу страницы в разделе Disqus .
Можно также оставить заявку на помощь в решении своих задач у наших проверенных партнеров (здесь или здесь).

Как проверить значимость коэффициента корреляции в excel

Исследователя нередко интересует, как связаны между собой две или большее количество переменных в одной или нескольких изучаемых выборках. Например, может ли рост влиять на вес человека или может ли давление влиять на качество продукции? Такого рода зависимость между переменными величинами называется корреляционной, или корреляцией. Задача корреляционного анализа сводится к установлению направления (положительное или отрицательное) и формы (линейная, нелинейная) связи между варьирующими признаками, измерению ее тесноты, и, наконец, к проверке уровня значимости полученных коэффициентов корреляции. В статье рассматривается применение корреляционного анализа с расчетом коэффициентов корреляции Стьюдента. Изложенный материал дает общие сведения об оценке степени тесноты взаимосвязи между переменными.

корреляционный анализ
коэффициенты корреляции
переменные
случайные колебания
генеральная совокупность

1. Бондаренко В.А., Донец З.Г., Цыплакова О.Н. Теория игр и финансовые рынки // Финансово-экономические и учетно-аналитические проблемы развития региона: материалы Ежегодной 78-й научно-практической конференции. – 2014. – С. 231–236.

2. Бондаренко В.А., Мамаев И.И., Сахнюк П.А., Сахнюк Т.И. Решение задачи планирования посевов с использованием теории игр // Экономические, инновационные и информационные проблемы развития региона: материалы Международной научно-практической конференции. – 2014. – С. 56–62.

3. Бондаренко Д.В., Бражнев С.М., Литвин Д.Б., Варнавский А.А. Метод повышения точности измерения векторных величин // Наука Парк. – 2013. № 6 (16). – С. 66–69.

4. Гулай Т.А., Литвин Д.Б., Попова С.В., Мелешко С.В. Прогнозирование в регрессионном анализе при построении статистических моделей экономических задач с помощью программы Microsoft excel // Экономика и предпринимательство. – 2017. – № 8–2 (85–2). – С. 688–692.

5. Долгополова А.Ф., Гулай Т.А., Литвин Д.Б. Финансовая математика в инвестиционном проектировании (учебное пособие) // Международный журнал прикладных и фундаментальных исследований. – 2014. – № 8–2. – С. 178–179.

6. Долгополова А.Ф., Гулай Т.А., Литвин Д.Б. Совершенствование экономических механизмов для решения проблем экологической безопасности // Информационные системы и технологии как фактор развития экономики региона. II Международная научно-практическая конференция, 2013. – С. 68–71.

7. Литвин Д.Б. Дифференциальное исчисление в исследовании производственных моделей. // В сборнике: экономические приоритеты и информационный механизм устойчивого развития регионов России: Сборник научных статей Всероссийской научно-практической конференции, 2017. – С. 102–105.

8. Литвин Д.Б., Гулай Т.А., Жукова В.А., Мамаев И.И. Модель экономического роста с распределенным запаздыванием в инвестиционной сфере. // Вестник АПК Ставрополья. – 2017. № 2 (26). – С. 225–228.

9. Литвин Д.Б., Шепеть И.П. Моделирование роста производства с учетом инвестиций и выбытием фондов. // Социально-экономические и информационные проблемы устойчивого развития региона: Международная научно-практическая конференция. – 2015. – С. 114–116.

10. Литвин Д.Б., Шепеть И.П., Бондарев В.Г., Литвина Е.Д. Применение дифференциального исчисления функций нескольких переменных к разработке алгоритма определения координат объекта. // Финансово-экономические и учетно-аналитические проблемы развития региона: Материалы Ежегодной 78-й научно-практической конференции, 2014. – С. 242–246.

Как неоднократно отмечалось, для статистического вывода о наличии или отсутствии корреляционной связи между исследуемыми переменными необходимо произвести проверку значимости выборочного коэффициента корреляции. В связи с тем что надежность статистических характеристик, в том числе и коэффициента корреляции, зависит от объема выборки, может сложиться такая ситуация, когда величина коэффициента корреляции будет целиком обусловлена случайными колебаниями в выборке, на основании которой он вычислен. При существенной связи между переменными коэффициент корреляции должен значимо отличаться от нуля. Если корреляционная связь между исследуемыми переменными отсутствует, то коэффициент корреляции генеральной совокупности ρ равен нулю [9].

Как всякая статистическая характеристика, выборочный коэффициент корреляции является случайной величиной, т.е. его значения случайно рассеиваются вокруг одноименного параметра генеральной совокупности (истинного значения коэффициента корреляции) [1].

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

Процедура проверки значимости начинается с формулировки нулевой гипотезы H0. В общем виде она заключается в том, что между параметром выборки и параметром гене ральной совокупности нет каких-либо существенных различий. Альтернативная гипотеза H1 состоит в том, что между этими параметрами имеются существенные различия. Например, при проверке наличия корреляции в генеральной совокупности нулевая гипотеза заключается в том, что истинный коэффициент корреляции равен нулю svl1.wmf. Если в результате проверки окажется, что нулевая гипотеза не приемлема, то выборочный коэффициент корреляции svl2.wmfзначимо отличается от нуля (нулевая гипотеза отвергается и принимается альтернативная Н1) [7, 10].

При проверке значимости исследователь устанавливает уровень значимости α, который дает определенную практическую уверенность в том, что ошибочные заключения будут сделаны только в очень редких случаях. Уровень значимости выражает вероятность того, что нулевая гипотеза Н0 отвергается в то время, когда она в действительности верна. Ясно, что имеет смысл выбирать эту вероятность как можно меньшей [2].

Пусть известно распределение выборочной характеристики, являющейся несмещенной оценкой параметра генеральной совокупности. Выбранному уровню значимости α соответствуют под кривой этого распределения заштрихованные площади (см. рис. 1). Незаштрихованная площадь под кривой распределения определяет вероятность

svl3.wmf

.

Границы отрезков на оси абсцисс под заштрихованными площадями называют критическими значениями, а сами отрезки образуют критическую область, или область отклонения гипотезы [3].

svl4.wmf

Когда же надо убедиться в том, что одна величина в среднем строго больше или мень ше другой, используется односторонняя критическая область. Если распределение выборочной характеристики симметрично, то уровень значимости двусторонней критической области равен α, а односторонней (см. рис. 1). Далее мы лишь укажем критерии значимости для различных процедур, не останавливаясь на их построении [6].

sav1.tif

Рис. 1 Проверка нулевой гипотезы H0

Проверяя значимость коэффициента парной корреляции, устанавливают наличие или отсутствие корреляционной связи между исследуемыми явлениями. При отсутствии связи коэффициент корреляции генеральной совокупности равен нулю (p=0). Процедура проверки начинается с формулировки нулевой и альтернативной гипотез [8]:

Н0: различие между выборочным коэффициентом корреляции r и ρ = 0 незначимо,

Н1: различие между r и p=0 значимо, и следовательно, между переменными у и х имеется существенная связь. Из альтернативной гипотезы следует, что нужно воспользоваться двусторонней критической областью.

svl5.wmf

Выборочный коэффициент корреляции при определенных предпосылках связан со случайной величиной t, подчиняющейся распределению Стьюдента с степенями свободы [4].

Вычисленная по результатам выборки статистика

svl6.wmf

(1)

сравнивается с критическим значением, определяемым по таблице распределения Стьюдента при заданном уровне значимости α и svl8.wmfстепенях свободы. Правило применения критерия заключается в следующем: если svl9.wmf, то нулевая гипотеза на уровне значимости α отвергается, т. е. связь между переменными значима; если

svl11.wmf

,

svl12.wmf

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

Рассмотрим на конкретном примере проверку гипотезы о значимости выборочного коэффициента корреляции.

Для 15 пар порядковых переменных (x, y) был вычислен коэффициент корреляции Спирмена, который оказался равным svl13.wmf. При уровне значимости svl14.wmfпроверить гипотезу H0 о равенстве генерального коэффициента ранговой корреляции нулю svl15.wmfпри конкурирующей гипотезе svl16.wmf.

svl17.wmf

Найдем наблюдаемое значение критерия:

svl18.wmf

svl19.wmf

Найдем значения границ двусторонней критической области из условия, что при малых объемах выборок критерий t распределен (при справедливости нулевой гипотезы) по закону распределения Стьюдента с числом степеней свободы

На основе таблицы «Критические точки распределения Стьюдента» найдем границы двусторонней критической области на основе

svl20.wmfи svl21.wmf

Полученные результаты покажем графически (рис. 2).

Поскольку наблюдаемое значение критерия попало в критическую область, то следует отклонить нулевую гипотезу в пользу альтернативной гипотезы. Это означает, что коэффициент ранговой корреляции значим: между двумя порядковыми случайными величинами X и Y в генеральной совокупности имеется значимая связь, которая говорит о наличии связи этих двух случайных величин.

Корреляция и ковариация в EXCEL

Коэффициент корреляции ( критерий корреляции Пирсона, англ. Pearson Product Moment correlation coefficient) определяет степень линейной взаимосвязи между случайными величинами.

где Е[…] – оператор математического ожидания , μ и σ – среднее случайной величины и ее стандартное отклонение .

Как следует из определения, для вычисления коэффициента корреляции требуется знать распределение случайных величин Х и Y. Если распределения неизвестны, то для оценки коэффициента корреляции используется выборочный коэффициент корреляции r ( еще он обозначается как R xy или r xy ) :

Как видно из формулы для расчета корреляции , знаменатель (произведение стандартных отклонений с точностью до безразмерного множителя) просто нормирует числитель таким образом, что корреляция оказывается безразмерным числом от -1 до 1. Корреляция и ковариация предоставляют одну и туже информацию, но корреляцией удобнее пользоваться, т.к. она является безразмерной величиной.

Рассчитать коэффициент корреляции и ковариацию выборки в MS EXCEL не представляет труда, так как для этого имеются специальные функции КОРРЕЛ() и КОВАР() . Гораздо сложнее разобраться, как интерпретировать полученные значения, большая часть статьи посвящена именно этому.

Теоретическое отступление

Напомним, что корреляционной связью называют статистическую связь, состоящую в том, что различным значениям одной переменной соответствуют различные средние значения другой (с изменением значения Х среднее значение Y изменяется закономерным образом). Предполагается, что обе переменные Х и Y являются случайными величинами и имеют некий случайный разброс относительно их среднего значения .

Примечание . Если случайную природу имеет только одна переменная, например, Y, а значения другой являются детерминированными (задаваемыми исследователем), то можно говорить только о регрессии.

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

Корреляционная связь между переменными может возникнуть несколькими путями:

  1. Наличие причинной зависимости между переменными. Например, количество инвестиций в научные исследования (переменная Х) и количество полученных патентов (Y). Первая переменная выступает как независимая переменная (фактор) , вторая — зависимая переменная (результат) . Необходимо помнить, что зависимость величин обуславливает наличие корреляционной связи между ними, но не наоборот.
  2. Наличие сопряженности (общей причины). Например, с ростом организации растет фонд оплаты труда (ФОТ) и затраты на аренду помещений. Очевидно, что неправильно предполагать, что аренда помещений зависит от ФОТ. Обе этих переменных во многих случаях линейно зависят от количества персонала.
  3. Взаимовлияние переменных (при изменении одной, вторая переменная изменяется, и наоборот). При таком подходе допустимы две постановки задачи; любая переменная может выступать как в роли независимой переменной и в роли зависимой.

Таким образом, показатель корреляции показывает, насколько сильна линейная взаимосвязь между двумя факторами (если она есть), а регрессия позволяет прогнозировать один фактор на основе другого.

Корреляция , как и любой другой статистический показатель, при правильном применении может быть полезной, но она также имеет и ограничения по использованию. Если диаграмма рассеяния показывает четко выраженную линейную зависимость или полное отсутствие взаимосвязи, то корреляция замечательно это отразит. Но, если данные показывают нелинейную взаимосвязь (например, квадратичную), наличие отдельных групп значений или выбросов, то вычисленное значение коэффициента корреляции может ввести в заблуждение (см. файл примера ).

Корреляция близкая к 1 или -1 (т.е. близкая по модулю к 1) показывает сильную линейную взаимосвязь переменных, значение близкое к 0 показывает отсутствие взаимосвязи. Положительная корреляция означает, что с ростом одного показателя другой в среднем увеличивается, а при отрицательной – уменьшается.

Для вычисления коэффициента корреляции требуется, чтобы сопоставляемые переменные удовлетворяли следующим условиям:

  • количество переменных должно быть равно двум;
  • переменные должны быть количественными (например, частота, вес, цена). Вычисленное среднее значение этих переменных имеет понятный смысл: средняя цена или средний вес пациента. В отличие от количественных, качественные (номинальные) переменные принимают значения лишь из конечного набора категорий (например, пол или группа крови). Этим значениям условно сопоставлены числовые значения (например, женский пол – 1, а мужской – 2). Понятно, что в этом случае вычисление среднего значения , которое требуется для нахождения корреляции , некорректно, а значит некорректно и вычисление самой корреляции ;
  • переменные должны быть случайными величинами и иметь нормальное распределение.

Двумерные данные могут иметь различную структуру. Для работы с некоторыми из них требуются определенные подходы:

  • Для данных с нелинейной связью корреляцию нужно использовать с осторожностью. Для некоторых задач бывает полезно преобразовать одну или обе переменных так, чтобы получить линейную взаимосвязь (для этого требуется сделать предположение о виде нелинейной связи, чтобы предложить нужный тип преобразования).
  • С помощью диаграммы рассеяния у некоторых данных можно наблюдать неравную вариацию (разброс). Проблема неодинаковой вариации состоит в том, что места с высокой вариацией не только предоставляют наименее точную информацию, но и оказывают наибольшее влияние при расчете статистических показателей. Эту проблему также часто решают с помощью преобразования данных, например, с помощью логарифмирования.
  • У некоторых данных можно наблюдать разделение на группы (clustering), что может свидетельствовать о необходимости разделения совокупности на части.
  • Выброс (резко отклоняющееся значение) может исказить вычисленное значение коэффициента корреляции. Выброс может быть причиной случайности, ошибки при сборе данных или могут действительно отражать некую особенность взаимосвязи. Так как выброс сильно отклоняется от среднего значения, то он вносит большой вклад при расчете показателя. Часто расчет статистических показателей производят с и без учета выбросов.

Использование MS EXCEL для расчета корреляции

В качестве примера возьмем 2 переменные Х и Y и, соответственно, выборку состоящую из нескольких пар значений (Х i ; Y i ). Для наглядности построим диаграмму рассеяния .

Примечание : Подробнее о построении диаграмм см. статью Основы построения диаграмм . В файле примера для построения диаграммы рассеяния использована диаграмма График , т.к. мы здесь отступили от требования случайности переменной Х (это упрощает генерацию различных типов взаимосвязей: построение трендов и заданный разброс). В случае реальных данных необходимо использовать диаграмму типа Точечная (см. ниже).

Расчеты корреляции проведем для различных случаев взаимосвязи между переменными: линейной, квадратичной и при отсутствии связи .

Примечание : В файле примера можно задать параметры линейного тренда (наклон, пересечение с осью Y) и степень разброса относительно этой линии тренда. Также можно настроить параметры квадратичной зависимости.

В файле примера для построения диаграммы рассеяния в случае отсутствия зависимости переменных использована диаграмма типа Точечная. В этом случае точки на диаграмме располагаются в виде облака.

Примечание : Обратите внимание, что изменяя масштаб диаграммы по вертикальной или горизонтальной оси, облаку точек можно придать вид вертикальной или горизонтальной линии. Понятно, что при этом переменные останутся независимыми.

Как было сказано выше, для расчета коэффициента корреляции в MS EXCEL существует функций КОРРЕЛ() . Также можно воспользоваться аналогичной функцией PEARSON() , которая возвращает тот же результат.

Для того, чтобы удостовериться, что вычисления корреляции производятся функцией КОРРЕЛ() по вышеуказанным формулам, в файле примера приведено вычисление корреляции с помощью более подробных формул:

Примечание : Квадрат коэффициента корреляции r равен коэффициенту детерминации R2, который вычисляется при построении линии регрессии с помощью функции КВПИРСОН() . Значение R2 также можно вывести на диаграмме рассеяния , построив линейный тренд с помощью стандартного функционала MS EXCEL (выделите диаграмму, выберите вкладку Макет , затем в группе Анализ нажмите кнопку Линия тренда и выберите Линейное приближение ). Подробнее о построении линии тренда см., например, в статье о методе наименьших квадратов .

Использование MS EXCEL для расчета ковариации

Ковариация близка по смыслу с дисперсией (также является мерой разброса) с тем отличием, что она определена для 2-х переменных, а дисперсия — для одной. Поэтому, cov(x;x)=VAR(x).

Для вычисления ковариации в MS EXCEL (начиная с версии 2010 года) используются функции КОВАРИАЦИЯ.Г() и КОВАРИАЦИЯ.В() . В первом случае формула для вычисления аналогична вышеуказанной (окончание обозначает Генеральная совокупность ), во втором – вместо множителя 1/n используется 1/(n-1), т.е. окончание обозначает Выборка .

Примечание : Функция КОВАР() , которая присутствует в MS EXCEL более ранних версий, аналогична функции КОВАРИАЦИЯ.Г() .

Примечание : Функции КОРРЕЛ() и КОВАР() в английской версии представлены как CORREL и COVAR. Функции КОВАРИАЦИЯ.Г() и КОВАРИАЦИЯ.В() как COVARIANCE.P и COVARIANCE.S.

Дополнительные формулы для расчета ковариации :

Эти формулы используют свойство ковариации :

Если переменные x и y независимые, то их ковариация равна 0. Если переменные не являются независимыми, то дисперсия их суммы равна:

VAR(x+y)= VAR(x)+ VAR(y)+2COV(x;y)

А дисперсия их разности равна

VAR(x-y)= VAR(x)+ VAR(y)-2COV(x;y)

Оценка статистической значимости коэффициента корреляции

При проверке значимости коэффициента корреляции нулевая гипотеза состоит в том, что коэффициент корреляции равен нулю, альтернативная — не равен нулю (про проверку гипотез см. статью Проверка гипотез ).

Для того чтобы проверить гипотезу, мы должны знать распределение случайной величины, т.е. коэффициента корреляции r. Обычно, проверку гипотезы осуществляют не для r, а для случайной величины t r :

которая имеет распределение Стьюдента с n-2 степенями свободы.

Если вычисленное значение случайной величины |t r | больше, чем критическое значение t α,n-2 (α- заданный уровень значимости ), то нулевую гипотезу отклоняют (взаимосвязь величин является статистически значимой).

Надстройка Пакет анализа

В надстройке Пакет анализа для вычисления ковариации и корреляции имеются одноименные инструменты анализа .

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

  • Входной интервал : нужно ввести ссылку на диапазон с исходными данными для 2-х переменных
  • Группирование : как правило, исходные данные вводятся в 2 столбца
  • Метки в первой строке : если установлена галочка, то Входной интервал должен содержать заголовки столбцов. Рекомендуется устанавливать галочку, чтобы результат работы Надстройки содержал информативные столбцы
  • Выходной интервал : диапазон ячеек, куда будут помещены результаты вычислений. Достаточно указать левую верхнюю ячейку этого диапазона.

Надстройка возвращает вычисленные значения корреляции и ковариации (для ковариации также вычисляются дисперсии обоих случайных величин).

Как проверить значимость коэффициента корреляции в excel

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949
ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *