Как сделать дашборд в excel

Пример как сделать простой дашборд в Excel скачать шаблон

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

Создание дашбордов в Excel шаг за шагом

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

Чтобы создать свой такой же или подобный визуальный отчет в виде дашборда следует выполнить ряд последовательных действий в Excel.

В первую очередь создадим новую книгу с 3-ма листами:

Сначала создадим табличку с входящими данными на листе «Данные» так как показано ниже на рисунке:

После чего на листе «Дашборд» создадим первый управляющий элемент – выпадающий список. В данном случае рационально использовать поле со списком, так как оно имеет больше настроек. Конечно можно было бы воспользоваться стандартным выпадающим списком в Excel выбрав инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных»-«Тип данных: Список». Но мы так делать не будем, так как он неудобен из-за своей боковой полосы прокрутки, которая появляется уже при 10-ти значений. А у нас в выпадающем списке должны отображаться 12 месяцев. Поэтому выберите другой инструмент: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Поле со списком».

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

Теперь нам необходимо его настроить. Щелкаем правой кнопкой мышки по выпадающему списку и из появившегося контекстного меню выбираем опцию: «Формат объекта». После чего появилось окно «Формат элементов управления», которое следует заполнить параметрами так как показано ниже на рисунке:

Как видно из параметров данный выпадающий список в данном примере настраивается 3-мя параметрами на вкладке «Элемент управления»:

1. Список отображает значения из диапазона первого столбца ячеек таблицы входящих данных ссылаясь в первом поле «Формировать список по диапазону:» по адресу Данные!$A$2:$A$13.
2. Второе поле «Связь с ячейкой:» позволяет указать ячейку куда будут возвращаться порядковые номера значений выпадающего списка. В данном случае они передаются в ячейку по адресу Обработка!$A$1. Например, если будет выбрано значение из нашего списка – «Март» тогда в ячейку A1 на листе «Обработка» передается число 3 для дальнейшей обработки.
3. «Количество строк списка:» — числовой параметр позволяет нам отображать выпадающий список без полосы прокрутки. Указав число 12, мы увеличили его размер на 12 записей, чего нельзя сделать с обычным выпадающим списком из проверки данных.

Готовый желаемый результат выглядит так:

Далее начинаем упорно работать с 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 единицами и строим по ним статический по такому же принципу, как и предыдущий – динамический.

Сначала создадим черный статический график. Для этого выполним ряд последовательных действий:

1. Выделите диапазон ячеек R2:AA11 и выберите инструмент: «ВСТАВКА»-«Диаграммы»-«Линейчатая»-«Линейчатая с накоплением»
2. Делаем двойной щелчок мышкой по оси X, чтобы изменить настройки: «Формат оси»-«ПАРАМЕТРЫ ОСИ»-«Границы»-«Максимум» – с 12 на 10.
3. После чего удаляем саму ось X, затем ось Y, название, легенду, сетку – поочередно выделяя их и нажимая клавишу Delete на клавиатуре:
4. Делаем двойной щелчок по любому ряду данных графика и делаем настройку: «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«Боковой зазор» – 5%.
5. Рядом возле графика создаем фигуру в виде черного круга. Выбреете инструмент: «ВСТАВКА»-«Иллюстрации»-«Фигуры»-«Овал». Удерживая зажатой клавишу SHIFT на клавиатуре нарисуйте круг.
6. Получился синий круг поэтому меняем цвет на черный. Для этого сделайте активной фигуру круг щелкнув по ней левой кнопкой мышки и выберите инструмент из дополнительного меню: «ФОРМАТ»-«Стили фигур»-«Черная заливка»:
7. Скопируйте черную фигуру круга нажав комбинацию клавиш CTRL+C, затем выделите один из рядов на диаграмме и вставьте ее нажав клавиши CTRL+V на клавиатуре.
8. Измените размеры сторон диаграммы сделав их равными – 5 на 5 см. Щелкните по графику сделав его активным и вызвав его дополнительное меню: «РАБОТА С ДИАГРАММАМИ»-«Формат»-«Размер»:

Черный график для фона готов! Теперь создадим динамический желтый, но сначала следует временно изменить значение 50% на 100% в таблице входящих данных (или временно вместо формулы ввести 100% в ячейку F1). Иначе не получится создать линейный график с накоплением для диапазона ячеек G2:P11.

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

ВНИМАНИЕ: Не забудьте обратно поменять значение 100% на 50%!

Так же для динамического желтого графика следует убрать заливку фона области. Для этого делаем двойной щелчок мышкой по фоновой области и вносим настройки: «Формат области диаграммы»-«ПАРАМЕТРЫ ДИАГРАММЫ»-«ЗАЛИВКА»-«Нет заливки».

Далее выделите два графика удерживая клавишу CTRL на клавиатуре и выберите инструмент: «РАБОТА С ДИАГРАММАМИ»-«Формат»-«Упорядочивание»-«Группировать», как показано выше на рисунке.

После чего наложите один на другой и переместите группу (вырезать, вставить) на главный лист «Дашборд»:

Для управления слоями наложения диаграмм используйте инструмент: «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Упорядочение»-«Область выделения», как показано выше на рисунке.

Динамический вафельный график в Excel – готов!

Аналогичным образом создаем еще два вафельных графика для показателей: «Показатель качества» и «Производительность».

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

Готовый шаблон дашборда в Excel

Ниже под вафельными диаграммами у нас на дашборде расположены 3 переключателя для нижнего графика (под ними). Чтобы создать переключатели на листе «Дашборд» выберите инструмент: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Переключатель». После щелкаем по нему правой кнопкой мышки и в контекстном меню выбираем опцию «Формат объекта»:

В появившемся диалоговом окне «Формат элемента управления» на вкладке «Элемент управления» в поле ввода «Связь с ячейкой:» указываем ссылку для вывода числовых значений в ячейку по адресу: Обработка!$A$6.

Копируем новый элемент управления – переключатель, 2 раза для создания его копий остальным показателям. Теперь при переключении переключателя на листе «Обработка» в ячейке A6 будут возвращаться числовые значения 1,2 и 3. В зависимости от выбранного пользователем переключателя.

Пришло время подготовить данные для нижнего динамически изменяемого графика при переключении. Но сначала на листе «Обработка» сделаем подготовку и обработку всех необходимых значений. Снова выполним ряд последовательных действий:

1. На листе «Обработка» заполняем диапазон ячеек A7:A18 внешними ссылками на ячейки из другого листа «Данные» в диапазоне A2:A13, чтобы получить список полных названий месяцев их таблицы входящих данных:
2. В следующем столбце таблички для динамического нижнего графика будут находиться сокращенные названия этих же месяцев для комфортного отображения их на оси X. Используем функцию обрезки текста =ЛЕВСИМВ() с указанным параметром 3 символа, которые нужно оставить с начала строки:
3. Для динамического изменения названия графика создадим формулу выборки наименования показателя по условию:
4. В третьем столбце мы будем делать выборку данных по условию из таблицы входящих данных. Условие заключается в следующем если в ячейке A6 возвращено число 1 тогда с помощью функции ВПР будут выбраны значения для показателя «Уровень обслуживания» по столбцу 1, если 2 – «Показатель качества» по столбцу 2 и если 3 – «Производительность». Реализуется данная задача с помощью формулы:

Данные подготовлены и обработанные!

Теперь выполним ряд действий для создания самого нижнего графика:

1. Выделите диапазон ячеек B7:C18 и выберите инструмент: «ВСТАВКА»-«Диаграммы»-«График с маркерами»:
2. Из дополнительного меню выберите стиль его оформления: «РАБОТА С ДИАГРАММАМИ»-«Стили диаграмм»-«Стиль 2»:
3. Сделайте двойной щелчок левой клавише мышки по линии графика чтобы в окне «Формат ряда данных» изменить цвет линий и маркеров:
4. Чтобы автоматически изменялось название графика следует щелкнуть по самому названию сделав его активным, а после в строке формул следует ввести знак равно (=) и кликнуть по ячейке (в данном примере C6) из которой следует брать значение наименования, а затем нажать клавишу Enter:

Можно еще сделать несколько шагов для оформления:

• изменить цвет фона – двойной клик по фону и в окне «Формат области диаграммы»-«ПАРАМЕТРЫ ДИАГРАММЫ»-«ЗАЛИВКА»-«Цвет» выбрать – белый;
• добавить вертикальную ось Y значений – «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Макеты диаграмм»-«Добавить элемент диаграммы»-«Оси»-«Основная вертикальная».

После всех оформлений переносим график на главный лист «Дашборд». Там же делаем все необходимые дополнительные оформления на свой вкус. После настраиваем расположение элементов и наслаждаемся готовым результатом:

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Всё о дашбордах: как они устроены, как компании их разрабатывают и как сделать их самому

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

Фото: Bloomberg / Getty Images

Дарья Чепурнова

Обозреватель Skillbox Media, отраслевой журналист. Работала с TexTerra, SMMplanner, «Нетологией», «ПланФактом», Semantica. Написала больше 60 текстов для рекламных кампаний в «Дзене». Вела нишевой канал на YouTube.

О дашбордах рассказал

Иван Успенский

Ведущий аналитик департамента аналитических решений в ГК «КОРУС Консалтинг».

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

В этом материале Skillbox Media рассказываем:

