Заставьте данные говорить. Как сделать бизнес-дашборд в Excel. Руководство по визуализации данных

● обе диаграммы можно сделать и в более старых версиях с помощью «подушки» – вспомогательных прозрачных рядов.

Воронка = динамика + структура

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

С одной стороны, это последовательный процесс, то есть динамика. А с другой – анализ изменения структуры: мы смотрим на то, какая доля отсеивается на каждом этапе. Для комбинации этих 2 видов анализа данных подходит диаграмма «Воронка».

В HR такой же подход используют для визуализации процесса подбора персонала. Здесь тоже важно, чтобы воронка постоянно пополнялась новыми кандидатами. И тоже известно, что на каждом последующем этапе будет отсев. Вот пример диаграммы, построенной в Power BI, – мне в ней нравится, что можно сразу отобразить конверсию каждого этапа.

Как построить воронку в Excel 2019 и выше

Начиная с версии 2019 эта диаграмма уже есть в стандартном наборе Excel. Я был приятно удивлен видом по умолчанию: минимальный боковой зазор, контрастные подписи. Все хорошо, только строить ее на сводных таблицах Excel отказывается. Но диаграмму можно сделать интерактивной. Вот как это работает.

Получается, что срез фильтрует сводную таблицу слева, а справа ячейки ссылаются на нее. Таким образом, мы обхитрили Excel и построили кликабельную воронку.

Лайфхак для старых версий

Excel 2019 или Office 365 есть далеко не у всех, так что покажу, как построить такую диаграмму в любой старой версии. Это можно сделать с помощью дополнительного столбца в таблице и линейчатой диаграммы.

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

Шаг 1

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

Для каждого следующего этапа в столбце «Подушка» задаем формулу: разницу значений воронки на предыдущем и текущем этапах делим на 2 и прибавляем значение «подушки» с предыдущего этапа.

В нашем случае формула выглядит так: =(E2-E3)/2+F2, где E – столбец с показателями, F – столбец со значениями «подушки», а 2 и 3 – номера строк с нужными данными.

Можно не добавлять каждый раз предыдущее значение, а вычитать всегда из первого, самого крупного (у меня это количество звонков). Для этого зафиксируем ячейку E2 (перед буквой и цифрой ставим знак $ либо просто нажимаем F4).

Шаг 2

Теперь в нашей таблице 3 столбца. Выделяем их и вставляем из ленты меню линейчатую диаграмму с накоплением. То, что построит Excel на этом этапе, пока еще совсем не похоже на воронку.

Дело в том, что «подушка» должна идти первым рядом и сдвигать воронку к центру. Чтобы сделать именно так, вызываем меню «Выбрать данные» и в открывшемся окне «Выбор источника» изменяем порядок столбцов: ряд «Воронка» опускаем вниз, нажав на кнопку со стрелкой.

Второй шаг, я надеюсь, вы уже выучили. Надо в сотый раз поставить галочку «Обратный порядок категорий» на оси. Тогда это станет больше похоже на прототип нужной диаграммы.

Шаг 3

Чтобы диаграмма приобрела форму воронки, теперь нам нужно сделать ряд «Подушка» невидимым, то есть просто убрать цветную заливку. Останется только настроить воронку по чек-листу из главы 3.4:

добавляем метки данных;

убираем ось со значениями;

удаляем линии сетки;

выставляем для рядов данных зазор в 10 %;

убираем легенду;

увеличиваем размер шрифта подписей и меток данных до 12.

Водопад = динамика + рейтинг

В оригинале эта диаграмма называется Waterfall Chart, в русском переводе MS Office – «Каскадная», но самый популярный вариант названия – «Водопадная диаграмма». В некоторых компаниях о ее построении говорят что-то вроде «строить бриджи» или даже «висячие сады».

Чаще всего этот визуальный элемент используют для факторного анализа. Диаграмма состоит из столбцов: первый и последний показывают начальное и конечное значения, а промежуточные – факторы, повлиявшие на результат. Наглядности придает разный цвет промежуточных столбцов: зеленый – там, где показатель вырос, красный – там, где он снизился.