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

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

Для расчета коэффициента корреляции можно использовать функцию Excel =КОРРЕЛ(массив1;массив2) из категории статистические.

Для вывода результатов в виде таблицы применяют функцию Корреляция из пакета Анализ данных. После ее вызова из окна диалога Анализ данных откроется соответствующее диалоговое окно (рис. 7.1).

Рис. 7.1. Корреляция

В диалоговом окне (рис. 7.1) задаются следующие параметры:

1. Входной интервал - вводится диапазон ячеек, содержащих анализируемые данные.

2. Группирование – переключатель устанавливается в требуемое положение в зависимости от расположения исходных данных.

3. Метки в первой строке – флажок ставится, если первая строка содержит заголовок, в противном случае будут созданы стандартные заголовки автоматически.

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

Линейная регрессия

Форма связи результативного признака Y с факторами X₁, X₂, … X_m называется уравнением регрессии. В зависимости от типа выбранного уравнения различают линейную и нелинейную регрессию, а в зависимости от количества факторов – парную (простую, m = 1) и множественную (многофакторную, m > 1).

На этапе регрессионного анализа решаются следующие задачи:

1. Выбор общего вида уравнения регрессии и определение параметров регрессии.

2. Определение степени взаимосвязи результативного признака и факторов, проверка общего качества уравнения регрессии.

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

Уравнение простой линейной регрессии имеет вид: , множественная линейная регрессия описывается следующим уравнением: .

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

Рис.7.2 Регрессия

В диалоговом окне (рис. 7.2) задаются следующие параметры:

1. Входной интервал Y- вводится диапазон ячеек (один столбец), содержащих исходные данные по результирующему признаку.

2. Входной интервал X - вводится диапазон ячеек (число столбцов равно количеству признаков), содержащих исходные данные факторного признака.

3. Метки – флажок ставится, если первая строка содержит заголовок, в противном случае будут созданы стандартные заголовки автоматически.

4. Уровень надежности – флажок устанавливается, если требуется ввести значение уровня отличное от 95%. При выключенном флажке уровень надежности принимается равным 95%.

5. Константа-ноль - флажок устанавливается в том случае, когда требуется, чтобы линия регрессии прошла через начало координат, т.е. .

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

7. Остатки – при необходимости вывода столбцов остатков и графиков остатков и подбора необходимо включить соответствующие флажки.

8. Нормальная вероятность – флажок устанавливается, если не требуется вывести график зависимости наблюдаемых значений от автоматически формируемых интервалов персентилей.

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

Таблица 7.1

Y	X
затраты на ТО	срок службы

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

Результаты расчета регрессионной статистики выводятся в следующей таблице (табл. 7.2):

Таблица 7.2

Регрессионная статистика
Множественный R	0,984535285
R-квадрат	0,969309728
Нормированный R-квадрат	0,959079637
Стандартная ошибка	1,724792855
Наблюдения

В таблице 7.2 указаны следующие элементы:

Множественный R – коэффициент корреляции.

R-квадрат – коэффициент детерминации.

Нормированный R-квадрат – нормированное значение коэффициента корреляции.

Стандартная ошибка - стандартное отклонение для остатков.

Наблюдения - количество исходных наблюдений.

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

Таблица 7.3

Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия		281,8752688	281,8752688	94,75084337	0,002303227
Остаток		8,924731183	2,974910394
Итого		290,8

В таблице 7.3 указаны следующие элементы:

df - число степеней свободы. Для строки Регрессия это количество факторных признаков, для строки Остаток – число наблюдений минус количество переменных в уравнении регрессии, для строки Итого – сумма степеней свободы для строк Регрессия и Остаток.

SS - сумма квадратов отклонений. Для строки Регрессия это значение определяется как сумма квадратов отклонений теоретических данных от среднего, для строки Остаток это сумма квадратов отклонений эмпирических данных от теоретических, для строки Итого это сумма квадратов отклонений эмпирических данных от среднего.

MS - дисперсии. Для строки Регрессия это факторная дисперсия, для строки Остаток это остаточная дисперсия.

F - расчетное значение F‑критерия Фишера, определяемое как отношение факторной дисперсии к остаточной.

Значимость F - значение уровня значимости, соответствующее вычисленному значению F.

Полученные значения коэффициентов регрессии и их статистические оценки сводятся в следующую таблицу (табл. 7.4):

Таблица 7.4

	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение
Y-пересечение	-1,064516129	1,710826692	-0,622223241	0,577881718
срок службы	2,752688172	0,282790929	9,734004488	0,002303227

В таблице 7.4 указаны следующие элементы:

Коэффициенты - значения коэффициентов модели.

Стандартная ошибка - стандартные ошибки коэффициентов.

t-статистика - расчетные значения t‑критерия, вычисляемого как отношение значений коэффициентов к соответствующим стандартным ошибкам.

P-Значение - значения уровней значимости, соответствующие вычисленным значениям t_p.

В экранной таблице Excel также указываются нижние и верхние границы доверительных интервалов для коэффициентов регрессии - Нижние 95%, Верхние 95% (ввиду ограниченности места в таблице 7.4 они опущены).

На основе данных из полученных таблиц можно сделать следующие выводы:

1. Уравнение регрессии имеет вид: .

2. Значение коэффициента детерминации, равного 0,97 показывает, что срок службы существенно влияют на затраты на ТО, что подтверждает правильность включения его в построенную модель.

3. Рассчитанный уровень значимости Значимость F = 0,002 меньший 0,05 подтверждает значимость величины коэффициента детерминации.

4. P-Значение для срока службы, равное 0,002 и меньшее 0,05 подтверждает значимость коэффициента .

5. P-Значение для коэффициента превышает 0,05, это означает, что данный коэффициент для модели не является значимым и его можно опустить, т.е. график модели будет проходить через точку начала координат.

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

Таблица 7.5

	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение
Y-пересечение		-	-	-
срок службы	2,595628415	0,11732738	22,12295556	2,47108E-05

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

В итоге модель получится следующего вида: .

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

Содержание работы

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

Порядок выполнения

1. Для исходных данных (табл. 7.1) вычислить корреляцию попарно между признаками, применяя функцию =КОРРЕЛ(массив1;массив2) и с помощью функции Корреляция. Сделать выводы.

2. Чему равен коэффициент корреляции двух случайных величин, представленных в таблице 7.6.

Таблица 7.6

X	12,1	14,7	20,5	16,6	19,0
Y	53,2	44,2	51,4	45,5	34,0

3. На основе данных (табл. 7.7) построить линейную модель и провести ее анализ.

Таблица 7.7

Количество работников (X)	Объем производства (Y)

4. Построить модель зависимости величины заработной платы от стажа работы и пола сотрудника (табл. 7.8). Проверить адекватность модели.

Таблица 7.8

Заработная плата, Y	Стаж работы, X1	Пол (0-муж., 1-жен) X2

5. Определить по данным (табл.7.9) параметры уравнения линейной регрессии и провести его анализ.

Таблица 7.9

Предприятие	Прибыль, Y	Оборотные средства, X1	Основные фонды, X2

Контрольные вопросы

1. Что называется регрессионной моделью?

2. Привести общий вид регрессионной модели.

3. Каким образом можно проверить значимость коэффициента регрессии?

4. Какой метод обычно используется при определении коэффициентов регрессионной модели?