Коэффициент парной корреляции в Excel. Пример вычисления корреляции, построения линейной регрессии и проверки гипотезы зависимости двух СВ нашим сервисом
Для определения степени зависимости между несколькими показателями применяется множественные коэффициенты корреляции. Их затем сводят в отдельную таблицу, которая имеет название корреляционной матрицы. Наименованиями строк и столбцов такой матрицы являются названия параметров, зависимость которых друг от друга устанавливается. На пересечении строк и столбцов располагаются соответствующие коэффициенты корреляции. Давайте выясним, как можно провести подобный расчет с помощью инструментов Excel.
Принято следующим образом определять уровень взаимосвязи между различными показателями, в зависимости от коэффициента корреляции:
- 0 – 0,3 – связь отсутствует;
- 0,3 – 0,5 – связь слабая;
- 0,5 – 0,7 – средняя связь;
- 0,7 – 0,9 – высокая;
- 0,9 – 1 – очень сильная.
Если корреляционный коэффициент отрицательный, то это значит, что связь параметров обратная.
Для того, чтобы составить корреляционную матрицу в Экселе, используется один инструмент, входящий в пакет «Анализ данных» . Он так и называется – «Корреляция» . Давайте узнаем, как с помощью него можно вычислить показатели множественной корреляции.
Этап 1: активация пакета анализа
Сразу нужно сказать, что по умолчанию пакет «Анализ данных» отключен. Поэтому, прежде чем приступить к процедуре непосредственного вычисления коэффициентов корреляции, нужно его активировать. К сожалению, далеко не каждый пользователь знает, как это делать. Поэтому мы остановимся на данном вопросе.
![](https://i1.wp.com/lumpics.ru/wp-content/uploads/2017/05/Perehod-v-parametryi-v-Microsoft-Excel-2.png)
После указанного действия пакет инструментов «Анализ данных» будет активирован.
Этап 2: расчет коэффициента
Теперь можно переходить непосредственно к расчету множественного коэффициента корреляции. Давайте на примере представленной ниже таблицы показателей производительности труда, фондовооруженности и энерговооруженности на различных предприятиях рассчитаем множественный коэффициент корреляции указанных факторов.
![](https://i2.wp.com/lumpics.ru/wp-content/uploads/2017/05/Zapusk-paketa-analiza-v-Microsoft-Excel.png)
Этап 3: анализ полученного результата
Теперь давайте разберемся, как понимать тот результат, который мы получили в процессе обработки данных инструментом «Корреляция» в программе Excel.
Как видим из таблицы, коэффициент корреляции фондовооруженности (Столбец 2 ) и энерговооруженности (Столбец 1 ) составляет 0,92, что соответствует очень сильной взаимосвязи. Между производительностью труда (Столбец 3 ) и энерговооруженностью (Столбец 1 ) данный показатель равен 0,72, что является высокой степенью зависимости. Коэффициент корреляции между производительностью труда (Столбец 3 ) и фондовооруженностью (Столбец 2 ) равен 0,88, что тоже соответствует высокой степени зависимости. Таким образом, можно сказать, что зависимость между всеми изучаемыми факторами прослеживается довольно сильная.
Как видим, пакет «Анализ данных» в Экселе представляет собой очень удобный и довольно легкий в обращении инструмент для определения множественного коэффициента корреляции. С его же помощью можно производить расчет и обычной корреляции между двумя факторами.
Коэффициент корреляции используется в том случае, когда нужно определить значение зависимости между значениями. Позже эти данные задают в одной таблице которая определяется как матрица корреляции. С помощью программы Microsoft Excel можно сделать расчёт корреляции.
Коэффициент корреляции определяется некоторыми данными. Если уровень показателя составляет от 0 до 0.3, то в таком случае связи нет. Если показатель составляет от 0.3 до 0.5 - это слабая связь. Если показатель доходит до 0.7, то связь средняя. Высокой можно назвать когда показатель достигает отметки 0.7-0.9. Если показатель составляет 1 - это наиболее сильная связь.
Первым делом нужно подключить пакет анализа данных. Без его активации дальнейшие действия нельзя провести. Подключить его можно открыв раздел "Главная" и в меню выбрать "Параметры".
Далее откроется новое окно. В нём нужно выбрать "Надстройки" и в поле управления параметрами выбрать среди элементов списка "Надстройки Excel"
После запуска окна параметров посредством его левого вертикального меню переходим в раздел «Надстройки». После этого нажимаем "Перейти".
После этих действий можно начать работу. Создана таблица с данными и на её примере сделаем нахождение множественного коэффициента корреляции.
Для начала откроем раздел "Данные" и среди инструментария выбираем "Анализ данных".
Откроется специальное окно с инструментами для анализа. Выбираем "Корреляция" и подтверждаем действие.
Перед пользователем появится новое окно с параметрами. Как входной интервал задается диапазон значений в таблице. Задать можно как в ручную так и выделив данные, которые будут отображены в специальном поле. Также можно разгруппировать элементы таблицы. Вывод сделаем на текущей странице, а значит в настройках параметра вывода выбираем "Выходной интервал". После этого подтверждаем действие.
1.Открыть программу Excel
2.Создать столбцы с данными. В нашем примере мы будем считать взаимосвязь, или корреляцию, между агрессивностью и неуверенностью в себе у детей-первоклассников. В эксперименте участвовали 30 детей, данные представлены в таблице эксель:
1 столбик — № испытуемого
2 столбик — агрессивность в баллах
3 столбик — неуверенность в себе в баллах
3.Затем необходимо выбрать пустую ячейку рядом с таблицей и нажать на значок f(x) в панели Excel
4.Откроется меню функций, среди категорий необходимо выбрать Статистические , а затем среди списка функций по алфавиту найти КОРРЕЛ и нажать ОК
5.Затем откроется меню аргументов функции, которое позволит выбрать нужные нам столбики с данными. Для выбора первого столбика Агрессивность нужно нажать на синюю кнопочку у строки Массив1
6.Выберем данные для Массива1 из столбика Агрессивность и нажмем на синюю кнопочку в диалоговом окне
7. Затем аналогично Массиву 1 нажмём на синюю кнопочку у строки Массив2
8.Выберем данные для Массива2 — столбик Неуверенность в себе и опять нажмем синюю кнопку, затем ОК
9.Вот, коэффициент корреляции r-Пирсона посчитан и записан в выбранной ячейке.В нашем случае он положительный и приблизительно равен 0,225 . Это говорит об умеренной положительной связи между агрессивностью и неуверенностью в себе у детей-первоклассников
Таким образом, статистическим выводом эксперимента будет: r = 0,225, выявлена умеренная положительная взаимосвязь между переменными агрессивность и неуверенность в себе.
В некоторых исследованиях требуется указывать р-уровень значимости коэффициента корреляции, однако программа Excel, в отличие от SPSS, не предоставляет такой возможности. Ничего страшного, есть (А.Д. Наследов).
Также Вы можете и приложить её к результатам исследования.
Коэффициент корреляции отражает степень взаимосвязи между двумя показателями. Всегда принимает значение от -1 до 1. Если коэффициент расположился около 0, то говорят об отсутствии связи между переменными.
Если значение близко к единице (от 0,9, например), то между наблюдаемыми объектами существует сильная прямая взаимосвязь. Если коэффициент близок к другой крайней точке диапазона (-1), то между переменными имеется сильная обратная взаимосвязь. Когда значение находится где-то посередине от 0 до 1 или от 0 до -1, то речь идет о слабой связи (прямой или обратной). Такую взаимосвязь обычно не учитывают: считается, что ее нет.
Расчет коэффициента корреляции в Excel
Рассмотрим на примере способы расчета коэффициента корреляции, особенности прямой и обратной взаимосвязи между переменными.
Значения показателей x и y:
Y – независимая переменная, x – зависимая. Необходимо найти силу (сильная / слабая) и направление (прямая / обратная) связи между ними. Формула коэффициента корреляции выглядит так:
![](https://i0.wp.com/exceltable.com/otchety/images/otchety56-2.png)
Чтобы упростить ее понимание, разобьем на несколько несложных элементов.
![](https://i2.wp.com/exceltable.com/otchety/images/otchety56-3.png)
![](https://i1.wp.com/exceltable.com/otchety/images/otchety56-9.png)
Между переменными определяется сильная прямая связь.
Встроенная функция КОРРЕЛ позволяет избежать сложных расчетов. Рассчитаем коэффициент парной корреляции в Excel с ее помощью. Вызываем мастер функций. Находим нужную. Аргументы функции – массив значений y и массив значений х:
![](https://i0.wp.com/exceltable.com/otchety/images/otchety56-10.png)
Покажем значения переменных на графике:
![](https://i2.wp.com/exceltable.com/otchety/images/otchety56-11.png)
Видна сильная связь между y и х, т.к. линии идут практически параллельно друг другу. Взаимосвязь прямая: растет y – растет х, уменьшается y – уменьшается х.
Матрица парных коэффициентов корреляции в Excel
Корреляционная матрица представляет собой таблицу, на пересечении строк и столбцов которой находятся коэффициенты корреляции между соответствующими значениями. Имеет смысл ее строить для нескольких переменных.
![](https://i0.wp.com/exceltable.com/otchety/images/otchety56-12.png)
Матрица коэффициентов корреляции в Excel строится с помощью инструмента «Корреляция» из пакета «Анализ данных».
![](https://i0.wp.com/exceltable.com/otchety/images/otchety56-13.png)
![](https://i2.wp.com/exceltable.com/otchety/images/otchety56-15.png)
Между значениями y и х1 обнаружена сильная прямая взаимосвязь. Между х1 и х2 имеется сильная обратная связь. Связь со значениями в столбце х3 практически отсутствует.
При корреляционной связи одной и той же величине одного признака соответствуют разные величины другого. Например: между ростом и весом имеется корреляционная связь, между заболеваемостью злокачественными новообразованиямии возрастом и т.д.
Существует 2 метода вычисления коэффициента корреляции: метод квадратов(Пирсона), метод рангов (Спирмена).
Наиболее точным является метод квадратов (Пирсона), при котором коэффициент корреляции определяется по формуле: , где
r ху ― коэффициент корреляции между статистическим рядом X и Y.
d х ― отклонение каждого из чисел статистического ряда X от своей средней арифметической.
d у ― отклонение каждого из чисел статистического ряда Y от своей средней арифметической.
В зависимости от силы связи и ее направления коэффициент корреляции может находиться в пределах от 0 до 1 (-1). Коэффициент корреляции, равный 0, говорит о полном отсутствии связи. Чем ближе уровень коэффициента корреляции к 1 или (-1), тем соответственно больше, теснее измеряемая им прямая или обратная связь. При коэффициенте корреляции равном 1 или (-1) связь полная, функциональная.
Схема оценки силы корреляционной связи по коэффициенту корреляции
Сила связи |
Величина коэффициента корреляции при наличии |
|
прямой связи (+) |
обратной связи (-) |
|
Связь отсутствует | ||
Связь малая (слабая) |
от 0 до +0,29 |
от 0 до –0,29 |
Связь средняя (умеренная) |
от +0,3 до +0,69 |
от –0,3 до –0,69 |
Связь большая (сильная) |
от +0,7 до +0,99 |
от –0,7 до –0,99 |
Связь полная (функциональная) |
Для вычисления коэффициента корреляции по методу квадратов составляется таблица из 7 колонок. Разберем процесс вычисления на примере:
ОПРЕДЕЛИТЬ СИЛУ И ХАРАКТЕР СВЯЗИ МЕЖДУ
Пора- ность зобом (V y ) |
d x = V x –M x |
d y = V y –M y |
d x d y |
d x 2 |
d y 2 |
|
Σ -1345 ,0 |
Σ 13996 ,0 |
Σ 313 , 47 |
1. Определяем среднее содержание йода в воде (в мг/л).
мг/л
2.Определяем среднюю пораженность зобом в %.
3. Определяем отклонение каждого V x от М x , т.е. d x .
201–138=63; 178–138=40 и т.д.
4. Аналогично определяем отклонение каждого V у от M у, т.е. d у.
0,2–3,8=-3,6; 0,6–38=-3,2 и т.д.
5. Определяем произведения отклонений. Полученное произведение суммируем и получаем.
6. d х возводим в квадрат и результаты суммируем, получаем.
7. Аналогично возводим в квадрат d у, результаты суммируем, получим
8. Наконец, все полученные суммы подставляем в формулу:
Для решения вопроса о достоверности коэффициента корреляции определяют его среднюю ошибку по формуле:
(Если число наблюдений менее 30, тогда в знаменателе n–1).
В нашем примере
Величина коэффициента корреляции считается достоверной, если не менее чем в 3 раза превышает свою среднюю ошибку.
В нашем примере
Таким образом, коэффициент корреляции не достоверен, что вызывает необходимость увеличения числа наблюдений.
Коэффициент корреляции можно определить несколько менее точным, но намного более легким способом ― методом рангов (Спирмена).
Метод Спирмена: P=1-(6∑d 2 /n-(n 2 -1))
составить два ряда из парных сопоставляемых признаков, обозначив первый и второй ряд соответственно х и у. При этом представить первый ряд признака в убывающем или возрастающем порядке, а числовые значения второго ряда расположить напротив тех значений первого ряда, которым они соответствуют
величину признака в каждом из сравниваемых рядов заменить порядковым номером (рангом). Рангами, или номерами, обозначают места показателей (значения) первого и второго рядов. При этом числовым значениям второго признака ранги должны присваиваться в том же порядке, какой был принят при раздаче их величинам первого признака. При одинаковых величинах признака в ряду ранги следует определять как среднее число из суммы порядковых номеров этих величин
определить разность рангов между х и у (d): d = х - у
возвести полученную разность рангов в квадрат (d 2)
получить сумму квадратов разности (Σ d 2) и подставить полученные значения в формулу:
Пример: методом рангов установить направление и силу связи между стажем работы в годах и частотой травм, если получены следующие данные:
Обоснование выбора метода: для решения задачи может быть выбран только метод ранговой корреляции, т.к. первый ряд признака "стаж работы в годах" имеет открытые варианты (стаж работы до 1 года и 7 и более лет), что не позволяет использовать для установления связи между сопоставляемыми признаками более точный метод - метод квадратов.
Решение . Последовательность расчетов изложена в тексте, результаты представлены в табл. 2.
Таблица 2
Стаж работы в годах |
Число травм |
Порядковые номера (ранги) |
Разность рангов |
Квадрат разности рангов |
|
d(х-у) |
d 2 |
||||
Каждый из рядов парных признаков обозначить через "х" и через "у" (графы 1-2).
Величину каждого из признаков заменить ранговым (порядковым) номером. Порядок раздачи рангов в ряду "x" следующий: минимальному значению признака (стаж до 1 года) присвоен порядковый номер "1", последующим вариантам этого же ряда признака соответственно в порядке увеличения 2-й, 3-й, 4-й и 5-й порядковые номера - ранги (см. графу 3). Аналогичный порядок соблюдается при раздаче рангов второму признаку "у" (графа 4). В тех случаях, когда встречаются несколько одинаковых по величине вариант (например, в задаче-эталоне это 12 и 12 травм на 100 работающих при стаже 3-4 года и 5-6 лет, порядковый номер обозначить средним числом из суммы их порядковых номеров. Эти данные о числе травм (12 травм) при ранжировании должны занимать 2 и 3 места, таким образом среднее число из них равно (2 + 3)/2 = 2,5. Таким образом, числу травм "12" и "12" (признаку) следует раздать ранговые номера одинаковые - "2,5" (графа 4).
Определить разность рангов d = (х - у) - (графа 5)
Разность рангов возвести в квадрат (d 2) и получить сумму квадратов разности рангов Σ d 2 (графа 6).
Произвести расчет коэффициента ранговой корреляции по формуле:
где
n - число сопоставляемых пар вариант в
ряду "x" и в ряду "у"