Приветствую всех посетителей нашего сайта MoiSovety.ru! Недавно я работал над моделью для одной из торговых стратегий, которая является рыночно-нейтральной. Для этой модели мне пришлось рассчитывать коэффициенты корреляции (и, по сути, корреляционную матрицу). Я сделал это с помощью Python, но после этого все равно пришлось обрабатывать данные, так как невозможно вручную просмотреть миллионы ячеек. Но на этапе первичного анализа для кого-то может быть удобнее использовать Excel. Поэтому в данной статье я расскажу вам основные способы расчета коэффициента корреляции в Excel.
Если у вас возникнут вопросы или есть что-то дополнить, пожалуйста, напишите в комментариях. Я постараюсь ответить на них.
Дополнительно можно использовать стандартную формулу корреляции в Excel для расчета коэффициента. Однако в данной статье предлагается обратить внимание на уже имеющиеся способы в Excel.
Коротко про коэффициент корреляции
Коэффициент корреляции отражает степень взаимосвязи между двумя наборами данных. Он может принимать значения от -1 до +1, включая все дробные промежуточные значения:
- +1 – если обе величины абсолютно зависимы друг от друга, например, на рынке криптовалюты все очень зависит от биткоина, и коэффициент корреляции к «битку» для среднего альткоина обычно стремится к единице (но не полностью).
- 0 – если нет никакой зависимости, каждый движется в своем направлении.
- -1 – если наблюдается обратная зависимость, т.е. если один ряд идет вверх, то другой идет строго вниз.
Другими словами, каждой паре значений можно присвоить определенный коэффициент корреляции. Если же необходимо сравнить большое количество рядов между собой, то более удобным будет создать таблицу, где на пересечении соответствующих строк и столбцов будут находиться значения коэффициента корреляции для выбранных пар. Эту таблицу называют корреляционной матрицей.
В заключение, можно использовать несколько математических методов для вычисления коэффициента корреляции. Самый распространенный (и используемый в Excel) – метод Пирсона. Для кого-то другие варианты (например, Спирмен) могут показаться более простыми, но лично я предпочту перейти к любому более продвинутому языку программирования. Или, в крайнем случае, ввести формулу вручную.
Я слишком углубился в теорию, предлагаю перейти к практике.
Способ расчета 1 – Формула
Надеюсь, что вы уже знаете, как писать формулы. Если нет, то лучше закройте Excel
Теперь давайте попробуем рассчитать коэффициент корреляции. Если вы пришли сюда за формулой, то вот она (можно также найти через мастер функций, но руками быстрее):
Массивами мы подразумеваем ряды данных величин, между которыми мы хотим найти корреляцию. Давайте посмотрим на примере:
Этот конкретный пример показывает, что за очень короткий отрезок времени удалось получить коэффициент корреляции равный 0,29. Однако самое важное, что этот метод работает. Поэтому, если вам нужно быстро получить значение корреляции, то не придумать ничего лучше этого способа.
Способ 2 (корреляционная матрица) – Пакет анализа
В программе Microsoft Excel также имеется скрытый раздел под названием «Пакет анализа», в котором содержится надстройка для вычисления коэффициента корреляции. Однако лично я этим не пользуюсь, так как этот процесс занимает слишком много времени. Поэтому данный раздел скорее предназначен для тех, кто интересуется этой функцией, чем для тех, кто использует ее на практике.
- Сначала необходимо активировать данный раздел. Для этого перейдите в «Файл — Параметры — Надстройки».
- В списке найдите «Пакет анализа» (обычно он находится в разделе неактивных надстроек), выделите его, убедитесь, что внизу установлено «Надстройки Excel», и нажмите кнопку «Перейти».
- Поставьте галочку напротив «Пакет анализа» и нажмите кнопку «Ок». Это позволит активировать пакет.
- Следующим шагом будет переключиться на вкладку «Данные». Именно здесь справа появится кнопка «Анализ данных». Просто кликаем на неё.
- Выбираем опцию «Корреляция» из списка.
- Устанавливаем параметры. Необходимо передать все столбцы входного диапазона, для которых мы будем рассчитывать корреляцию. Выходные данные можно переместить на новый лист или задать здесь же.
Если количество указанных столбцов составляет два, результат будет аналогичен вычисленному по формуле. Однако, если передано несколько столбцов, на выходе будет представлена полная корреляционная матрица:
Мне не нравится то, что здесь нельзя сразу передать названия столбцов и оставить пустые ячейки в верхнем «треугольнике». Я могу пользоваться этим, но это удобно только для небольшого количества данных. Для работы с остальным приходится использовать Excel.
Видео по теме
Предоставляю видеоматериал на данную тему, в котором помимо основных аспектов также рассматриваются понятия корреляции Спирмена и поля корреляции.
