Как создать диаграмму «ящик с усами» в Microsoft Excel

Что такое диаграмма «ящик с усами»

Диаграмма «ящик с усами» — график, который показывает распределение числовых данных с помощью пяти ключевых показателей: минимум, первый квартиль (Q1), медиана, третий квартиль (Q3) и максимум. Она особенно полезна для сравнения распределений между группами и выявления размаха и выбросов.

Кратко о терминах:

• Минимум — наименьшее значение в наборе.
• Q1 (первый квартиль) — значение на 25-м процентиле.
• Медиана — центральное значение (50-й процентиль).
• Q3 (третий квартиль) — значение на 75-м процентиле.
• Максимум — наибольшее значение в наборе.

Когда использовать диаграмму «ящик с усами»

Используйте диаграмму «ящик с усами», когда нужно:

• сравнить распределения числовых показателей между группами;
• показать медиану и разброс данных (интерквартильный размах);
• быстро выявить асимметрию и потенциальные выбросы.

Когда она не подходит:

• для отображения точной временной динамики (используйте линейный график);
• если требуется показать доли частей (используйте круговые/столбчатые диаграммы);
• при очень небольших наборах данных (n < 5) визуализация может вводить в заблуждение.

Подготовка данных

Перед построением проверьте данные:

• столбцы содержат только числовые значения;
• отсутствуют пустые строки в середине диапазона (или замените их на NA/удалите);
• группы разделены в соседних столбцах или присутствует столбец «Группа» для сводных сравнений.

Важно: для корректного расчёта квартилей учитывайте метод, который использует Excel (QUARTILE.EXC или QUARTILE.INC). Разные методы дают немного разные значения при небольших наборах.

Как создать диаграмму «ящик с усами» в Microsoft Excel — пошагово

1. Выделите диапазон с данными: либо перетяните мышью, либо щёлкните верхнюю левую ячейку, удерживая Shift, и щёлкните правую нижнюю. Для нескольких групп — выделите все соответствующие столбцы.
2. Откройте вкладку Вставка на ленте.
3. В группе Диаграммы выберите меню Вставить статистическую диаграмму и кликните Ящик с усами.
4. Диаграмма появится на листе. Перетащите её и измените размер, если нужно.

Советы при выделении данных:

• Для сравнения нескольких категорий помещайте каждую категорию в отдельный столбец;
• Если у вас одна колонка значений и колонка с метками группы, используйте сводную таблицу или Power Query для предварительной трансформации данных.

Как проверить значения диаграммы вручную в Excel

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

Вычисление минимума, медианы и максимума

1. Выберите ячейку для результата (например, рядом с исходными данными).
2. На вкладке Формулы откройте Другие функции → Статистические и выберите MIN. В аргументе укажите диапазон данных и нажмите OK.
3. Аналогично выполните для MEDIAN и MAX.

Прямые формулы в строке формул:

• =MIN(A2:A101)
• =MEDIAN(A2:A101)
• =MAX(A2:A101)

Вычисление квартилей

Excel предлагает два основных подхода к квартилям:

• QUARTILE.EXC — исключающий метод (интерполяция без включения 0 и 1);
• QUARTILE.INC — включающий метод (включает крайние процентили).

Шаги:

1. Выберите ячейку для Q1.
2. В Формулы → Другие функции → Статистические выберите QUARTILE.EXC или QUARTILE.INC (в зависимости от желаемого метода).
3. В аргументах укажите диапазон и номер квартиля (1 для Q1, 3 для Q3).
4. Нажмите OK.

Примеры формул:

• =QUARTILE.EXC(A2:A101,1)
• =QUARTILE.EXC(A2:A101,3)

Важно: стандартный встроенный график Excel использует определённую методику для отображения «усов» (whiskers). Если вам критично соответствие академическому определению (например, whiskers до 1.5 × IQR), вычислите границы вручную и при необходимости постройте кастомную диаграмму с помощью сочетания столбчатых диаграмм и полос ошибок.

Настройка диаграммы: оформление и элементы

Выделите диаграмму — справа появится меню с быстрыми кнопками: элементы диаграммы (оси, подписи, легенда) и стили.

Элементы диаграммы

• Заголовок диаграммы: редактируйте текст напрямую на графике.
• Оси: задайте подписи и формат чисел (например, 0.0, проценты, денежные единицы).
• Метки данных: отобразите медиану или конкретные статистики рядом с ящиком.
• Легенда: переместите легенду в удобное место или отключите её, если не нужна.

Стили и цвета

• Выберите готовую тему стилей, чтобы быстро настроить палитру и эффект. Наведение показывает предварительный просмотр.
• Чтобы выделить важную группу, используйте насыщенный цвет для одного ящика и приглушённые оттенки для остальных.

Перемещение и изменение размера

• Для перемещения: наведите курсор, пока не появится четырёхстрелочная ручка, затем перетащите диаграмму.
• Для изменения размера: перетяните круги по краю рамки диаграммы.

Ограничения, ошибки и «когда это не работает»

• Небольшие выборки: при n < 5 квартильная визуализация теряет смысл и может показывать некорректные интерпретации.
• Разные методы расчёта квартилей: QUARTILE.EXC vs QUARTILE.INC дают отличия; укажите в отчёте использованный метод.
• Выбросы: Excel может отображать точки-выбросы по-разному; если вам нужна строгая обработка выбросов, фильтруйте их заранее.
• Грубое смешение типов: если в столбце есть текст или пустые строки, функции вернут ошибки или некорректные результаты.

Примеры ошибок и как их избегать:

• Ошибка: «#VALUE!» при вызове функции — проверьте диапазон и содержимое ячеек.
• Проблема: видимые «усы» доходят до крайних значений, а вы ожидали 1.5×IQR — вычислите вручную границы и отобразите на графике дополнительно.

Альтернативные подходы

• Power Query: удобно для подготовки и агрегации данных по группам перед построением графиков.
• Сводная таблица + визуализация: используйте, если требуется динамическое сравнение групп.
• R / Python (matplotlib, seaborn): для точного соответствия научным определениям и гибкой настройки отображения выбросов.
• Ручная сборка диаграммы: если нужна нетипичная визуализация (напр., whiskers по 1.5×IQR), соберите «ящик» из столбчатых диаграмм и примените верхние/нижние полосы ошибок.

Как интерпретировать диаграмму — практические эвристики

• Широкий ящик означает большой интерквартильный размах (IQR) — высокая изменчивость в середине распределения.
• Смещение медианы ближе к Q1 или Q3 указывает на асимметрию в сторону большего числа маленьких или больших значений.
• Длинные усы и отдельные точки-выбросы указывают на наличие экстремов и необходимость проверки данных.

Ментальная модель: представьте ящик как «среднюю половину» набора, усы — как крайние наблюдения, а точки вне усов — потенциальные аномалии.

Ролевые чеклисты перед публикацией отчёта

Для аналитика:

• Проверить, что набор данных очищен и числовые значения корректны.
• Указать метод расчёта квартилей (EXC/INC).
• Добавить подписи осей и единицы измерения.

Для преподавателя/лектора:

• Убедиться, что выборка достаточна для визуализации.
• Подготовить поясняющий слайд о том, как читать ящик и усы.

Для менеджера/заказчика:

• Выделить ключевую группу цветом.
• Добавить краткий вывод под диаграммой (что важно для решения).

Мини‑методика: от данных до слайда (быстро)

1. Очистить и проверить данные (удалить NaN, конвертировать в числа).
2. Вычислить MIN, Q1, MEDIAN, Q3, MAX и при необходимости IQR.
3. Построить диаграмму в Excel и настроить стили.
4. Добавить подписи и краткий вывод (1–2 предложения).
5. Сделать контрольную проверку формул и сравнить значения с отображаемыми на диаграмме.

Критерии приёмки

• Диаграмма строится без ошибок и корректно отображает диапазон выбранных данных.
• Подписи осей и заголовок ясны и содержат единицы измерения.
• Метод расчёта квартилей задокументирован.
• При необходимости — отмечены и прокомментированы выбросы.

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

• IQR — интерквартильный размах (Q3 − Q1).
• Whiskers — «усы» диаграммы, линии от ящика до минимума и максимума или до вычисленных границ.
• Выброс (outlier) — значение заметно отличающееся от остальных наблюдений.

Заключение

Диаграмма «ящик с усами» — удобный инструмент для визуального сравнения распределений и обнаружения выбросов. В Microsoft Excel её легко создать и настроить, но важно понимать, какие методы расчёта квартилей используются и какие ограничения есть у встроенного построителя. Перед публикацией проверяйте вычисления функциями MIN/MEDIAN/MAX/QUARTILE и убедитесь, что оформление диаграммы понятно аудитории.

Важно: если вам нужна строгая статистическая интерпретация (например, правила для выбросов на основе 1.5×IQR), рекомендуется рассчитать границы вручную и добавить их на диаграмму или использовать статистические языки (R/Python) для верификации.

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

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