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

Как рассчитать ранговую корреляцию Спирмена в 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 ) не превышает этого критического значения, это означает, что корреляция между баллами по математике и естественным наукам не является статистически значимой.

Расчет коэффициента корреляции Спирмена в Excell

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

1.Вносим значения для двух переменных в таблицу (Например Переменная 1 и Переменная 2)

2. Ставим курсор в пустую ячейку

3. На панеле инструментов нажимаем кнопку fx (вставить формулу)

4. В открывшемся окне «Мастер функций» в поле «Категории» выбираем Полный алфавитный перечень

5. Затем в поле «Выберите функцию» находим функцию КОРЕЛЛ

5.1. Нажимаем Ок

6. В открывшемся окне «Аргументы функции» в поле Массив1 вносим номера ячеек, содержащие значения Переменной 1, в поле Массив2 вносим номера ячеек, содержащие значения Переменной2.

7. Нажимаем Ок

8. Смотрим получившийся результат

Как рассчитать ранговую корреляцию Спирмена в Excel?

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

Что такое коэффициент ранговой корреляции Спирмена?

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

Диапазон коэффициента ранговой корреляции Спирмена

1. Если график монотонно возрастает, то коэффициент Спирмена стремится к 1.
2. Если график монотонно убывающий , то коэффициент Спирмена стремится к -1 .
3. Если график одновременно увеличивается и уменьшается , коэффициент Спирмена стремится к 0 .
4. Идеальное значение 1 означает, что данные имеют идеальную положительную корреляцию .
5. Идеальное значение -1 означает, что данные имеют идеальную отрицательную корреляцию .
6. Идеальное значение 0 означает, что данные не имеют связи между двумя переменными.

Следовательно, значение коэффициента Спирмена находится в диапазоне [-1, 1] , где -1 и 1 включены.

Преимущества коэффициента ранговой корреляции Спирмена

Поскольку Спирмен работает с порядковыми данными, это непараметрический тест. Тест не имеет отношения к фактическим значениям в наборе данных. Этот коэффициентный тест хорошо работает с выбросами. Значение корреляции не искажается, если в наборе данных есть значительные выбросы.

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

Была предоставлена формула для расчета рангового коэффициента Спирмена. Формула:

r _s = коэффициент ранговой корреляции Спирмена,

d _i = разница рангов значений в наборе данных,

n = размер набора данных.

Note: The Formula works only if there are no tie ranks in your data set, i.e. there should be only distinct values for each Variable.

For example:

DataSet 1: Variable1: [1, 4, 3, 5], Variable2: [3, 4, 2, 5]

DataSet 2: Variable1: [1, 2, 2, 2], Variable2: [3, 4, 2, 5]

In the above two given data sets, DataSet1 satisfies the condition, and hence the formula could be applied to find spearman coefficient, but DataSet2 do not satisfies the condition, as there are duplicate values in Variable1 of second data set, hence the formula could not be applied to find spearman coefficient.

Как рассчитать ранговую корреляцию Спирмена в Excel?

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

Ранговая функция

Ранг указывает ранг данного числа в наборе данных; можно также выбрать порядок, в котором должен появляться ранг. =RANK.AVG() принимает три аргумента: число , ссылка и порядок .

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

Аргумент 2: Ссылка — второй аргумент в функции ранга. Необходимо указать абсолютный диапазон набора данных.

Аргумент 3: Порядок — это третий аргумент в функции ранга. Порядок может быть либо по возрастанию(1) , либо по убыванию(0) .

Корреляционная функция

Подобно коэффициенту ранговой корреляции Спирмена, у нас также есть коэффициент корреляции Пирсона . Коэффициент корреляции Пирсона — это параметрический критерий для расчета значения корреляции двух переменных. Оба теста почти одинаковы. Просто разница заключается в том, что Спирмен работает над рангами данных, а Пирсон работает над фактическими данными. Функция =CORREL() вычисляет коэффициент корреляции Пирсона. Это может быть очень полезно при нахождении коэффициента корреляции Спирмена, о котором мы поговорим на более позднем этапе статьи. Функция =CORREL() принимает два аргумента: array1 и array2 .

Аргумент 1: Массив1 — это первый аргумент корреляционной функции. Он принимает весь набор данных переменной1.

Аргумент 2: Массив2 — второй аргумент корреляционной функции. Он принимает весь набор данных переменной2.

Различные способы найти коэффициент Спирмена в Excel

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

Способ 1: использование формулы

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

Ниже приведены шаги

