Читать интересную книгу Заставьте данные говорить. Как сделать бизнес-дашборд в 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 – «Каскадная», но самый популярный вариант названия – «Водопадная диаграмма». В некоторых компаниях о ее построении говорят что-то вроде «строить бриджи» или даже «висячие сады».

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

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

Чтобы получить такую визуализацию, мы также комбинируем два базовых вида анализа:

● динамику, чтобы по этапам отобразить изменения показателя от начальной точки до конечной;

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

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

Цветовая кодировка может быть инвертирована в случае анализа расходов. Здесь мы видим, что плановые расходы на оплату труда были 55 млн, а по факту составили 70,5 млн. Основная экономия вышла за счет сдвига сроков, вывода объектов и немного за счет сокращения административно-управленческого персонала. А перерасход вышел из-за пересмотра ставок, текучести и премирования. На диаграмму мы вывели самые значимые факторы, но были еще и мелкие, которые в сумме сэкономили 5 млн рублей.

Расскажу, как построить диаграмму в более новых версиях программы (спойлер – легко), а также как использовать лайфхак с «подушкой» в версиях до 2016 года.

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

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

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

Шаг 1

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

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

Шаг 2

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