Главная » Статьи » Excel » Различные инструменты Excel

Динамические каскадные списки на основе сводных таблиц

Мы продолжаем развивать тему удачнейшего симбиоза выпадающих списков и сводных таблиц, при необходимости налету формировать содержимое этих самых списков по обширному массиву данных. Сегодня мы рассмотрим шикарный пример реализации каскадных выпадающих списков на основе некислого набора данных в 50 000 строк! При этом симпатичная панелька с вышеозначенными списками работает шустро и чётко, как будто бы оперирует данными из ста строк.

Диспозиция

Итак, имеем таблицу в 50 000 строк, состоящую из столбцов: Город, Должность, Сотрудник и Акций. Предполагается, что таблица описывает гигантское предприятие, раскинувшееся на территории 300 крупных городов РФ (столбец Город), в каждом городе на некоторых должностях (столбец Должность) работают конкретные люди (столбец Сотрудник), которые владеют определенным количеством акций (столбец Акции) данного предприятия.

Цель

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

Файл примера

Скачать

Устройство

  1. Исходные данные удачно расположены на листе Data и привычно оформлены в виде умной таблицы tblData

  2. На листе Pivots на основе tblData вручную созданы 3 вспомогательных сводных таблицы, называющиеся слева-направо: ptCities, ptPositions и ptPersons. Они, как не трудно догадаться, используются для: формирования уникального списка городов, упоминаемых в tblData; уникального списка должностей, имеющихся в конкретном городе; и списка имён сотрудников, которые работают в выбранном городе на выбранной должности. Как видно из описания, сводные таблицы два и три должны иметь фильтры, что вы и наблюдаете на рисунке:

    Таблица ptPositions имеет 1 фильтр - Город, таблица ptPersons имеет 2 фильтра - Город и Должность.

    Эти сводные таблицы управляются полностью автоматически с листа Lists посредством VBA кода, но об этом ниже.

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

    • ИД selCity фотографирует список городов в сводной таблице ptCities

    • ИД selPosition - список должностей в ptPositions

    • ИД selPerson - список сотрудников в ptPersons

    • ИД rngShares - тоже самое, что и selPerson, плюс колонка, где указано количество акций у сотрудника

  4. На листе Lists мы нарисовали симпатичную панельку для трёх выпадающих списков и поля для вывода количества акций.

    Выпадающие списки реализованы стандартно и ссылаются соответственно на ИД selCity (D3), selPosition (D5) и selPerson (D7).

    Поле Имеется акций (D9) вычисляем формулой =ЕСЛИОШИБКА(ВПР($D$7;rngShares;2;0);"")

  5. На события Activate и Change листа Lists прикручены обработчики событий, которые я для вас тщательно прокомментировал в коде.

  6. Для красоты на листах отключены показ заголовков и сетки, но вы их можете включить в меню ВИД, группа Показ, параметры Сетка и Заголовки

Взаимодействие

Опишем, как перечисленные компоненты работают совместно.
Когда вы активируете лист 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.

Вам понравилось? :)

Читайте также:

Категория: Различные инструменты Excel | Добавил: dsb75 (13.02.2015) | Автор: Батьянов Денис E W
Просмотров: 3685 | Комментарии: 4 | Теги: smart table, cascaded lists, Pivot table, умная таблица, выпадающий список, dropdown list, каскадные списки, сводная таблица | Рейтинг: 5.0/2
Всего комментариев: 4
0
3 marik1809   (13.10.2015 11:17)
Здравствуйте, Денис!
Спасибо большое за отличную статью! Метод элегантный и достаточно простой в реализации. У меня на 150 000 строк работает на "ура". Плюс нет ограничения в количестве связанных списков.
P.S. думаю, есть смысл оновлять сводные не при активации листа со списками, а при деактивации листа с исходными данными.
P.P.S. В коде часть, которая закомментирована "очень важная проверка - догадайтесь почему" - я так и не догадался smile У меня и без неё все работает без сбоев)

0
4 dsb75   (20.10.2015 00:24)
Здравствуйте! Пожалуйста smile Про деактивацию - звучит разумно. Про проверку сейчас уже сам не помню... smile

0
2 dsb75   (12.09.2015 22:08)
Здравствуйте.
Ну технология та же... если вы можете это сделать в одной ячейке, то просто распространите это не несколько. Умная таблица запоминает правила Data Validation существующих столбцов и проецирует их для создаваемых строк.

0
1 L-Colonel   (12.09.2015 20:57)
Здравствуйте!
Помогите, пожалуйста. Все подсказывают, как сделать выпадающие списки, но при этом в одной ячейке. А возможно ли сделать выпадающие динамические (зависимые) списки в таблице? Причём в умной?
Спасибо.

Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Яндекс.Метрика