.

Сделать репост в соц сети!

суббота, 13 декабря 2014 г.

Как строить 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: Вставка / Гистограмма / Гистограмма с группировкой
Самая простая гистограмма (диаграммы можно увеличивать кликом)
Как строить boxplot в excel

Получаем

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

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

Получаем
Как строить boxplot в excel



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

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

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

Получаем
Как строить boxplot в excel

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

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

Задаем границы с двух сторон
Как строить boxplot в excel
получаем
Как строить boxplot в excel

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

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

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

Выбираем поинт Нет заливки
Как строить boxplot в excel

Получаем
Как строить boxplot в excel

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

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

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

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

Получаем
Как строить boxplot в excel

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

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

Там выбираем цвет линий / нет линий
Как строить boxplot в excel

Получаем

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

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

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

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

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


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


2 комментария:

  1. Никогда бы не подумал, что это скажу, но чёрт возьми, как всё просто в R

    ОтветитьУдалить