Как создать дашборд в Эксель: инструкция

Широко известная программа Excel, поставляемая в составе офисного пакета Microsoft Office, –многоцелевой инструмент, используемый профессионалами по всему миру при анализе и визуализации информации. Хотя её функционал может показаться громоздким, Вы удивитесь, насколько легко построить эффективную панель мониторинга (дашборд) с помощью этого приложения.

Дашборд в Эксель

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

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

Как это помогает бизнесу

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

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

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

Как построить дашборд в Excel

При построении панели мониторинга в MS Excel применяется универсальный алгоритм, который включает всего 6 этапов, выполняемых последовательно.

Шаг 1. Определите цель и задачи

Прежде чем приступить к проектированию панели мониторинга, установите чёткие цели. Просто ответьте на вопрос: какую информацию Вы хотите сообщить?

Сделайте это в 3 шага:

1. Осознайте бизнес-контекст. Рассмотрите бизнес в целом, конкретный процесс или проблему, которую нужно решить. Сопоставьте стратегию, цели и ключевые показатели, затем постарайтесь их согласовать между собой.
2. Определите потребности потенциальных пользователей. Поймите, какие вопросы ставят перед собой потенциальные пользователи панели.
3. Выявите наиболее важные показатели. Они должны предоставлять исчерпывающие ответы на поставленные вопросы.

Как только Вы поймёте общее состояние бизнеса, цели и способы их достижения, можете приступать к непосредственному проектированию информационной панели.

Шаг 2. Подготовьте данные

В первую очередь подготовьте отчёт и приведите его в надлежащий вид:

1. Создайте отчёт в Excel и оформите его в виде плоской таблицы (flat table) – 2-мерного массива, состоящего из набора строк, столбцов и содержащего все необходимые сведения в одном месте.
   
   
2. Убедитесь, что в каждом столбце данные приведены в единый формат. В противном случае при создании сводных таблиц и расчётах могут появляться ошибки.

Шаг 3. Спроектируйте макет

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

• основные показатели KPI (объём продаж, количество сделок, средний чек);
• график, отображающий динамику продаж;
• графики сравнения объёма продаж по менеджерам и городам.

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

Макет панели мониторинга продаж может выглядеть так:

Шаг 4. Постройте сводные таблицы

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

1. Откройте заранее подготовленную таблицу и на основной панели инструментов выберите «Вставка» → «Сводная таблица» → «Сводная таблица».
   
   
   
2. В диалоговом окне укажите диапазон данных (первую и последнюю ячейки таблицы), отметьте пункт «На новый лист» и нажмите кнопку «OK». Новый лист будет создан автоматически; присвойте ему название «Сводные таблицы» (или на своё усмотрение).
   
   
   
3. На созданном листе создайте сводную таблицу, сведения из которой впоследствии будут использованы при формировании карточек KPI. Чтобы это сделать, выберите в списке полей сводной таблицы (окно настроек расположено справа) необходимые атрибуты: в нашем примере это «Номер заказа» и «Сумма заказа».
   
   
   

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

Шаг 5. Постройте диаграммы

По завершении подготовки и сведения данных приступите к созданию основных графиков согласно ранее (на шаге 3) намеченному плану:

1. Выделите диапазон данных второй таблицы (для графика динамики продаж). Перейдите в раздел «Вставка» → «Диаграммы» → «Гистограмма» и выберите тип «Гистограмма с группировкой». График будет создан автоматически.
   
   
   
2. Отредактируйте диаграмму. По своему усмотрению скорректируйте размеры, заголовок, формат отображения данных (по осям) и пр.
   
   
3. По аналогии постройте остальные диаграммы.
   
   

Шаг 6. Осуществите сборку информационной панели

Теперь приступите к финальной сборке дашборда:

1. Создайте новый лист и присвойте ему название «Дашборд».
2. Вернитесь в «Сводные таблицы», выделите и скопируйте ранее созданные графики, затем вставьте их на лист «Дашборд». Скорректируйте расположение блоков в соответствии с макетом.
   
   
3. Вставьте блоки для карточек KPI. Чтобы это сделать, на панели инструментов перейдите в раздел «Вставка» → «Фигуры» и выберите «Надпись».
   
   
   
4. Создайте 3 одинаковых блока и симметрично разместите их в верхней части макета. Отформатируйте: сделайте заливку и укажите заголовки.
   
   
5. Внутри каждой карточки создайте и отцентрируйте новый текстовый блок. Внутри них планируется отобразить показатели KPI.
   
   
6. Перед заполнением первой карточки отобразите сумму сделок в миллионах; так она будет восприниматься лучше. Перейдите на лист «Сводные таблицы», активируйте ячейку рядом со значением параметра и введите формулу.
   
   
7. Вернитесь на лист «Дашборд» и заполните первую карточку. Активируйте внутренний текстовый блок и введите значение с помощью формулы вида:
   =‘Название листа’!ячейка
   
   
8. По аналогии заполните все карточки и отформатируйте значения: отцентрируйте, выберите желаемый тип и размер шрифта, а также цвет фона.
   
   
9. Скорректируйте структуру, если это необходимо.

Частые ошибки и пути их решения

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

Альтернативы Excel

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

• Google Sheets, наиболее похожий по функционалу и возможностям;
• Airtable (только английская версия) – лучший альтернативный вариант по сем параметрам;
• Apple Numbers – лучшая бесплатная альтернатива;
• Apache OpenOffice Calc – лучшая альтернатива среди приложений Open Source;
• LibreOffice Calc – хороший аналог среди приложений Open Source, но с ограниченным инструментарием для визуализации.

FAQ

В каких проектах целесообразно использовать MS Excel, чтобы отслеживать важные финансовые показатели?

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

Можно ли настроить автоматическое обновление данных в дашборде, созданном с помощью сервиса электронных таблиц Excel?

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

Вывод

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