Критерий Манна-Уитни в Excel: расчёт по шагам без функции

Студент открывает Excel, набирает «=МАНН» — и подсказка молчит. Функции для U-критерия Манна-Уитни в Excel нет: ни среди формул, ни в надстройке «Пакет анализа».

Считать всё равно можно. Критерий держится на рангах, а ранги Excel расставляет одной формулой — весь расчёт укладывается в три шага.

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

Когда нужен Манна-Уитни

U-критерий сравнивает две независимые группы и отвечает на вопрос: различаются они по уровню показателя или разница случайная.

• Группы состоят из разных людей — экспериментальный и контрольный класс, юноши и девушки, спортсмены и не занимающиеся спортом.
• Данные — баллы теста, анкеты или места — порядковая шкала, где расстояние между 3 и 4 баллами не равно расстоянию между 8 и 9.
• Или распределение далеко от нормального — сильный перекос, выбросы, маленькая выборка.
• В каждой группе хотя бы 3-5 человек — на меньших объёмах различия не станут значимыми ни при каком раскладе.

Если сомневаетесь между Манна-Уитни и Стьюдентом, разберитесь сначала со связанными и независимыми выборками, а потом посмотрите развёрнутое сравнение — Стьюдент или Манна-Уитни.

Почему в Excel его нет и что делать

Набор функций Excel собран вокруг параметрических методов: ТТЕСТ, ФТЕСТ, ХИ2ТЕСТ, КОРРЕЛ. Ранговых критериев среди них нет — ни Манна-Уитни, ни Вилкоксона, ни Краскела-Уоллиса.

«Пакет анализа» тоже не спасает: в надстройке 19 инструментов, и все они параметрические — что в неё входит, разобрано отдельно. Английская версия и Google Таблицы устроены так же.

Остаётся три пути: собрать расчёт из формул самому (об этом статья), взять готовый файл с настроенным листом или посчитать в онлайн-калькуляторе.

Возьмём сюжет из диплома по педагогике: тест учебной мотивации, 30 подростков — 15 в экспериментальном классе и 15 в контрольном.

• Контроль: 31, 35, 28, 33, 37, 30, 34, 29, 36, 32, 37, 33, 26, 35, 31
• Эксперимент: 35, 39, 32, 37, 41, 34, 38, 30, 42, 36, 33, 40, 31, 37, 35

Разложить данные на листе нужно в один столбец, а не в два — иначе ранжировать общий ряд будет неудобно.

Как разложить данные на листе Excel

Сначала идут 15 строк контрольной группы с меткой 1, затем 15 строк экспериментальной с меткой 2. Данные занимают строки со 2-й по 31-ю.

Шаг 1: проранжировать объединённый ряд

Главная мысль критерия: обе группы сваливают в общую кучу и расставляют по росту от самого маленького значения к самому большому. Ранг — это место в этой общей очереди.

В ячейку D2 вводится формула и протягивается до D31:

=СЧЁТЕСЛИ($B$2:$B$31;"<"&B2)+(СЧЁТЕСЛИ($B$2:$B$31;B2)+1)/2

Формула из двух частей.

Первая часть — СЧЁТЕСЛИ($B$2:$B$31;"<"&B2) — считает, сколько значений во всём ряду меньше текущего. Это количество тех, кто стоит в очереди впереди.

Вторая часть — (СЧЁТЕСЛИ($B$2:$B$31;B2)+1)/2 — добавляет среднее место среди одинаковых значений. Если значение уникально, здесь получается ровно 1, и ранг выходит целым.

Проверка на нашем ряду: балл 30 встречается дважды и занимает 4-е и 5-е места — обоим ставится 4,5. Балл 31 встречается трижды, занимает 6, 7 и 8-е места — всем троим ставится 7.

Готовые ранги стоит проверить: сумма всех рангов обязана равняться N·(N + 1)/2. У нас 30 · 31 / 2 = 465.

=СУММ(D2:D31)

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

Шаг 2: посчитать U

Дальше нужны две суммы рангов — по каждой группе отдельно. Здесь и пригодилась метка в столбце C:

