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

диаграмму можно вывести и два ряда, и некоторые радуются такому лайфхаку.

Но это все-таки неудачная визуализация, она искажает данные. Посмотрите внимательно на оранжевый сектор: «Факт» в 23 % выглядит больше, чем «План» в 24 %. Это можно логически объяснить тем, что радиус кольца с «Фактом» больше, поэтому меньшая доля занимает больше места, чем на кольце с «Планом». Но в том и проблема, что это приходится объяснять. А правильная визуализация должна работать по принципу «увидел и понял».

Как компромисс в такой ситуации можно рассмотреть нормированную линейчатую диаграмму. Здесь два кольца развернулись в линии одинаковой длины. Сектора хотя и смещены относительно друг друга, зато 24 % явно больше, чем 23 %. И действительно, видно общее изменение фактической структуры выручки относительно плановой.

Обращаю ваше внимание на важность чек-листа оформления диаграммы! Каждый пункт по отдельности может казаться незначительным, но в сумме они создают совершенно другое впечатление. Сравните: если бы мы просто взяли и изменили тип диаграммы, то вот бы что вышло.

Вот ключевые моменты, которые мы доработали.

● Подписи данных отобразили в процентах. Для линейчатой диаграммы нет такого параметра отображения, поэтому нужно их пересчитать в исходных данных (дальше вы узнаете, как это сделать в сводной таблице).

● Сделали полосы широкими и наглядными за счет сокращения бокового зазора, а также за счет удаления шкалы и линий сетки.

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

Сложный тримап

Тримап может показывать структуру не только по одному ряду данных, но и по двум категориям, точнее – по двум уровням, например группировать виды услуг по направлениям деятельности логистической компании. Расскажу, как построить такую диаграмму с долями.

Шаг 1

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

Шаг 2

Чтобы привести данные сводной таблицы в нужный вид, необходимо на вкладке «Конструктор» выбрать для начала пункт «Макет отчета» и сначала подпункт «Показать в табличной форме», а затем подпункт «Повторять все подписи элементов».

После этого отключить показ итогов по кнопке «Промежуточные итоги» подпункт «Не показывать промежуточные суммы».

Шаг 3

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

Шаг 4

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

Шаг 5

Создаем на основе таблицы-дубля новую диаграмму тримап и настраиваем подписи данных, проставив галочку у пунктов «имя категории» и «значение», а также выбрав тип разделителя «новая строка».

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

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

С другой стороны, понимая анатомию диаграмм на таком уровне, вы и в продвинутых BI-системах будете иметь больше свободы действий.

Резюме

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

Рейтинг

Совмещаем два столбца по принципу буллет-чарта, как будто «Факт» пробивает границу «Плана».

● На основе линейчатой – за счет построения ряда «Факт» по вспомогательной оси, настройки бокового зазора.

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

Динамика

● Комбинируем линию и столбец, чтобы избежать нагромождения точек в одном месте.

● Используем вспомогательную ось, если значения разного порядка (одно в сотнях, а другое – в тысячах). Только шкалу нельзя удалять, придется маскировать ее в цвет фона.

Структура

● Для сопоставления структуры «Плана» и «Факта» используем нормированную линейчатую. Наглядной она будет в пределах 6 категорий.

● Для двух категорий подойдет тримап. Он покажет общую структуру по категориям верхнего уровня, а также структуру подкатегорий внутри.

6.4 Как показать все и сразу

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

– А давайте добавим на диаграмму не только план, но и факт прошлого года?

– Сравнивать с прошлым годом недостаточно (пандемия, кризис или новая напасть). Нужен еще предыдущий год.

– Добавьте на график отклонения в процентах. Нет, в абсолютных значениях. Хм, а давайте и в процентах, и в абсолютных!

Этот список можно продолжать долго, и от каждого нового пункта у менеджеров и аналитиков дергается глаз. Конечно, получается нечто ужасное.

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

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