Ящик с усами в Excel: как построить диаграмму размаха

Ящик с усами просят в третьей главе, когда распределение ненормальное и показатель описывают медианой, а не средним. Хорошая новость: с Excel 2016 такой тип диаграммы встроен в программу и строится в три клика.

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

Что показывает ящик с усами

Диаграмма размаха умещает в одну картинку то, на что уходит целая строка таблицы: где середина данных, насколько они разбросаны и есть ли среди них выскочки.

Прямоугольник-ящик тянется от первого квартиля Q1 до третьего Q3 — внутри него лежит средняя половина выборки. Черта внутри — медиана, а не среднее. Усы дотягиваются до крайних значений, которые ещё считаются типичными. Точки за усами — выбросы.

Зачем этот рисунок нужен и как его читать — подробно в статье «Диаграмма размаха boxplot». Здесь занимаемся только построением в Excel.

Пять чисел, которые нужны

Любой ящик с усами стоит на пятичисловой сводке: минимум, Q1, медиана, Q3, максимум. Каждое считается одной функцией.

Пример. В дипломе по физической культуре у 20 школьников измерили гибкость — наклон вперёд из положения сидя, см: 2, 4, 5, 6, 6, 7, 8, 9, 9, 9, 10, 11, 12, 12, 13, 14, 15, 16, 18, 28.

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

Пятичисловая сводка гибкости, n = 20

В английском Excel это MIN, QUARTILE, MEDIAN, MAX. Второй аргумент КВАРТИЛЬ — номер квартиля: 1 для Q1, 2 для медианы, 3 для Q3.

Как считаются медиана и квартили и почему медиана честнее среднего на скошенных данных — в статье «Медиана и мода в Excel».

Excel 2016 и новее: готовый тип диаграммы

С версии 2016 «Ящик с усами» стоит в меню рядом с гистограммой и диаграммой Парето. Пять чисел для него считать не нужно — Excel найдёт квартили сам.

1. Выделите столбец с исходными значениями — все 20 чисел вместе с заголовком.
2. Вставка → Диаграммы → Вставить статистическую диаграмму (значок с ящиком и точками) → Ящик с усами.
3. Диаграмма появится на листе готовой.

Дальше правый клик по ящику → Формат ряда данных. Там переключатели, которые и превращают диаграмму в ту, что примут на защите.

• Показать точки выбросов. Включите: точки за усами — главный смысл рисунка. Без них Excel растянет усы до максимума и спрячет выскочку.
• Показать средний маркер. Крестик среднего поверх ящика. Полезно, когда нужно показать, как сильно среднее «уехало» от медианы.
• Показать внутренние точки. Все наблюдения точками рядом с ящиком. На выборке до 30 человек выглядит хорошо, на 200 — превращается в кашу.
• Метод расчёта квартилей. «Включающая медиана» совпадает с `КВАРТИЛЬ.ВКЛ`, «исключающая» — с `КВАРТИЛЬ.ИСКЛ`. Возьмите тот же вариант, что и в таблице работы, иначе числа в тексте и на рисунке разойдутся.
• Несколько групп — несколько ящиков. Выделите два столбца (КГ и ЭГ) — Excel поставит ящики рядом на одной оси. Это лучшая иллюстрация к сравнению групп.
• Заголовок и ось. Заголовок внутри картинки удалите, подпись пойдёт снизу по ГОСТу. Ось Y подпишите с единицами: «Гибкость, см».

В Google Таблицах отдельного типа «Ящик с усами» нет — там его собирают из диаграммы-свечи, логика такая же, как в старом Excel ниже.

Excel до 2016: через биржевую диаграмму

В Excel 2007–2013 нужного типа в меню нет. Рабочий обходной путь — биржевая диаграмма «открытие-максимум-минимум-закрытие»: она рисует вертикальную линию от минимума до максимума и прямоугольник между открытием и закрытием. Это ровно ящик с усами, если подставить в столбцы правильные числа.

Порядок столбцов строгий — Excel читает их по позиции, а не по названию.

Дальше по шагам:

1. Выделите четыре столбца с числами вместе с подписью строки.
2. Вставка → Другие диаграммы → Биржевая (в Excel 2013 — Вставка → Рекомендуемые диаграммы → Все диаграммы → Биржевая) → вариант с четырьмя рядами «Открытие-максимум-минимум-закрытие».
3. Excel нарисует линию мин–макс и прямоугольник Q1–Q3. Медианы пока нет.
4. Конструктор → Выбрать данные → Добавить ряд со значением медианы. Правый клик по новому ряду → Изменить тип диаграммы для ряда → Точечная.
5. Маркер медианы сделайте горизонтальным штрихом: Формат ряда → Маркер → Встроенный → тип «—», размер побольше.
6. Прямоугольник Q1–Q3 красится через Формат ряда → Повышающиеся полосы: заливка и контур задаются там, а не кликом по самому ящику.

Метод честно рабочий, но муторный: на два ящика рядом уходит минут двадцать. Поэтому в старом Excel часто проще посчитать пять чисел, нарисовать ящик в другом месте и вставить картинкой.

Как найти выбросы: правило 1,5 межквартильных размаха

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

Нижняя граница = Q1 − 1,5 × IQR, верхняя граница = Q3 + 1,5 × IQR, где IQR = Q3 − Q1.

Пусть IQR лежит в F2, границы — в G2 и G3:

=КВАРТИЛЬ(B2:B21;1)-1,5*F2
=КВАРТИЛЬ(B2:B21;3)+1,5*F2

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

