Как построить поле корреляции в Excel

Обновлено: 20.11.2024

Одним из самых простых статистических расчетов, которые можно выполнить в Excel, является корреляция. Несмотря на простоту, он очень полезен для понимания взаимосвязей между двумя или более переменными. Microsoft Excel предоставляет все необходимые инструменты для проведения корреляционного анализа, вам просто нужно знать, как ими пользоваться.

Корреляция в Excel — основы

Корреляция — это мера, описывающая силу и направление взаимосвязи между двумя переменными. Он обычно используется в статистике, экономике и социальных науках для составления бюджетов, бизнес-планов и т. п.

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

Вот несколько примеров сильной корреляции:

  • Количество потребляемых калорий и ваш вес (положительная корреляция)
  • Температура снаружи и ваши счета за отопление (отрицательная корреляция)

А вот примеры данных со слабой корреляцией или без корреляции:

  • Имя вашей кошки и ее любимая еда
  • Цвет ваших глаз и ваш рост

Важно понимать, что корреляция показывает только то, насколько тесно связаны две переменные. Однако корреляция не означает причинно-следственной связи. Тот факт, что изменения одной переменной связаны с изменениями другой переменной, не означает, что одна переменная на самом деле вызывает изменение другой.

Если вам интересно изучать причинно-следственные связи и делать прогнозы, сделайте шаг вперед и проведите линейный регрессионный анализ.

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

Числовая мера степени связи между двумя непрерывными переменными называется коэффициентом корреляции (r).

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

Сила

Чем больше абсолютное значение коэффициента, тем сильнее связь:

  • Экстремальные значения -1 и 1 указывают на идеальную линейную зависимость, когда все точки данных лежат на прямой. На практике идеальная корреляция, как положительная, так и отрицательная, наблюдается редко.
  • Коэффициент 0 указывает на отсутствие линейной зависимости между переменными. Это то, что вы, вероятно, получите с двумя наборами случайных чисел.
  • Значения от 0 до +1/-1 представляют собой шкалу слабой, умеренной и сильной взаимосвязи. Чем ближе значение r к -1 или 1, тем сильнее связь.

Направление

Знак коэффициента (плюс или минус) указывает направление отношения.

  • Положительные коэффициенты представляют собой прямую корреляцию и создают восходящий наклон на графике: по мере увеличения одной переменной увеличивается и другая, и наоборот.
  • Отрицательные коэффициенты представляют собой обратную корреляцию и создают нисходящий наклон на графике: по мере увеличения одной переменной другая переменная имеет тенденцию к уменьшению.

Для лучшего понимания взгляните на следующие графики корреляции:

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

Корреляция Пирсона

В статистике измеряют несколько типов корреляции в зависимости от типа данных, с которыми вы работаете. В этом уроке мы сосредоточимся на наиболее распространенном.

Корреляция Пирсона, полное название Корреляция момента продукта Пирсона (PPMC), используется для оценки линейных отношений между данными, когда изменение одной переменной связано с пропорциональным изменением другой переменной. . Проще говоря, корреляция Пирсона отвечает на вопрос: можно ли представить данные в виде прямой?

В статистике это самый популярный тип корреляции, и если вы имеете дело с "коэффициентом корреляции" без дополнительных уточнений, скорее всего, это будет коэффициент Пирсона.

Вот наиболее часто используемая формула для определения коэффициента корреляции Пирсона, также называемого коэффициентом корреляции Пирсона:

Иногда вам могут встретиться две другие формулы для расчета коэффициента корреляции выборки (r) и коэффициента корреляции генеральной совокупности (ρ).

Как сделать корреляцию Пирсона в Excel

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

  • Найдите коэффициент корреляции Пирсона с помощью функции КОРРЕЛ.
  • Создайте корреляционную матрицу, выполнив анализ данных.
  • Найти несколько коэффициентов корреляции с помощью формулы.
  • Постройте график корреляции, чтобы визуально представить взаимосвязь данных.

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

Чтобы вычислить коэффициент корреляции вручную, вам придется использовать эту длинную формулу. Чтобы найти коэффициент корреляции в Excel, используйте функцию КОРРЕЛ или ПИРСОН и получите результат за доли секунды.

Функция КОРРЕЛ в Excel

