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

Срезы – основа интерактивности дашборда. Они работают как кнопки, и клик по каждой обновляет все данные на экране. Удобно, понятно, просто для анализа.

1. Добавьте и настройте базовую выборку на листе со сводной таблицей: отметьте в панели справа нужные для выборки поля.

2. Отключите режим автонастройки ширины столбцов – иначе придется задавать ширину столбцов каждый раз после использования среза.

3. Добавьте срез из выборки («Вставка» → «Срез») и в открывшейся панели настроек отметьте галочками нужные поля.

4. Чтобы деактивировать некоторые категории на срезе, выделите его, зажмите Ctrl и кликните мышкой по ненужным позициям.

5. Тиражируйте настроенную выборку и добавляйте заготовки визуальных элементов.

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

Как настроить интерактивные срезы: урок на YouTube

https://rebrand.ly/slices

Умная таблица

Вот что произойдет, если преобразовать обычный список в таблицу (CTRL+T):

● шапка таблицы автоматически закрепится;

● включится автофильтр;

● размеры таблицы подстроятся под данные;

● все формулы будут автоматически копироваться на весь столбец.

Заполнение пустых ячеек

Чтобы заполнить пустоты значениями из расположенных выше ячеек:

● выделите весь диапазон;

● F5 → Выделить (Special) → Только пустые ячейки (Blanks);

● нажмите на знак «=», потом на стрелку вверх, чтобы создать ссылку на предыдущую ячейку;

● введите полученную формулу во все ячейки, нажав CTRL+Enter.

Быстрое копирование

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

Быстрое добавление новых данных в диаграмму

Просто скопируйте диапазон ячеек и вставьте его в диаграмму правой кнопкой мыши или с помощью сочетания клавиш CTRL+C и CTRL+V.

Часть 2
Сборка дашборда

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

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

2.1 Собираем по макету

Начать нужно с создания эскиза, то есть наброска будущего дашборда. Для этого достаточно иметь любой графический редактор, программу Microsoft PowerPoint или просто лист бумаги – эскиз можно схематически нарисовать от руки. На основе этого документа будем создавать макет.

Структуру нашего дашборда можно представить следующим образом.

● Название дашборда

● Карточки KPI

● Рабочая область

● Панель со срезами слева

Эскиз нужно превратить в полноценный макет, который и станет нашей отправной точкой. Здесь будет уже больше точности – макет должен содержать все визуализации, срезы и вспомогательные элементы.

В средней части (под карточками KPI) мы объединили два блока в один, чтобы отследить динамику расходов за более длительный срок.

Теперь у нас есть макет и заготовки визуальных элементов. Собираем их так, чтобы структура соответствовала созданному макету.

Делаем это на отдельном листе нашего файла Excel – назовем его «Чистовик».

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

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

Вырезаем всю группу срезов.

Переходим на лист «Чистовик» и вставляем вырезанное. Размещаем срезы слева согласно макету, оставив сверху 5 строк для названия дашборда и карточек KPI.

Затем из «Черновика» переносим на «Чистовик» подготовленные диаграммы – так же как делали это со срезами. Размещаем диаграммы на листе в соответствии с макетом:

● сверху по всей ширине экрана – график динамики;

● слева внизу – гистограмма по подразделениям;

● справа внизу – линейчатая диаграмма по статьям расходов.

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

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

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

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: проследите, чтобы это осталось в формуле.

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

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

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

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

Не переборщите с количеством карточек KPI. Если заказчик просит вывести в них 7–10 показателей, объясните, что это сделает числа сложными для восприятия и приведет к перегрузу дашборда. Я рекомендую добавлять не более 6 карточек.

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

1. На листе со сводными таблицами сформировали выборку с общими показателями плана и факта.

2. На листе с заготовками диаграмм («Чистовик») сформировали первую карточку: сослались на ячейку с показателем плана на другом листе, настроили разрядность и дали карточке понятное название.

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

4. С помощью тиражирования уже созданной карточки создали две новые – «Факт» и «Исполнение». Повторять все шаги настройки уже не придется, но нужно изменить ссылку в строке формул для показателя карточки «Исполнение».

Оформляем карточки итоговых показателей: урок на YouTube

https://rebrand.ly/kpi-cards

2.3 Выравниваем дашборд и добавляем заголовок

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

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

То же самое делаем и с остальными визуальными элементами. Если необходимо переместить блок с диаграммой ровно по горизонтали или вертикали, выделяем его и, зажав клавишу Shift, передвигаем мышкой.

Удаляем лишнюю строчку между карточками и визуальными элементами. Теперь добавим на лист название дашборда.

Над карточками KPI нам нужны 3 пустые строки: добавьте, если их не хватает.

Во второй пустой строке, в ячейке над первой карточкой (у нас это ячейка D2), напишите название дашборда – «Анализ фонда оплаты труда».

Задайте размер шрифта заголовка в 20 пунктов – так же как у числовых показателей на карточках.

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

Вот как выглядит наш дашборд после всех операций по сборке.

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

Вот что мы сделали для этого.

1. Изменили размер диаграмм, чтобы уместить все на один экран.

2. Выровняли расположение визуальных элементов.

3. Добавили заголовок дашборда в пустые строки над карточками.

4. Убрали с листа Excel линии сетки.

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

Выравнивание, заголовок, отключение сетки: урок на YouTube

https://rebrand.ly/alignment

Часть 3
Анатомия диаграмм

Довольно быстро мы построили дашборд. И выглядит он как интерактивное аналитическое приложение, а не просто набор диаграмм и фильтров, как это обычно получается в Excel. Многим кажется, что уже «нормально», но мы с вами только в начале пути.

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

Эти правила актуальны для любого инструмента, будь то Excel, PowerPoint, Power BI, Tableau или новая российская BI-система. Названия элементов диаграммы в них могут отличаться, но принципы настройки одинаковы. То, что вы научитесь делать в обычном Excel, затем сможете грамотно применять в продвинутых программах.

3.1 Анализируем готовые стили оформления

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

Чтобы посмотреть предложенные варианты, выделите любую диаграмму и откройте появившуюся вкладку «Конструктор» в меню. Список стандартных шаблонов можно увидеть в разделе «Стили диаграмм» и в выпадающем меню под кнопкой «Экспресс-макет».

Как выбрать экспресс-макет

Для нашего графика «Динамика расходов» Excel предлагает 12 вариантов шаблонов. Отличаются они наличием или отсутствием некоторых элементов, а также их разным расположением.

Из этих вариантов нам более или менее подходит «Макет 2», потому что на нем есть необходимый набор данных и элементов:

● название диаграммы;

● правильно расположенная легенда (над диаграммой);

● метки данных;

● только горизонтальная ось (вертикальная не нужна, потому что есть метки данных);

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

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

Мы не будем использовать этот вариант, а проведем тонкую настройку диаграммы самостоятельно, чтобы вы понимали, как устроен этот процесс. Но сначала посмотрим, какие шаблоны предлагает Excel в разделе «Стили диаграмм».

Чем не подходят шаблонные стили

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

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

Шаблон с акцентом на данных. В качестве первого примера рассмотрим вариант стиля с яркими крупными шариками.

Шарики привлекают внимание к данным, и это делает график оригинальным. Но у шаблона все равно есть недостатки.

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

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

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

Шаблон стиля на темном фоне привлекает внимание своими «неоновыми», будто светящимися столбцами. Но для практического применения в нашем случае он не годится.

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

Шаблон с вертикальными метками. Среди предложенных стилей есть и варианты без цветного фона. Рассмотрим один из них.

Этот стиль выглядит неплохо, но и у него слишком много недостатков для использования на дашборде.

Вертикальные метки данных – это жуть. Чтобы прочитать их, пользователю придется наклонять голову, а это точно не помогает комфортному восприятию отчета.

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

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

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

Шаблон с градиентом на столбцах. Для полноты картины рассмотрим и вариант стандартного стиля для линейчатой диаграммы.

Первым в глаза бросается название диаграммы. Не буду критиковать заглавные буквы – они могли бы быть уместными. Но здесь это самый заметный элемент, и он отвлекает внимание от данных.

Для расстановки акцентов есть три инструмента:

● размер шрифта;

● выделение жирным шрифтом;

● заглавные буквы.

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

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

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

Какой должна быть деловая графика: рекомендованные образцы

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

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

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

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