Хи-квадрат в Excel: функция ХИ2ТЕСТ и ожидаемые частоты

В Excel есть функция ХИ2ТЕСТ, и она сразу возвращает готовое p-значение — считать формулу критерия руками не нужно.

Но студенты застревают на первом же шаге. Функция просит две таблицы: фактические частоты и ожидаемые. Фактические у вас есть, а ожидаемые Excel не считает — их надо построить самому.

🧮Онлайн-калькулятор критерия хи-квадратПосчитайте свои данные за пару минут — нажмите, чтобы открыть→

Когда нужен χ²: частоты, а не измерения

Критерий Стьюдента и Манна-Уитни сравнивают числа, которые вы измерили: секунды, баллы, сантиметры. Хи-квадрат работает иначе — он сравнивает количество людей, попавших в категории.

Типовой сюжет из диплома по педагогике: 60 школьников, 30 занимались по авторской программе и 30 по обычной. Норматив сдали 18 человек из экспериментальной группы и 9 из контрольной. Вопрос: результат связан с программой или это случайность?

Такие данные укладывают в таблицу сопряжённости — она же и есть исходник для расчёта.

Прежде чем открывать Excel, проверьте, что ваши данные подходят критерию.

• Признак категориальный — «справился / не справился», «юноша / девушка», «высокий / средний / низкий уровень», а не секунды и баллы.
• В клетках количество людей — 18 и 12, а не 60 % и 40 %.
• Каждый человек в одной клетке — суммы по строкам и столбцам сходятся к общему числу обследованных.
• Группы независимы — это разные люди. Для одних и тех же людей «до и после» берут критерий Макнемара.
• Ожидаемая частота в каждой клетке не меньше 5 — иначе критерий даст завышенную значимость.

Шаг 1: постройте таблицу фактических частот

Это то, что вы посчитали по своим протоколам. Разместите её на листе так, чтобы в клетках были только числа — без заголовков внутри диапазона.

Пусть данные лежат в B2:C3:

Фактические частоты — то, что получилось в исследовании

Итоги по строкам и столбцам посчитайте обычной =СУММ() — они понадобятся на следующем шаге.

Шаг 2: посчитайте ожидаемые частоты

Ожидаемая частота — это сколько человек оказалось бы в клетке, если бы связи между группой и результатом не было вообще. Excel их не строит: ни ХИ2ТЕСТ, ни «Пакет анализа» такой таблицы не создают.

Формула простая: итог по строке умножить на итог по столбцу и разделить на общее число человек.

В Excel это одна формула с закреплёнными ссылками, растянутая на всю таблицу. Если фактические частоты лежат в B2:C3, итоги строк — в D2:D3, итоги столбцов — в B4:C4, а общее число — в D4, то в первую клетку ожидаемых частот пишем:

=$D2*B$4/$D$4

Дальше растягиваем её вправо и вниз на все четыре клетки. Знаки доллара держат нужные ссылки на месте: столбец итогов не съезжает вбок, строка итогов — вниз.

Ожидаемые частоты для нашего примера

Сравните две таблицы: 18 против ожидаемых 13,5 и 9 против 13,5. Расхождение есть — критерий скажет, достаточно ли оно велико.

Шаг 3: =ХИ2ТЕСТ(факт;ожид) и =ХИ2ОБР(0,05;df)

Теперь считаем. Функция принимает два диапазона — сначала фактические частоты, потом ожидаемые:

=ХИ2ТЕСТ(B2:C3;B7:C8)

Она возвращает сразу p-значение — в нашем примере 0,0195. Это меньше 0,05, значит различия достоверны.

Проблема в том, что в таблицу диплома нужно ещё само χ². Его ХИ2ТЕСТ не отдаёт, поэтому считаем отдельной формулой:

=СУММПРОИЗВ((B2:C3-B7:C8)^2/B7:C8)

Получается 5,45. Число степеней свободы для таблицы 2×2 всегда равно 1, в общем случае — (строки − 1) × (столбцы − 1).

Критическое значение берём функцией:

=ХИ2ОБР(0,05;1)

Ответ — 3,841. Наши 5,45 больше, вывод тот же: нулевую гипотезу отклоняем.

Поправка Йейтса для таблиц 2×2

Хи-квадрат — приближённый критерий: он подгоняет дискретные частоты под непрерывное распределение. На таблице 2×2 это приближение грубее всего, и критерий немного завышает значимость.

Лечится поправкой Йейтса на непрерывность: перед возведением в квадрат из модуля разности вычитают 0,5.

=СУММПРОИЗВ((ABS(B2:C3-B7:C8)-0,5)^2/B7:C8)

В нашем примере получается 4,31 вместо 5,45. Вывод не изменился — 4,31 всё ещё больше критических 3,841, — но так бывает не всегда: результат на границе значимости после поправки часто уходит в «различий нет».

Применять поправку или нет. Для таблиц 2×2 её приводят по умолчанию — так строже и честнее. Для таблиц больше 2×2 поправка Йейтса не используется.

Когда χ² применять нельзя

Главное ограничение — размер ожидаемых частот. Если хотя бы в одной клетке ожидаемая частота меньше 5, приближение ломается и критерий начинает выдавать значимость там, где её нет.

Проверить легко, ожидаемые частоты у вас уже посчитаны:

=МИН(B7:C8)

Меньше 5 — переходите на точный критерий Фишера. Он перебирает все возможные таблицы с такими же итогами и считает вероятность напрямую, без приближений. В Excel готовой функции для него нет — только онлайн или в статистическом пакете.

Второе ограничение мягче: объём выборки. При общем числе меньше 20 человек хи-квадрат лучше не применять даже с поправкой — берите Фишера.

