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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Видео по теме

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

Автор статьи
Ботан 1047 статей
Мастер занудных текстов и технического слога. Мистер классные очки и зачётная бабочка. Дипломированный Wi-Fi специалист.
WiFiGid
Комментарии: 5
  1. Аноним

    Спасибо за пример, все стало понятно теперь.

  2. Вадим

    Насколько же Ексель полезная прога. А в школе я её не изучал. Теперь приходится, нужно по работе.

  3. Алена

    Очень простой и интересный пример. Долго не могла понять сначала, но потом вроде дошло

  4. Аноним

    Ochen polezniy legko i ponyatno skazano spasibo bolshoye :smile: :smile: :smile: :smile: :smile:

  5. Аноним

    Статья помогла очень, т.к. я пенсионерка, уже многое забыто, а тут ВКР пишу, всё подробно разъяснено, только подставляй свои данные. Но вся беда в том, что у меня виндовс -7, и я никак не могла найти папку “анализ данных”, помогают, используя Вашу статью Спасибо!

Добавить комментарий
После отправки комментарий может не отображаться - это нормально. Сразу же после модерации он будет опубликован. Если Вы хотите быстро узнать о получении ответа, рекомендуем оставить свой e-mail (это необязательно). E-mail используется исключительно для Вашего оповещения, мы не занимаемся спамом.

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

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