Как выполнить корреляционный тест в Excel (шаг за шагом)
Одним из способов количественной оценки взаимосвязи между двумя переменными является использование коэффициента корреляции Пирсона, который является мерой линейной связи между двумя переменными.
Он всегда принимает значение от -1 до 1, где:
- -1 указывает на совершенно отрицательную линейную корреляцию между двумя переменными
- 0 указывает на отсутствие линейной корреляции между двумя переменными
- 1 указывает на совершенно положительную линейную корреляцию между двумя переменными.
Чтобы определить, является ли коэффициент корреляции статистически значимым, вы можете выполнить тест корреляции, который включает вычисление t-показателя и соответствующего p-значения.
Формула для расчета t-показателя:
т = г √ (п-2) / (1-г 2 )
- r: коэффициент корреляции
- n: размер выборки
Значение p рассчитывается как соответствующее двустороннее значение p для t-распределения с n-2 степенями свободы.
В следующем пошаговом примере показано, как выполнить тест корреляции в Excel.
Шаг 1: введите данные
Во-первых, давайте введем некоторые значения данных для двух переменных в Excel:
Шаг 2: Рассчитайте коэффициент корреляции
Затем мы можем использовать функцию CORREL() для вычисления коэффициента корреляции между двумя переменными:
Коэффициент корреляции между двумя переменными оказывается равным 0,803702 .
Это очень положительный коэффициент корреляции, но чтобы определить, является ли он статистически значимым, нам нужно рассчитать соответствующий t-показатель и p-значение.
Шаг 3: Рассчитайте статистику теста и P-значение
Затем мы можем использовать следующие формулы для расчета тестовой статистики и соответствующего значения p:
Тестовая статистика оказывается равной 4,27124 , а соответствующее значение p равно 0,001634 .
Поскольку это p-значение меньше 0,05, у нас есть достаточно доказательств, чтобы сказать, что корреляция между двумя переменными является статистически значимой.
2 способа корреляционного анализа в Microsoft Excel
Корреляционный анализ – популярный метод статистического исследования, который используется для выявления степени зависимости одного показателя от другого. В Microsoft Excel имеется специальный инструмент, предназначенный для выполнения этого типа анализа. Давайте выясним, как пользоваться данной функцией.
Суть корреляционного анализа
Предназначение корреляционного анализа сводится к выявлению наличия зависимости между различными факторами. То есть, определяется, влияет ли уменьшение или увеличение одного показателя на изменение другого.
Если зависимость установлена, то определяется коэффициент корреляции. В отличие от регрессионного анализа, это единственный показатель, который рассчитывает данный метод статистического исследования. Коэффициент корреляции варьируется в диапазоне от +1 до -1. При наличии положительной корреляции увеличение одного показателя способствует увеличению второго. При отрицательной корреляции увеличение одного показателя влечет за собой уменьшение другого. Чем больше модуль коэффициента корреляции, тем заметнее изменение одного показателя отражается на изменении второго. При коэффициенте равном 0 зависимость между ними отсутствует полностью.
Расчет коэффициента корреляции
Теперь давайте попробуем посчитать коэффициент корреляции на конкретном примере. Имеем таблицу, в которой помесячно расписана в отдельных колонках затрата на рекламу и величина продаж. Нам предстоит выяснить степень зависимости количества продаж от суммы денежных средств, которая была потрачена на рекламу.
Способ 1: определение корреляции через Мастер функций
Одним из способов, с помощью которого можно провести корреляционный анализ, является использование функции КОРРЕЛ. Сама функция имеет общий вид КОРРЕЛ(массив1;массив2).
-
Выделяем ячейку, в которой должен выводиться результат расчета. Кликаем по кнопке «Вставить функцию», которая размещается слева от строки формул.
В поле «Массив2» нужно внести координаты второго столбца. У нас это затраты на рекламу. Точно так же, как и в предыдущем случае, заносим данные в поле.
Как видим, коэффициент корреляции в виде числа появляется в заранее выбранной нами ячейке. В данном случае он равен 0,97, что является очень высоким признаком зависимости одной величины от другой.
Способ 2: вычисление корреляции с помощью пакета анализа
Кроме того, корреляцию можно вычислить с помощью одного из инструментов, который представлен в пакете анализа. Но прежде нам нужно этот инструмент активировать.
- Переходим во вкладку «Файл».
- В открывшемся окне перемещаемся в раздел «Параметры».
- Далее переходим в пункт «Надстройки».
- В нижней части следующего окна в разделе «Управление» переставляем переключатель в позицию «Надстройки Excel», если он находится в другом положении. Жмем на кнопку «OK».
- В окне надстроек устанавливаем галочку около пункта «Пакет анализа». Жмем на кнопку «OK».
- После этого пакет анализа активирован. Переходим во вкладку «Данные». Как видим, тут на ленте появляется новый блок инструментов – «Анализ». Жмем на кнопку «Анализ данных», которая расположена в нем.
- Открывается список с различными вариантами анализа данных. Выбираем пункт «Корреляция». Кликаем по кнопке «OK».
- Открывается окно с параметрами корреляционного анализа. В отличие от предыдущего способа, в поле «Входной интервал» мы вводим интервал не каждого столбца отдельно, а всех столбцов, которые участвуют в анализе. В нашем случае это данные в столбцах «Затраты на рекламу» и «Величина продаж».
Параметр «Группирование» оставляем без изменений – «По столбцам», так как у нас группы данных разбиты именно на два столбца. Если бы они были разбиты построчно, то тогда следовало бы переставить переключатель в позицию «По строкам».
В параметрах вывода по умолчанию установлен пункт «Новый рабочий лист», то есть, данные будут выводиться на другом листе. Можно изменить место, переставив переключатель. Это может быть текущий лист (тогда вы должны будете указать координаты ячеек вывода информации) или новая рабочая книга (файл).
Так как место вывода результатов анализа было оставлено по умолчанию, мы перемещаемся на новый лист. Как видим, тут указан коэффициент корреляции. Естественно, он тот же, что и при использовании первого способа – 0,97. Это объясняется тем, что оба варианта выполняют одни и те же вычисления, просто произвести их можно разными способами.
Как видим, приложение Эксель предлагает сразу два способа корреляционного анализа. Результат вычислений, если вы все сделаете правильно, будет полностью идентичным. Но, каждый пользователь может выбрать более удобный для него вариант осуществления расчета.
Корреляционный анализ в Excel. Пример выполнения корреляционного анализа
Корреляционный анализ – это распространённый метод исследования, применяемый для определения уровня зависимости 1-й величины от 2-й. В табличном процессоре есть особый инструмент, который позволяет реализовать данный тип исследования.
Суть корреляционного анализа
Он необходим для определения зависимости между двумя разными величинами. Иными словами, происходит выявление того, в какую сторону (меньшую/большую) меняется величина в зависимости от изменений второй.
Назначение корреляционного анализа
Зависимость устанавливается тогда, когда начинается выявление коэффициента корреляции. Этот метод отличается от анализа регрессии, так как здесь только один показатель, рассчитываемый при помощи корреляции. Интервал изменяется от +1 до -1. Если она плюсовая, то повышение первой величины способствует повышению 2-й. Если минусовая, то повышение 1-й величины способствует понижению 2-й. Чем выше коэффициент, тем сильнее одна величина влияет на 2-ю.
Важно! При 0-м коэффициенте зависимости между величинами нет.
Расчет коэффициента корреляции
Разберем расчёт на нескольких образцах. К примеру, есть табличные данные, где по месяцам описаны в отдельных столбцах траты на рекламное продвижение и объём продаж. Исходя из таблицы, будем выяснять уровень зависимости объема продаж от денег, затраченных на рекламное продвижение.
Способ 1: определение корреляции через Мастер функций
КОРРЕЛ – функция, позволяющая реализовать корреляционный анализ. Общий вид — КОРРЕЛ(массив1;массив2). Подробная инструкция:
- Необходимо произвести выделение ячейки, в которой планируется выводить итог расчета. Нажать «Вставить функцию», находящуюся слева от текстового поля для ввода формулы.
- Открывается «Мастер функций». Здесь необходимо найти КОРРЕЛ, кликнуть на нее, затем на «ОК».
- Открылось окошко аргументов. В строку «Массив1» необходимо ввести координаты интервалы 1-го из значений. В рассматриваемом примере — это столбец «Величина продаж». Нужно просто произвести выделение всех ячеек, которые находятся в этой колонке. В строку «Массив2» аналогично необходимо добавить координаты второй колонки. В рассматриваемом примере — это столбец «Затраты на рекламу».
- После введения всех диапазонов кликаем на кнопку «ОК».
Коэффициент отобразился в той ячейке, которая была указана в начале наших действий. Полученный результат 0,97. Этот показатель отображает высокую зависимость первой величины от второй.
4
Способ 2: вычисление корреляции с помощью Пакета анализа
Существует еще один метод определения корреляции. Здесь используется одна из функций, находящаяся в пакете анализа. Перед ее использованием нужно провести активацию инструмента. Подробная инструкция:
- Переходим в раздел «Файл».
- Открылось новое окошко, в котором нужно кликнуть на раздел «Параметры».
- Жмём на «Надстройки».
- Находим в нижней части элемент «Управление». Здесь необходимо выбрать из контекстного меню «Надстройки Excel» и кликнуть «ОК».
- Открылось специальное окно надстроек. Ставим галочку рядом с элементом «Пакет анализа». Кликаем «ОК».
- Активация прошла успешно. Теперь переходим в «Данные». Появился блок «Анализ», в котором необходимо кликнуть «Анализ данных».
- В новом появившемся окошке выбираем элемент «Корреляция» и жмем на «ОК».
- На экране появилось окошко настроек анализа. В строчку «Входной интервал» необходимо ввести диапазон абсолютно всех колонок, принимающих участие в анализе. В рассматриваемом примере — это столбики «Величина продаж» и «Затраты на рекламу». В настройках отображения вывода изначально выставлен параметр «Новый рабочий лист», что означает показ результатов на другом листе. По желанию можно поменять локацию вывода результата. После проведения всех настроек нажимаем на «ОК».
Вывелись итоговые показатели. Результат такой же, как и в первом методе – 0,97.
Определение и вычисление множественного коэффициента корреляции в MS Excel
Для выявления уровня зависимости нескольких величин применяются множественные коэффициенты. В дальнейшем итоги сводятся в отдельную табличку, именуемую корреляционной матрицей.
- В разделе «Данные» находим уже известный блок «Анализ» и жмем «Анализ данных».
- В отобразившемся окошке жмем на элемент «Корреляция» и кликаем на «ОК».
- В строку «Входной интервал» вбиваем интервал по трём или более столбцам исходной таблицы. Диапазон можно ввести вручную или же просто выделить его ЛКМ, и он автоматически отобразится в нужной строчке. В «Группирование» выбираем подходящий способ группировки. В «Параметр вывода» указывает место, в которое будут выведены результаты корреляции. Кликаем «ОК».
- Готово! Построилась матрица корреляции.
Коэффициент парной корреляции в Excel
Разберем, как правильно проводить коэффициент парной корреляции в табличном процессоре Excel.
Расчет коэффициента парной корреляции в Excel
К примеру, у вас есть значения величин х и у.
12
Х – это зависимая переменна, а у – независимая. Необходимо найти направление и силу связи между этими показателями. Пошаговая инструкция:
- Выявим средние показатели величин при помощи функции СРЗНАЧ.
- Произведем расчет каждого х и хсредн, у и усредн при помощи оператора «-».
- Производим перемножение вычисленных разностей.
- Вычисляем сумму показателей в этом столбце. Числитель – найденный результат.
- Посчитаем знаменатели разницы х и х-средн, у и у-средн. Для этого произведем возведение в квадрат.
- Используя функцию АВТОСУММА, найдем показатели в полученных столбиках. Производим перемножение. При помощи функции КОРЕНЬ возводим результат в квадрат.
- Производим подсчет частного, используя значения знаменателя и числителя.
- КОРРЕЛ – интегрированная функция, которая позволяет предотвратить проведение сложнейших расчетов. Заходим в «Мастер функций», выбираем КОРРЕЛ и указываем массивы показателей х и у. Строим график, отображающий полученные значения.
Матрица парных коэффициентов корреляции в Excel
Разберем, как проводить подсчет коэффициентов парных матриц. К примеру, есть матрица из четырех переменных.
22
- Заходим в «Анализ данных», находящийся в блоке «Анализ» вкладки «Данные». В отобразившемся списке выбираем «Корелляция».
- Выставляем все необходимые настройки. «Входной интервал» – интервал всех четырех колонок. «Выходной интервал» – место, в котором желаем отобразить итоги. Кликаем на кнопку «ОК».
- В выбранном месте построилась матрица корреляции. Каждое пересечение строки и столбца – коэффициенты корреляции. Цифра 1 отображается при совпадающих координатах.
Функция КОРРЕЛ для определения взаимосвязи и корреляции в Excel
КОРРЕЛ – функция, применяемая для подсчета коэффициента корреляции между 2-мя массивами. Разберем на четырех примерах все способности этой функции.
Примеры использования функции КОРРЕЛ в Excel
Первый пример. Есть табличка, в которой расписана информация об усредненных показателях заработной платы работников компании на протяжении одиннадцати лет и курсе $. Необходимо выявить связь между этими 2-умя величинами. Табличка выглядит следующим образом:
24
Алгоритм расчёта выглядит следующим образом:
25
Отображенный показатель близок к 1. Результат:
26
Определение коэффициента корреляции влияния действий на результат
Второй пример. Два претендента обратились за помощью к двум разным агентствам для реализации рекламного продвижения длительностью в пятнадцать суток. Каждые сутки проводился социальный опрос, определяющий степень поддержки каждого претендента. Любой опрошенный мог выбрать одного из двух претендентов или же выступить против всех. Необходимо определить, как сильно повлияло каждое рекламное продвижение на степень поддержки претендентов, какая компания эффективней.
27
Используя нижеприведенные формулы, рассчитаем коэффициент корреляции:
- =КОРРЕЛ(А3:А17;В3:В17).
- =КОРРЕЛ(А3:А17;С3:С17).
28
Из полученных результатов становится понятно, что степень поддержки 1-го претендента повышалась с каждыми сутками проведения рекламного продвижения, следовательно, коэффициент корреляции приближается к 1. При запуске рекламы другой претендент обладал большим числом доверия, и на протяжении 5 дней была положительная динамика. Потом степень доверия понизилась и к пятнадцатым суткам опустилась ниже изначальных показателей. Низкие показатели говорят о том, что рекламное продвижение отрицательно повлияло на поддержку. Не стоит забывать, что на показатели могли повлиять и остальные сопутствующие факторы, не рассматриваемые в табличной форме.
Анализ популярности контента по корреляции просмотров и репостов видео
Третий пример. Человек для продвижения собственных роликов на видеохостинге Ютуб применяет соцсети для рекламирования канала. Он замечает, что существует некая взаимосвязь между числом репостов в соцсетях и количеством просмотров на канале. Можно ли про помощи инструментов табличного процессора произвести прогноз будущих показателей? Необходимо выявить резонность применения уравнения линейной регрессии для прогнозирования числа просмотров видеозаписей в зависимости от количества репостов. Табличка со значениями:
29
Теперь необходимо провести определение наличия связи между 2-мя показателями по нижеприведенной формуле:
0,7;ЕСЛИ(КОРРЕЛ(A3:A8;B3:B8)>0,7;»Сильная прямая зависимость»;»Сильная обратная зависимость»);»Слабая зависимость или ее отсутствие»)’ >
Если полученный коэффициент выше 0,7, то целесообразней применять функцию линейной регрессии. В рассматриваемом примере делаем:
30
Теперь производим построение графика:
31
Применяем это уравнение, чтобы определить число просматриваний при 200, 500 и 1000 репостов: =9,2937*D4-206,12. Получаем следующие результаты:
32
Функция ПРЕДСКАЗ позволяет определить число просмотров в моменте, если было проведено, к примеру, двести пятьдесят репостов. Применяем: 0,7;ПРЕДСКАЗ(D7;B3:B8;A3:A8);»Величины не взаимосвязаны»)’ следующие результаты:
33
Особенности использования функции КОРРЕЛ в Excel
Данная функция имеет нижеприведенные особенности:
- Не учитываются ячейки пустого типа.
- Не учитываются ячейки, в которых находится информация типа Boolean и Text.
- Двойное отрицание «—» применяется для учёта логических величин в виде чисел.
- Количество ячеек в исследуемых массивах обязаны совпадать, иначе будет выведено сообщение #Н/Д.
Оценка статистической значимости коэффициента корреляции
При проверке значимости корреляционного коэффициента нулевая гипотеза состоит в том, что показатель имеет значение 0, а альтернативная не имеет. Для проверки применяется нижеприведенная формула:
34
Заключение
Корреляционный анализ в табличном процессоре – это простой и автоматизированный процесс. Для его выполнения необходимо знать всего лишь, где находятся нужные инструменты и как их активировать через настройки программы.
Проверить значимость коэффициента парной корреляции excel. Как найти корреляцию в Excel
«Корреляция» в переводе с латинского обозначает «соотношение», «взаимосвязь». Количественная характеристика взаимосвязи может быть получена при вычислении коэффициента корреляции. Этот популярный в статистических анализах коэффициент показывает, связаны ли какие-либо параметры друг с другом (например, рост и вес; уровень интеллекта и успеваемость; количество травм и продолжительность работы).
Использование корреляции
Вычисление корреляции особенно широко используется в экономике, социологических исследованиях, медицине и биометрии — везде, где можно получить два массива данных, между которыми может обнаружиться связь.
Рассчитать корреляцию можно вручную, выполняя несложные арифметические действия. Однако процесс вычисления оказывается очень трудоемким, если набор данных велик. Особенность метода в том, что он требует сбора большого количества исходных данных, чтобы наиболее точно отобразить, есть ли связь между признаками. Поэтому серьезное использование корреляционного анализа невозможно без применения вычислительной техники. Одной из наиболее популярных и доступных программ для решения этой задачи является .
Как выполнить корреляцию в Excel?
Самым трудоемким этапом определения корреляции является набор массива данных. Сравниваемые данные располагаются обычно в двух колонках или строчках. Таблицу следует делать без пропусков в ячейках. Современные версии Excel (с 2007 и младше) не требуют установок дополнительных настроек для статистических расчетов; необходимые манипуляции можно сделать :
- Выбрать пустую ячейку, в которую будет выведен результат расчетов.
- Нажать в главном меню Excel пункт «Формулы».
- Среди кнопок, сгруппированных в «Библиотеку функций», выбрать «Другие функции».
- В выпадающих списках выбрать функцию расчета корреляции (Статистические — КОРРЕЛ).
- В Excel откроется панель «Аргументы функции». «Массив 1» и «Массив 2» — это диапазоны сравниваемых данных. Для автоматического заполнения этих полей можно просто выделить нужные ячейки таблицы.
- Нажать «ОК», закрыв окно аргументов функции. В ячейке появится подсчитанный коэффициент корреляции.
Корреляция может быть прямая (если коэффициент больше нуля) и обратная (от -1 до 0).
Первая означает, что при росте одного параметра растет и другой. Обратная (отрицательная) корреляция отражает факт, что при росте одной переменной другая уменьшается.
Корреляция может быть близка к нулю. Это обычно свидетельствует, что исследуемые параметры не связаны друг с другом. Но иногда нулевая корреляция возникает, если сделана неудачная выборка, которая не отразила связь, либо связь имеет сложный нелинейный характер.
Если коэффициент показывает среднюю или сильную взаимосвязь (от ±0,5 до ±0,99), следует помнить, что это лишь статистическая взаимосвязь, которая вовсе не гарантирует влияние одного параметра на другой. Также нельзя исключать ситуации, что оба параметра независимы друг от друга, но на них воздействует какой-нибудь третий неучтенный фактор. Excel помогает моментально вычислить коэффициент корреляции, но обычно только количественных методов недостаточно для установления причинно-следственных связей в соотносимых выборках.
Вычислим коэффициент корреляции и ковариацию для разных типов взаимосвязей случайных величин.
Коэффициент корреляции (критерий корреляции Пирсона, англ. Pearson Product Moment correlation coefficient) определяет степень линейной взаимосвязи между случайными величинами.
Как следует из определения, для вычисления коэффициента корреляции требуется знать распределение случайных величин Х и Y. Если распределения неизвестны, то для оценки коэффициента корреляции используется выборочный коэффициент корреляции r (еще он обозначается как R xy или r xy ) :
где S x – стандартное отклонение выборки случайной величины х, вычисляемое по формуле:
Как видно из формулы для расчета корреляции , знаменатель (произведение стандартных отклонений) просто нормирует числитель таким образом, что корреляция оказывается безразмерным числом от -1 до 1. Корреляция и ковариация предоставляют одну и туже информацию (если известны стандартные отклонения ), но корреляцией удобнее пользоваться, т.к. она является безразмерной величиной.
Рассчитать коэффициент корреляции и ковариацию выборки в MS EXCEL не представляет труда, так как для этого имеются специальные функции КОРРЕЛ() и КОВАР() . Гораздо сложнее разобраться, как интерпретировать полученные значения, большая часть статьи посвящена именно этому.
Теоретическое отступление
Напомним, что корреляционной связью называют статистическую связь, состоящую в том, что различным значениям одной переменной соответствуют различные средние значения другой (с изменением значения Х среднее значение Y изменяется закономерным образом). Предполагается, что обе переменные Х и Y являются случайными величинами и имеют некий случайный разброс относительно их среднего значения .
Примечание . Если случайную природу имеет только одна переменная, например, Y, а значения другой являются детерминированными (задаваемыми исследователем), то можно говорить только о регрессии.
Таким образом, например, при исследовании зависимости среднегодовой температуры нельзя говорить о корреляции температуры и года наблюдения и, соответственно, применять показатели корреляции с соответствующей их интерпретацией.
Корреляционная связь между переменными может возникнуть несколькими путями:
- Наличие причинной зависимости между переменными. Например, количество инвестиций в научные исследования (переменная Х) и количество полученных патентов (Y). Первая переменная выступает как независимая переменная (фактор) , вторая — зависимая переменная (результат) . Необходимо помнить, что зависимость величин обуславливает наличие корреляционной связи между ними, но не наоборот.
- Наличие сопряженности (общей причины). Например, с ростом организации растет фонд оплаты труда (ФОТ) и затраты на аренду помещений. Очевидно, что неправильно предполагать, что аренда помещений зависит от ФОТ. Обе этих переменных во многих случаях линейно зависят от количества персонала.
- Взаимовлияние переменных (при изменении одной, вторая переменная изменяется, и наоборот). При таком подходе допустимы две постановки задачи; любая переменная может выступать как в роли независимой переменной и в роли зависимой.
Таким образом, показатель корреляции показывает, насколько сильна линейная взаимосвязь между двумя факторами (если она есть), а регрессия позволяет прогнозировать один фактор на основе другого.
Корреляция , как и любой другой статистический показатель, при правильном применении может быть полезной, но она также имеет и ограничения по использованию. Если показывает четко выраженную линейную зависимость или полное отсутствие взаимосвязи, то корреляция замечательно это отразит. Но, если данные показывают нелинейную взаимосвязь (например, квадратичную), наличие отдельных групп значений или выбросов, то вычисленное значение коэффициента корреляции может ввести в заблуждение (см. файл примера ).
Корреляция близкая к 1 или -1 (т.е. близкая по модулю к 1) показывает сильную линейную взаимосвязь переменных, значение близкое к 0 показывает отсутствие взаимосвязи. Положительная корреляция означает, что с ростом одного показателя другой в среднем увеличивается, а при отрицательной – уменьшается.
Для вычисления коэффициента корреляции требуется, чтобы сопоставляемые переменные удовлетворяли следующим условиям:
- количество переменных должно быть равно двум;
- переменные должны быть количественными (например, частота, вес, цена). Вычисленное среднее значение этих переменных имеет понятный смысл: средняя цена или средний вес пациента. В отличие от количественных, качественные (номинальные) переменные принимают значения лишь из конечного набора категорий (например, пол или группа крови). Этим значениям условно сопоставлены числовые значения (например, женский пол – 1, а мужской – 2). Понятно, что в этом случае вычисление среднего значения , которое требуется для нахождения корреляции , некорректно, а значит некорректно и вычисление самой корреляции ;
- переменные должны быть случайными величинами и иметь .
Двумерные данные могут иметь различную структуру. Для работы с некоторыми из них требуются определенные подходы:
- Для данных с нелинейной связью корреляцию нужно использовать с осторожностью. Для некоторых задач бывает полезно преобразовать одну или обе переменных так, чтобы получить линейную взаимосвязь (для этого требуется сделать предположение о виде нелинейной связи, чтобы предложить нужный тип преобразования).
- С помощью диаграммы рассеяния у некоторых данных можно наблюдать неравную вариацию (разброс). Проблема неодинаковой вариации состоит в том, что места с высокой вариацией не только предоставляют наименее точную информацию, но и оказывают наибольшее влияние при расчете статистических показателей. Эту проблему также часто решают с помощью преобразования данных, например, с помощью логарифмирования.
- У некоторых данных можно наблюдать разделение на группы (clustering), что может свидетельствовать о необходимости разделения совокупности на части.
- Выброс (резко отклоняющееся значение) может исказить вычисленное значение коэффициента корреляции. Выброс может быть причиной случайности, ошибки при сборе данных или могут действительно отражать некую особенность взаимосвязи. Так как выброс сильно отклоняется от среднего значения, то он вносит большой вклад при расчете показателя. Часто расчет статистических показателей производят с и без учета выбросов.
Использование MS EXCEL для расчета корреляции
В качестве примера возьмем 2 переменные Х и Y и, соответственно, выборку состоящую из нескольких пар значений (Х i ; Y i). Для наглядности построим .
Примечание : Подробнее о построении диаграмм см. статью . В файле примера для построения диаграммы рассеяния использована , т.к. мы здесь отступили от требования случайности переменной Х (это упрощает генерацию различных типов взаимосвязей: построение трендов и заданный разброс). В случае реальных данных необходимо использовать диаграмму типа Точечная (см. ниже).
Расчеты корреляции проведем для различных случаев взаимосвязи между переменными: линейной, квадратичной и при отсутствии связи .
Примечание : В файле примера можно задать параметры линейного тренда (наклон, пересечение с осью Y) и степень разброса относительно этой линии тренда. Также можно настроить параметры квадратичной зависимости.
В файле примера для построения диаграммы рассеяния в случае отсутствия зависимости переменных использована диаграмма типа Точечная. В этом случае точки на диаграмме располагаются в виде облака.
Примечание : Обратите внимание, что изменяя масштаб диаграммы по вертикальной или горизонтальной оси, облаку точек можно придать вид вертикальной или горизонтальной линии. Понятно, что при этом переменные останутся независимыми.
Как было сказано выше, для расчета коэффициента корреляции в MS EXCEL существует функций КОРРЕЛ() . Также можно воспользоваться аналогичной функцией PEARSON() , которая возвращает тот же результат.
Для того, чтобы удостовериться, что вычисления корреляции производятся функцией КОРРЕЛ() по вышеуказанным формулам, в файле примера приведено вычисление корреляции с помощью более подробных формул:
Примечание : Квадрат коэффициента корреляции r равен коэффициенту детерминации R2, который вычисляется при построении линии регрессии с помощью функции КВПИРСОН() . Значение R2 также можно вывести на диаграмме рассеяния , построив линейный тренд с помощью стандартного функционала MS EXCEL (выделите диаграмму, выберите вкладку Макет , затем в группе Анализ нажмите кнопку Линия тренда и выберите Линейное приближение ). Подробнее о построении линии тренда см., например, в .
Использование MS EXCEL для расчета ковариации
Ковариация близка по смыслу с (также является мерой разброса) с тем отличием, что она определена для 2-х переменных, а дисперсия — для одной. Поэтому, cov(x;x)=VAR(x).
Для вычисления ковариации в MS EXCEL (начиная с версии 2010 года) используются функции КОВАРИАЦИЯ.Г() и КОВАРИАЦИЯ.В() . В первом случае формула для вычисления аналогична вышеуказанной (окончание .Г обозначает Генеральная совокупность ), во втором – вместо множителя 1/n используется 1/(n-1), т.е. окончание .В обозначает Выборка .
Примечание : Функция КОВАР() , которая присутствует в MS EXCEL более ранних версий, аналогична функции КОВАРИАЦИЯ.Г() .
Примечание : Функции КОРРЕЛ() и КОВАР() в английской версии представлены как CORREL и COVAR. Функции КОВАРИАЦИЯ.Г() и КОВАРИАЦИЯ.В() как COVARIANCE.P и COVARIANCE.S.
Дополнительные формулы для расчета ковариации :
Эти формулы используют свойство ковариации :
Если переменные x и y независимые, то их ковариация равна 0. Если переменные не являются независимыми, то дисперсия их суммы равна:
VAR(x+y)= VAR(x)+ VAR(y)+2COV(x;y)
А дисперсия их разности равна
VAR(x-y)= VAR(x)+ VAR(y)-2COV(x;y)
Оценка статистической значимости коэффициента корреляции
Для того чтобы проверить гипотезу, мы должны знать распределение случайной величины, т.е. коэффициента корреляции r. Обычно, проверку гипотезы осуществляют не для r, а для случайной величины t r:
которая имеет с n-2 степенями свободы.
Если вычисленное значение случайной величины |t r | больше, чем критическое значение t α,n-2 (α- заданный ), то нулевую гипотезу отклоняют (взаимосвязь величин является статистически значимой).
Надстройка Пакет анализа
В для вычисления ковариации и корреляции имеются одноименные инструменты анализа .
После вызова инструмента появляется диалоговое окно, которое содержит следующие поля:
- Входной интервал : нужно ввести ссылку на диапазон с исходными данными для 2-х переменных
- Группирование : как правило, исходные данные вводятся в 2 столбца
- Метки в первой строке : если установлена галочка, то Входной интервал должен содержать заголовки столбцов. Рекомендуется устанавливать галочку, чтобы результат работы Надстройки содержал информативные столбцы
- Выходной интервал : диапазон ячеек, куда будут помещены результаты вычислений. Достаточно указать левую верхнюю ячейку этого диапазона.
Надстройка возвращает вычисленные значения корреляции и ковариации (для ковариации также вычисляются дисперсии обоих случайных величин).
Вы уже сталкивались с необходимостью рассчитать степень связи двух статистических величин и определить формулу, по которой они коррелируют? Нормальный человек может спросить, зачем это вообще может быть нужно. Как ни странно, это действительно бывает нужно. Знание достоверных корреляций может помочь вам зарабатывать бешенные деньги, если вы, скажем, биржевой трейдер. Проблема в том, что почему-то эти корреляции никто не раскрывает (удивительно, не правда ли?).
Давайте посчитаем их сами! Для примера, я решил попробовать посчитать корреляцию рубля к доллару через евро. Давайте разберем, как это делается подробно.
Эта статья рассчитана на продвинутый уровень владения Microsoft Excel. Если у вас нет времени читать всю статью, вы можете скачать файл и разобраться с ним самостоятельно.
Если вы часто сталкиваетесь с необходимостью сделать что-то подобное , настоятельно рекомендую подумать о покупке книги Статистические вычисления в среде Excel .
Что важно знать о корреляциях
Чтобы рассчитать достоверную корреляцию, необходимо иметь достоверную выборку, чем больше она будет, тем достовернее будет результат. Для целей данного примера я взял ежедневную выборку курсов валют за 10 лет. Данные есть в свободном доступе, я их брал с сайта http://oanda.com .
Что я, собственно, сделал
(1) Когда у меня были исходные данные, я начал с того, что проверил степень корреляции этих двух наборов данных. Для этого я воспользовался функцией CORREL (КОРРЕЛ) — о ней есть немного информации . Она возвращает степень корреляции двух диапазонов данных. Результат, прямо скажем, получился не особенно впечатляющим (всего около 70%). А вообще говоря, степень соотношения двух величин принято считать, как квадрат этой величины, то есть корреляция получилась достоверной приблизительно на 49%. Это очень мало!
(2) Мне это показалось очень странным. Какие ошибки могли закрасться в мои расчеты? Поэтому я решил построить график и посмотреть, что могло произойти. График был простоен специально с разбивкой по годам, чтобы можно было визуально увидеть, где рвет корреляцию. График получился вот таким
(3) Из графика очевидно, что на диапазоне около 35 рублей за евро корреляцию начинает рвать на две части. Из-за этого она и получилась недостоверной. Необходимо было определить в связи с чем это происходит.
(4) По цвету видно, что эти данные относятся к 2007, 2008, 2009 годам. Конечно! Периоды экономических пиков и спадов обычно недостоверны статистически, что и произошло в данном случае. Поэтому я попробовал исключить из данных эти периоды (ну и для проверки, я проверрил степень корреляции данных в этом периоде). Степень корреляции только этих данных составляет 0.01%, то есть она отсутствует в принципе. Зато без них данные коррелируют приблизительно на 81%. Это уже достаточно достоверная корреляция. Вот график с функцией.
Дальнейшие шаги
Теоретически, функцию корреляции можно уточнить, если перевести ее из линейной в экспоненциальную или логарифмическую. При этом статистическая достоверность корреляции вырастает приблизительно на один процент, но сложность применения формулы повышается неимоверно. Поэтому для себя я ставлю вопрос: а нужно ли это на самом деле? Решать вам — для каждого конкретного случая.
Заметьте! Решение вашей конкретной задачи будет выглядеть аналогично данному примеру, включая все таблицы и поясняющие тексты, представленные ниже, но с учетом ваших исходных данных…
Задача:
Имеется связанная выборка из 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 равно единице, то можно говорить о функциональной связи между величинами, то есть одну величину можно выразить через другую посредством математической функции.
На практике, для вычисления коэффициента корреляции чаще используется формула (1.4) т.к. она требует меньше вычислений. Однако если предварительно была вычислена ковариация cov(X,Y) , то выгоднее использовать формулу (1.1), т.к. кроме собственно значения ковариации можно воспользоваться и результатами промежуточных вычислений.
1.1 Вычислим коэффициент корреляции по формуле (1.4) , для этого вычислим значения x k 2 , y k 2 и x k y k и занесем их в таблицу 1.
k |
x k | y k | х k 2 | y k 2 | х k y k |
| 1 | 2 | 3 | 4 | 5 | 6 |
| 1 | 25.2 | 30.8 | 635.04000 | 948.64000 | 776.16000 |
| 2 | 26.4 | 29.4 | 696.96000 | 864.36000 | 776.16000 |
| 3 | 26.0 | 30.2 | 676.00000 | 912.04000 | 785.20000 |
| 4 | 25.8 | 30.5 | 665.64000 | 930.25000 | 786.90000 |
| 5 | 24.9 | 31.4 | 620.01000 | 985.96000 | 781.86000 |
| 6 | 25.7 | 30.3 | 660.49000 | 918.09000 | 778.71000 |
| 7 | 25.7 | 30.4 | 660.49000 | 924.16000 | 781.28000 |
| 8 | 25.7 | 30.5 | 660.49000 | 930.25000 | 783.85000 |
| 9 | 26.1 | 29.9 | 681.21000 | 894.01000 | 780.39000 |
| 10 | 25.8 | 30.4 | 665.64000 | 924.16000 | 784.32000 |
| 11 | 25.9 | 30.3 | 670.81000 | 918.09000 | 784.77000 |
| 12 | 26.2 | 30.5 | 686.44000 | 930.25000 | 799.10000 |
| 13 | 25.6 | 30.6 | 655.36000 | 936.36000 | 783.36000 |
| 14 | 25.4 | 31 | 645.16000 | 961.00000 | 787.40000 |
| 15 | 26.6 | 29.6 | 707.56000 | 876.16000 | 787.36000 |
| 16 | 26.2 | 30.4 | 686.44000 | 924.16000 | 796.48000 |
| 17 | 26 | 30.7 | 676.00000 | 942.49000 | 798.20000 |
| 18 | 22.1 | 31.6 | 488.41000 | 998.56000 | 698.36000 |
| 19 | 25.9 | 30.5 | 670.81000 | 930.25000 | 789.95000 |
| 20 | 25.8 | 30.6 | 665.64000 | 936.36000 | 789.48000 |
| 21 | 25.9 | 30.7 | 670.81000 | 942.49000 | 795.13000 |
| 22 | 26.3 | 30.1 | 691.69000 | 906.01000 | 791.63000 |
| 23 | 26.1 | 30.6 | 681.21000 | 936.36000 | 798.66000 |
| 24 | 26 | 30.5 | 676.00000 | 930.25000 | 793.00000 |
| 25 | 26.4 | 30.7 | 696.96000 | 942.49000 | 810.48000 |
| 26 | 25.8 | 30.8 | 665.64000 | 948.64000 | 794.64000 |
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
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 нет. В противном случае, экспериментальные данные не противоречат гипотезе о зависимости случайных величин.
Искомое значение t кр.α располагается на пересечении строки соответствующей числу степеней свободы и столбца соответствующего заданному уровню значимости α .
В нашем случае число степеней свободы есть n — 2 = 26 — 2 = 24 и α = 0.05 , что соответствует критическому значению критерия t кр.α = 2.064 (см. табл. 2)
Таблица 2 t -распределение
| Число степеней свободы (n — 2) |
α = 0.1 | α = 0.05 | α = 0.02 | α = 0.01 | α = 0.002 | α = 0.001 |
| 1 | 6.314 | 12.706 | 31.821 | 63.657 | 318.31 | 636.62 |
| 2 | 2.920 | 4.303 | 6.965 | 9.925 | 22.327 | 31.598 |
| 3 | 2.353 | 3.182 | 4.541 | 5.841 | 10.214 | 12.924 |
| 4 | 2.132 | 2.776 | 3.747 | 4.604 | 7.173 | 8.610 |
| 5 | 2.015 | 2.571 | 3.365 | 4.032 | 5.893 | 6.869 |
| 6 | 1.943 | 2.447 | 3.143 | 3.707 | 5.208 | 5.959 |
| 7 | 1.895 | 2.365 | 2.998 | 3.499 | 4.785 | 5.408 |
| 8 | 1.860 | 2.306 | 2.896 | 3.355 | 4.501 | 5.041 |
| 9 | 1.833 | 2.262 | 2.821 | 3.250 | 4.297 | 4.781 |
| 10 | 1.812 | 2.228 | 2.764 | 3.169 | 4.144 | 4.587 |
| 11 | 1.796 | 2.201 | 2.718 | 3.106 | 4.025 | 4.437 |
| 12 | 1.782 | 2.179 | 2.681 | 3.055 | 3.930 | 4.318 |
| 13 | 1.771 | 2.160 | 2.650 | 3.012 | 3.852 | 4.221 |
| 14 | 1.761 | 2.145 | 2.624 | 2.977 | 3.787 | 4.140 |
| 15 | 1.753 | 2.131 | 2.602 | 2.947 | 3.733 | 4.073 |
| 16 | 1.746 | 2.120 | 2.583 | 2.921 | 3.686 | 4.015 |
| 17 | 1.740 | 2.110 | 2.567 | 2.898 | 3.646 | 3.965 |
| 18 | 1.734 | 2.101 | 2.552 | 2.878 | 3.610 | 3.922 |
| 19 | 1.729 | 2.093 | 2.539 | 2.861 | 3.579 | 3.883 |
| 20 | 1.725 | 2.086 | 2.528 | 2.845 | 3.552 | 3.850 |
| 21 | 1.721 | 2.080 | 2.518 | 2.831 | 3.527 | 3.819 |
| 22 | 1.717 | 2.074 | 2.508 | 2.819 | 3.505 | 3.792 |
| 23 | 1.714 | 2.069 | 2.500 | 2.807 | 3.485 | 3.767 |
| 24 | 1.711 | 2.064 | 2.492 | 2.797 | 3.467 | 3.745 |
| 25 | 1.708 | 2.060 | 2.485 | 2.787 | 3.450 | 3.725 |
| 26 | 1.706 | 2.056 | 2.479 | 2.779 | 3.435 | 3.707 |
| 27 | 1.703 | 2.052 | 2.473 | 2.771 | 3.421 | 3.690 |
| 28 | 1.701 | 2.048 | 2.467 | 2.763 | 3.408 | 3.674 |
| 29 | 1.699 | 2.045 | 2.462 | 2.756 | 3.396 | 3.659 |
| 30 | 1.697 | 2.042 | 2.457 | 2.750 | 3.385 | 3.646 |
| 40 | 1.684 | 2.021 | 2.423 | 2.704 | 3.307 | 3.551 |
| 60 | 1.671 | 2.000 | 2.390 | 2.660 | 3.232 | 3.460 |
| 120 | 1.658 | 1.980 | 2.358 | 2.617 | 3.160 | 3.373 |
| ∞ | 1.645 | 1.960 | 2.326 | 2.576 | 3.090 | 3.291 |
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.
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. Строим диаграмму рассеяния (корреляционное поле) и график линии регрессии.
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).
ЛАБОРАТОРНАЯ РАБОТА
КОРРЕЛЯЦИОННЫЙ АНАЛИЗ В EXCEL
Корреляционный анализ состоит в определении степени связи между двумя случайными величинами X и Y. В качестве меры такой связи используется коэффициент корреляции. Коэффициент корреляции оценивается по выборке объема п связанных пар наблюдений (x i , y i) из совместной генеральной совокупности X и Y. Для оценки степени взаимосвязи величин X и Y, измеренных в количественных шкалах, используетсякоэффициент линейной корреляции (коэффициент Пирсона), предполагающий, что выборки X и Y распределены по нормальному закону.
Коэффициент корреляции изменяется от -1 (строгая обратная линейная зависимость) до 1 (строгая прямая пропорциональная зависимость). При значении 0 линейной зависимости между двумя выборками нет.
Общая классификация корреляционных связей (по Ивантер Э.В., Коросову А.В., 1992):
Существует несколько типов коэффициентов корреляции, что зависит от переменных Х иY, которые могут быть измерены в разных шкалах. Именно этот факт и определяет выбор соответствующего коэффициента корреляции (см. табл. 13):
В MS Excel для вычисления парных коэффициентов линейной корреляции используется специальная функция КОРРЕЛ (массив1; массив2),
Пример 1: 10 школьникам были даны тесты на наглядно-образное и вербальное мышление. Измерялось среднее время решения заданий теста в секундах. Исследователя интересует вопрос: существует ли взаимосвязь между временем решения этих задач? Переменная X — обозначает среднее время решения наглядно-образных, а переменная Y- среднее время решения вербальных заданий тестов.
Р
ешение: Для выявления степени взаимосвязи, прежде всего, необходимо ввести данные в таблицу MS Excel (см. табл., рис. 1). Затем вычисляется значение коэффициента корреляции. Для этого курсор установите в ячейку C1. На панели инструментов нажмите кнопку Вставка функции (fx).
В появившемся диалоговом окне Мастер функций выберите категорию Статистические и функциюКОРРЕЛ , после чего нажмите кнопку ОК. Указателем мыши введите диапазон данных выборки Х в поле массив1 (А1:А10). В поле массив2 введите диапазон данных выборки У (В1:В10). Нажмите кнопку ОК. В ячейке С1 появится значение коэффициента корреляции — 0,54119. Далее необходимо посмотреть на абсолютное число коэффициента корреляции и определить тип связи (тесная, слабая, средняя и т.д.)
Рис. 1. Результаты вычисления коэффициента корреляции
Таким образом, связь между временем решения наглядно-образных и вербальных заданий теста не доказана.
Задание 1. Имеются данные по 20 сельскохозяйственным хозяйствам. Найтикоэффициент корреляции между величинами урожайности зерновых культур и качеством земли и оценить его значимость. Данные приведены в таблице.
Таблица 2. Зависимость урожайности зерновых культур от качества земли
Номер хозяйства
Качество земли, балл
Урожайность, ц/га
Задание 2. Определите, имеется ли связь между временем работы спортивного тренажера для фитнеса (тыс. часов) и стоимость его ремонта (тыс. руб.):
Время работа тренажера (тыс. часов)
Стоимость ремонта (тыс. руб.)
1.2 Множественная корреляция в MS Excel
При большом числе наблюдений, когда коэффициенты корреляции необходимо последовательно вычислять для нескольких выборок, для удобства получаемые коэффициенты сводят в таблицы, называемые корреляционными матрицами .
Корреляционная матрица — это квадратная таблица, в которой на пересечении соответствующих строк и столбцов находятся коэффициент корреляции между соответствующими параметрами.
В MS Excel для вычисления корреляционных матриц используется процедура Корреляция из пакета Анализ данных. Процедура позволяет получить корреляционную матрицу, содержащую коэффициенты корреляции между различными параметрами.
Для реализации процедуры необходимо:
1. выполнить команду Сервис — Анализ данных ;
2. в появившемся списке Инструменты анализа выбрать строку Корреляция и нажать кнопку ОК ;
3. в появившемся диалоговом окне указать Входной интервал , то есть ввести ссылку на ячейки, содержащие анализируемые данные. Входной интервал должен содержать не менее двух столбцов.
4. в разделе Группировка переключатель установить в соответствии с введенными данными (по столбцам или по строкам);
5. указать выходной интервал , то есть ввести ссылку на ячейку, начиная с которой будут показаны результаты анализа. Размер выходного диапазона будет определен автоматически, и на экран будет выведено сообщение в случае возможного наложения выходного диапазона на исходные данные. Нажать кнопку ОК .
В выходной диапазон будет выведена корреляционная матрица, в которой на пересечении каждых строки и столбца находится коэффициент корреляции между соответствующими параметрами. Ячейки выходного диапазона, имеющие совпадающие координаты строк и столбцов, содержат значение 1, так как каждый столбец во входном диапазоне полностью коррелирует сам с собой
Пример 2. Имеются ежемесячные данные наблюдений за состоянием погоды и посещаемостью музеев и парков (см. табл. 3). Необходимо определить, существует ли взаимосвязь между состоянием погоды и посещаемостью музеев и парков.
Таблица 3. Результаты наблюдений
Число ясных дней
Количество посетителей музея
Количество посетителей парка
Решение . Для выполнения корреляционного анализа введите в диапазон A1:G3 исходные данные (рис. 2). Затем в меню Сервис выберите пункт Анализ данных и далее укажите строку Корреляция . В появившемся диалоговом окне укажите Входной интервал (А2:С7). Укажите, что данные рассматриваются по столбцам. Укажите выходной диапазон (Е1) и нажмите кнопку ОК .
На рис. 33 видно, что корреляция между состоянием погоды и посещаемостью музея равна -0,92, а между состоянием погоды и посещаемостью парка — 0,97, между посещаемостью парка и музея — 0,92.
Таким образом, в результате анализа выявлены зависимости: сильная степень обратной линейной взаимосвязи между посещаемостью музея и количеством солнечных дней и практически линейная (очень сильная прямая) связь между посещаемостью парка и состоянием погоды. Между посещаемостью музея и парка имеется сильная обратная взаимосвязь.
Рис. 2. Результаты вычисления корреляционной матрицы из примера 2
Задание 3 . 10 менеджеров оценивались по методике экспертных оценок психологических характеристик личности руководителя. 15 экспертов производили оценку каждой психологической характеристики по пятибальной системе (см. табл. 4). Психолога интересует вопрос, в какой взаимосвязи находятся эти характеристики руководителя между собой.