Как Сделать Связанные Выпадающие Списки в Excel • Скачать пример

Как Сделать Связанные Выпадающие Списки в Excel

Как это выглядит

Для тех, кто знает, как работать с Проверкой данных (Data Validation) в Excel, не раз создавал выпадающие списки, но плохо разбирается в макросах, увидеть эти списки будет серьёзным испытанием для психики, особенно если вы подсунете данный файл, уже адаптированным под ваши задачи. Они пол дня потратят, чтобы понять: «КАК, чёрт возьми, ты это сделал»? 🙂

Функционал

  1. Создание каскадных выпадающих списков. Количество каскадов не ограничено, но увлекаться не стоит, так как это может повлиять на производительность Excel при работе с ячейками списков. 2-4 каскада более чем достаточно для типовых задач.
  2. В цепочке списков выделяются начальный список, так как он ни от кого не зависит, и конечный список, так как от него никто не зависит. Промежуточные списки всегда от кого-то зависят и всегда являются для кого-то родительскими. Родительский список передаёт своему наследнику своё значение и тот отображает список следующего этапа уже фильтруя возможные варианты по родительскому значению.
  3. При изменении значения ячейки-списка вышестоящего уровня, нижестоящие (зависимые) ячейки очищаются.
  4. При переходе на ячейку, содержащую список, он автоматически открывается, что упрощает и ускоряет выбор значения. Если в выборе нет необходимости, то следует нажать Esc.

Скачать пример

Как это настроить для вашего файла с нуля?

Шаг 1

Создайте книгу Excel, которая поддерживает макросы (*.XLSM)

  1. Первый — место, где будут находиться ваши каскадные списки. Я далее буду называть этот лист FRONT , а вы можете дать ему имя, которое захотите
  2. Второй — место для таблицы-справочника, столбцы которого будут отображать списки. Этот лист я буду называть REF . Вы опять можете дать другое имя листа, но немного проще будет оставить это.
  3. Третий — вспомогательный лист, назовём его BACK . Имя менять не рекомендую, так как этот лист нам предстоит всё равно сделать скрытым.

Шаг 2

На листе REF создайте таблицу-справочник по аналогии с нашим примером. Таблица не должна содержать пустых ячеек. Первая колонка должна использоваться для списков первого уровня, вторая — для второго и так далее. Вас не должно смущать, что значения в старших колонках могут повторяться — макрос отфильтрует все дубликаты, показывая лишь уникальные значения.

Как Сделать Связанные Выпадающие Списки в Excel • Скачать пример

  1. Встаньте на любую ячейку внутри справочника,
  2. выберите в ленте на вкладке ГЛАВНАЯ пункт Форматировать как таблицу ,
  3. выберите понравившейся шаблон таблицы, укажите есть ли строка с заголовками, нажмите OK
  4. в ленте на вкладке КОНСТРУКТОР (курсор должен стоять на ячейке таблицы), перейдите в поле ввода Имя таблицы и дайте нашей таблице имя, например Reference

Шаг 3

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

