Как Сделать Связанные Выпадающие Списки в Excel • Скачать пример
Как Сделать Связанные Выпадающие Списки в Excel
Как это выглядит
Для тех, кто знает, как работать с Проверкой данных (Data Validation) в Excel, не раз создавал выпадающие списки, но плохо разбирается в макросах, увидеть эти списки будет серьёзным испытанием для психики, особенно если вы подсунете данный файл, уже адаптированным под ваши задачи. Они пол дня потратят, чтобы понять: «КАК, чёрт возьми, ты это сделал»? 🙂
Функционал
- Создание каскадных выпадающих списков. Количество каскадов не ограничено, но увлекаться не стоит, так как это может повлиять на производительность Excel при работе с ячейками списков. 2-4 каскада более чем достаточно для типовых задач.
- В цепочке списков выделяются начальный список, так как он ни от кого не зависит, и конечный список, так как от него никто не зависит. Промежуточные списки всегда от кого-то зависят и всегда являются для кого-то родительскими. Родительский список передаёт своему наследнику своё значение и тот отображает список следующего этапа уже фильтруя возможные варианты по родительскому значению.
- При изменении значения ячейки-списка вышестоящего уровня, нижестоящие (зависимые) ячейки очищаются.
- При переходе на ячейку, содержащую список, он автоматически открывается, что упрощает и ускоряет выбор значения. Если в выборе нет необходимости, то следует нажать Esc.
Скачать пример
Как это настроить для вашего файла с нуля?
Шаг 1
Создайте книгу Excel, которая поддерживает макросы (*.XLSM)
- Первый — место, где будут находиться ваши каскадные списки. Я далее буду называть этот лист FRONT , а вы можете дать ему имя, которое захотите
- Второй — место для таблицы-справочника, столбцы которого будут отображать списки. Этот лист я буду называть REF . Вы опять можете дать другое имя листа, но немного проще будет оставить это.
- Третий — вспомогательный лист, назовём его BACK . Имя менять не рекомендую, так как этот лист нам предстоит всё равно сделать скрытым.
Шаг 2
На листе REF создайте таблицу-справочник по аналогии с нашим примером. Таблица не должна содержать пустых ячеек. Первая колонка должна использоваться для списков первого уровня, вторая — для второго и так далее. Вас не должно смущать, что значения в старших колонках могут повторяться — макрос отфильтрует все дубликаты, показывая лишь уникальные значения.
- Встаньте на любую ячейку внутри справочника,
- выберите в ленте на вкладке ГЛАВНАЯ пункт Форматировать как таблицу ,
- выберите понравившейся шаблон таблицы, укажите есть ли строка с заголовками, нажмите OK
- в ленте на вкладке КОНСТРУКТОР (курсор должен стоять на ячейке таблицы), перейдите в поле ввода Имя таблицы и дайте нашей таблице имя, например Reference
Шаг 3
Создадим именованный диапазон, который будет ссылаться на только что созданную умную таблицу. В сущности, умная таблица нам нужна только для того, чтобы наш именованный диапазон автоматически расширялся при добавлении новых строк / столбцов в справочник.
- В ленте выберите вкладку ФОРМУЛЫ , пункт Диспетчер имен
- Нажмите кнопку Создать.
- Дайте имя rngRef , Область оставьте Книга , в качестве Диапазона укажите =Reference[#Данные] (=Reference[#Data])
- Нажмите OK
Шаг 4
На листе FRONT наметьте, где будут располагаться ваши списки выбора. Не используйте для этого ячейку A1 .
Шаг 5
- В ячейку A1 листа BACK внесите имя созданного вами именованного диапазона, то есть rngRef
- В те же ячейке на листе FRONT , где у вас будут выпадающие списки, но только на листе BACK необходимо занести данные в определенном формате
- Данные в каждой ячейке будут состоять из трёх частей, разделенных точкой с запятой, например » 2 ; A2 ; C2 » или » 1 ;; B3 «
- Первая часть (до первого символа «;») означает номер столбца в вашем справочнике, который (столбец) будет использован для формирования значений списка.
- Вторая часть (между двумя символами «;») означает адрес родительской ячейки. У списков, являющихся первыми в каскаде, на этом месте всегда пустота, так как у них нет родительских ячеек.
- Третья часть (справа от второго символа «;») означает адрес ячейки, которая зависит от текущей. У списков, которые замыкают каскад, на этой позиции всегда пустота, например, » 3 ; B4 ;»
- Скройте лист BACK (правой кнопкой мыши по ярлыку листа, далее выбрать пункт Скрыть )
Шаг 6 (последний)
- Откройте редактор Visual Basic через комбинацию клавиш Alt+F11
- Слева, в окне с именем Project — VBAProject найдите лист FRONT вашей книги (не забудьте, что вы могли его назвать иначе)
- Сделайте двойной щелчок мышью по имени данного листа
- Скопируйте этот код с сайта в редактор кода Visual Basic (правая часть экрана)
- Если вы лист REF назвали иначе, то найдите строку Const con_wsRef = «REF» и исправьте стандартное имя листа в кавычках на ваше (это в 8-й строке).
Как это работает?
Если макрос видит, что пользователь встал на ячейку, в которой должен быть список, то он проверяет, от какой ячейки текущая ячейка зависит, если родительская ячейка пуста, то макрос ничего не делает, а если она содержит значение, то формируется набор значений для выпадающего списка и подставляется через механизм Проверки данных (Data Validation)
Выше вы можете найти код макроса, но я рекомендую его изучать в редакторе VBA в Excel, так как никак не поборю отсутствие отступов в коде VBA на сайте. Надеюсь, статья вам понравилась. Удивите своих коллег крутыми каскадными выпадающими списками в своих файлах! 🙂
Обновление!
Как сделать выпадающий список в Excel
- В ячейку A1 листа BACK внесите имя созданного вами именованного диапазона, то есть rngRef
- В те же ячейке на листе FRONT , где у вас будут выпадающие списки, но только на листе BACK необходимо занести данные в определенном формате
- Данные в каждой ячейке будут состоять из трёх частей, разделенных точкой с запятой, например » 2 ; A2 ; C2 » или » 1 ;; B3 «
- Первая часть (до первого символа «;») означает номер столбца в вашем справочнике, который (столбец) будет использован для формирования значений списка.
- Вторая часть (между двумя символами «;») означает адрес родительской ячейки. У списков, являющихся первыми в каскаде, на этом месте всегда пустота, так как у них нет родительских ячеек.
- Третья часть (справа от второго символа «;») означает адрес ячейки, которая зависит от текущей. У списков, которые замыкают каскад, на этой позиции всегда пустота, например, » 3 ; B4 ;»
- Скройте лист BACK (правой кнопкой мыши по ярлыку листа, далее выбрать пункт Скрыть )
5. Для задания параметров списка нажмите кнопку «Свойства» того же раздела. Откроется окно настроек «Properties». Обе вкладки этого окна содержат одни и те же настройки, рассортированные в первом случае — по алфавиту, во втором — по категориям.
Публикуя свою персональную информацию в открытом доступе на нашем сайте вы, даете согласие на обработку персональных данных и самостоятельно несете ответственность за содержание высказываний, мнений и предоставляемых данных. Мы никак не используем, не продаем и не передаем ваши данные третьим лицам.