Dashboard в переводе с английского означает «приборная панель». В контексте работы с данными дашборд - это интерактивная информационная панель, заменяющая собой несколько таблиц или слайдов.
Интерактивность заключается в том, что такая панель имеет «органы управления», позволяющие быстро изменять выводимые на ней данные.
Такой способ представления данных помогает легче воспринимать и анализировать информацию, что значительно упрощает процесс принятия решений.
В этой заметке я расскажу, как вы можете быстро сделать интерактивный дашборд в Excel. Для этого не нужно знать ни язык программирования VBA, ни устанавливать какие-либо надстройки на Эксель.
Все что вам понадобится - это обычный Эксель и данные, которые нужно превратить в дашборд.
Итак, давайте приступим.
Что нужно для создания дашборда в Excel
Чаще всего дашборды используются для анализа каких-то финансовых показателей, но их можно применять к абсолютно любым данным. Главное понять алгоритм их создания.
Итак, у нас есть данные по продажам определенных товаров за определенный период времени, а именно за 2020-ый год (скачать файл).
Необходимо создать интерактивный отчет по продажам за этот год.
Стоит помнить, что дашборд - это всего лишь инструмент, помогающий принимать правильные решения на основе представленных на нем данных. По этой причине дашборд всегда должен давать ответы на конкретные вопросы. Эти вопросы должны быть сформулированы в самом начале и тогда можно будет спроектировать дашборд, четко соответствующий поставленным задачам.
Давайте сформулируем ряд вопросов.
- Какова была динамика продаж в течение года? Так как нам нужен отчет за год, то в нем должен быть общий график продаж по месяцам.
- Каковы были объемы продаж отдельных категорий товаров по месяцам, торговым сетям и городам?
- Составить рейтинг товаров, принесших наибольшую выручку.
Думаю, этих вопросов будет достаточно, чтобы ответить на них простым, но функциональным дашбордом.
В начале стоит прикинуть структуру дашборда на бумаге. Такой эскиз позволит спланировать всю работу.
Итак, я использую три диаграммы, каждая из которых будет отвечать на отдельный вопрос. Также я воспользуюсь тремя фильтрами - временной шкалой и срезами по городам и торговым сетям. Фильтры позволят сделать отчет интерактивным и быстро получать данные в соответствии с выбранными критериями.
Итак, план готов и переходим ко второму этапу - подготовка данных.
Подготовка данных для дашборда
У нас есть таблица с данными, но даже в такой структурированной форме проанализировать данные крайне сложно. Максимально просто и быстро привести данные к нужному виду позволяют сводные таблицы. Им было посвящено подробное видео, поэтом если вы не знакомы с этим инструментом, то обязательно его посмотрите.
Создадим сводную таблицу, которая будет отвечать на первый вопрос - какова динамика продаж в течение года?
Перетянем поле Дата в область Строк, а Итого в Значения.
Все готово!
Переименуем лист со сводной таблицей в «Динамика продаж».
Динамику продаж по месяцам лучше всего отразит график, поэтому построим на базе сводной таблицы сводную диаграмму и выберем тип «график с маркерами».
Чуть позже мы поработаем над внешним видом диаграммы, а пока ответим на второй вопрос - каковы были объемы продаж отдельных категорий товаров? При этом нас будут интересовать три фильтра - по месяцам, торговым сетям и городам.
Создадим еще одну сводную таблицу на новом листе. В область Строк поместим поле Категории товаров, а в Значения - Итого. Изменим название листа на «Категории товаров», а затем построим гистограмму.
Над внешним видом диаграммы поработаем позже, тогда же вставим необходимые фильтры.
Третья задача - составить рейтинг товаров.
Снова создаем сводную таблицу на новом листе и в область Строки поместим Товар, а в область Значения поле Итого. Переименуем лист в «Товары» и затем создадим линейчатую диаграмму.
В общих чертах все готово. Осталось создать дашборд и поработать над внешним видом диаграмм.
Компоновка дашборда
Сначала создадим новый лист, который так и назовем «Дашборд», а затем подкорректируем и перенесем на него ранее созданные диаграммы.
Во-первых, изменим название диаграмм. Я предпочитаю задавать все параметры диаграммы на листе - в ячейках листа или таблицы. Так проще в случае необходимости вносить изменения. Поэтому в ячейке над сводной таблицей введем заголовок (1) и затем свяжем ячейку с ним с названием диаграммы. Для этого выбираем на диаграмме ее название (2) и в строке формул вводим знак равенства. Далее просто указываем ячейку с заголовком (1).
На сводных диаграммах выводятся дополнительные кнопки (1), позволяющие производить фильтрацию и сортировку данных. Нам они не нужны, поэтому отключим их отображение через контекстное меню (2), вызванное на любой кнопке диаграммы. Можем убрать и легенду (3), так как она не информативна.
Теперь копируем диаграмму в буфер обмена (например, с помощью сочетания клавиш Ctrl + C) и вставляем ее на лист дашборда.
Аналогичные действия проделаем и с другими диаграммами.
Теперь, когда все три диаграммы расположены на одном листе, можем их предварительно скомпоновать, изменив размер и расположение. Кстати, если нажать и удерживать нажатой клавишу Alt, то при перемещении диаграмм они будут привязываться к линиям сетки листа. Это здорово помогает при позиционировании диаграмм.
Добавляем интерактивность
Осталось добавить фильтры и поработать над оформлением.
Выбираем диаграмму с категориями товаров (1) и на контекстной вкладке Анализ сводной диаграммы (2) вставляем два среза (3) - по Заказчику и Городу (4).
Разместим срезы в левой части экрана.
Так как заголовки срезов (1) не особо нужны, то можно их отключить через настройки среза, выбрав соответствующий пункт в контекстном меню (2).
Также через контекстную вкладку Срез (1) можем задать одинаковую ширину для срезов и выбрать оптимальную высоту для каждого из них (2). Здесь же задается высота и ширина отдельной кнопки в срезе (3), поэтому поработав с настройками можно добиться оптимального результата (4).
Если теперь проверить работу срезов, то выяснится, что фильтрация работает только на первой диаграмме. Это произошло потому, что мы выбрали именно ее при вставке срезов. Нам же нужно, чтобы и вторая диаграмма была интерактивной, поэтому выбираем из контекстного меню среза пункт Подключение к отчетам и укаызваем вторую диаграмму. Я не давал названия сводным таблицам, но так как они расположены на отдельных листах, то по названию листов легко догадаться что именно нужно включить.
Тоже самое проделываем и со вторым срезом, после чего фильтрация будет работать на двух диаграммах.
Аналогично вставим и настроим временную шкалу и свяжем ее с двумя диаграммами.
Затем уберем со шкалы все лишнее - для этого отключим все чек-боксы на панели инструментов Показать контекстной вкладки Временная шкла.
Теперь можем упорядочить данные на дашборде.
Для нас главным будет динамика продаж за год, поэтому основной график вынесем наверх. Ниже будут уточняющие данные, которые мы сможем выводить по определенным критериям. Срезы расположим слева, а временную шкалу внизу.
Каркас дашборда готов и осталось поработать над его внешним видом.
Форматирование дашборда
Теперь границы дашборда понятны, поэтому можем скрыть все ячейки листа, которые нам не нужны.
Выделяем столбец справа и нажимаем Ctrl + Shift + стрелка вправо. Это позволит выделить все столбцы листа, начиная с указанного. Затем скрываем столбцы через контекстное меню.
Аналогично поступим и со строками. В этом нам поможет сочетание клавиш Ctrl + Shift + стрелка вниз.
Затем объединим верхние ячейки над графиком и зальем их цветом из основной палитры. Вставим название дашборда и изменим его шрифт.
Откорректируем диаграммы. На первой диаграмме выведем подписи к точкам данных.
Расположим подписи точек данных сверху.
В подписях вертикальной оси и точек данных значения отображаются в миллионах и сами цифры плохо смотрятся на диаграмме. Откорректируем подписи оси, выбрав миллионы.
Значения изменятся и на оси, и над точками данных. Удалим подписи оси, так как они нам более не нужны, а для подписей точек данных измеим числовое форматирование, сохранив лишь два значка после запятой.
Ну а чтобы было понятно, что речь идет о миллионах, изменим подпись к диаграмме. Для этого переключимся на соответствующий лист и изменим заголовок сводной таблицы.
График в целом выглядит неплохо, но мне не нравится, что довольно большая его часть пустует, а горизонтальные линии сетки просто бесполезны.
Давайте решим эти задачи.
Во-первых, уберем сетку и контур диаграммы, а затем переключимся на сводную таблицу и продублируем столбец со значениями - просто еще раз перетянем поле Итого в область Значения.
Переключимся на дашборд и изменим тип диаграммы на комбинированный. Для второго ряда данных выберем диаграмму с областями.
Затем изменим заливку диаграммы с областями. Лучше использовать стандартную палитру и выбрать цвет из той же цветовой гаммы, что и у графика. Это важно, так как позволит нам в дальнейшем изменять всю цветовую схему дашборда в два щелчка мыши.
Аналогичные действия произведем и с двумя другими диаграммами - удалим сетку, уберем контур, вставим подписи точек данных, откорректируем их отображение (в миллионах) и изменим название диаграммы.
На вертикальной оси третьей диаграммы выводятся товары и их довольно много, поэтому Excel автоматически скрыл часть наименований, которые перекрывались. Чтобы включить отображение всех подписей в окне форматирования оси в разделе Подписи изменим интервал между подписями с Авто на 1.
На этой диаграмме должен выводится рейтинг товаров, поэтому нужно, чтобы они шли по порядку в зависимости от объема продаж. Перейдем на лист соответствующей сводной таблицы и щелкнем правой кнопкой мыши на столбце со значениями объемов продаж. Из контекстного меню выберем нужное направление сортировки.
Диаграмма перестроится автоматически.
Ну и осталось поработать со срезами.
Чтобы убрать их контур можно продублировать один из готовых стилей.
Затем отредактировать новый стиль, убрав контур.
Аналогично убирается контур и у временной шкалы.
Ну а теперь осталось на вкладке Вид отключить отображение строки формул, заголовков строк и столбцов, а также сетку листа.
Свернем ленту с инструментами, чтобы максимально расширить область дашборда и откорректируем его масштаб.
Все готово и замечательно работает.
Ну а для того чтобы изменить цветовую гамму дашборда, нужно зайти на вкладку Разметка страницы и выбрать понравившуюся тему из списка.
Ну а тема дашбордов довольно обширная и ей я посвятил отдельный большой видеокурс, который так и называется «Дашборды в Excel».
В курсе по шагам рассматривается процесс создания дашборда и подробно разбираются инструменты Excel, которые позволяют сделать дашборд максимально информативным и легко воспринимаемым.
Если тема дашбордов вам интересна, то обратите внимание на этот курс.
Нажмите на эту ссылку, чтобы перейти на страницу курса >>
_________________________________________________________________________________________
Ссылки на мои ресурсы по Excel
