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 чуть быстрее, чтобы сэкономить время на более важные вещи

Пост оказался полезен?

если Вы захотите выразить мне благодарность за интересный пост, вы можете перевести небольшую сумму мне на Яндекс кошелек (кликните по кнопке Перевести) 
или сделать перевод на карту Сбербанка,
Номер карты 676 280 38 921 538 46 57 - укажите "за пост  Как считать Хи квадрат в excel". 
Или просто покликайте на директ рекламу ниже на странице - у вас это отнимет несколько секунд
спасибо!

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

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