Шаг 1: Создайте новый столбец с именем Study Rank . В ячейке D3 используйте формулу =RANK.AVG(B3, $B$3:$B$9, 1) . Это находит ранг ячейки B3 для часов обучения . Нажмите «Ввод» .

Шаг 2: В ячейке D3 появляется число 4 . Это число заняло 4 место в наборе данных Study Hours .

Шаг 3: Скопируйте ту же формулу D3 в ячейки D4:D9 .

Шаг 4: Создайте новый столбец с именем Play Rank . В ячейке E3 используйте формулу =RANK.AVG(C3, $C$3:$C$9, 1) . Это находит ранг ячейки C3 для игровых часов. Нажмите «Ввод» .

Шаг 5: В ячейке E3 появится число 3 . Это число заняло 3 место в наборе данных о часах игры .

Шаг 6: Скопируйте ту же формулу E3 в ячейки E4:E9 .

Шаг 7: Создайте новый столбец, имя, d . В ячейке F3 используйте формулу =D3-E3 . Это вычисляет разницу в рангах. Нажмите «Ввод» .

Шаг 8: Скопируйте ту же формулу F3 в ячейки F4:F9 .

Шаг 9: Создайте новый столбец с именем d . В ячейке G3 используйте формулу =F3^2 . Это вычисляет квадрат разницы. Нажмите «Ввод» .

Шаг 10: Скопируйте ту же формулу G3 в ячейки G4:G9 .

Шаг 11: Используйте функцию =COUNT(C3:C9) для расчета размера набора данных. Нажмите «Ввод».

Шаг 12: В ячейке появляется J3 , 7 , что является размером набора данных.

Шаг 13: Используйте функцию =СУММ(G3:G9) для вычисления суммы разницы между рангами. Нажмите «Ввод» .

Шаг 14: В ячейке появится G10 , 110 .

Шаг 15: В ячейке J5 примените формулу Спирмена , как указано выше в статье, т.е. =1-(6*G10/(J3*(J3^2-1))) . Нажмите «Ввод».

Шаг 16: Мы получаем ранговый коэффициент корреляции Спирмена как -0,96429 , что доказывает, что часы обучения и часы игры имеют отрицательную корреляцию .

Способ 2: Использование функции =CORREL()

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

Ниже приведены шаги

Шаг 1: Создайте новый столбец с именем Study Rank . В ячейке D3 используйте формулу =RANK.AVG(B3, $B$3:$B$9, 1) . Это находит ранг ячейки B3 для часов обучения . Нажмите «Ввод» .

Шаг 2: В ячейке D3 появляется число 4 . Это число заняло 4 место в наборе данных Study Hours .

Шаг 3: Скопируйте ту же формулу D3 в ячейки D4:D9 .

Шаг 4: Создайте новый столбец с именем Play Rank . В ячейке E3 используйте формулу =RANK.AVG(C3, $C$3:$C$9, 1) . Это находит ранг ячейки C3 для игровых часов . Нажмите «Ввод» .

Шаг 5: В ячейке E3 появится число 3 . Это число заняло 3 место в наборе данных о часах игры .

Шаг 6: Скопируйте ту же формулу E3 в ячейки E4:E9 .

Шаг 7: В ячейке H4 используйте функцию =CORREL(D3:D9, E3:E9) , чтобы найти ранговый коэффициент корреляции Спирмена. Нажмите «Ввод» .

Шаг 8: Мы получаем ранговый коэффициент корреляции Спирмена как -0,96429 , что доказывает, что часы обучения и часы игры имеют отрицательную корреляцию .

Как рассчитать корреляцию рангов спирмена в excel

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

В этом руководстве объясняется, как рассчитать ранговую корреляцию Спирмена между двумя переменными в 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: Рассчитайте коэффициент ранговой корреляции Спирмена.

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

Ранговая корреляция Спирмена оказывается равной -0,41818 .

Шаг 4 (необязательно): Определите, является ли ранговая корреляция Спирмена статистически значимой.

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

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

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

В нашем примере размер выборки составил n = 10 студентов. Используя уровень значимости 0,05, мы находим, что критическое значение составляет 0,564 .

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

Об авторе

бенджамин андерсон

Здравствуйте, я Бенджамин, профессор статистики на пенсии, ставший преданным преподавателем Statorials. Имея обширный опыт и знания в области статистики, я хочу поделиться своими знаниями, чтобы расширить возможности студентов с помощью Statorials. Узнать больше

Похожие публикации:

1. Как изменить масштаб документа в word
2. Как очистить кэш браузера на телефоне редми
3. Как рассчитать разницу в эксель
4. Куда лучше подключать наушники спереди или сзади