Распределение показателей в Excel дашборде

Наиболее распространенным способом визуализации распределения числовых данных  в описательной статистике является коробчатый график («ящик с усами»). В excel такие графики носят название биржевые диаграммы. Но по опыту ежедневной работы, приходит понимание, что не многие люди знакомы с этим типом визуализации. Поэтому мы постараемся создать более простую диаграмму, для упрощения восприятия.

распределение показателей дашборда

Светло серый ряд данных отображает все значения KPI, темно серый – 10 видимых на дашборде показателей. Крестом помечено среднее значение, вертикальная линия определяет целевое значение. На данном графике собрано меньше информации, чем в традиционном коробчатом графике, но я предполагаю, что это даст большее понимание.

Реализация

Для лучшего понимания скачайте файл с примером создания распределения показателей дашборда

Давайте посмотрим, какая информация нам понадобится для построения ящика с усами: минимум и максимум для всех данных и десяти видимых на дашборде, среднее и целевое значение. Формулы достаточно просты. Вы сможете их найти на листе Расчеты в диапазоне AX17:BD22.

Основой нашей визуализации станет гистограмма с одной категорией и четырьмя рядами данных.

  1. Невидимый столбец (от нуля до общего минимума)
  2. Левый светло-серый столбец (от общего минимума до минимума 10-ти видимых значений)
  3. Темно-серый столбец (от минимума до максимума 10-ти видимых значений)
  4. Правый светло-серый столбец (от максимума 10-ти видимых значений до общего максимума)

Опять же эти формулы достаточно просты, находятся они в диапазоне Расчеты!BF17:BH22

данные для дашборда

Создайте линейчатую диаграмму и отформатируйте ее соответствующим образом (без заливки и границ для невидимого ряда данных и светло- и темно-серая заливка для остальных колонок)

Добавьте два дополнительных ряда данных для среднего и целевого значений. Используйте для этого точечные диаграммы (Координаты оси X для будут среднее и целевое значение, по оси Y – просто 1). Отформатируйте маркер среднего значения как крест (либо, что вам больше нравиться). Для этого щелкните правой кнопкой мыши по маркеру ряда данных со средним значением и в меню выберите Формат ряда данных. Справа (Office 2013) появиться диалоговое окно, со свойствами ряда данных. Нас интересуют оформительские свойства маркера. Выбираем Граница и заливка (иконка ведра с краской) -> Маркер –> Параметры маркера. Указываем Встроенный, из списка возможных маркеров выбираем понравившийся и задаем нужный нам размер.

Форматирование среднего значения

Теперь нам необходимо отформатировать ряд данных с целевым значением. Я хочу, чтобы оно отображалось в виде вертикальной линии синего цвета (аналогично среднему значению в таблице дашборда). Можно прибегнуть к предыдущему способу выделения, но мы немного схитрим, для отображения полосы, мы воспользуемся пределами погрешности не совсем традиционным способом.

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

изменеие типа диаграммы в excel

Далее, активируем всю диаграмму полностью и идем во вкладку Работа с диаграммами –> Конструктор -> Добавить элемент диаграммы -> Предел погрешностей -> Дополнительные параметры предела погрешностей. В появившемся диалоговом окне Добавление предела погрешностей выбираем целевой ряд данных. Это позволило отобразить погрешности для наших данных. Теперь настраиваем внешний вид, щелкаем правой копкой на погрешности, в появившемся справа окне настраиваем данные для вертикального предела, как на рисунке:

форматирование предела погрешности в excel дашборде

Теперь тоже самое проделываем для горизонтального предела, щелкаем на треугольнике справа от фразы Параметры предела погрешностей, в выпавшем меню выбираем Ряд «Целевое» предел погрешности по X» и задаем параметры Стиль края –> Точка, Величина погрешности –> фиксированное значение -> 1.2.

И на последок задаем цвет линии – синий.

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

Удаляем все ненужные элементы графиков: заливку, границы, линии сетки и т.д.

Повторяем предыдущие шаги для создания остальных показателей дашборда

Разместите диаграммы на дашборде в нужном месте и добавьте описание к элементам диаграммы.

Вот и все. Теперь можно насладиться новыми возможностями нашего дашборда: поменяйте критерий сортировки, прокрутите вверх/вниз и вы увидите, как меняются графики.

Напоследок

Это упрощенная версия коробчатого графика и работает только с положительными значениями. Конечно, существуют более сложные пути создания графиков с любыми наборами данных (положительными и отрицательными, т.е. ряды данных пересекающие вертикальную ось). Это немного сложнее, так как необходимо будет оперировать восьмью рядами данных, а не четырьмя, но подход такой же.

Наш финальный дашборд будет выглядеть следующим образом.

финальный excel дашборд

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


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

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *