Главная » Статьи » 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
Просмотров: 11824 | Комментарии: 58 | Теги: Выпадающие списки, Worksheet_Change, Worksheet_SelectionChange, data validation, dropdown list, каскадные списки | Рейтинг: 5.0/1
Всего комментариев: 571 2 »
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.

0
31 ИФ   (26.08.2015 12:38)
Добрый день!
 Большое спасибо за столь подробную статью! У меня каскад состоит из 2 колонок, для первый колонки значения проставляются корректно, по после этого возникает ошибка "Subscript out of range". Подскажите, пожалуйста, в чем может быть причина? Заранее спасибо!

0
32 dsb75   (26.08.2015 13:41)
Проверьте, что вы корректно настроили лист BACK

33 ИФ   (27.08.2015 12:17)
Настроил как было описано в статье. Единственное, у меня каскад начинается с ячейки D7. Когда сдвигаю все таблицы так, как в Вашем примере - все работает. Когда сдвигаю так, как надо мне - получаю указанную ошибку. Возможно, дело в коде?
И после сохранения и открытия файла он перестает работать вообще.

0
34 dsb75   (27.08.2015 21:15)
Присылайте файл

35 ИФ   (28.08.2015 14:38)
Добрый день!
Отправил в личку.

0
36 dsb75   (28.08.2015 18:43)
Ну ясен пень, лист Back вообще левый. Вчитайтесь КАК его надо оформлять и зачем. На какие колонки A и B вы ссылаетесь, зачем? Там у вас нет никаких выпадающих списков.

37 ИФ   (02.09.2015 11:38)
Спасибо, разобрался! Сбило то, что в описанном примере справочник и список на листе Front находятся в тех же ячейках. Еще раз спасибо! smile

0
39 dsb75   (03.09.2015 20:35)
Это хорошо

0
29 irakitin2014   (30.07.2015 15:05)
Это только у меня коммнты расплагаются не по порядку поступления ,а как им заблагорассудится?

0
30 dsb75   (30.07.2015 15:38)
Они по типу ЖЖ - ветками

15 Yra   (30.07.2015 09:36)
Добрый день! Подскажите - использовал представленный вам код для организации каскадных выпадающих списков. В списке содержится 5-ть каскадов.
При открытии файла появляется ошибка:
1.Обнаружено содержимое которое не удалось прочитать. Попробовать восстановить файл.
а затем после восстановления:
1.Удаленное свойство: Проверка данных из части /xl/worksheets/sheet1.xml

Помогите устранить данную ошибку.

0
16 dsb75   (30.07.2015 11:12)
Как было дело? Вы взяли мой файл, настроили то, что вам надо и оно работало? А после выхода с сохранением стало говорить, что файл повреждён?

17 Yra   (30.07.2015 11:19)
взял ваш файл. Изменил таблицу с исходными данными, при этом расширил до 5-ти каскадов. изменил структуру в листе back. После этого начала появляться такая ошибка.

0
18 dsb75   (30.07.2015 11:21)
Я пересохранил архив к статье. Проделайте ваши настройки с новым файлом и, если будет наблюдаться тоже самое, то пришлите мне файл на посмотреть.

19 Yra   (30.07.2015 11:32)
попробовал с обновленным файлом - ошибка осталась. Каким образом можно направить вам мой файл для проверки?

0
20 dsb75   (30.07.2015 11:33)
На е-mail в заголовке сайта

21 Yra   (30.07.2015 11:47)
Разместил фал по данной ссылке
http://my-files.ru/bxfbs3.CascadedLists.xlsm

Большая просьба: помогите исправить ошибку. Спасибо.

0
22 dsb75   (30.07.2015 11:50)
Какой версией Excel вы пользуетесь? Полная версия.

0
23 dsb75   (30.07.2015 11:51)
У меня ваш файл открывается корректно.

0
24 Yra   (30.07.2015 11:54)
ошибка появляется при выборе новых данных.

например попробуйте в строке 4 выбрать из списков данные. затем сохраните файл, закройте, и вновь откройте - ошибка появится.
Спасибо.

0
25 dsb75   (30.07.2015 11:56)
Да, удалось воспроизвести....

0
26 Yra   (30.07.2015 12:08)
сможете помочь в решении?

0
27 dsb75   (30.07.2015 12:12)
Быстро не обещаю... После выбора в третьем каскаде появляется эта ошибка... Я знаю, что там есть ограничение в 8Kб на поле, обеспечивающее хранение списка значений для функции проверки данных, но, вроде как, вы его превышать не должны... Пока не знаю в чём проблема. Поищу на досуге.

0
28 Yra   (30.07.2015 12:14)
Спасибо большое. Буду ждать.

0
48 boot   (01.11.2015 21:36)
Добрый день.
Удалось ли побороть данную ошибку? Тоже интересует как подправить

0
49 dsb75   (02.11.2015 09:21)
Удалось. Я же выложил 0.02.

50 boot   (02.11.2015 09:56)
Добрый день.
Если список длинный и в первом поле этого списка нажать, но не выбрать, то оно все равно после сохранения вылетает с ошибкой открытия(

0
51 dsb75   (02.11.2015 10:16)
Для этого у меня есть процедура CheckAllDataValidationCells, привязанная на активацию/деактивацию листа FRONT. Убедитесь, что она вызывается до (во время) сохранения вашей книги для соответствующего листа (у вас может он не FRONT).

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