Создание информационной панели Microsoft Excel включает в себя извлечение информации из нескольких источников, чтобы вы могли просматривать эти данные в одном месте. Excel предлагает множество способов сделать это, что делает его одним из самых мощных инструментов отчетности, которые вы можете использовать.
Информация в этом руководстве относится к Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 и Excel для Mac.
Что такое панель управления Excel?
Дашборд - это инструмент, который помогает визуально отслеживать и анализировать наиболее важные для вас показатели. Вы можете использовать панель инструментов для мониторинга эффективности вашего бизнеса, эффективности производственного процесса или работы сотрудников вашего отдела продаж.
Независимо от того, как вы используете панель мониторинга, концепция всегда одна и та же: фоновые электронные таблицы извлекают данные из файлов, служб или подключений API к базам данных и другим источникам. На главном листе отображаются данные из этих нескольких листов в одном месте, где вы можете просмотреть их все с первого взгляда.
Типичные элементы панели данных в Excel включают:
- Графики
- Графики
- Датчики
- Карты
Вы можете создать два типа информационных панелей. Для создания отчетов на сводных панелях можно создать статическую сводную панель из данных в других таблицах, которые можно отправить кому-либо в отчете Word или PowerPoint. динамическая информационная панель - это та панель, которую люди могут просматривать в Excel, и она обновляется всякий раз, когда обновляются данные на других листах.
Перенос данных в панель управления Excel
Первый этап создания информационной панели Excel - это импорт данных в электронные таблицы из различных источников.
Потенциальные источники для импорта данных в Excel включают:
- Другие файлы книг Excel
- Текстовые файлы, файлы CSV, XML или JSON
- база данных SQL
- Microsoft Access
- Azure Data Explorer
- Facebook и другие веб-страницы
- Любая другая база данных, поддерживающая ODBC или OLEDB
- Веб-источники (любой веб-сайт, содержащий таблицы данных)
С таким количеством потенциальных источников данных возможности того, какие данные вы можете использовать в Excel для создания полезных информационных панелей, безграничны.
Чтобы ввести источник данных:
-
Откройте пустой лист Excel. Выберите меню Данные и в раскрывающемся меню Получить данные выберите нужный тип данных, а затем выберите источник данных.
Image -
Перейдите к файлу или другому источнику данных, который вы хотите импортировать, и выберите его. Выберите Импорт.
Image -
В зависимости от выбранного вами типа источника данных вы увидите различные диалоговые окна для преобразования данных в формат электронной таблицы Excel.
Image -
Таблица будет заполнена всеми данными из внешнего файла или базы данных.
Image -
Чтобы обновить данные, чтобы они регулярно загружали любые изменения, сделанные во внешнем источнике данных, выберите значок Обновить в правой части Запросы и Панель Connections.
Image -
Выберите три точки рядом со ссылкой РЕДАКТИРОВАТЬ в нижней части окна обновления и выберите Свойства.
Image -
Настройте обновление данных из источника через регулярные промежутки времени, установив для Обновлять каждые xx минут любой интервал, с которым вы хотите обновлять данные.
Обновление данных из внешних источников полезно, но может потреблять процессорное время, если вы установите слишком высокую частоту обновления. Выберите частоту обновления, при которой данные обновляются так же часто, как они изменяются в источнике, но не так часто, чтобы вы каждый раз копировали только одни и те же данные.
Image - Повторяйте описанный выше процесс в новых отдельных рабочих листах, пока вы не импортируете все данные, которые хотите использовать в новой информационной панели.
- Наконец, создайте новый рабочий лист, поместите его первым рабочим листом в рабочую книгу и переименуйте его Dashboard.
Как создать информационную панель Excel
Теперь, когда у вас есть все необходимые данные в вашей книге Excel, и все эти данные обновляются автоматически, пришло время создать панель управления Excel в реальном времени.
Приведенный ниже пример панели мониторинга будет использовать данные о погоде с веб-сайтов со всего Интернета.
Иногда, когда вы импортируете данные из внешних источников, вы не можете отобразить импортированные данные на диаграмме. Чтобы исправить это, создайте новую электронную таблицу и в каждой ячейке введите =convert(и выберите данные из импортированной электронной таблицы. Для параметров единицы измерения просто выберите те же параметры, что и до и после. Заполните весь лист одной и той же функцией, чтобы все данные были скопированы на новый лист и преобразованы в числа, которые вы можете использовать в различных диаграммах, которые вы создадите для своей панели инструментов.
-
Создайте гистограмму для отображения одной точки данных. Например, чтобы отобразить текущую относительную влажность (от 0 до 100 процентов), вы должны создать гистограмму с 0 процентами в качестве самой низкой точки и 100 процентами в качестве самой высокой точки. Сначала выберите меню Вставка, а затем выберите гистограмму 2D Clustered Column..
Image -
В меню Дизайн диаграммы в группе Данные выберите Выбрать данные.
Image -
В появившемся окне Выбрать источник данных щелкните поле Диапазон данных диаграммы, а затем выберите ячейку в данных таблицу, которую вы хотите отобразить с помощью этой гистограммы.
Image -
Измените заголовок диаграммы, чтобы он соответствовал отображаемым данным. Обновите границы оси, чтобы они были от 0 до 100 процентов. Затем переместите диаграмму в область тире, где вы хотите ее отобразить.
Image -
Повторите те же шаги, описанные выше, чтобы создать гистограммы для любых других отдельных точек данных, которые вы хотите отобразить. Сделайте диапазон оси минимальным и максимальным для этих измерений. Например, хорошим диапазоном барометрического давления будет от 28 до 32.
Выбор правильного диапазона данных важен, потому что, если вы используете только значение по умолчанию, масштаб может оказаться слишком большим для данных, в результате чего гистограммы останутся пустыми. Вместо этого держите минимальный и максимальный конец шкалы оси лишь немного ниже и выше предельно возможных значений ваших данных.
Image -
Создайте Линейный график для отображения тренда данных. Например, чтобы отобразить историю местных температур для вашего региона, вы должны создать линейную диаграмму, охватывающую данные за последнее количество дней, которые вы можете импортировать из таблицы веб-сайта погоды. Сначала выберите меню «Вставка», выберите 2D-область диаграмму.
Image -
В меню Дизайн диаграммы в группе Данные выберите Выбрать данные.
Image -
В появившемся окне Выбрать источник данных щелкните поле Диапазон данных диаграммы, а затем выберите ячейки в данных таблицу, которую вы хотите отобразить на этой линейной диаграмме.
Image -
Измените название диаграммы, чтобы оно соответствовало отображаемым данным, и переместите диаграмму в область тире, где вы хотите ее отобразить.
Диаграммы очень гибки при размещении их на панели инструментов. Вы можете изменить расположение, а также размер и форму виджета диаграммы. Используйте эту гибкость для создания организованных информационных панелей, предоставляющих пользователю как можно больше информации на минимальном пространстве.
Image -
Создайте текстовое поле для отображения строковых данных из импортированных листов. Например, чтобы просматривать обновления предупреждений о погоде на панели управления, свяжите содержимое текстового поля с ячейкой в импортированном листе данных. Для этого выберите меню Вставка, выберите Текст, а затем выберите Текстовое поле
Image -
Поместите курсор мыши в поле формулы, введите =, а затем выберите ячейку в таблице импортированных данных, содержащую строковые данные, которые вы хотите отобразить.
Image -
Выберите текстовое поле и используйте окно Format Shape справа, чтобы отформатировать область отображения текста на панели управления.
Image -
Вы также можете сравнить две точки данных в импортированных листах данных, используя круговые диаграммы Например, вы можете отобразить относительную влажность в виде круговой диаграммы. Сначала выберите данные, которые вы хотите отобразить, и в меню Вставка выберите 2D Pie диаграмму.
Круговые диаграммы сравнивают два или более значений. Если вы отображаете процентное значение, такое как относительная влажность, вам может потребоваться вручную создать другую ячейку, которая вычитает это значение из 100% для второго значения, чтобы сравнить его. В результате появится круговая диаграмма, отображающая процентное значение как долю от общего числа возможных 100 процентов.
-
Измените заголовок диаграммы в соответствии с отображаемыми данными, а затем переместите диаграмму в ту область тире, где вы хотите ее отобразить.
Image - Добавляя различные типы диаграмм данных, вы можете создать полезную информационную панель, которая отображает все типы данных на одной удобной информационной панели.
Добавьте визуальную привлекательность и контекст с помощью цвета
Еще один способ внести ясность в панель управления – это залить гистограммы градиентной заливкой с предупреждающим цветом, например красным, для областей данных, которые могут быть неверными.
Например, если вы хотите показать, что относительная влажность выше 75 % некомфортна, вы можете соответствующим образом изменить градиентную заливку одиночной гистограммы. Вот как.
-
Щелкните правой кнопкой мыши внешнюю границу гистограммы и выберите Формат области диаграммы.
Image -
Выберите значок Заливка на панели Формат области диаграммы и измените выделение на Градиентная заливка.
Image -
Выберите каждый значок уровня вдоль линии градиентной заливки и измените цвет и яркость в соответствии с тем, насколько «хорошим» или «плохим» является этот уровень. В этом примере высокая относительная влажность становится темно-красной.
Image - Повторите этот процесс для каждой диаграммы, где добавление цветового контекста к диаграмме имеет смысл для этой точки данных.
Как автоматически обновляются информационные панели Excel
После создания информационной панели вам не нужно ничего делать для обновления графики. Все данные в этих диаграммах и виджетах обновляются следующим образом:
- Таблицы с импортированными данными обновляются в дату, которую вы установили при первом создании импорта данных.
- Любые дополнительные листы, которые вы создали для исправления или переформатирования данных из импортированных листов, будут обновлены новыми данными в этих листах.
- Каждый виджет на панели инструментов автоматически обновляется, чтобы отображать новые данные внутри этих обновленных листов для диапазонов, выбранных вами при создании этих диаграмм.
Эти обновления происходят автоматически, пока открыт Excel.
Как использовать информационные панели Excel
Создание информационных панелей в Excel может быть полезным по многим причинам. Однако важно создавать их на основе конкретных потребностей, а не пытаться создать одну панель управления, которая делает все.
Например, если вы менеджер по продажам и заинтересованы в мониторинге эффективности своей команды по продажам, то панель управления менеджера по продажам должна быть сосредоточена на ключевых показателях эффективности (KPI), связанных с эффективностью продаж.
Дашборд такого типа не должен содержать информацию, не связанную с эффективностью продаж, иначе он может стать слишком загроможденным. На загроможденной приборной панели труднее увидеть взаимосвязь данных и закономерности, которые имеют значение.
Другие соображения при создании информационных панелей:
- Используйте правильные диаграммы для правильных данных.
- Не используйте слишком много цветов на приборной панели.
- Разместите информационную панель с похожими данными и типами диаграмм в общих блоках.
- Убедитесь, что каждая диаграмма имеет простые метки и не слишком загромождена.
- Организуйте виджеты в иерархии важности, чтобы самая важная информация находилась в левом верхнем углу панели управления.
- Используйте условное форматирование, чтобы плохие числа отображались красным цветом, а хорошие - зеленым.
Самое главное, используйте творческий подход для разработки информационных панелей, которые будут информативными и интересными для использования.
