Как сделать корреляционный анализ пирсона в excel

Как создать и интерпретировать корреляционную матрицу в Excel

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

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

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

В этом руководстве объясняется, как создать и интерпретировать корреляционную матрицу в Excel.

Как создать корреляционную матрицу в Excel

Предположим, у нас есть следующий набор данных, который показывает среднее количество очков, подборов и передач для 10 баскетболистов:

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

В новом появившемся окне выберите « Корреляция » и нажмите « ОК ».

Для Input Range выберите ячейки, в которых находятся данные (включая первую строку с метками). Установите флажок рядом с Метки в первой строке.Для выходного диапазона выберите ячейку, в которой вы хотите разместить корреляционную матрицу. Затем нажмите ОК .

Это автоматически создаст следующую корреляционную матрицу:

Как интерпретировать матрицу корреляции в Excel

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

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

Соотношение очков и передач: 0,121871. Очки и передачи имеют небольшую положительную корреляцию, но это значение также довольно близко к нулю, поэтому нет убедительных доказательств значимой связи между этими двумя переменными.

Корреляция между подборами и передачами: 0,713713. Подборы и передачи имеют сильную положительную корреляцию. То есть игроки, у которых больше подборов, как правило, и чаще делают передачи.

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

Бонус: визуализация коэффициентов корреляции

Один из простых способов визуализировать значение коэффициентов корреляции в таблице — применить условное форматирование к таблице.

На верхней ленте в Excel перейдите на вкладку « Главная », затем в группу « Стили ».

Нажмите « Таблица условного форматирования » , затем нажмите « Цветовые шкалы » , затем нажмите « Цветовая шкала зелено-желто-красный» .

Это автоматически применяет следующую цветовую шкалу к корреляционной матрице:

Это помогает нам легко визуализировать силу корреляции между переменными.

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

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в R:

Функция ПИРСОН расчета коэффициента корреляции Пирсона в Excel

Функция ПИРСОН (вводить следует PEARSON на английском) предназначена для вычисления коэффициента корреляции Пирсона r . Данную функцию используют в работе в том случае, когда необходимо отразить степень линейной зависимости между двумя массивами данных. В Excel имеется несколько функций с помощью которых можно получить такой же результат, однако универсальность и простота функции Пирсон делают выбор в ее пользу.

Как работает функция ПИРСОН в Excel?

Рассмотрим пример расчета корреляции Пирсона между двумя массивами данных при помощи функции PEARSON в MS EXCEL. Первый массив представляет собой значения температур, второй давление в определенный летний период. Пример заполненной таблицы изображен на рисунке:

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

Пример решения с функцией ПИРСОН при анализе в Excel

1. Выберем ячейку С17 в которой должен будет посчитаться критерий Пирсона как результат и нажмем кнопку мастер функций «fx» или комбинацию горячих клавиш (SHIFT+F3). Откроется мастер функций, в поле Категория необходимо выбрать «Статистические». В списке статистических функций выбрать PEARSON и нажать Ok:
2. В меню аргументов выбрать Массив 1, в примере это утренняя температура воздуха, а затем массив 2 – атмосферное давление.
3. В результате в ячейке С17 получим коэффициент корреляции Пирсона. В нашем случае он отрицательный и приблизительно равен -0,14.

Данный показатель -0,14 по Пирсону, который вернула функция, говорит об неблагоприятной зависимости температуры и давления в раннее время суток.

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

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

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

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

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

Корреляционный анализ по Пирсону в Excel

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

Пример решения: представим исходные данные в виде таблицы:

1. Переходим курсором в ячейку F2. Откроем мастер функций fx (SHIFT+F3) или вводим вручную.
2. Выберем функцию PEARSON.
3. Выделим мышкой Массив1, затем Массив 2.
4. Нажмем ОК и в ячейке F2 получим критерий согласия Пирсона.

Интерпретация результата вычисления по Пирсону

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

Если коэффициент корреляции по модулю оказывается близким к 1, то это соответствует высокому уровню связи между переменными.

Если же получен знак минус, то большей величине одного признака соответствует меньшая величина другого. Иначе говоря, при наличии знака минус, увеличению одной переменной (признака, значения) соответствует уменьшение другой переменной. Такая зависимость носит название обратно пропорциональной зависимости. Эти положения очень важно четко усвоить для правильной интерпретации полученной корреляционной зависимости.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Переходим во вкладку «Файл».
2. В открывшемся окне перемещаемся в раздел «Параметры».
3. Далее переходим в пункт «Надстройки».
4. В нижней части следующего окна в разделе «Управление» переставляем переключатель в позицию «Надстройки Excel», если он находится в другом положении. Жмем на кнопку «OK».
5. В окне надстроек устанавливаем галочку около пункта «Пакет анализа». Жмем на кнопку «OK».
6. После этого пакет анализа активирован. Переходим во вкладку «Данные». Как видим, тут на ленте появляется новый блок инструментов – «Анализ». Жмем на кнопку «Анализ данных», которая расположена в нем.
7. Открывается список с различными вариантами анализа данных. Выбираем пункт «Корреляция». Кликаем по кнопке «OK».
8. Открывается окно с параметрами корреляционного анализа. В отличие от предыдущего способа, в поле «Входной интервал» мы вводим интервал не каждого столбца отдельно, а всех столбцов, которые участвуют в анализе. В нашем случае это данные в столбцах «Затраты на рекламу» и «Величина продаж».

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

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

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

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

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

Для того, чтобы рассчитать коэффициент корреляции Пирсона в Excell необходимо сделать следующие шаги:

1.Вносим значения для двух переменных в таблицу (Например Переменная 1 и Переменная 2)

2. Ставим курсор в пустую ячейку

3. На панеле инструментов нажимаем кнопку fx (вставить формулу)

4. В открывшемся окне «Мастер функций» в поле «Категории» выбираем Полный алфавитный перечень

5. Затем в поле «Выберите функцию» находим функцию ПИРСОН

5.1. Нажимаем Ок

6. В открывшемся окне «Аргументы функции» в поле Массив1 вносим номера ячеек, содержащие значения Переменной 1, в поле Массив2 вносим номера ячеек, содержащие значения Переменной2.