Тут, у цьому підручнику, ми крок за кроком представимо, як створити залежний спадний список, який відображає варіанти залежно від значень, вибраних у першому спадному списку. Іншими словами, ми створимо список перевірки даних Excel на основі значення іншого списку.

Створіть динамічний залежний розкривний список
10s, щоб створити залежний спадний список за допомогою зручного інструменту
Створіть динамічний залежний спадний список у Excel 2021 або Excel 365
Деякі запитання, які ви можете поставити щодо цього підручника

Безкоштовно завантажте файл зразка

Відео: створіть розкривний список, залежний від Excel

Створіть динамічний залежний розкривний список

Крок 1: Введіть записи для розкривних списків

1. По-перше, введіть записи, які потрібно відобразити в розкривних списках, кожен список у колонці окремо.

повідомлення що елементи в першому стовпці (Продукт) будуть іменами Excel для залежних списків пізніше. Наприклад, тут Fruit and Vegetable будуть іменами для стовпців B2:B5 і C2:C6 окремо.

Дивіться знімок екрана:

2. Потім створіть таблиці для кожного списку даних.

Виберіть діапазон стовпців A1:A3, натисніть Insert > таблиця, а потім у діалоговому вікні «Створити таблицю» поставте галочку У моїй таблиці є заголовки прапорець. Клацніть OK.

Потім повторіть цей крок, щоб створити таблиці для двох інших списків.

Ви можете переглянути всі таблиці та посилання на діапазони в диспетчері імен (натисніть Ctrl + F3 щоб відкрити його).

Крок 2: Створіть імена діапазонів

На цьому кроці вам потрібно створити імен для основного списку та кожного залежного списку.

1. Виберіть елементи, які з’являться в основному списку (A2: A3).

2. Потім перейдіть до Ім'я поля який поруч Рядок формули.

3. Введіть ім’я, тут введіть його як Product.

4. прес Що натомість? Створіть віртуальну версію себе у ключ для завершення.

Потім повторіть наведені вище дії, щоб окремо створити імена для кожного залежного списку.

Тут другий стовпець (B2:B5) називається Fruit, а третій стовпець (C2:C6) як Vegetable.

Ви можете переглянути всі назви діапазонів у диспетчері імен (натисніть Ctrl + F3 щоб відкрити його).

Крок 3: Додайте головний спадний список

Далі додайте головний спадний список (Продукт), який є звичайним спадним списком перевірки даних, а не залежним спадним списком.

1. Спочатку створіть таблицю.

Виберіть клітинку (E1) і введіть заголовок першого стовпця (Product), і перейдіть до наступної клітинки стовпця (F1), введіть заголовок другого стовпця (пункт). Ви додасте розкривний список до цієї таблиці.

Потім виберіть ці два заголовки (E1 та F1), натисніть Insert Вкладка і виберіть таблиця у групі Таблиці.

У діалоговому вікні Створити таблицю поставте прапорець У моїй таблиці є заголовки поле та клацніть OK.

2. Виберіть клітинку E2 до якого потрібно вставити головний спадний список, клацніть дані та перейдіть до Data Tools групу, яку потрібно натиснути Перевірка достовірності даних > Перевірка достовірності даних.

3. У діалоговому вікні перевірки даних

• Вибирати список в дозволяти розділ
• Введіть наведену нижче формулу Source bar, Product — назва основного списку,
• Натисніть OK.

=Product

Ви бачите, що створено головний спадний список.

Крок 4: Додайте залежний спадний список

1. Виберіть клітинку F2 до якого потрібно додати залежний розкривний список, натисніть дані і перейдіть до групи Інструменти даних, щоб натиснути Перевірка достовірності даних > Перевірка достовірності даних.

2. У діалоговому вікні перевірки даних

• Вибирати список в дозволяти розділ
• Введіть наведену нижче формулу Source панель E2 — це комірка, яка містить головний розкривний список.
• Натисніть OK.

=INDIRECT(SUBSTITUTE(E2," ","_"))

