Создание диаграммы «ящик с усами» в Microsoft Excel

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

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

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

Ящик визуально покрывает промежуток от Q1 до Q3, внутри ящика проходит линия медианы. «Усы» (whiskers) тянутся от краёв ящика до минимума и максимума (или до выбранной границы при исключении выбросов).

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

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

Когда «ящик с усами» полезен и когда не подходит

Когда использовать:

• Сравнение распределений нескольких групп (оценки, измерения).
• Быстрая визуализация медианы и межквартильного размаха (IQR).
• Поиск выбросов и асимметрии.

Когда он не подойдёт:

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

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

Перед построением убедитесь, что ваши данные:

• Числовые и представлены в одном столбце для каждой группы (или в ряде столбцов, если групп несколько).
• Не содержат пустых строк внутри диапазона — лучше убрать/отфильтровать их.
• Если есть текстовые метки групп, разместите их в заголовках столбцов.

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

Шаг за шагом: как создать диаграмму в Excel

1. Откройте книгу Excel и лист с набором данных.
2. Выделите диапазон данных. Можно кликнуть первую ячейку, удерживать мышь и протянуть, или кликнуть левую верхнюю ячейку, удержать Shift и кликнуть правую нижнюю.
3. Перейдите на вкладку Insert на ленте.
4. В группе Chart нажмите Insert Statistical Chart и выберите Box and Whisker.
5. Диаграмма появится на листе. При необходимости перетащите её и измените размер.

Если у вас локализованный Excel на русском языке, названия вкладок могут отличаться (например, «Вставка» вместо Insert), но логика та же: вставка → статистические диаграммы → «ящик с усами».

Как вручную перепроверить значения (MIN, Q1, медиана, Q3, MAX)

Excel рассчитывает квартильные значения автоматически для построения диаграммы. Но полезно перепроверить числа с помощью функций. Это удобно для отчётов или для отладки.

Общие функции, которые используются:

• MIN(range) — минимум.
• MEDIAN(range) — медиана.
• MAX(range) — максимум.
• QUARTILE.EXC(range, k) — квартиль по методу EXC (k = 1 для Q1, k = 3 для Q3).
• QUARTILE.INC(range, k) — альтернатива (инклюзивный метод). Уточните, какой метод использует ваша версия Excel или ваша практика.

Пример пошагово:

1. Выберите ячейку для MIN.
2. На вкладке Formulas выберите More Functions → Statistical → MIN. Введите диапазон и нажмите ОК.
3. Повторите для MEDIAN и MAX, выбрав соответствующие функции.
4. Для квартилей выберите QUARTILE.EXC (или QUARTILE.INC), укажите диапазон и в поле аргумента Quart введите 1 для Q1 и 3 для Q3.

Если в меню вы видите только список функций, можно также ввести формулу вручную, например:

• =MIN(A2:A50)
• =MEDIAN(A2:A50)
• =QUARTILE.EXC(A2:A50,1)
• =QUARTILE.EXC(A2:A50,3)
• =MAX(A2:A50)

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

Настройка внешнего вида диаграммы

После вставки диаграммы можно менять её элементы и стиль.

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

Нажмите на диаграмму — справа появится меню с кнопками Chart Elements и Chart Styles. В разделе элементов можно включить или выключить:

• Оси (Axis)
• Заголовок диаграммы (Chart Title)
• Подписи данных (Data Labels)
• Легенду (Legend)

Для легенды можно выбрать расположение. Для подписей данных — формат и точность.

Стили диаграммы

Через Chart Styles можно менять внешний вид: темы, цветовые схемы, предустановленные стили. Наведите курсор на стиль, чтобы увидеть превью, и кликните, чтобы применить.

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

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

Практическая методика: от данных до презентации (мини-SOP)

1. Подготовка данных: очистите, удалите ненужные пробелы, убедитесь в числовом формате.
2. Копирование: создайте резервную копию диапазона на отдельном листе.
3. Построение диаграммы: выделите диапазон → Insert → Insert Statistical Chart → Box and Whisker.
4. Верификация: вычислите MIN, Q1, MEDIAN, Q3, MAX формулами и сверяйте с отображаемыми значениями.
5. Настройка: включите/выключите оси и легенду; примените цветовую схему и добавьте подписи данных при необходимости.
6. Подготовка для слайдов: экспортируйте диаграмму как изображение (правый клик → Save as Picture) или копируйте в PowerPoint.
7. Документация: укажите метод вычисления квартилей (EXC/INC) рядом с диаграммой в подписи.

Контроль качества: критерии приёмки диаграммы

• Диаграмма показывает ожидаемые значения медианы и квартилей, совпадающие с ручными вычислениями.
• Магнитные подписи данных читаемы и не перекрывают элементы диаграммы.
• Цвета согласованы с корпоративным стилем и доступны для людей с дальтонизмом (контраст ≥ 3:1 для фон/текст).
• Если график публикуется, рядом указана методика расчёта (EXC/INC) и количество наблюдений.

Быстрые подсказки по интерпретации

• Если медиана ближе к нижней границе ящика, распределение смещено вправо (правостороннее).
• Широкий IQR указывает на высокую вариативность в центральной половине данных.
• Длинные «усы» по одной стороне могут указывать на асимметрию или выбросы.

Альтернативы и когда их выбирать

• Гистограмма — для изучения формы распределения и числа пиков.
• Диаграмма размаха с точками (strip plot/jittered points) — если важно показать каждое наблюдение.
• Плот плотности (kernel density) — для гладкой оценки распределения.
• Violin plot — сочетает box plot и плотность; полезен когда важна форма распределения.

Частые ошибки и как их избежать

• Неправильная интерпретация «усов» как всегда равных границ: уточняйте, считаются ли они как минимумы/максимумы или обрезаны с учётом IQR.
• Использование box plot для очень малых выборок — результаты ненадёжны.
• Игнорирование метода вычисления квартилей: EXC и INC дают разные значения, особенно при небольших n.

Роль‑ориентированные чеклисты

Для аналитика данных:

• Проверить очистку данных и типы столбцов.
• Рассчитать MIN/Q1/MEDIAN/Q3/MAX вручную для контроля.
• Задокументировать метод расчёта квартилей.

Для презентационного дизайнера:

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

Для менеджера продукта:

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

Примеры проверочных тестов (Test cases)

1. Набор из 11 чисел, симметрично распределённых — медиана должна совпасть с центральным значением.
2. Набор с явным выбросом (например, {1,2,2,3,3,4,100}) — выброс должен быть виден и не смещать внутриквартильный размах.
3. Две группы с разными дисперсиями — диаграммы должны показать разницу в IQR.

Сводка и рекомендации

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

Коротко: используйте box plot для обзора распределений и поиска выбросов; используйте проверки и документацию для достоверности результатов.

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

• IQR: межквартильный размах (Q3 − Q1).
• Выброс: значение, значительно отличающееся от остальных наблюдений.
• QUARTILE.EXC / QUARTILE.INC: разные методы вычисления квартилей в Excel.