Как рассчитать ранговую корреляцию Спирмена в Excel
В статистике корреляция относится к силе и направлению связи между двумя переменными. Значение коэффициента корреляции может варьироваться от -1 до 1 со следующими интерпретациями:
- -1: идеальная отрицательная связь между двумя переменными
- 0: нет связи между двумя переменными
- 1: идеальная положительная связь между двумя переменными
Один особый тип корреляции называется ранговой корреляцией Спирмена и используется для измерения корреляции между двумя ранжированными переменными. (например, оценка балла учащегося на экзамене по математике и оценка его оценки на экзамене по естественным наукам в классе).
В этом руководстве объясняется, как рассчитать ранговую корреляцию Спирмена между двумя переменными в Excel.
Пример: ранговая корреляция Спирмена в Excel
Выполните следующие шаги, чтобы вычислить ранговую корреляцию Спирмена между результатами экзамена по математике и результатами экзамена по естественным наукам 10 учащихся в определенном классе.
Шаг 1: Введите данные.
Введите экзаменационные баллы для каждого учащегося в два отдельных столбца:
Шаг 2: Рассчитайте ранги для каждого экзаменационного балла.
Далее мы рассчитаем рейтинг для каждого экзаменационного балла. Используйте следующие формулы в ячейках D2 и E2, чтобы вычислить рейтинги по математике и естественным наукам для первого ученика, Остина:
Ячейка D2: =RANK.AVG(B2, $B$2:$B$11, 0)
Ячейка E2: =RANK.AVG(C2, $C$2:$C$11, 0)
Затем выделите оставшиеся ячейки для заполнения:
Затем нажмите Ctrl+D, чтобы заполнить ранги для каждого ученика:
Шаг 3: Рассчитайте коэффициент ранговой корреляции Спирмена.
Наконец, мы рассчитаем коэффициент ранговой корреляции Спирмена между оценками по математике и по естественным наукам с помощью функции CORREL() :
Ранговая корреляция Спирмена оказывается равной -0,41818 .
Шаг 4 (необязательно): Определите, является ли ранговая корреляция Спирмена статистически значимой.
На предыдущем шаге мы обнаружили, что ранговая корреляция Спирмена между результатами экзаменов по математике и естественным наукам составляет -0,41818 , что указывает на отрицательную корреляцию между двумя переменными.
Однако, чтобы определить, является ли эта корреляция статистически значимой, нам нужно будет обратиться к таблице ранговой корреляции Спирмена критических значений, которая показывает критические значения, связанные с различными размерами выборки (n) и уровнями значимости (α).
Если абсолютное значение нашего коэффициента корреляции больше критического значения в таблице, то корреляция между двумя переменными является статистически значимой.
В нашем примере размер выборки составлял n = 10 студентов. Используя уровень значимости 0,05, мы находим, что критическое значение равно 0,564 .
Поскольку рассчитанное нами абсолютное значение рангового коэффициента корреляции Спирмена ( 0,41818 ) не превышает этого критического значения, это означает, что корреляция между баллами по математике и естественным наукам не является статистически значимой.
Функция КОРРЕЛ
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
Функция КОРРЕЛ возвращает коэффициент корреляции двух диапазонов ячеев. Коэффициент корреляции используется для определения взаимосвязи между двумя свойствами. Например, можно установить зависимость между средней температурой в помещении и использованием кондиционера.
Синтаксис
КОРРЕЛ(массив1;массив2)
Аргументы функции КОРРЕЛ описаны ниже.
- массив1 — обязательный аргумент. Диапазон значений ячеок.
- массив2 — обязательный аргумент. Второй диапазон значений ячеев.
Замечания
- Если аргумент массива или ссылки содержит текст, логические значения или пустые ячейки, эти значения игнорируются; однако ячейки с нулевыми значениями включаются.
- Если массив1 и массив2 имеют различное количество точек данных, то correl возвращает #N/A.
- Если массив1 или массив2 пуст или если s (стандартное отклонение) их значений равно нулю, то corREL возвращает значение #DIV/0! ошибку «#ВЫЧИС!».
- Так как коэффициент корреляции ближе к +1 или -1, он указывает на положительную (+1) или отрицательную (-1) корреляцию между массивами. Положительная корреляция означает, что при увеличении значений в одном массиве значения в другом массиве также увеличиваются. Коэффициент корреляции, который ближе к 0, указывает на отсутствие или неабную корреляцию.
- Уравнение для коэффициента корреляции имеет следующий вид: где являются средними значениями выборок СРЗНАЧ(массив1) и СРЗНАЧ(массив2).
Пример
В следующем примере возвращается коэффициент корреляции двух наборов данных в столбцах A и B.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
24. Коэффициент ранговой корреляции Спирмена
На предыдущих уроках мы познакомились с линейным коэффициентом корреляции Пирсона, линейной регрессией, а также потренировались в построении нелинейных моделей. Но эти методы далеко не всегда подходят для описания зависимости признака-результата от признака-фактора . Не всегда понятна форма зависимости (Линейная? Гиперболическая? Экспоненциальная? Какая-то другая?). Эта форма бывает сложной, а то и вовсе не определИма (в принципе). И вообще, мы можем исследовать не количественный, а некоторый качественный признак.
Представьте, что в вазе лежит яблоко, киви, банан, апельсин и мандарин. Как можно проранжировать это множество? Напрашивается пронумеровать фрукты по возрастанию (либо убыванию) их массы. На первом месте самый лёгкий, на втором подобрее, на третьем – ещё добрее, … и на последнем – самый добрый:
Таким образом, каждому фрукту присвоен свой ранг (порядковый номер) по количественному критерию – массе, а именно, по возрастанию массы.
Но есть более вкусный качественный критерий. Сейчас я расположу эти фрукты в порядке моего ЛИЧНОГО вкусового предпочтения: что бы я съел в первую, вторую, третью, четвёртую и, наконец, последнюю очередь:
Таким образом, каждому фрукту тоже присвоен свой ранг.
И здесь любопытно сравнить качественный признак с количественным – выяснить, насколько я склонен считать лёгкие фрукты более вкусными. Для этого нужно сопоставить соответствующие ранги по фруктам и оценить степень их близости:
Иными словами, нужно определить, насколько теснА корреляционная зависимость моего вкуса от массы фрукта? Или она близка к нулю?
Но это, конечно, не самое интересное. Теперь ВЫ расположите те же фрукты в порядке СВОИХ вкусовых предпочтений. …Есть? Вероятнее всего, вы предпочли употребить фрукты в другой последовательности и проранжировали их иначе, например, так:
После чего появляется возможность сравнить ранги – чтобы выяснить, насколько коррелируют (совпадают) наши вкусы. Визуально можно сразу сказать, что коррелируют они слабо, т. к. читатель явно не жалует цитрусовые. Но, разумеется, есть математическая оценка этой связи, и называется она коэффициент ранговой корреляции Спирмена.
Оставим вкусное на десерт и начнём с более прозаичной задачи, где сопоставляются два количественных признака:
Имеются выборочные данные по студентам: – количество прогулов за некоторый период времени и – суммарная успеваемость за этот период:
Найти коэффициент ранговой корреляции Спирмена, сделать вывод.
В Примере 67 мы вычислили линейный коэффициент корреляции , что говорит о сильной обратной корреляционной зависимости – суммарной успеваемости от – количества прогулов. Далее было найдено уравнение линейной регрессии – это прямая, которая наилучшим образом (по сравнению с другими прямыми) приближает эмпирические точки :
Но у такого подхода могут быть изъяны. Во-первых, прогулы и успеваемость – это величины дискретные (прерывные), но мы приблизили их непрерывной функцией (линейной). И во-вторых, зависимость может быть гораздо более сложной. Когда прогулов немного, успеваемость, вероятно, падает несущественно; когда их количество растёт – ситуация начинает ухудшаться, и, наконец, с некоторого момента достижения стремительно падают к плинтусу. Возможно, удастся подобрать кривую, удачно приближающую точки, но у нас мало данных (8 наблюдений всего), и по чертежу сомнительно, что удастся.
Поэтому в качестве альтернативы уместно рассмотреть ранговый подход. И я расскажу вам как о ручном решении этой задачи, так и о машинном – с помощью MS Excel.
Сначала рассмотрим признак-фактор и для удобства упорядочим количество прогулов по возрастанию: 
Это можно сделать на черновике или в Экселе. Теперь каждому значению легко присвоить свой ранг и записать ранги на чистовик, для примера парочка синих линий:
Следует заметить, что записывать числа по возрастанию (справа) вовсе не обязательно, это сделано чисто для удобства. Значения несложно проранжировать в уме (при небольшой выборке) или опять же с помощью специальной функции Экселя (кино будет ниже).
И ещё заметим такой момент, у нас есть одинаковые значения , но ранги у них разные (7 и 8) и возникает вопрос, а почему не наоборот? В подобных ситуациях обычно находят средний арифметический ранг, который присваивают каждой варианте. В нашей задаче одинаковых значений два, поэтому их средний ранг составит: – вот теперь всё справедливо, относим дробный ранг 7,5 и к варианте и к варианте
Аналогично ранжируем значения признака-результата – тоже и ОБЯЗАЛЬНО по возрастанию значений. Ранги легко проставить устно (что я только что сделал), без фактической сортировки «игрековых» значений:
Среди значений нет одинаковых, и поэтому ранги не нуждаются в дополнительной корректировке. После ранжирования полезно выполнить проверку. Суммы «иксовых» и «игрековых» рангов должны совпадать и равняться , в нашей задаче объём выборки составляет и обе суммы равны .
Оценим тесноту связи между рангами. Для этого нужно вычислить коэффициент ранговой корреляции Спирмена, и это – есть в точности линейный коэффициент корреляции Пирсона* между рангами и .
* а коль скоро так, то минимальный объем совокупности должен равняться 6-7.
Технически вычисления можно провести разными способами. Если вас устраивает результат «на скорую руку», то просто забиваем в Экселе:
= КОРРЕЛ(выделяем мышкой массив ; выделяем массив ) и жмём Enter.
Но в учебных задачах, как правило, нужны подробные расчёты. Если нет дробных рангов, то коэффициент ранговой корреляции Спирмена удобно вычислить по упрощенной формуле:
, где – объем совокупности, а – квадраты разностей между соответствующими рангами.
Если же дробные ранги есть (это означает, что есть одинаковые значения и / или ), то возможны варианты. В том случае, если точность вычислений не критична и дробных рангов не так много, можно пользоваться той же формулой, но она будет давать приближённый результат: .
Но если вам необходимы абсолютно точные и подробные расчёты, то лучше расписать нахождение линейного коэффициента корреляции подробно – по образцу, только не между значениями и , а между их рангами . Кроме того, существуют специальные модификации вышеприведённой формулы – с поправкой на повторяющиеся значения , но лишь для некоторых частных случаев. И да, должен предупредить, что формулы, приведённые во многих источниках Интернета, некорректны. Поэтому лучше потратить время и получить стопудовый результат.
В нашей задаче дробные ранги есть, и мы выберем упрощенный вариант. Для этого вычислим разности соответствующих рангов , их квадраты и сумму . Заполним расчётную таблицу:
Так как среди рангов есть дробные, то формула даёт лишь приближенный результат:
Более точное значение, вычисленное с помощью функции =КОРРЕЛ() приложения MS Excel: . И, как видите, погрешность вполне приемлемая, одна сотая всего.
Поскольку – это линейный коэффициент корреляции между рангами, то его интерпретация будет такой же. Коэффициент ранговой корреляции изменяется в пределах и чем он ближе по модулю к единице, тем теснее ранговая корреляционная зависимость. Для оценки тесноты связи используем ту же шкалу Чеддока:
при этом если , то корреляционная связь обратная, а если , то прямая
Теперь смотрим кино, как это всё быстро подсчитать в Экселе:
и записываем ответ: , таким образом, существует сильная обратная корреляционная зависимость – суммарной успеваемости от – количества прогулов.
Напомню значение линейного коэффициента корреляции , и сейчас мы получили примерно такой же, даже более убедительный результат.
По аналогии с линейным коэффициентом, можно проверить статистическую значимость рангового коэффициента корреляции и построить соответствующие доверительные интервалы. Но это уже немного дебри статистики, с которыми можно ознакомиться, например, в учебном пособии Гмурмана (поздние издания) и других источниках. …Ловко я модернизировал метод Ивана Сусанина 🙂
К недостатку рангового коэффициента корреляции Спирмена можно отнести тот факт, что он практически ничего не говорит о форме зависимости. Но повторюсь, эта форма может быть трудноопределима или не определИма вовсе. Как, например, при сопоставлении качественных признаков. По этой причине ранговый подход нашёл широчайшее применение в психологии, социологии и других гуманитарных направлениях. К слову, Чарльз Спирмен был именно психологом, и в его честь мы рассмотрим как раз простенькую задачу по психологии. На совместимость двух людей:
Коле и Оле было предложено проранжировать свои увлечения – от самого любимого до самого скучного / неприятного. В результате были получены следующие результаты:
! В подобных задачах объекты принято ранжировать по убыванию их «качества» – от самого «хорошего» до самого «плохого».
С помощью коэффициента корреляции Спирмена определить совместимость Коли и Оли в плане увлечений.
Это задача для самостоятельного решения! – все числа уже в Экселе. Образец для сверки внизу.
В наиболее благоприятном случае все ранги по увлечениям совпадают, их разности равны нулю и посему , это говорит о практически идеальной совместимости. По мере убывания совместимость будет падать до нейтрального околонулевого значения, где нельзя сказать, что увлечения как-то сильно совпадают или наоборот, разнятся. И в отрицательной зоне начинает нарастать негатив – вплоть до значения , при котором Коля и Оля – совершенно разные люди.
Помимо подхода Спирмена, существует и другой принцип ранжированию объектов, который выражается ранговым коэффициентом корреляции Кендалла. Но он не слишком распространен в массовой практике (по крайне мере, технической), поэтому едем дальше:
Решения и ответы:
Пример 79. Решение: вычислим разности соответствующих рангов , их квадраты и сумму :
Так как среди рангов нет дробных, то:
Ответ: , таким образом, Коля и Оля имеют слабо-умеренно-негативную совместимость по интересам.
Автор: Емелин Александр
(Переход на главную страницу)
Zaochnik.com – профессиональная помощь студентам,
cкидкa 15% на первый зaкaз, при оформлении введите прoмoкoд: 5530-hihi5
© Copyright mathprofi.ru, Александр Емелин, 2010-2023. Копирование материалов сайта запрещено
Расчет коэффициента корреляции Спирмена в Excell
Для того, чтобы рассчитать коэффициент корреляции в Excell необходимо сделать следующие шаги:
1.Вносим значения для двух переменных в таблицу (Например Переменная 1 и Переменная 2)
2. Ставим курсор в пустую ячейку
3. На панеле инструментов нажимаем кнопку fx (вставить формулу)
4. В открывшемся окне «Мастер функций» в поле «Категории» выбираем Полный алфавитный перечень
5. Затем в поле «Выберите функцию» находим функцию КОРЕЛЛ
5.1. Нажимаем Ок
6. В открывшемся окне «Аргументы функции» в поле Массив1 вносим номера ячеек, содержащие значения Переменной 1, в поле Массив2 вносим номера ячеек, содержащие значения Переменной2.
7. Нажимаем Ок
8. Смотрим получившийся результат