Как проверить значимость коэффициента корреляции в 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, вы готовы к проведению корреляционного анализа:

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

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

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

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

В корреляционной матрице 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) )

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

В первой строке и первом столбце матрицы введите метки переменных в том же порядке, в котором они указаны в исходной таблице (см. снимок экрана ниже).
Введите указанную выше формулу в крайнюю левую ячейку (в нашем случае – B16).
Перетащите формулу вниз и вправо, чтобы скопировать ее в нужное количество строк и столбцов (в нашем примере — в 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 лучший способ получить визуальное представление взаимосвязей между вашими данными — нарисовать точечную диаграмму с линией тренда. Вот как:

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

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

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

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

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

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

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

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

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

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

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

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

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

Что такое корреляционный тест Пирсона?

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

Выходными данными является коэффициент корреляции Пирсона (r), значение которого находится в диапазоне от -1 до 1 и указывает на силу связи.

Следующие значения r указывают направление и силу связи.

r = -1 – идеальная отрицательная ассоциация.
r = 0: связи нет
r = +1: идеальная положительная ассоциация.

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

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

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

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

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

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

Просто замените «массив1» на диапазон ячеек, содержащих первую переменную, и замените «массив2» на диапазон ячеек, содержащих вторую переменную.

В приведенном выше примере коэффициент корреляции Пирсона (r) равен «0,76».

2. Рассчитать t-статистику по значению коэффициента

Следующим шагом является преобразование значения коэффициента корреляции Пирсона в t-статистику. Для этого необходимы две составляющие: r и количество пар в тесте (n).

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

Уравнение, используемое для преобразования r в t-статистику, можно найти ниже.

Формула для этого в Excel приведена ниже.

Просто замените «r» на значение коэффициента корреляции, а «n» — на количество наблюдений в анализе.

Для примера в этом руководстве формулу, используемую в Excel, можно увидеть ниже.

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

Добавление функции ABS преобразует значение коэффициента в абсолютное (положительное) число. В противном случае отрицательное значение коэффициента вызовет ошибку.

3. Рассчитайте значение p из статистики t

Последний шаг в процессе вычисления p-значения для теста корреляции Пирсона в Excel — преобразование t-статистики в p-значение.

Прежде чем это можно будет сделать, нам просто нужно вычислить последнюю часть информации: количество степеней свободы (DF). DF можно найти, вычитая 2 из n (n – 2).

Теперь мы готовы вычислить p-значение. Для этого просто используйте функцию =СТЬЮДРАСП в Excel.

Просто введите приведенную ниже формулу.

Замените «x» статистикой t, созданной ранее, и замените «deg_freedom» на DF. Наконец, для хвостов введите число «1» для одностороннего анализа или «2» для двустороннего анализа. Если вы не уверены, что использовать, используйте двусторонний анализ («2»).

Ниже на снимке экрана показано, как это выглядит в Excel на примере.

В этом примере значение p равно «0,006». Следовательно, существует значительная положительная корреляция (r=0,76) между возрастом участников и их ИМТ.

Заключение

Не существует простого способа вычислить значение p для теста корреляции Пирсона в Excel. Однако путем расчета коэффициента корреляции Пирсона его можно преобразовать в t-статистику, которую, в свою очередь, можно использовать для расчета p-значения.

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

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

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

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

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

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

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

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

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

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

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

Лесли Крамер – обозреватель 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, данные будут созданы автоматически. Вы можете добавить текст и условное форматирование, чтобы очистить результат.

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