Критерий Вилкоксона в Excel: как посчитать T по шагам

Вы открываете Excel, набираете «ВИЛКОКСОН» — и ничего не находится. Такой функции в программе нет и никогда не было.

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

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

Когда нужен Вилкоксон

T-критерий Вилкоксона проверяет один вопрос: изменился ли показатель у одних и тех же людей после вашего воздействия — тренинга, курса занятий, коррекционной программы.

Ключевое слово — «одних и тех же». Каждому значению «до» соответствует своё «после» у того же человека. Если вы сравниваете две разные группы, нужен не Вилкоксон, а Манна-Уитни.

• Замеры повторные. Одна группа измерена дважды: до и после. Строки строго попарно.
• Данные — баллы теста или анкеты. Порядковая шкала, где расстояние между 3 и 4 баллами не равно расстоянию между 8 и 9.
• Или разности распределены ненормально. Числовые измерения тоже подойдут, если проверка нормальности провалилась.
• Есть хотя бы 5 ненулевых разностей. Меньше — критерий физически не способен показать значимость.

Развёрнутое сравнение с параметрическим вариантом — в статье Стьюдент или Вилкоксон. Если разности всё же распределены нормально, точнее будет парный критерий Стьюдента: на одних и тех же данных он чувствительнее.

Почему в Excel его нет

В Excel зашиты только параметрические тесты: ТТЕСТ, ФТЕСТ, ХИ2ТЕСТ. Ранговых критериев среди них нет — ни Вилкоксона, ни Манна-Уитни, ни Краскела-Уоллиса.

Надстройка «Пакет анализа» тоже не спасает: в её списке есть t-критерий, дисперсионный анализ и корреляция, но непараметрики там нет вообще.

Значит, остаётся ручная сборка из четырёх шагов.

Дальше — данные для примера. В дипломе по психологии у 18 подростков замерили индекс агрессивности по опроснику Басса-Дарки до и после программы профилактики. Данные лежат в столбцах B (до) и C (после), строки со второй по девятнадцатую.

Шаг 1: разности и почему нули отбрасываются

В столбце D, начиная с D2:

=B2-C2

Протяните до D19. Направление вычитания выбираете сами — важно только не менять его от строки к строке. Здесь агрессивность должна снижаться, поэтому «до минус после» даёт плюс при улучшении.

Дальше главное. Строки, где разность равна нулю, из расчёта выбрасываются полностью. Человек, у которого «до» и «после» совпали, не голосует ни за изменение, ни против него — ранжировать его нечем.

Чтобы нули не мешали, в столбце E считаем модуль, а нулевые строки оставляем пустыми:

=ЕСЛИ(D2=0;"";ABS(D2))

Теперь объём выборки для критерия — это количество заполненных ячеек в E:

=СЧЁТ(E2:E19)

У нас совпали значения у трёх подростков. В таблице 18 строк, а n = 15 — и дальше везде работает именно эта пятнадцатка.

Если нулевых пар много — скажем, семь из двадцати, — это само по себе результат: программа не подействовала на треть выборки, и в третьей главе это стоит обсудить.

Шаг 2: ранги модулей разностей

Ранжируется не сама разность, а её модуль. Знак нужен позже, отдельно.

В столбце F, начиная с F2:

=ЕСЛИ(E2="";"";СЧЁТЕСЛИ($E$2:$E$19;"<"&E2)+(СЧЁТЕСЛИ($E$2:$E$19;E2)+1)/2)

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

Одинаковые модули получают средний ранг. Если разность «3 балла» встретилась дважды, обе строки делят первое и второе места и получают 1,5. Именно это и делает вторая половина формулы.

Проверить себя просто: сумма всех рангов обязана равняться n × (n + 1) / 2. При n = 15 это 120. Не сошлось — где-то остался ранг у нулевой строки.

Индекс агрессивности 18 подростков до и после программы, баллы

Максимальный ранг 15 — по числу оставшихся пар, а не по числу строк в таблице.

Шаг 3: суммы рангов и T

Теперь знак возвращается в игру. Ранги складываются отдельно для положительных и отрицательных разностей:

=СУММЕСЛИ($D$2:$D$19;">0";$F$2:$F$19)

=СУММЕСЛИ($D$2:$D$19;"<0";$F$2:$F$19)

СУММЕСЛИ смотрит на знак в столбце D, а суммирует соответствующие ранги из столбца F. Нулевые строки не попадают ни в одно из условий и выпадают сами.

Эмпирическое T — меньшая из двух сумм:

=МИН(H2;H3)

Результат расчёта по нашим восемнадцати подросткам

Меньшая сумма — 5, её и берём. Смысл у неё простой: это вес всех изменений, которые пошли против общей тенденции. У нас 13 подростков стали спокойнее, двое — агрессивнее, и оба «против» оказались слабенькими: разности всего 3 и 4 балла, ранги 1,5 и 3,5.

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

Здесь и живёт главная контринтуитивность критерия.

У Стьюдента, хи-квадрата и корреляции всё привычно: больше эмпирическое — сильнее эффект. У Вилкоксона наоборот. Чем МЕНЬШЕ T, тем сильнее сдвиг, потому что T — это вес исключений. Ноль означает, что все без исключения изменились в одну сторону.

Поэтому и правило перевёрнутое: сдвиг значим, если T эмпирическое МЕНЬШЕ ИЛИ РАВНО критического.

Критическое значение Excel не считает — готовой функции нет. Его берут из таблицы по числу пар n.

Критические значения T для двустороннего критерия

Для n = 15 критическое значение — 25. Наше T = 5 меньше, значит сдвиг статистически значим. Больше того, 5 меньше 15, то есть значим и на строгом уровне p ≤ 0,01.

Если кафедра просит ещё и p-значение, при n больше 15 его оценивают через нормальное приближение:

=(ABS(H4-H1*(H1+1)/4)-0,5)/КОРЕНЬ(H1*(H1+1)*(2*H1+1)/24)

Это z, положите его в H6. Само p получается из него:

=2*(1-НОРМСТРАСП(H6))

На наших данных z = 3,10 и p = 0,002. В новых версиях функция называется НОРМ.СТ.РАСП(H6;ИСТИНА), в английском Excel — NORMSDIST.

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

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

Критерию отведён лист «13. T Вилкоксона»: там уже стоят и разности, и отбрасывание нулей, и ранги со средним при совпадениях, и обе суммы, и сам T — вместе с готовой формулировкой для работы. Критическое значение под ваше n лежит рядом, на листе «22. Таблицы значений»: колонка «T крит.» для n от 5 до 25.

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

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

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

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

Непараметрический критерий описывают медианой, а не средним: медиана устойчива к выбросам, а среднее по баллам теста — величина сомнительная. Как её посчитать, разобрано в статье про медиану и квартили.

В формулировку идут четыре вещи: сам критерий, T, число пар n и p.

Для оценки достоверности сдвига применялся T-критерий Вилкоксона. Медиана индекса агрессивности снизилась с 22,5 до 18,0 балла. Сдвиг статистически значим: T = 5 при n = 15; T крит = 25 (p ≤ 0,05); p = 0,002. Размер эффекта r = 0,80 — большой.

Отдельно поясните судьбу нулевых пар — рецензент это спросит:

У трёх подростков показатель не изменился, нулевые разности исключены из расчёта, поэтому число пар для критерия n = 15 при объёме выборки 18 человек.

Если сдвига не нашлось, пишут так же честно:

Достоверного сдвига не выявлено: T = 47 при n = 15; T крит = 25 (p ≤ 0,05); p = 0,478.

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

• Оставляют нулевые разности в расчёте. Число пар завышается, критическое значение берётся не то, и вывод может перевернуться.Считайте n как количество ненулевых разностей: =СЧЁТ(E2:E19) по столбцу модулей.
• Считают, что большое T — это хорошо. Логика перевёрнута по сравнению со Стьюдентом: T — вес изменений «против течения».Сдвиг значим, когда T эмпирическое меньше или равно критического.
• Ранжируют разности со знаком. Тогда все минусы уезжают в начало ряда и ранги получаются бессмысленными.Ранжируйте только модули: ABS(D2). Знак учитывается на шаге с СУММЕСЛИ.
• Забывают про средний ранг при одинаковых модулях. Обычный РАНГ ставит двум тройкам ранг 1 и пропускает второй — сумма перестаёт сходиться.Проверяйте: сумма всех рангов обязана равняться n × (n + 1) / 2.
• Применяют Вилкоксона к двум разным группам. Критерий рассчитан только на связанные выборки — попарно, человек с самим собой.Для экспериментальной и контрольной групп нужен Манна-Уитни.
• Сортируют столбцы «до» и «после» по отдельности. Пары рассыпаются, и разности считаются между разными людьми.Сортируйте всю таблицу целиком, вместе со столбцом фамилий или номеров.

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

Есть ли в Excel функция критерия Вилкоксона

Нет — ни в русской версии, ни в английской, ни в Google Таблицах. ТТЕСТ считает только параметрический критерий Стьюдента. Вилкоксона собирают вручную из четырёх формул или считают в калькуляторе.

Почему число пар меньше числа испытуемых

Из расчёта выбрасываются все строки, где «до» и «после» совпали. Если из 18 человек у троих показатель не изменился, критерий работает с n = 15 — и критическое значение берётся тоже для 15.

Что делать, если нулевых разностей больше половины

Критерий станет бессильным: на 5-6 оставшихся парах значимость почти недостижима. Это повод описать результат словами — программа не подействовала на большую часть группы — и подкрепить его критерием знаков или простой долей изменившихся.

T получилось 0 — это ошибка

Нет, это идеальный результат: все без исключения изменились в одну сторону. Сдвиг значим при любом n от 6 и выше.

Можно ли считать Вилкоксона в «Пакете анализа»

Нельзя, непараметрических критериев в надстройке нет вообще. Их считают в SPSS, jamovi, R — или онлайн, без установки программ.

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

1. Разложите данные в два столбца: «до» в B, «после» в C, строго по парам в строках.
2. В D посчитайте разности =B2-C2, в E — модули с отсевом нулей: =ЕСЛИ(D2=0;"";ABS(D2)).
3. Определите n: =СЧЁТ(E2:E19) — это и есть число пар для критерия.
4. В F проставьте ранги модулей формулой со средним рангом при совпадениях.
5. Сложите ранги отдельно для плюсов и минусов через СУММЕСЛИ, возьмите меньшую сумму — это T.
6. Сравните T с критическим значением для вашего n: меньше или равно — сдвиг значим.
7. Запишите в работу T, n, p и медианы «до» и «после».

Или пропустите шаги 2-6: вставьте два столбца в калькулятор Вилкоксона — нули отбросятся сами, вместе с готовым выводом и рисунком.

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

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

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