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

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

В этой статье я расскажу, как создать диаграмму с ящиком и усами в каждой версии Excel.

• Создание диаграммы с ящиком и усами в Excel 2016 и более поздних версиях
• Создание диаграммы с ящиком и усами в Excel 2013 и более ранних версиях
• Скачать пример файла с диаграммой с ящиком и усами
• Видео: Создание диаграммы с ящиком и усами в Excel

На диаграмме с ящиком и усами отображается пятизначная сводка данных – минимальное значение, первый квартиль (Q1), медиана, третий квартиль (Q3) и максимальное значение.

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

Объяснение каждого из пяти чисел:

Создание диаграммы с ящиком и усами в Excel 2016 и более поздних версиях

В Excel 2016, 2019 и Office 365 встроенная диаграмма с ящиком и усами поддерживается для пользователей Excel. Чтобы быстро и легко создать диаграмму с ящиком и усами, выполните следующие шаги:

1. Выберите данные, на основе которых вы хотите создать диаграмму с ящиком и усами.

2. Затем нажмите Вставка > Вставить статистическую диаграмму > Ящик и усы, см. скриншот:

3. И сразу же будет вставлена диаграмма с ящиком и усами, см. скриншот:

4. Затем выберите и удалите горизонтальную ось, вы также можете изменить заголовок диаграммы и добавить легенду по необходимости, см. скриншот:

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

В Excel 2013 и более ранних версиях нет типа диаграммы «Ящик и усы», в этом случае вам нужно создавать диаграмму с ящиком и усами пошагово.

Шаг 1: Подготовьте диапазон данных для создания диаграммы с ящиком и усами на основе

Предположим, у вас есть следующий диапазон данных:

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

2. После ввода формул вы получите следующий результат:

3. Затем создайте третью таблицу на основе второй таблицы для расчета различий, примените следующие формулы:

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

Шаг 2: Создание столбчатой диаграммы с накоплением

5. После получения диапазона данных для создания диаграммы с ящиком и усами на основе, выберите данные третьей таблицы, затем нажмите Вставка > Вставить столбчатую или гистограммную диаграмму > Столбчатая диаграмма с накоплением, см. скриншот:

6. И столбчатая диаграмма с накоплением будет вставлена в рабочий лист, как показано на скриншоте ниже:

7. Нажмите, чтобы выбрать диаграмму, затем нажмите Поменять строки/столбцы в разделе Конструктор, и диаграмма будет преобразована в следующую диаграмму, см. скриншоты:

Шаг 3: Форматирование столбчатой диаграммы с накоплением в диаграмму с ящиком и усами

8. Теперь вы должны скрыть нижнюю серию данных, щелкните правой кнопкой мыши любую из нижних серий данных и выберите Заливка > Без заливки из правого меню, см. скриншот:

9. И теперь нижние серии данных становятся невидимыми, вы также можете удалить заголовок диаграммы и легенду, см. скриншот:

10. Далее вы должны создать усы, заменив верхний и второй снизу сегменты, щелкните правой кнопкой мыши верхнюю полосу диаграммы, затем выберите Заливка > Без заливки, см. скриншот:

11. Теперь продолжайте нажимать вкладку Конструктор на ленте, затем нажмите Добавить элемент диаграммы > Линии ошибок > Стандартное отклонение, и вы получите следующий результат:

12. Затем щелкните правой кнопкой мыши линию ошибок и выберите Формат линий ошибок из контекстного меню, см. скриншот:

13. В панели Формат линий ошибок, в разделе Опции линий ошибок, выполните следующие действия:

• Выберите опцию Минус в разделе Направление;
• Выберите Без колпачка в разделе Стиль конца;
• В разделе Величина ошибки нажмите опцию Процент и введите 100 в текстовое поле.

14. Затем вы также можете отформатировать линии, продолжайте нажимать вкладку Заливка и линия, выполните следующие действия:

• Выберите Сплошная линия в разделе Линия;
• Укажите нужный цвет из выпадающего списка Цвет;
• Затем введите 1.5пт в текстовое поле Ширина.

15. Затем повторите вышеуказанный шаг 10 - шаг 14 для второй нижней серии данных. Смотрите демонстрацию ниже:

16. И вы получите диаграмму с ящиком и усами, как показано на скриншоте ниже:

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

• Щелкните правой кнопкой мыши, чтобы выбрать верхние ящики, и выберите Заливка, затем выберите один цвет заливки, который вам нравится, например светло-синий.
• Затем выберите темно-синий из выпадающего списка Контур, продолжайте выбирать 1½ пт в разделе Толщина.

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

Скачать пример файла с диаграммой с ящиком и усами

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