Функция пирсон расчета коэффициента корреляции пирсона в excel. Проверка простых гипотез критерием хи-квадрат Пирсона в MS EXCEL

Задача 1.

Используя критерий Пирсона, при уровне значимости a = 0,05 проверить, согласуется ли гипотеза о нормальном распределении генеральной совокупности X с эмпирическим распределением выборки объема n = 200.

Решение.

1. Вычислим и выборочное среднее квадратическое отклонение .
2. Вычислим теоретические частоты учитывая, что n = 200, h = 2, = 4,695, по формуле
.

Составим расчетную таблицу (значения функции j (x ) приведены в приложении 1).

i

3. Сравним эмпирические и теоретические частоты. Составим расчетную таблицу, из которой найдем наблюдаемое значение критерия :

i









Сумма

По таблице критических точек распределения (приложение 6), по уровню значимости a = 0,05 и числу степеней свободы k = s – 3 = 9 – 3 = 6 находим критическую точку правосторонней критической области (0,05; 6) = 12,6.
Так как =22,2 > = 12,6, гипотезу о нормальном распределении генеральной совокупности отвергаем. Другими словами, эмпирические и теоретические частоты различаются значимо.

Задача2

Представлены статистические данные.

Результаты измерений диаметров n = 200 валков после шлифовки обобщены в табл. (мм):
Таблица Частотный вариационный ряд диаметров валков

i
xi , мм


xi , мм

Требуется:

1) составить дискретный вариационный ряд, при необходимости упорядочив его;

2) определить основные числовые характеристики ряда;

3) дать графическое представление ряда в виде полигона (гистограммы) распределения;

4) построить теоретическую кривую нормального распределения и проверить соответствие эмпирического и теоретического распределений по критерию Пирсона. При проверке статистической гипотезы о виде распределения принять уровень значимости a = 0,05

Решение: Основные числовые характеристики данного вариационного ряда найдем по определению. Средний диаметр валков равен (мм):
x ср = = 6,753;
исправленная дисперсия (мм2):
D = = 0,0009166;
исправленное среднее квадратическое (стандартное) отклонение (мм):
s = = 0,03028.

Рис. Частотное распределение диаметров валков

Исходное («сырое») частотное распределение вариационного ряда, т.е. соответствие ni (xi ), отличается довольное большим разбросом значений ni относительно некоторой гипотетической «усредняющей» кривой (рис.). В этом случае предпочтительно построить и анализировать интервальный вариационный ряд, объединяя частоты для диаметров, попадающих в соответствующие интервалы.
Число интервальных групп K определим по формуле Стерджесса:
K = 1 + log2n = 1 + 3,322lgn ,
где n = 200 – объем выборки. В нашем случае
K = 1 + 3,322×lg200 = 1 + 3,322×2,301 = 8,644 » 8.
Ширина интервала равна (6,83 – 6,68)/8 = 0,01875 » 0,02 мм.
Интервальный вариационный ряд представлен в табл.

Таблица Частотный интервальный вариационный ряд диаметров валков.

k
xk , мм

Интервальный ряд может быть наглядно представлен в виде гистограммы частотного распределения.

Рис . Частотное распределение диаметров валков. Сплошная линия – сглаживающая нормальная кривая.

Вид гистограммы позволяет сделать предположение о том, что распределение диаметров валков подчиняется нормальному закону, согласно которому теоретические частоты могут быть найдены как
nk , теор = n ×N (a ; s; xk )×Dxk ,
где, в свою очередь, сглаживающая гауссова кривая нормального распределения определяется выражением:
N (a ; s; xk ) = .
В этих выражениях xk – центры интервалов в частотном интервальном вариационном ряде.

Например, x 1 = (6,68 + 6,70)/2 = 6,69. В качестве оценок центра a и параметра s гауссовой кривой можно принять:
a = x ср.
Из рис. видно, что гауссова кривая нормального распределения в целом соответствует эмпирическому интервальному распределению. Однако следует удостовериться в статистической значимости этого соответствия. Используем для проверки соответствия эмпирического распределения эмпирическому критерий согласия Пирсона c2 . Для этого следует вычислить эмпирическое значение критерия как сумму
= ,
где nk и nk ,теор – эмпирические и теоретические (нормальные) частоты, соответственно. Результаты расчетов удобно представить в табличном виде:
Таблица Вычисления критерия Пирсона

[xk , xk+ 1), мм	xk , мм	nk ,теор

Критическое значение критерия найдем по таблице Пирсона для уровня значимости a = 0,05 и числа степеней свободы d .f . = K – 1 – r , где K = 8 – число интервалов интервального вариационного ряда; r = 2 – число параметров теоретического распределения, оцененных на основании данных выборки (в данном случае, – параметры a и s). Таким образом, d .f . = 5. Критическое значение критерия Пирсона есть крит(a; d .f .) = 11,1. Так как c2эмп < c2крит, заключаем, что согласие между эмпирическим и теоретическим нормальным распределением является статистическим значимым. Иными словами, теоретическое нормальное распределение удовлетворительно описывает эмпирические данные.

Задача3

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

Требуется используя критерий Пирсона при уровне значимости a=0,05 проверить гипотезу о том, что случайная величина X – вес упаковок – распределена по нормальному закону. Построить на одном графике гистограмму эмпирического распределения и соответствующую нормальную кривую.

Решение

1012,5
= 615,3846

Примечание:

В принципе в качестве дисперсии нормального закона распределения следует взять исправленную выборочную дисперсию. Но т.к. количество наблюдений – 130 достаточно велико, то подойдет и “обычная” .
Таким образом, теоретическое нормальное распределение имеет вид:

Интервал

[xi ; xi+1 ]

Эмпирические частоты

Вероятности
pi

Теоретические частоты
npi

(ni-npi)2

Рассмотрим применение в MS EXCEL критерия хи-квадрат Пирсона для проверки простых гипотез.

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

Сначала рассмотрим применение критерия согласия Пирсона Х 2 (хи-квадрат) в отношении простых гипотез (параметры теоретического распределения считаются известными). Затем - , когда задается только форма распределения, а параметры этого распределения и значение статистики Х 2 оцениваются/рассчитываются на основании одной и той же выборки .

Примечание : В англоязычной литературе процедура применения критерия согласия Пирсона Х 2 имеет название The chi-square goodness of fit test .

Напомним процедуру проверки гипотез:

на основе выборки вычисляется значение статистики , которая соответствует типу проверяемой гипотезы. Например, для используется t -статистика (если не известно);
при условии истинности нулевой гипотезы , распределение этой статистики известно и может быть использовано для вычисления вероятностей (например, для t -статистики это );
вычисленное на основе выборки значение статистики сравнивается с критическим для заданного значением ();
нулевую гипотезу отвергают, если значение статистики больше критического (или если вероятность получить это значение статистики () меньше уровня значимости , что является эквивалентным подходом).

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

Дискретный случай

Предположим, что два человека играют в кости. У каждого игрока свой набор костей. Игроки по очереди кидают сразу по 3 кубика. Каждый раунд выигрывает тот, кто выкинет за раз больше шестерок. Результаты записываются. У одного из игроков после 100 раундов возникло подозрение, что кости его соперника – несимметричные, т.к. тот часто выигрывает (часто выбрасывает шестерки). Он решил проанализировать насколько вероятно такое количество исходов противника.

Примечание : Т.к. кубиков 3, то за раз можно выкинуть 0; 1; 2 или 3 шестерки, т.е. случайная величина может принимать 4 значения.

Из теории вероятности нам известно, что если кубики симметричные, то вероятность выпадения шестерок подчиняется . Поэтому, после 100 раундов частоты выпадения шестерок могут быть вычислены с помощью формулы
=БИНОМ.РАСП(A7;3;1/6;ЛОЖЬ)*100

В формуле предполагается, что в ячейке А7 содержится соответствующее количество выпавших шестерок в одном раунде.

Примечание : Расчеты приведены в файле примера на листе Дискретное .

Для сравнения наблюденных (Observed) и теоретических частот (Expected) удобно пользоваться .

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

В нашем случае на первый взгляд частоты достаточно близки и без вычислений сложно сделать однозначный вывод. Применим критерий согласия Пирсона Х 2 , чтобы вместо субъективного высказывания «существенно отличаться», которое можно сделать на основании сравнения гистограмм , использовать математически корректное утверждение.

Используем тот факт, что в силу закона больших чисел наблюденная частота (Observed) с ростом объема выборки n стремится к вероятности, соответствующей теоретическому закону (в нашем случае, биномиальному закону ). В нашем случае объем выборки n равен 100.

Введем тестовую статистику , которую обозначим Х 2:

где O l – это наблюденная частота событий, что случайная величина приняла определенные допустимые значения, E l – это соответствующая теоретическая частота (Expected). L – это количество значений, которые может принимать случайная величина (в нашем случае равна 4).

Как видно из формулы, эта статистика является мерой близости наблюденных частот к теоретическим, т.е. с помощью нее можно оценить «расстояния» между этими частотами. Если сумма этих «расстояний» «слишком велика», то эти частоты «существенно отличаются». Понятно, что если наш кубик симметричный (т.е. применим биномиальный закон ), то вероятность того, что сумма «расстояний» будет «слишком велика» будет малой. Чтобы вычислить эту вероятность нам необходимо знать распределение статистики Х 2 (статистика Х 2 вычислена на основе случайной выборки , поэтому она является случайной величиной и, следовательно, имеет свое распределение вероятностей ).

Из многомерного аналога интегральной теоремы Муавра-Лапласа известно, что при n->∞ наша случайная величина Х 2 асимптотически с L - 1 степенями свободы.

Итак, если вычисленное значение статистики Х 2 (сумма «расстояний» между частотами) будет больше чем некое предельное значение, то у нас будет основание отвергнуть нулевую гипотезу . Как и при проверке параметрических гипотез , предельное значение задается через уровень значимости . Если вероятность того, что статистика Х 2 примет значение меньше или равное вычисленному (p -значение ), будет меньше уровня значимости , то нулевую гипотезу можно отвергнуть.

В нашем случае, значение статистики равно 22,757. Вероятность, что статистика Х 2 примет значение больше или равное 22,757 очень мала (0,000045) и может быть вычислена по формулам
=ХИ2.РАСП.ПХ(22,757;4-1) или
=ХИ2.ТЕСТ(Observed; Expected)

Примечание : Функция ХИ2.ТЕСТ() специально создана для проверки связи между двумя категориальными переменными (см. ).

Вероятность 0,000045 существенно меньше обычного уровня значимости 0,05. Так что, у игрока есть все основания подозревать своего противника в нечестности (нулевая гипотеза о его честности отвергается).

При применении критерия Х 2 необходимо следить за тем, чтобы объем выборки n был достаточно большой, иначе будет неправомочна аппроксимация распределения статистики Х 2 . Обычно считается, что для этого достаточно, чтобы наблюденные частоты (Observed) были больше 5. Если это не так, то малые частоты объединяются в одно или присоединяются к другим частотам, причем объединенному значению приписывается суммарная вероятность и, соответственно, уменьшается число степеней свободы Х 2 -распределения .

Для того чтобы улучшить качество применения критерия Х 2 (), необходимо уменьшать интервалы разбиения (увеличивать L и, соответственно, увеличивать количество степеней свободы ), однако этому препятствует ограничение на количество попавших в каждый интервал наблюдений (д.б.>5).

Непрерывный случай

Критерий согласия Пирсона Х 2 можно применить так же в случае .

Рассмотрим некую выборку , состоящую из 200 значений. Нулевая гипотеза утверждает, что выборка сделана из .

Примечание : Cлучайные величины в файле примера на листе Непрерывное сгенерированы с помощью формулы =НОРМ.СТ.ОБР(СЛЧИС()) . Поэтому, новые значения выборки генерируются при каждом пересчете листа.

Соответствует ли имеющийся набор данных можно визуально оценить .

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

Для этого разобьем диапазон изменения случайной величины на интервалы с шагом 0,5 . Вычислим наблюденные и теоретические частоты. Наблюденные частоты вычислим с помощью функции ЧАСТОТА() , а теоретические – с помощью функции НОРМ.СТ.РАСП() .

Примечание : Как и для дискретного случая , необходимо следить, чтобы выборка была достаточно большая, а в интервал попадало >5 значений.

Вычислим статистику Х 2 и сравним ее с критическим значением для заданного уровня значимости (0,05). Т.к. мы разбили диапазон изменения случайной величины на 10 интервалов, то число степеней свободы равно 9. Критическое значение можно вычислить по формуле
=ХИ2.ОБР.ПХ(0,05;9) или
=ХИ2.ОБР(1-0,05;9)

На диаграмме выше видно, что значение статистики равно 8,19, что существенно выше критического значения – нулевая гипотеза не отвергается.

Ниже приведена , на которой выборка приняла маловероятное значение и на основании критерия согласия Пирсона Х 2 нулевая гипотеза была отклонена (не смотря на то, что случайные значения были сгенерированы с помощью формулы =НОРМ.СТ.ОБР(СЛЧИС()) , обеспечивающей выборку из стандартного нормального распределения ).

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

В качестве примера также возьмем выборку из U(-3; 3). В этом случае, даже из графика очевидно, что нулевая гипотеза должна быть отклонена.

Критерий согласия Пирсона Х 2 также подтверждает, что нулевая гипотеза должна быть отклонена.

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

Как работает функция ПИРСОН в Excel?

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

Задача следующая: необходимо определить взаимосвязь между температурой и давлением за июнь месяц.

Пример решения с функцией ПИРСОН при анализе в Excel

