Как создать динамические каскадные списки в Excel?
Вы, возможно, знаете, как создать каскадный выпадающий список проверки данных в 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 > Выбрать > Выбор дубликатов и уникальных ячеек. В диалоговом окне «Выбор дубликатов и уникальных ячеек» выберите опцию Все уникальные (включая первый дубликат) и нажмите кнопку OK. После этого все уникальные значения будут выбраны в столбце. Пожалуйста, скопируйте их и вставьте в новое место. Смотрите скриншот:
Kutools для Excel: более 200 удобных дополнений для Excel, бесплатно для тестирования без ограничений в течение 60 дней. Скачайте и попробуйте прямо сейчас!
2. Вставьте два списка по отдельности, нажав Разработчик > Вставить > Поле со списком (Элемент управления ActiveX). Смотрите скриншот:
3. Щелкните правой кнопкой мыши родительский список и выберите Свойства в контекстном меню. В диалоговом окне Свойства измените поле (Имя) на Напиток или другое имя по необходимости, введите диапазон ячеек, содержащий извлеченные уникальные значения, в поле ListFillRange и закройте диалоговое окно.
4. Повторите шаг 3, чтобы изменить поле (Имя) второго списка на Товар в диалоговом окне Свойства.
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Примечания: В коде Напиток и Товар являются именами двух списков, измените их на свои собственные имена.
6. Нажмите клавиши Alt + Q, чтобы закрыть окно Microsoft Visual Basic for Applications.
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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек