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

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

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

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

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

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

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

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

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

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

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

Существует множество сервисов бизнес-аналитики, таких как Tableau, Power BI, Qlik, DataLens, Google Data Studio. Самый доступный — Excel.

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

вы можете настроить интерактивность, используя следующие методы:

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

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

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

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

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

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

Плоская таблица — это двумерная матрица данных, состоящая из столбцов и строк. Столбцы — это информационные атрибуты таблицы, строки — это отдельные записи, состоящие из нескольких атрибутов.

Пример плоского стола:

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

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

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

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

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

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

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

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

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

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

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

В таблицах будут показаны продажи по месяцам, по продуктам и по складам.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

И мы ссылаемся на эту ячейку:

Мы делаем то же самое с другими значениями: выделите текстовое поле и обратитесь к короткому значению в сводной таблице через поле «Вставить функцию»:

Оживляем дашборд — вставляем срезы в сводных таблицах

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

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

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

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

Эти действия повторяем с каждым срезом.

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

— и разместите их слева в соответствии с выбранной структурой.

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

Итак, вот как выглядит наша панель для руководителя отдела продаж:

Не стоит бояться пробовать сложное

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

После небольшой практики панель управления может выглядеть так:

Не надо бояться неизвестного — просто нужно начать это делать, чтобы понять, что сложные вещи на самом деле не такие уж и трудные

Принцип «от простого к сложному» — самый правильный. Создавая интерактивную панель инструментов впервые, многие люди трепещут перед ней. Когда вы нажимаете на разделы, панель инструментов восстанавливается, как по волшебству. Желаем вам испытать и эти ощущения!