Мы продолжаем развивать тему удачнейшего симбиоза выпадающих списков и сводных таблиц, при необходимости налету формировать содержимое этих самых списков по обширному массиву данных. Сегодня мы рассмотрим шикарный пример реализации каскадных выпадающих списков на основе некислого набора данных в 50 000 строк! При этом симпатичная панелька с вышеозначенными списками работает шустро и чётко, как будто бы оперирует данными из ста строк.
Диспозиция
Итак, имеем таблицу в 50 000 строк, состоящую из столбцов: Город, Должность, Сотрудник и Акций. Предполагается, что таблица описывает гигантское предприятие, раскинувшееся на территории 300 крупных городов РФ (столбец Город), в каждом городе на некоторых должностях (столбец Должность) работают конкретные люди (столбец Сотрудник), которые владеют определенным количеством акций (столбец Акции) данного предприятия.
Цель
Цель - получить вот этот, не побоюсь этого слова, совершенный "берилл" из моей коллекции рецептов Excel, который вы наблюдаете на анимированной иллюстрации.
Файл примера
Устройство
-
Исходные данные удачно расположены на листе Data и привычно оформлены в виде умной таблицы tblData
-
На листе Pivots на основе tblData вручную созданы 3 вспомогательных сводных таблицы, называющиеся слева-направо: ptCities, ptPositions и ptPersons. Они, как не трудно догадаться, используются для: формирования уникального списка городов, упоминаемых в tblData; уникального списка должностей, имеющихся в конкретном городе; и списка имён сотрудников, которые работают в выбранном городе на выбранной должности. Как видно из описания, сводные таблицы два и три должны иметь фильтры, что вы и наблюдаете на рисунке:
Таблица ptPositions имеет 1 фильтр - Город, таблица ptPersons имеет 2 фильтра - Город и Должность.
Эти сводные таблицы управляются полностью автоматически с листа Lists посредством VBA кода, но об этом ниже.
-
Сводные таблицы формируют и выводят на экран списки городов, должностей и сотрудников, которые мы фотографируем (мне кажется это удачной метафорой) через именованные диапазоны:
ИД selCity фотографирует список городов в сводной таблице ptCities ИД selPosition - список должностей в ptPositions ИД selPerson - список сотрудников в ptPersons ИД rngShares - тоже самое, что и selPerson, плюс колонка, где указано количество акций у сотрудника
-
На листе Lists мы нарисовали симпатичную панельку для трёх выпадающих списков и поля для вывода количества акций.
Выпадающие списки реализованы стандартно и ссылаются соответственно на ИД selCity (D3), selPosition (D5) и selPerson (D7).
Поле Имеется акций (D9) вычисляем формулой =ЕСЛИОШИБКА(ВПР($D$7;rngShares;2;0);"")
-
На события Activate и Change листа Lists прикручены обработчики событий, которые я для вас тщательно прокомментировал в коде.
-
Для красоты на листах отключены показ заголовков и сетки, но вы их можете включить в меню ВИД, группа Показ, параметры Сетка и Заголовки
Взаимодействие
Опишем, как перечисленные компоненты работают совместно.
Когда вы активируете лист Lists, то процедура Worksheet_Activate рефрешит кэш сводных таблиц листа Pivots, поэтому, если на лист Data были добавлены новые данные, то они включаются в кэш наших сводных таблиц.
Когда на листе Lists вы встаёте на ячейку D3, то вы видите, что она содержит выпадающий список на основе ИД selCity. Указанный именованный диапазон фотографирует список городов на листе Pivots в сводной таблице ptCities. Выбрав какой-то город, вы меняете содержимое ячейки D3, поэтому Excel вызывает процедуру Worksheet_Change текущего листа, которая реагирует на изменение D3 тем, что очищает ячейки D5 и D7 и устанавливает выбранный город в качестве фильтра сводных таблиц ptPositions и ptPersons. Это в свою очередь приводит к тому, что ИД selPosition и selPerson получают актуальные должности текущего города и имена сотрудников. Таким образом, выпадающий список в D5 готов к работе.
Когда вы уточняете Должность, изменяя D5, процедура Worksheet_Change отрабатывает опять, но уже очищает лишь D7 и обновляет фильтры таблицы ptPersons. После этого ИД selPerson и rngShares обновляются самыми актуальными данными и далее, когда вы выбираете конкретного сотрудника, происходит лишь расчёт формулы в D9 и вывод на экран количества акций из диапазона rngShares.
Вам понравилось? :)
Читайте также:
|
0
У меня и без неё все работает без сбоев)
