Как сделать корреляционный анализ зависимости данных в Excel

Назначение корреляционного анализа

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

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

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

Определение и вычисление множественного коэффициента корреляции в MS Excel

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

Подробные инструкции:

1. В разделе «Данные» находим уже знакомый блок «Анализ» и нажимаем «Анализ данных».

1. В появившемся окне кликните по пункту «Корреляция» и нажмите «ОК».
2. В строке «Входной интервал» вводим интервал по трем и более столбцам исходной таблицы. Область можно ввести вручную или просто выделить ее ЛКМ и она автоматически появится на нужной строке. В «Группировке» выберите правильный метод группировки. Параметр «Вывод» указывает место, куда должны выводиться результаты корреляции. Нажмите «ОК».

1. Прозрачный! Построена корреляционная матрица.

Ложные корреляции

Дело в том, что с помощью коэффициента корреляции можно проверить на наличие связей все явления, которые можно выразить в числовом выражении. То есть практически все – например, количество свадеб в Нью-Йорке и объем импорта нефти в США из Норвегии:

tylervigen.com — если вы знаете английский, вы можете найти его на сайте
еще более странные связи

Корреляция составила 86%! Действительно ли свадьбы влияют на экспорт нефти? Конечно нет – такая зависимость совершенно случайна. Именно так выглядит ловушка ложной корреляции: она может выявить взаимосвязь там, где ее на самом деле нет.

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

Как я уже упоминал, существует несколько способов расчета коэффициента корреляции в Excel.

Использование формулы CORREL

КОРРЕЛ — статистическая функция, представленная в Excel 2007.

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

Ниже приведена формула, которая сделает это:
= КОРРЕЛЬ (B2:B12; C2:C12)

Приведенная выше функция КОРРЕЛ принимает два аргумента — массив точек данных высоты и массив точек данных веса.

И это все!

Когда вы нажмете клавишу ВВОД, Excel выполнит все вычисления на серверной стороне и предоставит вам один коэффициент корреляции Пирсона.

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

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

Однако если у вас есть несколько рядов и вы хотите узнать коэффициент корреляции всех этих рядов, вы также можете рассмотреть возможность использования набора инструментов для анализа данных в Excel (обсуждается далее).

Использование пакета инструментов анализа данных

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

Полезно: Как задать область печати на листах Excel

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

Включение пакета инструментов анализа данных

Ниже приведены шаги по включению набора инструментов для анализа данных в Excel:

1. Перейдите на вкладку «Файл.
2. Нажмите «Параметры
3. В открывшемся диалоговом окне «Параметры Excel» выберите параметр «Надстройка» на боковой панели.
4. В раскрывающемся списке «Управление» выберите «Надстройки Excel.
5. Нажмите «Перейти». Откроется диалоговое окно «Дополнения.
6. Установите флажок «Пакет инструментов анализа
7. Нажмите ОК

Вышеупомянутые шаги добавят новую группу на вкладку «Данные» ленты Excel под названием «Анализ». В этой группе у вас будет возможность проанализировать данные

Читайте также: Как вставить файл PDF в Autocad

Коэффициент корреляции и ПАММ-счета

Будучи студентом экономического университета, я познакомился с корреляционными расчетами на втором курсе. Однако я долгое время недооценивал важность расчета корреляции именно для выбора ПАММ-портфеля. 2018 год очень наглядно показал, что ПАММ-счета со схожими стратегиями могут вести себя очень похоже в случае кризиса.

Так получилось, что с середины года дала сбой не только одна стратегия управляющего, но и большинство торговых систем, связанных с активными движениями валютной пары EUR/USD:

Рынок был по-своему неблагоприятен для каждого менеджера, но присутствие всех в портфеле привело к большому падению. Совпадения? Не совсем, ведь это были ПАММ-счета со схожими элементами торговых стратегий. Без опыта торговли на Форексе может быть сложно понять, как это работает, но таблица корреляции показывает степень взаимосвязи следующим образом:

Ранее мы рассматривали корреляции до +1, но как видите на практике, даже совпадение в районе 20-30% уже указывает на определенное сходство ПАММ-счетов и, как следствие, результатов торговли.

Чтобы снизить вероятность повторения ситуации, как в 2018 году, думаю, стоит выбирать для своего портфеля ПАММ-счета с низкой кросс-корреляцией. По сути, нам нужны уникальные стратегии с разными подходами и разными валютными парами для торговли. На практике, конечно, сложнее выбрать прибыльные счета с уникальными стратегиями, но если углубиться в оценку ПАММ-счетов, то все возможно. Кроме того, низкая взаимная корреляция снижает требования к диверсификации, достаточно 5-6 счетов.

Несколько слов о расчете коэффициента корреляции для ПАММ-счетов. Сами данные относительно легко получить: в Альпари прямо с сайта, для других платформ через сайтinvestflow. Однако им необходимо внести некоторые незначительные изменения.

Данные о доходности ПАММ изначально хранятся в формате накопленной доходности, который нам не подходит. Корреляция между стандартными графиками доходности двух прибыльных ПАММ-счетов всегда будет очень высокой, просто потому, что все они перемещаются в правый верхний угол:

Все аккаунты имеют положительную корреляцию 0,5 и выше за редким исключением, поэтому мы ничего не понимаем. Настоящее сходство между стратегиями ПАММ-счетов можно увидеть только в ежедневной доходности. Рассчитать их не представляет особой сложности, если знать необходимые формулы рентабельности. Если прибыль или убыток двух ПАММ-счетов совпадают по дням и процентам, велика вероятность того, что их стратегии имеют общие элементы – и коэффициент корреляции покажет нам это:

Как видите, некоторые корреляции стали нулевыми, а другие остались на высоком уровне. Теперь мы видим, какие ПАММ-счета действительно похожи друг на друга, а какие не имеют ничего общего.

Напоследок разберемся, что делать и как посчитать корреляцию, если понадобится.

Корреляция и диверсификация

Как знание корреляции активов может помочь вам лучше инвестировать свои деньги? Я думаю, вы все хорошо знаете золотое правило инвесторов – не кладите все яйца в одну корзину. Речь, естественно, идет о диверсификации, которая неразрывно связана с понятием корреляции. Это понятно даже из названия – английское diversify означает «разнообразить», а как коэффициент корреляции показывает лишь сходство или различие между двумя явлениями.

Другими словами, инвестировать в высококоррелированные финансовые инструменты – не лучшая идея. Почему? Все просто – подобные активы плохо диверсифицированы. Вот пример портфеля из двух активов с корреляцией +1:

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

Портфель дает небольшой выигрыш в снижении риска — но только по сравнению с более доходным Активом 1. А так, выгоды по сути никакой, нам лучше просто вложить все деньги в Актив 1 и не волноваться.

Вот пример портфеля из двух активов с корреляцией, близкой к 0:

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

Мы видим заметное снижение стандартного отклонения, а это значит, что портфель будет менее волатильным и будет расти более равномерно. Также мы видим небольшое снижение максимальных выводов, особенно по сравнению с активом 1. Инвестиционные инструменты без корреляции довольно распространены и имеет смысл формировать из них портфель.

Однако это не предел. Наиболее эффективный инвестиционный портфель можно составить, используя активы с корреляцией -1:

Уже известное «зеркало» позволяет свести показатели риска портфеля к минимуму:

Несмотря на то, что каждый актив имеет определенный риск, портфель оказался практически безрисковым. Какая-то магия, да? Обидно, но на практике этого не происходит, иначе было бы слишком легко инвестировать.

Коэффициент парной корреляции в Excel

Давайте рассмотрим, как правильно рассчитать коэффициент парной корреляции в таблице Excel.

Расчет коэффициента парной корреляции в Excel

Например, у вас есть значения x и y.

X — зависимая переменная, а y — независимая переменная. Необходимо найти направление и силу связи между этими показателями. Пошаговые инструкции:

1. Определим средние значения с помощью функции СРЗНАЧ.

1. Давайте вычислим каждый x и x-mean, y и среднее значение, используя оператор «-».

1. Умножаем рассчитанные разницы.

1. Рассчитаем сумму показателей в этом столбце. Счетчик — результат найден.

1. Давайте вычислим знаменатели разности x и x-mean, y и y-mean. Для этого возведем его в квадрат.

1. С помощью функции АВТОСУММ находим показатели в полученных столбцах. Делаем умножение. Используя функцию КОРЕНЬ, возводим результат в квадрат.

1. Вычисляем частное, используя значения знаменателя и числителя.

19

1. КОРРЕЛ — интегрированная функция, позволяющая избежать сложных вычислений. Заходим в «Мастер функций», выбираем КОРРЕЛ и задаем массивы индикаторами x и y. Строим график, на котором показаны полученные значения.

Матрица парных коэффициентов корреляции в Excel

Давайте посмотрим, как вычислить коэффициенты составных матриц. Например, это массив из четырех переменных.

Пошаговые инструкции:

1. Перейдите в раздел «Анализ данных», расположенный в блоке «Анализ» вкладки «Данные». В появившемся списке выберите «Корреляция».
2. Выставляем все необходимые настройки. «Входной интервал» — это интервал всех четырех столбцов. «Интервал вывода» — это то место, где мы хотим отображать результаты. Нажмите на кнопку «ОК».
3. В выбранном месте была построена корреляционная матрица. Каждое пересечение строки и столбца представляет собой коэффициент корреляции. Число 1 отображается, когда координаты совпадают.

Выполняем корреляционный анализ

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

Здесь вы можете найти данные о среднесуточной температуре и средней влажности по месяцам года. Наша задача — выяснить, есть ли связь между этими параметрами и если да, то насколько сильная.

Метод 1: применяем функцию КОРРЕЛ

В Excel есть специальная функция, позволяющая проводить корреляционный анализ – КОРРЕЛ. Синтаксис следующий:

КОРРЕЛ(матрица1; матрица2).

Порядок работы с данным инструментом следующий:

1. Переходим к пустой ячейке таблицы, где планируем рассчитать коэффициент корреляции. Затем щелкните значок «fx (Вставить функцию)» слева от строки формул.
2. В открывшемся окне ввода функции выберите вкладку «Статистика» (или «Полный алфавитный список»), из предложенных вариантов выберите «КОРРЕЛЬ» и нажмите «ОК.
3. На экране появится окно аргументов функции, в котором курсор будет помещен в первое поле рядом с «Массив 1». Здесь мы указываем координаты ячеек в первом столбце (без заголовка таблицы), данные которых необходимо проанализировать (в нашем случае B2:B13). Сделать это можно вручную, введя нужные символы с помощью клавиатуры. Вы также можете выделить нужную область прямо в самой таблице, зажав левую кнопку мыши. Затем переходим ко второму аргументу «Массив 2», щелкнув соответствующее поле или нажав клавишу Tab. Здесь мы указываем координаты диапазона ячеек во втором анализируемом столбце (в нашей таблице это C2:C13). Нажмите ОК, когда будете готовы.
4. Получаем коэффициент корреляции в ячейке с функцией. Значение «-0,63» указывает на обратную связь между анализируемыми данными от умеренной до сильной.

Функция ПИРСОН пошаговая инструкция

Коэффициент корреляции является наиболее подходящим показателем готовности количественных характеристик.

Задача: Определить коэффициент линейной корреляции Пирсона.

Пример решения:

1. В таблице приведены данные по группе курильщиков. Первая матрица x представляет возраст курильщика, вторая матрица y представляет количество сигарет, выкуриваемых в день.
2. Выберите ячейку B4, в которой необходимо вычислить результат, и нажмите кнопку мастера для функции fx (SHIFT+F3).
3. В группе Статистика выберите функцию ПИРСОН.
4. Выберем Массив 1 – возраст курильщика, а затем Массив 2 – количество выкуриваемых сигарет в день
5. Нажмите «ОК» и посмотрите тест нормального распределения Пирсона в ячейке B4.

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

Функция КОРРЕЛ для определения взаимосвязи и корреляции в Excel

КОРРЕЛ — это функция, используемая для расчета коэффициента корреляции между двумя массивами. Давайте рассмотрим четыре примера всех свойств этой функции.

Примеры использования функции КОРРЕЛ в Excel

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

Алгоритм расчета выглядит следующим образом:

Отображаемое значение близко к 1. Результат:

Определение коэффициента корреляции влияния действий на результат

Второй пример. Два заявителя обратились в два разных агентства за помощью в проведении рекламной кампании за пятнадцать дней. Каждый день проводился социальный опрос для определения уровня поддержки каждого кандидата. Любой респондент мог выбрать одного из двух кандидатов или выступить против всех. Необходимо определить, насколько каждая рекламная кампания повлияла на степень поддержки соискателей, и какая компания более эффективна.

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

• =КОРРЕЛ(A3:A17;B3:B17).
• =КОРРЕЛ(A3:A17;C3:C17).

Полученные результаты:

Из полученных результатов становится ясно, что степень поддержки 1 заявителя увеличивалась с каждым днем ​​рекламной кампании, поэтому коэффициент корреляции приближается к 1. При запуске рекламы у второго заявителя было большое количество трастов, и была положительная динамика в течение 5 дней. Затем степень уверенности упала и к пятнадцатому дню упала ниже первоначальных показателей. Низкие оценки указывают на то, что кампании оказали негативное влияние на поддержку. Не забывайте, что на показатели могут влиять и другие сопутствующие факторы, не учтенные в табличной форме.

Анализ популярности контента по корреляции просмотров и репостов видео

Третий пример. Человек использует социальные сети для продвижения собственных видеороликов на видеохостинге YouTube. Он отмечает, что существует определенная корреляция между количеством репостов в социальных сетях и количеством просмотров на канале. Можно ли предсказать будущие показатели с помощью инструментов электронных таблиц? Необходимо выявить целесообразность использования уравнения линейной регрессии для прогнозирования количества просмотров видео в зависимости от количества репостов. Таблица значений:

Теперь необходимо определить наличие связи между 2-мя показателями по формуле ниже:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;»Сильная прямая зависимость»;»Сильная обратная зависимость»);»Слабая зависимость или ее отсутствие»)’ class=’formula’>

Если результирующий коэффициент выше 0,7, целесообразнее использовать функцию линейной регрессии. В рассматриваемом примере делаем:

Теперь создадим график:

Используем это уравнение для определения количества показов при 200, 500 и 1000 репостах: =9,2937*D4-206,12. Мы получаем следующие результаты:

Функция ПРОГНОЗ позволяет определить количество просмотров за раз, если было сделано, например, двести пятьдесят репостов. Мы используем: 0.7;PREDICTION(D7;B3:B8;A3:A8);»Суммы не связаны»)’ class=’formula’>. Мы получаем следующие результаты:

Особенности использования функции КОРРЕЛ в Excel

Эта функция имеет следующие особенности:

1. Пустые ячейки не учитываются.
2. Ячейки, содержащие логическую и текстовую информацию, не учитываются.
3. Двойное отрицание «—» используется для учета логических величин в виде чисел.
4. Количество ячеек в изучаемых массивах должно совпадать, иначе будет выведено сообщение #Н/Д.