Данный показатель -0,14 по Пирсону, который вернула функция, говорит об неблагоприятной зависимости температуры и давления в раннее время суток.

Функция ПИРСОН пошаговая инструкция

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

Задача: Определить линейный коэффициент корреляции Пирсона.

Пример решения:

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

Корреляционный анализ по Пирсону в Excel

Задача: школьникам были даны тесты на наглядное и вербальное мышление. Измерялось среднее время решения заданий теста в секундах. Психолога интересует вопрос: существует ли взаимосвязь между временем решения этих задач?

Пример решения: представим исходные данные в виде таблицы:

Интерпретация результата вычисления по Пирсону

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

Если коэффициент корреляции по модулю оказывается близким к 1, то это соответствует высокому уровню связи между переменными.

Если же получен знак минус, то большей величине одного признака соответствует меньшая величина другого. Иначе говоря, при наличии знака минус, увеличению одной переменной (признака, значения) соответствует уменьшение другой переменной. Такая зависимость носит название обратно пропорциональной зависимости. Эти положения очень важно четко усвоить для правильной интерпретации полученной корреляционной зависимости.

Оценка соответствия нормальному распределению

Этот метод используется для проверки согласия опытного и теоретического распределения, если число испытаний больше 100.

Суть метода заключается в определении критерия Пирсона (c 2 ) с последующим сравнением полученного значения с теоретическим.

Порядок определения критерия Пирсона:

Определяют среднее значение и среднее квадратическое отклонение. Для расчета критерия Пирсона составляют таблицу (таблице 11).

2. Определяют отношение

3. С помощью специальной таблицы (таблица 12) определяют частоту распределения Y 0 .

Таблица 11

Таблица 12

t	0,00	0,01	0,02	0,03	0,04	0,05	0,06	0,07	0,08	0,09
0,0 0,1 0,2 0,3 0,4 0,5 0,6 0,7 0,8 0,9 1,0 1,1 1,2 1,3 1,4 1,5 1,6 1,7 1,8 1,9 2,0 2,1 2,2 2,3 2,4 2,5 2,6 2,7 2,8 2,9 3,0 3,1 3,2 3,3 3,4 3,5 3,6 3,7 3,8 3,9	0,3989 0,2420 0,0544 0,0044

4. Рассчитывают теоретическое значение частот

(40)

где n - общее число испытаний;

k - классовый интервал;

S - среднее квадратическое отклонение.

5. Определяют разность между фактической и теоретической частотой распределения

y i – U т (41)

рассчитывают

6. Находят критерий Пирсона

(43)

7. Определяют число степеней свободы

С = m-3 (44)

где C - число степеней свободы;

m - число классов или строк.

8. Задаваясь доверительной вероятностью q , определяют теоретическое значение критерия Пирсона.

9. Сравнивают c ф 2 с c т 2. Если c 2 ф < c 2 т , то для принятой доверительной вероятности гипотеза о согласии опытного и теоретического распределения принимается, в противном случае отвергается.

В программе Excel проверка осуществляется с помощью функции ХИ2ТЕСТ (рис. 22). ХИ2ТЕСТ возвращает значение для распределения χ 2 Критерий используется для определения того, подтверждается ли гипотеза экспериментом.

Рис. 22. Функция ХИ2ТЕСТ

ХИ2ТЕСТ (фактический_интервал ;ожидаемый_интервал )

Фактический_интервал - это интервал данных, которые содержат наблюдения, подлежащие сравнению с ожидаемыми значениями.

Ожидаемый_интервал - это интервал данных, который содержит отношение произведений итогов по строкам и столбцам к общему итогу.

Если фактический_интервал и ожидаемый_интервал имеют различное количество точек данных, то функция ХИ2ТЕСТ возвращает значение ошибки #Н/Д.

Критерий χ 2 сначала вычисляет χ 2 статистику, используя формулу:

(45)

где A ij - фактическая частота в i -ой строке, j -ом столбце

E ij - ожидаемая частота в i-ой строке, j-ом столбце

r - число строк

c - число столбцов

Значение критерия χ 2 является индикатором независимости. Как видно из формулы, критерий χ 2 всегда положительный или равен 0, а последнее возможно только, если A ij = E ij при любых значениях i,j .

ХИ2ТЕСТ возвращает вероятность того, что при условии независимости может быть получено значение χ 2 статистики по крайней мере такое же высокое, как полученное из приведенной выше формулы. Чтобы вычислить эту вероятность, ХИ2ТЕСТ использует распределение χ 2 с соответствующим числом степеней свободы (df ). Если r > 1, а c > 1, то df = (r - 1)(c - 1). Если r = 1, а c > 1, то df = c - 1 или если r > 1, а c = 1, то df = r - 1. Равенство, где r = c= 1, не позволительно, поэтому появится сообщение об ошибке #Н/Д.

Функцию ХИ2ТЕСТ можно использовать в тех случаях, когда гипотетическое распределение задано полностью, то есть заданы не только вид гипотетического закона распределения, но и все параметры этого закона. Только в этом случае функция правильно выдает число степеней свободы.

ХИ2РАСП (x;степени_свободы) (рис. 23) возвращает одностороннюю вероятность распределения хи-квадрат. Распределение χ 2 связано с критерием χ 2 . Критерий χ 2 используется для сравнения предполагаемых и наблюдаемых значений. Например, в генетическом эксперименте выдвигается гипотеза, что следующее поколение растений будет обладать определенной окраской. Сравнивая наблюдаемые результаты с предполагаемыми, можно определить, была ли верна исходная гипотеза.

х – значение, для которого требуется вычислить распределение.

Степени_свободы – число степеней свободы.

Рис. 23. Функция ХИ2РАСП

Если какой-либо из аргументов не является числом, функция ХИ2РАСП возвращает значение ошибки #ЗНАЧ!.

Если x отрицательное значение, функция ХИ2РАСП

Если степени_свободы < 1 или степени_свободы > 10^10, функция ХИ2РАСП возвращает значение ошибки #ЧИСЛО!.

ХИ2РАСП вычисляется как ХИ2РАСП = P(X> x), где x - χ 2 случайная величина.

ХИ2ОБР (вероятность;степени_свободы) (рис. 24) возвращает значение, обратное односторонней вероятности распределения хи-квадрат. Если вероятность = ХИ2РАСП (x;...), то ХИ2ОБР (вероятность;...) = x. Данная функция позволяет сравнить наблюдаемые результаты с ожидаемыми, чтобы определить, была ли верна исходная гипотеза.

Вероятность - вероятность, связанная с распределением c2 (хи-квадрат).

Степени_свободы - число степеней свободы.

Если какой-либо из аргументов не является числом, функция ХИ2ОБР возвращает значение ошибки #ЗНАЧ!

Рис. 24. Функция ХИ2ОБР

Если вероятность < 0 или вероятность > 1, функция ХИ2ОБР возвращает значение ошибки #ЧИСЛО!

Если значение аргумента «степени_свободы» не является целым числом, оно усекается.

Если степени_свободы < 1 или степени_свободы ≥ 10^10, ХИ2ОБР возвращает значение ошибки #ЧИСЛО!

Если задано значение вероятности, то функция ХИ2ОБР ищет значение x, для которого функция ХИ2РАСП (x; степень_свободы) = вероятность. Однако точность функции ХИ2ОБР зависит от точности ХИ2РАСП . В функции ХИ2ОБР для поиска применяется метод итераций. Если поиск не закончился после 100 итераций, функция возвращает сообщение об ошибке #Н/Д.

1.Открыть программу Excel

2.Создать столбцы с данными. В нашем примере мы будем считать взаимосвязь, или корреляцию, между агрессивностью и неуверенностью в себе у детей-первоклассников. В эксперименте участвовали 30 детей, данные представлены в таблице эксель:

1 столбик — № испытуемого

2 столбик — агрессивность в баллах

3 столбик — неуверенность в себе в баллах

3.Затем необходимо выбрать пустую ячейку рядом с таблицей и нажать на значок f(x) в панели Excel

4.Откроется меню функций, среди категорий необходимо выбрать Статистические , а затем среди списка функций по алфавиту найти КОРРЕЛ и нажать ОК

5.Затем откроется меню аргументов функции, которое позволит выбрать нужные нам столбики с данными. Для выбора первого столбика Агрессивность нужно нажать на синюю кнопочку у строки Массив1

6.Выберем данные для Массива1 из столбика Агрессивность и нажмем на синюю кнопочку в диалоговом окне

7. Затем аналогично Массиву 1 нажмём на синюю кнопочку у строки Массив2

8.Выберем данные для Массива2 — столбик Неуверенность в себе и опять нажмем синюю кнопку, затем ОК

9.Вот, коэффициент корреляции r-Пирсона посчитан и записан в выбранной ячейке.В нашем случае он положительный и приблизительно равен 0,225 . Это говорит об умеренной положительной связи между агрессивностью и неуверенностью в себе у детей-первоклассников

Таким образом, статистическим выводом эксперимента будет: r = 0,225, выявлена умеренная положительная взаимосвязь между переменными агрессивность и неуверенность в себе.

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

Также Вы можете и приложить её к результатам исследования.