Главная » Статьи » Excel » Макросы и программы VBA

Каскадные выпадающие списки

Введение

Несколько дней назад ко мне поступил вопрос от бывшего коллеги, как реализовать в Excel каскадные выпадающие списки, чтобы то, что вы выбрали в первом списке, определяло то, что вы можете выбрать во втором списке и так далее. Тема выпадающих списков так давно и так плотно засижена популяризаторами Excel, что я не сомневался, что с лёгкостью найду нужный рецепт, но не тут то было. Рецепт я нашёл, но он мне не очень понравился из-за своей громоздкости, повторить такое пользователю со средней квалификацией будет достаточно трудно. Хотя способ внушает уважение, так как реализован только формулами рабочего листа. Однако мне пришёл в голову ещё один способ, который мы с вами сейчас и разберем. Само-собой я поделюсь примером, который вы легко сможете адаптировать под свои нужды.

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

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

Функционал

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

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

CascadedLists.zip

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

Шаг 1

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

Создайте 3 листа:

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

Шаг 2

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

Сделайте эту таблицу "умной". Для этого:

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

Шаг 3

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

Для создания именованного диапазона:

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

Шаг 4

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

Шаг 5

Теперь необходимо настроить лист BACK. Для этого:

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

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

Нам необходимо записать макрос в нашу созданную книгу

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

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

В Excel в стандартные объекты (листы, ячейки), с которыми мы работаем, встроены пользовательские обработчики событий, которые VBA программист может легко использовать в своих целях. Вот мы и использовали событие рабочего листа FRONT, которое называется SelectionChange. Это означает, что при каждом изменении текущей ячейки на листе вызывается процедура Worksheet_SelectionChange, в которую мы поместили свой код. Этот код ничего не делает, пока пользователь не встанет на какую-нибудь ячейку, которая определенным образом помечена на листе BACK.

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

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

Обновление!

Все, кто пытался прикрутить данную реализацию каскадных списков к более-менее обширным данным, сталкивался с одной печальной ситуацией: всё вроде бы работает, но после переоткрытия файла Excel вам с прискорбием сообщает, что ваш файл повреждён и предлагает его восстановить, что успешно и делает. Возникает это из-за того, что существует ограничение в 255 символов на строку, которую мы прописываем в правило проверки данных для реализации выпадающего списка. Когда мы создаём этот список программно, то Excel на это не ругается (в отличие от попытки это сделать через стандартное диалоговое окно). Не ругается он на это и при сохранении файла, а вот при открытии - ой. В связи с этим у меня возникла идея не хранить в ячейке правило валидации вообще. То есть на событие SelectionChange мы налету формируем выпадающий список, а на событие Change мы этот список удаляем, так как пользователь уже выбрал интересовавшее его значение. Однако, если пользователь не осуществляет выбор значения из списка, то событие Change не возникает и правило контроля данных остаётся, что опять несёт риск "повреждения" файла. Поэтому приходится идти на такой извращенческий приём, как перебор всех правил контроля данных на листе и удаление "наших" (те, которые коррелируют по расположению с листов BACK). Сейчас я прикрутил это к событиям листа Activate / Deactivate. Если у вас на листе много ячеек, использующих правила валидации данных, но которые не связаны с каскадными выпадающими списками, то, данное решение я, пожалуй, вам НЕ порекомендую использовать.

Скачать версию 0.02

Читайте также:

Категория: Макросы и программы VBA | Добавил: dsb75 (03.06.2014) | Автор: Батьянов Денис E W
Просмотров: 37173 | Комментарии: 84 | Теги: Выпадающие списки, Worksheet_Change, Worksheet_SelectionChange, data validation, dropdown list, каскадные списки | Рейтинг: 5.0/1
Всего комментариев: 831 2 3 »
0
81 a-loboda   (03.09.2019 15:11) [Материал]
Еще раз здравствуйте форумчане!
Дурная голова рукам покоя не дает: увидел подобную ситуацию у dents и надеюсь что решение подойдет и для моей.
Проверю отпишусь.
Спасибо!

0
80 a-loboda   (03.09.2019 15:06) [Материал]
Здравствуйте форумчане.
Нашел данное решение и подумал, что вопрос выпадающих списков для моей ситуации решен.
Но нет - в моем варианте (применительно к исходному файлу) в каждом регионе может быть центр продаж с повторяющимся названием. А вот все филиалы имеют уникальные названия.
При выборе региона предлагается выбрать центр продаж, но так как центров продаж с одним названием получилось несколько в разных регионах, предлагается выбор из большего количества филиалов.
Не соображу как сделать чтобы выбор филиалов предоставлялся только из удовлетворяющих условиям выбранного региона и центра продаж.

0
78 Diana1234   (25.03.2019 10:06) [Материал]
Добрый день! Подскажите - использовал представленный вами код для организации каскадных выпадающих списков.
При открытии файла появляется ошибка:
1.Обнаружено содержимое которое не удалось прочитать. Попробовать восстановить файл.
а затем после восстановления:
1.Удаленное свойство: Проверка данных из части /xl/worksheets/sheet1.xml

Помогите устранить данную ошибку.
И еще когда пытаюсь посмотреть выпадающий список при каждом выборе из категорий мне выдает ошибку "Method range of object Worksheet failed и Subscripte out of Range

0
79 irakitin2014   (25.03.2019 10:48) [Материал]
Добрый день. Задайте вопрос на форуме. без вашего файла дать вразумительный ответ трудно

0
82 a-loboda   (03.09.2019 15:22) [Материал]
Добрый день, Диана.
Смотрите файл версии 0.02, там исправлено.

74 zedro   (31.10.2018 15:18) [Материал]
добрый день. спасибо за статью.
подскажите, пожалуйста, с чем могут быть связаны проблемы с использованием четвертого столбца с вкладки ref? с первых трех все "выпадает", как нужно, а на ячейке, где должен раскрываться список значений из четвертого - выпадает пустая область.
возможно, приведенный код актуален только для трех уровней?
буду благодарен за любую информацию

0
75 irakitin2014   (31.10.2018 15:37) [Материал]
Цитата
Количество каскадов не ограничено
Если честно лично я пока не знаю как настроить это "неограничено".Задайте вопрос на форуме. Приложите файл-пример. Посмотрим, подумаем.

76 zedro   (31.10.2018 18:04) [Материал]
понятно, сейчас попробую. спасибо

77 zedro   (31.10.2018 18:05) [Материал]
_

72 Катрин   (11.10.2018 12:54) [Материал]
Спасибо большое за статью! Очень нужно и очень вовремя! smile

0
73 irakitin2014   (11.10.2018 13:08) [Материал]
Всегда готовы помочь!!!

70 mild   (11.10.2018 11:45) [Материал]
Как сделать что бы выпадающий список формировался не в ячейке А2 на листе Фронт? Спасибо!

0
71 irakitin2014   (11.10.2018 12:21) [Материал]
И вам здравствуйте. Я думаю ваш вопрос следовало бы задать на форуме. И не забудьте приложить файл-пример.

67 noswall1992   (13.12.2017 10:01) [Материал]
Выбираю первый параметр-работает, второй-работает, а на третий некоторые группы выходят,а на некоторые выходит только название столбца. проверил базу там всё нормально.

0
68 irakitin2014   (13.12.2017 11:39) [Материал]
без вашего файла сказать трудно, почему так. попробуйте на форуме создать тему, приложите файл. посмотрим

69 noswall1992   (13.12.2017 11:41) [Материал]
Меня антиспам не пропускает. пробовал

66 BiznessDronss   (27.03.2017 21:08) [Материал]
Добрый день! а как можно реализовать вторую группу выпадающего списка не зависящей от первого выпадающего списка

60 noswall1992   (14.03.2017 05:52) [Материал]
Здравствуйте. Вот файл.Шаблон это Front. База есть, нужно чтоб к номеру заказа выдавал списки

-1
61 irakitin2014   (14.03.2017 07:09) [Материал]
Доброе утро. файл можно выложить на форум, создав новую тему с вашим вопросом

62 noswall1992   (14.03.2017 07:12) [Материал]
Не могу привязать файл

0
63 irakitin2014   (14.03.2017 07:27) [Материал]
я же вам написал: на форуме надо создать новую тему, там есть возможность прикрепить файл
http://perfect-excel.ru/forum/8

64 noswall1992   (14.03.2017 07:32) [Материал]
Антиспам не пропускает

0
65 irakitin2014   (14.03.2017 07:50) [Материал]
странно

58 noswall1992   (13.03.2017 13:40) [Материал]
Срабатывает только в первом столбце, остальные почему то не выводит

0
59 irakitin2014   (13.03.2017 15:06) [Материал]
Попробуйте на форум кусочек файла выложить с ошибкой

1-10 11-20 21-25
Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Яндекс.Метрика