Создание динамического зависимого раскрывающегося списка в Excel (шаг за шагом)
Здесь, в этом руководстве, мы шаг за шагом представим, как создать зависимый раскрывающийся список, в котором отображаются варианты в зависимости от значений, выбранных в первом раскрывающемся списке. Другими словами, мы создадим список проверки данных Excel на основе значения другого списка.
Сделать динамический зависимый выпадающий список
10 секунд, чтобы сделать зависимый раскрывающийся список с помощью удобного инструмента
Создайте динамический зависимый раскрывающийся список в Excel 2021 или Excel 365.
Некоторые вопросы, которые вы можете задать об этом уроке
Бесплатно скачать образец файла 
Видео: создание зависимого раскрывающегося списка Excel
Сделать динамический зависимый выпадающий список
Шаг 1: введите записи для раскрывающихся списков
1. Сначала введите записи, которые должны отображаться в раскрывающихся списках, каждый список в столбце отдельно.
Уведомление что элементы в первом столбце (Продукт) позже будут именами Excel для зависимых списков. Например, здесь «Фрукты» и «Овощи» будут именами столбцов B2:B5 и C2:C6 отдельно.
Смотрите скриншот:
2. Затем создайте таблицы для каждого списка данных.
Выберите диапазон столбцов A1:A3, нажмите Вставить > Настольные, затем в диалоговом окне «Создать таблицу» отметьте В моей таблице есть заголовки флажок. Нажмите OK.
Затем повторите этот шаг, чтобы создать таблицы для двух других списков.
Вы можете просмотреть все таблицы и ссылки на диапазоны в Менеджере имен (нажмите Ctrl + F3 чтобы открыть его).
Шаг 2: Создайте имена диапазонов
На этом шаге вам нужно создать имен для основного списка и каждого зависимого списка.
1. Выберите элементы, которые отображаются в основном списке (A2: A3).
2. Затем перейдите в Поле имени что рядом Панель формул.
3. Введите в него имя, здесь оно именуется как Продукт.
4. Нажмите Enter ключ для завершения.
Затем повторите описанные выше шаги, чтобы отдельно создать имена для каждого зависимого списка.
Здесь второй столбец (B2:B5) назван «Фрукты», а третий столбец (C2:C6) — «Овощи».
Вы можете просмотреть все имена диапазонов в диспетчере имен (нажмите Ctrl + F3 чтобы открыть его).
Шаг 3: Добавьте основной выпадающий список
Затем добавьте основной раскрывающийся список (Продукт), который представляет собой обычный раскрывающийся список проверки данных, а не зависимый раскрывающийся список.
1. Во-первых, создайте таблицу.
Выберите ячейку (E1) и введите заголовок первого столбца (Продукт) и перейти к следующей ячейке столбца (F1), введите заголовок второго столбца (Товар). Вы добавите раскрывающийся список в эту таблицу.
Затем выберите эти два заголовка (E1 и F1), нажмите Вставить Вкладка и выберите Настольные в группе Таблицы.
В диалоговом окне «Создать таблицу» отметьте В моей таблице есть заголовки и нажмите OK.
2. Выберите ячейку E2 в который вы хотите вставить основной выпадающий список, нажмите Данные вкладка и перейдите к Инструменты данных группа для нажатия проверка достоверности данных > проверка достоверности данных.
3. В диалоговом окне «Проверка данных»
- Выберите Список в Разрешить раздел,
- Введите приведенную ниже формулу в Источник bar, Product — название основного списка,
- Нажмите OK.
=Product
Вы можете видеть, что основной выпадающий список создан.
Шаг 4: Добавьте зависимый выпадающий список
1. Выберите ячейку F2 к которому вы хотите добавить зависимый выпадающий список, нажмите Данные вкладку и перейдите в группу «Инструменты данных», чтобы щелкнуть проверка достоверности данных > проверка достоверности данных.
2. В диалоговом окне «Проверка данных»
- Выберите Список в Разрешить раздел,
- Введите приведенную ниже формулу в Источник bar, E2 — это ячейка, содержащая основной выпадающий список.
- Нажмите OK.
=INDIRECT(SUBSTITUTE(E2," ","_"))
Если E2 пуст (вы не выбрали ни один элемент в основном раскрывающемся списке), вы увидите всплывающее сообщение, как показано ниже, нажмите Да для продолжения.
Теперь зависимый выпадающий список был сделан.
Шаг 5: Протестируйте зависимый раскрывающийся список.
1. Выбрать Фрукты в главном выпадающем списке (E2), затем перейдите к зависимому раскрывающемуся списку (F2), чтобы щелкнуть значок стрелки, посмотреть, есть ли фрукты в списке, затем выбрать один элемент из зависимого раскрывающегося списка.
2. Нажмите Tab чтобы начать новую строку в таблице ввода данных, выберите Овощной, и перейдите к следующей ячейке справа, посмотрите, есть ли элементы овощей в списке, затем выберите один элемент из зависимого раскрывающегося списка.
- Если в основном выпадающем списке (столбец Товар) не выбран элемент, зависимый выпадающий список (столбец Товар) работать не будет.
- Если вы хотите сбросить или очистить содержимое зависимого раскрывающегося списка после выбора измененного, перейдите к этой статье. Как очистить зависимую ячейку раскрывающегося списка после изменения выбора в Excel?, он вводит код VBA, чтобы помочь вам.
- Если вы хотите создать трехуровневый раскрывающийся список, эта статья Как создать многоуровневый зависимый выпадающий список в Excel? Вам поможет.
10 секунд, чтобы сделать зависимый раскрывающийся список с помощью удобного инструмента
Kutools for Excel предоставляет мощный инструмент для упрощения и ускорения зависимого раскрывающегося списка, давайте посмотрим:
Перед выполнением следующих шагов, пожалуйста, нажмите, чтобы загрузить Kutools for Excel для 30-дневной бесплатной пробной версии в первую очередь.
Шаг 1: введите записи для раскрывающегося списка
Во-первых, расположите свои данные, как показано ниже:
Шаг 2: Применение инструмента Kutools
1. Выберите созданные вами данные, нажмите Кутулс вкладку и щелкните Раскрывающийся список для отображения подменю нажмите Динамический раскрывающийся список.
2. В зависимом выпадающем списке
- Проверить Режим Б который соответствует вашему режиму передачи данных,
- Выберите выходной диапазон, столбец диапазона вывода должен быть равен столбцу диапазона данных,
- Нажмите Ok.
Теперь зависимый выпадающий список создан.
- Режим B поддерживает создание раскрывающегося списка третьего или более уровней:
- Если ваши данные расположены так, как показано на снимке экрана ниже, вам необходимо использовать режим A, режим A поддерживает только создание двухуровневого зависимого раскрывающегося списка.
- Более подробную информацию о том, как использовать Kutools для создания зависимого раскрывающегося списка, см. этот учебник .
Создайте динамический зависимый раскрывающийся список в Excel 2021 или Excel 365.
Если вы работаете в Excel 2021 или Excel 365, есть еще один способ быстро создать динамический зависимый раскрывающийся список с помощью новых функций. УНИКАЛЬНЫЙ и ФИЛЬТР.
Предположим, что ваши исходные данные организованы, как показано на скриншоте, выполните следующие шаги, чтобы создать динамический раскрывающийся список.
Шаг 1. Использование формулы для получения элементов основного раскрывающегося списка
Выберите ячейку, например ячейку G3, и с помощью функций UNIQUE и FILTER извлеките уникальные значения из Продукт список, который будет источником основного выпадающего списка, и нажмите Enter .
=UNIQUE(FILTER(A3:A20, A3:A20<>""))
Шаг 2. Создайте основной раскрывающийся список
1. Выберите ячейку, которую вы хотите разместить в основном раскрывающемся списке, например, ячейку D3, нажмите Данные вкладка и перейдите к Инструменты данных группа для нажатия проверка достоверности данных > проверка достоверности данных.
2. В диалоговом окне «Проверка данных»
- Выберите Список в Разрешить раздел,
- Введите приведенную ниже формулу в Источник бар,
- Нажмите OK.
=$G$3#
Теперь основной выпадающий список создан.
Шаг 3. Использование формулы для получения элементов для зависимого раскрывающегося списка
Выберите ячейку, например, ячейку H3, используя функцию ФИЛЬТР для фильтрации элементов на основе значения в ячейке. D3 (выбранный элемент в основном раскрывающемся списке), нажмите Enter .
=FILTER(B3:B20, A3:A20=D3)
Шаг 4: Создайте зависимый раскрывающийся список
1. Выберите ячейку, в которой будет размещен зависимый выпадающий список, например, ячейка E3, нажмите Данные вкладка и перейдите к Инструменты данных группа для нажатия проверка достоверности данных > проверка достоверности данных.
2. В диалоговом окне «Проверка данных»
- Выберите Список в Разрешить раздел,
- Введите приведенную ниже формулу в Источник бар,
- Нажмите OK.
=$H$3#
Теперь зависимый раскрывающийся список успешно создан.
Когда вы добавляете новые элементы или вносите некоторые изменения в A3: A20, раскрывающийся список будет обновляться автоматически.
Отсортировать выпадающий список по алфавиту
Если вы хотите расположить элементы в раскрывающемся списке в алфавитном порядке, вы можете использовать приведенную ниже формулу в таблице подготовки.Для основного раскрывающегося списка (формула в ячейке G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))Для зависимого раскрывающегося списка (формула в ячейке H3):
=SORT(FILTER(B3:B20, A3:A20=D3))Теперь оба выпадающих списка сортируются в алфавитном порядке от А до Я.
Для сортировки по алфавиту от Z до A используйте следующую формулу:
Для основного раскрывающегося списка (формула в ячейке G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)Для зависимого раскрывающегося списка (формула в ячейке H3):
=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)Некоторые вопросы, которые вы можете задать:
1. Зачем вставлять таблицу для каждого списка данных?
Вставка таблицы для списка данных поможет вам автоматически обновлять раскрывающийся список на основе изменений в списке данных. Например, при добавлении «Другие» в первый список данных в основной раскрывающийся список будет автоматически добавлено «Другие».
2. Зачем использовать таблицу для размещения выпадающих списков?
Когда вы нажимаете клавишу Tab, чтобы добавить новую строку в таблицу, выпадающие списки также будут автоматически добавлены в новую строку.
3. Как работает функция ДВССЫЛ?
КОСВЕННЫЕ Функция используется для преобразования текстовой строки в допустимую ссылку.
4. Как работает формула ДВССЫЛ(ПОДСТАВИТЬ(E2&F2," ",""))?
Во-первых, ЗАМЕНА функция заменяет текст другим текстом. Здесь он используется для удаления пробелов из комбинированных имен (E2 и F2). Затем КОСВЕННЫЕ Функция преобразует текстовую строку (объединенное содержимое клавиш E2 и F2) в допустимую ссылку.
Лучшие инструменты для офисной работы
Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Содержание
- Видео: создание зависимого раскрывающегося списка Excel
- Сделать динамический зависимый выпадающий список
- 10 секунд, чтобы сделать зависимый выпадающий список
- Создайте динамический зависимый раскрывающийся список в Excel 2021/365.
- FAQ
- Связанные статьи
- Лучшие инструменты для работы в офисе
- Комментарии