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

«Докажите, что показатели связаны» — типовая задача третьей главы. Считают для этого корреляцию.

В Excel есть готовая функция КОРРЕЛ, но она умеет только Пирсона. Если у вас баллы теста или анкеты, нужен Спирмен — а его в Excel придётся собрать руками. Разберём оба пути.

Что показывает коэффициент корреляции

Коэффициент корреляции — одно число, которое отвечает на вопрос: меняются ли два показателя согласованно.

Он всегда лежит от −1 до +1. Знак говорит о направлении, модуль — о силе:

• плюс — оба показателя растут вместе (прямая связь);
• минус — один растёт, другой падает (обратная связь);
• около нуля — связи нет, точки на графике разбросаны облаком.

Силу связи в дипломах описывают по шкале Чеддока — она привязывает модуль коэффициента к словам, которые вы напишете в выводе.

Подробный разбор границ и спорных случаев — в статье «Шкала Чеддока».

Формула в Excel: корреляция Пирсона

Пусть первый показатель лежит в столбце B, второй — в столбце C, со второй по двадцать первую строку. Тогда коэффициент считается одной формулой:

=КОРРЕЛ(B2:B21;C2:C21)

В английском Excel эта функция называется CORREL. Есть ещё ПИРСОН (PEARSON) — она даёт ровно то же число, дублирование осталось с давних версий.

КОРРЕЛ всегда считает именно Пирсона — и никак иначе. Никакого переключателя «а теперь Спирмена» внутри неё нет.

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

Пример на реальных данных

В дипломе по педагогике у 20 первокурсников замерили ситуативную тревожность (баллы Спилбергера) и балл за экзамен по 100-балльной шкале.

Тревожность в столбце B: 48, 52, 45, 41, 54, 47, 50, 43, 46, 51, 44, 49, 53, 42, 47, 55, 40, 46, 50, 45.

Балл за экзамен в столбце C: 70, 68, 65, 95, 71, 80, 81, 84, 85, 78, 91, 56, 80, 88, 85, 72, 92, 94, 72, 87.

Что получается на этих двадцати парах

Читается так: чем выше тревожность, тем ниже балл за экзамен, и связь заметная.

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

Готовой функции для Спирмена в Excel нет. Ни в русской версии, ни в английской, ни в Google Таблицах. Считают её в два шага: сначала переводят значения в ранги, потом применяют к рангам ту же КОРРЕЛ.

Шаг 1. Ранги первого показателя. В столбце D, начиная с D2:

=РАНГ(B2;$B$2:$B$21;1)+(СЧЁТЕСЛИ($B$2:$B$21;B2)-1)/2

Шаг 2. Ранги второго показателя. В столбце E, начиная с E2 — та же формула, но по столбцу C:

=РАНГ(C2;$C$2:$C$21;1)+(СЧЁТЕСЛИ($C$2:$C$21;C2)-1)/2

Шаг 3. Корреляция по рангам. Это и есть ρ Спирмена:

=КОРРЕЛ(D2:D21;E2:E21)

На наших данных получается ρ = −0,68. Пирсон давал −0,62: разница небольшая, но именно ρ здесь корректен, и в диплом идёт он.

Зачем в формуле вторая половина

РАНГ сама по себе с одинаковыми значениями работает неправильно. Если тревожность 45 встретилась дважды, обеим строкам она поставит ранг 6, а ранг 7 просто пропустит. Сумма рангов перестаёт сходиться, и ρ уезжает.

Правильное поведение — средний ранг: два одинаковых значения делят между собой два соседних места и получают полусумму. Именно это и добавляет хвост +(СЧЁТЕСЛИ(...)-1)/2.

🧮Онлайн-калькулятор корреляции СпирменаВставьте два столбца — ранги, ρ и вывод посчитаются сами→

Как проверить значимость связи

Сам коэффициент ничего не доказывает: на маленькой выборке ρ = −0,4 может получиться случайно. Нужна проверка значимости.

Считают эмпирическое значение t. Пусть коэффициент лежит в ячейке F2, а n — объём выборки:

=ABS(F2)*КОРЕНЬ((20-2)/(1-F2^2))

Полученное число сравнивают с критическим для уровня 0,05 и числа степеней свободы n − 2:

=СТЬЮДРАСПОБР(0,05;18)

Проверка значимости для нашей выборки из 20 человек

Эмпирическое больше критического — связь статистически значима. В обоих случаях t превышает и порог для 0,01 (он равен 2,88), поэтому в работе можно писать p < 0,01.

Точное p-значение даёт функция =СТЬЮДРАСП(3,89;18;2) в старых версиях или =СТЬЮДЕНТ.РАСП.2Х(3,89;18) в новых: для Спирмена получается 0,001.

Формулы можно не вбивать: возьмите готовый файл

Если считать нужно не один коэффициент, а всю статистику для третьей главы — есть готовая книга Excel. Вы вставляете свои данные в жёлтые ячейки, и всё считается само.

