Как строить boxplot в excel

Данный пост будет интересен тем, у кого пока нет офиса 16, в 16-м офисе в excel уже есть возможность строить боксплот.

Warning
Если у вас 2016 excel и выше, см. видео

В посте Прогноз эффективности продавцов на основе теста CPI и многих других постах ранее я использовал диаграмму boxplot, 

Диаграмму включает такие показатели:

• медиана - средняя линия в боксе;
• минимальное значение - нижний хвост, перевернутая Т;
• первый квартиль - "дно" бокса;
• третий квартиль - "крышка" бокса;
• максимальное значение - верхний хвост 
• и выбросы, которые точками

Диаграмма информативна и показательна для решения задач:

• представления о распределении выборки (нормальное / ненормальное, в какую сторону scew)
• разнице групп

Делал я эту диаграмму в программе Rstudio. Можно делать в SPSS. Но это специализированные программы, а если под рукой только excel? А в excel нет возможности создать легко и просто такой тип диаграммы. Зато есть возможность создать не очень легко и не очень просто. И я расскажу, как это делается.

За основу возьмем данные этого поста Прогноз эффективностипродавцов на основе теста CPI. 

Итак, строим boxplot в excel

Шаг №1 Строим таблицу показателей boxplot

	эфф	средние	неэффективные
медиана	47	42	36
1-й квартиль	44	37	31
min	31	25	24
max	57	56	54
3-й квартиль	51	47	44

Используем для построения формулы в excel – Медиана, Квартиль, Мин, Макс, Квартиль

Порядок обязательно тот, который указан в таблице.

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

Шаг №2 строим гистограмму

В excel: Вставка / Гистограмма / Гистограмма с группировкой

Самая простая гистограмма (диаграммы можно увеличивать кликом)

Получаем

Шаг № 3 Строки в столбцы

Далее делаем процедуру перевода строк в столбцы: на закладке Работа с диаграммами / Конструктор находим кнопку Строка / столбец

Получаем

Шаг № 4 Меняем тип диаграммы

Кликаем по полю диаграммы правой клавишей и выбираем изменить тип

Выбираем Биржевой тип диаграммы, последний рисунок

Получаем

Шаг № 4 Формат оси

Приводим формат оси к единой шкале

Задаем границы с двух сторон

получаем

Шаг № 5 Делаем прозрачным прямоугольник

Выделяем правой клавишей прямоугольники, кликаем на Формат полосы повышения

Выбираем поинт Нет заливки

Получаем

Шаг № 6 Меняем тип диаграммы для синей области

Кликаем правой клавишей по синей области, в открывшемся списке выбираем Изменить тип диаграммы для ряда

Выбираем тип диаграммы График, четвертый рисунок

Получаем

Шаг № 7 наводим красоту

Кликаем в легенде по медиане правой клавишей, выбираем формат ряда данных

Там выбираем цвет линий / нет линий

Получаем

Синий кружочек это наша медиана. Все, boxplot в excel готов

Шаг № 8 на любителя

Я бы допилил до такого состояния диаграмму. В данном случае «допилка» относится к базовым функциям работы с диаграммами, поэтому я не показываю, как это делать.

Мне вот такая картинка нравится: я вывел значения мин, макс, квартилей на диаграмме.