Как рассчитать коэффициент корреляции в Excel (2 простых способа)

Установка взаимосвязи между переменными. Как посчитать корреляцию в Экселе? Как в Excel сделать корреляционный анализ?

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

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

Таблица данных:

Пример 1.

Формула для расчета:

КОРРЕЛ.

Описание аргументов:

  • B3:B13 – диапазон ячеек, в которых хранятся данные о среднем курсе доллара;
  • C3:C13 – диапазон ячеек со значениями средней зарплаты.

Результат расчетов:

взаимосвязь валюты и зарплаты.

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

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

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

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

Использование корреляции

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

Рассчитать корреляцию можно вручную, выполняя несложные арифметические действия. Однако процесс вычисления оказывается очень трудоемким, если набор данных велик. Особенность метода в том, что он требует сбора большого количества исходных данных, чтобы наиболее точно отобразить, есть ли связь между признаками. Поэтому серьезное использование корреляционного анализа невозможно без применения вычислительной техники. Одной из наиболее популярных и доступных программ для решения этой задачи является Microsoft Office Excel.kak-vypolnyaetsya-korrelyaciya-v-excel-101

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

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

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

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

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

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

↑ К СОДЕРЖАНИЮ ↑

Примеры использования

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

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

Корреляция в excel 1

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

Корреляция в excel 2

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

Пример 2.

Есть показатели продаж мебели за квартал, а также изменение цены на товар за тот же период времени.

Корреляция в excel 3

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

Пример 3.

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

Корреляция в excel 4

Полученный результат говорит о слабой связи этих категорий.

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

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

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

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

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

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

В списке, который представлен в окне Мастера функций, ищем и выделяем функцию КОРРЕЛ. Жмем на кнопку «OK».

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

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

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

Жмем на кнопку «OK».

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

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

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

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

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

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

Переход во вкладку Файл в 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

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

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

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

Помогла ли вам эта статья?

ДА НЕТ

Как выполнить корреляцию в Excel?

Самым трудоемким этапом определения корреляции является набор массива данных. Сравниваемые данные располагаются обычно в двух колонках или строчках. Таблицу следует делать без пропусков в ячейках. Современные версии Excel (с 2007 и младше) не требуют установок дополнительных настроек для статистических расчетов; необходимые манипуляции можно сделать в разделе формул:

  1. Выбрать пустую ячейку, в которую будет выведен результат расчетов.
  2. Нажать в главном меню Excel пункт «Формулы».
  3. Среди кнопок, сгруппированных в «Библиотеку функций», выбрать «Другие функции».
  4. В выпадающих списках выбрать функцию расчета корреляции (Статистические — КОРРЕЛ).
  5. В Excel откроется панель «Аргументы функции». «Массив 1» и «Массив 2» — это диапазоны сравниваемых данных. Для автоматического заполнения этих полей можно просто выделить нужные ячейки таблицы.
  6. Нажать «ОК», закрыв окно аргументов функции. В ячейке появится подсчитанный коэффициент корреляции.

Корреляция может быть прямая (если коэффициент больше нуля) и обратная (от -1 до 0).

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

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

Если коэффициент показывает среднюю или сильную взаимосвязь (от ±0,5 до ±0,99), следует помнить, что это лишь статистическая взаимосвязь, которая вовсе не гарантирует влияние одного параметра на другой. Также нельзя исключать ситуации, что оба параметра независимы друг от друга, но на них воздействует какой-нибудь третий неучтенный фактор. Excel помогает моментально вычислить коэффициент корреляции, но обычно только количественных методов недостаточно для установления причинно-следственных связей в соотносимых выборках.kak-vypolnyaetsya-korrelyaciya-v-excel-102

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

Оставить комментарий ВКонтакте

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

Пример 3. Владелец канала YouTube использует социальную сеть для рекламы своих роликов. Он заметил, что между числом просмотров и количеством репостов в социальной сети существует некоторая взаимосвязь. Можно ли спрогнозировать виральность контента канала в Excel? Определить целесообразность использования уравнения линейной регрессии для предсказания количества просмотров роликов в зависимости от числа репостов.

Исходные данные:

Пример 3.

Определим наличие взаимосвязи между двумя параметрами по формуле:

Если модуль коэффициента корреляции больше 0,7, считается рациональным использование функции линейной регрессии (y=ax+b) для описания связи между двумя величинами. В данном случае:

взаимосвязи.

Построим график зависимости числа просмотров от количества репостов, отобразим линию тренда и ее уравнение:

график зависимости.

Используем данное уравнение для определения количества просмотров при 200, 500 и 1000 репостов:

=9,2937*D4-206,12

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

уравнение.

Аналогичное уравнение использует функция ПРЕДСКАЗ. То есть, чтобы найти количество просмотров в случае, если было сделано, например, 250 репостов, можно использовать формулу:

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

ПРЕДСКАЗ.

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

Пример

В следующем примере возвращается коэффициент корреляции двух наборов данных в столбцах A и B.

Функция КОРРЕЛ возвращает коэффициент корреляции двух наборов данных в столбце A & B с =КОРРЕЛ(A1:A6;B2:B6). Результат — 0,997054486.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Нужна дополнительная помощь?

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

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

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

Этапы и виды

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

  • Определяют объясняющие и зависимые переменные.
  • Собирают статистическую информацию отдельно для каждого компонента, участвующего в анализе.
  • Формулируют гипотезу, допускающую, какой будет связь: линейной, множественной, простой, нелинейной.
  • Рассчитывают числовые значения для тех компонентов уравнения, относительно которых это возможно.
  • Оценивают степень точности анализа.
  • Выполняют интерпретацию результатов и сравнивают их с гипотезой. Оценивают, насколько полученные значения являются правдоподобными и корректными.
  • Прогнозируют, какие значения может принимать зависимый компонент.
Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...