Корреляция лежит на двух листах: «15. Корреляция Пирсона» и «16. Корреляция Спирмена». На листе Спирмена ранги проставляются автоматически, со средним рангом при совпадениях — вставили два столбца значений, получили ρ, проверку значимости и формулировку для работы.

Три способа посчитать — что выбрать

← листайте вправо, чтобы сравнить →

Число везде получится одно и то же. Разница в том, сколько ручной работы остаётся на вас и где можно ошибиться.

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

Что писать в дипломе

Формулировка держится на трёх вещах: сам коэффициент, сила связи по Чеддоку и значимость.

Для проверки связи ситуативной тревожности и академической успеваемости применялся коэффициент ранговой корреляции Спирмена. Выявлена заметная обратная связь: ρ = −0,68 при p < 0,01 (n = 20). Чем выше уровень ситуативной тревожности, тем ниже балл за экзамен.

Если данные числовые и нормальные, а вы считали Пирсона:

Обнаружена заметная обратная корреляционная связь между показателями (r = −0,62; p < 0,01; n = 20).

Когда связь не подтвердилась, так и пишут:

Статистически значимой связи между показателями не выявлено (ρ = 0,14; p > 0,05).

Частые ошибки

• Пишут «влияет» вместо «связан». Корреляция не доказывает причину: оба показателя могут зависеть от третьего фактора.В выводах используйте слова «связаны», «сопряжены», «сопутствуют» — и не более того.
• Считают КОРРЕЛ по баллам теста. Баллы анкет и опросников — порядковая шкала, для неё Пирсон некорректен.Переведите значения в ранги и посчитайте Спирмена — разница в третьей главе бывает принципиальной.
• Берут РАНГ без поправки на совпадения. Одинаковым значениям достаётся один и тот же ранг, а следующий пропускается — ρ получается неверным.Добавляйте хвост +(СЧЁТЕСЛИ(диапазон;значение)-1)/2 или используйте РАНГ.СР.
• Не смотрят на диаграмму рассеяния. Один выброс способен и создать корреляцию, и обнулить её.Постройте точечную диаграмму до расчёта: аномальная точка видна сразу.
• Делают выводы по 6-8 наблюдениям. На такой выборке даже r = 0,7 не проходит проверку значимости.Для корреляции нужно хотя бы 20-30 пар; при меньшем n указывайте это как ограничение исследования.

Частые вопросы

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

КОРРЕЛ (в английской версии CORREL). Синтаксис: =КОРРЕЛ(массив1;массив2). Она считает только коэффициент Пирсона. Функция ПИРСОН — её полный дубль.

Как посчитать корреляцию Спирмена в экселе, если функции нет

В два дополнительных столбца проставьте ранги формулой =РАНГ(B2;$B$2:$B$21;1)+(СЧЁТЕСЛИ($B$2:$B$21;B2)-1)/2, а затем примените КОРРЕЛ к этим столбцам рангов. Либо посчитайте сразу в калькуляторе Спирмена.

Можно ли получить корреляционную матрицу по нескольким показателям сразу

Да, через надстройку «Пакет анализа»: «Данные → Анализ данных → Корреляция». Но матрица строится только по Пирсону, а самой надстройки нет в Excel для Mac и в веб-версии.

Почему КОРРЕЛ выдаёт ошибку

#Н/Д — диапазоны разной длины. #ДЕЛ/0! — в одном из столбцов все значения одинаковые либо чисел меньше двух. Ещё частая причина — числа вставлены из Word как текст: они выравниваются по левому краю и в расчёт не попадают.

Что делать, если корреляция значима, но слабая

Так и писать: связь статистически значима, но по силе слабая (например, ρ = 0,22; p < 0,05). Значимость говорит только о том, что связь не случайна, а не о том, что она сильная.

Короткий алгоритм

1. Разложите данные в два столбца — пара значений на строку, без пустых ячеек внутри.
2. Числовые измерения с нормальным распределением? Считайте =КОРРЕЛ(B2:B21;C2:C21) — это r Пирсона.
3. Баллы, ранги или ненормальные данные? Проставьте ранги формулой с поправкой на совпадения и примените КОРРЕЛ к столбцам рангов — это ρ Спирмена.
4. Проверьте значимость: =ABS(r)*КОРЕНЬ((n-2)/(1-r^2)) сравните с =СТЬЮДРАСПОБР(0,05;n-2).
5. Определите силу по шкале Чеддока, направление — по знаку, и запишите вывод с коэффициентом, n и p.

Или пропустите все шаги: скачайте файл выше либо вставьте данные в калькулятор Пирсона или Спирмена.

Что ещё почитать

• Корреляция Пирсона или Спирмена: что выбрать — развилка по типу данных со схемой.
• Корреляция Пирсона: полное руководство — формула, условия, критические значения.
• Шкала Чеддока: как оценить силу связи — границы и спорные случаи.
• Как посчитать статистику в Excel: формулы — остальные показатели третьей главы.
• Стандартное отклонение в Excel — запись «M ± σ» и однородность выборки.

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