Ранговая корреляция Спирмена в Excel: формула и график

В учебнике простым языком объясняются основы корреляции Спирмена и показано, как рассчитать коэффициент ранговой корреляции Спирмена в Excel.

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

Корреляция Спирмена – основы

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

Корреляция момента продукта Pearson проверяет линейную связь между двумя непрерывными переменными. Линейная означает зависимость, при которой две переменные изменяются в одном направлении с постоянной скоростью.

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

Когда делать корреляцию Спирмена

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

1. Если ваши данные демонстрируют нелинейную зависимость или не имеют нормального распределения.
2. Если хотя бы одна переменная порядковая. Если ваши значения можно расположить в порядке «первый, второй, третий…», вы имеете дело с порядковыми данными.
3. Если есть существенные отклонения. В отличие от корреляции Пирсона, корреляция Спирмена не чувствительна к выбросам, поскольку она выполняет расчеты по рангам, поэтому разница между фактическими значениями не имеет значения.

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

• Люди с более высоким уровнем образования больше заботятся об окружающей среде?
• Связано ли количество симптомов у пациента с его готовностью принимать лекарства?

Коэффициент корреляции Спирмена

В статистике коэффициент корреляции Спирмена представлен либо рупий или греческое письмо р («ро»), поэтому его часто называют Ро Спирмена.

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

• 1 – идеальная положительная корреляция
• -1 – идеальная отрицательная корреляция
• 0 – нет корреляции

Формула ранговой корреляции Спирмена

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

Если равных рангов нет, подойдет более простая формула:

Где:

• из это разница между парой рангов
• н количество наблюдений

Чтобы иметь дело со связанными рангами, необходимо использовать полную версию формулы корреляции Спирмена, которая представляет собой слегка модифицированную версию r Пирсона:

Где:

• R(x) и R(y) — ранги Икс а также у переменные
• R(x) и R(y) — средние ранги

Как рассчитать корреляцию Спирмена в Excel с помощью функции КОРРЕЛ

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

В качестве примера попробуем выяснить, имеет ли наша физическая активность какое-либо отношение к нашему кровяному давлению. В столбце B у нас есть количество минут, которые 10 мужчин того же возраста ежедневно проводят в тренажерном зале, а в столбце C у нас есть их систолическое артериальное давление.

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

1. Ранжируйте свои данные

   Поскольку корреляция Спирмена оценивает связи между двумя переменными на основе их рангов, вам необходимо ранжировать исходные данные. Это можно быстро сделать с помощью функции Excel RANK.AVG.

   Чтобы ранжировать первую переменную (физическую активность), введите приведенную ниже формулу в D2, а затем перетащите ее вниз в D11:

   =РАНГ.СРЕДНЕЕ(B2,$B$2:$B$11,0)

   Чтобы ранжировать вторую переменную (кровяное давление), поместите следующую формулу в ячейку E2 и скопируйте ее вниз по столбцу:

   =РАНГ.СРЕДНЕЕ(C2,$C$2:$C$11,0)

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

   На этом этапе ваши исходные данные должны выглядеть примерно так:
   

2. Найдите коэффициент корреляции Спирмена
   Теперь, когда ранги установлены, мы можем использовать функцию Excel CORREL для получения ро Спирмена:

   = КОРРЕЛ (D2: D11, E2: E11)

   Формула возвращает коэффициент -0,7576 (округленный до 4 цифр), который показывает достаточно сильную отрицательную корреляцию и позволяет сделать вывод, что чем больше человек тренируется, тем ниже у него артериальное давление.

   Коэффициент корреляции Пирсона для той же выборки (-0,7445) указывает на несколько более слабую корреляцию, но все же статистически значимую:
   

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

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

Если вы не совсем уверены, что функция КОРРЕЛ вычислила право Спирмена, вы можете проверить результат с помощью традиционной формулы, используемой в статистике. Вот как:

1. Найдите разницу между каждой парой рангов (d), вычитая один ранг из другого:

   =D2-E2

   Эта формула помещается в F2, а затем копируется вниз по столбцу.

2. Возведите каждую разницу в рангах в степень двойки (d2):
   =F2^2

   Эта формула идет в столбец G.

3. Складываем квадраты разностей:

   =СУММ(G2:G11)

   Эта формула может относиться к любой пустой ячейке, в нашем случае к G12.

   На следующем снимке экрана вы, вероятно, лучше поймете расположение данных:
   

4. В зависимости от того, имеет ли ваш набор данных какие-либо связанные ранги или нет, используйте один из эти формулы для расчета коэффициента корреляции Спирмена.

В нашем примере ничьих нет, поэтому можно использовать более простую формулу:

С d2 равно 290, и н (количество наблюдений) равным 10, формула претерпевает следующие преобразования:

В результате вы получаете -0,757575758, что полностью согласуется с коэффициентом корреляции Спирмена, рассчитанным в предыдущем примере.

В Microsoft Excel приведенные выше расчеты можно выполнить с помощью следующего уравнения:

=1-(6*G12/(10*(10^2-1)))

Где G12 — сумма квадратов разностей рангов (d2).

Как сделать корреляцию Спирмена в Excel с помощью графика

Коэффициенты корреляции в Excel измеряют только линейные (Пирсон) или монотонные (Спирмен) отношения. Однако возможны и другие ассоциации. Таким образом, независимо от того, какую корреляцию вы делаете, всегда полезно представить взаимосвязь между переменными на графике.

Чтобы построить график корреляции ранжированных данных, вам нужно сделать следующее:

1. Вычислите ранги с помощью функции RANK.AVG, как описано в этот пример.
2. Выберите два столбца с рангами.
3. Вставьте точечную диаграмму XY. Для этого нажмите кнопку Разброс диаграмма значок на Вставка вкладка, в Чаты группа.
4. Добавьте линию тренда на график. Самый быстрый способ — нажать Элементы диаграммы кнопка > Добавить линию тренда….
5. Отобразите значение R-квадрата на графике. Дважды щелкните линию тренда, чтобы открыть ее панель, переключитесь на Параметры линии тренда вкладку и выберите Отображение значения R-квадрата на графике коробка.
6. Покажите больше цифр в значении R2 для большей точности.

В результате вы получите наглядное представление о соотношении между рангами. Кроме того, вы получите Коэффициент детерминации (R2), квадратный корень из которого представляет собой коэффициент корреляции Пирсона (r). Но поскольку вы построили ранжированные данные, это значение r Пирсона не что иное, как значение rho Спирмена.

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

В нашем случае R2 равно 0,5739210285. Используйте функцию SQRT, чтобы найти квадратный корень:

=КОРЕНЬ(0,5739210285)

…и вы получите уже знакомый коэффициент 0,757575758.

Нисходящий наклон на графике демонстрирует отрицательную корреляцию, поэтому мы добавляем знак минус и получаем правильный коэффициент корреляции Спирмена, равный -0,757575758.

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

Практическая рабочая тетрадь

Ранговая корреляция Спирмена в Excel (файл .xlsx)

Вас также могут заинтересовать