Описать задачу
Формулник › Для учёбы › Расчёты по шагам › Коэффициент корреляции

Коэффициент корреляции в Excel — по шагам, с графиком и отчётом

Редакция Формулника · обновлено · как мы проверяем

Коэффициент корреляции Пирсона показывает, насколько точки близки к прямой: от −1 до 1. Значимость проверяется t-критерием, сила связи — по шкале Чеддока.

обычно 0,05

Функции Excel: КОРРЕЛ, СТЬЮДЕНТ.ОБР.2Х. В примере — учебные данные; замените их своими.

Считаем и оформляем — работу не пишем. Вывод в отчёте — заготовка: допишите его своими словами.

Другие расчёты

Метод наименьших квадратов (МНК)Линия тренда и прогноз (ЛИНЕЙН)Матрица корреляцийПарная линейная регрессияОписательная статистикаДисперсия и стандартное отклонениеДоверительный интервалt-критерий СтьюдентаПогрешности измеренийГрафик функцииСистема линейных уравнений (матрицы)Подбор параметраИнтервальный ряд и гистограмма

Вопросы

Это готовая лабораторная?

Нет. Формулник считает и оформляет: книга 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 — связь «высокая» по Чеддоку, но статистически не подтверждена.

По шагам

  1. Расположите пары значений в двух столбцах построчно: одна строка — один объект. Пустые и текстовые ячейки функция пропускает, нули считает.
  2. Посчитайте r: =КОРРЕЛ(A2:A11;B2:B11). Порядок столбцов на результат не влияет.
  3. Оцените силу связи по модулю r по шкале Чеддока из таблицы ниже, знак скажет направление: плюс — растут вместе, минус — одна растёт, другая падает.
  4. Посчитайте наблюдаемое t: если r в B5, а число пар в B6, то =B5*КОРЕНЬ(B6-2)/КОРЕНЬ(1-B5^2).
  5. Переведите t в p-значение: =СТЬЮДЕНТ.РАСП.2Х(ABS(B7);B6-2). При p < 0,05 корреляция значима.
  6. Постройте точечную диаграмму: она сразу покажет выбросы и нелинейную связь, которые r не видит.

Если переменных больше двух — матрица корреляций

В «Пакете анализа» (Данные → Анализ данных → «Корреляция») выделите все столбцы сразу — Excel построит треугольную матрицу r для каждой пары. Значимость при этом не считается: её нужно добавить по формуле t для каждой ячейки или сравнить r с критическим значением из таблицы.

Если данные — ранги или есть выбросы: Спирмен

Отдельной функции для коэффициента Спирмена в Excel нет. Переведите оба столбца в ранги =РАНГ.СР(A2;A$2:A$11) и примените КОРРЕЛ к столбцам рангов — получится ρ Спирмена с учётом одинаковых значений.

Если нужен готовый отчёт по корреляции

Обычно в нём просят r, вывод по шкале Чеддока, t, p и диаграмму рассеяния с линией тренда. Формулник считает это по вашим двум столбцам по шагам — с формулами в книге, а не готовыми числами, — и оставляет вывод для вашей формулировки.

Шкала Чеддока и критические значения r (α = 0,05)

|r|Сила связиПар nКритическое r
0,1–0,3слабая80,707
0,3–0,5умеренная100,632
0,5–0,7заметная200,444
0,7–0,9высокая300,361
0,9–1,0весьма высокая——

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

Вопросы и ответы

Как найти коэффициент корреляции в Excel?

Функцией КОРРЕЛ с двумя диапазонами одинаковой длины. Ответ — число от −1 до 1; значение около нуля значит, что линейной связи нет.

Чем КОРРЕЛ отличается от КВПИРСОН?

КВПИРСОН — квадрат коэффициента корреляции, то есть R² для прямой. Если r = 0,8, КВПИРСОН вернёт 0,64.

Как определить, значима ли корреляция?

Сравните |r| с критическим значением для вашего n или посчитайте p через t-критерий. При α = 0,05 и 10 парах связь значима, если |r| больше 0,632.

Что делать, если связь нелинейная?

Пирсон её недооценит. Посмотрите диаграмму: для монотонной кривой подойдёт Спирмен по рангам, для параболы — подбор полинома и R².

Отрицательная корреляция — это плохо?

Нет, это направление: например, чем больше пропусков, тем ниже балл. Сила оценивается по модулю.

Проверено редакцией Формулника 17 октября 2026. Как мы проверяем. Источники: