Расчет коэффициентов ковариации и корреляции в табличном процессоре Microsoft Excel

Режим работы «Ковариация» служит для расчета генеральной ковариации на основе выборочных данных.

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

В диалоговых окнах данных режимов задаются следующие параметры:

1. Входной интервал.

2. Группирование.

3. Метки в первой строке/ Метки в первом столбце.

4. Выходной интервал/ Новый рабочий лист/ Новая рабочая книга.

Пример 1. Показатели уровня образования, уровня преступности, а также отношение числа безработных к числу вакансий в некоторых центральных областях России в 1995 году (по данным Госкомстата РФ) приведены в таблице, сформированной на рабочем листе Excel

B	C	D	E
Область	Уровень образования	Отношение числа безработных к числу вакансий	Уровень преступности
Брянская		22,3
Владимирская		10,8
Ивановская		52,9
Калужская		2,2
Костромская		10,4
г. Москва		0,4
Московская		2,4
Нижегородская		5,4
Орловская		4,1
Рязанская		4,1
Смоленская		1,0
Тверская		4,2
Тульская		2,1
Ярославская		25,1

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

Для решения задачи используем режим работы «Ковариация» и «Корреляция». Значения параметров, установленных в одноименных диалоговых окнах следующие:

1. Входной интервал: С4:E18.

2. Группирование: по столбцам.

3. Метки в первой строке: устанавливаем флажок.

4. Выходной интервал: G4 для «Ковариации» и G9 для «Корреляции».

Рассчитанные в данных режимах показатели представлены в таблицах.

Ковариация

	Уровень образования	Отношение числа безработных к числу вакансий	Уровень преступности
Уровень образования	1750,245
Отношение числа безработных к числу вакансий	-149,859	192,5135
Уровень преступности	-4159,28	498,4541	22905,66

Корреляция

	Уровень образования	Отношение числа безработных к числу вакансий	Уровень преступности
Уровень образования
Отношение числа безработных к числу вакансий	-0,25817
Уровень преступности	-0,6569	0,237369

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

– связь «уровня образования» – «отношение числа безработных к числу вакансий» является слабой и обратной (r_xy = -0,25817), т.е. с повышением уровня образования отношение числа безработных к числу вакансий уменьшается;

– связь «уровень образования» – «уровень преступности» является заметной и обратной (r_xy = -0,6569), т.е. с повышением уровня образования преступность уменьшается;

– связь «отношение числа безработных к числу вакансий» – «уровень преступности» является слабой и прямой (r_xy = 0,237369), т.е. с увеличением отношения числа безработных к числу вакансий увеличивается и уровень преступности.

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

Коэффициент корреляции вычисляется в Excel одной из двух функций: КОРРЕЛ или ПИРСОН. Эти функции выдают одинаковый результат, если значения наблюдаемых величин записаны в виде чисел.

Пример 2.Получены данные о числе работников магазинов (Х) и объем розничного товарооборота в млн. руб. (Y):

Х
Y	0,5	0,7	0,9	1,1	1,4	1,4	1,7	1,9

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

Исследовать связь объема розничного товарооборота магазинов и числа работников в них, т.е. найти ковариацию, коэффициент корреляции.

Для выполнения этого задания необходимо проделать следующие пункты.

1. Наберите исходные данные в два столбца, в заголовке которых наберите буквы Х и Y, соответственно, в ячейки А1 и В1. Тогда данные Х займут диапазон А2:А9, а данные Y займут диапазон В2:В9.

2. Вычислите ковариацию. Для этого в ячейку А10 наберите «». В ячейку В10 наберите формулу для вычисления ковариации: =КОВАР(А2:А9; В2:В9). Должно получиться: μ_х_y = 10,475.

3. Вычислите коэффициент корреляции. Наберите в ячейку А11 «». В ячейку В11 наберите формулу для вычисления коэффициента корреляции: КОРРЕЛ(А2:А9; В2:В9). Должно получиться: .