Как рассчитать частичную корреляцию в Excel
В статистике мы часто используемкоэффициент корреляции Пирсона для измерения линейной зависимости между двумя переменными. Однако иногда нам интересно понять взаимосвязь между двумя переменными , контролируя при этом третью переменную .
Например, предположим, что мы хотим измерить связь между количеством часов, отработанных учащимся, и полученной им итоговой оценкой на экзамене, контролируя текущую оценку учащегося в классе. В этом случае мы могли бы использовать частичную корреляцию для измерения взаимосвязи между часами обучения и итоговой оценкой на экзамене.
В этом руководстве объясняется, как рассчитать частичную корреляцию в Excel.
Пример: Частичная корреляция в Excel
Предположим, у нас есть набор данных, который показывает следующую информацию для 10 студентов:
- Текущая оценка в классе
- Количество часов, потраченных на подготовку к выпускному экзамену
- Итоговый балл за экзамен
Используйте следующие шаги, чтобы найти частичную корреляцию между часами обучения и экзаменационными баллами при контроле текущей оценки.
Шаг 1: Рассчитайте каждую попарную корреляцию.
Во-первых, мы рассчитаем корреляцию между каждой парной комбинацией переменных:
Шаг 2: Рассчитайте частичную корреляцию между часами и экзаменационным баллом.
Формула для расчета частичной корреляции между переменной A и переменной B при контроле переменной C выглядит следующим образом:
Частная корреляция = (r A,B – r A,C *r B,C ) / √((1-r 2 A,B )(1-r 2 B,C ))
На следующем снимке экрана показано, как использовать эту формулу для расчета частичной корреляции между часами и экзаменационным баллом с учетом текущей оценки:
Частная корреляция составляет 0,190626.Чтобы определить, является ли эта корреляция статистически значимой, мы можем найти соответствующее значение p.
Шаг 3: Рассчитайте p-значение частной корреляции.
Тестовая статистика t может быть рассчитана как:
т = г √ (n-3) / √ (1-г 2 )
На следующем снимке экрана показано, как использовать эту формулу для расчета статистики теста и соответствующего значения p:
Тестовая статистика t равна 0,51377.Всего степеней свободы n-3 = 10-3 = 7.Соответствующее значение p равно 0,623228.Поскольку это значение не меньше 0,05, это означает, что частичная корреляция между часами и экзаменационным баллом не является статистически значимой.
Расчет коэффициента корреляции Пирсона в Excell
Для того, чтобы рассчитать коэффициент корреляции Пирсона в Excell необходимо сделать следующие шаги:
1.Вносим значения для двух переменных в таблицу (Например Переменная 1 и Переменная 2)
2. Ставим курсор в пустую ячейку
3. На панеле инструментов нажимаем кнопку fx (вставить формулу)
4. В открывшемся окне «Мастер функций» в поле «Категории» выбираем Полный алфавитный перечень
5. Затем в поле «Выберите функцию» находим функцию ПИРСОН
5.1. Нажимаем Ок
6. В открывшемся окне «Аргументы функции» в поле Массив1 вносим номера ячеек, содержащие значения Переменной 1, в поле Массив2 вносим номера ячеек, содержащие значения Переменной2.
7. Нажимаем Ок
8. Смотрим получившийся результат
Как рассчитать ранговую корреляцию Спирмена в 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. Смотрим получившийся результат