Всё считается в готовом файле

Если возиться с двумя таблицами и долларами в ссылках не хочется — есть готовая книга Excel. Вписываете четыре числа, остальное считается само.

Хи-квадрату отведён лист «14. χ² Пирсона». У него своя таблица ввода: критерий работает не с измерениями, а с количеством людей, поэтому данные с листа «Мои данные» здесь не нужны. Вписываете четыре числа — и ниже сразу появляются ожидаемые частоты, χ², поправка Йейтса, критическое значение, p, коэффициент φ и предупреждение, если ожидаемая частота опустилась ниже 5.

Три способа посчитать — что выбрать

← листайте вправо, чтобы сравнить →

Число везде получится одно и то же. Разница в том, сколько шагов остаётся на вас и кто проверит, что критерий вообще применим к вашим данным.

Что писать в дипломе

В таблицу и в текст идут четыре величины: χ², число степеней свободы, p и размер эффекта.

Коэффициент сопряжённости φ для таблицы 2×2 считается в одну формулу — корень из χ², делённого на общее число человек:

=КОРЕНЬ(5,45/60)

Получается 0,30. Шкала простая: 0,1 — слабая связь, 0,3 — средняя, 0,5 — сильная. Для таблиц больше 2×2 вместо φ берут V Крамера.

Готовая формулировка:

Доля справившихся с нормативом в экспериментальной группе составила 60 % (18 из 30) против 30 % (9 из 30) в контрольной. Различия статистически достоверны: χ² = 5,45, df = 1, p = 0,020. С поправкой Йейтса χ² = 4,31, вывод сохраняется. Коэффициент сопряжённости φ = 0,30 указывает на связь средней силы.

Проценты в тексте приводить можно и нужно — читателю так понятнее. Главное, чтобы в расчёт ушли количества, а рядом с процентом стояло само число человек.

Частые ошибки

• Подставляют в ХИ2ТЕСТ проценты или доли. Критерий чувствителен к объёму выборки, а проценты его скрывают.В таблице должно быть количество людей: 18 и 12, а не 60 % и 40 %.
• Округляют ожидаемые частоты до целых. 13,5 превращается в 14 — и χ² уезжает.Оставляйте дробные значения, округляйте только итоговый результат.
• Путают порядок аргументов. Сначала фактические частоты, потом ожидаемые — если поменять местами, p будет другим.Читайте формулу как «сравни факт с ожиданием»: =ХИ2ТЕСТ(факт;ожид).
• Включают в диапазон строку и столбец итогов. Excel посчитает, но по данным, где каждый человек учтён дважды.В диапазон входят только клетки самой таблицы, без «Всего».
• Применяют χ² при ожидаемой частоте меньше 5. Значимость получается завышенной.Проверьте минимум по таблице ожидаемых и при необходимости возьмите точный критерий Фишера.
• Пишут в диплом только p. Рецензент спросит, какое было χ² и сколько степеней свободы.Приводите связку «χ² = …, df = …, p = …» и рядом размер эффекта.

Частые вопросы

Почему ХИ2ТЕСТ выдаёт ошибку #Н/Д

Таблицы фактических и ожидаемых частот разного размера. Проверьте, что оба диапазона одинаковые: 2×2 и 2×2. Ошибка появляется и когда в один из диапазонов случайно попала строка итогов.

Можно ли посчитать хи-квадрат через «Пакет анализа»

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

Что делать, если групп три или категорий больше двух

Всё то же самое, размер таблицы значения не имеет. Меняется только число степеней свободы: (строки − 1) × (столбцы − 1). Для таблицы 3×2 это будет 2, и в ХИ2ОБР подставляем =ХИ2ОБР(0,05;2). Поправка Йейтса при этом не нужна.

Хи-квадрат показал значимость — можно ли сказать, что программа сработала

Критерий говорит только о том, что распределение признака в группах различается. Направление вы читаете из самой таблицы: 60 % против 30 %. А вот причинность зависит от дизайна исследования, а не от статистики.

Чем хи-квадрат отличается от углового преобразования Фишера

Оба сравнивают доли, но φ*-критерий Фишера работает с двумя долями и терпимее к маленьким выборкам. Разбор различий — в статье хи-квадрат или угловое преобразование Фишера.

Короткий алгоритм

1. Постройте таблицу фактических частот — только количество людей, без процентов.
2. Посчитайте итоги по строкам, столбцам и общий с помощью =СУММ().
3. Рядом сделайте таблицу ожидаемых частот формулой =$D2*B$4/$D$4 и растяните её.
4. Проверьте минимум ожидаемых: меньше 5 — переходите на точный критерий Фишера.
5. Введите =ХИ2ТЕСТ(факт;ожид) — это p, и =СУММПРОИЗВ((факт-ожид)^2/ожид) — это χ².
6. Для таблицы 2×2 добавьте поправку Йейтса и посчитайте φ.
7. Запишите в работу «χ² = …, df = …, p = …» вместе с процентами и размером эффекта.

Или пропустите все семь шагов: скачайте файл выше либо вставьте частоты в калькулятор.

Что ещё почитать

• Критерий хи-квадрат Пирсона: полное руководство — формула, степени свободы и таблица критических значений.
• Таблица сопряжённости: как построить и читать — подробнее про исходные данные критерия.
• Хи-квадрат или угловое преобразование Фишера — что выбрать для сравнения долей.
• V Крамера: размер эффекта для хи-квадрат — как оценить силу связи.
• t-критерий Стьюдента в Excel — если данные всё-таки измерения, а не категории.

Не уверены, какой критерий нужен вашим данным, — загляните в базу методов или напишите нам, поможем с расчётами.