Как рассчитать коэффициент кендалла в экселе
Несколько лет назад я работал финансовым аналитиком, и, хотя мы не принимали активного участия в статистических данных, обнаружение корреляции было тем, что нам все же приходилось делать довольно часто.
В этом уроке я покажу вам два действительно простых способа рассчитать коэффициент корреляции в Excel . Для этого уже есть встроенная функция, и вы также можете использовать Data Analysis Toolpak.
Что такое коэффициент корреляции?
Поскольку это не статистический класс, позвольте мне кратко объяснить, что такое коэффициент корреляции, а затем мы перейдем к разделу, где мы вычисляем коэффициент корреляции в Excel.
Значение меньше нуля указывает на отрицательную корреляцию, что означает, что если рост увеличивается, то вес уменьшается, или если вес увеличивается, тогда рост уменьшается.
И значение больше нуля указывает на положительную корреляцию, что означает, что если рост увеличивается, то увеличивается вес, а если рост уменьшается, то вес уменьшается.
Чем ближе значение к 1, тем сильнее положительная корреляция. Таким образом, значение 0,8 будет означать, что данные о росте и весе сильно коррелированы.
Теперь давайте посмотрим, как рассчитать этот коэффициент корреляции в Excel.
Расчет коэффициента корреляции в Excel
Как я уже упоминал, есть несколько способов рассчитать коэффициент корреляции в Excel.
Использование формулы CORREL
Предположим, у вас есть набор данных, показанный ниже, где вы хотите рассчитать коэффициент корреляции между ростом и весом 10 человек.
Ниже приведена формула, которая сделает это:
Как только вы нажмете клавишу ВВОД, Excel выполнит все вычисления в серверной части и выдаст вам один единственный коэффициент корреляции Пирсона.
В нашем примере это значение немного больше 0,5, что указывает на довольно сильную положительную корреляцию.
Но если у вас есть несколько рядов, и вы хотите узнать коэффициент корреляции всех этих рядов, вы также можете рассмотреть возможность использования пакета инструментов анализа данных в Excel (рассматривается далее).
Использование пакета инструментов анализа данных
В Excel есть пакет инструментов для анализа данных, который можно использовать для быстрого расчета различных значений статистики (включая получение коэффициента корреляции).
Но пакет Data Analysis Toolpak по умолчанию отключен в Excel. Итак, первым шагом было бы снова включить инструмент анализа данных, а затем использовать его для расчета коэффициента корреляции Пирсона в Excel.
Включение пакета инструментов анализа данных
Ниже приведены шаги по включению пакета инструментов анализа данных в Excel:
Вышеупомянутые шаги добавят новую группу на вкладке «Данные» на ленте Excel под названием «Анализ». В этой группе у вас будет опция анализа данных
Расчет коэффициента корреляции с помощью пакета Data Analysis Toolpak
Теперь, когда инструмент анализа снова доступен на ленте, давайте посмотрим, как с его помощью рассчитать коэффициент корреляции.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите выяснить корреляцию между тремя рядами (рост и вес, рост и доход, вес и доход).
Ниже приведены шаги для этого:
Как только вы это сделаете, Excel рассчитает коэффициент корреляции для всех серий и выдаст вам таблицу, как показано ниже:
Обратите внимание, что результирующая таблица является статической и не будет обновляться в случае изменения какой-либо точки данных в вашей таблице. В случае каких-либо изменений вам придется повторить вышеуказанные шаги еще раз, чтобы сгенерировать новую таблицу коэффициентов корреляции.
Итак, это два быстрых и простых метода расчета коэффициента корреляции в Excel.
Приведем пример расчет коэффициента ранговой корреляции -Кендалла.
Для одного класса в 14 учащихся нам известны результаты их уровня интеллекта (IQ) и время решения серии логических заданий (X).
| № | Уровень интеллекта (IQ) | Время решения логических задач в секундах (X) |
| 1 | 100 | 154 |
| 2 | 118 | 123 |
| 3 | 112 | 120 |
| 4 | 97 | 213 |
| 5 | 99 | 200 |
| 6 | 103 | 187 |
| 7 | 102 | 155 |
| 8 | 132 | 100 |
| 9 | 122 | 114 |
| 10 | 121 | 115 |
| 11 | 115 | 107 |
| 12 | 117 | 176 |
| 13 | 109 | 143 |
| 14 | 111 | 111 |
1. Проранжируем полученные данные по столбцу IQ
| № | IQ | X |
| 4 | 1 | 14 |
| 5 | 2 | 13 |
| 1 | 3 | 9 |
| 7 | 4 | 10 |
| 6 | 5 | 12 |
| 13 | 6 | 8 |
| 14 | 7 | 3 |
| 3 | 8 | 6 |
| 11 | 9 | 2 |
| 12 | 10 | 11 |
| 2 | 11 | 7 |
| 10 | 12 | 5 |
| 9 | 13 | 4 |
| 8 | 14 | 1 |
2. Вычислим число совпадений значений P(p).
Например,
для первого испытуемого число совпадений будет равно 0, поскольку ранг второго испытуемого по столбцу X меньше, чем ранг первого испытуемого, следовательно у них нет совпадающих между собой рангов.
для третьего испытуемого число совпадений будет 1, поскольку если из ранга четвертого испытуемого по столбцу X вычесть ранг третьего испытуемого, разность будет равна 1, следовательно количество совпадающих рангов 1.
2.1. Заполняем столбец с числом совпадений для каждого испытуемого:
| № | Число совпадений P(p) |
| 4 | 0 |
| 5 | 0 |
| 1 | 1 |
| 7 | 2 |
| 6 | 0 |
| 13 | 0 |
| 14 | 3 |
| 3 | 0 |
| 11 | 9 |
| 12 | 0 |
| 2 | 0 |
| 10 | 0 |
| 9 | 0 |
| 8 | 0 |
| Сумма P(p) | 15 |
3. Подсчитываем сумму совпадающих значений P(p).
3.1. Вычислить сумму инверсий можно применив формулу:
Корреляционный анализ – популярный метод статистического исследования, который используется для выявления степени зависимости одного показателя от другого. В Microsoft Excel имеется специальный инструмент, предназначенный для выполнения этого типа анализа. Давайте выясним, как пользоваться данной функцией.
Суть корреляционного анализа
Предназначение корреляционного анализа сводится к выявлению наличия зависимости между различными факторами. То есть, определяется, влияет ли уменьшение или увеличение одного показателя на изменение другого.
Если зависимость установлена, то определяется коэффициент корреляции. В отличие от регрессионного анализа, это единственный показатель, который рассчитывает данный метод статистического исследования. Коэффициент корреляции варьируется в диапазоне от +1 до -1. При наличии положительной корреляции увеличение одного показателя способствует увеличению второго. При отрицательной корреляции увеличение одного показателя влечет за собой уменьшение другого. Чем больше модуль коэффициента корреляции, тем заметнее изменение одного показателя отражается на изменении второго. При коэффициенте равном 0 зависимость между ними отсутствует полностью.
Расчет коэффициента корреляции
Теперь давайте попробуем посчитать коэффициент корреляции на конкретном примере. Имеем таблицу, в которой помесячно расписана в отдельных колонках затрата на рекламу и величина продаж. Нам предстоит выяснить степень зависимости количества продаж от суммы денежных средств, которая была потрачена на рекламу.
Способ 1: определение корреляции через Мастер функций
Одним из способов, с помощью которого можно провести корреляционный анализ, является использование функции КОРРЕЛ. Сама функция имеет общий вид КОРРЕЛ(массив1;массив2).
-
Выделяем ячейку, в которой должен выводиться результат расчета. Кликаем по кнопке «Вставить функцию», которая размещается слева от строки формул.
В поле «Массив2» нужно внести координаты второго столбца. У нас это затраты на рекламу. Точно так же, как и в предыдущем случае, заносим данные в поле.
Как видим, коэффициент корреляции в виде числа появляется в заранее выбранной нами ячейке. В данном случае он равен 0,97, что является очень высоким признаком зависимости одной величины от другой.
Способ 2: вычисление корреляции с помощью пакета анализа
Кроме того, корреляцию можно вычислить с помощью одного из инструментов, который представлен в пакете анализа. Но прежде нам нужно этот инструмент активировать.
-
Переходим во вкладку «Файл».
Параметр «Группирование» оставляем без изменений – «По столбцам», так как у нас группы данных разбиты именно на два столбца. Если бы они были разбиты построчно, то тогда следовало бы переставить переключатель в позицию «По строкам».
В параметрах вывода по умолчанию установлен пункт «Новый рабочий лист», то есть, данные будут выводиться на другом листе. Можно изменить место, переставив переключатель. Это может быть текущий лист (тогда вы должны будете указать координаты ячеек вывода информации) или новая рабочая книга (файл).
Так как место вывода результатов анализа было оставлено по умолчанию, мы перемещаемся на новый лист. Как видим, тут указан коэффициент корреляции. Естественно, он тот же, что и при использовании первого способа – 0,97. Это объясняется тем, что оба варианта выполняют одни и те же вычисления, просто произвести их можно разными способами.
Как видим, приложение Эксель предлагает сразу два способа корреляционного анализа. Результат вычислений, если вы все сделаете правильно, будет полностью идентичным. Но, каждый пользователь может выбрать более удобный для него вариант осуществления расчета.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Корреляционный анализ – это распространённый метод исследования, применяемый для определения уровня зависимости 1-й величины от 2-й. В табличном процессоре есть особый инструмент, который позволяет реализовать данный тип исследования.
Суть корреляционного анализа
Он необходим для определения зависимости между двумя разными величинами. Иными словами, происходит выявление того, в какую сторону (меньшую/большую) меняется величина в зависимости от изменений второй.
Назначение корреляционного анализа
Зависимость устанавливается тогда, когда начинается выявление коэффициента корреляции. Этот метод отличается от анализа регрессии, так как здесь только один показатель, рассчитываемый при помощи корреляции. Интервал изменяется от +1 до -1. Если она плюсовая, то повышение первой величины способствует повышению 2-й. Если минусовая, то повышение 1-й величины способствует понижению 2-й. Чем выше коэффициент, тем сильнее одна величина влияет на 2-ю.
Важно! При 0-м коэффициенте зависимости между величинами нет.
Расчет коэффициента корреляции
Разберем расчёт на нескольких образцах. К примеру, есть табличные данные, где по месяцам описаны в отдельных столбцах траты на рекламное продвижение и объём продаж. Исходя из таблицы, будем выяснять уровень зависимости объема продаж от денег, затраченных на рекламное продвижение.
Способ 1: определение корреляции через Мастер функций
- Необходимо произвести выделение ячейки, в которой планируется выводить итог расчета. Нажать «Вставить функцию», находящуюся слева от текстового поля для ввода формулы.
- Открывается «Мастер функций». Здесь необходимо найти КОРРЕЛ, кликнуть на нее, затем на «ОК».
- Открылось окошко аргументов. В строку «Массив1» необходимо ввести координаты интервалы 1-го из значений. В рассматриваемом примере — это столбец «Величина продаж». Нужно просто произвести выделение всех ячеек, которые находятся в этой колонке. В строку «Массив2» аналогично необходимо добавить координаты второй колонки. В рассматриваемом примере — это столбец «Затраты на рекламу».
- После введения всех диапазонов кликаем на кнопку «ОК».
Коэффициент отобразился в той ячейке, которая была указана в начале наших действий. Полученный результат 0,97. Этот показатель отображает высокую зависимость первой величины от второй.
4
Способ 2: вычисление корреляции с помощью Пакета анализа
Существует еще один метод определения корреляции. Здесь используется одна из функций, находящаяся в пакете анализа. Перед ее использованием нужно провести активацию инструмента. Подробная инструкция:
Вывелись итоговые показатели. Результат такой же, как и в первом методе – 0,97.
Определение и вычисление множественного коэффициента корреляции в MS Excel
Для выявления уровня зависимости нескольких величин применяются множественные коэффициенты. В дальнейшем итоги сводятся в отдельную табличку, именуемую корреляционной матрицей.
Коэффициент парной корреляции в Excel
Разберем, как правильно проводить коэффициент парной корреляции в табличном процессоре Excel.
Расчет коэффициента парной корреляции в Excel
К примеру, у вас есть значения величин х и у.
12
Х – это зависимая переменна, а у – независимая. Необходимо найти направление и силу связи между этими показателями. Пошаговая инструкция:
Матрица парных коэффициентов корреляции в Excel
Разберем, как проводить подсчет коэффициентов парных матриц. К примеру, есть матрица из четырех переменных.
22
Функция КОРРЕЛ для определения взаимосвязи и корреляции в Excel
КОРРЕЛ – функция, применяемая для подсчета коэффициента корреляции между 2-мя массивами. Разберем на четырех примерах все способности этой функции.
Примеры использования функции КОРРЕЛ в Excel
Первый пример. Есть табличка, в которой расписана информация об усредненных показателях заработной платы работников компании на протяжении одиннадцати лет и курсе $. Необходимо выявить связь между этими 2-умя величинами. Табличка выглядит следующим образом:
24
Алгоритм расчёта выглядит следующим образом:
25
Отображенный показатель близок к 1. Результат:
26
Определение коэффициента корреляции влияния действий на результат
Второй пример. Два претендента обратились за помощью к двум разным агентствам для реализации рекламного продвижения длительностью в пятнадцать суток. Каждые сутки проводился социальный опрос, определяющий степень поддержки каждого претендента. Любой опрошенный мог выбрать одного из двух претендентов или же выступить против всех. Необходимо определить, как сильно повлияло каждое рекламное продвижение на степень поддержки претендентов, какая компания эффективней.
27
Используя нижеприведенные формулы, рассчитаем коэффициент корреляции:
28
Из полученных результатов становится понятно, что степень поддержки 1-го претендента повышалась с каждыми сутками проведения рекламного продвижения, следовательно, коэффициент корреляции приближается к 1. При запуске рекламы другой претендент обладал большим числом доверия, и на протяжении 5 дней была положительная динамика. Потом степень доверия понизилась и к пятнадцатым суткам опустилась ниже изначальных показателей. Низкие показатели говорят о том, что рекламное продвижение отрицательно повлияло на поддержку. Не стоит забывать, что на показатели могли повлиять и остальные сопутствующие факторы, не рассматриваемые в табличной форме.
29
Теперь необходимо провести определение наличия связи между 2-мя показателями по нижеприведенной формуле:
Если полученный коэффициент выше 0,7, то целесообразней применять функцию линейной регрессии. В рассматриваемом примере делаем:
30
Теперь производим построение графика:
31
Применяем это уравнение, чтобы определить число просматриваний при 200, 500 и 1000 репостов: =9,2937*D4-206,12. Получаем следующие результаты:
32
33
Особенности использования функции КОРРЕЛ в Excel
Данная функция имеет нижеприведенные особенности:
Оценка статистической значимости коэффициента корреляции
При проверке значимости корреляционного коэффициента нулевая гипотеза состоит в том, что показатель имеет значение 0, а альтернативная не имеет. Для проверки применяется нижеприведенная формула:
34
Заключение
Корреляционный анализ в табличном процессоре – это простой и автоматизированный процесс. Для его выполнения необходимо знать всего лишь, где находятся нужные инструменты и как их активировать через настройки программы.
Читайте также: