Корреляция в Excel: сила связи двух рядов и уравнение прямой

Два столбца чисел почти всегда как-то меняются вместе, но на глаз не понять, связь это или совпадение. Справка Microsoft определяет силу связи по коэффициенту: значение около 0 означает слабую связь или её отсутствие^[1]. Один коэффициент отвечает на вопрос «связаны ли ряды», а регрессия — на вопрос «насколько вырастет один показатель, если вырастет другой».

Порядок работы такой: найти функцию КОРРЕЛ, ввести формулу с двумя диапазонами одинаковой длины, проверить знак и силу связи, исправить ошибки #Н/Д и #ДЕЛ/0!, а затем применить расчёт к своим данным и построить линию тренда. Корреляция в Excel и уравнение регрессии считаются на одних и тех же двух рядах. Остальные темы анализа собраны на стартовой странице тренажёра с уроками Excel.

Ключевые факты: корреляция в Excel

• КОРРЕЛ возвращает коэффициент корреляции двух диапазонов ячеек^[1].
• Полиномиальная линия тренда строится до 6-й степени, а R-квадрат принимает значения от 0 до 1^[3].
• Ряды разной длины дают #Н/Д, а пустой или постоянный ряд — #ДЕЛ/0!^[1].
• Функция КОРРЕЛ доступна по справке в Excel 2016 и Excel 2019, а также в Microsoft 365, Excel 2024 и Excel 2021 с версиями для Mac^[1].
• ЛИНЕЙН в отличие от КОРРЕЛ возвращает не одно число, а массив коэффициентов уравнения y = mx + b^[2].

Как работает корреляция в Excel: функция КОРРЕЛ и шкала коэффициента

Корреляция в Excel считается функцией КОРРЕЛ, которая возвращает коэффициент от −1 до +1 для двух диапазонов. Синтаксис по справке: =КОРРЕЛ(массив1;массив2), оба аргумента обязательны^[1]. Ряды можно задавать диапазонами одинаковой длины в соседних столбцах или на разных листах. В английском интерфейсе функция называется CORREL, а в пакете анализа её результат совпадает с PEARSON^[5].

КОРРЕЛ по-разному обращается с пропусками и нулями. Текст, логические значения и пустые ячейки в диапазоне пропускаются, а ячейки с нулём учитываются^[1]. Поэтому пустой месяц и месяц с нулевыми продажами дают разный коэффициент: в первом случае точка выпадает из расчёта, во втором — тянет связь вниз.

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

Разобравшись со шкалой, переходим к примеру — на нём видно и коэффициент, и уравнение.

Пошаговый пример: связь рекламы и продаж

КОРРЕЛ на этом примере показывает, насколько тесно продажи следуют за расходами на рекламу за пять месяцев. Расходы стоят в A2:A6, продажи — в B2:B6, оба ряда в тысячах рублей.

1. Введите ряды в A2:A6 и B2:B6 — одинаковой длины.
2. В E2 введите =КОРРЕЛ(A2:A6;B2:B6) и нажмите Enter: получится 0,997054486.
3. В E3 и E4 введите НАКЛОН и ОТРЕЗОК — первым аргументом идут продажи, вторым реклама.
4. Проверьте результат: наклон 2,6, отрезок 1,6, R-квадрат 0,9941^[7].
5. Сверьте R-квадрат с квадратом коэффициента: =E2^2 даёт то же число.

Уравнение читается так: каждая тысяча рекламы добавляет 2,6 тысячи продаж, а 1,6 — уровень продаж при нулевой рекламе. Порядок аргументов важен: НАКЛОН и ОТРЕЗОК принимают сначала зависимый ряд, а КОРРЕЛ от перестановки не меняется. Посчитайте коэффициент в уроке тренажёра: урок меняет ряды и проверяет, что формула ссылается на оба диапазона.

На своих данных начните с той же раскладки: зависимый показатель — продажи, выручка, число заявок — ставьте во второй столбец, а фактор — рекламу, цену, число звонков — в первый. Так аргументы НАКЛОН и ОТРЕЗОК не перепутаются, а подписи в столбце D подскажут коллеге, что считает каждая формула.

Однако одна пара чисел наклона и отрезка — лишь частный случай регрессии, а ЛИНЕЙН устроена шире.

