Как построить дашборд в excel
Аналитика данных: как построить дашборд в Excel
В бизнесе сложно добиться системного роста, если регулярно не отслеживать ключевые показатели, которые влияют на прибыльность компании. Для этого лучше всего подходят дашборды, в которых данные представлены в понятном виде, что существенно облегчает принятие решений.
Пошагово рассмотрим, как построить дашборд по продажам в Excel. Статья будет полезна всем, кто начинает знакомство с этим мощным инструментом аналитики данных.
ОНЛАЙН-ШКОЛА ВИЗУАЛИЗАЦИИ ДАННЫХ EXCELLENT
Дашборд помогает решать задачи менеджерам по продажам, HR-специалистам, бухгалтерам, маркетологам, руководителям
Дашборд ― динамический отчёт, который состоит из структурированного набора данных и их визуализации на основе диаграмм, графиков и таблиц.
Основные задачи дашборда:
Построение дашбордов ― такой же hard skill, как владение формулами в Excel. По статистике, пользователь Excel среднего уровня может освоить этот навык за 20 часов обучения и практики.
Для специалистов, которые работают с отчётами, навык построения дашбордов стал необходимостью, а не дополнительным преимуществом.
Чаще всего созданием дашборда занимается аналитик — он обрабатывает огромные массивы данных, оформляет их в красивый и понятный дашборд и передаёт заказчику задачи. Это могут быть руководители, менеджеры по продажам, HR-специалисты, бухгалтеры, маркетологи.
Менеджерам по продажам дашборд помогает управлять продажами. HR-специалистам ― отслеживать основные метрики, связанные с трудовыми ресурсами. Для бухгалтера будет полезен дашборд о движении средств, который отражает финансовое состояние организации. Маркетологи анализируют рекламные кампании и оценивают их эффективность. Руководителю дашборд позволит быстро оценивать состояние ключевых показателей и принимать управленческие решения.
Существует большое количество сервисов для бизнес―аналитики, такие как Tableau, Power BI, Qlik, DataLens, Google Data Studio. Самым доступным можно назвать Excel.
Главное и самое интересное в дашборде ― интерактивность.
Настроить интерактивность можно с помощью следующих приёмов:
Создаём классический дашборд для руководителя отдела продаж
Для этого выбираем наиболее популярный способ с помощью сводных таблиц.
Советуем проделать все шаги вместе с нами. Как говорит гуру мотивации Наполеон Хилл, «мастерство приходит только с практикой и не может появиться лишь в ходе чтения инструкций». Файл с данными для тренировки можно скачать здесь.
Собираем данные
Построение любого дашборда начинается со сбора данных. На этом этапе важно привести таблицы в плоский вид, чтобы в дальнейшем на их основе создавать сводные таблицы для дашборда.
Плоская таблица (flat table) ― двумерный массив данных, состоящий из столбцов и строк. Столбцы ― это информационные атрибуты таблицы, строки ― отдельные записи, состоящие из множества атрибутов.
Пример плоской таблицы:
В примере выше атрибуты — это «Наименование», «День», «Год», «Склад», «Продажи (тыс. руб)», «Менеджер», «Заказчик». Они вынесены в заголовок таблицы.
Эта таблица послужит основой для построения нашего дашборда по продажам.
Выбираем макет дашборда и цели
Если известно, для чего и для кого предназначен дашборд, легче понять, какие показатели должны выводиться на экран. Это могут быть любые количественные показатели, важные для организации: прибыль, продажи, численность сотрудников, количество заявок, фонд оплаты труда.
Также необходимо определиться с макетом — структурой — дашборда. Для начала достаточно будет прикинуть её на листе формата А4.
Пример универсальной структуры, которая подойдёт под любые задачи:
Количество информационных блоков может быть разным: это зависит от того, сколько метрик надо отразить на дашборде. Главное — соблюдать выравнивание по сетке.
Порядок и симметрия в расположении информационных блоков помогают восприятию и внушают больше доверия.
Помимо симметрии важно учитывать и логику расположения информационных блоков. Это связано с нашим восприятием: мы привыкли читать слева направо, поэтому наиболее важные метрики необходимо располагать слева направо и сверху и вниз, менее важные ― справа внизу:
Построим несколько сводных таблиц по продажам
— на основе таблицы с данными, приведённой выше в качестве примера плоской таблицы.
Таблицы будут показывать продажи по месяцам, по товарам и по складу.
Пример как сделать простой дашборд в Excel скачать шаблон
Простой, но полезный дашборд в Excel для интерактивной визуализации данных отчета по показателям: уровень обслуживания, качество и производительность. С помощью этой управляемой панели отчета можно продемонстрировать, как визуализировать свой небольшой набор данных с помощью интерактивных элементов.
Создание дашбордов в Excel шаг за шагом
Так будет выглядеть готовый результат построения дашборда в Excel с возможностью интерактивного взаимодействия отчета с пользователем.
Чтобы создать свой такой же или подобный визуальный отчет в виде дашборда следует выполнить ряд последовательных действий в Excel.
В первую очередь создадим новую книгу с 3-ма листами:
Сначала создадим табличку с входящими данными на листе «Данные» так как показано ниже на рисунке:
После чего на листе «Дашборд» создадим первый управляющий элемент – выпадающий список. В данном случае рационально использовать поле со списком, так как оно имеет больше настроек. Конечно можно было бы воспользоваться стандартным выпадающим списком в Excel выбрав инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных»-«Тип данных: Список». Но мы так делать не будем, так как он неудобен из-за своей боковой полосы прокрутки, которая появляется уже при 10-ти значений. А у нас в выпадающем списке должны отображаться 12 месяцев. Поэтому выберите другой инструмент: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Поле со списком».
После выбора инструмента рисуем прямоугольник размером на полторы ячейки и получился выпадающий список.
Теперь нам необходимо его настроить. Щелкаем правой кнопкой мышки по выпадающему списку и из появившегося контекстного меню выбираем опцию: «Формат объекта». После чего появилось окно «Формат элементов управления», которое следует заполнить параметрами так как показано ниже на рисунке:
Как видно из параметров данный выпадающий список в данном примере настраивается 3-мя параметрами на вкладке «Элемент управления»:
Готовый желаемый результат выглядит так:
Далее начинаем упорно работать с 3-тим листом «Обработка». На данном листе обрабатываются и подготавливаются все данные для вывода на дашборд. Будем двигаться с верху вниз. Сначала подготовим данные для верхних подписей. Для этого создаем табличку выборки показателей при условии полученного номера месяца, переданного выпадающим списком на лист «Обработка» в ячейку A1. В ячейке A2 определяем название месяца на основе полученного числа в ячейке A1, по формуле:
Делаем выборку из входящей таблицы на листе «Данные» для всех показателей с помощью функции =ВПР() скопировав формулу во все остальные ячейки:
Данные для верхних подписей показателей – подготовлены!
Как сделать вафельный график в Excel
Подготавливаем значения для графического вывода информации на верхних вафельных графиках дашборда. Сначала делаем данные для вафельной диаграммы по показателю «Уровень обслуживания».
В первую очередь нам необходимо преобразовать процентное значение в числовое сохраняя самое значение числа перед знаком %. Естественно для этого нужно умножить его на 100, но для предотвращения ошибок и простоты отображения на графике мы еще и округлим значение данного показателя до целого числа:
Теперь в ячейку G1 вводим число 0, а целый диапазон ячеек G2:P11 заполняем формулой:
=$F$1;»»;1)’ > 
Диапазон G2:P11 состоит из 100 ячеек (10×10) и 100 единиц – соответственно. В каждой ячейке формула, которая проверяет количество единиц в диапазоне. Если оно больше или равно числу (процентов) в ячейке F1 значит следует прекратить заполнять данный диапазон единицами. Как видно, пока-что формула не работает, так как ей не хватает значений в диапазоне H1:P1, к которым она также обращается. В этом диапазоне будут вычисляться итоговые суммы чисел для подсчета количества единиц из предыдущих столбцов с помощью формулы, которую копируем во все ячейки диапазона H1:P1:
Теперь как видно все работает и диапазон ячеек G2:P11 заполняется единицами по условию, в зависимости от числового значения в ячейке F1.
Вафельный график будет состоять из двух слоев динамического (переднего плана – желтый цвет) и статического (задний план – черный цвет). Мы составили динамически изменяемые данные для первого желтого графика. Нам нужно еще создать черный статический график, который послужит задним фоном. Для этого понадобится диапазон размером 10×10 ячеек которые просто статически заполнены единицами. Поэтому рядом заполняем диапазон ячеек R2:AA11 единицами и строим по ним статический по такому же принципу, как и предыдущий – динамический.
Сначала создадим черный статический график. Для этого выполним ряд последовательных действий:
Черный график для фона готов! Теперь создадим динамический желтый, но сначала следует временно изменить значение 50% на 100% в таблице входящих данных (или временно вместо формулы ввести 100% в ячейку F1). Иначе не получится создать линейный график с накоплением для диапазона ячеек G2:P11.
Для создания динамической вафельной диаграммы выполняем все выше перечисленные действия, только цвет фигуры круга должен быть желтый.
ВНИМАНИЕ: Не забудьте обратно поменять значение 100% на 50%!
Так же для динамического желтого графика следует убрать заливку фона области. Для этого делаем двойной щелчок мышкой по фоновой области и вносим настройки: «Формат области диаграммы»-«ПАРАМЕТРЫ ДИАГРАММЫ»-«ЗАЛИВКА»-«Нет заливки».
Далее выделите два графика удерживая клавишу CTRL на клавиатуре и выберите инструмент: «РАБОТА С ДИАГРАММАМИ»-«Формат»-«Упорядочивание»-«Группировать», как показано выше на рисунке.
После чего наложите один на другой и переместите группу (вырезать, вставить) на главный лист «Дашборд»:
Для управления слоями наложения диаграмм используйте инструмент: «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Упорядочение»-«Область выделения», как показано выше на рисунке.
Динамический вафельный график в Excel – готов!
Аналогичным образом создаем еще два вафельных графика для показателей: «Показатель качества» и «Производительность».
Полезный совет. При создании остальных двух вафельных графиков можно не создавать фоновую черную диаграмму, а просто скопировать ее из первой уже созданной вафельной диаграммы.
Готовый шаблон дашборда в Excel
Ниже под вафельными диаграммами у нас на дашборде расположены 3 переключателя для нижнего графика (под ними). Чтобы создать переключатели на листе «Дашборд» выберите инструмент: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Переключатель». После щелкаем по нему правой кнопкой мышки и в контекстном меню выбираем опцию «Формат объекта»:
В появившемся диалоговом окне «Формат элемента управления» на вкладке «Элемент управления» в поле ввода «Связь с ячейкой:» указываем ссылку для вывода числовых значений в ячейку по адресу: Обработка!$A$6.
Копируем новый элемент управления – переключатель, 2 раза для создания его копий остальным показателям. Теперь при переключении переключателя на листе «Обработка» в ячейке A6 будут возвращаться числовые значения 1,2 и 3. В зависимости от выбранного пользователем переключателя.
Пришло время подготовить данные для нижнего динамически изменяемого графика при переключении. Но сначала на листе «Обработка» сделаем подготовку и обработку всех необходимых значений. Снова выполним ряд последовательных действий:
Данные подготовлены и обработанные!
Теперь выполним ряд действий для создания самого нижнего графика:
Можно еще сделать несколько шагов для оформления:
После всех оформлений переносим график на главный лист «Дашборд». Там же делаем все необходимые дополнительные оформления на свой вкус. После настраиваем расположение элементов и наслаждаемся готовым результатом:
В результате получился простой аналитический дашборд в Excel. Научившись делать такие простые дашборды для интерактивных и презентабельных отчетов с динамической визуализацией данных, постепенно можно переходить к более сложным.
Как сделать простой дашборд в Excel
Простой дашборд в Excel
14 апреля 2021 г. 1544
Введение
Дашборд – это умный отчет в режиме реального времени. Главное условие создания дашборда – наличие данных, которые можно анализировать. Благодаря такой инфопанели руководители и менеджеры имеют возможность отслеживать наиболее важные показатели в режиме реального времени и быстро реагировать. Конечно, не каждому бизнесу нужно отчитываться каждую секунду, в большинстве случаев это еженедельная или ежемесячная периодичность.
Для создания дашборда не нужно быть профессиональным аналитиком, достаточно иметь знания, которые позволят вам определять самые важные показатели из большого массива данных и отображать их на итоговой панели.
Золотые правила дашборда
Зачем нужен дашборд?
Чаще всего дашборды используются для визуализации бизнес-процессов, для отслеживания ключевых показателей эффективности, для сбора информации о состоянии веб-сайтов, для мониторинга производства. Список можно продолжать ещё, мы перечислили наиболее яркие примеры.
Данные есть в любой сфере деятельности. А если есть данные, то их можно и нужно проанализировать!
Интерактивный дашборд в Excel
В качестве примера мы решили рассмотреть достаточно актуальный, простой и общедоступный кейс – мониторинг продаж.
Для простейшего дашборда мы использовали стандартные виджеты – круговую диаграмму для категорий, карточки с показателями, столбчатую диаграмму для сравнения показателей, таблицу с рейтингом, а также задействовали фильтрацию данных, для возможности ежемесячного просмотра отчета.
В этом видео мы хотим показать вам, как легко визуализировать данные. Важно использовать правильные инструменты и следовать основным статистическим принципам
Рабочие файлы по ссылке ниже
Заключение
Тем, кому до сих пор сложно создавать подобные дашборды, мы рекомендуем собрать список всех имеющихся данных о вашем бизнесе, сформулировать свои пожелания и отправить их нам на mail@birdyx.ru. Мы составим полезные и наглядные сводки данных, которые изменят направление вашего бизнеса или жизнедеятельности к лучшему.
Как сделать анализ состава тела дома без умных весов
20 мая 2021 г. 1142 Info Health
В этой статье мы расскажем вам, как сделать анализ состава тела за 5 минут, не выходя из дома. ИМТ, процент жира, масса мышц и многое другое в одном отчете!
Поиск объединённых ячеек в Excel и Google таблицах
2 марта 2021 г. 2019 MS Excel Google Sheets Video
Не можете сделать фильтр или сортировку, потому что мешают объединённые ячейки? Мы расскажем, как быстро найти и разъединить все объединённые ячейки.
Простая CRM-система в Google таблицах
2 мая 2021 г. 1733 Google Sheets Code Video File
В этой статье мы рассмотрим что такое CRM-система и для чего ее используют, а также покажем, как создать простую CRM-систему в Google таблицах.
Автоматическое разделение таблицы на листы
14 марта 2021 г. 1151 MS Excel Google Sheets Video Code
Таблица уже настолько большая, что Вы решили разделить ее на листы по категориям? Вручную разделять это целая вечность. В статье мы расскажем, как сделать это за несколько минут.
Простой дашборд для визуализации данных в Excel без VBA-макросов
Вашему вниманию предлагается простой дашборд без использования VBA-макросов. По предпочтениям подписчиков на моей странице в фейсбук: https://www.facebook.com/excelvisualdata/ большинство предпочитают пользоваться дашбордами Excel без VBA-макросов и сторонних надстроек. В угоду большинству был создан данный шаблон интерактивного дашборда.
Как сделать интерактивный дашборд в Excel без VBA-макросов
Данный шаблон дашборда предназначен для выполнения визуального сравнительного анализа продаж по трем категориям товаров A, B, и C. Этот простой дашборд содержит все лишь только 4 блока. Для 3-х блоков применяется учетный период в одну неделю, а для четвертого блока – 1 месяц.
Описание диаграмм и графиков на простом дашборде Excel
Рассмотрим каждый блок по отдельности:
В левой части дашборда расположена шкала для визуализации выбранного текущего периода в году:
Управляется дашборд с помощью элемента управления «счетчик» или вводом числовых значений 1-53 в ячейку B22. При изменении значения в этой ячейке изменяются все показатели на дашборде соответственно. А также перемещается стрелка на шкале. Сама шкала разбита на 12 месяцев напротив каждого месяца указан номер недели с которой начинается этот же месяц. Таким образом номера на шкале служат подсказкой для быстрого перехода по месяцам с помощью ввода соответственного ему номера недели в ячейку B22.
В целом получился удобный шаблон для интерактивной визуализации Ваших данных на дашборде:
Исходные данные как всегда заполняются на листе «Data». Шаблон не содержит макросов, поэтому редактировать его сможет даже пользователь ниже среднего уровня освоения универсальной программой Excel.
Строим дашборд в Excel на базе Power Query и Power Pivot
С выходом интервью «Исповедь гения эффективности» и запуском продвижения в Сети в 2015 году, мы с партнёром Школы траблшутеров Даниилом Шмиттом озаботились планированием предстоящих активностей и фиксацией количественных результатов.
Уже шесть лет ведём Excel таблицу, в которую бережно заносим показатели вышедших статей, аудио и видео. К 30 ноября 2020 года скопилось 3’500 увидевших свет материалов, опубликованных на наших и внешних ресурсах. Подобное количество виделось недостижимым.
Обычай подбивать результаты оказался не только полезным: систематизировали публикации, построили тематический план, изучили показатели; но и дальновидным: десятки следов в Интернете пропали вместе со статистикой и откликами из-за закрытия каналов и сайтов.
На листе «Навыки» (Рис. 1, A) сделали тематическую классификацию и подбили вхождение в книги, «Покрытие» (B) отвели под публикации и статистику «реакций», вкладка «Сборники» © помогла распределить статьи по аудиоальбомам.
Выборочность распределения ссылок по листам заставила отмести функции ВПР и ИНДЕКС. В результате, ручным дублированием строк создали ненужную работу и наплодили оплошностей. Файл разросся настолько, что еженедельное обновление стало отнимать больше часа.
Трудоёмкость отслеживания многочисленных показателей раздражала. На подмогу пришли надстройки Power Pivot и Power Query, ставшие частью Excel с версии 2016 года.
Начали с нехитрого: спасались от задвоения и снижали долю ручных операций. С помощью «Антисоединения слева» в Power Query выявили полсотни накопленных огрехов: несоответствие наименований, различие ссылок, отсутствие материалов.
Восстановление пропущенного радовало: затыкались бреши незаконченных сборников, снижая плановый объём работ по «закрытию» тем. После чистки избавились от листа «Навыки», загрузив данные из PowerQuery в модель и создав сводную диаграмму на странице «Dashboard» (Рис. 2) 
Стало ненужным проставлять единички на пересечении столбцов «Навык» и строк «Статья». Функциональность возросла благодаря полю отбора и сортировки по наименованию и количеству (D). Появились дополнительные фильтры в «Поля сводной диаграммы» (Рис. 3, E): 
Лист «Покрытие» (Рис. 4) сделали кратким: добивались быстроты сортировок и фильтров — показатели обновляли макросами и прокручивали таблицу вниз для сверки достижения контрольных величин (F). Статистика была простенькой: без временных срезов, агрегированных значений и размера вклада переменных в результат. 
Добавили столбцы: «Навык», «Код», «Сборник» (Рис. 5). Учитывая, что удалённый лист «Навыки» хранил 524 колонки с названиями умений, массу формул и предполагал «сортировку» перетягиванием столбцов, обмен оказался выгодным. 
Тем более, что Power Query мгновенно подгружает таблицу с добавленными строками и дозаполненными ячейками: автоматически при открытии файла или через вкладку «Данные» — «Обновить всё». Попутно синхронизируются модель в Power Pivot и сводные диаграммы.
. и загрузили на лист «Dashboard» таблицу агрегированных значений (Рис. 7): 
При первом выводе данные выглядели неприглядно и малоинформативно: выбрали макет, задали стиль, маску чисел, условное форматирование. Добавили строки «KPI» и «Осталось», сделали сортировку, установили фильтр. Раз и навсегда: при обновлениях красота не слетает!
Для определения выхлопа на одну публикацию рассчитали средние в Power Query добавлением настраиваемого столбца (Рис. 8): 
Как и для суммарных значений вывели результаты и добавили форматирование (Рис. 9): 
Расчёты в Power Query завершили за 15 минут, хотя подобные операции с помощью встроенных функций заняли бы гораздо больше времени, а создание сводных таблиц привело бы к значительному увеличению размера файла.
Чтобы выявить перспективные издания, построили график интенсивности реакций и добавили линию тренда (Рис. 10). Выяснилось, что 11,5% Интернет-ресурсов в сумме дают 86,1% просмотров. В очередной раз убедились: пресловутое правило 80 на 20 не работает. 
Визуализацию информации выполнили иерархическими диаграммами совокупного интереса аудитории к Интернет-ресурсам и YouTube-передачам (Рис. 11): 
Больше всего интересовал канал «Олег Брагинский»: на нём выпускали максимальное число публикаций в неделю, проводили постоянные эксперименты. Для отслеживания изменений и тренда, без захода в творческую студию ресурса, в Power Pivot построили сопоставление Like for Like (LFL), используя меру с PARALLELPERIOD со сдвигом в месяц.
Так как функция работает с непрерывным набором дат, в Power Pivot создали таблицу «Календарь» и построили связь один ко многим (Рис. 12, G): 
Отняли из просмотров текущего месяца значения предыдущего и рассчитали процентную долю интервалов периода. Построили комбинированную диаграмму, где месячное изменение отобразили гистограммой с группировкой, а долю просмотров — графиком с трендом (Рис. 13): 
Поверх представления разместили фильтр «Временная шкала» (H), чтобы выбирать диапазон, не раскрывая лишних окон. Несмотря на «убыточные» месяцы, динамика оказалась положительной: после начала экспериментов наметился стабильный рост просмотров.
Вернулись к подсчёту аудио, видео, статей и их распределению по книгам и аудиосборникам: использовали построенную в Power Pivot модель на основе таблиц «Покрытие» и «Сборники». Создали меры (I) с нехитрой функцией COUNT (Рис. 14): 
. и вывели семь сводных диаграмм (Рис. 15): 
Собрали все представления на новом листе «Dashboard» (Рис. 16): 
Полное обновление по команде занимает полминуты, отдельные диаграммы/таблицы «освежаются» за пару секунд. Время добавления публикаций сократилось в 2,5 раза, снизилась вероятность ошибок, ключевые показатели удалось сгруппировать на один экран.
Добавилось пять моделей в Power Pivot, девять запросов в Power Query, 11 диаграмм, две вычисляемые таблицы, лист «Справочник», а файл увеличился лишь на 1,4 MB — приемлемая цена экономии и наглядности.
Ещё раз убедились, что Excel — инструмент «на все случаи жизни», а изучение надстроек — прикладной источник конкурентных преимуществ.