• что такое дашборд и зачем он нужен;
• как работает дашборд;
• кто и как создаёт дашборды;
• как сделать дашборд самому.

Что такое дашборд и зачем он нужен

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

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

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

• Табло в лифте показывает этаж, направление движения, не превышен ли допустимый вес.
• Приборная панель в автомобиле показывает время, скорость, расстояние, которое вы проехали, количество топлива в баке.
• Электронные часы показывают дату, время, день недели.

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

В бизнесе дашборды появились не так давно. Раньше работал подход Highest Paid Person’s Opinion — принятие решений на основе мнения руководителя, которое часто было субъективным. В противовес ему сформулировали подход Data Driven Decision Making (DDDM) — при нём решения принимают на основе данных. Чтобы использовать этот подход, понадобилось собирать данные и представлять их в какой-то форме. Так появилось множество отчётов.

Чтобы сделать работу с данными удобнее, специалисты модифицировали отчёты — например, добавляли в них визуализации данных. Так появились дашборды. Сейчас они есть во многих системах, связанных с обработкой данных. Например, готовые панели есть в сервисах веб-аналитики и CRM — системах управления отношениями с клиентами. Однако дашборд можно сделать и самому, и многие компании разрабатывают нужные им информационные панели под свои задачи.

Как работает дашборд

Простой пример работы дашборда: водитель включил ближний свет → электроника зафиксировала это → на приборной панели загорелась лампочка.

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

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

Допустим, оперативное совещание руководства завода проходит ежедневно в 9 часов утра. К этому времени информация о том, что станок в производственном цехе сломался, уже должна быть на дашборде. На нём отобразится, например, новое число инцидентов на предприятии и пересчитанный прогноз объёма выпуска продукции.

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

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

Кто и как создаёт дашборды

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

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

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

Второй шаг — определить, где брать данные. На этом этапе вместе с бизнес-аналитиком начинает работать системный аналитик. Они определяют:

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

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

Вместе с архитектором или параллельно с ним над дашбордом может работать UX/UI-дизайнер. Он проектирует сценарии использования и элементы управления, определяет внешний вид дашборда. Эти задачи также может решать бизнес-аналитик.

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

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

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

Как сделать дашборд самому

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

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

Дашборд для бизнеса создают в BI-системах — программах для сбора, обработки, агрегации, визуализации и анализа данных компании. К ним относятся, например, системы Qlik, Microsoft Power BI и Luxms BI.

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

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

Что ещё нужно знать о дашбордах

У тех, кто впервые встретил это понятие, могли остаться вопросы. На некоторые из них отвечала старая статья Skillbox Media о дашбордах, которую написала редактор Мария Ираидина. Мы решили показать вам её ответы на распространённые вопросы.

В каких отраслях бизнеса используют дашборды? Во всех. Например, в интернет-маркетинге, PR-кампаниях и рекламе с их помощью отслеживают эффективность рекламных кампаний. В менеджменте — контролируют нагрузку и планируют работу команды. В HR-аналитике — анализируют информацию по зарплате сотрудников, KPI, а также данные по отпускам и стажировкам.

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

Чтобы создать дашборд для бизнеса, нужно быть аналитиком? Это не обязательно, потому что программы всё делают автоматически. Но желательно, чтобы у вас были аналитические навыки — ведь придётся обработать много информации и вычленить самое важное.

Есть ли какие-то правила создания дашбордов? Жёстких правил нет. Но важно так подать информацию, чтобы её было легко прочитать и использовать. Поэтому можно следовать стандартным правилам визуализации данных. Например, специалист по информационному дизайну Эдвард Тафти считает, что главное в визуализации данных — увеличить объём отображения полезной информации и исключить ненужные графические элементы.

Другие материалы Skillbox Media, которые могут быть вам полезны

• Нотация BPMN — что это такое и как её используют в бизнес-анализе
• Power Pivot в Excel: как выгрузить данные из внешних источников и собрать их в одну базу
• Продуктовый аналитик: чем занимается, сколько зарабатывает и как им стать
• Деловая коммуникация: большой гайд, с которого можно начать изучать тему
• Логические функции в Excel: для чего нужны и как использовать — примеры со скриншотами

Построение дашбордов в Excel: как вывести отчеты на новый уровень

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

Главные тезисы вебинара

• По какому принципу строить дашборды. Во-первых, действуйте по принципу «от общего к частному». Верхний уровень дашборда — самые важные показатели, KPI и сводка. Средний уровень — расшифровка KPI и аналитика данных. Нижний уровень — детализация и справочная информация. Во-вторых, не отходите от модульной сетки. Графики, таблицы и остальное нужно выравнивать, чтобы не создавать визуальный хаос. В-третьих, правильно расставляйте акценты. Покажите важность информации размером — сделайте ее крупнее. Выделяйте нужные элементы цветом, чтобы привлечь к ним внимание.
• Как располагать элементы на дашборде. Тепловые карты показывают, что человек зрительно лучше воспринимает элементы, когда они расположены по направлению чтения, то есть слева направо и сверху вниз. Смысловые блоки желательно располагать друг под другом, это сделает дашборд аккуратным. Важно найти правильное место для фильтров. Представьте, что вы зашли в онлайн-магазин и хотите отсортировать товары. Где вы будете искать нужные кнопки? Скорее всего, сверху или слева. В дашбордах та же логика.

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

Подписчики РБК Pro могут пройти онлайн-курс школы Excellent «Интерактивные дашборды в Excel» со скидкой 10%. Скидка действует на любой пакет курса бессрочно. Воспользуйтесь промокодом RBC22.

Полезные материалы

• Готовый дашборд, построенный на мастер-классе — скачать
• Файл для отработки — скачать
• Памятка «Структура дашборда» — скачать

Аналитика данных: как построить дашборд в Excel

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

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

ОНЛАЙН-ШКОЛА ВИЗУАЛИЗАЦИИ ДАННЫХ EXCELLENT

Дашборд помогает решать задачи менеджерам по продажам, HR-специалистам, бухгалтерам, маркетологам, руководителям

Дашборд ― динамический отчёт, который состоит из структурированного набора данных и их визуализации на основе диаграмм, графиков и таблиц.

Основные задачи дашборда:

• представить набор данных максимально наглядным и понятным;
• держать под контролем ключевые бизнес―показатели;
• находить взаимосвязи, выявлять негативные и положительные тенденции, находить слабые места в организации рабочих процессов;
• давать оперативную сводку в режиме реального времени.

Построение дашбордов ― такой же hard skill, как владение формулами в Excel. По статистике, пользователь Excel среднего уровня может освоить этот навык за 20 часов обучения и практики.

Для специалистов, которые работают с отчётами, навык построения дашбордов стал необходимостью, а не дополнительным преимуществом.

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

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

Существует большое количество сервисов для бизнес―аналитики, такие как Tableau, Power BI, Qlik, DataLens, Google Data Studio. Самым доступным можно назвать Excel.

Главное и самое интересное в дашборде ― интерактивность.

Настроить интерактивность можно с помощью следующих приёмов:

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

Создаём классический дашборд для руководителя отдела продаж

Для этого выбираем наиболее популярный способ с помощью сводных таблиц.

Советуем проделать все шаги вместе с нами. Как говорит гуру мотивации Наполеон Хилл, «мастерство приходит только с практикой и не может появиться лишь в ходе чтения инструкций». Файл с данными для тренировки можно скачать здесь.

Собираем данные

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

Плоская таблица (flat table) ― двумерный массив данных, состоящий из столбцов и строк. Столбцы ― это информационные атрибуты таблицы, строки ― отдельные записи, состоящие из множества атрибутов.

Пример плоской таблицы:

В примере выше атрибуты — это «Наименование», «День», «Год», «Склад», «Продажи (тыс. руб)», «Менеджер», «Заказчик». Они вынесены в заголовок таблицы.

Эта таблица послужит основой для построения нашего дашборда по продажам.

Выбираем макет дашборда и цели

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

Также необходимо определиться с макетом — структурой — дашборда. Для начала достаточно будет прикинуть её на листе формата А4.

Пример универсальной структуры, которая подойдёт под любые задачи:

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

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

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

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

— на основе таблицы с данными, приведённой выше в качестве примера плоской таблицы.

Таблицы будут показывать продажи по месяцам, по товарам и по складу.

Должно получиться вот так:

Также построим таблицу для ключевых показателей «Продажи», «Средний чек», «Количество продаж»:

Чтобы в дальнейшем было проще ориентироваться при подключении срезов, присвоим сводным таблицам понятное имя. Для этого перейдём на ленте в раздел Анализ сводной таблицы → Сводные таблицы → в поле Имя укажем название таблицы.

Создадим диаграммы на основе сводных таблиц

В нашем дашборде будем использовать три типа диаграмм:

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

Выделим диапазон таблицы, перейдём на ленте в раздел Вставка → Диаграммы → Вставка диаграммы → Выберем нужный тип диаграммы → ОК:

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

Переместим построенные диаграммы на отдельный лист

… и распределим их согласно выбранному на втором шаге макету:

Добавим ключевые показатели (KPI)

После размещения диаграмм необходимо вставить поля с ключевыми показателями: перейдём на ленте в раздел Вставка ⟶ Фигуры и вставим 3 текстбокса:

Далее сделаем заливку и подпишем каждый блок: