Share |

понедельник, 23 декабря 2013 г.

Как считать Хи квадрат в excel



Данный пост не отвечает, как в принципе считать критерий Хи квадрат, его цель - показать, как можно автоматизировать расчет Хи квадрат в excel, какие функции для расчета критерия Хи квадрат там есть. Ибо не всегда под рукой есть SPSS или программа R.
В каком-то смысле это напоминалка и подсказка участникам семинара Аналитика для HR, надеюсь вы используете эти методы в работе, этот пост будет еще одной подсказкой.
Я не даю файл ссылкой на скачивание, но вы вполне можете просто скопировать приведенные мной таблицы примеров и провести вычисления Хи квадрат в excel по приведенным мной данным и формулам

Вводная 

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

На вычисление Хи квадрат вы выходите через сводную таблицу, когда ваши данные сведены в таблицу сопряжения, например в таком виде
Таблица №1
менее 1 года
1
2
3
4
 Сумма по строкам
Да
26
28
24
30
43
151
Нет
44
18
10
8
19
99
Не знаю
13
9
7
10
6
45
 Сумма по столбцам
83
55
41
48
68
295
Для вычисления Хи квадрат в excel существуют следующие формулы

ХИ2.ТЕСТ

Формула ХИ2.ТЕСТ вычисляет вероятность независимости (случайность / неслучайность) распределения
Синаксис такой
ХИ2.ТЕСТ(фактический_интервал,ожидаемый­­_интервал)
В нашем случае фактический интервал это содержимое таблицы, т.е.
26
28
24
30
43
44
18
10
8
19
13
9
7
10
6
а ожидаемые частоты мы даем отдельной таблицей (как считаются ожидаемые частоты - смотрите пост Как в excel быстро считать ожидаемые частоты для вычисления Хи квадрат)
42,48474576
28,15254
20,98644
24,56949
34,80678
27,85423729
18,45763
13,75932
16,10847
22,82034
12,66101695
8,389831
6,254237
7,322034
10,37288
Т.е. получив две таблицы - эмпирических и ожидаемых (или теоретических частот) - мы фактически снимаем с себя работу по получению разницы, возведению в квадрат и прочим вычислениям, а также сверки с таблицей критических значений.
в нашем случае значение ХИ2.ТЕСТ = 0,000466219908895455 - т.е. вероятность независимости распределения 0, 046 %, что значительно ниже принятых в статистике норм в 5 и 1 %. Т.е. мы отвергаем гипотезу о независимости распределения.

НО

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

ХИ2.РАСП.ПХ

Возвращает правостороннюю вероятность распределения хи-квадрат (или вероятность случайности / не случайности распределения) 
Синаксис


ХИ2.РАСП.ПХ(x;степени_свободы), где х - Хи квадрат эмпирическое
В нашем случае формула будет выглядеть так
ХИ2.РАСП.ПХ(28, 04258;8)
Т.е. в отличие от формулы вычисления Хи квадрат в excel ХИ2.ТЕСТ в данном случае мы считает Хи квадрат эмпирические = 28, 04258 и число степеней свободы
В нашем случае ХИ2.РАСП.ПХ = 0,000466219908895455, как и в примере с ХИ2.ТЕСТ

Примечание

Эта формула вычисления Хи квадрат в excel подойдет вам для вычисления таблиц размерностью 2Х2, поскольку вы сами считаете Хиквадрат эмпирическое и можете ввести в расчеты поправку на непрерывность

Примечание 2

Есть также формула ХИ2.РАСП (вы с неизбежностью увидите ее в excel) - она считает левостороннюю вероятность (если по простому, то левосторонняя считается как 1 - правосторонняя, т.е. мы просто переворачиваем формулу, поэтому я и не даю ее в расчетах Хи квадрат, в нашем примере ХИ2.РАСП = 0,999533780091105.
Итого ХИ2.РАСП + ХИ2.РАСП.ПХ = 1. 

ХИ2.ОБР.ПХ 

Возвращает значение, обратное правосторонней вероятности распределения хи-квадрат (или просто значение Хи квадрат для определенного уровня вероятности и количества степеней свободы)
Синаксис
ХИ2.ОБР.ПХ(вероятность;степени_свободы)
В нашем случае Хи квадрат эмпирическое = 28, 04258, а число степеней свободы = 8, мы хотим проверить критические значения Хи квадрат для данного распределения. Как уже сказал, в статистике принято принимать гипотезы при уровне 0, 05 и 0, 01. В нашем случае
ХИ2.ОБР.ПХ(0, 05;8) = 15,5073130558655
ХИ2.ОБР.ПХ(0, 01;1) = 20,0902350296632
Наш Хи квадрат эмпирический превышает необходимое критическое значение в 1 %, поэтому мы отвергаем гипотезу о независимости (случайности) распределения.

Примечание

С помощью формулы можно получить не только Хи квадрат критический, но и собственно Хи квадрат эмпирический.
В первом примере мы получили вероятность ХИ2.ТЕСТ = 0,000466219908895455
Теперь мы вычисляем 
ХИ2.ОБР.ПХ(0,000466219908895455;8) = 28, 04258
Круг замкнулся)

Примечание 2

Есть также формула ХИ2.ОБР, для этой формулы справедливо примечание 2, которое я привел для формулы ХИ2.РАСП.ПХ 

Заключение

Честно признаюсь, не владею точной информацией, насколько полученные результаты вычисления Хи квадрат в excel отличаются от результатов вычисления Хи квадрат в SPSS. Точно понимаю. что отличаются, хотя бы потому, что при самостоятельном вычислении Хи квадрат значения округляются и теряется какое-то количество знаков после запятой. Но не думаю, что это является критичным. Рекомендую лишь страховаться в том случае, когда вероятность распределения Хи квадрат близко к порогу (p-value) 0, 05.
Не очень здорово, что не учитывается поправка на непрерывность - у нас многое вычисляется в таблицах 2Х2. Поэтому мы почти не достигаем оптимизации в случае расчета таблиц 2Х2 
Ну и тем не менее, думаю, что приведенных знаний достаточно, чтобы сделать вычисление Хи квадрат в excel чуть быстрее, чтобы сэкономить время на более важные вещи



Читайте нас в фейсбуке и телеграме



Комментариев нет:

Отправить комментарий

Популярные сообщения

п