Создание и совместное использование информационной панели с помощью Excel и групп

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

Пример информационной панели со срезами и временной шкалой

Обзор

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

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

После создания панели мониторинга мы покажем, как поделиться ею с людьми, создав группу Майкрософт. Кроме того, мы предлагаем для скачивания интерактивную книгу Excel, в которой вы можете самостоятельно выполнить все описанные ниже действия.

Скачайте учебник по панели мониторинга Excel.

Получение данных

Вы можете скопировать и вставить данные непосредственно в Excel или настроить запрос из источника данных. В этой статье мы использовали запрос Анализ продаж из шаблона Борей для Microsoft Access. Если вы хотите использовать его, откройте Access и выберите Файл > Создать > поиск и в поле Поиск введите "Northwind" и создайте шаблон базы данных. После этого вы сможете получить доступ к любому из запросов, включенных в шаблон. Мы уже поместили эти данные в книгу Excel, поэтому вам не нужно беспокоиться, если у вас нет доступа.
Убедитесь, что данные правильно структурированы без отсутствующих строк или столбцов. Каждая строка должна представлять отдельную запись или элемент. Сведения о настройке запроса или необходимости управления данными см. в статье Получение & преобразования в Excel.
При необходимости отформатируйте данные в виде таблицы Excel. При импорте из Access данные автоматически импортируются в таблицу.

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

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

В этом примере мы создали общую сводку продаж по категориям продуктов и отсортировали данные по полю "Продажи" в порядке убывания.

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

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

На этом этапе может потребоваться присвоить своим сводным таблицам понятные имена, чтобы вы знали, что они делают. В противном случае Excel назовет их сводной таблицей1, сводной таблицей2 и т. д. Вы можете выбрать каждый из них, а затем перейти к разделу Анализ сводной таблицы > сводной таблицы, а затем ввести новое имя в поле Имя сводной таблицы . Имена понадобятся при подключении сводных таблиц к срезам и временным шкалам.

Создание сводных диаграмм

Щелкните в любом месте первой сводной таблицы и перейдите в раздел Анализ > средствасводной таблицы > сводной диаграммы, а затем выберите тип диаграммы. Мы выбрали комбинированную диаграмму с гистограммой Sales в качестве кластеризованной гистограммы , а % total — в качестве графика, наложенного на вторичной оси.
Выберите диаграмму, а затем выберите нужный размер и формат на вкладках Анализ, Конструктор и Форматсводной диаграммы. Дополнительные сведения см. в нашей серии статей о диаграммах форматирования.
Повторите действия для каждой сводной таблицы.
Теперь самое время переименовать свои сводные диаграммы. Перейдите в раздел Анализ сводной диаграммы > сводной диаграммы и введите новое имя в поле Имя диаграммы .

Добавление срезов и временных шкал

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

Развернутые срезы и временная шкала на информационной панели

Выберите любую сводную таблицу и перейдите к разделу Анализ > фильтр > вставка среза, а затем проверка каждый элемент, который вы хотите использовать для среза. Для этой панели мониторинга мы выбрали Категория, Название продукта, Сотрудник и Имя клиента. Когда вы нажмете кнопку ОК, срезы будут добавлены в середину экрана с накоплением друг над другом, поэтому вам потребуется упорядочить и изменить их размер по мере необходимости.
Параметры среза: Щелкнув любой срез, можно перейти на вкладку Срез и выбрать различные параметры, например Стиль и количество отображаемых столбцов. Вы можете выровнять несколько срезов, выбрав их с помощью клавиш CTRL+левой кнопки мыши, а затем используйте средства выравнивания на вкладке Инструменты среза .
Connections среза: Срезы будут подключены только к сводной таблице, которую вы использовали для их создания, поэтому необходимо выбрать каждый срез, а затем перейти к разделу Срез > срез > отчет Connections и проверка, к каким сводных таблицам необходимо подключиться. С помощью срезов и временных шкал можно управлять сводными таблицами на любом листе, даже если он скрыт.
Добавьте временную шкалу: Выберите любую сводную таблицу и перейдите в раздел Анализ сводной таблицы > Фильтр > Вставить временную шкалу, а затем проверка каждый элемент, который вы хотите использовать. Для этой панели мониторинга мы выбрали Дата заказа.
Параметры временной шкалы: Щелкните временную шкалу, перейдите на вкладку Временная шкала и выберите такие параметры, как Стиль, Заголовок и Заголовок. Выберите параметр Подключения к отчетам, чтобы связать временную шкалу с нужной сводной таблицей.

Узнайте больше о срезах и временных шкалах.

Дальнейшие действия

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

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

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