Как рассчитать коэффициент корреляции в Excel

В сегодняшней статье речь пойдет о том, как переменные могут быть связаны друг с другом. С помощью корреляции мы сможем определить, существует ли связь между первой и второй переменной. Надеюсь, это занятие покажется вам не менее увлекательным, чем предыдущие!

Корреляция измеряет мощность и направление связи между x и y. На рисунке представлены различные типы корреляции в виде графиков рассеяния упорядоченных пар (x, y). По традиции переменная х размещается на горизонтальной оси, а y — на вертикальной.

виды корреляций

График А являет собой пример положительной линейной корреляции: при увеличении х также увеличивается у, причем линейно. График В показывает нам пример отрицательной линейной корреляции, на котором при увеличении х у линейно уменьшается. На графике С мы видим отсутствие корреляции между х и у. Эти переменные никоим образом не влияют друг на друга.

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

Оставшаяся часть статьи посвящена линейным взаимосвязям между зависимой и независимой переменными.

Коэффициент корреляции

Коэффициент корреляции, r, предоставляет нам как силу, так и направление связи между независимой и зависимой переменными. Значения r находятся в диапазоне между — 1.0 и + 1.0. Когда r имеет положительное значение, связь между х и у является положительной (график A на рисунке), а когда значение r отрицательно, связь также отрицательна (график В). Коэффициент корреляции, близкий к нулевому значению, свидетельствует о том, что между х и у связи не существует график С).

Сила связи между х и у определяется близостью коэффициента корреляции к — 1.0 или +- 1.0. Изучите следующий рисунок.

сила взаимсвязи между переменными

График A показывает идеальную положительную корреляцию между х и у при r = + 1.0. График В — идеальная отрицательная корреляция между х и у при r = — 1.0. Графики С и D — примеры более слабых связей между зависимой и независимой переменными.

Коэффициент корреляции, r, определяет, как силу, так и направление связи между зависимой и независимой переменными. Значения r находятся в диапазоне от — 1.0 (сильная отрицательная связь) до + 1.0 (сильная положительная связь). При r= 0 между переменными х и у нет никакой связи.

Мы можем вычислить фактический коэффициент корреляции с помощью следующего уравнения:

уравнение корреляции

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

таблица данных

формула корреляции

результат корреляции

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

Какова выгода устанавливать связь между подобными переменными? Отличный вопрос. Если обнаруживается, что связь существует, мы можем предугадать экзаменационные результаты на основе определенного количества часов, посвященных изучению предмета. Проще говоря, чем сильнее связь, тем точнее будет наше предсказание.

Использование Excel для вычисления коэффициентов корреляции

Я уверен, что, взглянув на эти ужасные вычисления коэффициентов корреляции, вы испытаете истинную радость, узнав, что программа Excel может выполнить за вас всю эту работу с помощью функции КОРРЕЛ со следующими характеристиками:

КОРРЕЛ (массив 1; массив 2),

где:

массив 1 = диапазон данных для первой переменной,

массив 2 = диапазон данных для второй переменной.

Например, на рисунке показана функция КОРРЕЛ, используемая при вычислении коэффициента корреляции для примера с экзаменационной оценкой.

функция коррел excel

Скачать файл с примером расчета коэффициента корреляции


