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

Лабораторная  работа 6.

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

 

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

 

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

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

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

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

 

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

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

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

массив2 – ссылка на диапазон ячеек второй выборки (Y).

Пример 1. 10 школьникам были даны тесты на наглядно-образное и вербальное мышление. Измерялось среднее время решения заданий теста в секундах. Исследователя интересует вопрос: существует ли взаимосвязь между временем решения этих задач? Переменная X — обозначает среднее время решения наглядно-образных, а переменная Y— среднее время решения вербальных заданий тестов (см. лекцию 7).

Таблица 1

№ испытуемых	X	Y
1	19	17
2	32	7
3	33	17
4	44	28
5	28	27
6	35	31
7	39	20
8	39	17
9	44	35
10	44	43

 

 

 

Рис. 1. Результаты вычисления коэффициента корреляции

Решение: Для  выявления степени взаимосвязи, прежде всего, необходимо ввести данные в таблицу MS Excel (см. табл. 1, рис. 1). Затем вычисляется значение коэффициента корреляции. Для этого курсор установите в ячейку C1. На панели инструментов нажмите кнопку Вставка функции (fx). В появившемся диалоговом окне Мастер функций выберите категорию Статистические и функцию КОРРЕЛ, после чего нажмите кнопку ОК. Указателем мыши введите диапазон данных выборки Х в поле массив1 (А1:А10). В поле массив2 введите диапазон данных выборки У (В1:В10). Нажмите кнопку ОК. В ячейке С1 появится значение коэффициента корреляции — 0,54119. Далее необходимо по статистическим таблицам определить критические значения для полученного коэффициента корреляции (см. лекцию 7 Приложение 3). При нахождении критических значений для вычисленного коэффициента линейной корреляции Пирсона число степеней свободы рассчитывается как k = n – 2 = 8.

к_крит=0,63 > 0,54 , следовательно, гипотеза Н₁ отвергается и принимается гипотеза H₀, иными словами, связь между временем решения наглядно-образных и вербальных заданий теста не доказана.

Задание для самостоятельной  работы

1. Определите, имеется ли взаимосвязь между  рождаемостью и смертностью (количество  на 1000 человек) в Санкт-Петербурге:

Годы	Рождаемость	Смертность
1991	9,3	12,5
1992	7,4	13,5
1993	6,6	17,4
1994	7,1	17,2
1995	7,0	15,9
1996	6,6	14,2
1997	7,1	16
1998	8,2	13,4

 

Ответ: коэффициент  корреляции равен –0,726

2. Рассчитайте  коэффициент корреляции Пирсона  из примера 1 и задания 1 в  статистическом пакете Stadia (см. лаб. 5). Для этого выбираем процедуру 3=Корреляция в окне Статистические методы – Параметрические тесты. Совпадают ли полученные значения.

Множественная корреляция

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

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

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

Для реализации процедуры необходимо:

1. выполнить  команду Сервис - Анализ данных;

2. в появившемся  списке Инструменты анализа выбрать строку Корреляция и нажать кнопку ОК;

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

4. в разделе Группировка переключатель установить в соответствии с введенными данными (по столбцам или по строкам);

5. указать выходной интервал, то есть ввести ссылку на ячейку, с которой будут показаны результаты анализа. Размер выходного диапазона будет определен автоматически, и на экран будет выведено сообщение в случае возможного наложения выходного диапазона на исходные данные. Нажать кнопку ОК.

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

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

Пример 2. Имеются ежемесячные данные наблюдений за состоянием погоды и посещаемостью музеев и парков (см. табл. 2). Необходимо определить, существует ли взаимосвязь между состоянием погоды и посещаемостью музеев и парков.

Таблица 2.

Число ясных  дней	Количество  посетителей музея	Количество  посетителей парка
8	495	132
14	503	348
20	380	643
25	305	865
20	348	743
15	465	541

 

Решение. Для  выполнения корреляционного анализа  введите в диапазон A1:G3 исходные данные (рис. .2). Затем в меню Сервис выберите пункт Анализ данных и далее укажите строку Корреляция. В появившемся диалоговом окне укажите Входной интервал (А2:С7). Укажите, что данные рассматриваются по столбцам. Укажите выходной диапазон (Е1) и нажмите кнопку ОК.

Рис. 2. Результаты вычисления корреляционной матрицы  из примера 2

На рис. 2 видно, что корреляция между состоянием погоды и посещаемостью музея равна -0,92, а между состоянием погоды и посещаемостью парка — 0,97, между посещаемостью парка и музея —

-0,92.

Таким образом, в результате анализа выявлены зависимости: сильная степень обратной линейной взаимосвязи между посещаемостью музея и количеством солнечных дней и практически линейная (очень сильная прямая) связь между посещаемостью парка и состоянием погоды. Между посещаемостью музея и парка имеется сильная обратная взаимосвязь.

Задание для самостоятельной работы

1. 10 менеджеров  оценивались по методике экспертных  оценок психологических характеристик  личности руководителя (см. Психологические  тесты. Т.2. Под ред. А.А. Карелина. - М., ВЛАДОС, 1999, стр. 99). 15 экспертов производили  оценку каждой психологической характеристики по пятибальной системе (см. табл. 3). Психолога интересует вопрос, в какой взаимосвязи находятся эти характеристики руководителя между собой.

Таблица 3.

Испытуемые  п/п	тактичность	требовательность	критичность
1	70	18	36
2	60	17	29
3	70	22	40
4	46	10	12
5	58	16	31
6	69	18	32
7	32	9	13
8	62	18	35
9	46	15	30
10	62	22	36

 

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

Рис. 3. Результаты вычисления корреляционной матрицы  из задания 1

2. Постройте корреляционную матрицу из примера 2 и задания 1 в статистическом пакете Stadia (см. лаб. 5). Для этого выбираем процедуру 3=Корреляция в окне Статистические методы – Параметрические тесты. Совпадают ли полученные значения.