Функция КОРРЕЛ возвращает коэффициент корреляции Пирсона для двух наборов значений. Его синтаксис очень прост и понятен:

  • Массив1 – это первый диапазон значений.
  • Массив2 – это второй диапазон значений.

Два массива должны иметь одинаковую длину.

Предполагая, что у нас есть набор независимых переменных (x) в B2:B13 и зависимых переменных (y) в C2:C13, наша формула коэффициента корреляции выглядит следующим образом:

Или мы можем поменять местами диапазоны и все равно получить тот же результат:

В любом случае формула показывает сильную отрицательную корреляцию (около -0,97) между среднемесячной температурой и количеством проданных обогревателей:

3 вещи, которые вы должны знать о функции КОРРЕЛ в Excel

Чтобы успешно рассчитать коэффициент корреляции в Excel, имейте в виду три простых факта:

Функция ПИРСОН в Excel

Функция PEARSON в Excel делает то же самое — вычисляет коэффициент корреляции момента продукта Pearson.

  • Массив1 – это диапазон независимых значений.
  • Массив2 – это диапазон зависимых значений.

Поскольку PEARSON и CORREL вычисляют коэффициент линейной корреляции Пирсона, их результаты должны совпадать, что обычно и происходит в последних версиях Excel 2007–Excel 2019.

Однако в Excel 2003 и более ранних версиях функция ПИРСОН может отображать некоторые ошибки округления. Поэтому в старых версиях рекомендуется использовать CORREL, а не PEARSON.

В нашем образце данных обе функции дают одинаковые результаты:

Как создать корреляционную матрицу в Excel с помощью анализа данных

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

Матрица корреляции – это таблица, в которой показаны коэффициенты корреляции между переменными на пересечении соответствующих строк и столбцов.

Матрица корреляции в Excel строится с помощью инструмента Корреляция из надстройки Пакет инструментов анализа. Эта надстройка доступна во всех версиях Excel с 2003 по Excel 2019, но не включена по умолчанию. Если вы еще не активировали его, сделайте это сейчас, выполнив действия, описанные в разделе Как включить пакет инструментов анализа данных в Excel.