=ЕСЛИ(ИЛИ(B2<$G$2;B2>$G$3);"выброс";"")

Гибкость 28 см выше верхней границы 23,0 — это выброс, и на диаграмме он станет отдельной точкой. Верхний ус при этом дотянется до 18 см. Что делать с найденным значением — удалять или оставлять — разбираем в статье «Выбросы в данных».

Пять чисел уже посчитаны в готовом файле

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

Пять чисел лежат на листе «4. Описательная статистика»: минимум, Q1, медиана, Q3, максимум и объём выборки в одной колонке. Оттуда их остаётся перенести в столбцы биржевой диаграммы — или просто сверить с тем, что нарисовал Excel.

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

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

Числа везде получатся одинаковые. Разница в том, сколько шагов остаётся на вас: границы Тьюки, поиск выбросов и формулировка вывода — или ни одного. Сам ящик в любом случае рисует Excel, а калькулятор отдаёт для него готовые числа.

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

Подставьте свои числа в готовые формулировки.

Распределение показателя гибкости отклонилось от нормального, поэтому данные представлены медианой и квартилями: Me = 9,5 см [6,75; 13,25], межквартильный размах IQR = 6,5 см.

Для наглядности распределение показано диаграммой размаха («ящик с усами»): границы ящика соответствуют квартилям Q1 и Q3, черта внутри — медиане, усы построены по критерию Тьюки (1,5 × IQR).

Значение 28 см вышло за верхнюю границу 23,0 см и классифицировано как выброс; повторная проверка протокола подтвердила корректность измерения, значение оставлено в выборке.

Подпись под рисунком — снизу и без номера в самой картинке: «Рисунок 4 — Распределение показателя гибкости у школьников 5-х классов (n = 20)».

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

• Строят диаграмму «Ящик с усами» по пяти числам. Excel примет их за выборку из пяти наблюдений и нарисует ящик по ним.Готовый тип диаграммы кормите исходным столбцом; пять чисел нужны только для биржевого способа.
• Путают порядок столбцов в биржевой диаграмме. Excel читает их по позиции: открытие, максимум, минимум, закрытие. Перепутали — ящик вывернется наизнанку.Порядок строго Q1 → макс → мин → Q3, подписи столбцов на это не влияют.
• Берут разные методы квартилей в таблице и на рисунке. В тексте `КВАРТИЛЬ.ИСКЛ`, а на диаграмме «включающая медиана» — числа не сойдутся.Выберите один метод и держите его во всей работе, метод укажите в примечании к таблице.
• Оставляют усы до абсолютного максимума. Тогда выброс прячется внутри уса, и смысл рисунка теряется.Включите «Показать точки выбросов» или в биржевом способе подставьте крайние значения внутри границ Тьюки.
• Строят ящик на 5–7 значениях. Квартили на такой выборке неустойчивы, и рисунок обманывает.До 8 человек показывайте сами точки на числовой оси, а не ящик.
• Подписывают черту в ящике как среднее. Внутри ящика медиана; на скошенных данных это разные числа.Среднее выводите отдельным маркером-крестиком и так и подписывайте.

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

В моём Excel нет типа «Ящик с усами»

Значит, версия старше 2016 — тип появился именно в ней. Проверьте: Вставка → Диаграммы, раздел «Статистические». Если раздела нет, стройте через биржевую диаграмму или посчитайте пять чисел и нарисуйте ящик в онлайн-инструменте.

Можно ли построить ящик с усами по готовым пяти числам

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

Почему квартили на диаграмме не совпали с формулой КВАРТИЛЬ

Excel использует два метода расчёта — «включающая медиана» и «исключающая медиана». Первый совпадает с КВАРТИЛЬ и КВАРТИЛЬ.ВКЛ, второй — с КВАРТИЛЬ.ИСКЛ. Переключается в «Формат ряда данных». Медиана при этом совпадает всегда, расходятся только Q1 и Q3.

Как показать на одном рисунке две группы

Разложите КГ и ЭГ по двум соседним столбцам и выделите оба сразу — Excel поставит два ящика на одной оси. Различие медиан после этого подтвердите критерием, картинка сама по себе ничего не доказывает.

Ящик с усами или гистограмма — что выбрать

Гистограмма показывает форму распределения, ящик — его сводку и выбросы. Для одной группы нагляднее гистограмма, для сравнения двух-трёх групп — ящики: они помещаются рядом на одной оси. Как построить первую — в статье «Гистограмма в Excel».

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

1. Сложите значения в один столбец — без заголовков и пустых строк внутри.
2. Посчитайте пять чисел: МИН, КВАРТИЛЬ(…;1), МЕДИАНА, КВАРТИЛЬ(…;3), МАКС.
3. Найдите IQR = Q3 − Q1 и границы Тьюки: Q1 − 1,5 × IQR и Q3 + 1,5 × IQR.
4. В Excel 2016 и новее выделите исходный столбец → Вставка → Статистическая диаграмма → Ящик с усами, включите показ выбросов.
5. В версиях постарше разложите числа по столбцам «открытие-максимум-минимум-закрытие» и добавьте медиану отдельным точечным рядом.
6. Уберите заголовок внутри картинки, подпишите ось с единицами и вставьте рисунок в Word с подписью снизу.

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

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

• Диаграмма размаха boxplot — что показывает ящик с усами и как его читать.
• Медиана и мода в Excel — функции для черты внутри ящика.
• Квартиль, дециль, процентиль — откуда берутся Q1 и Q3.
• Выбросы в данных — что делать с точками за усами.
• Гистограмма в Excel — второй рисунок для описания распределения.

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