Під час статистичного аналізу може знадобитися створити стандартну коробчасту діаграму, щоб відобразити розподіл набору даних. На такій діаграмі числові дані розділено на квартилі, а між першим і третім квартилями розташовано прямокутник із додатковою лінією, що проходить через другий квартиль і позначає медіану. На деяких коробчастих діаграмах мінімальні та максимальні значення, що виходять за межі першого й третього квартилів, зображено як лінії, які часто називають вусиками.
Хоча в програмі Excel 2013 немає шаблону діаграми для коробчастої діаграми, ви можете створити коробчасті діаграми, виконавши такі дії:
- Обчисліть значення квартилів із вихідного набору даних.
- Обчисліть різницю між квартилями.
- З діапазонів квартилів створіть стовпчасту діаграму з накопиченням.
- Перетворіть стовпчасту діаграму з накопиченням на коробчасту.
У нашому прикладі у вихідному наборі даних налічується три стовпця. Кожен із них включає по 30 записів із таких діапазонів:
- Стовпець 1 (2013 р.): 100–200
- Стовпець 2 (2014 р.): 120–200
- Стовпець 3 (2015 р.): 100–180
У цій статті
Крок 1. Обчислення значень квартилів
Спочатку потрібно обчислити мінімальне, максимальне значення та медіану, а також перший і третій квартилі з набору даних.
Для цього створіть другу таблицю та заповніть її такими формулами:
Значення Формула мінімальне значення MIN(cell range) Перший квартиль QUARTILE.INC(cell range;1) Значення медіани QUARTILE.INC(cell range;2) Третій квартиль QUARTILE.INC(cell range;3) максимальне значення MAX(cell range) У результаті ви отримаєте таблицю, яка містить потрібні значення. На основі прикладу набору даних обчислюються такі квартилі:
Крок 2. Обчислення різниці між квартилями
Потім обчислити різницю між кожним етапом. По суті, потрібно обчислити диференціали між такими значеннями:
- між першим квартилем і мінімальним значенням;
- між медіаною та першим квартилем;
- між третім квартилем і медіаною;
- між максимальним значенням і третім квартилем.
- Для початку створіть третю таблицю та скопіюйте до неї мінімальні значення з останньої таблиці.
- Обчислити різницю між квартилями за допомогою формули віднімання Excel (клітинка1 – клітинка2) і заповнити третю таблицю диференціалами.
Наприклад, набір даних у третій таблиці має такий вигляд:
Крок 3. Створення стовпчастої діаграми з накопиченням
Дані в третій таблиці підходять для побудови коробчастої діаграми. Для початку створимо стовпчасту діаграму з накопиченням, а потім змінимо її.
Виділіть усі дані з третьої таблиці та натисніть кнопку Вставити>стовпчасту діаграму> знакопиченням.
Спочатку діаграма ще не схожа на коробчасту діаграму, оскільки excel за замовчуванням креслить стовпці з накопиченням із горизонтальних, а не вертикальних наборів даних.
Щоб поміняти осі діаграми місцями, клацніть її правою кнопкою миші та виберіть команду Вибір даних.
Клацніть Перехід рядок/стовпець.
Порада.
- Щоб перейменувати стовпці, в області Підписи горизонтальної осі (категорії) клацніть Змінити, виберіть у третій таблиці діапазон клітинок із потрібними іменами категорій і натисніть кнопку OK.
- Щоб перейменувати записи легенди, в області Записи легенд (ряди) натисніть кнопку Змінити та введіть потрібне значення.
- Щоб перейменувати стовпці, в області Підписи горизонтальної осі (категорії) клацніть Змінити, виберіть у третій таблиці діапазон клітинок із потрібними іменами категорій і натисніть кнопку OK.
Натисніть кнопку OK.
Графік тепер має виглядати, як показано нижче. У цьому прикладі також змінено назву діаграми та приховано легенду.
Крок 4. Перетворення стовпчастої діаграми з накопиченням на коробчасту
Приховання нижнього ряду даних
Щоб перетворити стовпчасту діаграму з накопиченням на коробчасту, спочатку приховайте нижній ряд даних:
Виберіть нижню частину стовпців.
Примітка.
Якщо клацнути один стовпець, буде виділено всі екземпляри одного ряду.
Натисніть кнопку Формат>виділеного фрагмента>. Праворуч відкриється панель Формат.
На панелі Формат на вкладці Заливка виберіть Без заливки.
Нижній ряд даних у діаграмі буде приховано.
Створення вусиків для коробчастої діаграми
Потім потрібно замінити верхній і другий знизу ряди (їх виділено на зображенні темно-синім і помаранчевим кольорами) на лінії, або вусики.
Виберіть верхній ряд даних.
На панелі Формат на вкладці Заливка виберіть Без заливки.
На стрічці виберіть конструктор> додатипланки похибок>елементів>діаграми Standard відхилення.
Клацніть одну з накреслених планок похибок.
Перейдіть на вкладку Параметри планки похибок і на панелі Формат установіть такі параметри:
у розділі Напрямок установіть перемикач Мінус;
для параметра Кінцевий стиль укажіть значення Без точки;
для параметра Величина похибки укажіть відсоток рівним 100.
Повторіть попередні кроки для другого знизу ряду даних.
Стовпчаста діаграма з накопиченням тепер має нагадувати коробчасту.
Коробчасті діаграми зазвичай відображаються з однотонною заливкою та тонкими межами. Нижче описано, як завершити їх оформлення.
Виберіть верхню частину коробчастої діаграми.
На вкладці Заливка & Лінія на панелі Формат натисніть кнопку Суцільна заливка.
Виберіть колір заливки.
Клацніть Суцільна лінія на цій самій вкладці.
Виберіть колір контуру та ширину розчерку.
Укажіть ті самі значення для інших областей коробчастої діаграми.
У результаті ви отримаєте коробчасту діаграму.