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

Лабораторная работа №1-2.

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

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

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

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

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

Используя правильную терминологию, можно сказать, что сила зависимости, существующей между двумя величинами, измеряется корреляцией. Сила взаимосвязи оценивается с помощью коэффициента корреляции, определяемого следующим образом. Две величины с совершенной отрицательной зависимостью имеют коэффициент корреляции, равный -1 (см. рис.1).

Рис.1. Две величины с совершенной отрицательной зависимостью

На другом полюсе — две величины с совершенной положительной зависимостью, которые имеют коэффициент корреляции, равный +1 (рис.2).

Рис.2. Две величины с совершенной положительной зависимостью

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

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

На рис.3 изображены диаграммы для величин, не являющихся линейно зависимыми. Коэффициент корреляции в этом случае равен 0, т.е. никакой линейной зависимости не существует.

Рис.3. Диаграммы для величин, не являющихся линейно зависимыми

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

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

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

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

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

Таблица 1

№ испытуемых	X	Y

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

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

Таблица 2 - Интерпретация коэффициента корреляции r

Интервал значений коэффициента корреляции	Интерпретация результата
0-0,2	Очень слабая корреляция
0,2-0,5	Слабая корреляция
0,5-0,7	Средняя корреляция
0,7-0,9	Сильная корреляция
0,9-1	Очень сильная корреляция