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

Диаграмма Ящик с усами (англ. Box and Whisker Chart, Box Plot) обычно используется для отображения статистического анализа. К сожалению, Excel не может строить такие диаграммы, но вы можете создать свою диаграмму ящик с усами с помощью гистограммы и планок погрешностей. Данная статья посвящена тому, как построить вертикальный Box Plot в Excel 2013.

91-1-диаграмма ящик с усами excel

Простая диаграмма ящика с усами отображает диапазон данных, находящийся между первым и третьим квартилем, а медиана делит эту коробку на две части (межквартильный диапазон). Усы отображают данные первого квартиля — от второго квартиля до минимального значения, и четвертого квартиля – от третьего до максимального значения.

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

Чтобы лучше понять материал и работать с одними и теми же цифрами, скачайте книгу Excel с примером Диаграмма ящик с усами.xlsx.

Данные, используемые в примере имеют нормальное распределение со средним значением равным 10 и стандартным отклонением равным 5-ти. Данные имеют четыре столбца по 20 значений.

Все значения положительные, так как при смешанном (положительные и отрицательные значения) виде, данная методика требует некоторых модификаций.

91-2-Данные для boxplot

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

91-3-подготовка данных для boxplot

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

91-4-данные для boxblop

Теперь рассчитаем минимум, максимум, медиану, значение первого и третьего квартиля.

91-5-данные для boxblop

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

91-6-данные для boxblop

Построение диаграммы ящик с усами

Выделите заголовок таблицы с расчетами, затем удерживая клавишу Ctrl, выделите три строки содержащие данные Низ, 2Q Коробка и 3Q Коробка. Этот диапазон с несколькими площадями выделен оранжевым на рисунке ниже.

91-7-построение ящика с усами

Во вкладке Вставка перейдите в группу Диаграммы и выберите Вставить гистограмму –> Гистограмма с накоплением.

91-8-первоначальная диаграмма коробки с усами

Чтобы добавить усы, выделите ряд данных Низ. Перейдите во вкладку Работа с диаграммами -> Конструктор в группу Макеты диаграмм. Нажмите кнопку Добавить элемент диаграммы, в выпадающем меню выберите Предел погрешностей -> Дополнительные параметры предела погрешностей. В появишейся справа панели Формат предела погрешностей в поле Направление установите маркер Минус, а в поле Величина погрешности установите маркер Пользовательская и нажмите кнопку Укажите значение. В появившемся диалоговом окне Настраиваемый предел погрешности поле Положительные значения ошибки оставьте без изменений, а для Отрицательные значения ошибки укажите диапазон B14:E14, который называется Усы-.

91-9-Настраиваемый предел погрешностей

Нажимаем OK и получаем диаграмму, имеющую следующий вид.

91-10-ящик с нижними усами

Теперь необходимо добавить верхние усы. Для этого выделяем ряд данных 3Q Коробка и повторяем действия описанные выше, только теперь в поле Направление панели Формат предела погрешностей устанавливаем маркер Плюс. А в диалоговом окне Натраиваемый предел погрешностей поле Отрицательное значение ошибки оставляем неизменным, а в поле Положительное значение ошибки указываем диапазон B15:E15, который называется Усы+. Жмем ОК и получам следующую диаграмму ящика с усами.

91-11-ящик с верхними усами

Осталось навести антураж и отформатировать нашу таблицу. Выделяем ряд данных Низ и убираем заливку и границы ряда данных. Для ряда данных 2Q Коробка и 3Q Коробка задаем светло серую заливку и темный контур. Удаляем легенду и название диаграммы.

91-13-форматирование диаграммы

Добавление среднего значения

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

91-12-данные для среднего

Скопируйте выделенные данные в буфер обмена с помощью сочетания клавиш Ctrl+C. Затем выделите диаграмму и вставьте скопированные данные с помощью клавиш Ctrl+V. У вас должна получиться следующая картинка.

91-13-построение среднего

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

91-14-финальная диаграмма ящик с усами Excel

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


15 комментариев

  1. Артём, при выделенном ряде данных диаграмы на вкладке «Макет» есть кнопка с меню «Планки погрешностей», а там уже ищите «Дополнительные параметры…»

  2. Ренат, большое спасибо за материал, оказался очень полезным.

    У меня не сошлись с Вами формулы усов, мой вариант таков:
    Усы- = B7-B6 (квартиль 1 — Минимум)
    Усы+ = B10-B9 (Максимум — квартиль 3)

    В Вашем варианте (на картинке):
    Усы- = B10-B9 (Максимум — квартиль 3)
    Усы+ = B9-B8 (квартиль 3 — Медиана)
    Ваши цифры из таблицы совпадают с моими формулами, необходимо подкорректировать картинку с цветными стрелочками и формулами усов.

  3. Спасибо большое! Статья полезна и информативна! Ксения молодец, что написала правильную формулу — я время сэкономила на поисках ошибки.

  4. Здравствуйте. Спасибо за подробную статью, но у меня почему-то так и не получается добавить усы. Делаю все, как написано, но после введения диапазона настраиваемого предела погрешностей и нажатии ОК, выдает ошибку в формуле. В чем может быть дело? Пишу =$B$10:$D$10 у меня такой диапазон….

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

  6. Благодарю! Всё получилось, всё совпало, всё значения посчитаны правильно.
    Возникла одна проблема при построении верхних усов: на графике усы превосходят табличные значения (например, в таблице для 1 и 2 ряда значение верхних усов равны 0,8 и 0,4, то на графике усы для 1 и 2 ряда по 1,0 ). Что не так?

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

  8. Доброй ночи. Я прочитал вашу статью»Создание диаграммы ящик с усами в Excel», собрал все необходимые данные, но застреваю на этапе введения данных в настраиваемый предел погрешностей. Что для верхних, что для нижних усов ввести данные ячеек не могу. Пишет что не хватает формулы, хотя я ввожу именно так, как у вас в статье. С уважением, Александр.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *