Воронка, водопад и волшебная «подушка» — новая «Нарния» в Excel и BI-аналитике

Навигация по разделам:

• Воронка = динамика + структура
• Как построить воронку в Excel 2019 и выше
• Лайфхак для старых версий
• Водопад = динамика + рейтинг
• Как построить диаграмму в более новых версиях программы и использовать лайфхак с «подушкой» в версиях до 2016 года
• Резюме

Есть продвинутые диаграммы, которые можно построить в Excel.

Наиболее популярные из нестандартных: 

1. воронка 
2. водопадная диаграмма. 

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

Кто-то использовал дополнительные надстройки, например Thinkcell, чтобы сделать такую диаграмму в PowerPoint. Кто-то думал, что для этого нужна только BI-система. 

И тут есть два нюанса:

• и воронка, и водопад есть в новых версиях Excel, но построить их на сводных таблицах нельзя;
• обе диаграммы можно сделать и в более старых версиях с помощью «подушки» — вспомогательных прозрачных рядов.

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

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

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

Воронка = динамика

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

Вот пример диаграммы, построенной в Power BI — в ней можно сразу отобразить конверсию каждого этапа.

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

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

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

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

Построить такую диаграмму в любой старой версии можно с помощью дополнительного столбца в таблице и линейчатой диаграммы.

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

В дальнейшем эту «подушку» мы сделаем невидимой.

1. Добавляем в таблицу новый столбец с названием «Подушка»

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

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

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

Читайте по теме:

Самые распространенные ошибки в Excel и как их исправить

Как анализ данных поможет усилить позиции бизнеса в условиях хаоса

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

2. Теперь в нашей таблице три столбца

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

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

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

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

А дальше: 

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

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

В оригинале эта диаграмма называется Waÿerfall Chart, в русском переводе MS Office — «Каскадная», но самый популярный вариант названия — «Водопадная диаграмма». В некоторых компаниях о ее построении говорят что-то вроде «строить бриджи» или «висячие сады». Чаще всего этот визуальный элемент используют для факторного анализа. 

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

• зеленый — там, где показатель вырос,
• красный — там, где он снизился.

Этот пример построен на финансовых данных о том, как сформировался размер чистой прибыли. 

Мы видим, что из валовой прибыли вычли управленческие расходы, добавили проценты к получению и вычли проценты к уплате. Потом добавили 2,8 млн прочих доходов и получили пик денежного потока: 4,1 млн из него списали в прочие расходы, а уже с оставшейся небольшой базы уплатили 340 тыс. налога. 

Читайте также: В Excel появится возможность создавать пользовательские функции

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

• динамику, чтобы по этапам отобразить изменения показателя от начальной точки до конечной;
• рейтинг, чтобы количественно сравнить факторы, повлиявшие на результат положительно или отрицательно.

Финансисту эта информация будет понятна и в таблице. Но руководителю, предпринимателю, не погруженному в детали расчетов, такая визуализация позволяет сразу увидеть, за счет чего компания ушла в минус, а что повлияло на увеличение прибыли. 

Цветовая кодировка может быть инвертирована в случае анализа расходов. Здесь мы видим, что плановые расходы на оплату труда были 55 млн, а по факту составили 70,5 млн.

Основная экономия вышла за счет сдвига сроков, вывода объектов и немного за счет сокращения административно-управленческого персонала. А перерасход вышел из-за пересмотра ставок, текучести и премирования. 

На диаграмму мы вывели самые значимые факторы — но были еще и мелкие, которые в сумме сэкономили 5 млн рублей.

Как построить диаграмму в более новых версиях программы и использовать лайфхак с «подушкой» в версиях до 2016 года

Как построить каскадную диаграмму в Excel 2016 и выше

В Excel эта диаграмма появилась с 2016 года, но используют ее нечасто — во многом из-за логики построения: она непонятна интуитивно, и ее не отформатировать по образцу привычных столбцов и графиков.

Разберем на примере изменения прибыли.

Исходные данные нужно записать в таблицу так, чтобы расходы шли со знаком минус: именно по этому критерию Excel будет определять направление столбца: положительные значения — вверх, отрицательные — вниз.

• На ленте находим иконку с каскадной диаграммой. Нажимаем и получаем странную фигуру

На этом шаге многие окончательно разочаровываются в новинках Excel и уходят собирать водопад из прямоугольников в PowerPoint.

Сначала еще понятно: синий столбец валовой прибыли, из него вычитаются управленческие расходы... Но вместо зеленых столбцов роста — опять синие, а в конце чистая прибыль висит в воздухе. Да и в легенде еще какой-то непонятный серый «Итог».

• Дело в том, что «Итог» — это и есть признак столбца, который будет строиться от нуля

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

После этого правой кнопкой мыши вызываем контекстное меню и выбираем «Установить в качестве итога». Аналогично делаем для начального столбца.

• «Опоры моста» стали нейтрально серыми, но вот «доходы» остались желтыми, а я хочу их сделать зелеными

И тут снова начинаются неочевидные нюансы. В Excel предусмотрена возможность настроить цвет сразу для всей категории.

Для этого нажимаем на легенду один раз, затем еще раз — на элемент «Увеличение» — и выбираем ему зеленый цвет. Такая функция стабильно работает только в версиях 2019+, в более старых это окрашивает фон легенды в зеленый.

• Оформляем диаграмму 

Для этого убираем лишние:

1. ось Y, 
2. линии сетки, 
3. легенду. 

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

Если для текста недостаточно места, Excel будет принудительно поворачивать его и размещать под углом. Так что если у вас длинные названия факторов отклонений, то они все равно не уместятся, и с этим ничего не поделать.

• Последние штрихи

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

Расположение подписей данных не случайно: для плюсовых категорий они над цветными столбцами, а для минусовых — под ними. 

Что я делаю далее: 

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

Теперь чистовик готов.

Так же как и воронка, каскадная диаграмма не строится на сводных таблицах в Excel. Чтобы его обмануть, опять делаем ссылки на соседние ячейки.

Как сделать водопад в старых версиях Excel

Если у вас Excel 2013 или все еще 2010, вы тоже можете построить такой же красивый водопад с помощью «подушки». 

Например, можно сделать горизонтальный водопад на основе линейчатой диаграммы и вместить длинные названия категорий.

• В таблице создаем столбцы «Минус» и «Плюс» 

В первый перенесем из столбца «Сумма» показатели с отрицательными значениями (без знака минус!), а во второй — с положительными. В «Сумме» оставляем только исходный показатель — размер валовой прибыли. На основе получившейся таблицы и будем строить диаграмму.

• Теперь будем наполнять опустевший столбец «Сумма»

Для этого понадобятся формулы. Сначала заполним вторую ячейку столбца, под показателем валовой прибыли.

Вводим формулу: 

• отмечаем предыдущую ячейку этого столбца →
• отнимаем значение из столбца «Минус» в активной строке → 
• прибавляем предыдущую ячейку столбца «Плюс».

В моем случае формула будет выглядеть так: =C3-D4+E3. После этого останется только протянуть формулу на весь столбец «Сумма». 

Если вы все правильно посчитали, то два последних значения (налог на прибыль и чистая прибыль) у вас будут одинаковыми. В результате получаем вот такую таблицу.

• Выделяем таблицу и вставляем линейчатую диаграмму с накоплением

Получаем базу для будущего каскада. 

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

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

Есть еще много нюансов визуализации факторного анализа: 

• добавление столбцов с промежуточными итогами,
• «накопительный» пересчет с начала года с фильтрацией через сводные таблицы. 

Я считаю это полезной разминкой для мозгов. Когда такое входит в привычку, и вы за 10 минут делаете нужную визуализацию, пока другие часами пытаются на словах объяснить свой замысел и согласуют макеты. 

В этом и есть свобода действий и гибкость работы в новом цифровом мире.

Резюме

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

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

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

1. Добавьте в таблицу новый столбец и рассчитайте в нем значения «подушки» по заданной формуле.
2. Постройте линейчатую диаграмму с накоплением и поставьте ряд «Подушка» первым.
3. Уберите цветную заливку с этого ряда, чтобы он стал прозрачным.
4. Оформите диаграмму по чек-листу.

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

1. Добавьте в таблицу дополнительные столбцы с отрицательными и положительными значениями.
2. Заполните опустевший основной столбец новыми значениями «подушки» по заданной формуле.
3. Постройте гистограмму с накоплением. Ряд с «подушкой» сделайте прозрачным, только верните цвет первому и последнему столбцам — это «опоры моста».
4. Для факторов отклонений задайте цвета по правилу светофора.
5. Оформите внешний вид диаграммы по чек-листу.

Иллюстрации предоставлены автором 

Фото на обложке: Shutterstock / PeopleImages.com - Yuri A