Выпадающие списки являются очень популярным и востребованным инструментом MS Excel. Эта статья является продолжением видео «Каскады связанных выпадающих списков Excel», которое вы можете найти на канале SubEx YouTube, однако представляет более прогрессивную технологию создания каскадов выпадающих списков, а именно использование для этих целей «умных таблиц». Одновременно статья является презентацией нового блока по выпадающим спискам надстройки SubEx. При разработке данного блока команд основной целью было создать простой метод создания многоуровневых выпадающих списков -  чтобы они были динамическими, то есть чтобы можно было исходные данные обновлять и эти данные подвязывались бы автоматически в выпадающие списки.

 

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

 

Это является достаточно серьезным ограничением при создании больших проектов, поскольку, как правило, при работе с большими объемами данных все-таки необходимо создавать именно динамические диапазоны. Эта проблема легко и быстро решается, если создавать связанные выпадающие списки на основе «умных таблиц». Сейчас мы с  пошагово проделаем эту операцию, и посмотри команды Subex по этой теме. В конце урока у нас  получится пятиуровневый выпадающий список на основе университетской структуры, то есть  будет поле «Факультет» после выбора которого откроется с выбором курса, потом с выбором потока, потом с выбором группы и пятый уровень – это выбор конкретного студента.

Основа метода заключается в организации исходных данных на основе «умных таблиц».

 

Представим, что в нашем институте два факультета: факультет психологии и факультет журналистики. Создадим «умную таблицу» на основе исходных данных.

Каскады связанных выпадающих списков в Excel

Из этой статьи вы узнаете о простых способах создания каскадов (многоуровневых) выпадающих списков в Excel на основе "умных таблиц" при помощи надcтройки SubEx.

Видеоурок:

Связанные выпадающий списки Excel
Связанные выпадающий списки Excel
Связанные выпадающий списки Excel
Связанные выпадающий списки Excel

(транскрибация видеоурока)

Связанные выпадающий списки Excel

Идем далее на второй уровень. На психологии, предположим, что у нас два курса: на первом курсе - два потока, на втором курсе - один поток. На факультете журналистики у нас тоже два курса: также на первом курсе – два потока, на втором курсе - один поток. Таким образом, мы создали уже три уровня.

Идем дальше. Каждый поток состоит из нескольких групп, а группа, в свою очередь, состоит из какого-то количества студентов. В примере студенты номерами, т.к. проблематично придумывать столько имен. Выделяем таблицы со студентами и группами, через Ctrl выделяем имя потока. Нажимаем «Создать таблицу» (во вкладке надстройки SubEx «Умные таблицы»). Ту же самую операцию проделываем со всеми потоками.

 

Итак, мы создали 5 уровней исходных данных:

1 уровень - это факультеты

2 уровень – это курсы факультетов

3 уровень -  это потоки курсов

4 уровень – это группы потока

и 5 уровень -  это студенты группы.

 

Приступаем к формированию самих связанных списков.

Для первого уровня нам необходимо создать выпадающий список с факультетами. Обращаемся к блоку «Умные таблицы» надстройки Subex – кнопка «Простой выпадающий список». Здесь третье меню сверху - создать выпадающий список из элементов таблицы «Факультеты», поле «Факультет». Можем видеть, выпадающий список с факультетами был создан. Задача данной функции в том, чтобы вставить в поле проверку данных-источник вот такую формулу:

=ФАКУЛЬТЕТЫ[ФАКУЛЬТЕТ]

 

То есть она берет элементы данных из поля «Факультет» таблицы «Факультет».

Теперь мы создадим связанный выпадающий список из курсов факультета психологии. Заходим в группу «Выпадающие списки» -  «Умные таблицы»  - «Связанный выпадающий список». Здесь выбираем двухуровневый список – «Список полей Таблицы (связь Таблица-поле)» и в качестве таблицы указываем поле «Психология».

