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

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

Получаем уже нечто похожее на дашборд. На «Черновике» при этом остаются только выборки на сводной таблице.

Резюме

Начинайте работу над дашбордом с создания эскиза и макета. Используйте для этого любой визуальный редактор или сделайте набросок от руки.

1. Определите, какие визуализации нужны на дашборде.

2. Расположите их на макете по принципу «от общего – к частному».

3. Убедитесь, что самые важные данные не потеряются на фоне остальных.

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

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

Черновик и чистовик: урок на YouTube

https://rebrand.ly/clean-copy

2.2 Создаем карточки KPI

В верхней части дашборда мы оставили место для итоговых значений. Здесь не нужно строить диаграммы – достаточно крупно отобразить число и подпись к нему. Такой объект я называю карточкой KPI. Нам нужно создать 3 такие карточки:

● план;

● факт;

● исполнение.

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

Выборка данных для карточек

Для тиражирования копируем любую выборку на листе «Черновик», вставляем чуть ниже и на панели «Поля сводной таблицы» оставляем галочки только возле названий полей «План» и «Факт». Также можно убрать лишние поля из блока «Строки».

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

Карточка «План»

На листе «Чистовик» приступаем к созданию первой карточки KPI. Поставив курсор в ячейку, добавляем ссылку на общий показатель плана на листе «Черновик». Для этого вводим =черновик! А43, где А43 – адрес ячейки на листе с выборками. Получаем необходимое значение уже в «Чистовике».

Если ссылка выглядит не так – отключите режим GETPIVOTDATA (ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ)

Изначально ссылка на ячейку может выглядеть следующим образом:

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ («Сумма по полю План»; Черновик!$A$42). В таком случае нужно отключить функцию GETPIVOTDATA.

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

Находясь в любой ячейке выборки, зайдите на вкладку «Анализ сводной таблицы» → «Сводная таблица» → «Параметры» → уберите галочку «Создать GetPivotData».

Так вы будете получать ссылки на ячейки сводной таблицы не с помощью GETPIVOTDATA, а привычным способом – в виде ссылок на ячейки A1, B2 или C4.

Обратите внимание! Если ссылки на сводную таблицу были созданы до отключения режима, то они остаются в первоначальном виде и после отключения.

Если число в карточке состоит из большого количества знаков, измените его разрядность, например переведите в тысячи или миллионы. Так показатель станет более компактным и простым для восприятия.

В ячейке над показателем пишем его название и указываем разрядность: «План, тыс. руб.». Исходные данные в ячейке разделим на 1000. Останется только задать вид числа. Делаем это на вкладке «Главная» в разделе «Число»:

клик по иконке «Формат с разделителями» (выглядит как «000») добавит пробел между разрядами числа;

клик по иконке рядом «Уменьшить разрядность» (000 со стрелкой вправо) уберет числа после запятой.

НЕ СТРОЙТЕ ГРАФИК РАДИ ОДНОГО ЧИСЛА!

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

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

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

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

выбрать в разделе «Числовые форматы» вид «Числовой»;

убедиться, что число десятичных знаков равно нулю;

удостовериться, что в поле «Разделитель групп разрядов ()» стоит галочка;

нажать «ОК».

Это все – карточка с плановым показателем готова.

Карточки «Факт» и «Исполнение»: создание и настройка

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

Меняем название новой карточки на «Факт, тыс. руб.». Не страшно, если данные не поместились и отобразились в ячейке как «#######». Чтобы увидеть фактическое значение, просто растяните ширину столбца. Останется только привести полученные данные к нужной разрядности.

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

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