Визуализация данных при помощи дашбордов и отчетов в Excel

Дик Куслейка

Визуализация данных  
при помощи дашбордов и отчетов 
в Excel

Data Visualization  
with Excel® Dashboards 
and Reports 

Dick Kusleika

Визуализация 
данных при помощи 
дашбордов и отчетов 
в Excel

Дик Куслейка

Москва, 2022

УДК 004.424
ББК 32.372
К94

Куслейка Д.
К94  Визуализация данных при помощи дашбордов и отчетов в Excel / пер. 

с англ. А. Ю. Гинько. – М.: ДМК Пресс, 2022. – 338 с.: ил. 

ISBN 978-5-97060-966-8

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

УДК 004.424
ББК 32.372

All rights reserved. This Translation publish under license with the original publisher John 
Wiley & Sons, lnc. 

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

ISBN 978-1-119-69872-2 (англ.) 
© John Wiley & Sons, Inc., 2021
ISBN 978-5-97060-966-8 (рус.)  
©  Перевод, оформление, издание,  

ДМК Пресс, 2022

Баттерсу

Содержание

От издательства .........................................................................................................11

Об авторе ....................................................................................................................12

О техническом редакторе .......................................................................................13

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

Введение .....................................................................................................................15

Часть I Отображение данных на дашборде ......................................................18

Глава 1 Основы дашбордов ..................................................................................19

Когда необходимо использовать дашборды ..........................................19
Что такое дашборд? ..............................................................................21
Ключевые показатели эффективности .............................................22
Пользовательские требования .................................................................22
Типы конечных пользователей ..........................................................23
Сбор данных ...............................................................................................24
Сводные таблицы..................................................................................24
Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ 
(GETPIVOTDATA) ..............................................................................28
Функции рабочего листа ......................................................................30
Функция ВПР (VLOOKUP) ...............................................................30
Функция ПРОСМОТРХ (XLOOKUP) ...............................................31
Функции ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) ...................32
Функция СУММПРОИЗВ (SUMPRODUCT) ....................................33
Формулы массива ............................................................................36
Умные таблицы .....................................................................................37
Структурированные ссылки на таблицы .....................................39
Текст по столбцам .................................................................................41
Удаление дубликатов ...........................................................................43
Построение дашбордов .............................................................................45
Организация элементов ......................................................................46
Разнообразие элементов .....................................................................47

Содержание 7

Отображение трендов ..........................................................................48
Форматирование дашбордов ...................................................................50
Форматирование чисел ........................................................................54

Глава 2 Практические примеры дашбордов .....................................................56
Отслеживание процесса ............................................................................56
Практический пример: отслеживание процесса написания ПО ....56
Планирование и составление макета ...........................................57
Сбор данных .....................................................................................59
Построение визуальных элементов .............................................60
Размещение элементов на дашборде ...........................................72
Отображение ключевых показателей эффективности ........................73
Практический пример: отдел по работе с персоналом ..................73
Планирование и составление макета ...........................................73
Сбор данных .....................................................................................74
Построение визуальных элементов .............................................75
Размещение элементов на дашборде ...........................................86
Отображение финансовых показателей .................................................89
Практический пример: финансовая информация и показатели ...89
Планирование и составление макета ...........................................90
Сбор данных .....................................................................................90
Построение визуальных элементов .............................................92
Размещение элементов на дашборде .........................................101

Глава 3 Организация данных для дашбордов ................................................104
Разделение слоев данных .......................................................................104
Слой источника ...................................................................................106
Слой подготовки и анализа ...............................................................108
Слой представления ...........................................................................109
Работа с внешними данными ................................................................109
Power Query против Power Pivot .......................................................109
Текстовые файлы ................................................................................110
Файлы Excel .........................................................................................115
Базы данных Access ............................................................................123
Базы данных SQL Server .....................................................................129
Преобразование данных при помощи Power Query ...........................132
Управление столбцами и строками .................................................135
Преобразование столбцов .................................................................138
Преобразование типов данных ...................................................138
Преобразование чисел ..................................................................140
Разделение столбцов ....................................................................141

Часть II Основы визуализации данных .............................................................145

Глава 4 Основы эффективной визуализации ..................................................146
Создание эффективной визуализации .................................................146
Держитесь в рамках одного экрана .................................................146

Содержание

Повысьте привлекательность дашборда .........................................148
Расскажите свою историю быстро ...................................................148
История должна быть согласована с данными ...............................152
Выберите правильный тип диаграммы ..........................................152
Управление цветом ..................................................................................154
Как использовать цвет .......................................................................154
Изменение цвета точек данных с изменением значений ......155
Использование контраста для выделения данных ..................155
Группирование данных при помощи цвета ..............................156
Советы по использованию цвета .....................................................157
Используйте пустое пространство ..............................................157
Используйте простую цветовую палитру ..................................159
Используйте цвета, согласующиеся с данными .......................159
Используйте контраст ..................................................................159
Используйте неинформационные пиксели  
при необходимости .......................................................................160
Равнение на текст ....................................................................................160
Шрифты ................................................................................................160
Легенды ................................................................................................161
Оси ........................................................................................................163
Подписи данных .................................................................................163
Демонстрация аналитических выводов (инсайтов) на графиках ....164
Сравнение ............................................................................................165
Анализ состава ....................................................................................166
Взаимосвязи ........................................................................................168

