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

Корреляция измеряет мощность и направление связи между x и y. На рисунке представлены различные типы корреляции в виде графиков рассеяния упорядоченных пар (x, y). По традиции переменная х размещается на горизонтальной оси, а y - на вертикальной.

График А являет собой пример положительной линейной корреляции: при увеличении х также увеличивается у, причем линейно. График В показывает нам пример отрицательной линейной корреляции, на котором при увеличении х у линейно уменьшается. На графике С мы видим отсутствие корреляции между х и у. Эти переменные никоим образом не влияют друг на друга.

Наконец, график D - это пример нелинейных отношений между переменными. По мере увеличения х у сначала уменьшается, потом меняет направление и увеличивается.

Оставшаяся часть статьи посвящена линейным взаимосвязям между зависимой и независимой переменными.

Коэффициент корреляции

Коэффициент корреляции, r, предоставляет нам как силу, так и направление связи между независимой и зависимой переменными. Значения r находятся в диапазоне между — 1.0 и + 1.0. Когда r имеет положительное значение, связь между х и у является положительной (график A на рисунке), а когда значение r отрицательно, связь также отрицательна (график В). Коэффициент корреляции, близкий к нулевому значению, свидетельствует о том, что между х и у связи не существует график С).

Сила связи между х и у определяется близостью коэффициента корреляции к - 1.0 или +- 1.0. Изучите следующий рисунок.

График A показывает идеальную положительную корреляцию между х и у при r = + 1.0. График В - идеальная отрицательная корреляция между х и у при r = — 1.0. Графики С и D - примеры более слабых связей между зависимой и независимой переменными.

Коэффициент корреляции, r, определяет, как силу, так и направление связи между зависимой и независимой переменными. Значения r находятся в диапазоне от — 1.0 (сильная отрицательная связь) до + 1.0 (сильная положительная связь). При r= 0 между переменными х и у нет никакой связи.

Мы можем вычислить фактический коэффициент корреляции с помощью следующего уравнения:

Ну и ну! Я знаю, что выглядит это уравнение как страшное нагромождение непонятных символов, но прежде чем ударяться в панику, давайте применим к нему пример с экзаменационной оценкой. Допустим, я хочу определить, существует ли связь между количеством часов, посвященных студентом изучению статистики, и финальной экзаменационной оценкой. Таблица, представленная ниже, поможет нам разбить это уравнение на несколько несложных вычислений и сделать их более управляемыми.

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

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

Использование Excel для вычисления коэффициентов корреляции

Я уверен, что, взглянув на эти ужасные вычисления коэффициентов корреляции, вы испытаете истинную радость, узнав, что программа Excel может выполнить за вас всю эту работу с помощью функции КОРРЕЛ со следующими характеристиками:

КОРРЕЛ (массив 1; массив 2),

массив 1 = диапазон данных для первой переменной,

массив 2 = диапазон данных для второй переменной.

Например, на рисунке показана функция КОРРЕЛ, используемая при вычислении коэффициента корреляции для примера с экзаменационной оценкой.

Где 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.1 Корреляционный анализ в MS Excel

Корреляционный анализ состоит в определении степени связи между двумя слу­чайными величинами X и Y. В качестве меры такой связи используется коэффи­циент корреляции. Коэффициент корреляции оценивается по выборке объема п связанных пар наблюдений (x i , y i) из совместной генеральной совокупности X и Y. Для оценки степени взаимосвязи величин X и Y, измеренных в количественных шкалах, используетсякоэффи­циент линейной корреляции (коэффициент Пирсона), предполагающий, что выборки X и Y распределены по нормальному закону.

Коэффициент корреляции изменяется от -1 (строгая обратная линейная зависимость) до 1 (строгая прямая пропорцио­нальная зависимость). При значении 0 линейной зависимости между двумя вы­борками нет.

Общая классификация корреляционных связей (по Ивантер Э.В., Коросову А.В., 1992):

Существует несколько типов коэффициентов корреляции, что зависит от переменных Х иY, которые могут быть измерены в разных шкалах. Именно этот факт и определяет выбор соответствующего коэффициента корреляции (см. табл. 13):

В MS Excel для вычисления парных коэффициентов линейной корреляции используется специальная функция КОРРЕЛ (массив1; массив2),

№ испытуемых

где массив1 – ссылка на диапазон ячеек первой выборки (X);

Пример 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). Психолога интересует вопрос, в какой взаимосвязи находятся эти характеристики руководителя между собой.

Таблица 4. Результаты исследования

Испытуемые п/п

тактичность

требовательность

критичность