Квартили в Excel: функция КВАРТИЛЬ и разница ВКЛ/ИСКЛ

Научрук просит описать баллы опросника как «Me [Q1; Q3]», а в Excel обнаруживаются сразу три похожие функции — КВАРТИЛЬ, КВАРТИЛЬ.ВКЛ и КВАРТИЛЬ.ИСКЛ. И на одних и тех же данных они дают разные числа.

Разберём один аргумент, который управляет всей функцией, разницу между «ВКЛ» и «ИСКЛ» и то, как из квартилей найти выбросы.

Что такое квартили и зачем они нужны

Квартили — это три числа, которые режут упорядоченный ряд на четыре равные по количеству человек части. Не по величине баллов, а именно по числу людей: в каждой четверти окажется по 25% выборки.

• Q1 (нижний квартиль) — граница, ниже которой лежит четверть значений.
• Q2 — это медиана, середина ряда.
• Q3 (верхний квартиль) — граница, ниже которой лежат три четверти значений.

Ситуативная тревожность 20 студентов: 48, 52, 45, 41, 54, 47, 50, 43, 46, 51, 44, 49, 53, 42, 47, 55, 40, 46, 50, 45. Отсортируем их и посмотрим, где пройдут границы.

Три ситуации, где квартили нужны обязательно.

• Шкала порядковая. Баллы опросника, ранги, ответы «согласен — скорее согласен». Складывать такие деления некорректно, поэтому вместо «M ± σ» пишут «Me [Q1; Q3]».
• Выборка маленькая или перекошенная. На 15–30 наблюдениях одно нетипичное значение уводит среднее и раздувает отклонение, а квартили этого не замечают.
• Нужно найти выбросы. Правило «1,5 межквартильных размаха» — стандартный способ отделить аномальные результаты от обычных, и считается оно только из Q1 и Q3.

Что такое квантили вообще и чем квартиль отличается от дециля и процентиля — разобрано в статье квартиль, дециль, процентиль, а математика расчёта вручную — в статье медиана и квартили. Здесь — только Excel.

Формула в Excel: =КВАРТИЛЬ(диапазон;1)

Пусть баллы лежат в столбце B со второй по двадцать первую строку. Нижний квартиль:

=КВАРТИЛЬ(B2:B21;1)

У функции два аргумента: диапазон с данными и номер квартиля. Второй и есть самое интересное место — от него зависит, какой из пяти показателей вы получите.

Что возвращает КВАРТИЛЬ при каждом значении второго аргумента

Аргументы 0 и 4 дублируют МИН и МАКС, аргумент 2 — функцию МЕДИАНА. Реально новую информацию дают только 1 и 3, поэтому в третью главу идут две формулы: КВАРТИЛЬ(…;1) и КВАРТИЛЬ(…;3).

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

КВАРТИЛЬ.ВКЛ и КВАРТИЛЬ.ИСКЛ: почему числа разные

В Excel 2010 старую КВАРТИЛЬ разделили на две функции. Считают они по разным правилам и на одних и тех же данных дают разные ответы.

КВАРТИЛЬ.ВКЛ — «включительно». Крайние значения ряда участвуют в разметке: позиция квартиля ищется по формуле (n − 1) × p + 1. Это ровно то, что делала старая КВАРТИЛЬ, — числа совпадают до знака.

КВАРТИЛЬ.ИСКЛ — «исключительно». Минимум и максимум считаются не границами, а такими же наблюдениями, как остальные, позиция ищется по формуле (n + 1) × p. Границы получаются шире.

Медиана у обеих одинаковая — 47. Расходятся Q1 и Q3, а вместе с ними межквартильный размах: 5,50 против 6,50, разница больше 18%.

Какую брать в дипломе. Берите КВАРТИЛЬ.ВКЛ: так считает старая КВАРТИЛЬ, Google Таблицы и «Пакет анализа». Вариант «ИСКЛ» нужен, если расчёт вы дублируете в SPSS или R — там он стоит по умолчанию.

Процентили: =ПЕРСЕНТИЛЬ для любого процента

Квартили режут ряд на четыре части. Если нужна произвольная граница — 10%, 90%, 95% — берите ПЕРСЕНТИЛЬ. Второй аргумент задаётся долей от нуля до единицы:

=ПЕРСЕНТИЛЬ(B2:B21;0,9)

Для наших данных это 53,10 балла: девять из десяти студентов набрали меньше. А =ПЕРСЕНТИЛЬ(B2:B21;0,1) даёт 41,90 — нижние 10%.

Квартили выражаются через процентили один в один: ПЕРСЕНТИЛЬ(B2:B21;0,25) вернёт те же 44,75, что и КВАРТИЛЬ(B2:B21;1). Пара «ВКЛ» и «ИСКЛ» у процентилей работает по тем же двум правилам.

Обратная задача — узнать, какому процентилю соответствует конкретный результат. Это функция ПРОЦЕНТРАНГ:

=ПРОЦЕНТРАНГ(B2:B21;52)

Ответ 0,842: студент с 52 баллами показал результат выше, чем 84% группы. Так переводят «сырой» балл в процентильную норму теста.

Межквартильный размах и поиск выбросов

Межквартильный размах (IQR) — это ширина средней половины данных, Q3 − Q1. В Excel он считается вычитанием:

=КВАРТИЛЬ.ВКЛ(B2:B21;3)-КВАРТИЛЬ.ВКЛ(B2:B21;1)

У нас 50,25 − 44,75 = 5,50 балла. Половина группы уместилась в интервал шириной пять с половиной баллов — выборка плотная.