С инструментами анализа данных, добавленными на ленту Excel, вы готовы к проведению корреляционного анализа:

  1. В правом верхнем углу вкладки Данные в группе Анализ нажмите кнопку Анализ данных.
  2. В диалоговом окне Анализ данных выберите «Корреляция» и нажмите «ОК».
  3. В поле Корреляция настройте параметры следующим образом:
    • Нажмите в поле Входной диапазон и выберите диапазон с вашими исходными данными, включая заголовки столбцов (в нашем случае B1:D13).
    • В разделе Сгруппировано по убедитесь, что установлен переключатель "Столбцы" (учитывая, что ваши исходные данные сгруппированы в столбцы).
    • Установите флажок "Ярлыки в первой строке", если выбранный диапазон содержит заголовки столбцов.
    • Выберите нужный вариант вывода.Чтобы матрица находилась на том же листе, выберите «Диапазон вывода» и укажите ссылку на крайнюю левую ячейку, в которую должна быть выведена матрица (в данном примере — A15).

    4. По завершении нажмите кнопку ОК:

    Ваша матрица коэффициентов корреляции готова и должна выглядеть примерно так, как показано в следующем разделе.

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

    В корреляционной матрице Excel вы можете найти коэффициенты на пересечении строк и столбцов. Если координаты столбца и строки совпадают, выводится значение 1.

    В приведенном выше примере нас интересует корреляция между зависимой переменной (количество проданных обогревателей) и двумя независимыми переменными (среднемесячная температура и расходы на рекламу). Итак, смотрим только на числа на пересечении этих строк и столбцов, которые выделены на скриншоте ниже:

    Отрицательный коэффициент -0,97 (округленный до 2 знаков после запятой) показывает сильную обратную зависимость между месячной температурой и продажами обогревателей: чем выше температура, тем меньше обогревателей продается.

    Положительный коэффициент 0,97 (с округлением до 2 знаков после запятой) указывает на сильную прямую связь между рекламным бюджетом и продажами — чем больше денег вы тратите на рекламу, тем выше продажи.

    Как провести множественный корреляционный анализ в Excel с помощью формул

    Создать корреляционную таблицу с помощью инструмента анализа данных очень просто. Однако эта матрица является статической, а это означает, что вам придется запускать корреляционный анализ заново каждый раз при изменении исходных данных.

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

    Чтобы это сделать, используйте эту общую формулу:

    КОРРЕЛ(СМЕЩ(диапазон_первой_переменной , 0, СТРОКИ($1:1)-1), СМЕЩ(диапазон_первой_переменной, 0, СТОЛБЦЫ($A:A)-1 ))

    Важное примечание! Чтобы формула работала, вы должны заблокировать первый диапазон переменных, используя абсолютные ссылки на ячейки.

    В нашем случае первый диапазон переменных равен $B$2:$B$13 (обратите внимание на знак $, который блокирует ссылку), и наша формула корреляции принимает следующий вид:

    =CORREL(СМЕЩЕНИЕ($B$2:$B$13, 0, СТРОКИ($1:1)-1), OFFSET($B$2:$B$13, 0, СТОЛБЦЫ($A:A)-1) )

    Подготовив формулу, давайте построим корреляционную матрицу:

    1. В первой строке и первом столбце матрицы введите метки переменных в том же порядке, в котором они указаны в исходной таблице (см. снимок экрана ниже).
    2. Введите указанную выше формулу в крайнюю левую ячейку (в нашем случае – B16).
    3. Перетащите формулу вниз и вправо, чтобы скопировать ее в нужное количество строк и столбцов (в нашем примере — в 3 строки и 3 столбца).

    В результате мы получили следующую матрицу с несколькими коэффициентами корреляции. Обратите внимание, что коэффициенты, возвращаемые нашей формулой, точно такие же, как и в Excel в предыдущем примере (соответствующие коэффициенты выделены):

    Как работает эта формула

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

      - возвращает диапазон, который представляет собой заданное количество строк и столбцов из указанного диапазона.
    • СТРОКИ и СТОЛБЦЫ – возвращает количество строк и столбцов в диапазоне соответственно. В нашей формуле корреляции оба используются с одной целью — получить количество столбцов для смещения от начального диапазона. И это достигается за счет грамотного использования абсолютных и относительных ссылок.

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

    Во-первых, давайте рассмотрим формулу в B18, которая находит корреляцию между месячной температурой (B2:B13) и количеством проданных обогревателей (D2:D13):

    =CORREL(СМЕЩЕНИЕ($B$2:$B$13, 0, СТРОКИ($1:3)-1), OFFSET($B$2:$B$13, 0, СТОЛБЦЫ($A:A)-1) )

    В первой функции OFFSET ROWS($1:1) преобразуется в ROWS($1:3), поскольку вторая координата является относительной, поэтому она изменяется в зависимости от относительного положения строки, в которую копируется формула (2 строки вниз). Таким образом, ROWS() возвращает 3, из которого мы вычитаем 1, и получаем диапазон, который находится на 2 столбца правее исходного диапазона, т. е. $D$2:$D$13 (продажа обогревателей).

    Второй параметр OFFSET не изменяет указанный диапазон $B$2:$B$13 (температура), поскольку COLUMNS($A:A)-1 возвращает ноль.

    В результате наша длинная формула превращается в простую КОРРЕЛ($D$2:$D$13, $B$2:$B$13) и возвращает именно тот коэффициент, который нам нужен.

    Формула в C18, которая вычисляет коэффициент корреляции для стоимости рекламы (C2:C13) и продаж (D2:D13), работает аналогичным образом:

    =CORREL(СМЕЩЕНИЕ($B$2:$B$13, 0, СТРОКИ($1:3)-1), OFFSET($B$2:$B$13, 0, СТОЛБЦЫ($A:B)-1) )

    Первая функция OFFSET абсолютно аналогична описанной выше и возвращает диапазон $D$2:$D$13 (продажа обогревателей).

    Во втором смещении COLUMNS($A:A)-1 меняется на COLUMNS($A:B)-1, потому что мы скопировали столбец формулы 1 вправо. Следовательно, OFFSET получает диапазон, который находится на 1 столбец правее исходного диапазона, т. е. $C$2:$C$13 (расходы на рекламу).

    Как построить график корреляции в Excel

    При корреляции в Excel лучший способ получить визуальное представление взаимосвязей между вашими данными — нарисовать точечную диаграмму с линией тренда. Вот как:

    1. Выберите два столбца с числовыми данными, включая заголовки столбцов. Порядок столбцов важен: независимая переменная должна быть в левом столбце, так как этот столбец должен быть отложен по оси x; переменная зависимая должна находиться в правом столбце, так как она будет отложена по оси Y.
    2. На вкладке Вставка в группе Чаты щелкните значок Точечной диаграммы. Это немедленно вставит точечную диаграмму XY на ваш лист.
    3. Щелкните правой кнопкой мыши любую точку данных на диаграмме и выберите «Добавить линию тренда…» в контекстном меню.

    Для получения подробных пошаговых инструкций см.:

    Для нашего примера набора данных графики корреляции выглядят так, как показано на изображении ниже. Кроме того, мы отображали значение R-квадрата, также называемое коэффициентом детерминации. Это значение указывает, насколько хорошо линия тренда соответствует данным: чем ближе R 2 к 1, тем лучше соответствие.

    Из значения R 2, отображаемого на диаграмме рассеяния, вы можете легко рассчитать коэффициент корреляции:

    1. Для большей точности заставьте Excel отображать больше цифр в значении R-квадрата, чем по умолчанию.
    2. Нажмите значение R 2 на диаграмме, выберите его с помощью мыши и нажмите Ctrl + C, чтобы скопировать его.
    3. Получите квадратный корень из R 2 либо с помощью функции SQRT, либо путем возведения скопированного значения R 2 в степень 0,5.

    Например, значение R 2 на втором графике равно 0,9174339392. Таким образом, вы можете найти коэффициент корреляции для Реклама и Проданные обогреватели по одной из следующих формул:

    Вы можете убедиться, что рассчитанные таким образом коэффициенты полностью соответствуют коэффициентам корреляции, найденным в предыдущих примерах, за исключением знака:

    Возможные проблемы с корреляцией в Excel

    Корреляция момента продукта Pearson показывает только линейную связь между двумя переменными. Это означает, что ваши переменные могут быть сильно связаны другим, криволинейным образом, и все же иметь коэффициент корреляции, равный или близкий к нулю.

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

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

    Вот как сделать корреляцию в Excel. Чтобы поближе ознакомиться с примерами, обсуждаемыми в этом руководстве, вы можете загрузить наш образец рабочей книги для расчета корреляции в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

    Коэффициент корреляции (значение от -1 до +1) показывает, насколько сильно две переменные связаны друг с другом. Мы можем использовать функцию КОРРЕЛ или надстройку пакета анализа в Excel, чтобы найти коэффициент корреляции между двумя переменными.

    – Коэффициент корреляции +1 указывает на идеальную положительную корреляцию. По мере увеличения переменной X увеличивается переменная Y. По мере уменьшения переменной X уменьшается и переменная Y.

    – Коэффициент корреляции, равный -1, указывает на полную отрицательную корреляцию. По мере увеличения переменной X переменная Z уменьшается. По мере уменьшения переменной X переменная Z увеличивается.

    – Коэффициент корреляции, близкий к 0, указывает на отсутствие корреляции.

    Чтобы использовать надстройку "Пакет инструментов анализа" в Excel для быстрого создания коэффициентов корреляции между несколькими переменными, выполните следующие действия.

    <р>1. На вкладке "Данные" в группе "Анализ" нажмите "Анализ данных".

    Примечание. Не можете найти кнопку "Анализ данных"? Нажмите здесь, чтобы загрузить надстройку Analysis ToolPak.

    <р>2. Выберите «Корреляция» и нажмите «ОК».

    <р>3. Например, выберите диапазон A1:C6 в качестве входного диапазона.

    <р>4. Проверьте ярлыки в первой строке.

    <р>5. Выберите ячейку A8 в качестве выходного диапазона.

    Вывод: переменные A и C имеют положительную корреляцию (0,91). Переменные А и В не коррелированы (0,19). Переменные B и C также не коррелированы (0.11). Вы можете проверить эти выводы, взглянув на график.

    В этом руководстве я покажу вам, как создать корреляционную матрицу с помощью Microsoft Excel. Я также покажу вам, как раскрасить ячейки, чтобы было легко визуализировать результаты.

    Ниже представлена ​​корреляционная матрица, которую я покажу вам, как ее создать.

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

    Для этого примера у меня есть 10 разных переменных; каждая переменная была введена в отдельный столбец.

    Для каждой переменной у меня есть 19 различных значений — это просто случайные числа для целей этого примера.

    На снимке экрана ниже показана верхняя часть моей таблицы данных.

    Как создать корреляционную матрицу в Excel

    Что я хочу сделать, так это создать матрицу корреляции, которая содержит значения коэффициента корреляции Пирсона между каждой из моих 10 различных переменных.

    1. Установите пакет инструментов анализа данных

    Возможно, самый простой способ создать корреляционную матрицу в Excel — использовать пакет инструментов анализа данных. Это надстройка, созданная корпорацией Майкрософт для предоставления инструментов анализа данных для статистического анализа.

    Чтобы установить это дополнение, перейдите в раздел Файл>Параметры.

    Затем нажмите Надстройки.

    Внизу вы хотите управлять надстройками Excel и нажмите кнопку Перейти.

    Затем установите флажок над надстройкой Пакет инструментов анализа и нажмите ОК.

    Теперь, когда вы нажимаете на ленту Данные вверху, вы должны увидеть кнопку Анализ данных в подразделе под названием Анализ.

    Теперь мы готовы создать корреляционную матрицу.

    2. Используйте Data Analysis ToolkPak для создания корреляционной матрицы в Excel

    Чтобы создать матрицу корреляции, перейдите в раздел Данные>Анализ данных.

    В списке выберите параметр Корреляция и нажмите ОК.

    Теперь введите следующее:

    • Диапазон ввода – введите ячейки, содержащие все данные, которые необходимо включить в анализ.
    • Сгруппировано по: если ваши данные организованы в разные столбцы, выберите параметр Столбцы; если ваши данные организованы в разные строки, вместо этого выберите параметр Строки
    • Ярлыки в первой строке. Установите этот флажок, если в верхней части набора данных есть ячейки, содержащие названия ярлыков.

    При этом есть также три варианта вывода, из которых можно выбрать, куда вы хотите вводить результаты:

    • Диапазон вывода — выберите область на текущем листе, куда вы хотите поместить результаты.
    • Новый слой рабочего листа: сохраните результаты на новом рабочем листе и назовите его.
    • Новая рабочая книга: сохраните корреляционную матрицу в отдельном файле Excel.

    Для моего примера я выбрал второй вариант, чтобы корреляционная матрица возвращалась на новом листе.

    Наконец нажмите кнопку ОК, чтобы запустить анализ.

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

    Теперь вы должны увидеть, что корреляционная матрица создана.

    В верхней строке и первом столбце будут перечислены все переменные, введенные в тест.

    Итак, в моем примере значение коэффициента корреляции для связи между Переменной 1 и Переменной 4 равно 0,108.

    Коэффициент корреляции — это значение в диапазоне от +1 до -1.

    Значение 0 означает отсутствие линейной корреляции между двумя переменными.

    Значение +1 означает, что существует совершенно положительная линейная корреляция между двумя переменными; Таким образом, по мере увеличения одной переменной увеличивается и другая.

    Вы можете видеть в матрице, что каждый раз, когда существует корреляция между одной и той же переменной, значение коэффициента корреляции равно 1. Это потому, что если вы нанесете две переменные с абсолютно одинаковыми значениями друг против друга, вы всегда получите идеальное соотношение. положительная линейная корреляция между ними.

    Значение -1 означает, что существует совершенно отрицательная линейная корреляция между двумя переменными. Таким образом, по мере увеличения одного значение другого уменьшается.

    Также обратите внимание, что заполнена только половина матрицы, потому что результаты были бы такими же, если бы были вычислены эти пустые ячейки.

    [Необязательно] Используйте функцию КОРРЕЛ для расчета коэффициента корреляции Пирсона

    Если вы предпочитаете вычислять значения коэффициента корреляции Пирсона самостоятельно, а не использовать матрицу, вы можете сделать это с помощью функции КОРРЕЛ.

    • массив1 – все ячейки, содержащие данные для первой переменной.
    • массив2 — все ячейки, содержащие данные для второй переменной.

    Если вы хотите пойти дальше и рассчитать p-значение для теста корреляции Пирсона, чтобы увидеть, значим ли результат, я отсылаю вас к моему руководству по выполнению теста корреляции Пирсона в Microsoft Excel.

    Как раскрасить ячейки корреляционной матрицы

    Иногда ячейки корреляционной матрицы окрашиваются в зависимости от значений их коэффициентов. Это легко сделать в Excel с помощью условного форматирования.

    Сначала выделите все значения в таблице, а затем перейдите на Главную>Условное форматирование>Новое правило.

    Выберите форматировать все ячейки на основе их значений в качестве типа правила.

    В приведенном ниже описании правила используйте раскрывающееся меню, чтобы изменить стиль на трехцветную шкалу.

    Затем измените настройки цвета на:

    МинимумСерединаМаксимум
    ТипЧислоЧислоЧисло
    Значение-1< /td>01
    ЦветКрасныйБелый Синий

    При использовании этого правила условного форматирования все ячейки со значением коэффициента корреляции -1 будут окрашены в красный цвет. Ячейки со значением 0 будут окрашены в белый цвет, а ячейки со значением 1 — в синий цвет.И поскольку это градиент цветов, любые значения между этими точками будут иметь оттенок цвета, который представляет значение их коэффициента корреляции.

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

    Ниже представлена ​​матрица корреляции для моего примера с примененным условным форматированием.

    Создание корреляционной матрицы в Excel: заключительные слова

    В этом руководстве я показал вам, как создать корреляционную матрицу в Microsoft Excel.

    Матрицу корреляции можно легко создать с помощью пакета инструментов анализа данных. После создания вы можете воспользоваться преимуществами условного форматирования Excel, раскрашивая ячейки в соответствии со значениями коэффициента корреляции.

    Используемая версия Microsoft Excel: 365 ProPlus

    Стивен является основателем Top Tip Bio. В настоящее время он является медицинским писателем и бывшим научным сотрудником с докторской степенью. Понравилось обучение? Тогда дайте мне знать, оставив комментарий ниже, или подумайте о том, чтобы угостить меня кофе.

    • -1 указывает на абсолютно отрицательную линейную корреляцию между двумя переменными.
    • 0 указывает на отсутствие линейной корреляции между двумя переменными.
    • 1 указывает на абсолютно положительную линейную корреляцию между двумя переменными.

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

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

    В этом руководстве объясняется, как создать и интерпретировать корреляционную матрицу в Excel.

    Как создать корреляционную матрицу в Excel

    Предположим, у нас есть следующий набор данных, который показывает среднее количество очков, подборов и передач для 10 баскетболистов:

    Чтобы создать матрицу корреляции для этого набора данных, перейдите на вкладку "Данные" на верхней ленте Excel и нажмите "Анализ данных".

    Если вы не видите эту опцию, вам нужно сначала загрузить бесплатный пакет инструментов анализа данных в Excel.

    В открывшемся новом окне выберите «Корреляция» и нажмите «ОК».

    В поле «Входной диапазон» выберите ячейки, в которых находятся данные (включая первую строку с метками). Установите флажок рядом с Метки в первой строке. Для выходного диапазона выберите ячейку, в которой вы хотите разместить корреляционную матрицу. Затем нажмите ОК.

    Это автоматически создаст следующую матрицу корреляции:

    Как интерпретировать корреляционную матрицу в Excel

    Значения в отдельных ячейках корреляционной матрицы говорят нам о коэффициенте корреляции Пирсона между каждой парной комбинацией переменных. Например:

    Корреляция между очками и подборами: -0,04639. Очки и подборы имеют небольшую отрицательную корреляцию, но это значение настолько близко к нулю, что нет убедительных доказательств значимой связи между этими двумя переменными.

    Корреляция между очками и передачами: 0,121871. Очки и передачи имеют небольшую положительную корреляцию, но это значение также довольно близко к нулю, поэтому нет убедительных доказательств значимой связи между этими двумя переменными.

    Корреляция между подборами и передачами: 0,713713. Подборы и передачи имеют сильную положительную корреляцию. То есть игроки, у которых больше подборов, чаще делают и больше передач.

    Обратите внимание, что все диагональные значения в матрице корреляции равны 1, потому что корреляция между переменной и самой собой всегда равна 1. На практике интерпретировать это число бесполезно.

    Дополнительно: визуализация коэффициентов корреляции

    Один из простых способов визуализировать значения коэффициентов корреляции в таблице — применить к таблице условное форматирование. На верхней ленте в Excel перейдите на вкладку «Главная», затем в группу «Стили». Нажмите «Диаграмма условного форматирования», затем нажмите «Цветовые шкалы», затем нажмите «Цветовая шкала зеленый-желтый-красный».

    Это автоматически применяет следующую цветовую шкалу к матрице корреляции:

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

    Читайте также: