Меню

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

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

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

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

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

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

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

Прежде, чем перейти дальше, небольшая рекламная вставка:

Хочу порекомендовать вам сервис учёта инвестиций от партнёра Блога Вебинвестора — компании Intelinvest. На нём вы можете следить за своим портфелем через сайт или мобильное приложение, при этом предоставлять пароли для импорта сделок не нужно. Можно вести учёт любых активов: акций, облигаций, криптовалют, драгметаллов, форекс-инвестиций и т.д. Для пробы есть функциональная бесплатная версия. Если вы захотите сделать полноценную подписку, используйте промокод 1VYV9CMSTD, чтобы получить скидку 20% на первую оплату.

Спасибо за внимание, продолжаем!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Не хочу сильно заострять внимание на этой проблеме, так что если интересно поразбираться — нашел для вас видео, в котором найдете еще несколько примеров странных взаимосвязей и причины их появления:

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

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

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

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

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

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

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

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

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

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

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

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

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

С расчётом корреляции я как студент экономического ВУЗа познакомился еще на втором курсе. Тем не менее, долгое время недооценивал важность расчёта корреляции именно для подбора ПАММ-портфеля. 2018 год очень четко показал, что ПАММ-счета с похожими стратегиями в случае кризиса могут вести себя очень похоже.

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

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

Читайте также:  Острова ТОП 110 самых больших и самых населенных в мире

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

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

Пару слов о расчёте коэффициента корреляции для ПАММ-счетов. Достать сами данные относительно несложно, в Альпари прямо с сайта, для остальных площадок через сайт investflow.ru. Однако с ними нужно сделать небольшие преобразования.

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

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

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

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

Коэффициент корреляции в Excel и формула расчёта

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

  • Rxy — коэффициент корреляции;
  • COVxy — ковариация переменных X и Y;
  • σX, σY — стандартное отклонение переменных X и Y
  • X и Y с чертой — среднее значение Х и Y

Кстати, студентам на экзамене до сих пор компьютеров не выдают, хоть калькулятор можно и на том спасибо. Как вы понимаете, занятие все равно трудоёмкое 🙂

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

Чтобы далеко за примером не ходить, давайте рассчитаем корреляцию двух популярных ПАММ-счетов Lucky Pound и Hohla EUR. Они находятся на площадке компании Alpari, а значит мы можем скачать историю доходности прямо с сайта:

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

Теперь, как я уже писал выше, для ПАММ-счетов (и для многих других инвестиционных инструментов) надо рассчитать дневные доходности:

А дальше все просто — используется встроенная формула коэффицента корреляции в Excel =КОРРЕЛ():

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

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

Мне понравилось работать над этой темой и статья получилась неплохой. Если вы согласны с этим, сделайте доброе дело и поделитесь ссылочкой с друзьями и коллегами 🙂

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

До встречи и успешных вам инвестиций!

Автор: Александр Дюбченко (добавляйтесь в друзья Вконтакте и на Facebook). С 2016 года веду блог об инвестировании в Интернете, изучаю инвестиции в ПАММ-счета, акции, криптовалюты, драгоценные металлы, валютный рынок. Также разрабатываю вспомогательные инструменты для инвесторов на основе MS Excel. Всегда готов ответить на любые ваши вопросы.

Источник

Корреляционный анализ в Excel. Пример выполнения корреляционного анализа

Корреляционный анализ – это распространённый метод исследования, применяемый для определения уровня зависимости 1-й величины от 2-й. В табличном процессоре есть особый инструмент, который позволяет реализовать данный тип исследования.

Суть корреляционного анализа

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

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

Зависимость устанавливается тогда, когда начинается выявление коэффициента корреляции. Этот метод отличается от анализа регрессии, так как здесь только один показатель, рассчитываемый при помощи корреляции. Интервал изменяется от +1 до -1. Если она плюсовая, то повышение первой величины способствует повышению 2-й. Если минусовая, то повышение 1-й величины способствует понижению 2-й. Чем выше коэффициент, тем сильнее одна величина влияет на 2-ю.

Важно! При 0-м коэффициенте зависимости между величинами нет.

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

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

Способ 1: определение корреляции через Мастер функций

КОРРЕЛ – функция, позволяющая реализовать корреляционный анализ. Общий вид – КОРРЕЛ(массив1;массив2). Подробная инструкция:

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

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza1

  1. Открывается «Мастер функций». Здесь необходимо найти КОРРЕЛ, кликнуть на нее, затем на «ОК».

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza2

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

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza3

  1. После введения всех диапазонов кликаем на кнопку «ОК».

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

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza

4

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

Существует еще один метод определения корреляции. Здесь используется одна из функций, находящаяся в пакете анализа. Перед ее использованием нужно провести активацию инструмента. Подробная инструкция:

  1. Переходим в раздел «Файл».

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza5

  1. Открылось новое окошко, в котором нужно кликнуть на раздел «Параметры».
  2. Жмём на «Надстройки».
  3. Находим в нижней части элемент «Управление». Здесь необходимо выбрать из контекстного меню «Надстройки Excel» и кликнуть «ОК».

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza6

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

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza7

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

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza8

Вывелись итоговые показатели. Результат такой же, как и в первом методе – 0,97.

Читайте также:  Как принимать правильные решения усовершенствованная методика Квадрат Декарта

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

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

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

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza9

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

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza10

  1. Готово! Построилась матрица корреляции.

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza11

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

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

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

К примеру, у вас есть значения величин х и у.

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza12

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

  1. Выявим средние показатели величин при помощи функции СРЗНАЧ.

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza13

  1. Произведем расчет каждого х и хсредн, у и усредн при помощи оператора «-».

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza14

  1. Производим перемножение вычисленных разностей.

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza15

  1. Вычисляем сумму показателей в этом столбце. Числитель – найденный результат.

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza16

  1. Посчитаем знаменатели разницы х и х-средн, у и у-средн. Для этого произведем возведение в квадрат.

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza17

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

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza18

  1. Производим подсчет частного, используя значения знаменателя и числителя.

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza19 korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza20

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

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza21

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

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

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza

22

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza

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

23

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

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

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

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

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza

24

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

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza

25

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

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza

26

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

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

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza

27

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

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza

28

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

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

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

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza

29

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

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

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

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza

30

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

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza

31

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

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza

32

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

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza

33

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

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

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

Оценка статистической значимости коэффициента корреляции

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

korrelyacionnyj-analiz-v-excel-primer-vypolneniya-korrelyacionnogo-analiza

34

Заключение

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

Источник



2 способа корреляционного анализа в Microsoft Excel

Корреляция в Microsoft Excel

Корреляционный анализ – популярный метод статистического исследования, который используется для выявления степени зависимости одного показателя от другого. В Microsoft Excel имеется специальный инструмент, предназначенный для выполнения этого типа анализа. Давайте выясним, как пользоваться данной функцией.

Суть корреляционного анализа

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

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

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

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

Способ 1: определение корреляции через Мастер функций

Одним из способов, с помощью которого можно провести корреляционный анализ, является использование функции КОРРЕЛ. Сама функция имеет общий вид КОРРЕЛ(массив1;массив2).

    Выделяем ячейку, в которой должен выводиться результат расчета. Кликаем по кнопке «Вставить функцию», которая размещается слева от строки формул.

Переход в мастер функций для корреляции в Microsoft Excel

Функция КОРРЕЛ в Мастере функций в Microsoft Excel

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

Читайте также:  Аналогия оригинал копия воблеров от BearKing

В поле «Массив2» нужно внести координаты второго столбца. У нас это затраты на рекламу. Точно так же, как и в предыдущем случае, заносим данные в поле.

Аргументы функции КОРРЕЛ в Microsoft Excel

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

Результат функции КОРРЕЛ в Microsoft Excel

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

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

    Переходим во вкладку «Файл».

Переход во вкладку Файл в Microsoft Excel

В открывшемся окне перемещаемся в раздел «Параметры».

Переход в раздел Параметры в Microsoft Excel

Далее переходим в пункт «Надстройки».

Переход в надстройки в Microsoft Excel

В нижней части следующего окна в разделе «Управление» переставляем переключатель в позицию «Надстройки Excel», если он находится в другом положении. Жмем на кнопку «OK».

Переход в надстройки Excel в Microsoft Excel

В окне надстроек устанавливаем галочку около пункта «Пакет анализа». Жмем на кнопку «OK».

Включение пакета анализа в Microsoft Excel

После этого пакет анализа активирован. Переходим во вкладку «Данные». Как видим, тут на ленте появляется новый блок инструментов – «Анализ». Жмем на кнопку «Анализ данных», которая расположена в нем.

Переход в анализ данных в Microsoft Excel

Открывается список с различными вариантами анализа данных. Выбираем пункт «Корреляция». Кликаем по кнопке «OK».

Переход в Корреляцию в Microsoft Excel

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

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

В параметрах вывода по умолчанию установлен пункт «Новый рабочий лист», то есть, данные будут выводиться на другом листе. Можно изменить место, переставив переключатель. Это может быть текущий лист (тогда вы должны будете указать координаты ячеек вывода информации) или новая рабочая книга (файл).

Когда все настройки установлены, жмем на кнопку «OK».

Параметры для рассчета корреляции в Microsoft Excel

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

Расчет корреляции в Microsoft Excel

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

ЗакрытьМы рады, что смогли помочь Вам в решении проблемы.

Помимо этой статьи, на сайте еще 12004 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Отблагодарите автора, поделитесь статьей в социальных сетях.

ЗакрытьОпишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Источник

Пример выполнения корреляционного анализа в Excel

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

  • Назначение корреляционного анализа
  • Выполняем корреляционный анализ
    • Метод 1: применяем функцию КОРРЕЛ
    • Метод 2: используем “Пакет анализа”
  • Заключение

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

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

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

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

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

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

Таблица в Excel для выполнения корреляционного анализа

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

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

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

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

  1. Встаем в свободную ячейку таблицы, в которой планируем рассчитать коэффициент корреляции. Затем щелкаем по значку “fx (Вставить функцию)” слева от строки формул.Вставка функции в ячейку таблицы Эксель
  2. В открывшемся окне вставки функции выбираем категорию “Статистические” (или “Полный алфавитный перечень”), среди предложенных вариантов отмечаем “КОРРЕЛ” и щелкаем OK.Выбор оператора КОРРЕЛ для вставки в ячейку таблицы Excel

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

    Альтернативным способом выполнения корреляционного анализа является использование “Пакета анализа”, который предварительно нужно включить. Для этого:

    1. Заходим в меню “Файл”.Переход в меню Файл в Экселе
    2. В перечне слева выбираем пункт “Параметры”.Переход к параметрам Excel
    3. В появившемся окне кликаем по подразделу “Надстройки”. Затем в правой части окна в самом низу для параметра “Управление” выбираем “Надстройки Excel” и щелкаем “Перейти”.Переход к управлению надстройками в параметрах Excel
    4. В открывшемся окошке отмечаем “Пакет анализа” и подтверждаем действие нажатием кнопки OK.Включение надстройки Пакет анализа в Эксель

    Все готово, “Пакет анализа” активирован. Теперь можно перейти к выполнению нашей основной задачи:

    1. Нажимаем кнопку “Анализ данных”, которая находится во вкладке “Данные”.Применение анализа данных в Эксель
    2. Появится окно, в котором представлен перечень доступных вариантов анализа. Отмечаем “Корреляцию” и щелкаем OK.Выбор Корреляции для анализа данных в Excel
    3. На экране отобразится окно, в котором необходимо указать следующие параметры:
      • “Входной интервал”. Выделяем весь диапазон анализируемых ячеек (т.е. сразу оба столбца, а не по одному, как это было в описанном выше методе).
      • “Группирование”. На выбор предложено два варианта: по столбцам и строкам. В нашем случае подходит первый вариант, т.к. именно подобным образом расположены анализируемые данные в таблице. Если в выделенный диапазон включены заголовки, следует поставить галочку напротив пункта “Метки в первой строке”.
      • “Параметры вывода”. Можно выбрать вариант “Выходной интервал”, в этом случае результаты анализа будут вставлены на текущем листе (потребуется указать адрес ячейки, начиная с которой будут выведены итоги). Также предлагается вывод результатов на новом листе или в новой книге (данные будут вставлены в самом начале, т.е. начиная с ячейки A1). В качестве примера оставляем “Новый рабочий лист” (выбран по умолчанию).
      • Когда все готово, щелкаем OK.Настройка параметров корреляционного анализа в Excel
    4. Получаем тот же самый коэффициент корреляции, что и в первом методе. Это говорит о том, что в обоих случаях мы все сделали верно.Результат выполнения корреляционного анализа в Excel

    Заключение

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

    Источник