Bullet chart в Excel — план-факт как никогда удобен 16

Продолжаем нашу замечательную серию статей про нестандартные графики в Excel. Кстати, раз уж рубрика стала постоянной, почему бы её не назвать «Эксель без штанов»? 🙂 Как обычно, никаких плагинов, обычный эксель и немного.. логики 🙂

Bullet Graph (bullet (eng) -дословный перевод «пуля») — достаточно новый вид диаграммы, появившийся всего несколько лет назад. Его придумал ни кто иной как Stephen Few, автор блога Perceprual Edge, книги о дэшбордах Information Dashboard Design (и ещё двух: Now you see it, и Show me the numbers). Ссылки на книги также доступны в конце этой статьи.

Ключевая идея, заложенная в Буллете — более компактный, и более удобный для чтения, индикатор для сложных многосоставных план-факт переменных. Очень полезен в дэшбордах. Занимает всего одну строчку, а читается не сложнее спидометров. Кстати, он уже пробегал чуть раньше в статье про диаграмму Nightingale, но, видимо, на него никто на тот момент не обратил внимание.

Итак, график в разрезе:

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

Состав диаграммы:

1. Название буллета
2. Количественная шкала, вдоль единой горизонтальной оси
3. Значение переменной
4. Значения переменной, используемые для сравнения (бенчмарки)
5. Цветовая шкала, кодирующая шкалу по качественному признаку.

А теперь строим это в экселе.

1. Делаем таблицу с данными:

• целевое значение (план)
• текущее значение (факт)
• сегменты «качественных» блоков (сектора)

2 Вычисляем все кусочки графика:

2.1 Вычисляем максимальную длину графика, как максимум из плана и факта (не забываем, что иногда план больше факта, а иногда наоборот). Делаем её чуть длиннее (умножим на 1.2, например);

2.2 Кусочки сектора есть части от максимальной длины (переводим %% в аболютные значения);

2.3 Буллет будет у нас Stack Bar / Row 100%, поэтому вычислим его кусочки: основа, кусок до плана, плановая отсечка, кусок после плана.

3. Нормируем все значения на единую длину, чтобы не пришлось задавать мин-макс значение по осям (высчитываем единую длину как максимум из значений):

4. Строим Stacked Row 100% диаграмму, и то что относится к буллету — располагаем на второй оси. Должно получиться вот так:

5. Ставим ширину зазора (gap) для подложки равную 0%.

6. Перекрашиваем сегменты буллета в чёрный, а план-отсечку в красный. Низ перекрашиваем в оттенки нужного цвета (для примера взял оттенки серого). И вот что получилось в итоге:

Две подсказки:

• Нормируем всё на максимальную длину, чтобы получить %% выполнения
• Чтобы плановая отсечка не была слишком маленькой, руками в формуле ставим её % равным 1% (на картинке ячейка выделена жёлтым цветом).

Итог: работает универсально при план > факте, и факте > плана.

Enjoy!

Ну а кто придумает более элегантное решение? Есть ещё пара вариантов решения задачи (например, при помощи маркеров ошибок), но меня рассказать всё — просто не хватит.

Кстати, буллеты могут быть как горизонтальными, так и вертикальными, и они ну оо-о-о-очень применимы в дэшбордах:

Как и все нестандартные графики, они требуют немного сноровки — но зато результат стоит того.  Я надеюсь они найдут применение у вас:) Присылайте скриншоты!

И в заключение, ссылки на книги Стивена на Amazon.

Покупайте!