Как делать корреляционный анализ в excel. Пример нахождения коэффициента корреляции

Вы уже сталкивались с необходимостью рассчитать степень связи двух статистических величин и определить формулу, по которой они коррелируют? Нормальный человек может спросить, зачем это вообще может быть нужно. Как ни странно, это действительно бывает нужно. Знание достоверных корреляций может помочь вам зарабатывать бешенные деньги, если вы, скажем, биржевой трейдер. Проблема в том, что почему-то эти корреляции никто не раскрывает (удивительно, не правда ли?).

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

Эта статья рассчитана на продвинутый уровень владения Microsoft Excel. Если у вас нет времени читать всю статью, вы можете скачать файл и разобраться с ним самостоятельно.

Если вы часто сталкиваетесь с необходимостью сделать что-то подобное , настоятельно рекомендую подумать о покупке книги Статистические вычисления в среде Excel .

Что важно знать о корреляциях

Чтобы рассчитать достоверную корреляцию, необходимо иметь достоверную выборку, чем больше она будет, тем достовернее будет результат. Для целей данного примера я взял ежедневную выборку курсов валют за 10 лет. Данные есть в свободном доступе, я их брал с сайта http://oanda.com .

Что я, собственно, сделал

(1) Когда у меня были исходные данные, я начал с того, что проверил степень корреляции этих двух наборов данных. Для этого я воспользовался функцией CORREL (КОРРЕЛ) - о ней есть немного информации . Она возвращает степень корреляции двух диапазонов данных. Результат, прямо скажем, получился не особенно впечатляющим (всего около 70%). А вообще говоря, степень соотношения двух величин принято считать, как квадрат этой величины, то есть корреляция получилась достоверной приблизительно на 49%. Это очень мало!

(2) Мне это показалось очень странным. Какие ошибки могли закрасться в мои расчеты? Поэтому я решил построить график и посмотреть, что могло произойти. График был простоен специально с разбивкой по годам, чтобы можно было визуально увидеть, где рвет корреляцию. График получился вот таким

(3) Из графика очевидно, что на диапазоне около 35 рублей за евро корреляцию начинает рвать на две части. Из-за этого она и получилась недостоверной. Необходимо было определить в связи с чем это происходит.

(4) По цвету видно, что эти данные относятся к 2007, 2008, 2009 годам. Конечно! Периоды экономических пиков и спадов обычно недостоверны статистически, что и произошло в данном случае. Поэтому я попробовал исключить из данных эти периоды (ну и для проверки, я проверрил степень корреляции данных в этом периоде). Степень корреляции только этих данных составляет 0.01%, то есть она отсутствует в принципе. Зато без них данные коррелируют приблизительно на 81%. Это уже достаточно достоверная корреляция. Вот график с функцией.

Дальнейшие шаги

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

«Корреляция» в переводе с латинского обозначает «соотношение», «взаимосвязь». Количественная характеристика взаимосвязи может быть получена при вычислении коэффициента корреляции. Этот популярный в статистических анализах коэффициент показывает, связаны ли какие-либо параметры друг с другом (например, рост и вес; уровень интеллекта и успеваемость; количество травм и продолжительность работы).

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

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

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

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

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

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

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

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

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

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

Где x·y , x , y - средние значения выборок; σ(x), σ(y) - среднеквадратические отклонения.
Кроме того, коэффициент линейной парной корреляции может быть определен через коэффициент регрессии b: , где σ(x)=S(x), σ(y)=S(y) - среднеквадратические отклонения, b - коэффициент перед x в уравнении регрессии y=a+bx .

Другие варианты формул:
или

К xy - корреляционный момент (коэффициент ковариации)

Линейный коэффициент корреляции принимает значения от –1 до +1 (см. шкалу Чеддока). Например, при анализе тесноты линейной корреляционной связи между двумя переменными получен коэффициент парной линейной корреляции, равный –1 . Это означает, что между переменными существует точная обратная линейная зависимость.

Геометрический смысл коэффициента корреляции : r xy показывает, насколько различается наклон двух линий регрессии: y(x) и х(у) , насколько сильно различаются результаты минимизации отклонений по x и по y . Чем больше угол между линиями, то тем больше r xy .
Знак коэффициента корреляции совпадает со знаком коэффициента регрессии и определяет наклон линии регрессии, т.е. общую направленность зависимости (возрастание или убывание). Абсолютная величина коэффициента корреляции определяется степенью близости точек к линии регрессии.

Свойства коэффициента корреляции

1. |r xy | ≤ 1;
2. если X и Y независимы, то r xy =0, обратное не всегда верно;
3. если |r xy |=1, то Y=aX+b, |r xy (X,aX+b)|=1, где a и b постоянные, а ≠ 0;
4. |r xy (X,Y)|=|r xy (a 1 X+b 1 , a 2 X+b 2)|, где a 1 , a 2 , b 1 , b 2 – постоянные.

Инструкция . Укажите количество исходных данных. Полученное решение сохраняется в файле Word (см. Пример нахождения уравнения регрессии). Также автоматически создается шаблон решения в Excel . .

Количество строк (исходных данных)
Заданы итоговые значения величин (∑x, ∑x 2 , ∑xy, ∑y, ∑y 2)

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

Корреляционный анализ в Excel

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

График корреляции в excel

6) В левой верхней ячейке выделенной области появится первый элемент итоговой таблицы. Поэтому гипотеза Н0 отклоняется, то есть параметры регрессии и коэффициент корреляции не случайно отличаются от нуля, а статистически значимы. 7. Полученные оценки уравнения регрессии позволяют использовать его для прогноза.

Как рассчитать коэффициент корреляции в Excel

Если коэффициент равен 0, это говорит о том, что взаимосвязь между значениями отсутствует. Чтобы найти взаимосвязь между переменными и у, воспользуйтесь встроенной функцией Microsoft Excel «КОРРЕЛ». Например, для «Массив1» выделите значения у, а для «Массив2» выделите значения х. В итоге вы получите рассчитанный программой коэффициент корреляции. Далее необходимо вычислить разницу между каждым x и xср, и yср. В выбранных ячейках напишите формулы x-x, y-. Не забудьте закрепить ячейки со средними значениями. Полученный результат и будет искомым коэффициентом корреляции.

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

Сделай сам: вычисление корреляций валют с использованием Excel

Мы, к примеру, используем Microsoft Excel, но подойдёт и любая другая программа, в которой можно использовать корреляционную формулу. 7.После этого выделите ячейки с данными по EUR/USD. 9.Нажмите Enter для того, чтобы высчитать коэффициент корреляции для EUR/USD и USD/JPY. Обновлять цифры каждый день не стоит (ну, разве что вы одержимы корреляциями валюты).

Вы уже сталкивались с необходимостью рассчитать степень связи двух статистических величин и определить формулу, по которой они коррелируют? Для этого я воспользовался функцией CORREL (КОРРЕЛ) — о ней есть немного информации здесь. Она возвращает степень корреляции двух диапазонов данных. Теоретически, функцию корреляции можно уточнить, если перевести ее из линейной в экспоненциальную или логарифмическую. Анализ данных и графиков корреляции позволяет улучшить ее достоверность очень существенно.

Предположим, в ячейке В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" и в ряду "у"