Заставьте данные говорить. Как сделать бизнес-дашборд в Excel. Руководство по визуализации данных - Алексей Сергеевич Колоколов
Когда таблица – единственный вариант
Даже в Power BI и Tableau не существует диаграммы, которая наглядно отобразит 4 ряда данных в разрезе нескольких категорий. 3 показателя – это предел для отдельной диаграммы: можно использовать комбинированные графики, которые я показывал в предыдущей главе, или двухуровневый тримап для категорий с подкатегориями.
Для большего объема данных есть только один рабочий вариант – это таблицы. Многим такой вариант не нравится, но он хотя бы позволит прочитать значения. И это лучше перегруженной диаграммы.
Чтобы сделать таблицу наглядной и удобной для восприятия, важно не переборщить с красочным оформлением и расставить визуальные акценты грамотно.
Многие довольствуются стандартным форматированием сводных таблиц. Но у меня здесь подход такой же, как и с диаграммой: есть черновик для расчетов, а есть чистовая версия, которая попадает на лист с дашбордом.
Обратите внимание! В оформленной версии я убрал плановые показатели. А вместо них добавил отклонения в процентах с цветовой кодировкой по принципу светофора. Вычислять в уме разницу между «Планом» и «Фактом» не придется – у нас есть визуальный акцент на «Факте» и наглядный индикатор отклонения от «Плана».
Разберемся по шагам, как получить такой результат, используя дополнительные возможности сводных таблиц.
Как добавить вычисляемое поле
У меня для примера это будут вычисления по выполнению плана по выручке, прибыли и марже.
Столбцы в сводную добавляем на вкладке «Анализ сводной таблицы»: раздел «Вычисления» → кнопка «Поля элементы и наборы» → пункт «Вычисляемое поле».
В открывшемся окне «Вставка вычисляемого поля» даем понятное название новому полю и задаем формулу для вычислений. Если в формуле нужны данные из существующих полей сводной таблицы, выбираем их из блока «Поля» по названию.
Также нужно изменить разрядность фактических показателей на миллионы – так цифры станут короче, и воспринимать их будет проще. Для этого таким же образом создаем расчетные столбцы, только в формуле делим значение из столбца «Факт» на 1 000 000.
В итоге в списке полей появилось 3 новых показателя (выполнение по выручке и прибыли, маржа), а также 2 поля с переведенными в миллионы выручкой и прибылью.
Кто-то скажет, что проще и быстрее поделить факт на план в соседней ячейке, и будет прав. Но плюс вычисляемого поля в том, что его можно использовать и в других выборках сводной таблицы. Такой подход в дальнейшем сэкономит время, да и в целом он считается хорошим тоном.
Из таблицы мы убрали «План» и добавили вычисляемые поля. Но это по-прежнему просто таблица без визуальных акцентов. Чтобы их расставить, используем условное форматирование.
В Excel этот функционал очень обширный и имеет кучу настроенных форматов. Но выбирать его стили форматирования мы не будем – там будет откровенный примитив вроде красной заливки ячеек с зелеными значениями или другого визуального безобразия. Зададим нужные настройки самостоятельно.
Правила для форматирования ячеек
Перед форматированием таблицы заранее определимся с его правилами. Для этого нам нужно определить способы форматирования (где добавлять гистограммы, где значки, а где изменять цвет шрифта), а также задать значения, при которых цвет будет изменяться.
К числовым показателям выручки, прибыли и количества заказов для наглядности добавим гистограммы – длина столбцов в ячейках сделает сравнение наглядным. Столбцы со значениями в процентах форматируем с помощью цвета шрифта. Для столбца с выполнением плана используем такое правило: если план выполнен, цвет зеленый, если нет – красный. Значения в столбце «Маржа» отметим иконками с окошками «светофора». Негативные показатели подсветим красным, средние – желтым, а положительные – зеленым.Условное форматирование при помощи гистограмм
Для столбцов с фактическими значениями, как правило, добавляют гистограммы, чтобы визуально обозначить рейтинг.
Выделяем столбец и на вкладке «Главная» под кнопкой «Условное форматирование» выбираем пункт «Гистограммы», а далее указываем ее вид. Для столбца «Выручка, млн руб.» добавим синюю гистограмму.
Цвет добавленной гистограммы по умолчанию насыщенный, но для форматирования таблиц лучше брать менее яркие оттенки. Их можно поменять уже после настройки всех условий.
Для этого на вкладке «Главная» под кнопкой «Условное форматирование» выбираем пункт «Управление правилами». В открывшемся окне «Диспетчер правил условного форматирования» уже приведены правила, которые применены к сводной таблице.
Выбираем из правил нужное: можно просто кликнуть по нему дважды или нажать «Изменить правило». В открывшемся окне в разделе «Цвет» задаем более светлый оттенок синего, передвинув ползунок вверх. Нажимаем «ОК».
Аналогично меняем цвет и для других гистограмм. Выделяем столбец «Выполнение выручки» и на главной вкладке меню под кнопкой «Условное форматирование» выбираем «Правила выделения ячеек» → «Больше».
В открывшемся окне указываем значение в первом поле раздела «Форматировать ячейки, которые БОЛЬШЕ» – 100 %. Во втором поле выбираем «Пользовательский формат». В открывшемся дополнительном окне «Формат ячеек» на вкладке «Шрифт» задаем зеленый цвет.
Теперь зададим второе правило для этого же столбца – для случаев, когда план не выполнен. Повторяем предыдущие шаги, только в меню «Условное форматирование» выбираем пункт «Меньше», а в «Пользовательском формате» задаем красный цвет.
Условное форматирование значками
Для условного форматирования столбца «Маржа» будем использовать цветные иконки по правилу светофора:
● красный цвет – для значений меньше 10 %;
● желтый цвет – для значений от 10 до 20 %;
● зеленый цвет – для значений больше 20 %.
Для этого выделяем столбец «Маржа» и на главной вкладке меню под кнопкой «Условное форматирование» выбираем «Наборы значков» → «Другие правила».
В открывшемся окне задаем наши правила. Указываем нужное значение в разделе «Отображать каждый значок согласно этим правилам», а в выпадающем списке «Тип» выбираем «Число».
После всех этих действий уберем заливку для заголовков и итогов. И получим гармонично оформленную таблицу в более спокойных цветах.
Так как это сводная таблица, к ней можно подключить срезы для фильтрации данных.
Типовые ошибки
С условным форматированием важно не перестараться, иначе