32 комментария

  1. Большое спасибо за простой и внятный а также общедоступный способ разжевывания информации. Теперь наконец-то обработаю в диссертации на вашем примере свою корреляцию. Побольше бы таких статей. Автору Большой Респект и Махталитет!

  2. Согласен, всё описал доступно и по теме. То, что искал. Мне нужно было показать зависимость продаж магазина от погоды. Всё получилось и вполне логично.
    Надо посмотреть и др. статьи, надеюсь найду ещё что-то полезное.
    Спасибо.

  3. Подскажите,пожалуйста,а как по значению корреляции построить такой график,и можно ли его получить,если переменная непараметрическая(да-1,0-нет)?

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

  4. Высчитывать ранговую или порядкову корреляцию типа 121211112211/111221122121111 и по всем факторам выходят понятные значения, и лишь при сравнении 2х определенных массивов постоянно выдает результат 2.26…..Е-17 что это значит?

    • результат 2.26…..Е-17 что это значит?
      я думаю, что это равно 2.26 умножить на 10 в минус 17ой степени, т.е. ну очень маленькое число корреляции и эти два массива не связаны..

  5. Добрый день!
    Обрабатываю экспериментальные данные, в Excel, выполнена аппроксимация графика. Получены уравнения. Не могу проверить на адекватность полученные уравнения (логарифмические, полиномиальные) с линейными получилось. А вот у остальных не знаю как ввести данные.
    Кто нибудь подскажет, как это выполнить?
    Где можно посмотреть алгоритм ввода?
    Буду очень рада вашей помощи.

    Файл:  .xlsx

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

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

    • В Эксел нет формулы расчета кратной корреляции.
      Для 3 или более переменных нужно рассчитывать их попарно.
      Или использовать кратную (многомерную) корреляцию: произведение усредненных массивов (данные минус среднее значение), деленное на кратную степень произведения дисперсий массивов. То есть при трех массивах вычисляете дисперсии каждого массива, перемножаете их и вычисляете КУБИЧЕСКИй корень (в знаменателе). При 5 массивах — произведение 5 массивов (центрированных — с вычитанием среднего) деленное корень пятой степени из произведения 5 дисперсий массивов

    • Проще вычислить сумму центрированных произведений переменных и разделить на произведение среднеквадратических отклонений переменных

  8. Комментариев, подобных моему тут, конечно, уже много, но всё-же!
    Спасибо за столь доступное и простое описание! Теперь действительно понятно стало!

  9. Спасибо, очень понятно.
    Вы приводите в качестве примера расчет корреляции по Персонал, т.е для количестве них переменных (напр. потраченные часы и оценка). Подскажите, а где в Excel функция ANOVA или MANOVA — расчет корреляция ной взаимосвязи между качественным и количестве ними переменными?

  10. Добрый день! Как рассчитать корреляцию в еxcele я поняла. Несколько уточняющих вопросов. Во-первых, это рассчитывается ведь кор. Пирсона? И второе. В калькуляторах, рассчитывающих кор. Пирсона, к значению корреляции указывается еще и «p» (обычно p0,05 или 0,01), а в еxcele он какой? И третье. Если формула везде расчета Пирсона одна, то почему в разных калькуляторах, в том числе, и в сравнении с расчетами в еxcele, получаются разные результаты? По поводу «р» — еще просьба: я слабо дружу с матимаиткой и не дружу со статистикой вообще. Можете ли мне объяснить доступным языком про это р?

  11. Расчёт корреляционного коэффициента предполагает последовательное выполнение ряда математических операций. Приведенная выше формула расчета коэффициента Пирсона, показывает насколько трудоемок этот процесс если выполнять его вручную. Использование возможностей Excell ускоряет процесс нахождения коэффициента в разы.

  12. Подскажите, пожалуйста, если нужно рассчитать коэфыициент корреляции для выборки Х( -1,95; -4,13, -8; -10; -41,5) и У (-0,22; 1,54; -8,8; -10,8; 8,04; 0,47) . В эксель через КОРРЕЛ не считает.. Вообще при таком разбросе чисел (от отрицательных до положительных) это возможно установить силу связи между Х и У? И как тогда рассчитывать. То что связь мужду Х и У есть это исходные данные, нужно оценить силу связи этой.. Может есть другие идеи?

  13. Pingback: Как составить консервативный портфель — Блог DTI Algorithmic

  14. Pingback: Ако има хаос по пътищата, правителството е корумпирано. Бедност, корупция и катастрофи са свързани - izvanredno.info

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

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