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

Москва
2023

Алексей Колоколов

Заставьте
данные
говорить

Как сделать бизнес-дашборд в Excel

Руководство по визуализации данных

ИНСТИТУТ БИЗНЕС-АНАЛИТИКИ

УДК 334.722:004.6

ББК 65.29:16.35

 
К61

© Колоколов А., 2022
© Оформление. ООО «Альпина ПРО», 2023
ISBN 978-5-206-00079-5

Колоколов А.

 
Заставьте данные говорить : Как сделать бизнес-дашборд в Excel. Руководство 
по визуализации данных. —  М. : Альпина ПРО, 2023. — 244 с.

ISBN 978-5-206-00079-5

Данные должны приносить пользу бизнесу. А их грамотная подача — повышать профессиональную ценность специалистов. Книга «Заставьте данные говорить» написана, чтобы вы добились и того и другого.

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

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

Все права защищены. Никакая часть этой книги 
не может быть воспроизведена в какой бы то ни 
было форме и какими бы то ни было средствами, 
включая размещение в сети Интернет и в корпоративных сетях, а также запись в память ЭВМ 
для частного или публичного использования, без 
письменного разрешения владельца авторских 
прав. По вопросу организации доступа к электронной библиотеке издательства обращайтесь 
по адресу mylib@alpina.ru

К61

УДК 334.722:004.6

ББК 65.29:16.35

СОДЕРЖАНИЕ

Благодарности ....................................................................................................................................................7

Три истории, которые заставили меня написать эту книгу ...............................................................8

Часть 1 
Подготовка данных .................................................................................................................. 14

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

1.2 
Готовим основу для дашборда ............................................................................................. 23

1.3 
Делаем выборки данных для визуализаций ................................................................... 32

1.4 
Настраиваем интерактив .......................................................................................................40

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

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

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

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

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

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

3.2 
Настраиваем подписи данных ..............................................................................................73

3.3 
Работаем с текстом: убираем лишнее, добавляем нужное ...................................... 82

3.4 Оформляем столбчатые диаграммы ................................................................................... 91

3.5 
Настраиваем шаблон диаграммы .......................................................................................98

Часть 4 
Дизайн чистовика .................................................................................................................. 104

4.1 
Выравниваем заголовки по сетке ячеек .........................................................................105

4.2 Создаем новые карточки поверх ячеек ............................................................................110

4.3 Оформляем интерактивные срезы ....................................................................................122

4.4 Работаем с цветами и шрифтами Excel ............................................................................129

4.5 Дорабатываем стандартные темы Excel ..........................................................................139

Часть 5 
Фирменный стиль ...................................................................................................................148

5.1 
Создаем тему в соответствии с брендбуком ................................................................149

5.2 
Адаптируем тему по чек-листу ...........................................................................................156

5.3 
Создаем дашборд в темной теме.......................................................................................163

Часть 6 
Правила визуализации данных ........................................................................................178

6.1 
Виды анализа данных .............................................................................................................179

6.2 
Как выбирать диаграммы ......................................................................................................187

6.3 Лайфхаки для нескольких рядов данных ........................................................................198

6.4 Как показать все и сразу .......................................................................................................212

6.5 Воронка, водопад и волшебная «подушка» ...................................................................226

Как повысить корпоративную культуру работы с данными ..........................................................239

От автора

Меня зовут Алексей Колоколов, я занимаюсь проектами по автоматизации корпоративной 
отчетности с 2009 года. Моя компания называется «Институт бизнес-аналитики» и специализируется на двух направлениях: 1) внедрении систем бизнес-аналитики; 2) обучении работе 
с данными и визуализации.

За прошедшие годы мне удалось поработать с промышленными холдингами, металлургическими заводами, банками, государственными корпорациями и международными FMCG-компаниями. Их объединяет общая проблема: данных много, а понятной информации мало. 
С каждым годом появляются новые IT-системы, аналитические подразделения, но на стратегическом уровне компании не видят от этого пользы.

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

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

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

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

Для кого эта книга

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

Эта книга подойдет и тем и другим, но написана она так, чтобы все было понятно бизнес-пользователям даже без знаний технической части. Я изложил материал простым 
«человеческим» языком — так же как объясняю на своих тренингах и программах MBA 
в бизнес-школах. 

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

Как читать книгу

Первые 5 частей — о пошаговом построении дашборда на данных по расходам фонда оплаты 
труда. На самом деле он не такой сложный, чтобы исписать ради него более 200 страниц. 
Просто в этот пошаговый кейс я включил все популярные ошибки и сложности, с которыми 
сталкиваются при работе в Excel. И поделился лайфхаками, как решить или предотвратить 
эти проблемы в несколько кликов и тем самым сэкономить время и нервы.

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

Финальная часть книги — про правила визуализации. Здесь я  пытался не столько сказать что-то новое , сколько  разложить по полочкам ответ на вечный вопрос начинающих: 
как выбирать диаграммы? 

Для этого я взял в качестве примеров самые востребованные визуальные элементы и сопоставил их с видами анализа данных. Такие схемы называют чарт-чузерами (char-chooser), 
и их придумано много. Мне не нравится ни один — они довольно сложные и плохо запоминаются. Моим  изобретением в этой части книги стал лишь «компас визуализации» — надеюсь, 
он поможет выбрать верные диаграммы для решения вашей задачи.

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

Эта книга не про Excel

Сколько бы ни обсуждали искусственный интеллект, роботизацию и другие новейшие технологии, решения в бизнесе по-прежнему принимают люди. Часто это приходится делать 
в условиях недостатка информации, когда данных много, а полезных выводов — мало. И Excel 
как базовый для большинства инструмент стоит освоить как минимум для решения этой 
проблемы.

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

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

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

БЛАГОДАРНОСТИ

Книга вышла под моим авторством, но это результат работы команды сотрудников Института бизнес-аналитики.

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

Людмиле Габидуллиной — за технический текст и иллюстрации.

Наталье Никулиной — за редактуру и заботу обо всем проекте.

Анастасии Башкировой — за верстку первой электронной версии, с которой я так 
перемудрил.

Анне Зыкиной — за вдохновение и позитив.

Наталье Гринь — за перевод и адаптацию англоязычной версии.

Екатерине Крюковой — за решение финансовых вопросов. И за то, что следила за порядком 
в нашем творческом бардаке.

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

Спасибо вам.

ТРИ ИСТОРИИ, КОТОРЫЕ ЗАСТАВИЛИ МЕНЯ
НАПИСАТЬ ЭТУ КНИГУ

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

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

История 1 
Как Владимир Маркович из министерства делал 
сводный отчет

Владимир Маркович руководит департаментом развития в одном 
из министерств. Обычно он не составляет отчетов — это делают подчиненные. Но иногда за качество и подачу данных приходится отвечать и ему. 

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

Проблема: данные есть, общей картины нет

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

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

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

Задача Владимира Марковича заключалась в том, чтобы показать:

• какие проекты не выполняются, отстают от запланированных сроков;

• где проблема в финансировании, где меняются источники;

• кто из ответственных лиц на местах не справляется;

• по каким проектам согласован перенос сроков.

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

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

Как показать все и сразу?

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

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

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

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

Нельзя делегировать? Придется научиться работать с данными

Руководители и тем более топ-менеджеры не занимаются каждый день сбором и обработкой данных. Для них аналитика — это 
не профессия и не хобби, а периодическая необходимость. Особенно 

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

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

История 2 
Как аналитик Михаил принес директору 
«пульт управления самолетом» 

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

Полтора часа на объяснение отчета

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

— Вам шашечки или ехать? — злился Михаил. — Я три дня потратил 
на эти расчеты, даю вам полную выкладку, а вам картиночки нужны?!

— Миш, ну мы уже полтора часа сидим, — жалобно тянул коммерческий директор. — И так каждую неделю. Не должно быть такого, чтобы 
каждую таблицу приходилось так долго объяснять, понимаешь?

Расходились обычно каждый со своим мнением. Директор — о том, 
что Михаил зануда, а Миша — о том, что директор туповат.

«Сделай понятно и приходи завтра»

Последней каплей стал факторный анализ за прошедший квартал. 
Мишу упрекнуть вроде и не в чем — он работал днями и ночами, чтобы 
успеть в срок. Пришел на собрание с красными глазами, но успел.

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

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