Выпадающие списки Excel

Пример создания многоуровневых (каскадных) выпадающих списков в Excel

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

Строить мы будем форму выбора товара по его классификации, например, Продукты питания →Молочные продукты → Молоко → Козье молоко →Жирность 8%→Стерилизованное

Ключевая задача при формировании такой системы выпадающих списков – правильно организовать исходные данные в виде системы «умных таблиц». При этом элементы вышестоящих таблиц должны одновременно являться названиями таблиц и полей нижестоящих таблиц.

Таким образом, каждая таблица включает в себя по 2 уровня будущих выпадающих списков – первый уровень это имена полей, второй уровень – элементы полей, поэтому условно разделим решение задачи на 3 шага (по 2 уровня в каждом).

Шаг 1. 

Сформируем первую таблицу, связывающую группы и категории товаров. Назовем таблицу «Группы_товаров»:

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

Параллельно на отдельном листе сразу будем формировать выпадающие списки. И сейчас уже можно сформировать 2 из 6 уровней выпадающих списков.

На рисунке 3 в столбце D указаны формулы с применением функции ДВССЫЛ, вводимые в меню Проверка данных → Списки в ячейки столбца С:

В данном случае на 1 уровне формируется выпадающий список полей таблицы Группы_товаров формулой =ДВССЫЛ(“Группы_товаров[#Заголовки]”), а на 2 уровне – список элементов того поля, которое выбрано в списке 1 уровня формулой =ДВССЫЛ(“Группы_товаров”&”[“&C2&”]”).

Шаг 2.

Сформируем 3 и 4 уровни выпадающих списков. Как было сказано выше, главное условие – наличие «умных таблиц», названных в соответствии с элементами первой таблицы. На рисунке 4 схематично показаны только 2 таблицы, детализирующие группу продуктов питания, по факту необходимы еще 2 аналогичные таблицы, описывающих одежду.

Итак, созданы таблицы Молочные_продукты и Мясо. Формируем 3 и 4 уровни выпадающих списков:

На 3 уровне формируется выпадающий список из названий полей таблицы, имя которой выбрано на 2 уровне (т.е. в ячейке C3). На 4 уровне впервые сталкиваемся с «двойной связью»: формируется выпадающий список из элементов таблицы, имя которой выбрано на 2 уровне (ячейке C3), поля, выбранного на 3 уровне (ячейка С4).

Шаг 3.

Завершаем разработку системы выпадающих списков, формируя 5 и 6 уровни. Аналогично предыдущим шагам формируем «умные таблицы» согласно элементам 4 уровня:

Созданы таблицы Козьемолоко и Коровьемолоко. Формируем непосредственно выпадающие списки:

На 5 уровне формируется выпадающий список из названий полей таблицы, имя которой выбрано на 4 уровне (т.е. в ячейке C5). На 6 уровне формируется выпадающий список из элементов таблицы, имя которой выбрано на 4 уровне (ячейке C5), поля, выбранного на 5 уровне (ячейка С6).

Итак, задача выполнена – сформирована система из 6 уровней связанных выпадающих списков Excel. Стоит отметить главное преимущество приведенной методики разработки выпадающих списков – на выходе получается полностью динамическая система, т.е. в любые таблицы можно добавлять как новые поля, так и новые элементы и они автоматически «подтягиваются» в выпадающие списки. С таким же успехом можно добавлять и новые уровни, углубляя иерархию, – количество таких уровней не ограничено.

Как можно заметить, формулы для формирования таких выпадающих списков достаточно просты, однако их написание может вызвать сложности, особенно если вы делаете это впервые. В нашей надстройке PowerQuick для 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 не будет опубликован. Обязательные поля помечены *

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