Как создать динамические каскадные списки в 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. После этого все уникальные значения будут выбраны в столбце. Пожалуйста, скопируйте их и вставьте в новое место. Смотрите скриншот:

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?