Как Сделать Связанные Выпадающие Списки в Excel • Скачать пример

  1. В ленте выберите вкладку ФОРМУЛЫ , пункт Диспетчер имен
  2. Нажмите кнопку Создать.
  3. Дайте имя rngRef , Область оставьте Книга , в качестве Диапазона укажите =Reference[#Данные] (=Reference[#Data])
  4. Нажмите OK

Шаг 4

На листе FRONT наметьте, где будут располагаться ваши списки выбора. Не используйте для этого ячейку A1 .

Как Сделать Связанные Выпадающие Списки в Excel • Скачать пример

Шаг 5

  1. В ячейку A1 листа BACK внесите имя созданного вами именованного диапазона, то есть rngRef
  2. В те же ячейке на листе FRONT , где у вас будут выпадающие списки, но только на листе BACK необходимо занести данные в определенном формате
  3. Данные в каждой ячейке будут состоять из трёх частей, разделенных точкой с запятой, например » 2 ; A2 ; C2 » или » 1 ;; B3 «
  4. Первая часть (до первого символа «;») означает номер столбца в вашем справочнике, который (столбец) будет использован для формирования значений списка.
  5. Вторая часть (между двумя символами «;») означает адрес родительской ячейки. У списков, являющихся первыми в каскаде, на этом месте всегда пустота, так как у них нет родительских ячеек.
  6. Третья часть (справа от второго символа «;») означает адрес ячейки, которая зависит от текущей. У списков, которые замыкают каскад, на этой позиции всегда пустота, например, » 3 ; B4 ;»
  7. Скройте лист BACK (правой кнопкой мыши по ярлыку листа, далее выбрать пункт Скрыть )

Как Сделать Связанные Выпадающие Списки в Excel • Скачать пример

Шаг 6 (последний)

  1. Откройте редактор Visual Basic через комбинацию клавиш Alt+F11
  2. Слева, в окне с именем Project — VBAProject найдите лист FRONT вашей книги (не забудьте, что вы могли его назвать иначе)
  3. Сделайте двойной щелчок мышью по имени данного листа
  4. Скопируйте этот код с сайта в редактор кода Visual Basic (правая часть экрана)
  5. Если вы лист REF назвали иначе, то найдите строку Const con_wsRef = «REF» и исправьте стандартное имя листа в кавычках на ваше (это в 8-й строке).

Как это работает?

Если макрос видит, что пользователь встал на ячейку, в которой должен быть список, то он проверяет, от какой ячейки текущая ячейка зависит, если родительская ячейка пуста, то макрос ничего не делает, а если она содержит значение, то формируется набор значений для выпадающего списка и подставляется через механизм Проверки данных (Data Validation)

Как Сделать Связанные Выпадающие Списки в Excel • Скачать пример

Выше вы можете найти код макроса, но я рекомендую его изучать в редакторе VBA в Excel, так как никак не поборю отсутствие отступов в коде VBA на сайте. Надеюсь, статья вам понравилась. Удивите своих коллег крутыми каскадными выпадающими списками в своих файлах! 🙂

Обновление!

Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Например, кто-то выбирает значение в раскрывающемся списке A, и вы хотите, чтобы значения обновлялись в раскрывающемся списке B. Если же вы хотите что-то уточнить, я с радостью помогу!
Давайте начнем с примера, чтобы лучше понять, как мы можем использовать связанные списки в Excel. Допустим, у меня есть таблица с тремя колонками, которые представляют марки автомобилей: Toyota, Honda и Nissan. Строки представляют модели:
Выпадающий список в excel 2010

Как сделать выпадающий список в Excel

  1. В ячейку A1 листа BACK внесите имя созданного вами именованного диапазона, то есть rngRef
  2. В те же ячейке на листе FRONT , где у вас будут выпадающие списки, но только на листе BACK необходимо занести данные в определенном формате
  3. Данные в каждой ячейке будут состоять из трёх частей, разделенных точкой с запятой, например » 2 ; A2 ; C2 » или » 1 ;; B3 «
  4. Первая часть (до первого символа «;») означает номер столбца в вашем справочнике, который (столбец) будет использован для формирования значений списка.
  5. Вторая часть (между двумя символами «;») означает адрес родительской ячейки. У списков, являющихся первыми в каскаде, на этом месте всегда пустота, так как у них нет родительских ячеек.
  6. Третья часть (справа от второго символа «;») означает адрес ячейки, которая зависит от текущей. У списков, которые замыкают каскад, на этой позиции всегда пустота, например, » 3 ; B4 ;»
  7. Скройте лист BACK (правой кнопкой мыши по ярлыку листа, далее выбрать пункт Скрыть )

5. Для задания параметров списка нажмите кнопку «Свойства» того же раздела. Откроется окно настроек «Properties». Обе вкладки этого окна содержат одни и те же настройки, рассортированные в первом случае — по алфавиту, во втором — по категориям.

Оставить отзыв

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