Якщо E2 порожній (ви не вибрали жодного елемента в головному розкривному списку), ви побачите повідомлення, як показано нижче, натисніть Так продовжувати.

Тепер створено залежний спадний список.

Крок 5: Перевірте залежний спадний список.

1. вибрати Фрукти в основному спадному списку (E2), потім перейдіть до залежного розкривного списку (F2), щоб натиснути піктограму стрілки, перевірити, чи є фрукти у списку, а потім вибрати один із залежного розкривного списку.

2. прес таб клавішу для початку нового рядка в таблиці введення даних виберіть Овочевийі перейдіть до наступної комірки праворуч, подивіться, чи є овочі в списку, а потім виберіть один із залежного розкривного списку.

10s, щоб створити залежний спадний список за допомогою зручного інструменту

Kutools для Excel надає потужний інструмент для полегшення та прискорення створення залежного спадного списку, давайте подивимося:

Крок 1: Введіть записи для розкривного списку

По-перше, упорядкуйте свої дані, як показано на знімку екрана нижче:

Крок 2: Застосування інструменту Kutools

1. Виберіть дані, які ви створили, клацніть Кутулс та натисніть Випадаючий список щоб відобразити підменю, натисніть Динамічний розкривний список.

2. У розкривному списку залежних

• Перевірте Режим В який відповідає вашому режиму даних,
• Виберіть діапазон виходу, стовпець діапазону виведення має дорівнювати стовпцю діапазону даних,
• Натисніть Ok.

Тепер залежний спадний список створено.

Створіть динамічний залежний спадний список у Excel 2021 або Excel 365

Якщо ви працюєте в Excel 2021 або Excel 365, є інший спосіб швидко створити динамічний залежний спадний список за допомогою нових функцій UNIQUE та ФІЛЬТР.

Припустимо, що ваші вихідні дані впорядковано, як показано на знімку екрана, виконайте наведені нижче кроки, щоб створити динамічний спадний список.

Крок 1. Використання формули для отримання елементів для головного розкривного списку

Виберіть комірку, наприклад комірку G3, і за допомогою функцій UNIQUE та FILTER витягніть унікальні значення з Product список, який буде джерелом головного розкривного списку, і натисніть Що натомість? Створіть віртуальну версію себе у ключ

=UNIQUE(FILTER(A3:A20, A3:A20<>""))

Крок 2: Створіть головний спадний список

1. Виберіть клітинку, яку потрібно розмістити в основному розкривному списку, наприклад клітинку D3, Натисніть дані та перейдіть до Data Tools групу, яку потрібно натиснути Перевірка достовірності даних > Перевірка достовірності даних.

2. У діалоговому вікні перевірки даних

• Вибирати список в дозволяти розділ
• Введіть наведену нижче формулу Source бар
• Натисніть OK.

=$G$3#

Тепер створено основний спадний список.

Крок 3. Використання формули для отримання елементів для залежного спадного списку

Виберіть клітинку, наприклад клітинку H3, використовуючи функцію FILTER, щоб відфільтрувати елементи на основі значення в клітинці D3 (вибраний пункт у головному спадному списку), натисніть Що натомість? Створіть віртуальну версію себе у ключ

=FILTER(B3:B20, A3:A20=D3)

Крок 4: Створіть залежний спадний список

1. Виберіть комірку, у якій розміститься залежний розкривний список, наприклад комірка E3, Натисніть дані та перейдіть до Data Tools групу, яку потрібно натиснути Перевірка достовірності даних > Перевірка достовірності даних.

2. У діалоговому вікні перевірки даних

• Вибирати список в дозволяти розділ
• Введіть наведену нижче формулу Source бар
• Натисніть OK.

=$H$3#

Тепер залежний спадний список успішно створено.

Коли ви додаєте нові елементи або вносите деякі зміни в A3:A20, розкривний список оновлюватиметься автоматично.

=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))

=SORT(FILTER(B3:B20, A3:A20=D3))

=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)

=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)