Глава 5 Неграфические визуализации ..............................................................170
Основы пользовательских числовых форматов ..................................170
Четыре секции форматирования .....................................................170
Специальные символы ......................................................................172
Заместитель цифры ......................................................................172
Запятые и точки .............................................................................172
Текст ................................................................................................173
Символ подчеркивания ................................................................174
Звездочка ........................................................................................174
Экранирование специальных символов ....................................175
Финансовый формат чисел ..........................................................175
Форматы даты и времени ..................................................................177
Условные пользовательские числовые форматы ...........................178
Использование значков ..........................................................................179
Цветовые шкалы .................................................................................179
Гистограммы .......................................................................................184
Наборы значков ..................................................................................187
Создание спарклайнов ............................................................................189
Типы спарклайнов ..............................................................................189
Создание спарклайнов .......................................................................190
Группы спарклайнов ..........................................................................191

Содержание 9

Настройка спарклайнов .....................................................................192
Изменение источника данных ....................................................192
Изменение цвета и толщины линий спарклайнов...................193
Настройка осей ..............................................................................195

Глава 6 Использование фигур для создания инфографики .........................197
Работа с фигурами ...................................................................................197
Вставка фигур ......................................................................................197
Настройка фигур .................................................................................200
Встраивание данных в фигуры ..............................................................202
Создание баннера ...............................................................................203
Создание закладки .............................................................................206
Работа с несколькими фигурами ......................................................208
Создание простых графиков с применением фигур .....................210
Создание пользовательской инфографики ..........................................212
Добавление других иллюстраций ..........................................................214

Часть III Рассказываем историю с помощью визуализации ..........................219

Глава 7 Визуализация сравнения показателей эффективности ..................220
Отдельные показатели ............................................................................220
Гистограмма ..............................................................................................222
Практический пример: продажи по кварталам .............................225
Пулевая диаграмма ..................................................................................226
Практический пример: расходы в сравнении с планом ...............227
Гистограмма с группировкой .................................................................231
Практический пример: брак на производстве ...............................232
Воронка ......................................................................................................233
Практический пример: конверсия продаж .....................................234
Точечная диаграмма ................................................................................236
Практический пример: температура и продажи ...........................238
Пузырьковая диаграмма .........................................................................241
Практический пример: ипотека .......................................................241
Точечная гистограмма.............................................................................244
Практический пример: объемы производства ..............................244

Глава 8 Визуализация частей от целого ............................................................255
Круговая диаграмма ................................................................................255
Кольцевая диаграмма ..............................................................................257
Практический пример: продажи по регионам ..............................258
Вафельная диаграмма .............................................................................260
Практический пример: привилегии для сотрудников ..................261
Диаграмма солнечные лучи ...................................................................265
Практический пример: анализ времени производственного 
процесса ...............................................................................................267
Гистограммы .............................................................................................268
Практический пример: средний чек в ресторане ..........................271

Содержание

Диаграмма дерево ....................................................................................272
Практический пример: виды страхования .....................................274
Каскадная диаграмма ..............................................................................275
Практический пример: чистая прибыль .........................................277

Глава 9 Визуализация изменений с течением времени ................................281
Линейный график ....................................................................................281
Практический пример: продажи товаров по категориям ............284
Столбчатая диаграмма с отклонениями ...............................................289
Практический пример: продажи домов по месяцам ....................289
Комбинированная диаграмма ...............................................................297
Практический пример: выручка от грузовых перевозок  
и мили ..................................................................................................298
Линейный график с отклонениями.......................................................300
Практический пример: отслеживание выручки по кварталам ....301
Смежные ящики с усами .........................................................................305
Практический пример: зарплаты по отделам ................................307
Анимированная диаграмма ...................................................................309
Сводные диаграммы ..........................................................................309
Промежуточные данные с формулами ............................................313
Анимация диаграммы при помощи макроса .................................316
Автоматизация диаграмм ......................................................................319
Манипулирование объектами диаграмм ........................................319
Создание панельных диаграмм ........................................................324

Предметный указатель ..........................................................................................334

От издательства

Отзывы и пожелания

Мы всегда рады отзывам наших читателей. Расскажите нам, что вы ду маете 
об этой книге – что понравилось или, может быть, не понравилось. Отзывы 
важны для нас, чтобы выпускать книги, которые будут для вас максимально 
полезны.
Вы можете написать отзыв на нашем сайте www.dmkpress.com, зайдя на 
страницу книги и оставив комментарий в разделе «Отзывы и рецензии». 
Также можно послать письмо главному редактору по адресу dmkpress@gmail.
com; при этом укажите название книги в теме письма. 
Если вы являетесь экспертом в какой-либо области и заинтересованы в написании 
новой книги, заполните форму на нашем сайте по адресу http://
dmkpress.com/authors/publish_book/ или напишите в издательство по адресу 
dmkpress@gmail.com.

Список опечаток

Хотя мы приняли все возможные меры для того, чтобы обеспечить высокое 
качество наших текстов, ошибки все равно случаются. Если вы найдете 
ошибку в одной из наших книг, мы будем очень благодарны, если вы сообщите 
о ней главному редактору по адресу dmkpress@gmail.com. Сделав это, 
вы избавите других читателей от недопонимания и поможете нам улучшить 
последующие издания этой книги. 

Нарушение авторских прав

Пиратство в интернете по-прежнему остается насущной проблемой. Издательства «
ДМК Пресс» и Wiley очень серьезно относятся к вопросам защиты авторских 
прав и лицензирования. Если вы столкнетесь в интернете с незаконной 
публикацией какой-либо из наших книг, пожалуйста, пришлите нам ссылку на 
интернет-ресурс, чтобы мы могли применить санкции.
Ссылку на подозрительные материалы можно прислать по адресу элект-

ронной почты dmkpress@gmail.com.
Мы высоко ценим любую помощь по защите наших авторов, благодаря 
которой мы можем предоставлять вам качественные материалы.

Об авторе

Дик Куслейка (Dick Kusleika) работает с пакетом Microsoft Office на протяжении 
более 20 лет. В течение 12 лет подряд удостаивался статуса Microsoft 
MVP. Перу Дика принадлежит несколько книг по Excel и Access.

О техническом редакторе

Даг Холланд (Doug Holland) – инженер-программист и разработчик архитектуры 
в Microsoft Corporation, обладающий степенью магистра Оксфордского 
университета в области программной инженерии. Перед тем как присоединиться 
к компании Microsoft, Даг получил статус Microsoft MVP и Intel Black 
Belt Developer.

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

Особую благодарность хотел бы выразить Келли Тальбо (Kelly Talbot) за поддержку 
в написании книги и дополнительную мотивацию. Также хочу сказать 
огромное спасибо Питу Гоэну (Pete Gaughan) за потраченное время на 
начальном этапе проекта – это мне очень помогло.
Отдельная признательность Джуди Флинн (Judy Flynn) и Дагу Холланду 
(Doug Holland) за помощь в поиске ошибок – вы помогли сделать эту книгу 
лучше. Мне было очень приятно работать в команде с такими крепкими профессионалами.
— 
Дик Куслейка

Введение

В настоящее время бизнес хранит и обрабатывает огромные массивы данных – 
больше, чем когда бы то ни было. И это касается не только крупных 
компаний. Мелкие и средние предприятия сегодня также обрабатывают беспрецедентное 
количество информации. Руководство компаний использует 
собранные данные для принятия важных решений, но сложность состоит 
в том, что с такими объемами информации в исходном виде им просто не 
справиться. В связи с этим основной задачей бизнес-аналитики (Business 
Intelligence – BI) является преобразование сырых необработанных данных 
в вид, пригодный для анализа.
Сама бизнес-аналитика появилась не вчера, но в последнее время значительно 
повысилось количество и качество инструментов в этой области. 
А это вкупе с возросшей доступностью данных привело к тому, что сегодня, 
похоже, только ленивый не строит дашборды.
Excel постепенно становится флагманом в мире бизнес-аналитики, если 
еще не стал. Компания Microsoft в последние годы вложила немало ресурсов 
в развитие инструментов бизнес-аналитики, встроенных в Excel и существующих 
отдельно. За это время появилось целое семейство продуктов Power, 
включающее в себя Power Query, PowerPivot и Power BI, а также значительно 
обогатился выбор типов встроенных диаграмм, который еще пару версий 
назад был далеко не таким разнообразным.
Постепенно некогда исключительно специализированные инструменты 
вошли в состав Excel и стали доступны всем и каждому. В прошлом вам понадобился 
бы целый серьезный проект для сбора данных, а без специальных 
инструментов о создании дашбордов не могло быть и речи. Сейчас же все эти 
средства наверняка уже установлены на вашем компьютере и просто ждут 
своего часа. И основой, объединяющей в себе все эти полезные инструменты, 
является именно Excel – программа, с которой вы работаете на ежедневной 
основе вне зависимости от масштаба вашего бизнеса.
Возможно, вы сами хотите начать строить дашборды, но считаете, что у вас 
для этого недостаточно знаний и умений. Или руководство компании поставило 
вам такую задачу. В этой книге мы пройдем поэтапно по всем средствам 
Excel, связанным с визуализацией данных, – от фигур через условное 
форматирование и до полноценных диаграмм. Попутно мы будем разбирать