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

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

Не удивляйтесь, что книга начинается с практики, а теория идет в конце. Так или иначе, но теорию все слышали и спотыкались на ее применении. Поэтому я проведу вас через сквозной кейс создания дашборда – от сырых данных до продвинутого дизайна. Для этого я дополнил книгу QR-кодами и ссылками на видеоуроки. В них работа описана еще подробнее: книга будет у вас под рукой как конспект с иллюстрациями.

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

Помните, не стоит рисовать диаграмму просто потому, что вы это умеете! Важно донести смысл данных и принять верное решение.

Удачного чтения и практики за компьютером!

Часть 1

Подготовка данных

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

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

1.1 Приводим в порядок исходные данные

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

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

Покажу еще не самый страшный пример – таблицу с данными по расходам на персонал.

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

● Правее в столбцах идут месяцы в объединенных ячейках, а под ними – группировка план/факт. То есть мы имеем двухуровневую шапку таблицы.

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

Решение: исходную матричную таблицу нужно перевести в так называемый плоский формат.

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

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

Как преобразовать данные в плоскую таблицу

Плоская таблица с исходными данными должна содержать:

● строку заголовков с названиями категорий;

● 5 столбцов по количеству категорий;

● неповторяющиеся данные в строках.

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

Перевести таблицу с группировкой в плоский формат можно несколькими способами – от ручного копирования данных в нужные ячейки до использования макросов или техник Power Pivot.

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

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

Шаг 1

Столбец А в исходной таблице содержит две категории данных – «Подразделение» и «Статья расхода». В плоской таблице они должны находиться в разных столбцах. Вот как их разделить:

Добавляем новый столбец слева от столбца А. Способ 1, самый простой: выделяем столбец А, вызываем контекстное меню правой кнопкой мыши, выбираем «Вставить». Способ 2: ставим курсор на любую ячейку в столбце А, в меню на вкладке «Главная» выбираем в разделе «Ячейки» кнопку «Вставить…» и в подменю кнопку «Вставить столбцы на лист».

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

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

Даем столбцам А и B правильные названия в строке над данными – «Подразделение» и «Статья расходов» соответственно. В этой же строке будем указывать заголовки остальных столбцов.

Шаг 2

Теперь из таблицы нужно убрать лишние данные.

Удаляем строки с суммарными значениями, то есть с общими итогами и промежуточными по подразделениям. В противном случае данные останутся суммированными несколько раз и мы получим некорректный результат.

Шаг 3

Добавляем и заполняем столбец с данными по месяцам.

Вставляем новый столбец слева от столбца С со статьями расходов.

Копируем название месяца в первую пустую ячейку нового столбца.

Выделяем эту ячейку и за правый нижний угол рамки протягиваем ее вниз – столбец автоматически заполнится месяцами по их порядку (то же самое будет с датой или последовательностью чисел);

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

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

Дальше надо будет переместить данные по