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

Обновлено: 20.11.2024

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

Поиск корреляции между двумя рядами данных — один из наиболее распространенных статистических расчетов при работе с большими наборами данных.

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

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

Итак, приступим!

Это руководство охватывает:

Что такое коэффициент корреляции?

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

Коэффициент корреляции — это значение, показывающее, насколько тесно связаны два ряда данных.

Наиболее часто используемым примером является вес и рост 10 человек в группе. Если мы вычислим коэффициент корреляции данных о росте и весе для этих людей, мы получим значение от -1 до 1.

Значение меньше нуля указывает на отрицательную корреляцию, что означает, что если рост увеличивается, вес уменьшается, или если вес увеличивается, то уменьшается рост.

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

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

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

Теперь давайте посмотрим, как рассчитать этот коэффициент корреляции в Excel.

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

Как я уже упоминал, есть несколько способов расчета коэффициента корреляции в Excel.

Использование формулы CORREL

CORREL – это статистическая функция, впервые появившаяся в Excel 2007.

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

Ниже приведена формула, которая это сделает:

Приведенная выше функция КОРРЕЛ принимает два аргумента: ряд с точками данных о высоте и ряд с точками данных о весе.

Как только вы нажмете Enter, Excel выполнит все расчеты в серверной части и выдаст вам один номер коэффициента корреляции Пирсона.

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

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

Но если у вас есть несколько рядов и вы хотите узнать коэффициент корреляции всех этих рядов, вы также можете рассмотреть возможность использования пакета инструментов анализа данных в Excel (см. далее)

Использование пакета инструментов анализа данных

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

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

Включение пакета инструментов анализа данных

Ниже приведены шаги для включения пакета инструментов анализа данных в Excel:

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

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

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

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

Ниже приведены шаги для этого:

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

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

Итак, это два быстрых и простых метода расчета коэффициента корреляции в Excel.

Если вы когда-нибудь изучали статистику, то наверняка сталкивались с коэффициентом корреляции.

Но можно ли рассчитать это в Excel?

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

В этом посте я покажу вам 3 способа расчета коэффициента корреляции в Excel.

Видеоруководство

Что такое коэффициент корреляции?

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

Вычисление может иметь значение от 0 до 1.

Значение 0 указывает, что две переменные сильно не связаны, а значение 1 указывает, что они сильно связаны.

Например, у вас могут быть данные о росте (метры) и весе (килограммы) для выборки людей, и вы хотите узнать, связаны ли эти две переменные.

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

Формула коэффициента корреляции

Коэффициент корреляции r можно рассчитать по приведенной выше формуле, где x и y – переменные, корреляцию которых вы хотите проверить.

В этом примере переменная x — это рост, а переменная y — вес. r — это соотношение между ростом и весом.

Вычисление коэффициента корреляции по определению

Давайте посмотрим, как мы можем рассчитать это в Excel на основе приведенного выше определения.

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

  1. Рассчитайте средний рост.
  2. Рассчитать средний вес.
  3. Рассчитайте разницу между высотой и средней высотой для каждой точки данных. Эту формулу необходимо скопировать для каждой строки.
  4. Рассчитайте разницу между весом и средним весом для каждой точки данных. Эту формулу необходимо скопировать для каждой строки.
  5. Вычислите квадрат разницы из шага 3 для каждой строки.
  6. Вычислите квадрат разницы из шага 4 для каждой строки.
  7. Вычислите произведение различий на шагах 3 и 4 для каждой строки.
  8. Рассчитайте сумму квадратов разностей на шаге 5.
  9. Рассчитайте сумму квадратов разностей на шаге 6.
  10. Рассчитайте сумму произведений разностей из шага 7.
  11. Рассчитайте корреляцию по следующей формуле.

Это довольно сложный расчет с множеством промежуточных шагов.

К счастью, в Excel есть встроенная функция для получения корреляции, которая значительно упрощает вычисления.

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

Эта функция предназначена специально для расчета коэффициента корреляции Пирсона в Excel.

Он очень прост в использовании. Он принимает два диапазона значений в качестве единственных двух аргументов.

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

Приведенная выше формула — это то, что вам нужно для расчета корреляции между ростом и весом.

Вау, это намного проще, чем вычислять с нуля!

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

