Note: The other languages of the website are Google-translated. Back to English

Как создать динамические каскадные списки в Excel?

Возможно, вы знаете, как создать раскрывающийся список каскадной проверки в Excel. Однако как создать динамические каскадные списки в Excel? Эта статья знакомит с методом VBA, чтобы избавиться от этого.

Создавайте динамические каскадные списки с кодом VBA

Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу ...
Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%
  • Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
  • Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
  • Инструменты слияния: Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
  • Разделить инструменты: Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
  • Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона; Отправляйте персонализированные электронные письма нескольким получателям массово.
  • Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий ...
  • Более 300 мощных функций; Работает с Office 2007-2021 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

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

Выберите столбец, содержащий уникальные значения, из которых вы будете извлекать данные. Затем включите утилиту, нажав Кутулс > Выберите > Выберите повторяющиеся и уникальные ячейки. В Выберите повторяющиеся и уникальные ячейки в диалоговом окне выберите Все уникальные (включая 1-е дубликаты) и нажмите OK кнопка. Затем в столбце выбираются все уникальные значения. Скопируйте и вставьте их на новое место. Смотрите скриншот:

Kutools for 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

Заметки: В коде Drink и Item - названия двух списков, измените их на свои собственные.

6. нажмите другой + Q ключи, чтобы закрыть Microsoft Visual Basic для приложений окно.

7. Выключите режим дизайна, нажав разработчик > Режим проектирования.

С этого момента при выборе любого напитка, например, кофе, в родительском списке все кофейные элементы будут отображаться во втором. При выборе «Чай» или «Вино» во втором списке отображаются только чай или вино. Смотрите скриншот:


Статьи по теме:


Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

  • Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
вкладка kte 201905

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Сортировать комментарии по
Комментарии (1)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
Hi

Что означает «Я» в коде и как изменится код, если у меня есть окно со списком MultiSelect?

С уважением
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места

Подписывайтесь на Нас

Copyright © 2009 - www.extendoffice.ком. | Все права защищены. Питаться от ExtendOffice, | Карта сайта
Microsoft и логотип Office являются товарными знаками или зарегистрированными товарными знаками Microsoft Corporation в США и / или других странах.
Защищено Sectigo SSL