Диаграмма Ящик с усами (англ. Box and Whisker Chart, Box Plot) обычно используется для отображения статистического анализа. К сожалению, Excel не может строить такие диаграммы, но вы можете создать свою диаграмму ящик с усами с помощью гистограммы и планок погрешностей. Данная статья посвящена тому, как построить вертикальный Box Plot в Excel 2013.
Простая диаграмма ящика с усами отображает диапазон данных, находящийся между первым и третьим квартилем, а медиана делит эту коробку на две части (межквартильный диапазон). Усы отображают данные первого квартиля — от второго квартиля до минимального значения, и четвертого квартиля – от третьего до максимального значения.
Подготовка данных
Чтобы лучше понять материал и работать с одними и теми же цифрами, скачайте книгу Excel с примером Диаграмма ящик с усами.xlsx.
Данные, используемые в примере имеют нормальное распределение со средним значением равным 10 и стандартным отклонением равным 5-ти. Данные имеют четыре столбца по 20 значений.
Все значения положительные, так как при смешанном (положительные и отрицательные значения) виде, данная методика требует некоторых модификаций.
Прежде чем начать строить диаграмму, нам необходимо произвести некоторые расчеты и подготовить данные. Для этого вставьте пустые строки над массивом и заполните заголовки показателями, которые потребуются для построения диаграммы, как показано на рисунке.
Для начала рассчитаем некоторые простые статистические меры, такие как количество значений, среднее и стандартное отклонение. Формулы, используемые для расчетов отображены на рисунке ниже.
Теперь рассчитаем минимум, максимум, медиану, значение первого и третьего квартиля.
Наконец, давайте определим значения, которые станут основой построения. Наша диаграмма имеет коробку второго квартиля, которая отображает разницу между медианой и первым квартилем, значения которых мы рассчитали ранее. Также имеется коробка третьего квартиля – рассчитывается как разница между значением третьего квартиля и медианой. Нижняя часть коробки опирается на первый квартиль. Длина нижних усов равняется значению первого квартиля минус минимальное значение, длина верхних усов – максимум минус значение третьего квартиля.
Построение диаграммы ящик с усами
Выделите заголовок таблицы с расчетами, затем удерживая клавишу Ctrl, выделите три строки содержащие данные Низ, 2Q Коробка и 3Q Коробка. Этот диапазон с несколькими площадями выделен оранжевым на рисунке ниже.
Во вкладке Вставка перейдите в группу Диаграммы и выберите Вставить гистограмму –> Гистограмма с накоплением.
Чтобы добавить усы, выделите ряд данных Низ. Перейдите во вкладку Работа с диаграммами -> Конструктор в группу Макеты диаграмм. Нажмите кнопку Добавить элемент диаграммы, в выпадающем меню выберите Предел погрешностей -> Дополнительные параметры предела погрешностей. В появишейся справа панели Формат предела погрешностей в поле Направление установите маркер Минус, а в поле Величина погрешности установите маркер Пользовательская и нажмите кнопку Укажите значение. В появившемся диалоговом окне Настраиваемый предел погрешности поле Положительные значения ошибки оставьте без изменений, а для Отрицательные значения ошибки укажите диапазон B14:E14, который называется Усы-.
Нажимаем OK и получаем диаграмму, имеющую следующий вид.
Теперь необходимо добавить верхние усы. Для этого выделяем ряд данных 3Q Коробка и повторяем действия описанные выше, только теперь в поле Направление панели Формат предела погрешностей устанавливаем маркер Плюс. А в диалоговом окне Натраиваемый предел погрешностей поле Отрицательное значение ошибки оставляем неизменным, а в поле Положительное значение ошибки указываем диапазон B15:E15, который называется Усы+. Жмем ОК и получам следующую диаграмму ящика с усами.
Осталось навести антураж и отформатировать нашу таблицу. Выделяем ряд данных Низ и убираем заливку и границы ряда данных. Для ряда данных 2Q Коробка и 3Q Коробка задаем светло серую заливку и темный контур. Удаляем легенду и название диаграммы.
Добавление среднего значения
Чтобы добавить данные со средним значение к каждому ящику, выделите ряд под названием Среднее. На картинке выделено голубым.
Скопируйте выделенные данные в буфер обмена с помощью сочетания клавиш Ctrl+C. Затем выделите диаграмму и вставьте скопированные данные с помощью клавиш Ctrl+V. У вас должна получиться следующая картинка.
Щелкните по новому ряду данных правой кнопкой мыши и выберите Изменить тип диаграммы для ряда. В появившемся диалоговом окне Изменение типа диаграммы найдите рад данных Среднее, поменяйте тип диаграммы на Точечная и снимите маркер Вспомогательная ось, если он был установлен.
Наша финальная диаграмма ящик с усами готова. На ней можно увидеть распределение данных от первого до третьего квартиля, медиану и среднее значение.
по-моему здесь неправильно рассчитывается усы+
Спасибо, Ренат, большое! Классная и полезная вещь.
как в 2007 добавить элемент диаграммы? там нет кнопки такой
Артём, при выделенном ряде данных диаграмы на вкладке «Макет» есть кнопка с меню «Планки погрешностей», а там уже ищите «Дополнительные параметры…»
Спасибо, все стало понятно и доступно!
В Excel 2016 ящик с усами добавлен в типовые диаграммы. Можно здесь почитать и посмотреть https://statanaliz.info/excel/diagrammy/110-diagramma-yashchik-s-usami-v-excel-2016
Жаль, что нельзя побольше файл загрузить. Я бы вам показал крутую диаграмму с ящиками и усами.
Ренат, большое спасибо за материал, оказался очень полезным.
У меня не сошлись с Вами формулы усов, мой вариант таков:
Усы- = B7-B6 (квартиль 1 — Минимум)
Усы+ = B10-B9 (Максимум — квартиль 3)
В Вашем варианте (на картинке):
Усы- = B10-B9 (Максимум — квартиль 3)
Усы+ = B9-B8 (квартиль 3 — Медиана)
Ваши цифры из таблицы совпадают с моими формулами, необходимо подкорректировать картинку с цветными стрелочками и формулами усов.
Ксения, спасибо!
Спасибо большое! Статья полезна и информативна! Ксения молодец, что написала правильную формулу — я время сэкономила на поисках ошибки.
Здравствуйте. Спасибо за подробную статью, но у меня почему-то так и не получается добавить усы. Делаю все, как написано, но после введения диапазона настраиваемого предела погрешностей и нажатии ОК, выдает ошибку в формуле. В чем может быть дело? Пишу =$B$10:$D$10 у меня такой диапазон….
Всё бы хорошо, но только этот рецепт работает только с положительными числами. Если медиана или первый квартиль распределения получаются отрицательными, то «коробочка» обрезается снизу нулём. Просто потому, что гистограмма с накоплением в Экселе строится от нуля.
Благодарю! Всё получилось, всё совпало, всё значения посчитаны правильно.
Возникла одна проблема при построении верхних усов: на графике усы превосходят табличные значения (например, в таблице для 1 и 2 ряда значение верхних усов равны 0,8 и 0,4, то на графике усы для 1 и 2 ряда по 1,0 ). Что не так?
Спасибо, интересное решение. Только в примере получается что усы являются лишь границами минимума и максимума, и соответственно на такой диаграмме невозможно отобразить выбросы, в стандартной интерпритации усами являются доверительные интервалы.
Доброй ночи. Я прочитал вашу статью»Создание диаграммы ящик с усами в Excel», собрал все необходимые данные, но застреваю на этапе введения данных в настраиваемый предел погрешностей. Что для верхних, что для нижних усов ввести данные ячеек не могу. Пишет что не хватает формулы, хотя я ввожу именно так, как у вас в статье. С уважением, Александр.