Данная команда формирует формулу =ДВССЫЛКА(A4&”[#Заголовки]”), которую необходимо вставить в меню проверки данных в качестве источника для выпадающего списка. Нажав кнопку «Закрыть, открыв Проверку данных» у нас автоматически открывается «Проверка вводимых значений».  Мы просто копируем и вставляем сюда эту формулу.

Итак, для факультета психологии у нас открываются 2 курса. Для факультета «Журналистика» у нас также два курса.

Теперь давайте создадим список потоков. Вызываем ту же самую команду. Только здесь мы применяем уже трехуровневый список и задаем следующие исходные данные: мы должны вывести список элементов таблицы «Журналистика», поле «Курс_1».  Копируем формулу, вставляем в  меню «Проверка данных». Мы видим, что на «Курс_1» «Журналистика» у нас два потока - 4 и 5, так же, как и в исходных данных. Выберем «Поток_4».

Давайте теперь создадим список групп 4-го потока. У нас потоки имеют уникальные названия. Нам достаточно двухуровневого списка – сослаться просто на имя «Поток_4» - и у нас выпадут названия полей таблицы «Поток_4». Для «Поток_4» видим пять групп – начинается с «Группа_12». Для «Поток_4» - 12-ая группа.

И последний шаг  - это подвязка списка студентов. Здесь нам уже необходим трехуровневый список. Мы должны для таблицы «Поток_4», поле «Группа_12» сформировать список студентов. Копируем формулу в проверку данных. Таким образом, мы сформировали пятиуровневые выпадающие списки.

А теперь самое интересное. Как я уже говорил в начале данного урока, основным преимуществом этого метода создания выпадающих списков является то, что эта структура является полностью динамической. Давайте посмотрим, что это значит.

 

Допустим, мы хотим в какую-то группу  добавить студента. Мы добавили студента в «Группа_8» «Поток_3». «Поток_3» у нас на факультете психологии.

Давайте посмотрим, добавятся нам наши списки: факультет «Психология» - «Курс_2» - «Поток_3» - «Группа_8» - добавился студент «Иванов И.И.».

Предположим, что мы хотим добавить группу на поток. Это также легко сделать. Например, добавляем группу под именем «Гркппа_7а» в «Поток_2». Автоматически у нас расширились границы «умной таблицы». Ставим сюда двух студентов  - «Петров А.А.» и «Сидоров И.И.». «Поток_2» у нас находится в составе факультета «Психология» «Курс_1». Факультет «Психология», меняем здесь «Курс_1», выбираем «Поток_2», выбираем «Группа_7а», которая уже подцепилась в наш список, и выбираем двух студентов «Петрова А.А.» или «Сидорова И.И.»

Давайте пойдем дальше и попробуем создать еще один поток. Например, это будет «Поток_7».

Соответственно группа будет «Группа_27» и «Группа_28». Здесь будет по одному студенту – «Николаев» и «Воробьев». Не забудем создать «умную таблицу» потока 7. Теперь нам нужно «Поток_7» привязать к какому-то курсу. Давайте привяжем «Поток_7» к «Курс_2» факультет «Журналистика». Выбираем здесь факультет «Журналистика» - «Курс_2», видим, что у нас подцепился «Поток_7». В «Поток_7» выбираем одну из добавленных нами групп, видим, что добавился наш новый студент «Воробьев». В «Группа_27» соответственно другой студент.

 Дальше я уже не буду показывать, но точно таким способом можно создать еще один факультет.

Таким образом, мы создали абсолютно динамическую структуру, которая позволяет как добавлять новые записи в систему, так и новые признаки в  систему. Мы ограничились пятиуровневой структурой, однако ограничений здесь никаких нет - просто увеличивается сложность оперирования исходными данными.

Обратите внимание!

На сайте вышли новая статья и видеоурок по данной теме!

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

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

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

 All rights reserved

Логотипы Excel и

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