Диаграмма со смешным названием “Ящик с усами” используется в Excel, как правило, для проведения статистического анализа. Когда имеется массив данных для нескольких тестовых групп за различные периоды, и необходимо понять, как изменился разброс показателей — не обойтись без этой диаграммы.
Конечно, если вывести все эти показатели в таблицу — то какой-то результат тоже можно увидеть. Но визуализации в виде диаграмм всегда воспринимаются лучше, чем просто цифры (тем более, что не все руководители дружат с цифрами).
Еще несколько лет назад для построения диаграммы “Ящик с усами” нужно было пользоваться специализированным софтом (или как минимум Python) или очень сильно колдовать в excel. Но начиная с версии Excel- 2016, данный вид диаграммы входит в стандартный пакет.
В этой статье мы рассмотрим два варианта построения диаграммы Ящик с усами: простой — для счастливых обладателей Excel от 2016-й версии и моложе, и сложный — “танцы с бубном” для тех, кому с версией Excel повезло меньше.
Содержание статьи:
Из чего состоит диаграмма
Смысл диаграммы Ящик с усами в том, чтобы показать основные характеристики статистической выборки данных: распределение данных между квартилями, среднее значение, медиану, максимальное и минимальное значения, а также выбросы данных.
Думаю, понятно, что ящик — это прямоугольник с заливкой, а усы — это черточки над и под прямоугольником.
Ящик — это межквартильный размах (или расстояние) — отделяет ¼ и ¾ выборки данных. Если ящик, условно говоря, большой — больше другого ящика — это означает, что выборка относительно однородна, и большая часть данных сконцентрирована вокруг медианы.
Черточки усов — это максимальное и минимальное значение (без учета выбросов).
Ус снизу — это разница между минимумом и 1-м квартилем.
Ус сверху — это разница между 3-м квартилем и максимумом.
Крестик посередине — среднее арифметическое значение по выборке.
Черта посередине ящика — медиана по выборке.
Выбросы — значения, сильно отклоняющиеся от основного массива выборки (выходит за пределы 1,5 межквартильных размаха от ближайшего квартиля).
Чтобы стало еще понятнее, рассмотрим построение диаграммы Ящик с усами на примере в excel. В нашем примере есть две возрастных группы покупателей: от 18 до 30 лет и от 30 до 45 лет. По ним имеем данные о суммах в чеках, на которые они совершали покупки.
Позже была проведена маркетинговая акция, и нужно понять, что изменилось в распределении сумм покупок в каждой группе.
Диаграмма Ящик с усами встроенным инструментом Excel (для версий от 2016 и новее)
Часть выборки данных выглядит следующим образом:
В левом столбце показатель периода (было до акции — стало после акции). Вверху названия групп (18-30, 31-45), и в ячейках суммы, на которые совершались покупки.
Внимание: таблица не должна содержать никаких итогов!
Все, что нужно сделать — это выделить массив данных вместе с названием периода и заголовками столбцов и далее: вкладка Вставка — блок Диаграммы — кнопка Гистограммы — выбрать Ящик с усами.
Переименовываем диаграмму и наслаждаемся результатом.
Произведем некоторые настройки.
Во-первых, выведем легенду, чтобы было понятно, где какая группа.
Во-вторых, добавим среднюю линию, показывающую тренд между периодами. Среднюю линию можно добавить, если есть не менее двух рядов данных.
Правой кнопкой мыши щелкнем на “ящике”, и выберем Формат ряда данных, установим “галку” Средняя линия.
Здесь же можно регулировать отображение точек выбросов на диаграмме.
Диаграмма готова.
Что можно понять из диаграммы Ящик с усами, которую мы сейчас построили:
- В группе 18-30 лет средний чек немного вырос. Смотрим на крестик, который отображает среднее значение, и на среднюю линию, которая идет слегка вверх.
- В группе 31-45 лет средний чек, наоборот, прилично упал. Это говорит о том, что формат акции не попал в эту целевую аудиторию.
- Медианная сумма, на которую чаще всего совершали покупки (линия посередине ящика) также немного выросла для группы 18-30, и упала для группы 31-45, что также говорит о неудачной акции для второй группы.
- Размер ящика для группы 18-30 увеличился, также и низ, и верх ящика заняли более высокие позиции. Снова “за” успешность акции для этой категории покупателей, они стали совершать более разнообразные покупки, и в целом тратить больше денег.
- А группа 31-45, напротив, стала тратить меньше денег (низ и верх ящика снизили позиции на графике), и размер ящика также уменьшился, как и размер усов. Т.е.покупки стали более фиксированными (возможно, остались самые постоянные покупатели с фик
- Присутствует также один выброс для группы 31-45 — точка на уровне 7800. Это чек, сумма которого сильно отклоняется от основной массы покупок.
Диаграмма Ящик с усами в excel при помощи гистограммы с накоплением (для версий Excel до 2016 г)
Работать будем с той же выборкой данных, только переформатируем ее так, чтобы для каждого ящика был отдельный столбец.
Создадим дополнительную таблицу, в которой пропишем определенные формулы. Форму таблицы и формулы смотрите на картинке:
Выделим заголовки и строки Низ, 2Q Коробка и 3Q Коробка (как на картинке).
Перейдем во вкладку Вставка — Гистограмма — Гистограмма с накоплением.
Теперь нужно нарисовать усы, начнем с нижних. Выделим на диаграмме ряд Низ, и перейдем на вкладку Конструктор — Макеты диаграмм — Добавить элементы диаграмм — Предел погрешностей — Дополнительные параметры погрешностей.
В окне Формат предела погрешностей нужно установить параметры в следующем порядке:
- Вертикальный предел погрешностей — Направление — Минус
- Величина погрешности — Пользовательская
- Нажать кнопку Укажите значение
- Поле Положительное значение ошибки оставить без изменений. Поле Отрицательное значение ошибки активировать и выделить значения из таблицы, соответствующие строке “Усы -” (только цифры).
Должны появиться вот такие черточки.
Теперь похожим образом нужно нарисовать верхние усы. Для этого выделим ряд 3Q Коробка, и снова перейдем на вкладку Конструктор — Макеты диаграмм — Добавить элементы диаграмм — Предел погрешностей — Дополнительные параметры погрешностей.
Здесь нужно указать направление вертикального предела погрешностей Плюс, величина погрешности Пользовательская, нажать кнопку Укажите значения. В поле Положительное значение установить курсор и выделить значения из строки “Усы +”. Поле Отрицательное значение ошибки оставить без изменений.
Должны появиться верхние усы.
Осталось немного доработать внешний вид диаграммы.
Уберем заливку с ряда Низ (синий в примере). Для этого выделим его, щелкнем правой кнопкой мыши — Формат ряда данных — и в блоке Заливка укажем Нет заливки.
Не выходя из окна Формат ряда данных, изменим цвет для ящиков.
Осталось добавить среднее значение (крестик).
Для этого выделим строку Среднее (только числа) и нажмем Ctrl + С.
Теперь выделим диаграмму и нажмем Ctrl + V. Должно получиться что-то похожее на картинку:
Правой кнопкой мыши щелкаем на новом ряде данных и выбираем Изменить тип диаграммы для ряда.
И для нового ряда выбираем тип диаграммы Точечная.
Обязательно снимите “галку” Вспомогательная ось”, если она установилась.
Осталось изменить точку на крестик (по желанию). Дважды щелкаем на любой точке, и в открывшемся окне Формат ряда данных выбираем: Маркер — Встроенный — крестик в выпадающем списке.
Диаграмма готова.
Конечно, у нее есть несколько недостатков по сравнению со встроенным инструментом:
- из диаграммы намеренно убраны точки выбросов, поскольку они существенно исказили бы результат. Точки выбросов можно нарисовать отдельно аналогично тому, как мы создавали крестики для среднего значения. Или не использовать их совсем.
- Нет средней линии между блоками одного ряда. При желании и сильно заморочившись, их можно нарисовать при помощи графиков. Возможно, в этой статье будет продолжение, как это сделать.
- Ряды данных не разделены визуально. Где ряд Было и Стало, видно только из названия.
Но в целом, если нет возможности установить более новую версию Excel, то это неплохой обходной путь создать диаграмму Ящик с усами в Excel.
