Как рассчитать коэффициент корреляции Пирсона в Excel
Обновлено: 28.06.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 на основе приведенного выше определения.
Для расчета коэффициента корреляции с нуля необходимо выполнить несколько шагов.
- Рассчитайте средний рост.
- Рассчитать средний вес.
- Рассчитайте разницу между высотой и средней высотой для каждой точки данных. Эту формулу необходимо скопировать для каждой строки.
- Рассчитайте разницу между весом и средним весом для каждой точки данных. Эту формулу необходимо скопировать для каждой строки.
- Вычислите квадрат разницы из шага 3 для каждой строки.
- Вычислите квадрат разницы из шага 4 для каждой строки.
- Вычислите произведение различий на шагах 3 и 4 для каждой строки.
- Рассчитайте сумму квадратов разностей на шаге 5.
- Рассчитайте сумму квадратов разностей на шаге 6.
- Рассчитайте сумму произведений разностей из шага 7.
- Рассчитайте корреляцию по следующей формуле.
Это довольно сложный расчет с множеством промежуточных шагов.
К счастью, в Excel есть встроенная функция для получения корреляции, которая значительно упрощает вычисления.
Функция КОРРЕЛ
Эта функция предназначена специально для расчета коэффициента корреляции Пирсона в Excel.
Он очень прост в использовании. Он принимает два диапазона значений в качестве единственных двух аргументов.
- Переменная1 и Переменная2 — это две переменные, между которыми вы хотите рассчитать коэффициент корреляции Пирсона.
- Это обязательные входные данные, и они должны представлять собой массив чисел из одного столбца или одной строки. Переменная1 и Переменная2 также должны иметь одинаковую размерность.
< /p>
Приведенная выше формула — это то, что вам нужно для расчета корреляции между ростом и весом.
Вау, это намного проще, чем вычислять с нуля!
Этот метод также является динамическим. Если ваши данные изменятся, расчет корреляции будет обновлен, чтобы отразить новые данные.
Статистические инструменты
Excel поставляется с мощной надстройкой статистических инструментов, но вам нужно сначала включить ее, чтобы использовать ее, и она довольно скрыта.
< бр />р>
Чтобы включить пакет инструментов анализа:
- Перейдите на вкладку "Файл" и выберите "Параметры".
- Перейдите на вкладку "Надстройки" в параметрах Excel.
- Выберите «Надстройки Excel» в раскрывающемся списке и нажмите кнопку «Перейти».
- Отметьте опцию Analysis ToolPak среди доступных надстроек.
- Нажмите кнопку ОК.
Теперь на вкладке "Данные" будет доступна команда "Анализ данных", которую можно щелкнуть, чтобы открыть пакет инструментов "Анализ".
< /p>
При этом откроется меню анализа данных, после чего вы сможете выбрать «Корреляция» и нажать кнопку «ОК».
Откроется меню корреляции анализа данных.
- Укажите входной диапазон для расчета корреляции. Это должен быть диапазон с числовыми значениями, организованными в столбцы или строки.
- Выберите параметр «Группировать по столбцам» или «Строкам». В этом примере данные организованы по столбцам, так как все значения роста находятся в одном столбце, а значения веса — в отдельном столбце.
- Укажите, будут ли в вашем диапазоне ввода метки в первой строке. Эти метки используются позже в выходных данных, поэтому лучше выбрать входной диапазон, включающий метки.
- Выберите место для вывода в параметрах вывода. Вы можете выбрать расположение на текущем листе, расположение на новом листе или в новой книге.
- Нажмите кнопку ОК, чтобы создать расчет.
Это выведет матрицу корреляции.
Это означает, что если у вас более двух столбцов переменных, матрица будет содержать коэффициент корреляции для всех комбинаций переменных.
Недостаток этого метода заключается в том, что выходные данные являются статическими. Если ваши данные изменятся, вам потребуется повторно запустить анализ данных, чтобы обновить матрицу корреляции.
Выводы
Корреляция — очень полезная статистика, позволяющая определить, связаны ли ваши данные.
Однако математическая формула может быть пугающей, особенно при попытке вычислить ее в 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 em> значение.
Как всегда, вы можете выделить значения, которые хотите найти для корреляции, с помощью мыши или перейдя к соответствующим ячейкам с помощью клавиш со стрелками на клавиатуре.
Вы также можете использовать функцию «Коррел», которая выполняет те же вычисления, что и «Пирсон», и в версиях 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 не коррелированы, так как коэффициент корреляции почти равен нулю.
Пример. Теперь давайте перейдем к двум другим методам, используя новый набор данных. Рассмотрим следующий набор данных:
Использование анализа данных
Мы также можем проанализировать данный набор данных и рассчитать коэффициент корреляции. Для этого выполните следующие действия:
- Перейдите на вкладку "Файл" в левом верхнем углу окна Excel и выберите "Параметры".
- Откроется диалоговое окно «Параметры Excel». Теперь перейдите к параметру «Надстройки» и в разделе «Управление» выберите «Надстройки Excel» в раскрывающемся списке.
- Нажмите кнопку "Перейти".
- Откроется диалоговое окно "Надстройки". В этом отметьте опцию Analysis ToolPak.
- Нажмите "ОК"!
Добавлена вкладка "Анализ данных"
Шаг 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. Точно так же вы можете сделать для других.
Читайте также: