Наиболее распространенным способом визуализации распределения числовых данных в описательной статистике является коробчатый график («ящик с усами»). В excel такие графики носят название биржевые диаграммы. Но по опыту ежедневной работы, приходит понимание, что не многие люди знакомы с этим типом визуализации. Поэтому мы постараемся создать более простую диаграмму, для упрощения восприятия.
Светло серый ряд данных отображает все значения KPI, темно серый – 10 видимых на дашборде показателей. Крестом помечено среднее значение, вертикальная линия определяет целевое значение. На данном графике собрано меньше информации, чем в традиционном коробчатом графике, но я предполагаю, что это даст большее понимание.
Реализация
Для лучшего понимания скачайте файл с примером создания распределения показателей дашборда
Давайте посмотрим, какая информация нам понадобится для построения ящика с усами: минимум и максимум для всех данных и десяти видимых на дашборде, среднее и целевое значение. Формулы достаточно просты. Вы сможете их найти на листе Расчеты в диапазоне AX17:BD22.
Основой нашей визуализации станет гистограмма с одной категорией и четырьмя рядами данных.
- Невидимый столбец (от нуля до общего минимума)
- Левый светло-серый столбец (от общего минимума до минимума 10-ти видимых значений)
- Темно-серый столбец (от минимума до максимума 10-ти видимых значений)
- Правый светло-серый столбец (от максимума 10-ти видимых значений до общего максимума)
Опять же эти формулы достаточно просты, находятся они в диапазоне Расчеты!BF17:BH22
Создайте линейчатую диаграмму и отформатируйте ее соответствующим образом (без заливки и границ для невидимого ряда данных и светло- и темно-серая заливка для остальных колонок)
Добавьте два дополнительных ряда данных для среднего и целевого значений. Используйте для этого точечные диаграммы (Координаты оси X для будут среднее и целевое значение, по оси Y – просто 1). Отформатируйте маркер среднего значения как крест (либо, что вам больше нравиться). Для этого щелкните правой кнопкой мыши по маркеру ряда данных со средним значением и в меню выберите Формат ряда данных. Справа (Office 2013) появиться диалоговое окно, со свойствами ряда данных. Нас интересуют оформительские свойства маркера. Выбираем Граница и заливка (иконка ведра с краской) -> Маркер –> Параметры маркера. Указываем Встроенный, из списка возможных маркеров выбираем понравившийся и задаем нужный нам размер.
Теперь нам необходимо отформатировать ряд данных с целевым значением. Я хочу, чтобы оно отображалось в виде вертикальной линии синего цвета (аналогично среднему значению в таблице дашборда). Можно прибегнуть к предыдущему способу выделения, но мы немного схитрим, для отображения полосы, мы воспользуемся пределами погрешности не совсем традиционным способом.
Щелкаем правой кнопкой мыши по маркеру целевого значения, в выпадающем меню выбираем Изменить тип диаграммы. И меняем тип ряда данных с целевым значением на точечная с прямыми отрезками.
Далее, активируем всю диаграмму полностью и идем во вкладку Работа с диаграммами –> Конструктор -> Добавить элемент диаграммы -> Предел погрешностей -> Дополнительные параметры предела погрешностей. В появившемся диалоговом окне Добавление предела погрешностей выбираем целевой ряд данных. Это позволило отобразить погрешности для наших данных. Теперь настраиваем внешний вид, щелкаем правой копкой на погрешности, в появившемся справа окне настраиваем данные для вертикального предела, как на рисунке:
Теперь тоже самое проделываем для горизонтального предела, щелкаем на треугольнике справа от фразы Параметры предела погрешностей, в выпавшем меню выбираем Ряд «Целевое» предел погрешности по X» и задаем параметры Стиль края –> Точка, Величина погрешности –> фиксированное значение -> 1.2.
И на последок задаем цвет линии – синий.
Методика комбинирования гистограмм и точечных диаграмм аналогична той, что мы использовали при создании микрографиков дашборда в одной из предыдущих статьях.
Удаляем все ненужные элементы графиков: заливку, границы, линии сетки и т.д.
Повторяем предыдущие шаги для создания остальных показателей дашборда
Разместите диаграммы на дашборде в нужном месте и добавьте описание к элементам диаграммы.
Вот и все. Теперь можно насладиться новыми возможностями нашего дашборда: поменяйте критерий сортировки, прокрутите вверх/вниз и вы увидите, как меняются графики.
Напоследок
Это упрощенная версия коробчатого графика и работает только с положительными значениями. Конечно, существуют более сложные пути создания графиков с любыми наборами данных (положительными и отрицательными, т.е. ряды данных пересекающие вертикальную ось). Это немного сложнее, так как необходимо будет оперировать восьмью рядами данных, а не четырьмя, но подход такой же.
Наш финальный дашборд будет выглядеть следующим образом.
Вы можете более детально познакомиться с предыдущими шагами создания дашбордов из предыдущих статей
Подскажите а можно ли как нибудь на этот дэшбоард наложить фильтры?
Стас, при создании любого дашборда предполагается, что будут использоваться фильтры, все зависит от вашей фантазии