Интерактивный дашборд в Excel: что это такое и как создать

Дашборд — это небольшая панель, на которой собрана основная информация о работе компании, отделов или сотрудников. Дашборды бывают разных типов, данные на них могут отображаться в виде графиков и диаграмм. Для создания дашбордов используют специальные BI-инструменты вроде Google Data Studio и DataLense. Но визуализировать данные можно и в Microsoft Excel. У программы есть встроенные функции, которые позволяют агрегировать данные из таблиц и размещать их на дашбордах. Это упрощает работу с данными для сотрудников, которые не занимаются аналитикой профессионально.

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

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

С помощью дашборда в Excel можно анализировать разные данные в режиме реального времени. Например, можно отслеживать:

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

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

На курсе «Аналитик данных» студенты учатся работать с разными источниками данных, определять важные для бизнеса показатели и визуализировать их с помощью разных инструментов.

Вот основные плюсы работы с дашбордами Excel:

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

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

✅ Объединение данных.
Дашборд можно сделать на основе нескольких источников данных: импортировать данные из реляционных баз, таблиц Excel, CSV-файлов. Например, данные о покупателях собирают в одну таблицу, а данные о купленных товарах — в другую. Их можно объединить и отобразить на одной информационной панели.

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

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

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

Чтобы создать дашборд, нужны:

● Данные
Источниками могут быть: реляционная база данных, Excel-таблицы, CSV-файлы.

● Программное обеспечение
Нужно установить на компьютер пакет Microsoft Office, в котором есть Excel. Другой вариант — работать в Google Sheets. Этот инструмент бесплатно доступен всем пользователям с аккаунтом в Google, но у него меньше возможностей и другие формулы.

● Таблицы
В Excel нужно создать файл — рабочую книгу, а в Google Sheets — таблицу. Это основа для дашборда.

● Формулы и функции
Чтобы собрать данные на дашборд, часто нужно их агрегировать: объединить, выделить часть по определенному признаку или выполнить вычисления. Для этого нужно знать формулы Excel, например SUM, AVERAGE, COUNT, IF. Уметь работать со сводными таблицами и писать макросы на языке VBA (язык программирования в Excel).

Основные правила построения дашборда в Excel такие же, как и в других инструментах. Вот порядок действий, который сделает его понятным и легко читаемым:

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

2. Определить ключевые показатели для отслеживания.
Не стоит добавлять на дашборд слишком много информации. Суть инструмента в том, чтобы собрать на одном листе всё самое главное. Нужно выбрать данные, которые больше всего важны для решения рабочих задач. Например, маркетологам важно отслеживать эффективность рекламных кампаний через показатели ROAS, ROMI и CPL. И не пригодится знание скорости обработки запросов.

4. Добавить фильтры.
Чтобы пользователь мог взаимодействовать с дашбордом и настраивать его для своих задач, нужно предусмотреть возможность менять параметры отображения: добавить фильтры — автоматические или собственные. Например, чтобы можно было отфильтровать данные за определённый период.

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

Пошаговая инструкция для создания интерактивного дашборда в Excel выглядит так:

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

Шаг 2. Создание сводных таблиц.
Вот что нужно сделать для создания сводной таблицы:
1. Выделить данные и выбрать вкладку «Вставка».

2. Выбрать «Сводная таблица» в группе «Таблицы». Появится диалоговое окно «Создать сводную таблицу».
3. Убедиться, что диапазон данных указан правильно, и выбрать новое место размещения сводной таблицы — новый лист или существующий.
4. Выбрать нужные поля для строк, столбцов и значений в сводной таблице.
5. Настроить агрегацию данных, используя функции и формулы Excel, например суммирование или вычисление на основе заданного условия. И нажать «ОК».

Шаг 3. Добавление диаграмм и графиков.
1. Выделить данные для построения графика и выбрать вкладку «Вставка».
2. Выбрать вид графика в группе «Диаграммы».

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

Шаг 4. Добавление фильтров и кнопок.
Чтобы добавить фильтр для элемента дашборда, нужно:
1. Выделить область данных, к которой нужно добавить фильтр или кнопку.
2. Выбрать вкладку «Вставка» и в группе «Фильтры» выбрать нужный тип фильтра — автофильтр, расширенный фильтр. Появится стрелка фильтрации для выбранного столбца, с помощью которой пользователи смогут настраивать отображение информации по заданным значениям.

Чтобы добавить кнопку, нужно:
1. Выбрать вкладку «Вставка» и в группе «Формы» выбрать кнопку.
3. Добавить кнопку на лист Excel. После этого появится диалоговое окно «Назначение макроса».
4. Выбрать «Создать новый макрос» и ввести имя для макроса.
5. Нажать «ОК». После этого откроется режим записи макроса.
6. Выполнить нужные действия, например выделить все ячейки с заданным форматированием и залить их красным цветом.
7. Завершить запись макроса, нажав кнопку «Остановить запись» на панели инструментов или выбрав соответствующую команду в меню.

Шаг 5. Условное форматирование.
1. Выделить область данных, к которой нужно применить условное форматирование.
2. Выбрать вкладку «Условное форматирование» и выбрать нужные условия форматирования, например подсветку значений выше или ниже порогового значения.
3. Настроить параметры форматирования — цвета, шрифты и стили.
4. Применить условное форматирование. Теперь данные будут автоматически форматироваться в соответствии с заданными условиями.