Многоуровневые (каскадные) выпадающие списки Excel

Как быстро создать многоуровневые (каскадные) выпадающие списки в Excel

В данной статье рассматривается методика быстрого создания многоуровневых (каскадных) выпадающих списков в Excel на основе умных таблиц для моделирования иерархических данных. В качестве примера в видеоуроке создаются 6-уровневые выпадающие списки.

Мир вокруг нас полон иерархических структур. Зачастую при разработке форм в Microsoft Excel мы сталкиваемся с задачей организации выбора показателей, имеющих некую иерархию, например, адреса (страна-город-улица-дом), организационная структура предприятия (департамент – управление – отдел-сотрудник) или же номенклатура товаров магазина. Почти каждый пользователь Excel умеет создавать в ячейке выпадающий список, более продвинутые могут создать второй, связанный с первым выпадающий список.

А можно ли создавать более сложные системы списков в Excel? На самом деле да, причем количество уровней таких списков ничем не ограничено и определяется только вашей потребностью. Рассмотрим методику, которая позволяет быстро и без особого труда строить многоуровневые выпадающие списки.

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

Основа методики – организация исходной информации в «Умных таблицах», т.е. специально структурированных объектах Excel. «Умную таблицу» можно создать клавишами Ctrl+T или кнопкой Таблица на вкладке Вставка, после чего таблице необходимо задать имя (во всплывающей при её выделении вкладки Конструктор). Основное требование – названия нижестоящих таблиц должны соответствовать элементам вышестоящих. В качестве примера рассмотрим фрагмент 6-уровневой иерархии товаров магазина, оформленной в “Умных таблицах”:

Многоуровневые (каскадные) выпадающие списки Excel
Первая таблица Группы_товаров связывает группы товаров и категории: в магазине 2 группы товаров – поля Продукты_питания и Одежда, каждая из которых включает по 2 категории товаров: продукты питания состоят из элементов Молочные_продукты и Мясо, одежда – Верхняя_одежда и Спортивные_товары.
Обратите внимание! Элементы таблицы будут являться одновременно названием для нижестоящих таблиц. К именам таблиц предъявляются специальные требования: имя должно начинаться с буквы, не должно содержать пробелов и специальных символов.
На следующем уровне создаются таблицы, связывающие категории и виды продукции: в приведенном примере создана таблица с именем Молочные_продукты, содержащая поля Молоко (подразделяется на козье и коровье) и Сыр (подразделяется на твердый и полутвердый), а также таблица с именем Мясо, содержащая поля Красное_мясо (подразделяется на говядина и свинина) и Птица (подразделяется на курицу и индейку). 

Остальные данные организуются по такому же принципу. После того, как данные организованы, необходимо непосредственно создать выпадающие списки, которые задаются специальными формулами, вводимыми в поле Источник в меню Проверка данных – Список:

Проверка данных Excel

Всего возможны три вида выпадающих списков:

1 уровень (первичный, не связанный ни с чем список)

2 уровень (имеющий одну связь на один вышестоящий выпадающий список: имя таблицы или имя поля)

3 уровень (имеющий две связи на вышестоящие списки: на имя таблицы и имя поля)

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

Итак, вот формулы, которые подставляются в Проверку данных в зависимости от конкретной ситуации. Обратите внимание, вместо красного текста подставляются непосредственно названия таблиц и полей, а вместо оранжевого – ставится ссылка на выпадающий список вышестоящего уровня:

Выпадающий список 1 уровня:

Создать список конкретных умных таблиц книги: Таблица1;Таблица2;Таблица3

Создать список полей конкретной таблицы:  =ДВССЫЛ(“Таблица1“&”[#Заголовки]”)

Создать список элементов конкретного поля конкретной таблицы: =ДВССЫЛ(“Таблица1“&”[Поле1]”)

Выпадающий список 2 уровня:

Создать список полей таблицы, выбираемой в вышестоящем списке:  =ДВССЫЛ(A1&”[#Заголовки]”)

Создать список элементов выбираемого поля конкретной таблицы: =ДВССЫЛ(“Таблица1“&”[“&А1&”]”)

Выпадающий список 3 уровня

 Создать список элементов выбираемого поля выбираемой таблицы: =ДВССЫЛ(A1&”[“&B1&”]”)

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

Выпадающий список из умной таблицы Excel
Выпадающий список из умной таблицы Excel

Ну а если у вас есть трудности с организацией исходных данных и построением выпадающих списков,  вы всегда можете получить консультацию разработчиков!

Share on vk
Share on odnoklassniki
Share on facebook
Share on twitter
Share on email
Share on whatsapp
Share on telegram
Share on print

Оставьте комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Пролистать наверх