Корреляционное отношение и проверка гипотезы об отсутствии корреляционной зависимости между признаками
По таблице распределения Фишера-Снедекора (или с помощью функции FРАСПОБР() программы Excel) находим критическое значение значение F-критерия Фишера-Снедекора с уровнем значимости α с k1 = m – 1 и k2 = n – m степенями свободы.
Если , то принимается гипотеза о наличии корреляционной зависимости между Y и X.
Если , то принимается гипотеза о наличии корреляционной зависимости между X и Y.
Для вычисления критического значения в программе Excel предназначена функция
FРАСПОБР(вероятность;степени_свободы1;степени_свободы2)
Вероятность — уровень значимости α.
Степени_свободы1 — число степеней свободы m – 1.
Степени_свободы2 — число степеней свободы n – m.
Пример 2.1 Задана таблица парных значений (табл. 2.2).
1) Вычислить выборочный коэффициент корреляции.
2) Проверить значимость коэффициента корреляции для уровня
α = 0,01.
3) Построить корреляционное поле.
Таблица 2.2
i | xi | yi | i | xi | yi | i | xi | yi |
0,43 | 2,66 | 0,32 | 2,52 | 0,52 | 3,54 | |||
0,25 | 2,09 | 0,79 | 4,11 | 0,38 | 2,27 | |||
0,52 | 2,89 | 0,70 | 4,52 | 0,32 | 2,04 | |||
0,63 | 4,13 | 0,95 | 4,90 | 0,34 | 2,48 | |||
0,01 | 0,26 | 0,60 | 3,81 | 0,28 | 2,14 | |||
0,91 | 5,01 | 0,98 | 5,68 | 0,91 | 5,04 | |||
0,41 | 2,44 | 0,75 | 4,03 | 0,90 | 4,97 | |||
0,45 | 3,10 | 0,69 | 4,08 | 0,60 | 3,19 | |||
0,05 | 0,89 | 0,46 | 2,57 | 0,45 | 2,45 | |||
0,44 | 2,29 | 0,63 | 3,49 | 0,84 | 4,98 |
Решение. 1) Введите исходные данные в диапазоне А1:С31 (рис. 2.1, показаны первые десять строк из тридцати).
Войти в меню «Анализ данных», и в появившемся окне выберем функцию «Корреляция». Появится окно, показанное на рис. 2.2. В этом окне укажем входной интервал В1:С31, группирование «по столбцам», поставим флажок в строке «Метки в первой строке», в параметрах вывода выберем «Новый рабочий лист» и нажмем кнопку «ОК». Получим таблицу, показанную на рис. 2.3.
Рис. 2.2
Функция «Корреляция» из пакета «Анализ данных» предназначена для вычисления корреляционной матрицы, которая содержит коэффициенты корреляции rij между всевозможными парами (Xi, Xj), где X1, X2, …, Xk — признаки, между которыми исследуется статистическая связь. Функция «Корреляция» выдает симметричную корреляционную матрицу, диагональные элементы которой равны единице.
Рис. 2.3
На рис. 2.3 коэффициент корреляции содержится в ячейке В3 .
2) Для проверки значимости коэффициента корреляции вычислим значение Тнабл.
Для этого введите в ячейку D7 формулу
=D3*КОРЕНЬ((30-2)/(1-D3^2)).
Получим Тнабл = 23,57.
В ячейку D9 введите формулу =СТЬЮДРАСПОБР(0,01;28), получим значение критерия Стьюдента 2,763262442.
Так как Tнабл = 23,57 > tкр(α; k) = 2,7 , то делаем вывод: коэффициент корреляции значим с уровнем доверия 99%
(1 – 0,01 = 0,99).
Значение выборочного коэффициента корреляции положительно и близко к единице, что означает очень сильную прямую связь между рассматриваемыми признаками.
3) Выделим диапазон ячеек В1:С31 и с помощью мастера диаграмм построим диаграмму «Точечная». Получим график, изображенный на рис. 2.4.
Рис. 2.4
Из этого графика можно сделать вывод о том, что между X и Y есть линейная корреляционная зависимость, так как точки расположены близко к некоторой воображаемой прямой. Этот вывод подтверждается значением выборочного коэффициента корреляции.
Замечание. Мы не можем в данном примере вычислить выборочное корреляционное отношение, так как данные не сгруппированы в корреляционную таблицу.
Пример 2.2 Дана корреляционная таблица (табл. 2.3).
Таблица 2.2
yj xi | |||||
22,5 | |||||
27,5 | |||||
32,5 | |||||
37,5 | |||||
42,5 |
1) Вычислить значение выборочного коэффициента корреляции и проверить значимость для уровня α = 0,05.
2) Вычислить выборочные корреляционные отношения и
, и проверить гипотезу об отсутствии корреляционной зависимости между Y и X.
Решение. 1) Введите корреляционную таблицу в программе Excel в диапазоне A1:F6.
В ячейках G1, H1 и A7, A8 введите обозначения, как показано на рис. 2.5.
В ячейку G2 введите формулу =СУММ(B2:F2) и затем протянем маркером заполнения ячейку G2 до G6. Получим суммы частот nxi.
В ячейку G7 введем формулу =СУММ(G2:G6). Получим объем выборки n = 50.
В ячейку В7 введите формулу =СУММ(B2:B6) и затем протяните маркером заполнения ячейку В7 до F7. Получим суммы частот nyj.
В ячейку H2 введите формулу
=СУММПРОИЗВ(B2:F2;B$1:F$1)/G2
и затем протяните маркером заполнения ячейку H2 до H6. В диапазоне H2:H6 получим групповые средние .
В ячейку В8 введите формулу
=СУММПРОИЗВ(B2:B6;$A2:$A6)/B7
и затем протяните маркером заполнения ячейку В8 вправо до F2. В диапазоне B8:F8 получим групповые средние .
Рис. 2.5
Теперь в ячейку А9 введите текст «Коэфф. корреляции», а в В9 — формулу (2.5) для вычисления выборочного коэффициента корреляции:
=(50*СУММПРОИЗВ(A2:A6;G2:G6;H2:H6)-
СУММПРОИЗВ(A2:A6;G2:G6)*СУММПРОИЗВ(B1:F1;B7:F7))/
(КОРЕНЬ(50*СУММПРОИЗВ(A2:A6^2;G2:G6)-СУММПРОИЗВ(A2:A6;G2:G6)^2)*КОРЕНЬ(50*
СУММПРОИЗВ(B1:F1^2;B7:F7)-СУММПРОИЗВ(B1:F1;B7:F7)^2))
и нажмите комбинацию клавиш Ctrl + Shift + Enter.
Получим значение r = 0,74.
Замечание. В приведенной формуле используются операции с массивами. Например, A2:A6^2 означает, что содержимое каждой ячейки диапазона A2:A6 будет возводиться в квадрат.
Для проверки значимости коэффициента корреляции вычислим значение Тнабл. Для этого введите в ячейку C9 формулу
=B9*КОРЕНЬ((50-2)/(1-B9^2))
Получим Тнабл = 7,62.
В ячейку D9 введите формулу =СТЬЮДРАСПОБР(0,05;48), получим значение критерия Стьюдента 2,01.
Так как Tнабл = > tкр(α; k) делаем вывод: коэффициент корреляции значим с уровнем доверия 95%.
Значение выборочного коэффициента корреляции положительно и близко к единице, что означает сильную прямую связь между рассматриваемыми признаками.
2) Введите в ячейку В10 формулу
=СУММПРОИЗВ((H2:H6-СУММПРОИЗВ(B1:F1;B7:F7)/50)^2;G2:G6)/
СУММПРОИЗВ((B1:F1-СУММПРОИЗВ(B1:F1;B7:F7)/50)^2;B7:F7)
и нажмите комбинацию клавиш Ctrl + Shift + Enter.
В ячейку В11 введите формулу =КОРЕНЬ(В10). Получим значение ηyx = 0,749585091 ≈ 0,77.
Введите в ячейку С10 формулу =B10*45/4, а в ячейку D10 формулу =FРАСПОБР(0,05;4;45).
В ячейке С10 получим значение статистики F = 6,32, в ячейке D10 критическое значение . Следовательно, можно утверждать, что между Y и X есть корреляционная зависимость.
Так как выборочное корреляционное отношение ηyx = 0,75 почти совпадает с выборочным коэффициентом корреляции rв = 0,74, мы можем заключить, что между Y и X есть линейная корреляционная зависимость.
Введите в ячейку В12 формулу для вычисления ηxy:
=СУММПРОИЗВ((B8:F8-СУММПРОИЗВ(A2:A6;G2:G6)/50)^2;B7:F7)/
СУММПРОИЗВ((A2:A6-СУММПРОИЗВ(A2:A6;G2:G6)/50)^2;G2:G6)
и нажмите комбинацию клавиш Ctrl + Shift + Enter.
В ячейку В13 введите формулу =КОРЕНЬ(В12).
Получим значение ηxy = 0,74.
Для проверки значимости введите в ячейку С12 формулу =B12*45/4. Получим значение 6,2.
Критическое значение то же самое . Так как
, можно утверждать, что между X и Y есть корреляционная зависимость.
Мы видим, что ηxy ≠ ηyx, но их значения близки значению коэффициента корреляции rв = 0,74, поэтому мы можем заключить, что между X и Y есть линейная корреляционная зависимость.
Пример 2.3. 1) Смоделировать выборку (xi, yi) объема n = 100, где xi — значения нормально распределенной случайной величины с математическим ожиданием a = 2 и среднеквадратическим отклонением σ = 1, а yi = (xi – 2)3.
2) Вычислить значение выборочного коэффициента корреляции и проверить значимость для уровня α = 0,05.
3) Вычислить выборочные корреляционные отношения и
, и проверить гипотезу об отсутствии корреляционной зависимости между Y и X.
Решение. 1) Введите на рабочем листе Лист1 в ячейку А1 формулу =СЛЧИС(), в ячейку В1 — формулу =НОРМОБР(A1;2;1), в ячейку С1 — формулу =(А1-2)^3.
Выделите ячейки А1:С1 и протяните маркером заполнения вниз до строки А100:С100.
Выделите диапазон В1:С100 скопируйте в буфер, перейдите на Лист2, щелкните правой кнопкой мыши в ячейке А2, в контекстном меню выберите «Специальная вставка», в появившемся окне в разделе «Вставить» выбереите «значения» и нажмите «ОК».
Замечание. Функция СЛЧИС() пересчитывается при каждом обновлении листа, т.е. все значения диапазона А1:С100 рабочего листа Лист1 будут изменяться при изменениях на этом листе (при вводе формул и т.п.). Поэтому мы на листе Лист2 зафиксировали полученные случайные значения.
На рабочем листе Лист2 мы имеем искомую парную выборку (xi, yi) в диапазоне А2:В101 (рис. 2.6, показана только часть).
Рис. 2.6
2) В ячейку С2 введите формулу =КОРРЕЛ(A2:A101;B2:B101). Получим значение 0,827244738.
Для проверки значимости коэффициента корреляции вычислите значение Тнабл . Для этого введите в ячейку C4 формулу
=C2*КОРЕНЬ((100-2)/(1-C2^2)).
Получим Тнабл = 14,57582176.
В ячейку С6 введите формулу =СТЬЮДРАСПОБР(0,05;98).
Получим значение критерия Стьюдента 1,9845.
Так как Tнабл = > tкр(α; k) делаем вывод: коэффициент корреляции значим с уровнем доверия 95%.
Значение выборочного коэффициента корреляции показывает сильную прямую корреляционную зависимость между рассматриваемыми признаками.
На самом деле между Y и X существует функциональная связь. Но эта связь нелинейная, поэтому значение выборочного коэффициента корреляции не равно единице.
3) Для вычисления корреляционного отношения необходимо построить корреляционную таблицу. Для этого создадим программу-макрос по следующему алгоритму:
Выполните команду меню «Сервис — Макрос — Редактор Visual Basic», в открывшемся окне выполните команду меню «Insert-Module» и введите текст программы KorTab(x, y, k) на языке Visual Basic:
Option Explicit: Option Base 0
Function KorTab(x, y, k)
Application.Volatile (False)
Dim i, j, m, n, ns, ns1 As Integer: Dim nxi(), nyj() As Integer
Dim nij(), xi(), yj(), axj(), ayi() As Variant
Dim minx, maxx, miny, maxy, hx, hy, s As Variant
n = Application.Count(x)
ReDim nij(k + 2, k + 2), xi(k), yj(k), axj(k), ayi(k), nxi(k), nyj(k)
minx = Application.WorksheetFunction.Min(x): maxx = Application.WorksheetFunction.Max(x)
miny = Application.WorksheetFunction.Min(y): maxy = Application.WorksheetFunction.Max(y)
hx = (maxx - minx) / (k - 1): hy = (maxy - miny) / (k - 1)
xi(0) = minx - hx / 2: yj(0) = miny - hy / 2
For i = 1 To k: xi(i) = xi(i - 1) + hx: yj(i) = yj(i - 1) + hy: Next i
For i = 1 To k: For j = 1 To k: For m = 1 To n
If (xi(i - 1) < x(m)) And (x(m) <= xi(i)) And (yj(j - 1) < y(m)) And (y(m) <= yj(j)) Then nij(i, j) = nij(i, j) + 1
Next m: Next j: Next i
For i = 1 To k: xi(i) = xi(i) - hx / 2: yj(i) = yj(i) - hy / 2: Next i
For j = 1 To k: s = 0: nyj(j) = 0: For i = 1 To k: nyj(j) = nyj(j) + nij(i, j)
s = s + xi(i) * nij(i, j): Next i: axj(j) = s / nyj(j): Next j
For i = 1 To k: s = 0: nxi(i) = 0: For j = 1 To k:
nxi(i) = nxi(i) + nij(i, j): s = s + yj(j) * nij(i, j): Next j: ayi(i) = s / nxi(i): Next i
ns = 0: ns1 = 0: For i = 1 To k: ns = ns + nyj(i): ns1 = ns1 + nxi(i): Next i
For i = 1 To k: nij(i, 0) = xi(i): nij(i, k + 1) = nxi(i): nij(i, k + 2) = ayi(i)
nij(0, i) = yj(i): nij(k + 1, i) = nyj(i): nij(k + 2, i) = axj(i)
Next i: nij(k + 1, k + 1) = ns: nij(k + 1, k + 2) = ns1
KorTab = nij
End Function
Для контроля правильности текст программы приведен ниже на рис.2.8.
Для вызова программы необходимо: 1) выделить диапазон ячеек, содержащий (k + 3) строки, (k + 3) столбца; 2) в строке формул ввести:
=KorTab(Диапазон_X; Диапазон_Y;Число_интервалов_k)
и, удерживая нажатыми клавиши Ctrl и Shift, нажать Enter.
Диапазон_X — диапазон ячеек, содержащий значения X;
Диапазон_Y — диапазон ячеек, содержащий значения Y;
Число_интервалов_k — число интервалов группировки выборки.
Программа строит квадратную матрицу частот [nij] порядка k, находит середины интервалов и
, условные средние
и
, частоты
и
. Результаты выводятся в массив размера (k + 3) строки, (k + 3) столбца.
После ввода текста программы перейдите на Лист2, выделите диапазон ячеек D1:P13, в строке формул введите
=KorTab(A2:A101;B2:B101;10)
и нажатмите комбинацию клавиш Ctrl + Shift + Enter.
Мы получим корреляционную таблицу (рис.2.7). Здесь первый параметр соответствует массиву значений показателя X, второй параметр — массиву значений показателя Y, третий параметр — числу интервалов группировки выборки.
В строке E1:N1 получены середины интервалов группировки показателя Y, а в столбце D2:D11 — середины
интервалов группировки показателя X, в диапазоне E2:N11 (выделен рамкой) — частоты nij, в строке E12:N12 — частоты
, в строке E13:N13 — условные средние
, в столбце О2:О11 — частоты
, в столбце P2:P11 — условные средние
, в ячейках O12, P12 выведены для контроля суммы
Рис.2.7
Вычислим корреляционное отношение . Для этого введите в ячейку Q2 формулу
=СУММПРОИЗВ((P2:P11-СУММПРОИЗВ(E1:N1;E12:N12)/100)^2;O2:O11)/
СУММПРОИЗВ((E1:N1-СУММПРОИЗВ(E1:N1;E12:N12)/100)^2;E12:N12),
а в ячейку Q3 — формулу =КОРЕНЬ(Q2). Получим значение 0,969389943.
Вычислим корреляционное отношение . Для этого введите в Q4 формулу
=СУММПРОИЗВ((E13:N13-СУММПРОИЗВ(D2:D11;O2:O11)/100)^2;E12:N12)/
СУММПРОИЗВ((D2:D11-СУММПРОИЗВ(D2:D11;O2:O11)/100)^2;O2:O11)
и в ячейку Q5 введите формулу =КОРЕНЬ(Q4). Получим значение 0,902441781.
Полученные значения = 0,97 и
= 0,90 близки к единице, что подтверждает наличие нелинейной корреляционной связи между признаками. Значимость можно не проверять, так как значения близки к единице и объем выборки большой (n = 100).
Замечание. При построении корреляционной таблицы мы фактически заменили выборочные значения показателей средними значениями интервалов группировки. Это привело к небольшому искажению картины — нелинейная функциональная зависимость между исходными показателями соответствует сильной нелинейной корреляционной зависимости между сгруппированными показателями. По этой же причине выборочные характеристики, построенные по корреляционной таблице, будут только приближенно совпадать с выборочными характеристиками, полученными для исходной выборки.
Рис.2.8