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

Обновлено: 03.07.2024

Лесли Крамер – обозреватель Institutional Investor, корреспондент CNBC, журналист Investopedia и главный редактор Markets Group.

Чип Стэплтон является обладателем лицензий Series 7 и Series 66, сдал экзамен CFA уровня 1 и в настоящее время имеет лицензию на страхование жизни, несчастных случаев и здоровья в Индиане. Он имеет 8-летний опыт работы в области финансов, от финансового планирования и управления активами до корпоративных финансов и планирования и анализа.

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

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

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

Иными словами, корреляция отвечает на вопрос: насколько переменная A (независимая переменная) объясняет переменную B (зависимая переменная)?

Ключевые выводы

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

Понимание корреляции

Формула корреляции

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

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

Распространенные ошибки при корреляции

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

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

Помните, корреляция между двумя переменными НЕ означает, что A вызвало B или наоборот.

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

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

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

Поиск корреляции в Excel

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

Это удобный способ расчета корреляции между двумя наборами данных. Но что, если вы хотите создать корреляционную матрицу для ряда наборов данных? Для этого вам нужно использовать плагин Excel Data Analysis. Плагин можно найти на вкладке «Данные» в разделе «Анализ».

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

После того как вы нажмете Enter, данные будут созданы автоматически. Вы можете добавить текст и условное форматирование, чтобы очистить результат.

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

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

Синтаксис

КОРРЕЛ(массив1, массив2)

Синтаксис функции КОРРЕЛ имеет следующие аргументы:

массив1 Обязательно. Диапазон значений ячеек.

массив2 Обязательно. Второй диапазон значений ячеек.

Примечания

Если аргумент массива или ссылки содержит текст, логические значения или пустые ячейки, эти значения игнорируются; однако включаются ячейки с нулевыми значениями.

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

Уравнение для коэффициента корреляции:

являются выборочными средними значениями AVERAGE(массив1) и AVERAGE(массив2).

Пример

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

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

В этой статье мы рассмотрим, как найти коэффициенты корреляции в 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. Точно так же вы можете сделать для других.

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.

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