Интерактивная гистограмма с раскрытием дополнительной информации

В двух словах: Узнайте, как создать интерактивную гистограмму (или график распределения), чтобы она отображала дополнительные сведения при выборе определенного столбца.

Уровень сложности: средний.

Интерактивная гистограмма

Вот так выглядит готовая гистограмма:

Отображаем дополнительную информацию при выборе конкретного столбца

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

В нашем примере мы рассматриваем данные о телефонных счетах сотрудников за месяц. Гистограмма собирает сотрудников в группы по величине счёта и затем показывает число сотрудников в каждой группе. На графике, показанном выше, видно, что у 71 сотрудника размер телефонного счёта за месяц оказался между $0 и $199

Кроме того, мы видим, что у 11 сотрудников счёт за телефон превысил $600 за месяц. Вот это да! Вот что получается, если много времени проводить в Фейсбуке! 🙂

Сразу возникает вопрос: «Кто эти люди с такими огромными счетами???»

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

Как эта диаграмма работает?

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

Срез связан со сводной таблицей, расположенной справа, и запускает фильтрацию по имени группы. Область Строки (Rows) этой сводной таблицы содержит имена сотрудников, а область Значения (Values) – величину счёта.

Исходные данные

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

В столбце G таблицы находится функция ВПР (VLOOKUP), которая возвращает имя группы. Эта формула выполняет поиск значения из столбца Bill Amount в таблице tblGroups и возвращает значение из столбца Group Name.

Обратите внимание, что последний аргумент функции ВПР (VLOOKUP) равен ИСТИНА (TRUE). Так функция будет просматривать столбец Group Min в поисках значения из столбца Bill Amount и остановится на ближайшем значении, не превышающем искомое.

Кроме этого, Вы можете создать группы автоматически при помощи сводных таблиц, не прибегая к использованию функции ВПР (VLOOKUP). Однако я люблю использовать ВПР (VLOOKUP), поскольку эта функция даёт больший контроль над названиями групп. Можно настроить формат имени группы по собственному желанию и контролировать границы каждой группы.

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

Гистограмма и сводная таблица

На этом рисунке показана сводная таблица, использованная для создания гистограммы. Область Строки (Rows) содержит имена групп из столбца Group таблицы с исходными данными, а область Значения (Values) содержит значения из столбца Count of Name. Теперь мы можем показать распределение сотрудников в виде гистограммы.

Сводная таблица с дополнительной информацией

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

• Область Строки (Rows) содержит имена сотрудников.
• Область Значения (Values) содержит месячный счет за телефон.
• Область Фильтры (Filters) содержит названия групп.

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

Собираем целое из частей

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

Для чего ещё мы можем использовать этот прием?

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

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

Спасибо!