Как создать динамические каскадные выпадающие списки в Excel?
Возможно, вы уже умеете создавать каскадные раскрывающиеся списки с помощью проверки данных в Excel. Но знаете ли вы, как сделать динамические каскадные списки? В этой статье мы расскажем, как решить эту задачу с помощью VBA.
Создание динамических каскадных полей со списком с помощью кода VBA
Создание динамических каскадных полей со списком с помощью кода VBA
Как показано на снимке экрана ниже, создайте родительское поле со списком, заполненное уникальными значениями из столбца «Напиток», и настройте второе поле со списком так, чтобы оно отображало соответствующие значения в зависимости от выбора в родительском поле. Приведённый ниже код VBA поможет вам реализовать эту функциональность. Выполните следующие действия.
1. Сначала извлеките все уникальные значения из столбца «Напиток». Выберите пустую ячейку, введите в строку формул массивную формулу =IFERROR(INDEX($A$2:$A$11, MATCH(0,COUNTIF($J$1:J1, $A$2:$A$11), 0)),«»), затем нажмите клавиши Ctrl + Shift + Enter. После этого перетащите маркер заполнения, чтобы получить все уникальные значения. См. снимок экрана:
Примечание: в формуле $A$2:$A$11 — это диапазон, из которого будут извлечены уникальные значения. J1 — ячейка, расположенная непосредственно над ячейкой с формулой.
Совет: если формулу сложно запомнить и применять, воспользуйтесь функцией Выбрать дубликаты/уникальные ячейки из набора инструментов Kutools для Excel, чтобы быстро извлечь все уникальные значения из столбца.
Выберите столбец с уникальными значениями, которые нужно извлечь. Затем активируйте функцию, последовательно выбрав Kutools > Выделить > Выбрать дубликаты/уникальные ячейки. В открывшемся диалоговом окне Выбрать дубликаты/уникальные ячейки отметьте опцию Уникальные значения (включая первый дубликат) и нажмите кнопку ОК. После этого все уникальные значения в столбце будут выделены — просто скопируйте их и вставьте в нужное место. См. снимок экрана:
Kutools для Excel: более чем 200 удобных надстроек для Excel, бесплатная пробная версия без ограничений на 60 дней.Скачайте и начните бесплатный пробный период прямо сейчас!
2. Вставьте два поля со списком по отдельности, последовательно выбрав Разработчик > Вставить > Поле со списком (элемент управления ActiveX). См. снимок экрана:
3. Щёлкните правой кнопкой мыши родительское поле со списком и выберите в контекстном меню пункт Свойства. В диалоговом окне Свойства измените значение поля (Имя) на Drink или другое нужное имя, укажите диапазон ячеек с извлечёнными уникальными значениями в поле ListFillRange и закройте диалоговое окно.
4. Повторите шаг 3, чтобы изменить имя второго поля со списком на «Item» в поле (Имя) в диалоговом окне Свойства.
5. Щёлкните правой кнопкой мыши ярлык листа и выберите в контекстном меню Просмотреть код. Затем скопируйте приведённый ниже код VBA в окно кода. См. снимок экрана:
Код VBA: создание динамических каскадных полей со списком в Excel
Dim xPreStr As String
Private Sub Drink_Click()
'Update by Extendoffice 2018/06/04
Dim I, xRows As Long
Dim xRg As Range
Dim xRegStr As String
Application.ScreenUpdating = False
xRegStr = Me.Drink.Text
Set xRg = Range("A2:A11")
xRows = xRg.Rows.Count
If xRegStr <> xPreStr Then
Me.Item.Clear
'Me.OtherListBoxName.Clear
Set xRg = xRg(1)
For I = 1 To xRows
If xRg.Offset(I - 1).Value = xRegStr Then
Me.Item.AddItem xRg.Offset(I - 1, 1).Value
'Me.OtherListBoxName.AddItem xRg.Offset(I - 1, 2).Value
End If
Next
xPreStr = xRegStr
End If
Application.ScreenUpdating = True
End SubПримечание: в коде Drink и Item — это имена двух полей со списком; замените их на свои.
6. Нажмите клавиши Alt+Q, чтобы закрыть окно Microsoft Visual Basic для приложений.
7. Отключите режим конструктора, последовательно выбрав Разработчик > Режим конструктора.
Теперь при выборе любого напитка — например, Кофе — во втором поле со списком автоматически отобразятся все соответствующие кофейные товары. Если выбрать Чай или Вино, во втором поле появятся только товары чая или вина соответственно. См. снимок экрана:
Связанные статьи:
- Как очистить ячейку зависимого раскрывающегося списка после изменения выбора в Excel?
- Как создать зависимые каскадные раскрывающиеся списки в Excel?
- Как автоматически заполнять другие ячейки при выборе значений в раскрывающемся списке Excel?
- Как создать календарь в виде раскрывающегося списка в Excel?
- К ак сохранить или оставить выделение в элементах управления «Поле со списком» ActiveX в Excel?
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек