Как создать панель управления в Excel

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

Информация в этом руководстве относится к Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 и Excel для Mac.

Что такое панель инструментов Excel?

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

NicoElNino / Getty Images

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

Типичные элементы панели данных в Excel включают в себя:

• Множество типов графиков
• Различные типы графиков
• Измерительные приборы
• Карты

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

Ввод данных в панель управления Excel

Первым этапом создания панели мониторинга Excel является импорт данных в электронные таблицы Excel из различных источников.

Потенциальные источники для импорта данных в Excel включают в себя:

• Другие файлы книг Excel
• Текстовые, CSV, XML или JSON файлы
• База данных SQL
• Microsoft Access
• Azure Data Explorer
• Facebook и другие веб-страницы
• Любая другая база данных, которая поддерживает ODBC или OLEDB
• Веб-источники (любой веб-сайт, содержащий таблицы данных)

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

Чтобы ввести источник данных:

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

   
   




2. Найдите файл или другой источник данных, который вы хотите импортировать, и выберите его. Затем выберите Импортировать.

   
   




3. В зависимости от выбранного типа источника данных вы увидите различные диалоговые окна для преобразования данных в формат электронной таблицы Excel.

   
   




4. Когда импорт будет завершен, вы увидите, что лист заполнен всеми данными из внешнего файла или базы данных.

   
   




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

   
   




6. Выберите три точки рядом с РЕДАКТИРОВАТЬ ссылку в нижней части окна обновления и выберите свойства.

   
   




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

   

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




8. Повторите описанный выше процесс на новых отдельных рабочих листах, пока вы не импортируете все данные, которые хотите использовать на новой информационной панели.




9. Наконец, создайте новый рабочий лист, поместите его в качестве первого рабочего листа в рабочую книгу и переименуйте в Dashboard.

Как создать панель управления Excel

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

Приведенный ниже пример панели инструментов будет основан на данных о погоде, импортированных с веб-сайтов о погоде со всего Интернета.

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

1. Создать Гистограмма для отображения одной точки данных. Например, чтобы отобразить текущую относительную влажность (от 0 до 100 процентов), вы должны создать гистограмму с 0 процентами как самой низкой точкой и 100 процентами как самой высокой точкой. Сначала выберите Вставить меню, а затем выберите 2D кластеризованная колонка гистограмма.

   
   




2. в Дизайн диаграммы меню, из Данные группа, выберите Выберите данные.




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

   
   




4. Измените заголовок диаграммы, чтобы он соответствовал отображаемым данным. Обновите границы оси, чтобы они были от 0 до 100 процентов. Затем переместите диаграмму в область черты, где вы хотите ее отобразить.

   
   




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

   

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




6. Создать Линия Диаграмма для отображения тенденции данных. Например, чтобы отобразить историю локальных температур для вашей местности, вы должны создать линейную диаграмму, охватывающую последние количество дней данных, которые вы можете импортировать из таблицы веб-сайта о погоде. Сначала выберите меню «Вставка» и выберите 2D область диаграмма.

   
   




7. в Дизайн диаграммы меню, из Данные группа, выберите Выберите данные.




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

   
   




9. Измените заголовок диаграммы, чтобы он соответствовал отображаемым данным. Затем переместите диаграмму в область черты, где вы хотите ее отобразить.

   

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




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

    
    




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

    
    




12. Выберите текстовое поле и используйте Формат Форма окно справа, чтобы отформатировать область отображения текста в вашей приборной панели, как вам нравится.

    
    




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

    

    Круговые диаграммы сравнивают два или более значений. Если вы отображаете процент, например, относительную влажность, вам может потребоваться вручную создать другую ячейку, которая вычитает это значение из 100% для второго значения, чтобы сравнить его. Это приведет к круговой диаграмме, которая отображает процентное значение как часть от возможных 100 процентов.




14. Измените заголовок диаграммы, чтобы он соответствовал отображаемым данным. Затем переместите диаграмму в область черты, где вы хотите ее отобразить.

    
    




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

Добавить визуальное обращение и контекст с цветом

Еще один отличный способ внести ясность в вашу панель — это дать понять, хорошие или плохие значения, если они высокие или низкие.

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

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

1. Щелкните правой кнопкой мыши по внешней границе гистограммы и выберите Область диаграммы формата.

   
   




2. Выберите значок Fill и измените выбор на Градиентная заливка.

   
   




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

   
   




4. Вы можете повторить этот процесс для каждой диаграммы, где добавление цветового контекста к диаграмме имеет смысл для этой точки данных.

Как Excel Dashboards обновляется автоматически

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

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

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

Как использовать Excel Dashboards

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

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

Hanieriani / Getty Images

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

Другие соображения при создании инструментальных панелей:

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

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