Корреляционный анализ в excel: 2 рабочих варианта

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

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

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

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

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

Отображенный показатель близок к 1. Результат:

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

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

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

=КОРРЕЛ(А3:А17;В3:В17).
=КОРРЕЛ(А3:А17;С3:С17).

Результаты:

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

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

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

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

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

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

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

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

Функция ПРЕДСКАЗ позволяет определить число просмотров в моменте, если было проведено, к примеру, двести пятьдесят репостов. Применяем: 0,7;ПРЕДСКАЗ(D7;B3:B8;A3:A8);»Величины не взаимосвязаны»)’ class=’formula’>. Получаем следующие результаты:

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

Данная функция имеет нижеприведенные особенности:

Не учитываются ячейки пустого типа.
Не учитываются ячейки, в которых находится информация типа Boolean и Text.
Двойное отрицание «—» применяется для учёта логических величин в виде чисел.
Количество ячеек в исследуемых массивах обязаны совпадать, иначе будет выведено сообщение #Н/Д.

Что такое корреляция простыми словами

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

Значение коэффициента может меняться от -1 до +1:

Если значение близко к единице или минус единице — значит два явления так или иначе сильно взаимосвязаны. Впрочем, причины этого не всегда очевидны — явление А может влиять на явление B, может быть наоборот. Нередко бывает, что существует явление C, которое приводит в движение А и В одновременно. В общем, природа корреляции — это уже второй вопрос, которым должны заниматься исследователи.

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

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

Движения графиков полностью повторяют друг друга, причем это как в случае простого добавления, так и с множителем.

При сильной отрицательной корреляции рост графика А приводит к падению графика B и наоборот. Вот так выглядит корреляция -1:

Движения графиков похожи на зеркальные отражения.

Коэффициент корреляции — удобный инструмент для анализа во многих сферах науки и жизни. Его легко рассчитать в Excel и применить, поэтому самая большая сложность в работе с ним — грамотно подобрать данные для расчёта. Основное правило — чем больше данных, тем лучше. Многие взаимосвязи проявляют себя лишь на длинной дистанции.

Также нужно следить за тем, чтобы найденные корреляции не были ложными.

Корреляционный анализ в EXCEL

Формула для вычислений

Функция EXCEL или инструмент Анализа данных

Оценка параметров модели парной регрессии

ЛИНЕЙН(изв_знач_у; зв_знач_х; константа; стат) Смысл аргументов функции изв_знач_у – диапазон значений у; изв_знач_х – диапазон значений х; константа – устанавливается на 0, если заранее известно, что свободный член равен 0 и на 1 в противном случае; стат– устанавливается на 0, если не нужен вывод дополнительных сведений регрессионного анализа и на 1 в противном случае.

Практическое занятие «Проверка адекватности модели».

Цель работы: Изучение t-критерия Стьюдента.

Чтобы определить насколько полученное уравнение регрессии значимо для всей совокупности, необходимо проверить:

• Определение значимости модели

• Установление наличия или отсутствия систематической ошибки.

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

Расчетные значения t -критерия сравнивают с табличным значением критерия, которое определяется при (n-k-1) степенях свободы и соответствующем уровне значимости α.

n – число уравнений,

k – число переменных,

α = 0,05 при доверительной вероятности 0,95 .

Формула для определения t-критерия Стьюдента:

где Sa0 и Sa1 – стандартные отклонения свободного члена и коэффициента регрессии.

Определяются по формулам:

=
,
=
.

Задание: Рассчитать t-критерий Стьюдента по данным в табл.3 и сделать выводы о значимости отдельных коэффициентов уравнения регрессии.

0,636263125

a0=

-109

ε 2

( x_i – х_ср. ) 2

3357

2425

2027

3135

2050

1886

2842

1683

1700

3991

2375

2431

2293

1167

1350

3340

1925

2017

3089

1042

1857

4372

2925

2673

3563

2200

2158

3219

1892

1940

3308

2008

1996

3724

2225

2261

3416

1983

2065

3022

2342

1814

3383

2458

2044

4267

2125

2606

Сумма

Расчет t-критерий Стьюдента можно также произвести с помощью Excel, используя стандартную функцию, приведенную в таблице 4.

Оценка параметров модели парной и множественной линейной регрессии.

Сервис / Анализ данных Для вычисления параметров уравнения регрессии следует воспользоваться инструментом Регрессия

Оценка значимости коэффициента парной корреляции с использованием t – критерия Стьюдента. Вычисленное по этой формуле значение t_набл сравнивается с критическим значением t-критерия, которое берется из таблицы значений t Стьюдента с учетом заданного уровня значимости и числа степеней свободы (n-2).

СТЬЮДРАСПОБР (вероятность; степени_свободы) Вероятность — вероятность, соответствующая двустороннему распределению Стьюдента. Степени_свободы — число степеней свободы, характеризующее распределение.

Сделать выводы о значимости коэффициентов уравнения регрессии.

Практическое занятие «Определение значимости модели по F – критерию Фишера»

Цель работы: Изучение F- критерия Фишера.

Для проверки значимости уравнения регрессии в целом используется F – критерий Фишера.

В случае парной линейной регрессии критерий определяется:

= (n-k-1) (6).

Если при заданном уровне значимости расчетное значение F – критерий Фишера с γ 1= k , γ 2 = n – k -1 степенями свободы больше табличного, то модель считается значимой

Задание: Используя данные предыдущей работы, рассчитать F- критерий Фишера и сделать выводы.

Для расчета следует воспользоваться инструментом Регрессия из пакета Сервис / Анализ данных и выбрать значение.

Расчет F-критерий Фишера можно также произвести с помощью Excel, используя стандартную функцию (см. табл.5)

Оценка параметров модели парной и множественной линейной регрессии.

Для вычисления параметров уравнения регрессии следует воспользоваться инструментом Регрессия
Проверка значимости модели регрессии с использованием F-критерий Фишера

FРАСПОБР(вероятность; степени_свободы1; степени_свободы2) Вероятность — это вероятность, связанная с F-распределением. Степени_свободы 1 — это числитель степеней свободы-n1= k. Степени_свободы 2 — это знаменатель степеней свободы-.n2 = (n – k – 1), где k – количество факторов, включенных в модель,

Дата добавления: 2019-07-15 ; просмотров: 110 ;

Использование MS EXCEL для расчета корреляции

В качестве примера возьмем 2 переменные Х и Y и, соответственно, выборку состоящую из нескольких пар значений (Х _i>;>i ). Для наглядности построим диаграмму рассеяния .

Примечание : Подробнее о построении диаграмм см. статью Основы построения диаграмм . В файле примера для построения диаграммы рассеяния использована диаграмма График , т.к. мы здесь отступили от требования случайности переменной Х (это упрощает генерацию различных типов взаимосвязей: построение трендов и заданный разброс). В случае реальных данных необходимо использовать диаграмму типа Точечная (см. ниже).

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

Примечание : В файле примера можно задать параметры линейного тренда (наклон, пересечение с осью Y) и степень разброса относительно этой линии тренда. Также можно настроить параметры квадратичной зависимости.

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

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

Как было сказано выше, для расчета коэффициента корреляции в MS EXCEL существует функций КОРРЕЛ() . Также можно воспользоваться аналогичной функцией PEARSON() , которая возвращает тот же результат.

Для того, чтобы удостовериться, что вычисления корреляции производятся функцией КОРРЕЛ() по вышеуказанным формулам, в файле примера приведено вычисление корреляции с помощью более подробных формул:

= КОВАРИАЦИЯ.Г(B28:B88;D28:D88)/СТАНДОТКЛОН.Г(B28:B88)/СТАНДОТКЛОН.Г(D28:D88)

= КОВАРИАЦИЯ.В(B28:B88;D28:D88)/СТАНДОТКЛОН.В(B28:B88)/СТАНДОТКЛОН.В(D28:D88)

Примечание : Квадрат коэффициента корреляции r равен коэффициенту детерминации R2, который вычисляется при построении линии регрессии с помощью функции КВПИРСОН() . Значение R2 также можно вывести на диаграмме рассеяния , построив линейный тренд с помощью стандартного функционала MS EXCEL (выделите диаграмму, выберите вкладку Макет , затем в группе Анализ нажмите кнопку Линия тренда и выберите Линейное приближение ). Подробнее о построении линии тренда см., например, в статье о методе наименьших квадратов .

Линейный коэффициент корреляции Пирсона

Обнаружение взаимосвязей между явлениями – одна из главных задач статистического анализа. На то есть две причины. Первая. Если известно, что один процесс зависит от другого, то на первый можно оказывать влияние через второй. Вторая. Даже если причинно-следственная связь отсутствует, то по изменению одного показателя можно предсказать изменение другого.

Взаимосвязь двух переменных проявляется в совместной вариации: при изменении одного показателя имеет место тенденция изменения другого. Такая взаимосвязь называется корреляцией, а раздел статистики, который занимается взаимосвязями – корреляционный анализ.

Корреляция – это, простыми словами, взаимосвязанное изменение показателей. Она характеризуется направлением, формой и теснотой. Ниже представлены примеры корреляционной связи.

Далее будет рассматриваться только линейная корреляция. На диаграмме рассеяния (график корреляции) изображена взаимосвязь двух переменных X и Y. Пунктиром показаны средние.

При положительном отклонении X от своей средней, Y также в большинстве случаев отклоняется в положительную сторону от своей средней. Для X меньше среднего, Y, как правило, тоже ниже среднего. Это прямая или положительная корреляция. Бывает обратная или отрицательная корреляция, когда положительное отклонение от средней X ассоциируется с отрицательным отклонением от средней Y или наоборот.

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

Крайне важная характеристика корреляции – теснота. Чем теснее взаимосвязь, тем ближе к прямой точки на диаграмме. Как же ее измерить?

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

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

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

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

Показатель имеет полное название линейный коэффициент корреляции Пирсона или просто коэффициент корреляции.

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

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

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

Линейная функция является моделью взаимосвязи между X иY и показывает ожидаемое значение Y при заданном X. Коэффициент детерминации – это соотношение дисперсии ожидаемых Y (точек на прямой линии) к общей дисперсии Y, или доля объясненной вариации Y. При r = 0,1 r 2 = 0,01 или 1%, при r = 0,5 r 2 = 0,25 или 25%.

Как найти корреляцию в Excel

Microsoft Excel — утилита, которая широко используется во многих компаниях и на предприятиях. Реалии таковы, что практически любой работник должен в той или иной мере владеть Экселем, так как эта программа применяется для решения очень широкого спектра задач. Работая с таблицами, нередко приходится определять, связаны ли между собой определённые переменные. Для этого используется так называемая корреляция. В этой статье мы подробно рассмотрим, как рассчитать коэффициент корреляции в Excel. Давайте разбираться. Поехали!

Рассчитываем корреляцию в Excel

Начнём с того, что такое коэффициент корреляции вообще. Он показывает степень взаимосвязи между двумя элементами и всегда находится в диапазоне от -1 (сильная обратная взаимосвязь) до 1 (сильная прямая взаимосвязь). Если коэффициент равен 0, это говорит о том, что взаимосвязь между значениями отсутствует.

Теперь, разобравшись с теорией, перейдём к практике. Чтобы найти взаимосвязь между переменными и у, воспользуйтесь встроенной функцией Microsoft Excel «КОРРЕЛ». Для этого нажмите на кнопку мастера функций (она расположена рядом с полем для формул). В открывшемся окне выберите из списка функций «КОРРЕЛ». После этого задайте диапазон в полях «Массив1» и «Массив2». Например, для «Массив1» выделите значения у, а для «Массив2» выделите значения х. В итоге вы получите рассчитанный программой коэффициент корреляции.

Коэффициент корреляции рассчитывается программой

Следующий способ будет актуален для студентов, от которых требуют найти зависимость по заданной формуле. Прежде всего, нужно знать средние значения переменных x и y. Для этого выделите значения переменной и воспользуйтесь функцией «СРЗНАЧ». Далее необходимо вычислить разницу между каждым x и x_ср, и y_ср. В выбранных ячейках напишите формулы x-x, y-. Не забудьте закрепить ячейки со средними значениями. Затем растяните формулу вниз, чтобы она применилась и к остальным числам.

Теперь, когда есть все необходимые данные, можно посчитать корреляцию. Перемножьте полученные разности таким образом: (x-x_ср) * (y-y_ср). После того как вы получите результат для каждой из переменных, просуммируйте полученные числа при помощи функции автосуммы. Таким образом рассчитывается числитель.

Теперь перейдём к знаменателю. Посчитанные разности нужно возвести в квадрат. Для этого в отдельной колонке введите формулы: (x-x_ср) 2 и (y-y_ср) 2 . Затем растяните формулы на весь диапазон. После, при помощи кнопки «Автосумма», найдите сумму по всем колонкам (для x и для y). Осталось перемножить найденные суммы и извлечь из них квадратный корень. Последний шаг — поделите числитель на знаменатель. Полученный результат и будет искомым коэффициентом корреляции.

Вычисляем коэффициент корреляции.

На практике, для вычисления коэффициента корреляции чаще используется формула (1.4) т.к. она требует меньше вычислений. Однако если предварительно была вычислена ковариация cov(X,Y) , то выгоднее использовать формулу (1.1), т.к. кроме собственно значения ковариации можно воспользоваться и результатами промежуточных вычислений.

1.1 Вычислим коэффициент корреляции по формуле (1.4) , для этого вычислим значения x k 2 , y k 2 и x k y k и занесем их в таблицу 1.

x 1 + x 2 + … + x 26 = 25.20000 + 26.40000 + . + 25.80000 = 669.500000

1.3.1. Сложим последовательно все элементы y k

y 1 + y 2 + … + y 26 = 30.80000 + 29.40000 + . + 30.80000 = 793.000000

1.3.2. Разделим полученную сумму на число элементов выборки

1.4.1. Сложим последовательно все элементы 6-го столбца таблицы 1

1.4.2. Разделим полученную сумму на число элементов

1.5.1. Сложим последовательно все элементы 4-го столбца таблицы 1

1.5.2. Разделим полученную сумму на число элементов

1.5.3. Вычтем из последнего числа квадрат величины M x получим значение для S x 2

S x 2 = 663.72731 — 25.75000 2 = 663.72731 — 663.06250 = 0.66481

1.6.1. Сложим последовательно все элементы 5-го столбца таблицы 1

1.6.2. Разделим полученную сумму на число элементов

1.6.3. Вычтем из последнего числа квадрат величины M y получим значение для S y 2

S y 2 = 930.45538 — 30.50000 2 = 930.45538 — 930.25000 = 0.20538

1.7. Вычислим произведение величин S x 2 и S y 2 .

1.8. Извлечем и последнего числа квадратный корень, получим значение S x S y .

1.9. Вычислим значение коэффициента корреляции по формуле (1.4.) .

R = (785.10885 — 25.75000 30.50000) / 0.36951 = (785.10885 — 785.37500) / 0.36951 = -0.72028

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

«Массив1»

Как рассчитать коэффициент корреляции

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

Способ 1. Определение корреляции с помощью Мастера Функций

Функция КОРРЕЛ – один из самых простых методов, как можно реализовать поставленную задачу. В своем общем виде этот оператор имеет следующий вид: КОРРЕЛ(массив1;массив2). Как же ее ввести? Для этого нужно осуществлять следующие действия:

С помощью левой кнопки мыши выделяем ту ячейку, в которой будет находиться получившийся коэффициент корреляции. После этого находим слева от строки формул кнопку fx, которая откроет инструмент ввода функций.
Далее выбираем категорию «Полный алфавитный перечень», в котором ищем функцию КОРРЕЛ. Как видно из названия категории, все названия функций располагаются в алфавитном порядке.
Далее открывается окно ввода параметров функции. У нас два основных аргумента, каждый из которых являет собой массив данных, которые сравниваются между собой. В поле «Массив 1» указываем координаты первого диапазона, а в поле «Массив 2» – адрес второго диапазона. Для ввода данных массива, используемого для расчета, достаточно выделить нажать левой кнопкой мыши по соответствующему полю и выделить правильный диапазон.
После того, как мы введем данные в аргументы, нажимаем кнопку «ОК», чем подтверждаем совершенные действия.

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

Способ 2. Вычисление корреляции с помощью пакета анализа

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

Нажимаем на кнопку «Файл», которая находится в левом верхнем углу сразу возле вкладки «Главная».
После этого открываем раздел с настройками.
В меню слева переходим в предпоследний пункт, озаглавленный, как «Надстройки». Делаем левый клик по соответствующей надписи.
Открывается окно управления надстройками. Нам нужно переключить поле ввода, находящееся внизу, на пункт «Надстройки Excel» и нажать на «Перейти». Если это поле уже находится в таком положении, то не выполняем никаких изменений.
Затем включаем пакет анализа в настройках. Для этого ставим соответствующую галочку и нажимаем на кнопку «ОК».

Все, теперь наша надстройка включена. Теперь мы во вкладке «Данные» можем увидеть кнопку «Анализ данных». Если она появилась, то мы все сделали правильно. Нажимаем на нее.

Появляется перечень с выбором разных способов анализа информации. Нам следует выбрать пункт «Корреляция» и нажать на «ОК».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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