Корреляция или регрессия: чем отличаются КОРРЕЛ, НАКЛОН и ЛИНЕЙН

КОРРЕЛ отличается от ЛИНЕЙН тем, что возвращает одно число — силу связи, а ЛИНЕЙН подбирает уравнение прямой y = mx + b методом наименьших квадратов^[2]. Синтаксис ЛИНЕЙН: =ЛИНЕЙН(известные_y;[известные_x];[конст];[статистика]).

ЛИНЕЙН возвращает массив, поэтому для одного числа её оборачивают в ИНДЕКС: =ИНДЕКС(ЛИНЕЙН(B2:B6;A2:A6);1) даёт наклон, а =ИНДЕКС(ЛИНЕЙН(B2:B6;A2:A6);2) — отрезок^[2]. Если аргумент «конст» равен ЛОЖЬ, отрезок b принудительно становится нулём; значение ИСТИНА в аргументе «статистика» добавляет к ответу r2 и другие показатели^[2]. В примере справки ряды y 1, 9, 5, 7 и x 0, 4, 2, 3 дают наклон 2 и отрезок 1^[2].

Регрессия через пакет анализа строит тот же расчёт отчётом: инструмент Regression выполняет линейную регрессию методом наименьших квадратов, а Correlation выводит матрицу КОРРЕЛ для всех пар столбцов^[5]. В Google Таблицах КОРРЕЛ тоже вычисляет коэффициент Пирсона, но аргументы там названы «данные_y» и «данные_x»^[6]. Справка Google называет её аналогом PEARSON и тоже пропускает текст среди значений^[6], так что формулу из Excel можно перенести без изменений.

Именно поэтому, кроме формул, полезно увидеть связь на диаграмме — это делает линия тренда.

Линия тренда и R-квадрат на практике: регрессия на диаграмме

Линия тренда показывает регрессию прямо на точечной диаграмме и подписывает, насколько хорошо прямая совпадает с точками. Чтобы добавить её, выделите диаграмму, нажмите кнопку элементов диаграммы — зелёный плюс справа сверху — и выберите Trendline^[4]. Тип и параметры меняют в панели Format Trendline.

R-квадрат линии тренда — число от 0 до 1: чем ближе к 1, тем лучше линия совпадает с данными^[3]. Для линейной линии он равен квадрату КОРРЕЛ, в примере выше — 0,9941^[7]. На точечной диаграмме линия строится по порядку значений x, поэтому ряды сортировать не нужно^[3].

Поля Forward и Backward в той же панели продлевают линию вперёд и назад^[4]. Если на диаграмме несколько рядов, Excel предложит сначала выбрать ряд, к которому относится линия^[4].

Остаётся понять, когда коэффициент и уравнение дадут неверный вывод.

Когда корреляция в Excel не подойдёт

Корреляция в Excel не подойдёт, если ряды разной длины, один из них постоянен или связь между ними не прямая. Ниже — признаки, которые видно до расчёта, и что сделать в каждом случае, чтобы коэффициент не ввёл в заблуждение.

• Если в рядах разное число точек, КОРРЕЛ вернёт #Н/Д^[1]: выровняйте диапазоны по строкам.
• Если один ряд пуст или все его значения одинаковы, стандартного разброса нет, и функция вернёт #ДЕЛ/0!^[1].
• Когда точки лежат дугой, коэффициент Пирсона занизит связь — постройте диаграмму и подберите тип линии тренда.
• При данных с нулями и отрицательными числами степенную и экспоненциальную линию тренда построить нельзя^[3].
• Если нужен y для x за пределами известных значений, ответ уравнения справка называет недостоверным^[2]: по примеру при x = 7 формула даст 19,8, но такой ответ опасно переносить в план.

Отдельный риск — числа, записанные как текст: функция их пропустит, как и пустые ячейки^[1], и коэффициент посчитается по неполному ряду. Если результат выглядит странно, проверьте формат ячеек в обоих столбцах и количество чисел в каждом ряду.

На Mac обе функции тоже работают: в блоке «Применяется к» у КОРРЕЛ перечислены версии для Mac у трёх новейших редакций^[1]. Как построить саму точечную диаграмму, разобрано на странице о диаграммах и графиках, а разброс одного ряда — на странице об отклонении и дисперсии. Если отчёт уйдёт коллеге со старой редакцией, сверьте её с этим списком заранее.