Статистические инструменты

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

Чтобы включить пакет инструментов анализа:

  1. Перейдите на вкладку "Файл" и выберите "Параметры".
  2. Перейдите на вкладку "Надстройки" в параметрах Excel.
  3. Выберите «Надстройки Excel» в раскрывающемся списке и нажмите кнопку «Перейти».
  4. Отметьте опцию Analysis ToolPak среди доступных надстроек.
  5. Нажмите кнопку ОК.

Теперь на вкладке "Данные" будет доступна команда "Анализ данных", которую можно щелкнуть, чтобы открыть пакет инструментов "Анализ".

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

Откроется меню корреляции анализа данных.

  1. Укажите входной диапазон для расчета корреляции. Это должен быть диапазон с числовыми значениями, организованными в столбцы или строки.
  2. Выберите параметр «Группировать по столбцам» или «Строкам». В этом примере данные организованы по столбцам, так как все значения роста находятся в одном столбце, а значения веса — в отдельном столбце.
  3. Укажите, будут ли в вашем диапазоне ввода метки в первой строке. Эти метки используются позже в выходных данных, поэтому лучше выбрать входной диапазон, включающий метки.
  4. Выберите место для вывода в параметрах вывода. Вы можете выбрать расположение на текущем листе, расположение на новом листе или в новой книге.
  5. Нажмите кнопку ОК, чтобы создать расчет.

Это выведет матрицу корреляции.

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

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

Выводы

Корреляция — очень полезная статистика, позволяющая определить, связаны ли ваши данные.

Однако математическая формула может быть пугающей, особенно при попытке вычислить ее в Excel.

К счастью, есть несколько простых способов реализовать этот расчет в Excel.

Об авторе

Джон МакДугалл

Джон — Microsoft MVP, внештатный консультант и тренер, специализирующийся на Excel, Power BI, Power Automate, Power Apps и SharePoint. Другие интересные статьи Джона можно найти в его блоге или на канале YouTube.

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

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

Что такое коэффициент корреляции Пирсона?

Коэффициент корреляции Пирсона – это простой способ расчета степени корреляции между двумя переменными, возвращающий значение (называемое r) в диапазоне от −1 до 1.Идеальная корреляция (r = 1) между двумя переменными будет иметь место, когда увеличение одной переменной на определенную величину приводит к соответствующему увеличению другой или наоборот.

Совершенная отрицательная корреляция (r = −1) в основном такая же, за исключением того, что увеличение одной переменной приводит к соответствующему уменьшению другой. Наконец, отсутствие какой бы то ни было корреляции означает, что между двумя вещами вообще нет связи.

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

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

Самый простой способ найти корреляцию Пирсона в Excel — использовать встроенную функцию «Пирсона» или (аналогично) функцию «Коррел». Функция имеет простой синтаксис: PEARSON(массив 1, массив 2).

Короче говоря, вам просто нужны два массива значений (т. е. столбцы результатов, например, возраст и кровяное давление, организованные таким образом, чтобы для каждого отдельного пациента была строка) одинаковой длины, а затем введите «=PEARSON(» в пустую ячейку, затем диапазон значений для первого массива, запятая, затем диапазон значений для второго. Затем вы закрываете скобки, нажимаете «Ввод», и он возвращает r значение.

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

Вы также можете использовать функцию «Коррел», которая выполняет те же вычисления, что и «Пирсон», и в версиях Excel, начиная с 2003 г., приводит к точно такому же результату. Однако, если у вас более старая версия Excel, вам следует использовать функцию «Коррекция», поскольку при использовании «Пирсона» могут возникать ошибки округления.

Поиск r Pearson вручную

Вы также можете рассчитать значение r в Excel более традиционным методом, но с помощью автоматических вычислений из программы. Сначала поместите значения ваших переменных (которые для ясности можно обозначить как x и y) в два столбца, а затем создайте еще три столбца: xy< /em>, x 2 и y 2 . Теперь умножьте каждое значение в столбце x на столбец y в столбце xy (используя номера ячеек в расчетах, чтобы вы могли перетаскивать их вниз для остальной части столбца), возведите в квадрат значения x для следующего столбца и возведите в квадрат значения y для последнего.

Создайте строку «сумма» под вашими данными и возьмите сумму всех значений для каждого столбца. Затем вы можете использовать формулу для расчета значения r:

Здесь n – это количество имеющихся у вас пар значений. Вы можете выполнить это по частям: возьмите количество пар значений, умножьте его на сумму столбца xy, а затем вычтите произведение сумм столбцов x. > и значения y.

Затем умножьте сумму столбца x 2 на n, вычтите сумму квадрата столбца x, сделайте то же самое. для y и умножьте их вместе, затем извлеките квадратный корень из всего этого. Наконец, разделите первый результат на второй, чтобы получить значение r.

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

Если коэффициент корреляции равен 0, двумерные данные не коррелируют друг с другом.

Если коэффициент корреляции равен -1 или +1, двумерные данные сильно коррелируют друг с другом.

r=-1 обозначает сильную отрицательную связь, а r=1 обозначает сильную положительную связь.

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

Коэффициент корреляции рассчитывается с использованием коэффициента корреляции Пирсона, который определяется как:

Коэффициент корреляции

В этой статье мы рассмотрим, как найти коэффициенты корреляции в Excel.

Пример. Рассмотрим следующий набор данных:

Нахождение коэффициента корреляции в Excel:

<р>1. Использование функции КОРРЕЛ

В Excel для нахождения коэффициента корреляции используйте формулу:

<р>1.Найдем коэффициент корреляции для переменных и X и Y1.

Коэффициент корреляции x и y1

array1 : Набор значений X. Диапазон ячеек от A2 до A6.

array2 : Набор значений Y1. Диапазон ячеек — от B2 до B6.

Аналогичным образом можно найти коэффициенты корреляции для (X , Y2) и (X , Y3) с помощью формулы Excel. Наконец, коэффициенты корреляции следующие:

Из приведенной выше таблицы мы можем сделать вывод, что:

X и Y1 имеют отрицательный коэффициент корреляции.

X и Y2 имеют положительный коэффициент корреляции.

X и Y3 не коррелированы, так как коэффициент корреляции почти равен нулю.

Пример. Теперь давайте перейдем к двум другим методам, используя новый набор данных. Рассмотрим следующий набор данных:

Использование анализа данных

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

  1. Перейдите на вкладку "Файл" в левом верхнем углу окна Excel и выберите "Параметры".
  2. Откроется диалоговое окно «Параметры Excel». Теперь перейдите к параметру «Надстройки» и в разделе «Управление» выберите «Надстройки Excel» в раскрывающемся списке.
  3. Нажмите кнопку "Перейти".
  4. Откроется диалоговое окно "Надстройки". В этом отметьте опцию Analysis ToolPak.
  5. Нажмите "ОК"!

Добавлена ​​вкладка "Анализ данных"

Шаг 2. Теперь нажмите «Данные», а затем «Анализ данных». Появится диалоговое окно.

Шаг 3. В диалоговом окне выберите «Корреляция» из списка параметров. Нажмите ОК!

Шаг 4. Появится меню "Корреляция".

Шаг 5. В этом меню сначала укажите диапазон ввода. Диапазон ввода — это диапазон ячеек столбцов X и Y1, как показано на рисунке ниже.

Шаг 6. Кроме того, укажите выходной диапазон в качестве номера ячейки, в которой вы хотите отобразить результат. По умолчанию вывод будет отображаться на новом листе Excel, если вы не укажете диапазон вывода.

Шаг 7. Установите флажок «Ярлыки в первой строке», если в наборе данных есть ярлыки. В нашем случае столбец 1 имеет метку X, а столбец 2 — метку Y1.

Шаг 8. Нажмите "ОК".

Шаг 9. Теперь таблица анализа данных готова. Здесь вы можете увидеть коэффициент корреляции между X и Y1 в таблице анализа.

Аналогично можно найти коэффициенты корреляции XY2 и XY3. Наконец, все коэффициенты корреляции:

Использование функции PEARSON

Она точно аналогична функции КОРРЕЛ, которую мы обсуждали в предыдущем разделе. Синтаксис функции ПИРСОН:

Найдем коэффициент корреляции для X и Y1 в наборе данных примера 2, используя функцию ПИРСОН.

Формула вернет коэффициент корреляции X и Y1. Точно так же вы можете сделать для других.

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