Как рассчитать корреляцию в Microsoft Excel. Как рассчитать линейный коэффициент корреляции
«Корреляция» в переводе с латинского обозначает «соотношение», «взаимосвязь». Количественная характеристика взаимосвязи может быть получена при вычислении коэффициента корреляции. Этот популярный в статистических анализах коэффициент показывает, связаны ли какие-либо параметры друг с другом (например, рост и вес; уровень интеллекта и успеваемость; количество травм и продолжительность работы).
Использование корреляции
Вычисление корреляции особенно широко используется в экономике, социологических исследованиях, медицине и биометрии — везде, где можно получить два массива данных, между которыми может обнаружиться связь.
Рассчитать корреляцию можно вручную, выполняя несложные арифметические действия. Однако процесс вычисления оказывается очень трудоемким, если набор данных велик. Особенность метода в том, что он требует сбора большого количества исходных данных, чтобы наиболее точно отобразить, есть ли связь между признаками. Поэтому серьезное использование корреляционного анализа невозможно без применения вычислительной техники. Одной из наиболее популярных и доступных программ для решения этой задачи является .
Как выполнить корреляцию в Excel?
Самым трудоемким этапом определения корреляции является набор массива данных. Сравниваемые данные располагаются обычно в двух колонках или строчках. Таблицу следует делать без пропусков в ячейках. Современные версии Excel (с 2007 и младше) не требуют установок дополнительных настроек для статистических расчетов; необходимые манипуляции можно сделать :
- Выбрать пустую ячейку, в которую будет выведен результат расчетов.
- Нажать в главном меню Excel пункт «Формулы».
- Среди кнопок, сгруппированных в «Библиотеку функций», выбрать «Другие функции».
- В выпадающих списках выбрать функцию расчета корреляции (Статистические — КОРРЕЛ).
- В Excel откроется панель «Аргументы функции». «Массив 1» и «Массив 2» — это диапазоны сравниваемых данных. Для автоматического заполнения этих полей можно просто выделить нужные ячейки таблицы.
- Нажать «ОК», закрыв окно аргументов функции. В ячейке появится подсчитанный коэффициент корреляции.
Корреляция может быть прямая (если коэффициент больше нуля) и обратная (от -1 до 0).
Первая означает, что при росте одного параметра растет и другой. Обратная (отрицательная) корреляция отражает факт, что при росте одной переменной другая уменьшается.
Корреляция может быть близка к нулю. Это обычно свидетельствует, что исследуемые параметры не связаны друг с другом. Но иногда нулевая корреляция возникает, если сделана неудачная выборка, которая не отразила связь, либо связь имеет сложный нелинейный характер.
Если коэффициент показывает среднюю или сильную взаимосвязь (от ±0,5 до ±0,99), следует помнить, что это лишь статистическая взаимосвязь, которая вовсе не гарантирует влияние одного параметра на другой.
Также нельзя исключать ситуации, что оба параметра независимы друг от друга, но на них воздействует какой-нибудь третий неучтенный фактор. Excel помогает моментально вычислить коэффициент корреляции, но обычно только количественных методов недостаточно для установления причинно-следственных связей в соотносимых выборках.
Коэффициент корреляции отражает степень взаимосвязи между двумя показателями. Всегда принимает значение от -1 до 1. Если коэффициент расположился около 0, то говорят об отсутствии связи между переменными.
Если значение близко к единице (от 0,9, например), то между наблюдаемыми объектами существует сильная прямая взаимосвязь. Если коэффициент близок к другой крайней точке диапазона (-1), то между переменными имеется сильная обратная взаимосвязь. Когда значение находится где-то посередине от 0 до 1 или от 0 до -1, то речь идет о слабой связи (прямой или обратной). Такую взаимосвязь обычно не учитывают: считается, что ее нет.
Расчет коэффициента корреляции в Excel
Рассмотрим на примере способы расчета коэффициента корреляции, особенности прямой и обратной взаимосвязи между переменными.
Значения показателей x и y:
Y – независимая переменная, x – зависимая. Необходимо найти силу (сильная / слабая) и направление (прямая / обратная) связи между ними. Формула коэффициента корреляции выглядит так:
Чтобы упростить ее понимание, разобьем на несколько несложных элементов.
Между переменными определяется сильная прямая связь.
Встроенная функция КОРРЕЛ позволяет избежать сложных расчетов. Рассчитаем коэффициент парной корреляции в Excel с ее помощью. Вызываем мастер функций. Находим нужную. Аргументы функции – массив значений y и массив значений х:
Покажем значения переменных на графике:
Видна сильная связь между y и х, т.к. линии идут практически параллельно друг другу. Взаимосвязь прямая: растет y – растет х, уменьшается y – уменьшается х.
Матрица парных коэффициентов корреляции в Excel
Корреляционная матрица представляет собой таблицу, на пересечении строк и столбцов которой находятся коэффициенты корреляции между соответствующими значениями. Имеет смысл ее строить для нескольких переменных.
Матрица коэффициентов корреляции в Excel строится с помощью инструмента «Корреляция» из пакета «Анализ данных».
Между значениями y и х1 обнаружена сильная прямая взаимосвязь. Между х1 и х2 имеется сильная обратная связь. Связь со значениями в столбце х3 практически отсутствует.
Утилита, которая широко используется во многих компаниях и на предприятиях. Реалии таковы, что практически любой работник должен в той или иной мере владеть Экселем, так как эта программа применяется для решения очень широкого спектра задач. Работая с таблицами, нередко приходится определять, связаны ли между собой определённые переменные. Для этого используется так называемая корреляция. В этой статье мы подробно рассмотрим, как рассчитать коэффициент корреляции в Excel. Давайте разбираться. Поехали!
Начнём с того, что такое коэффициент корреляции вообще. Он показывает степень взаимосвязи между двумя элементами и всегда находится в диапазоне от -1 (сильная обратная взаимосвязь) до 1 (сильная прямая взаимосвязь). Если коэффициент равен 0, это говорит о том, что взаимосвязь между значениями отсутствует.
Теперь, разобравшись с теорией, перейдём к практике. Чтобы найти взаимосвязь между переменными и у, воспользуйтесь встроенной функцией Microsoft Excel «КОРРЕЛ». Для этого нажмите на кнопку мастера функций (она расположена рядом с полем для формул). В открывшемся окне выберите из списка функций «КОРРЕЛ». После этого задайте диапазон в полях «Массив1» и «Массив2». Например, для «Массив1» выделите значения у, а для «Массив2» выделите значения х. В итоге вы получите рассчитанный программой коэффициент корреляции.
Следующий способ будет актуален для студентов, от которых требуют найти зависимость по заданной формуле. Прежде всего, нужно знать средние значения переменных x и y. Для этого выделите значения переменной и воспользуйтесь функцией «СРЗНАЧ». Далее необходимо вычислить разницу между каждым x и x ср, и y ср. В выбранных ячейках напишите формулы x-x, y-. Не забудьте закрепить ячейки со средними значениями. Затем растяните формулу вниз, чтобы она применилась и к остальным числам.
Теперь, когда есть все необходимые данные, можно посчитать корреляцию. Перемножьте полученные разности таким образом: (x-x ср) * (y-y ср). После того как вы получите результат для каждой из переменных, просуммируйте полученные числа при помощи функции автосуммы. Таким образом рассчитывается числитель.
Теперь перейдём к знаменателю. Посчитанные разности нужно возвести в квадрат. Для этого в отдельной колонке введите формулы: (x-x ср) 2 и (y-y ср) 2 . Затем растяните формулы на весь диапазон. После, при помощи кнопки «Автосумма», найдите сумму по всем колонкам (для x и для y). Осталось перемножить найденные суммы и извлечь из них квадратный корень. Последний шаг - поделите числитель на знаменатель. Полученный результат и будет искомым коэффициентом корреляции.
Как видите, умея правильно работать с функциями Microsoft Excel , можно существенно упростить себе задачу расчёта непростых математических выражений. Благодаря средствам, реализованным в программе, вы без труда сделаете корреляционный анализ в Excel всего за пару минут, сэкономив время и силы. Пишите в комментариях, помогла ли вам статья разобраться в вопросе, спрашивайте обо всём, что заинтересовало вас по рассмотренной теме.
При корреляционной связи одной и той же величине одного признака соответствуют разные величины другого. Например: между ростом и весом имеется корреляционная связь, между заболеваемостью злокачественными новообразованиямии возрастом и т.д.
Существует 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" и в ряду "у"
Задача:
Имеется связанная выборка из 26 пар значений (х k
,y k
):
| k | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
| x k | 25.20000 | 26.40000 | 26.00000 | 25.80000 | 24.90000 | 25.70000 | 25.70000 | 25.70000 | 26.10000 | 25.80000 |
| y k | 30.80000 | 29.40000 | 30.20000 | 30.50000 | 31.40000 | 30.30000 | 30.40000 | 30.50000 | 29.90000 | 30.40000 |
| k | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 |
| x k | 25.90000 | 26.20000 | 25.60000 | 25.40000 | 26.60000 | 26.20000 | 26.00000 | 22.10000 | 25.90000 | 25.80000 |
| y k | 30.30000 | 30.50000 | 30.60000 | 31.00000 | 29.60000 | 30.40000 | 30.70000 | 31.60000 | 30.50000 | 30.60000 |
| k | 21 | 22 | 23 | 24 | 25 | 26 |
| x k | 25.90000 | 26.30000 | 26.10000 | 26.00000 | 26.40000 | 25.80000 |
| y k | 30.70000 | 30.10000 | 30.60000 | 30.50000 | 30.70000 | 30.80000 |
Требуется вычислить/построить:
- коэффициент корреляции;
- проверить гипотезу зависимости случайных величин X и Y, при уровне значимости α
= 0.05 ;
- коэффициенты уравнения линейной регрессии;
- диаграмму рассеяния (корреляционное поле) и график линии регрессии;
РЕШЕНИЕ:
1. Вычисляем коэффициент корреляции.
Коэффициент корреляции - это показатель взаимного вероятностного влияния двух случайных величин. Коэффициент корреляции R может принимать значения от -1 до +1 . Если абсолютное значение находится ближе к 1 , то это свидетельство сильной связи между величинами, а если ближе к 0 - то, это говорит о слабой связи или ее отсутствии. Если абсолютное значение R равно единице, то можно говорить о функциональной связи между величинами, то есть одну величину можно выразить через другую посредством математической функции.
Вычислить коэффициент корреляции можно по следующим формулам:
| n |
| Σ |
| k = 1 |
| M x | = |
|
| x k , | M y | = | или по формуле
На практике, для вычисления коэффициента корреляции чаще используется формула (1.4) т.к. она требует меньше вычислений. Однако если предварительно была вычислена ковариация cov(X,Y) , то выгоднее использовать формулу (1.1), т.к. кроме собственно значения ковариации можно воспользоваться и результатами промежуточных вычислений. 1.1 Вычислим коэффициент корреляции по формуле (1.4) , для этого вычислим значения x k 2 , y k 2 и x k y k и занесем их в таблицу 1. Таблица 1
1.2. Вычислим M x по формуле (1.5) . 1.2.1. x k x 1 + x 2 + … + x 26 = 25.20000 + 26.40000 + ... + 25.80000 = 669.500000 1.2.2. 669.50000 / 26 = 25.75000 M x = 25.750000 1.3. Аналогичным образом вычислим M y . 1.3.1. Сложим последовательно все элементы y k y 1 + y 2 + … + y 26 = 30.80000 + 29.40000 + ... + 30.80000 = 793.000000 1.3.2. Разделим полученную сумму на число элементов выборки 793.00000 / 26 = 30.50000 M y = 30.500000 1.4. Аналогичным образом вычислим M xy . 1.4.1. Сложим последовательно все элементы 6-го столбца таблицы 1 776.16000 + 776.16000 + ... + 794.64000 = 20412.830000 1.4.2. Разделим полученную сумму на число элементов 20412.83000 / 26 = 785.10885 M xy = 785.108846 1.5. Вычислим значение S x 2 по формуле (1.6.) . 1.5.1. Сложим последовательно все элементы 4-го столбца таблицы 1 635.04000 + 696.96000 + ... + 665.64000 = 17256.910000 1.5.2. Разделим полученную сумму на число элементов 17256.91000 / 26 = 663.72731 1.5.3. Вычтем из последнего числа квадрат величины M x получим значение для S x 2 S x 2 = 663.72731 - 25.75000 2 = 663.72731 - 663.06250 = 0.66481 1.6. Вычислим значение S y 2 по формуле (1.6.) . 1.6.1. Сложим последовательно все элементы 5-го столбца таблицы 1 948.64000 + 864.36000 + ... + 948.64000 = 24191.840000 1.6.2. Разделим полученную сумму на число элементов 24191.84000 / 26 = 930.45538 1.6.3. Вычтем из последнего числа квадрат величины M y получим значение для S y 2 S y 2 = 930.45538 - 30.50000 2 = 930.45538 - 930.25000 = 0.20538 1.7. Вычислим произведение величин S x 2 и S y 2 . S x 2 S y 2 = 0.66481 0.20538 = 0.136541 1.8. Извлечем и последнего числа квадратный корень, получим значение S x S y . S x S y = 0.36951 1.9. Вычислим значение коэффициента корреляции по формуле (1.4.) . R = (785.10885 - 25.75000 30.50000) / 0.36951 = (785.10885 - 785.37500) / 0.36951 = -0.72028 ОТВЕТ: R x,y = -0.720279 2. Проверяем значимость коэффициента корреляции (проверяем гипотезу зависимости).Поскольку оценка коэффициента корреляции вычислена на конечной выборке, и поэтому может отклоняться от своего генерального значения,
необходимо проверить значимость коэффициента корреляции. Проверка производится с помощью t
-критерия:
Случайная величина t следует t -распределению Стьюдента и по таблице t -распределения необходимо найти критическое значение критерия (t кр.α) при заданном уровне значимости α . Если вычисленное по формуле (2.1) t по модулю окажется меньше чем t кр.α , то зависимости между случайными величинами X и Y нет. В противном случае, экспериментальные данные не противоречат гипотезе о зависимости случайных величин. 2.1. Вычислим значение t -критерия по формуле (2.1) получим:
2.2. Определим по таблице t -распределения критическое значение параметра t кр.α Искомое значение t
кр.α располагается на пересечении строки соответствующей числу степеней свободы
и столбца соответствующего заданному уровню значимости α
. Таблица 2 t -распределение
2.2. Сравним абсолютное значение t -критерия и t кр.α Абсолютное значение t -критерия не меньше критического t = 5.08680, t кр.α = 2.064, следовательно экспериментальные данные, с вероятностью 0.95 (1 - α ), не противоречат гипотезе о зависимости случайных величин X и Y. 3. Вычисляем коэффициенты уравнения линейной регрессии.Уравнение линейной регрессии представляет собой уравнение прямой, аппроксимирующей (приблизительно описывающей) зависимость между случайными величинами X и Y. Если считать, что величина X свободная, а Y зависимая от Х, то уравнение регрессии запишется следующим образом Y = a + b X (3.1), где:
Рассчитанный по формуле (3.2) коэффициент b называют коэффициентом линейной регрессии. В некоторых источниках a называют постоянным коэффициентом регрессии и b соответственно переменным. Погрешности предсказания Y по заданному значению X вычисляются по формулам: Величину σ y/x (формула 3.4) еще называют остаточным средним квадратическим отклонением , оно характеризует уход величины Y от линии регрессии, описываемой уравнением (3.1), при фиксированном (заданном) значении X. | . |
S y / S x = 0.55582
3.3 Вычислим коэффициент b по формуле (3.2)
b = -0.72028 0.55582 = -0.40035
3.4 Вычислим коэффициент a по формуле (3.3)
a = 30.50000 - (-0.40035 25.75000) = 40.80894
3.5 Оценим погрешности уравнения регрессии .
3.5.1 Извлечем из S y 2 квадратный корень получим:
3.5.4 Вычислим относительную погрешность по формуле (3.5)
δ y/x = (0.31437 / 30.50000)100% = 1.03073%
4. Строим диаграмму рассеяния (корреляционное поле) и график линии регрессии.
Диаграмма рассеяния - это графическое изображение соответствующих пар (x k , y k ) в виде точек плоскости, в прямоугольных координатах с осями X и Y. Корреляционное поле является одним из графических представлений связанной (парной) выборки. В той же системе координат строится и график линии регрессии. Следует тщательно выбрать масштабы и начальные точки на осях, чтобы диаграмма была максимально наглядной.4.1. Находим минимальный и максимальный элемент выборки X это 18-й и 15-й элементы соответственно, x min = 22.10000 и x max = 26.60000.
4.2. Находим минимальный и максимальный элемент выборки Y это 2-й и 18-й элементы соответственно, y min = 29.40000 и y max = 31.60000.
4.3. На оси абсцисс выбираем начальную точку чуть левее точки x 18 = 22.10000, и такой масштаб, чтобы на оси поместилась точка x 15 = 26.60000 и отчетливо различались остальные точки.
4.4. На оси ординат выбираем начальную точку чуть левее точки y 2 = 29.40000, и такой масштаб, чтобы на оси поместилась точка y 18 = 31.60000 и отчетливо различались остальные точки.
4.5. На оси абсцисс размещаем значения x k , а на оси ординат значения y k .
4.6. Наносим точки (x 1 , y 1 ), (x 2 , y 2 ),…,(x 26 , y 26 ) на координатную плоскость. Получаем диаграмму рассеяния (корреляционное поле), изображенное на рисунке ниже.
4.7. Начертим линию регрессии.
Для этого найдем две различные точки с координатами (x r1 , y r1) и (x r2 , y r2) удовлетворяющие уравнению (3.6), нанесем их на координатную плоскость и проведем через них прямую. В качестве абсциссы первой точки возьмем значение x min = 22.10000. Подставим значение x min в уравнение (3.6), получим ординату первой точки. Таким образом имеем точку с координатами (22.10000, 31.96127). Аналогичным образом получим координаты второй точки, положив в качестве абсциссы значение x max = 26.60000. Вторая точка будет: (26.60000, 30.15970).
Линия регрессии показана на рисунке ниже красным цветом
Обратите внимание, что линия регрессии всегда проходит через точку средних значений величин Х и Y, т.е. с координатами (M x , M y).
