Расчет связи между данными в Excel

Иван Корнев·11.04.2026·⏱4 мин

Чтобы посчитать коэффициент корреляции в Excel, используйте встроенную функцию =CORREL(массив1; массив2). Она возвращает значение от -1 до 1, показывающее силу линейной связи между двумя наборами чисел. Для ранговой корреляции (Спирмена) данные предварительно ранжируют функцией =RANK.AVG, а затем также применяют CORREL к полученным рангам.

Выбор метода анализа: Пирсон или Спирмен

Перед расчетом важно определить тип зависимости в ваших данных, так как от этого зависит точность результата.

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

Быстрый тест: Постройте точечную диаграмму. Если точки выстраиваются вдоль прямой линии — используйте Пирсона. Если точки образуют изогнутую, но неизменно растущую или падающую кривую — выбирайте Спирмена.

Способ 1: Расчет корреляции Пирсона функцией CORREL

Это самый быстрый способ получить коэффициент линейной корреляции ($r$).

Алгоритм действий:

Подготовьте два столбца с числовыми данными. Убедитесь, что они одинаковой длины и не содержат текста.
В пустой ячейке введите формулу: =CORREL(диапазон_первый; диапазон_второй) Пример: =CORREL(A2:A100; B2:B100)
Нажмите Enter.

Результат будет числом в диапазоне от -1 до 1:

Близко к 1: Сильная прямая связь.
Близко к -1: Сильная обратная связь.
Близко к 0: Линейная связь отсутствует.

Способ 2: Матрица корреляций через «Пакет анализа»

Если нужно сравнить взаимосвязь сразу нескольких показателей (например, 5 столбцов данных), удобнее использовать надстройку «Пакет анализа», которая строит полную матрицу корреляций.

Инструкция по подключению и использованию:

Перейдите на вкладку Файл → Параметры → Надстройки.
Внизу окна в поле «Управление» выберите Надстройки Excel и нажмите Перейти.
Поставьте галочку напротив Пакет анализа и нажмите ОК.
На ленте появится вкладка Данные. Нажмите кнопку Анализ данных.
Выберите пункт Корреляция и нажмите ОК.
Укажите входной интервал (выделите все столбцы с данными сразу) и выберите место для вывода результата.

Этот метод автоматически исключает заголовки (если отмечена галочка «Метки в первой строке») и выдает таблицу, где на пересечении строк и столбцов указаны коэффициенты связи между каждой парой переменных.

Способ 3: Расчет корреляции Спирмена

В Excel нет отдельной функции для ранговой корреляции Спирмена, поэтому расчет проводится в два этапа: ранжирование данных и вычисление корреляции рангов.

Пошаговый расчет:

Создайте два новых столбца рядом с исходными данными для рангов.
В первом столбце рангов введите формулу для ранжирования первого набора данных: =RANK.AVG(A2; $A$2:$A$100; 1) Где $A$2:$A$100 — весь диапазон данных, а 1 означает сортировку по возрастанию.
Протяните формулу вниз до конца таблицы. Повторите действие для второго столбца данных.
Теперь посчитайте корреляцию Пирсона уже для новых столбцов с рангами: =CORREL(диапазон_рангов_1; диапазон_рангов_2)

Полученное значение и есть коэффициент ранговой корреляции Спирмена ($\rho$).

Ошибка в диапазонах: При использовании функции RANK.AVG обязательно фиксируйте диапазон данных знаками доллара ($), например $A$2:$A$100. Иначе при протягивании формулы диапазон сместится, и ранги будут рассчитаны неверно.

Интерпретация результатов

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

Значение коэффициента	Характер связи	Пример интерпретации
0.7 – 1.0	Очень сильная прямая	Чем больше бюджет на рекламу, тем выше продажи.
0.3 – 0.7	Умеренная прямая	Существует тенденция: с ростом стажа зарплата увеличивается.
-0.3 – -0.7	Умеренная обратная	Чем выше цена товара, тем ниже спрос.
-0.7 – -1.0	Очень сильная обратная	Строгая обратная зависимость показателей.
-0.3 – 0.3	Слабая или отсутствует	Переменные практически не зависят друг от друга линейно.

Частые ошибки при расчете

Наличие пустых ячеек. Функция CORREL игнорирует пары, где хотя бы одно значение пусто, но это может исказить выборку. Лучше удалить строки с пропусками заранее.
Текст вместо чисел. Если в ячейках хранятся числа в текстовом формате (часто бывает при импорте из 1С или веб-сайтов), функция вернет ошибку или неверный результат. Преобразуйте текст в числа через «Текст по столбцам».
Разная длина массивов. Аргументы функции должны содержать одинаковое количество значений.
Путаница в причинности. Высокая корреляция не означает, что изменение переменной А вызывает изменение переменной Б. Они могут зависеть от третьего скрытого фактора.

FAQ

Можно ли посчитать корреляцию для более чем двух столбцов одной формулой? Нет, функция CORREL принимает только два массива. Для множественного анализа используйте «Пакет анализа» (Способ 2) или протяните формулу CORREL для разных пар столбцов вручную.

Что делать, если функция возвращает ошибку #ДЕЛ/0!? Эта ошибка возникает, если дисперсия одного из массивов равна нулю (все значения в столбце одинаковы) или если диапазоны пустые/некорректные. Проверьте данные на наличие вариации.

Подходит ли этот метод для прогнозов? Коэффициент корреляции сам по себе не делает прогнозы, он лишь оценивает надежность связи. Для прогнозирования значений одной переменной на основе другой используется инструмент «Регрессия» в том же «Пакеете анализа» или функция ПРЕДСКАЗ.