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

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

Шаг 2

Настроить ряд данных «План»: в разделе «Линия» выставляем значение «Нет линий», а в разделе «Маркер» в подразделе «Параметры маркера» выбираем вид «Встроенный», тип – самая большая черта и выставляем размер 45.

Шаг 3

Из-за перекрытия маркером значений факта необходимо перенести подписи данных для этого ряда и выбрать положение «В центре».

Но все получилось не идеально: для малых значений у «Логистики и сервиса» места меткам данных все равно не хватает.

К сожалению, такой тип буллет-чарта можно построить в Excel только для вертикальных столбцов, но не для горизонтальных. Потому что маркер – это атрибут графика, а график может отображать только динамику. А динамика может быть только на горизонтальной временной шкале. Здесь, как ни странно, создатели Excel поступили правильно и даже не дали возможности строить вертикальные графики.

Диаграммы для сложной структуры

В корпоративной отчетности структура редко принимает какие-то сложные формы. Но когда это случается, примерно 8 из 10 отчетов доносят эту информацию и непонятно и неправильно. В частности, это касается двух случаев:

● когда нужно показать структуру сразу по двум параметрам;

● когда структуру нужно представить в комбинации с другим видом анализа.

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

Ошибка: кольцевая для двух рядов данных

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

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

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

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

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

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

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

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

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

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

Шаг 1

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

Шаг 2

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

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

Шаг 3

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

Шаг 4

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

Шаг 5