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

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