Друзья, приветствую вас на WiFiGid! Буквально недавно разбирал данные для одной рыночно-нейтральной торговой стратегии, где как раз нужно было рассчитывать коэффициенты корреляции (точнее, строить корреляционную матрицу). Лично я делал это на «питоне» – все равно потом такие объемы данных удобнее обрабатывать программно, ведь тысячи и миллионы ячеек глазами не проверить.
Но на этапе предварительного анализа Excel остается отличным инструментом. Особенно если данных не слишком много и нужно быстро понять, есть ли связь между показателями. Поэтому в этой статье я покажу основные варианты расчета коэффициента корреляции в Excel.
А если вдруг останутся какие-то вопросы или дополнения – добро пожаловать в комментарии. По мере возможности буду отвечать.
И как дополнение, конечно, можно ввести стандартную математическую формулу корреляции в Excel и на ее основе вручную рассчитать коэффициент. Но в этой статье предлагаю все-таки остановиться на уже встроенных возможностях Excel, которые позволяют сделать это быстрее и с меньшим количеством ошибок.
Коротко про коэффициент корреляции
Коэффициент корреляции показывает, насколько сильно два набора данных связаны между собой. Проще говоря, он помогает понять: если меняется один показатель, меняется ли вместе с ним второй.
Значение коэффициента всегда находится в диапазоне от -1 до +1 (включая все промежуточные дробные значения):
- +1 – обе величины движутся полностью одинаково. Если первая растет, вторая тоже растет, если первая падает – вторая падает. Например, две одинаковые копии одного и того же показателя будут иметь корреляцию, близкую к единице.
- 0 – заметной зависимости нет. Изменение одного показателя никак не связано с изменением другого.
- -1 – обратная зависимость. Один показатель растет, второй при этом точно уменьшается.
Например, если мы сравниваем цену двух активов и получаем коэффициент корреляции 0,85, это говорит о сильной положительной связи. Но это не означает, что один актив обязательно вызывает движение другого. Корреляция показывает только совпадение поведения, а не причину.
Важно понимать: высокая корреляция не всегда означает прямую зависимость. Два показателя могут двигаться вместе из-за какого-то третьего фактора. Например, продажи мороженого и количество купающихся людей летом имеют высокую корреляцию, но одно не является причиной другого.
Для двух рядов значений можно определить одно число – коэффициент корреляции. Но если нужно проверить сразу много рядов между собой, удобнее построить таблицу, где на пересечении строк и столбцов находятся значения корреляции для каждой пары.
Такую таблицу называют корреляционной матрицей.
Напоследок, существует несколько математических способов расчета коэффициента корреляции. Самый популярный вариант и тот, который используется по умолчанию в Excel – коэффициент корреляции Пирсона.
Он хорошо подходит для оценки линейной зависимости между данными. Если же связь нелинейная или данные содержат много выбросов, иногда применяют другие методы, например корреляцию Спирмена.
Что-то я увлекся теорией, предлагаю уже переходить к практике.
Способ расчета 1 – Формула
Очень надеюсь, что вас не нужно учить писать формулы. Иначе просто закройте Excel
А теперь непосредственно попробуем посчитать коэффициент корреляции. Если вы пришли сюда именно за формулой, то вот она:
=КОРРЕЛ(массив1;массив2)
В некоторых версиях Excel также используется функция КОРРЕЛ с тем же принципом работы. В английской версии программы она называется CORREL.
Под массивами подразумеваем диапазоны данных, между которыми хотим найти зависимость. Например, можно сравнить два столбца с ценами, количеством продаж или любыми другими числовыми показателями.
Показываю на примере:
В данном примере у меня на небольшом промежутке времени получился коэффициент корреляции 0,29. Это означает слабую положительную связь между двумя рядами данных.
Главное преимущество этого способа – скорость. Если вам нужно быстро получить одно значение корреляции между двумя столбцами, обычная формула будет самым простым вариантом.
Способ 2 (корреляционная матрица) – Пакет анализа
В Excel есть еще встроенный «Пакет анализа», в котором находится инструмент для расчета корреляции. Лично я использую его не всегда, потому что для пары столбцов формула намного быстрее. Но если нужно сравнить сразу много показателей, этот вариант становится удобнее.
Например, если у вас есть данные по десяти товарам и нужно понять, какие продажи меняются похожим образом, вручную считать десятки формул будет неудобно.
- Сначала нужно активировать этот пакет. Переходим в «Файл – Параметры – Надстройки».
- В списке находим «Пакет анализа». Обычно он находится среди неактивных надстроек. Выделяем его, проверяем, что внизу выбрано «Надстройки Excel», и нажимаем кнопку «Перейти».
- Ставим галочку напротив «Пакет анализа» и нажимаем кнопку «ОК». После этого инструмент должен появиться среди доступных функций Excel.
- Теперь переходим на вкладку «Данные». Справа должна появиться кнопка «Анализ данных». Нажимаем на нее.
- В списке выбираем пункт «Корреляция».
- Задаем настройки. Во входной интервал передаем все столбцы, для которых хотим получить корреляцию. Результат можно вывести на новый лист или разместить рядом с исходными данными.
Если вы указали всего два столбца, получите примерно такой же результат, как и через функцию КОРРЕЛ. Но если передать сразу несколько столбцов, Excel создаст полноценную корреляционную матрицу:
Пользоваться можно, особенно если данных немного. Но лично мне не нравится, что здесь не всегда удобно работать с названиями столбцов и оформлением результата.
Для небольших таблиц это хороший вариант. Если же данных очень много, лучше использовать специализированные инструменты анализа данных или языки программирования.
На что обратить внимание при расчете корреляции
Есть несколько моментов, которые часто забывают начинающие пользователи:
- Корреляция работает только с числовыми данными. Текстовые значения Excel просто не сможет корректно сравнить.
- Количество данных должно быть достаточным. Если вы сравните всего несколько точек, результат может быть случайным.
- Сильная корреляция не означает гарантированный прогноз. Она показывает связь между данными, но не говорит, что будет дальше.
- Перед расчетом полезно проверить данные на пропуски и ошибки. Одна неправильная цифра иногда может сильно изменить результат.
На этом все. Если нужно быстро сравнить два ряда – используйте функцию КОРРЕЛ. Если нужно построить большую таблицу зависимостей – подойдет «Пакет анализа».
Видео по теме
Прикладываю видео по теме, где кроме основ есть те же понятия корреляции Спирмена и поля корреляции.
Спасибо за пример, все стало понятно теперь.
Насколько же Ексель полезная прога. А в школе я её не изучал. Теперь приходится, нужно по работе.
Очень простой и интересный пример. Долго не могла понять сначала, но потом вроде дошло
Ochen polezniy legko i ponyatno skazano spasibo bolshoye
Статья помогла очень, т.к. я пенсионерка, уже многое забыто, а тут ВКР пишу, всё подробно разъяснено, только подставляй свои данные. Но вся беда в том, что у меня виндовс -7, и я никак не могла найти папку “анализ данных”, помогают, используя Вашу статью Спасибо!