How to Make a Box and Whisker Plot in Excel
Display a five-number summary of data
Updated on September 30, 2020
In Microsoft Excel, a box plot uses graphics to display groups of numerical data through five values, called quartiles. Box plot charts can be dressed up with whiskers, which are vertical lines extending from the chart boxes. The whiskers indicate variability outside the upper and lower quartiles.
Box and whisker plots are typically used to depict information from related data sets than have independent sources, such as test scores between different schools or data from before and after changes in a process or procedure.
In recent versions of Excel, you can create a box and whisker chart using the Insert Chart tool. Although older versions of Excel don’t have a box and whisker plot maker, you can create one by converting a stacked column chart into a box plot and then adding the whiskers.
These instructions apply to Excel 2019, Excel 2016, Excel for Microsoft 365, Excel 2013, and Excel 2010.
Use Excel’s Box and Whisker Plot Maker
For Excel 2019, Excel 2016, or Excel for Microsoft 365, make a box and whisker plot chart using the Insert Chart tool.
-
Enter the data you want to use to create a box and whisker chart into columns and rows on the worksheet. This can be a single data series or multiple data series.
-
Select the data you want to use to make the chart.
-
Select the Insert tab.
-
Select Recommended Charts in the Charts group (or select the dialog box launcher in the lower-right corner of the charts group) to open the Insert Chart dialog box.
-
Select the All Charts tab in the Insert Chart dialog box.
-
Select Box and Whisker and choose OK. A basic box and whisker plot chart appears on the worksheet.
Transform a Box Plot Chart into a Box and Whisker Plot
For Excel 2013 or Excel 2010, start with a stacked column chart and transform it into a box and whisker plot chart.
Create a basic box plot chart in Excel and then add the whiskers.
Add the Top Whisker
The whiskers on a box and whisker box plot chart indicate variability outside the upper and lower quartiles. Any data point that falls outside the top or bottom whisker line would be considered an outlier when analyzing the data.
-
Select the top box on the chart and then select Add Chart Element on the Chart Design tab.
-
Select Error Bars and choose More Error Bar Options to open the Format Error Bars menu.
-
Select Plus under Direction in Error Bars Options.
-
Select Custom and choose Specify Value in the Error Amount Section to open the Custom Error Bars dialog box .
Add the Bottom Whisker
Once you’ve added the top whiskers, then you can add the bottom whiskers in a similar fashion.
-
Select the bottom box on the chart and select Add Chart Element on the Chart Design tab.
-
Select Error Bars and choose More Error Bar Options to open the Format Error Bars menu.
-
Select Minus under Direction in Error Bars Options.
-
Select Custom and choose Specify Value in the Error Amount Section. The Custom Error Bars dialog box will open.
-
Delete the contents of the Positive Error Value box. Select the bottom values on the worksheet and select OK to close the Custom Error Bars window.
Format a Box and Whisker Plot Chart in Excel
Once you have created the chart, use Excel’s chart formatting tools.
-
Select Chart Title and enter the title you want to appear for the chart.
-
Right-click one of the boxes on the chart and choose Format Data Series to open the Format Data Series pane.
-
Increase or decrease the Gap Width to control the spacing of the gap between the boxes.
-
Select Show Inner Points to display the data points between the two whisker lines.
-
Also in the Format Data Series pane, select Show Outlier Points to display outliers below or above the whisker lines.
-
Select Show Mean Markers to display the mean marker of the data series.
-
Select Show Mean Line to display the line connecting the means of the boxes in the data series.
-
Select a method for Quartile Calculation:
- Inclusive Median is included in the calculation if the number of values in the data is odd.
- Exclusive median is excluded from the calculation if there are an odd number of values in the data.
-
Select the next box in your plot chart to customize it in the Format Data Series pane and repeat for any remaining boxes.
Edit or Change the Appearance of the Box and Whisper Plot
To make changes to the appearance of the box and whisker plot chart, select any area of the chart and then choose Chart Design or Design Tools on the Chart Tools tab, depending on which version of Excel you are using.
Modify factors such as the chart layout, style, or colors using the same methods described above.
Thanks for letting us know!
Get the Latest Tech News Delivered Every Day
Subscribe
Я не думаю, что вы можете сделать это в Excel, но если вы готовы использовать надстройку, это возможно. Funfun надстройка позволяет создавать такого рода диаграммы с помощью Javascript в Excel.
вот рабочий код, который я написал для вас:
https://www.funfun.io/1/#/edit/5a69ddc9ee0b8c3283c653e0
я использовал формат, который вы хотели, я только изменил данные, чтобы создать два разных прямоугольника, имеющие только две координирует.
как вы можете видеть по этой ссылке, я использовал онлайн-редактор из Funfun, где я ввел вашу таблицу во встроенной электронной таблице.
я использую файл JSON, чтобы получить данные из электронной таблицы в свой код javascript как таковой:
{
"data": "=A1:E3"
}
затем я сохраняю данные в локальных переменных и создаю прямоугольники с правильными координатами, например:
/* we store the data from the spreadsheet in local variables */
var label = [];
var x = [];
var y = [];
for (var i = 1; i < $internal.data.length; i++)
{
label.push($internal.data[i][0]);
x.push([parseInt($internal.data[i][1]), parseInt($internal.data[i][3])]);
y.push([parseInt($internal.data[i][2]), parseInt($internal.data[i][4])]);
}
/* create all the shapes you want with the data stored */
var shapes = [];
var traces = [];
for (var i = 0; i < $internal.data.length - 1; i++) {
shapes.push({
type: 'rectangle',
xref: 'x',
yref: 'y',
fillcolor: 'rgba(50, 171, 96, 0.7)',
x0: x[i][0],
y0: y[i][0],
x1: x[i][1],
y1: y[i][1],
line: {
color: 'rgba(50, 171, 96, 1)'
}
});
traces.push({
x: [x[i][0] + 3.5],
y: [y[i][0] - 2],
text: label[i],
mode: 'text'
});
}
the shapes массив прямоугольников и trace пометить каждый прямоугольник.
вы можете настроить диаграмму, как вам нравится есть много вариантов, доступных, это красота javascript и его мощных библиотек. Для этого примера я использовал plotly.js
вы можете загрузить его в Excel, вставив URL-адрес в Funfun Excel add-in. Вот как это выглядит на моем примере:
Edit
если вы хотите напишите текст внутри фигур вам просто нужны другие локальные переменные со значениями, хранящимися в нем, и найдите правильные координаты как таковые:
var textInRectangles = ["First", "second"];
...
traces.push({
x: [x[i][0] + 2.5],
y: [y[i][0] + 4],
text: textInRectangles[i],
textfont: {
color: 'black',
size: 9,
family: 'Arial'
},
mode: 'text'
});
Я изменил ссылку на пример впереди.
раскрытие информации: я разработчик Funfun.
В визуализации информации и вычислениях, тримаппинг является методом отображения иерархических (древовидных) данных с помощью вложенных прямоугольников. Каждая ветвь дерева представляет из себя прямоугольник, который, в свою очередь, обложен прямоугольниками поменьше. Каждый прямоугольник имеет площадь пропорциональную данным, из которых строится Treemap.
Когда размеры прямоугольников коррелируют в некотором роде с древовидной структурой, часто можно увидеть модели, которые трудно обнаружить другими способами. Еще одним преимуществом является то, что treemap обеспечивает эффективное использование пространства.
Чтобы построить тримап в Excel, необходимо разобраться, как прямоугольники будут располагаться. Основной идеей является то, что прямоугольники располагаются по порядку от большего к меньшему, причем следующий по величине прямоугольник пристыковывается к самому близкому по происхождению.
Данная визуализация подходит для отображения нескольких данных от 2 до максимум 12. Если максимальное количество клеток известно и квадраты отсортированы от большего к меньшему можно упростить проблему позиционирования путем фиксации позиции квадратов. Вот так, к примеру, будет выглядеть treemap, состоящий из 12 прямоугольников.
Как сделать Treemap в Excel с помощью диаграмм
На основании значения данных необходимо вычислить координаты квадратов. Это осуществляется с помощью длины краев, где длина края определяется квадратным корнем.
Чтобы нарисовать прямоугольник по координатам я использовал точечный график с планками погрешностей. Основная идея отображена на картинке:
Таким образом, нам необходимо знать только координаты правой нижней и левой верхней точки. Правая нижняя координата определяется фиксированным положением и вычисляется с помощью добавления краев соответствующих квадратов. Я назвал эти координаты (x, y) – на графике они отмечены красными кружками.
Координаты левой верхней точки (x1, y1) вычисляются с помощью формул
x1 = x – край
y1 = y – край
На графике помечены зелеными кружками.
Чтобы нарисовать стороны прямоугольников я воспользовался планками погрешностей (мы уже рассматривали методику использования планок погрешностей в одной из предыдущих статей). В нашем случае было важно не ошибиться со знаком и направлением планки:
— Начиная с правого нижнего угла, нам нужны были вертикальные-положительные и горизонтальные-отрицательные планки.
— Начиная с левого верхнего угла, нам нужны были вертикальные-отрицательные и горизонтальные-положительные планки.
Чтобы добавит читабельности в наш график, необходимо создать еще третий ряд данных, который будет отображать подписи данных в середине прямоугольников. Значения координат подписей определяются по формуле:
x2 = x — край / 2
y2 = y + край / 2
Осталось немного отформатировать диаграмму и наш тримап в Excel готов.
Скачать файл Treemap в Excel
Время на прочтение
12 мин
Количество просмотров 14K
Не жди поездку в Гагры!
Давид Маркович, сериал «Ликвидация»
Всем привет!
За время карантина все, наверное, не по разу прочитали о том, как плодотворно А.С. Пушкин провел свой период «заточения», названный впоследствии «Болдинской осенью», поэтому не буду объяснять, что именно меня сподвигло написать следующую статью об инфографике средствами MS Office.
К тому же, мне есть что вам еще рассказать, ведь я более 10 лет преподаю и руковожу группой разработки учебных материалов в учебном центре «Сетевая Академия ЛАНИТ», а также являюсь автором курса по инфографике.
В первой статье я привела несколько примеров использования иконок в диаграммах вместо легенды и для визуализации итогового значения. Предлагаю продолжить тему под просмотр фильмов. За последние пару недель я пересмотрела больше кино, чем за целый год, и это натолкнуло меня на мысль объединить рассматриваемые примеры в группы и подобрать для каждой группы название фильма, с каким они у меня ассоциируются.
Я — легенда
Как вы, возможно, уже догадались, речь пойдет о легенде… для диаграмм.
Вариантов замены легенды значками несколько. Один мы рассматривали в предыдущей статье, в этой — будем рассматривать еще два. Первый – замена маркеров графика значками, а второй – заполнение рядов значками.
Начнем с первого. Он наиболее простой.
С первым вариантом мы закончили. Предлагаю сразу приступить ко второму.
Итак, теперь в вашем архиве три разных варианта использования в диаграммах значков вместо легенды.
Для тех, кто хочет еще, предлагаю попробовать самостоятельно построить вот такой вариант диаграммы.
Данные можете придумать и свои, а символ с изображением стопки книжек из шрифта Webdings вам в помощь!
Властелин колец
Моя прелесть…
Уж про круговые и кольцевые диаграммы в Excel только ленивый не знает. Они просты и наглядны. Их действительно любят использовать для визуализации данных.
Мы тоже один вариант построения кольцевой диаграммы уже рассматривали в прошлой статье, в этой предлагаю разобрать еще два примера.
Первый — для визуализации одного итогового значения, а второй — для нескольких.
По традиции начнем с первого.
Попробуйте изменить итоговое значение, и вы увидите, как красиво перерисовывается ваше колечко.
Этот пример мы рассматриваем в рамках курса Инфографика средствами Excel и PowerPoint, но немного усложняем его, добавляя условие, при котором ниже первого определенного значения цвет кольца будет красный, а выше второго, оговоренного значения — зеленый. И только если значение попадает в интервал, кольцо будет оранжевое.
А теперь к следующему примеру.
Ну что, повторю схему и предложу вам сделать самим еще один вариант кольцевой диаграммы.
В этом примере значения не в процентах, но максимальное значение равно 1000, поэтому относительно него и следует вычислять дополнительные значения. Удачи!
Миссия невыполнима
Выполнима или невыполнима, не нам решать, но наблюдать за процессом очень интересно…
Очень часто диаграммы используют для отображения результата текущего процесса, то есть для того, чтобы понимать, сколько еще до финиша.
Вот вам пара примеров. Такие диаграммы еще называют диаграммами прогресса, а первый вариант очень часто называют термометром.
Начнем с варианта построения диаграммы в виде термометра. Нам нужны будут дополнительные данные, разделяющие область построения на три раздела.
А второй пример диаграммы будем строить с небольшой хитростью. Тут нам понадобится функционал PowerPoint.
Вы уже ждете пример для самостоятельной реализации? Вот он.
Иллюзия обмана
Чем больше вам кажется, что вы видите, тем легче вас обмануть…
Ни в коем случае я никого сейчас обманывать не собираюсь, но элемент фокуса или иллюзии все-таки будет.
Есть в Excel команда, которая называется Камера, ее нужно принудительно добавлять на ленту. Это настолько нераспространенная команда, что наткнуться на ее использование мне пришлось лишь один раз в 2013 версии Excel в каких-то обзорных материалах. Это не была любовь с первого взгляда, я не сразу разглядела ее потенциал. Дружить с ней я начала только спустя года три, когда в интернете наткнулась на демонстрацию создания панели индикаторов какого-то процесса с использованием именно этой команды.
Задача ее в том, чтобы делать снимок с диапазона на листе Excel и помещать его на другой лист этой книги. Причем вставляется диапазон как рисунок, но рисунок, который будет обновляться, если в исходном диапазоне или диапазоне данных были выполнены какие-либо изменения.
Образно говоря, получается некое окно с одного листа на другой.
Эта команда позволит нам создавать целые информационные панели, не обращаясь к специализированным надстройкам.
Вот пример простого размещения снимков диаграмм на одном листе.
На первый взгляд, это просто размещенные диаграммы на отдельном листе, но вот в этом и есть иллюзия. Это снимки диапазонов ячеек, на которых размещены диаграммы, помещенные на отдельный лист. Так как эти снимки уже не диапазоны и не диаграммы, а изображения, то и параметры форматирования у них как у рисунков. Смотрите, как можно преобразовать эти снимки, используя вкладку Работа с рисунками/Формат.
Представляете, мы можем для этих объектов применять обрезку, эффекты и стили рисунков, но при этом связь с данными сохраняется. Вот тут вам все карты в руки, но не переусердствуйте.
Если вы уже готовы поближе познакомиться с данной командой, то давайте начнем с того, где ее найти и как ее добавить на ленту.
Теперь на вкладке Главная есть кнопка
Самое время разобраться, как она работает.
Мы рассмотрим пример с одной диаграммой. Сделаем снимок этой диаграммы, поместим его на новый чистый лист и выполним ряд настроек форматирования. В итоге получим вот такое изображение.
Приступим.
И чтобы окончательно убедить вас в полезности и волшебности данной команды, предлагаю изменить исходные данные на листе с диаграммой, а затем перейти на лист со снимком и проверить результат.
На листе с диаграммой введем в ячейку B1 значение 75% и сразу увидим, что диаграмма перерисовалась. Ну тут ничего особенного, к этому мы уже привыкли.
Теперь перейдем на лист со снимком…
Обновление изображения происходит в момент перехода на лист, и вы можете увидеть процесс перерисовки.
Надеюсь, вы не запрячете эти знания и умения в дальний ящик, а будете применять их. Лично я считаю, что область применения тут огромна.
Расскажите в комментариях, какой из примеров был наиболее интересен вам. Весь ли функционал был знаком и используется вами? Поделитесь, какие примеры диаграмм или визуализации данных вы бы хотели, чтобы были рассмотрены в дальнейших статьях.
А тех, кто хочет узнать о визуализации данных в Excel и PowerPoint больше, ждем на нашем онлайн-курсе по инфографике!
До встречи в сети или на наших курсах!
В Excel прямоугольная и усовая диаграмма, также называемая коробчатой диаграммой, используется для отображения статистического анализа, который помогает показать, как числа распределяются в наборе данных. Например, с помощью прямоугольной диаграммы и диаграммы с усами вы можете отобразить статистические данные о результатах тестов по различным предметам, чтобы определить, какой предмет требует большего внимания от учеников.
В этой статье я расскажу о том, как создать прямоугольную диаграмму и диаграмму усов в каждой версии Excel.
- Создание прямоугольной и усовой диаграммы в Excel 2016 и более поздних версиях
- Создание прямоугольной и усовой диаграммы в Excel 2013 и более ранних версиях
- Скачать образец файла Box and Whisker Chart
- Видео: создание прямоугольной и усовой диаграммы в Excel
В прямоугольной диаграмме и диаграмме усов он показывает сводку данных из пяти цифр — минимальное значение, первый квартиль (Q1), медиана, третий квартиль (Q3) и максимальное значение.
Нижняя строка соединяется с прямоугольником, представляющим минимальное значение, верхняя линия соединяется с прямоугольником — это максимальное значение в данных, поле указывает диапазон между первым и третьим квартилем, линия медианы делит прямоугольник на межквартильный диапазон.
Объяснение каждых пяти чисел:
- Минимальное значение: Минимальное или наименьшее значение в наборе данных;
- Первый квартиль: Среднее значение между минимумом и медианой — 25-й процентиль;
- Медиана: Среднее значение в наборе данных;
- Третий квартиль: Среднее значение медианы, а максимальное — 75-й процентиль;
- Максимальное значение: Максимальное или наибольшее значение в наборе данных.
Создание прямоугольной и усовой диаграммы в Excel 2016 и более поздних версиях
В Excel 2016, 2019 и Office 365 для пользователей Excel поддержана встроенная диаграмма Box and Whisker. Чтобы быстро и легко создать коробку и диаграмму усов, выполните следующие действия:
1. Выберите данные, на основе которых вы хотите создать прямоугольную и усовую диаграмму.
2. Затем нажмите Вставить > Вставить статистическую диаграмму > Коробка и ус, см. снимок экрана:
3. И тогда сразу будут вставлены прямоугольник и диаграмма усов, см. Снимок экрана:
4. Затем выберите и удалите горизонтальную ось, и вы также можете отредактировать заголовок диаграммы и вставить легенду, как вам нужно, см. Снимок экрана:
Создание прямоугольной и усовой диаграммы в Excel 2013 и более ранних версиях
В Excel 2013 и более ранних версиях отсутствует тип диаграммы «Коробка и усы», в этом случае вам следует создать диаграмму «Коробка и усы» шаг за шагом.
Шаг 1. Подготовьте диапазон данных для создания прямоугольной и усовой диаграммы на основе
Предположим, у вас есть следующий диапазон данных:
1. Создайте вторую таблицу для расчета минимального значения, значения первого квартиля, медианного значения, значения третьего квартиля и максимального значения, используя следующие формулы:
Minimum Value: =MIN(B2:B16)
Первый квартиль: = КВАРТИЛЬ.INC (B2: B16; 1)
Среднее значение: = КВАРТИЛЬ.INC (B2: B16; 2)
Третий квартиль: = КВАРТИЛЬ.INC (B2: B16; 3)
Максимальное значение: = МАКС (B2: B16)
2. После вставки формул, и вы получите следующий результат:
3. Затем создайте третью таблицу на основе второй таблицы, чтобы рассчитать различия, примените следующие формулы:
Minimum Value: =MIN(B2:B16)
Минимум первого квартиля: = G4-G3
Медиана первого квартиля: = G5-G4
Третий квартиль-медиана: = G6-G5
Максимум-третий квартиль: = G7-G6
4. После применения формул вы получите результат, как показано на скриншоте ниже, этот диапазон данных будет использоваться для создания прямоугольной диаграммы и диаграммы усов.
Шаг 2. Создайте столбчатую диаграмму с накоплением
5. После получения диапазона данных для создания прямоугольной и усовой диаграммы выберите данные третьей таблицы, а затем нажмите Вставить > Вставить столбец или гистограмму > Столбец с накоплением, см. снимок экрана:
6. И столбчатая диаграмма с накоплением была вставлена в рабочий лист, как показано ниже:
7. Щелкните, чтобы выбрать диаграмму, а затем щелкните Переключить строку / столбец вариант под Дизайн вкладка, и диаграмма будет преобразована в диаграмму ниже, см. скриншоты:
Шаг 3. Отформатируйте столбчатую диаграмму с накоплением в прямоугольную и усовую диаграмму.
8. Теперь вы должны скрыть нижний ряд данных, щелкнуть правой кнопкой мыши любой из нижних рядов данных и выбрать Заполнять > Без заливки из правого меню, см. снимок экрана:
9. Затем нижние ряды данных теперь невидимы, и вы также можете удалить плитку диаграммы и легенду, см. Снимок экрана:
10. Затем вы должны создать усы, заменив самый верхний и второй снизу сегменты, щелкните правой кнопкой мыши верхнюю полосу диаграммы и затем выберите Заполнять > Без заливки, см. снимок экрана:
11. Теперь продолжайте нажимать Дизайн на ленте, а затем щелкните Добавить элемент диаграммы > Полосы ошибок > Стандартное отклонение, и вы получите следующий результат:
12. Затем щелкните правой кнопкой мыши панель ошибок и выберите Полосы ошибок формата из контекстного меню см. снимок экрана:
13. В Полосы ошибок формата панель, под Параметры панели ошибок Вкладка, выполните следующие действия:
- Выберите Минус из файла Руководство раздел;
- Выберите Нет крышки из Конец Стиль раздел;
- Из Сумма ошибки разделом, щелкните Процент вариант и введите 100 в текстовое поле.
14. Затем вы также можете отформатировать строки, продолжайте нажимать Заливка и линия tab выполните следующие операции:
- Выберите SOLID линия под линия раздел;
- Укажите нужный вам цвет из Цвет раскрывающийся список;
- Затем введите 1.5pt в Ширина текстовое окно.
15. Затем повторите шаги 10–14, указанные выше для второго нижнего ряда данных. См. Демонстрацию ниже:
16. И вы получите коробку и диаграмму усов, как показано на скриншоте ниже:
17. Ящик и диаграмма усов обычно рисуются одним цветом заливки, поэтому вам следует изменить цвет заливки и цвет границы для ящиков, сделайте следующее:
- Щелкните правой кнопкой мыши, чтобы выбрать верхние поля, и выберите Заполнять, затем выберите один понравившийся цвет заливки, например светло-коричневый.
- Затем выберите один темно-синий из очертание раскрывающийся список, продолжайте выбирать 1½ пт из Вес .
18. Повторите шаг 17, описанный выше, для форматирования нижних полей. И теперь диаграмма ящиков и усов успешно создана, см. Снимок экрана:
Скачать образец файла Box and Whisker Chart
Видео: создание диаграммы в виде прямоугольников и усов в Excel
Лучшие инструменты для работы в офисе
Kutools for Excel — Помогает вам выделиться из толпы
Хотите быстро и качественно выполнять свою повседневную работу? Kutools for Excel предлагает 300 мощных расширенных функций (объединение книг, суммирование по цвету, разделение содержимого ячеек, преобразование даты и т. д.) и экономит для вас 80 % времени.
- Разработан для 1500 рабочих сценариев, помогает решить 80% проблем с Excel.
- Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
- Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
- 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Вкладка Office — включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