Дальше работает правило 1,5 IQR: подозрительно всё, что вышло за границы Q1 − 1,5 × IQR и Q3 + 1,5 × IQR.

Границы выбросов по правилу 1,5 IQR на двадцати значениях тревожности

Минимум в наших данных — 40, максимум — 55, оба внутри границ. Выбросов нет, все двадцать значений идут в расчёт.

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

Запись Me [Q1; Q3] для баллов теста

Для порядковых шкал — баллов опросников, рангов, экспертных оценок — стандартная строка результата выглядит так:

Уровень ситуативной тревожности в экспериментальной группе составил 47,00 [44,75; 50,25] балла (Me [Q1; Q3]).

Три числа заменяют пару «M ± σ» и не врут на перекошенных данных. Скобки допустимы круглые и квадратные — главное держать один формат по всей работе и расшифровать обозначение в подписи к таблице.

Взяли для описания медиану с квартилями — сравнивать группы тоже нужно ранговыми методами: Манна-Уитни для независимых выборок, Вилкоксона для повторных замеров. Полные правила оформления — в статье как записывать M ± σ и Me (Q1; Q3), а соседние формулы центра — в статье медиана и мода в Excel.

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

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

Квартили считаются на листе «4. Описательная статистика»: там же медиана, минимум и максимум, межквартильный размах и готовая строка «Me [Q1; Q3]» — та самая, которую остаётся перенести в таблицу третьей главы.

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

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

Числа получатся одинаковые — разница в том, сколько работы остаётся на вас.

Калькулятор делает то, чего КВАРТИЛЬ не умеет в принципе: сам считает размах, отмечает выбросы, строит ящик с усами и пишет развёрнутый вывод для третьей главы. Бесплатно и без регистрации.

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

• Считают Q1 одной функцией, а Q3 другой. Размах и границы выбросов получаются из несовместимых чисел.Одна функция на всю работу — обычно КВАРТИЛЬ.ВКЛ.
• Ставят вторым аргументом 4, думая, что это «четвёртый квартиль». Excel вернёт максимум, а не границу верхней четверти.Верхняя граница — это аргумент 3. Границ всего три: 1, 2 и 3.
• Округляют квартили до целых «для красоты». 44,75 превращается в 45, и межквартильный размах меняется на полбалла.Оставляйте два знака после запятой, как и у остальных показателей.
• Захватывают в диапазон лишние ячейки. Номер испытуемого или строка «Итого» сдвинут все границы.Выделяйте только столбец со значениями — без заголовка и без итогов.
• Пишут «Me [Q1; Q3]», а группы сравнивают t-критерием. В таблице ранговый показатель, в проверке гипотезы — параметрический метод.Взяли квартили — берите Манна-Уитни или Вилкоксона.
• Удаляют всё, что вышло за 1,5 IQR. Правило только помечает подозрительные значения, а не приказывает их выбросить.Сначала проверьте, не описка ли это при вводе, и опишите решение в тексте главы.

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

Какую функцию взять для диплома — КВАРТИЛЬ, КВАРТИЛЬ.ВКЛ или КВАРТИЛЬ.ИСКЛ

КВАРТИЛЬ.ВКЛ, а в старой версии Excel или в Google Таблицах — просто КВАРТИЛЬ: это одна и та же математика. Вариант «ИСКЛ» берут, только если расчёт сверяется со SPSS или R.

Почему квартиль получился дробным, хотя все баллы целые

Граница четверти редко приходится ровно на человека. Excel считает промежуточное значение между двумя соседними числами ряда — это нормальный результат, а не ошибка.

Как посчитать процентиль в Excel

Функцией =ПЕРСЕНТИЛЬ(диапазон;доля), где доля задаётся числом от 0 до 1: 0,9 — это 90-й процентиль. Обратная задача, «какому проценту соответствует балл», решается через ПРОЦЕНТРАНГ.

Почему формула выдаёт #ИМЯ?

Функций с точкой в имени нет в Excel 2007 и старше, а в Google Таблицах они называются иначе. Замените КВАРТИЛЬ.ВКЛ на КВАРТИЛЬ — результат будет тот же.

Нужны ли квартили, если я уже посчитал стандартное отклонение

Нет, если данные близки к нормальному распределению: тогда хватит «M ± σ», см. стандартное отклонение в Excel. Квартили нужны для баллов опросников, малых выборок и перекошенных данных.

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

1. Соберите значения в один столбец — без заголовков и пустых строк внутри.
2. Введите =КВАРТИЛЬ.ВКЛ(диапазон;1) — это Q1, и =КВАРТИЛЬ.ВКЛ(диапазон;3) — это Q3.
3. Рядом посчитайте =МЕДИАНА(диапазон) — она же КВАРТИЛЬ.ВКЛ(диапазон;2).
4. Вычтите Q1 из Q3 — получите межквартильный размах.
5. Посчитайте границы «Q1 − 1,5 × IQR» и «Q3 + 1,5 × IQR» и проверьте, не вышло ли за них минимум или максимум.
6. Запишите результат как «Me [Q1; Q3]» с двумя знаками после запятой и расшифруйте обозначение в подписи.

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

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

• Квартиль, дециль, процентиль — чем они отличаются и где применяются.
• Медиана и квартили — как считаются Q1 и Q3 вручную и что показывает боксплот.
• Медиана и мода в Excel — соседние формулы центра и ловушки МОДА.
• Описательная статистика в Excel — весь набор показателей и «Пакет анализа».
• Выбросы в данных — что делать с найденным аномальным значением.
• Как записывать M ± σ и Me (Q1; Q3) — правила оформления записи.

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