Заставьте данные говорить. Как сделать бизнес-дашборд в Excel. Руководство по визуализации данных - Алексей Сергеевич Колоколов
5. Отключили стандартные названия внутри диаграмм.
6. Скопировали стиль для ячеек справа от заголовков.
7. Отключили контуры диаграмм и расширили области построения.
Выравнивайте заголовки по левому краю, а не по центру, чтобы избежать пустых мест на дашборде. Так вы сможете поднять легенду выше, но при этом избежать нагромождения элементов.
4.2 Создаем новые карточки поверх ячеек
Заголовки диаграмм у нас сейчас находятся в ячейках, хотя внешне это и незаметно. Это облегчает выравнивание, увеличивает рабочую область диаграмм, но при этом имеет последствия.
Расстояние между нижними диаграммами равно ширине ячейки – это довольно много. Чтобы сократить его, придется уменьшить ширину столбца J. Но это скажется на карточке с фактическим показателем: значение перестанет помещаться и будет отображаться как несколько символов #.
Казалось бы, можно объединить ячейки. Но такие проблемы будут происходить всегда, если карточки живут в ячейках.
Например, я хочу добавить еще одну карточку со среднемесячной зарплатой. Тогда придется сжать и сдвинуть имеющиеся карточки. А это повлияет на выравнивание диаграмм.
В процессе создания дашборда и его согласования с заказчиком карточки и диаграммы могут добавляться и убираться несколько раз. Чтобы постоянно не возиться с их выравниванием, мы освободим карточки от привязки к сетке ячеек. Сделаем их по-новому в виде самостоятельных элементов.
Как добавить карточку с помощью фигуры и надписи
В современных BI-системах есть виджеты для карточек. Добавляешь показатель – и на экране появляется стилизованный прямоугольник с заголовком и итоговым значением. В Excel такого готового объекта нет, и мы будем собирать его из подручных средств – прямоугольников и надписей.
Это может показаться смешным, но такой лайфхак выручает не только в Excel. Особенно когда у дашборда нестандартный макет и под одним заголовком нужно сгруппировать несколько показателей. Так что давайте разберем этот процесс по шагам.
Шаг 1
На вкладке ленты меню «Вставка» в разделе «Иллюстрации» нажимаем на кнопку «Фигуры» → в выпадающем меню выбираем фигуру «Прямоугольник». Если у вас большой экран, то на ленте сразу будет отображаться кнопка «Фигуры».
Шаг 2
Располагаем прямоугольник поверх имеющейся первой карточки KPI. Ему можно задать любой размер, потянув за край фигуры, – это не отразится на ширине столбцов таблицы.
Шаг 3
Выделив прямоугольник, наберем название карточки – «План, тыс. руб.». Зададим размер шрифта (12) и выравнивание по центру.
Шаг 4
Кликнув по любой пустой ячейке, снимем выделение с прямоугольника. Теперь на вкладке «Вставка» в разделе «Текст» нажмите кнопку «Надпись», а появившийся элемент разместите на прямоугольнике. Именно в этот элемент будем добавлять наш показатель.
Чтобы добавить в карточку показатель, нам потребуется создать новую ячейку в выборке сводной таблицы.
Итоги для карточек в отдельных ячейках
Чтобы показатели на карточках отображались в удобном формате и не содержали большого количества знаков, на черновой версии дашборда мы прописывали формулы. Настроить этот формат нужно и здесь, только с учетом нюанса.
В строке формул для элементов типа «Прямоугольник» и «Надпись» формулу указать нельзя. При попытке сделать это мы увидим сообщение об ошибке.
Чтобы добавить числовой показатель в новый тип карточки, нам нужно создать ячейки с формулами под выборкой «Ключевые показатели» на листе «Черновик».
Шаг 1
Выделяем пустую ячейку под первым столбцом выборки и в строке формул добавляем: =А43/1000, где А43 – адрес ячейки с показателем плана, в котором нужно уменьшить число знаков.
Шаг 2
Задаем новой ячейке форматирование, как делали это раньше. То есть уменьшаем разрядность, чтобы оставить только целое число, без знаков после запятой.
Шаг 3
На листе с новой версией дашборда выделяем добавленный на карточку элемент «Надпись» и в строке формул указываем ссылку на новую ячейку с нужным показателем. А также настраиваем размер шрифта, цвет и положение показателя – по середине и по центру.
Таким же образом заранее подготовьте ячейки с формулами для двух других карточек:
● для показателя «Факт» задайте формулу, а затем уменьшите разрядность, убрав знаки после запятой;
● для карточки «Исполнение» пропишите в формуле деление фактического показателя на плановый. На вкладке «Главная» в разделе «Число» приведите результат к процентному формату.
Шаг 4
Выравниваем элементы карточки относительно друг друга. Зажмите клавишу Shift и выделите мышкой элемент «Надпись», а потом элемент «Прямоугольник». На вкладке меню «Формат фигуры» выберите «Выровнять» → «Выровнять по центру».
Шаг 5
Объединяем элементы карточки. Выделите сначала «Надпись», а потом «Прямоугольник». И используйте один из способов группировки.
1. На вкладке «Формат фигуры» выбираем «Выровнять» → «Привязать к фигуре».
2. В контекстном меню выбираем «Группировать» → «Группировать».
После выполнения всех шагов получаем сгруппированный элемент «Карточка» с плановым показателем.
Тиражирование карточки
На нашем дашборде должно быть 3 карточки с ключевыми показателями: «План», «Факт» и «Исполнение». Чтобы избежать настройки каждой с нуля, воспользуемся уже знакомым лайфхаком с тиражированием.
Для этого я выделяю карточку «План», зажимаю на клавиатуре клавишу Ctrl и передвигаю мышкой копию элемента на нужное место. Эту копию мы превратим в карточку с фактическим показателем.
Новой карточке задаем название «Факт, тыс. руб.» и меняем ссылку на фактический расчетный показатель на листе «Черновик».
В этот момент шрифт отформатированной надписи снова превращается в черный по умолчанию. Может показаться, что вы сделали что-то не так. Не удивляйтесь, это очередной баг Excel. Выдохните и снова настройте шрифт вручную: увеличьте и сделайте его белым.
Как исправить стиль подложки
Главное на карточках KPI – показатели. Именно на них взгляд должен падать в первую очередь. Но у нас этот акцент перетянула на себя яркая и тяжелая заливка. Чтобы сделать сами показатели заметными и контрастными, нам нужно изменить стиль элемента.
Не гонитесь
