В прошлой статье мы рассматривали теорию создания многоуровневых (каскадных) выпадающих списков в 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 есть удобный модуль по разработке систем выпадающих списков, с помощью которого можно быстро сформировать нужные формулы с учетом наименований таблиц и полей:
Узнать подробнее, как это делается, можно из следующего видео:
Здесь вы можете скачать нашу надстройку по быстрой разработке многоуровневых выпадающих списков: