Коэффициент корреляции в Excel — по шагам, с графиком и отчётом
Коэффициент корреляции Пирсона показывает, насколько точки близки к прямой: от −1 до 1. Значимость проверяется t-критерием, сила связи — по шкале Чеддока.
Функции Excel: КОРРЕЛ, СТЬЮДЕНТ.ОБР.2Х. В примере — учебные данные; замените их своими.
Другие расчёты
Вопросы
Это готовая лабораторная?
Нет. Формулник считает и оформляет: книга Excel с формулами по шагам, график и отчёт с таблицами. Вывод — заготовка: допишите своими словами, что показали расчёты. Работу за вас мы не пишем.
Можно ли верить числам?
Каждую формулу книги пересчитывают два движка — LibreOffice и второй независимый, — и результат сверяется с расчётом по определению. Если хоть одно число не сходится, файл не выдаётся.
Как вставить данные?
Скопируйте столбцы из Excel или Word и вставьте: по столбцу на величину, по строке на измерение. Запятая или точка в дробях — неважно. Первая строка с названиями — по желанию.
Откроется ли в моём Excel?
Да: Excel 2010–365, LibreOffice, Р7-Офис и МойОфис. Формулы обычные, без макросов; в листе «По шагам» каждая формула записана и по-русски.
КОРРЕЛ и PEARSON: как посчитать и проверить корреляцию в Excel
Коэффициент корреляции Пирсона в Excel — =КОРРЕЛ(A2:A11;B2:B11). Функция PEARSON даёт то же число; в русском Excel она пишется латиницей. Силу связи по модулю r оценивают по шкале Чеддока, но сильная связь ещё не значимая: проверьте её t-критерием t = r·√(n − 2) / √(1 − r²) при n − 2 степенях свободы.
Пример: при r = 0,775 и всего 5 парах t = 2,12, а критическое значение 3,182 — связь «высокая» по Чеддоку, но статистически не подтверждена.
По шагам
- Расположите пары значений в двух столбцах построчно: одна строка — один объект. Пустые и текстовые ячейки функция пропускает, нули считает.
- Посчитайте r:
=КОРРЕЛ(A2:A11;B2:B11). Порядок столбцов на результат не влияет. - Оцените силу связи по модулю r по шкале Чеддока из таблицы ниже, знак скажет направление: плюс — растут вместе, минус — одна растёт, другая падает.
- Посчитайте наблюдаемое t: если r в B5, а число пар в B6, то
=B5*КОРЕНЬ(B6-2)/КОРЕНЬ(1-B5^2). - Переведите t в p-значение:
=СТЬЮДЕНТ.РАСП.2Х(ABS(B7);B6-2). При p < 0,05 корреляция значима. - Постройте точечную диаграмму: она сразу покажет выбросы и нелинейную связь, которые r не видит.
Если переменных больше двух — матрица корреляций
В «Пакете анализа» (Данные → Анализ данных → «Корреляция») выделите все столбцы сразу — Excel построит треугольную матрицу r для каждой пары. Значимость при этом не считается: её нужно добавить по формуле t для каждой ячейки или сравнить r с критическим значением из таблицы.
Если данные — ранги или есть выбросы: Спирмен
Отдельной функции для коэффициента Спирмена в Excel нет. Переведите оба столбца в ранги =РАНГ.СР(A2;A$2:A$11) и примените КОРРЕЛ к столбцам рангов — получится ρ Спирмена с учётом одинаковых значений.
Если нужен готовый отчёт по корреляции
Обычно в нём просят r, вывод по шкале Чеддока, t, p и диаграмму рассеяния с линией тренда. Формулник считает это по вашим двум столбцам по шагам — с формулами в книге, а не готовыми числами, — и оставляет вывод для вашей формулировки.
Шкала Чеддока и критические значения r (α = 0,05)
| |r| | Сила связи | Пар n | Критическое r |
|---|---|---|---|
| 0,1–0,3 | слабая | 8 | 0,707 |
| 0,3–0,5 | умеренная | 10 | 0,632 |
| 0,5–0,7 | заметная | 20 | 0,444 |
| 0,7–0,9 | высокая | 30 | 0,361 |
| 0,9–1,0 | весьма высокая | — | — |
Частые ошибки
- Высокий r считают доказательством причины — корреляция показывает совместное изменение, а не причину. В выводе пишите «связь», а не «влияние».
- ПИРСОН выдаёт #ИМЯ? — в русском Excel функция называется
PEARSONлатиницей, аКОРРЕЛ— по-русски. Результат у них одинаковый. - Значимость не проверена на малой выборке — на 6–8 парах даже r = 0,7 может оказаться случайным. Без t или критического r вывод неполный.
- Строки сдвинуты — если в одном столбце удалили строку, а в другом нет, пары перепутаются, и r будет бессмысленным. Удаляйте пару целиком.
Вопросы и ответы
Как найти коэффициент корреляции в Excel?
Функцией КОРРЕЛ с двумя диапазонами одинаковой длины. Ответ — число от −1 до 1; значение около нуля значит, что линейной связи нет.
Чем КОРРЕЛ отличается от КВПИРСОН?
КВПИРСОН — квадрат коэффициента корреляции, то есть R² для прямой. Если r = 0,8, КВПИРСОН вернёт 0,64.
Как определить, значима ли корреляция?
Сравните |r| с критическим значением для вашего n или посчитайте p через t-критерий. При α = 0,05 и 10 парах связь значима, если |r| больше 0,632.
Что делать, если связь нелинейная?
Пирсон её недооценит. Посмотрите диаграмму: для монотонной кривой подойдёт Спирмен по рангам, для параболы — подбор полинома и R².
Отрицательная корреляция — это плохо?
Нет, это направление: например, чем больше пропусков, тем ниже балл. Сила оценивается по модулю.