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

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

Также нужно изменить разрядность фактических показателей на миллионы – так цифры станут короче, и воспринимать их будет проще. Для этого таким же образом создаем расчетные столбцы, только в формуле делим значение из столбца «Факт» на 1 000 000.

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

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

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

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

Правила для форматирования ячеек

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

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

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

Значения в столбце «Маржа» отметим иконками с окошками «светофора». Негативные показатели подсветим красным, средние – желтым, а положительные – зеленым.

Условное форматирование при помощи гистограмм

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

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

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

Для этого на вкладке «Главная» под кнопкой «Условное форматирование» выбираем пункт «Управление правилами». В открывшемся окне «Диспетчер правил условного форматирования» уже приведены правила, которые применены к сводной таблице.

Выбираем из правил нужное: можно просто кликнуть по нему дважды или нажать «Изменить правило». В открывшемся окне в разделе «Цвет» задаем более светлый оттенок синего, передвинув ползунок вверх. Нажимаем «ОК».

Аналогично меняем цвет и для других гистограмм. Выделяем столбец «Выполнение выручки» и на главной вкладке меню под кнопкой «Условное форматирование» выбираем «Правила выделения ячеек» → «Больше».

В открывшемся окне указываем значение в первом поле раздела «Форматировать ячейки, которые БОЛЬШЕ» – 100 %. Во втором поле выбираем «Пользовательский формат». В открывшемся дополнительном окне «Формат ячеек» на вкладке «Шрифт» задаем зеленый цвет.

Теперь зададим второе правило для этого же столбца – для случаев, когда план не выполнен. Повторяем предыдущие шаги, только в меню «Условное форматирование» выбираем пункт «Меньше», а в «Пользовательском формате» задаем красный цвет.

Условное форматирование значками

Для условного форматирования столбца «Маржа» будем использовать цветные иконки по правилу светофора:

● красный цвет – для значений меньше 10 %;

● желтый цвет – для значений от 10 до 20 %;

● зеленый цвет – для значений больше 20 %.

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

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

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

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

Типовые ошибки

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