Диаграмма Ящик с усами в Excel

Диаграмма со смешным названием “Ящик с усами” используется в Excel, как правило, для проведения статистического анализа. Когда имеется массив данных для нескольких тестовых групп за различные периоды, и необходимо понять, как изменился разброс показателей — не обойтись без этой диаграммы.

Конечно, если вывести все эти показатели в таблицу — то какой-то результат тоже можно увидеть. Но визуализации в виде диаграмм всегда воспринимаются лучше, чем просто цифры (тем более, что не все руководители дружат с цифрами).

Еще несколько лет назад для построения диаграммы “Ящик с усами” нужно было пользоваться специализированным софтом (или как минимум Python) или очень сильно колдовать в excel. Но начиная с версии Excel- 2016, данный вид диаграммы входит в стандартный пакет.

В этой статье мы рассмотрим два варианта построения диаграммы Ящик с усами: простой — для счастливых обладателей Excel от  2016-й версии и моложе, и сложный — “танцы с бубном” для тех, кому с версией Excel повезло меньше.

 

 

 

Из чего состоит диаграмма

 

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

 

Ящик с усами - для чего нужна эта диаграмма в Excel

 

Думаю, понятно, что ящик — это прямоугольник с заливкой, а усы — это черточки над и под прямоугольником.

Ящик — это межквартильный размах (или расстояние) — отделяет ¼ и ¾ выборки данных. Если ящик, условно говоря, большой — больше другого ящика — это означает, что выборка относительно однородна, и большая часть данных сконцентрирована вокруг медианы.

Черточки усов — это максимальное и минимальное значение (без учета выбросов). 

Ус снизу — это разница между минимумом и 1-м квартилем.

Ус сверху — это разница между 3-м квартилем и максимумом.

Крестик посередине — среднее арифметическое значение по выборке.

Черта посередине ящика — медиана по выборке. 

 

 

Выбросы — значения, сильно отклоняющиеся от основного массива выборки (выходит за пределы 1,5 межквартильных размаха от ближайшего квартиля).

Чтобы стало еще понятнее, рассмотрим построение диаграммы Ящик с усами на примере в excel. В нашем примере есть две возрастных группы покупателей: от 18 до 30 лет и от 30 до 45 лет. По ним имеем данные о суммах в чеках, на которые они совершали покупки.

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

 

Диаграмма Ящик с усами встроенным инструментом Excel (для версий от 2016 и новее)

 

Часть выборки данных выглядит следующим образом:

 

Ящик с усами - для чего нужна эта диаграмма в Excel

 

В левом столбце показатель периода (было до акции — стало после акции). Вверху названия групп (18-30, 31-45), и в ячейках суммы, на которые совершались покупки.

Внимание: таблица не должна содержать никаких итогов!

Все, что нужно сделать — это выделить массив данных вместе с названием периода и заголовками столбцов и далее: вкладка Вставка — блок Диаграммы — кнопка Гистограммы — выбрать Ящик с усами.

 

Ящик с усами - для чего нужна эта диаграмма в Excel

Переименовываем диаграмму и наслаждаемся результатом.

 

Ящик с усами - для чего нужна эта диаграмма в Excel

 

Произведем некоторые настройки. 

Во-первых, выведем легенду, чтобы было понятно, где какая группа.

Ящик с усами - для чего нужна эта диаграмма в Excel

 

 

Во-вторых, добавим среднюю линию, показывающую тренд между периодами. Среднюю линию можно добавить, если есть не менее двух рядов данных.

Правой кнопкой мыши щелкнем на “ящике”, и выберем Формат ряда данных, установим “галку” Средняя линия.

Здесь же можно регулировать отображение точек выбросов на диаграмме.

 

Ящик с усами - для чего нужна эта диаграмма в Excel

 

Диаграмма готова.

Ящик с усами - для чего нужна эта диаграмма в Excel

 

 

Что можно понять из диаграммы Ящик с усами, которую мы сейчас построили:

  • В группе 18-30 лет средний чек немного вырос. Смотрим на крестик, который отображает среднее значение, и на среднюю линию, которая идет слегка вверх.
  • В группе 31-45 лет средний чек, наоборот, прилично упал. Это говорит о том, что формат акции не попал в эту целевую аудиторию.
  • Медианная сумма, на которую чаще всего совершали покупки (линия посередине ящика) также немного выросла для группы 18-30, и упала для группы 31-45, что также говорит о неудачной акции для второй группы.
  • Размер ящика для группы 18-30 увеличился, также и низ, и верх ящика заняли более высокие позиции. Снова “за” успешность акции для этой категории покупателей, они стали совершать более разнообразные покупки, и в целом тратить больше денег.
  • А группа 31-45, напротив, стала тратить меньше денег (низ и верх ящика снизили позиции на графике), и размер ящика также уменьшился, как и размер усов. Т.е.покупки стали более фиксированными (возможно, остались самые постоянные покупатели с фик
  • Присутствует также один выброс для группы 31-45 — точка на уровне 7800. Это чек, сумма которого сильно отклоняется от основной массы покупок.

 

Диаграмма Ящик с усами в excel при помощи гистограммы с накоплением (для версий Excel до 2016 г)

 

Работать будем с той же выборкой данных, только переформатируем ее так, чтобы для каждого ящика был отдельный столбец.

Ящик с усами - для чего нужна эта диаграмма в Excel

 

 

Создадим дополнительную таблицу, в которой пропишем определенные формулы. Форму таблицы и формулы смотрите на картинке:

Ящик с усами - для чего нужна эта диаграмма в Excel

 

 

Выделим заголовки и строки Низ, 2Q Коробка и 3Q Коробка (как на картинке).

Ящик с усами - для чего нужна эта диаграмма в Excel

 

 

Перейдем во вкладку Вставка — Гистограмма — Гистограмма с накоплением.

Ящик с усами - для чего нужна эта диаграмма в Excel

 

 

Теперь нужно нарисовать усы, начнем с нижних. Выделим на диаграмме ряд Низ, и перейдем на вкладку Конструктор — Макеты диаграмм — Добавить элементы диаграмм — Предел погрешностей — Дополнительные параметры погрешностей.

 

Ящик с усами - для чего нужна эта диаграмма в Excel

 

В окне Формат предела погрешностей нужно установить параметры в следующем порядке: 

  1. Вертикальный предел погрешностей — Направление — Минус
  2. Величина погрешности — Пользовательская
  3. Нажать кнопку Укажите значение
  4. Поле Положительное значение ошибки оставить без изменений. Поле Отрицательное значение ошибки активировать и выделить значения из таблицы, соответствующие строке “Усы -” (только цифры).

Ящик с усами - для чего нужна эта диаграмма в Excel

 

Должны появиться вот такие черточки.

 

Ящик с усами - для чего нужна эта диаграмма в Excel

 

Теперь похожим образом нужно нарисовать верхние усы. Для этого выделим ряд 3Q Коробка, и снова перейдем на вкладку Конструктор — Макеты диаграмм — Добавить элементы диаграмм — Предел погрешностей — Дополнительные параметры погрешностей.

 

Ящик с усами - для чего нужна эта диаграмма в Excel

 

Здесь нужно указать направление вертикального предела погрешностей Плюс, величина погрешности Пользовательская, нажать кнопку Укажите значения. В поле Положительное значение установить курсор и выделить значения из строки “Усы +”. Поле Отрицательное значение ошибки оставить без изменений.

 

Должны появиться верхние усы.

 

Ящик с усами - для чего нужна эта диаграмма в Excel

 

Осталось немного доработать внешний вид диаграммы.

 

Уберем заливку с ряда Низ (синий в примере). Для этого выделим его, щелкнем правой кнопкой мыши — Формат ряда данных — и в блоке Заливка укажем Нет заливки.

 

Ящик с усами - для чего нужна эта диаграмма в Excel

 

Не выходя из окна Формат ряда данных, изменим цвет для ящиков.

 

Ящик с усами - для чего нужна эта диаграмма в Excel

 

 

Осталось добавить среднее значение (крестик).

Для этого выделим строку Среднее (только числа) и нажмем Ctrl + С.

 

Ящик с усами - для чего нужна эта диаграмма в Excel

 

Теперь выделим диаграмму и нажмем Ctrl + V. Должно получиться что-то похожее на картинку:

 

Ящик с усами - для чего нужна эта диаграмма в Excel

 

Правой кнопкой мыши щелкаем на новом ряде данных и выбираем Изменить тип диаграммы для ряда.

 

Ящик с усами - для чего нужна эта диаграмма в Excel

 

И для нового ряда выбираем тип диаграммы Точечная.

 

Ящик с усами - для чего нужна эта диаграмма в Excel

 

Обязательно снимите “галку” Вспомогательная ось”, если она установилась.

Осталось изменить точку на крестик (по желанию). Дважды щелкаем на любой точке, и в открывшемся окне Формат ряда данных выбираем: Маркер — Встроенный — крестик в выпадающем списке.

 

Ящик с усами - для чего нужна эта диаграмма в Excel

 

Диаграмма готова.

 

Ящик с усами - для чего нужна эта диаграмма в Excel

 

Конечно, у нее есть несколько недостатков по сравнению со встроенным инструментом:

  • из диаграммы намеренно убраны точки выбросов, поскольку они существенно исказили бы результат. Точки выбросов можно нарисовать отдельно аналогично тому, как мы создавали крестики для среднего значения. Или не использовать их совсем.
  • Нет средней линии между блоками одного ряда. При желании и сильно заморочившись, их можно нарисовать при помощи графиков. Возможно, в этой статье будет продолжение, как это сделать.
  • Ряды данных не разделены визуально. Где ряд Было и Стало, видно только из названия. 

 

Но в целом, если нет возможности установить более новую версию Excel, то это неплохой обходной путь создать диаграмму Ящик с усами в Excel.