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

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

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

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

 

Таким образом, каждая таблица включает в себя по 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. Стоит отметить главное преимущество приведенной методики разработки выпадающих списков – на выходе получается полностью динамическая система, т.е. в любые таблицы можно добавлять как новые поля, так и новые элементы и они автоматически «подтягиваются» в выпадающие списки. С таким же успехом можно добавлять и новые уровни, углубляя иерархию, – количество таких уровней не ограничено.

 

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

 

Узнать подробнее, как это делается, можно из следующего видео:

 

Скачайте нашу надстройку по быстрой разработке многоуровневых выпадающих списков:

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

© эксель-быстро.рф, 2016-2019

 All rights reserved

Логотипы Excel и

MS Office являются собственностью компании Microsoft