Главная » Статьи » 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
Просмотров: 14075 | Комментарии: 67 | Теги: Выпадающие списки, Worksheet_Change, Worksheet_SelectionChange, data validation, dropdown list, каскадные списки | Рейтинг: 5.0/1
Всего комментариев: 661 2 »
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)
Попробуйте на форум кусочек файла выложить с ошибкой

57 angelprosvetleniya   (11.10.2016 13:35)
Добрый день.

Огромное спасибо за наглядный и лёгкий в освоении материал!!!
Пытаюсь адаптировать свой файл под рабочие нужды, не совсем получается(.

Подскажите пожалуйста, что необходимо поправить, чтобы списки выпадали не с ячейки А2, а например с ячейки J9.

Заранее благодарю!

56 gris2002   (19.07.2016 09:51)
Добрый день. Пытаюсь соединить данный пример с умной таблицей на листе Front, но не получается, выдает ошибку "Application-defined or object-defined error". Это проблема из-за умной таблицы, они не совместимы?

54 polguev   (20.01.2016 16:44)
Добрый день. Очень хочу воспользоваться вашими списками да вот проблема они не работают на защещенной странице а это принципиально. Момогите решить проблему. Зарание спасибо.

55 irakitin2014   (21.01.2016 07:21)
повторюсь:
Цитата
для таких вопросов есть форум.лучше обратится за помощью туда.и приложить не ссылку на файлообменник а кусок реального файла

53 irakitin2014   (21.12.2015 09:09)
GAS для таких вопросов есть форум.лучше обратится за помощью туда.и приложить не ссылку на файлообменник а кусок реального файла

52 GAS   (15.12.2015 15:32)
Благодарю за качественный  и очень нужный материал.
У меня проблема с продлением списка на листе FRONT, копирование ячеек не получается. Продление списка только через изменение листа BACK, что очень неудобно. Окажите содействие  неопытному пользователю smile

0
45 Kokonoko   (27.10.2015 11:54)
Добрый день, Денис!
Спасибо, за свежую статью.

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

Вы не знаете какие ещё ограничения (например длин строк) приводят к поломке файлов после сохранения? Периодически приходится работать с файлами заказчика, и при пере сохранении они крашаться. У меня рабочий Office 2010, и поломанные файлы я обычно лечу открытием в Office 2007 и пере сохранением из него (не знаю почему, но это работает).

0
46 dsb75   (27.10.2015 19:54)
Здравствуйте!
Ну скажете тоже свежую, это всего лишь апдейт.
Кроме вышеописанного раза, я ни разу не сталкивался с новыми ограничениями, которые бы не были описаны в Excel Specification.
А данное ограничение в спецификации отсутствует.
Ещё Павлов писал про ограничение в 8K на Data Validation List, однако, оно оказалось гораздо строже.

0
47 Kokonoko   (28.10.2015 10:37)
Спасибо.
В ограничениях ничего нового не нашёл.
В моём случае, скорее всего, тоже что-то неописанное.

38 ИФ   (02.09.2015 14:21)
Здравствуйте!
Файл работает, все хорошо. Но при каждом открытии возникает ошибка: "Удаленное свойство: Проверка данных из части /xl/worksheets/sheet1.xml". К тому же выпадающие списки, которые были настроены в других ячейках - пропадают. Подскажите, пожалуйста, как можно решить эту проблему?

0
40 dsb75   (03.09.2015 20:37)
Обращались ко мне с подобной неприятностью. Судя по всему, ошибка в программе, то хоть убейте, не понимаю где... и боюсь, что в ближайшее время не помогу.

0
41 ИФ   (04.09.2015 08:41)
Нашел некое решение и хотел бы им поделиться: сохраните файл в формате .xls. Тогда ошибки при открытии не будет. Но будет сообщение о проверке совместимости в конце. Чтобы работала функция "Проверка данных" нужно диапазону ячеек, который вы хотите превратить в список, присвоить имя через "Диспетчер имен", и указать это имя в "Проверке данных". Ссылка на статью, которой пользовался (не сочтите за рекламу):
 http://www.planetaexcel.ru/techniques/1/40/

0
42 dsb75   (04.09.2015 10:35)
Идея с XLS интересная.
А вот именованные диапазоны тут не подойдут, так как у меня в каждой ячейке может быть разный список, их может быть очень много и создавать динамически какие-то именованные диапазоны вообще не вариант.
У меня про эти именованные диапазоны у самого статей как грязи, да и других вариантов каскадных списков smile

0
43 ИФ   (04.09.2015 14:05)
Тем не менее, когда я вносил в "Проверку данных" просто диапазон ячеек, после сохранения и повторного открытия документа выпадающий список пропадал. Когда ввел названия - это прекратилось.

0
44 dsb75   (21.10.2015 12:55)
ИФ, проблема, которую вы поднимали, решена. Смотрите версию 0.02.

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