В 2014 году я написал статью про каскадные выпадающие списки. Решение было интересное, но довольно громоздкое, плюс требовались работающие макросы. Сегодня я хочу познакомить вас с наиболее продвинутым и универсальным методом организации динамических каскадных выпадающих списков в ячейке, который только существует на данный момент.
Функционал
Поддержка любого уровня вложенности Обеспечение целостности данных. Вы не можете изменить уровень N, если уже определили уровень N+1 и выше. Иными словами, если вы выбрали фрукт яблоки, а затем сорт яблок - Антоновка, то, не очистив ячейку с Антоновкой, вы не измените яблоки на, скажем, груши.
Преимущества
Простота инфраструктуры решения Удобное ведение справочников Ни строчки VBA кода. Вы можете использовать файлы типа XLSX
Файл примера
Обязательные условия для работы решения
Отдельная и единственная умная таблица для ведения списков. Каждый столбец содержит все элементы одного выпадающего списка. В нашем примере она имеет имя tblLists. Таблица, в которой будут располагаться выпадающие списки тоже должна быть умной. У меня она называется tblEntry. Единственный именованный диапазон (SubList), который и выполняет всю работу. В качестве источника строк для выпадающего списка используется вышеупомянутый SubList. При чём во всех ячейках всех уровней! Это очень удобно. Безусловно, необходимо заполнить справочник. Вы можете посмотреть, как это сделал я в своём примере для классификации млекопитающих. На первом уровне у меня отряды млекопитающих, на втором - семейства, на третьем - рода (если видов слишком много) или виды. Всё очень просто и самоочевидно. Крайняя правая колонка после таблицы tblEntry не должна использоваться, так как формулы проверяют ячейку справа и по её заполненности делают вывод о возможности ввода значения (если заполнена, то список возвращается пустым).
Как, чёрт побери, это работает?!
Повторюсь, что все делает формула в ИД SubList. Формула, на первый взгляд, выглядит страшновато, но давайте попробуем разобраться. Кстати, если вы не чувствуете пока в себе сил вникать в нюансы работы таких формул, то и не надо. Решение в любом случае готово к употреблению.
Всё относительно
Первое, что вы должны знать об этой формуле, это то, что она относительная. Она содержит относительные ссылки, поэтому, если вы встанете на ячейку B3 листа Smart (так называется лист, содержащий таблицу tblEntry) и посмотрите на формулу в ИД SubList, то она будет выглядеть так:
=ЕСЛИ( ЕПУСТО( Smart!C3); ЕСЛИ( tblEntry[#Заголовки] Smart!B:B = tblEntry[[#Заголовки];[Главная категория]]; СМЕЩ( tblLists[[#Все];[Основная]]; 1; ; СЧЁТЗ(tblLists[Основная])); СМЕЩ( tblLists; 0; ПОИСКПОЗ(Smart!A3; tblLists[#Заголовки];0)-1; СЧЁТЗ( СМЕЩ( tblLists; ; ПОИСКПОЗ( Smart!A3; tblLists[#Заголовки];0)-1; ; 1) ); 1) ) )
а, если перейдёте в C5 и ещё раз посмотрите, то увидите уже это:
=ЕСЛИ( ЕПУСТО( Smart!D5); ЕСЛИ( tblEntry[#Заголовки] Smart!C:C = tblEntry[[#Заголовки];[Главная категория]]; СМЕЩ( tblLists[[#Все];[Основная]]; 1; ; СЧЁТЗ(tblLists[Основная])); СМЕЩ( tblLists; 0; ПОИСКПОЗ(Smart!B5; tblLists[#Заголовки];0)-1; СЧЁТЗ( СМЕЩ( tblLists; ; ПОИСКПОЗ( Smart!B5; tblLists[#Заголовки];0)-1; ; 1) ); 1) ) )
Декомпозиция
Посмотрим из каких функциональных блоков состоит формула и за что они отвечают. На примере ячейки B3.
Обратите внимание на первую, внешнюю формулу ЕСЛИ. У ней только 2 параметра: условие - блок 1 на рисунке, и параметр, возвращаемый в случае истинности блока 1 (большой блок 2), а вот параметра для ложного условия нет вообще. Блок 1 при помощи функции ЕПУСТО проверяет ячейку справа от текущей и, если она не пустая, то работа формулы на этом прекращается. Она возвращает Null в качестве источника строк для функции Проверка данных и выпадающий список не срабатывает. Если ячейка справа пуста, то значит необходимо определить, какой список необходимо показать, надо его найти в таблице tblLists и вернуть все его строки. За всё это отвечает блок 2, также обёрнутый в формулу ЕСЛИ. Параметр-условие формулы ЕСЛИ (блок 3) определяет имя столбца таблицы tblEntry над активной ячейкой. Определяет через операцию пересечения диапазонов (символ пробела) при помощи следующей конструкции tblEntry[#Заголовки] Smart!B:B. Результатом этой операции для B3 становится ячейка B2. Далее идёт выяснение того, является ли этот заголовок заголовком корневого списка. Если это так (а для B3 это так), то выполняется блок 4, а если нет, то блок 5. Блок 4 формирует диапазон - набор строк для корневого списка. Нам необходимо использовать СМЕЩ и СЧЁТЗ, так как в каждом столбце количество непустых строк может быть разное. Тут всё стандартно: первый параметр задаёт точку отсчёта (не надо пугаться, что точка отсчёта задана диапазоном, так как формула всё равно возьмёт первую ячейку), второй параметр - смещение по строкам (у нас это 1), третий - смещение по столбцам (опущен - у нас ширина массива 1 столбец), четвёртый параметр - число строк (их просто считаем через СЧЁТЗ), пятый параметр - число столбцов (опущен). Блок 5 формирует набор строк для случая промежуточного (не корневого) списка. Это означает, что надо взять значение ячейки слева от текущей и искать столбец с таким же именем, а потом действовать примерно так же, как в блоке 4. Всё это присутствует в блоке 5: мы видим опять СМЕЩ, первый параметр tblLists - это в качестве точки отсчёта берётся первая ячейка таблицы без заголовка (это Ref!A2), смещение по строкам - ноль, так как мы уже стоим на области данных, смещение по столбцам определяется в блоке 6, высота диапазона определяется в блоке 7, ширина диапазона равна 1. Обратите внимание, что блок 6 и блок 8 одинаковые - ведь мы определяем столбец динамически.
Если для вас функции СМЕЩ и СЧЁТЗ не пустой звук, то прочтя 2-3 раза раздел про декомпозицию, вы должно быть поняли, как это всё работает. Если же нет, то не расстраивайтесь, - всему своё время. Почитайте про структурные формулы умных таблиц, описания функций СМЕЩ, СЧЁТЗ, ПОИСКПОЗ и через некоторое время, когда знания улягутся в голове, вернитесь к этому описанию снова.
P.S.Данный рецепт подсмотрен мною для вас на сайте известного индийского экселиста Chandoo (Purna Duggirala).
Читайте также:
|
0
В таком случае, даже если у нас хватит терпения заполнить таблицу в нужном формате (в чем я искренне сомневаюсь 
), ширина рабочего листа не даст нам реализовать этот метод.
