Корреляционный анализ в Excel: как посчитать коэффициент корреляции

Друзья, приветствую вас на WiFiGid! Буквально на днях разрабатывал модель для одной рыночно-нейтральной торговой стратегии, где как раз и нужно было рассчитывать коэффициенты корреляции (на самом деле корреляционную матрицу). Лично я сделал это на «питоне» – все равно потом ее как-то же надо обрабатывать, ведь миллионы ячеек глазом не просмотришь. Но на этапе предварительного анализа кому-то может быть проще использовать именно Excel. Поэтому в этой статье я и покажу вам основные варианты расчета коэффициента корреляции в Excel.

А если вдруг останутся какие-то вопросы или дополнения – добро пожаловать в комментарии. По мере возможности буду отвечать.

И как дополнение, конечно, можно ввести стандартную формулу любой корреляции в Excel, и на ее основе рассчитать коэффициент. Но в этой статье предлагаю все-таки остановиться на уже встроенных способах в Excel.

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

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

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

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

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

Что-то я увлекся теорией, предлагаю уже переходить к практике.

Способ расчета 1 – Формула

Очень надеюсь, что вас не нужно учить писать формулы. Иначе просто закройте Excel

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

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

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

В данном примере у меня на очень малом промежутке времени получился коэффициент корреляции 0,29. Но главное, что способ работает. Так что если нужно получить быстро значение корреляции – круче формулы ничего и не придумаешь.

Способ 2 (корреляционная матрица) – Пакет анализа

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

1. Сначала нужно активировать этот пакет. Переходим в «Файл – Параметры – Надстройки».
2. В списке находим «Пакет анализа» (обычно он в разделе неактивных надстроек), выделяем его, убеждаемся что внизу установлено «Надстройки Excel» и нажимаем по кнопке «Перейти».

3. Ставим галочку напротив «Пакет анализа» и нажимаем кнопку «Ок». Это должно активировать пакет.

4. Теперь переходим на вкладку «Данные». Именно тут справа должна добавить кнопка «Анализ данных». Щелкаем по ней.

5. В списке выбираем «Корреляцию».

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

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

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

Видео по теме

Прикладываю видео по теме, где кроме основ есть те же понятия корреляции Спирмена и поля корреляции.