=СУММЕСЛИ($C$2:$C$31;1;$D$2:$D$31)

Для второй группы то же самое с меткой 2. На наших данных получилось R₁ = 175 и R₂ = 290, в сумме 465 — как и должно быть.

Теперь сама формула критерия. Для каждой группы:

U = n₁·n₂ + n₁·(n₁ + 1)/2 − R₁

В ячейках это выглядит так, если R₁ лежит в F2:

=15*15+15*16/2-F2

Считаем оба значения:

Суммы рангов и U по каждой группе (n₁ = n₂ = 15)

Эмпирическое U — меньшее из двух: =МИН(G2;G3), то есть U = 55.

Проверить себя легко: U₁ + U₂ всегда равно n₁ · n₂. У нас 170 + 55 = 225 = 15 · 15. Если равенство не выполняется, ошибка в рангах или суммах.

Шаг 3: сравнить с критическим значением

Эмпирическое U сравнивают с табличным — критическим для ваших объёмов групп. И здесь главная ловушка метода.

Для двух групп по 15 человек критическое значение при p ≤ 0,05 равно 64. Наше U = 55, и 55 ≤ 64 — различия статистически значимы.

Критические значения U для равных групп, двусторонний критерий, p ≤ 0,05

Групп разного размера в этой табличке нет — для них нужна полная таблица по паре n₁ и n₂. Она лежит и в готовом файле, и в калькуляторе, который берёт нужное значение автоматически.

Как получить p-значение

Многие кафедры просят не табличное сравнение, а само p. Excel его для U не считает, но при группах примерно от 10 человек работает нормальное приближение — через z:

=(55-15*15/2)/КОРЕНЬ(15*15*(15+15+1)/12)

Получается z = −2,38. Дальше двустороннее p:

=2*(1-НОРМ.СТ.РАСП(ABS(z);ИСТИНА))

Итог: p = 0,017. Тот же вывод, что и по таблице, но в привычном виде. В Excel 2007 и старше функция называется НОРМСТРАСП, в английской версии — NORM.S.DIST.

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

Если считать нужно не одно число, а всю статистику для третьей главы — есть готовая книга Excel. Вы вставляете свои данные в жёлтые ячейки, и всё считается само.

Критерию отведён лист «12. U Манна-Уитни»: ранги объединённого ряда проставляются сами, со средним рангом при совпадениях, суммы рангов и оба U считаются формулами. Критическое значение подтягивается с листа «22. Таблицы значений» по вашим n₁ и n₂ — искать его в учебнике не нужно.

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

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

Число U везде получится одно и то же. Ручной расчёт в Excel — самый трудоёмкий из трёх: ранги, суммы, два U, поиск критического значения в учебнике, отдельная формула для p. Каждый шаг — место для ошибки, которую в готовой таблице не видно.

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

Для непараметрического критерия группы описывают медианой, а не средним. Медиана в Excel — =МЕДИАНА(диапазон): у нас 33 балла в контроле и 36 в эксперименте.

Кроме U и p кафедры всё чаще требуют размер эффекта — он показывает не «есть ли различия», а насколько они велики. Для Манна-Уитни это r = |z| / √N:

=ABS(J2)/КОРЕНЬ(30)

Здесь J2 — ячейка с z, а 30 — общее число участников. Получается r = 0,44 — эффект средней силы (0,1 — малый, 0,3 — средний, 0,5 и выше — большой).

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

Сравнение уровня учебной мотивации в экспериментальной и контрольной группах проводилось с помощью U-критерия Манна-Уитни. Медиана в экспериментальной группе составила 36 баллов, в контрольной — 33 балла. Различия статистически значимы: U = 55 при U крит = 64 (n₁ = n₂ = 15; p = 0,017); размер эффекта r = 0,44 соответствует среднему уровню.

Если различий не нашлось, вывод пишется так же честно:

Различия между группами статистически не значимы: U = 92 при U крит = 64 (n₁ = n₂ = 15; p = 0,395). Нулевая гипотеза не отклоняется.

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

• Ранжируют каждую группу отдельно. Обе получают ранги от 1 до 15, суммы совпадают, U всегда показывает «различий нет».Ранги считаются по объединённому ряду из всех 30 значений — формула должна ссылаться на весь столбец баллов.
• Берут РАНГ без поправки на совпадения. Двум одинаковым баллам достаётся один ранг, а следующий пропускается, и сумма рангов не сходится с N(N+1)/2.Используйте формулу со СЧЁТЕСЛИ или РАНГ.СР — они дают средний ранг.
• Сравнивают U как t: «больше критического — значит значимо». Вывод получается прямо противоположный правильному.У Манна-Уитни различия значимы при U эмп ≤ U крит. Чем меньше U, тем сильнее различаются группы.
• Берут большее из двух U вместо меньшего. С U = 170 вместо 55 значимые различия превращаются в незначимые.В расчёт всегда идёт меньшее значение: =МИН(U1;U2), а проверка — U₁ + U₂ = n₁ · n₂.
• Применяют критерий к замерам «до и после» одних и тех же людей. Это связанные выборки, для них U не предназначен.Для «до и после» берите критерий Вилкоксона.
• Пишут в выводы средние арифметические. Ранговый критерий сравнивает не средние, и рецензент это заметит.Описывайте группы медианой и квартилями — =МЕДИАНА() и =КВАРТИЛЬ().

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

Есть ли Манна-Уитни в «Пакете анализа»

Нет. В надстройке только параметрические инструменты: t-тесты, дисперсионный анализ, регрессия, корреляция. Ранговых критериев там не появилось ни в одной версии Excel, включая 365.

Как считать, если группы разного размера

Точно так же. Формула U для каждой группы использует её собственное n: U₁ = n₁·n₂ + n₁(n₁+1)/2 − R₁, U₂ = n₁·n₂ + n₂(n₂+1)/2 − R₂. Меняется только критическое значение — его берут по паре n₁ и n₂ из полной таблицы.

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

Таблицы критических значений обычно обрываются на 20-25. Дальше переходят на нормальное приближение: считают z и p по формулам из третьего шага и сравнивают p с 0,05 — так же поступают SPSS и наш калькулятор.

Работает ли этот расчёт в Google Таблицах

Да, полностью. СЧЁТЕСЛИ там называется COUNTIF, СУММЕСЛИ — SUMIF, НОРМ.СТ.РАСП — NORM.S.DIST. Логика и порядок шагов те же.

Сумма рангов не совпала с 465 — где ошибка

Чаще всего диапазон в формуле закреплён не полностью (пропали $) и при протягивании съехал. Ещё варианты: в столбце баллов затесался текст или пустая ячейка, либо часть строк осталась без метки группы.

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

1. Сложите все значения обеих групп в один столбец, рядом поставьте метку группы — 1 или 2.
2. В соседнем столбце проставьте ранги: =СЧЁТЕСЛИ($B$2:$B$31;"<"&B2)+(СЧЁТЕСЛИ($B$2:$B$31;B2)+1)/2.
3. Проверьте сумму рангов — она обязана равняться N·(N + 1)/2.
4. Посчитайте суммы рангов по группам через СУММЕСЛИ.
5. Найдите оба U по формуле n₁·n₂ + n(n + 1)/2 − R и возьмите меньшее.
6. Сравните с критическим значением: U эмп ≤ U крит — различия значимы.
7. Добавьте p через нормальное приближение, медианы групп и размер эффекта r.

Или пропустите шаги 2-7: вставьте два столбца в калькулятор Манна-Уитни — ранги, U, критическое значение, p и вывод посчитаются сами.

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

• Критерий Манна-Уитни: полное руководство — математика метода и таблица критических значений.
• Стьюдент или Манна-Уитни — как выбрать критерий под свои данные.
• Ранжирование данных — что такое ранги и откуда берутся связки.
• t-критерий Стьюдента в Excel — соседняя задача третьей главы.
• Корреляция в Excel — там ранги нужны для Спирмена.
• Что умеет «Пакет анализа» — почему рангового критерия в нём нет.

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