Тук, в този урок, стъпка по стъпка ще представим как да създадем зависим падащ списък, който показва избори в зависимост от стойностите, избрани в първия падащ списък. С други думи, ще направим списък за валидиране на данни в Excel въз основа на стойността на друг списък.

Направете динамичен зависим падащ списък
10s, за да направите зависим падащ списък с удобен инструмент
Направете динамичен зависим падащ списък в Excel 2021 или Excel 365
Някои въпроси, които може да зададете относно този урок

Безплатно изтегляне на примерния файл

Видео: Направете падащ списък, зависим от Excel

Направете динамичен зависим падащ списък

Стъпка 1: Въведете записите за падащите списъци

1. Първо, въведете записите, които искате да се показват в падащите списъци, всеки списък в колона отделно.

Забележете че елементите в първата колона (Продукт) ще бъдат като имена на Excel за зависимите списъци по-късно. Например, тук плодове и зеленчуци ще бъдат имената за колона B2:B5 и C2:C6 поотделно.

Вижте екранна снимка:

2. След това създайте таблици за всеки списък с данни.

Изберете диапазон от колони A1:A3, щракнете Поставете > Маса, след което в диалоговия прозорец Създаване на таблица поставете отметка Моята таблица има заглавки квадратче за отметка. Щракнете върху OK.

След това повторете тази стъпка, за да създадете таблици за другите два списъка.

Можете да видите всички таблици и препратката към диапазони в Name Manager (натиснете Ctrl + F3 за да го отворите).

Стъпка 2: Създайте имена на диапазони

В тази стъпка трябва да създадете Имена за главния списък и всеки зависим списък.

1. Изберете елементите, които се показват в главния списък (A2: A3).

2. След това отидете на Кутия за име който до Лента с формули.

3. Въведете името в него, тук го наименува като Продукт.

4. Натиснете Въведете ключ за завършване.

След това повторете горните стъпки, за да създадете отделно имена за всеки зависим списък.

Тук втората колона (B2:B5) се нарича Fruit, а третата колона (C2:C6) като Vegetable.

Можете да видите всички имена на диапазони в Name Manager (натиснете Ctrl + F3 за да го отворите).

Стъпка 3: Добавете основния падащ списък

След това добавете основния падащ списък (Продукт), който е нормален падащ списък за валидиране на данни, а не зависим падащ списък.

1. Първо създайте таблица.

Изберете клетка (E1) и въведете заглавката на първата колона (Продукт) и преминете към следващата клетка на колона (F1), въведете заглавката на втората колона (Точка). Ще добавите падащия списък към тази таблица.

След това изберете тези две заглавки (E1 намлява F1), кликнете върху Поставете И изберете Маса в групата Таблици.

В диалоговия прозорец Създаване на таблица поставете отметка Моята таблица има заглавки и кликнете върху него OK.

2. Изберете клетка E2 към който искате да вмъкнете основния падащ списък, щракнете Дата раздел и отидете на Инструменти за данни група, за да щракнете Потвърждаване на данните > Потвърждаване на данните.

3. В диалоговия прозорец за проверка на данни,

• Изберете списък в Позволете раздел,
• Въведете формулата по-долу в източник лента, продуктът е името на основния списък,
• Кликнете OK.

=Product

Можете да видите, че основният падащ списък е създаден.

Стъпка 4: Добавяне на зависим падащ списък

1. Изберете клетка F2 към който искате да добавите зависимия падащ списък, щракнете Дата и отидете на групата Инструменти за данни, за да щракнете Потвърждаване на данните > Потвърждаване на данните.

2. В диалоговия прозорец за проверка на данни,

• Изберете списък в Позволете раздел,
• Въведете формулата по-долу източник лента, E2 е клетката, която съдържа основния падащ списък.
• Кликнете OK.

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

Ако E2 е празен (не изберете нито един елемент в главния падащ списък), ще видите изскачащо съобщение, както е показано по-долу, щракнете върху Да да продължи.

Сега зависимият падащ списък е направен.

Стъпка 5: Тествайте зависимия падащ списък.

1. изберете Плодове в главния падащ списък (E2), след което отидете на зависимия падащ списък (F2), за да щракнете върху иконата със стрелка, вижте дали плодовете са в списъка, след което изберете един елемент от зависимия падащ списък.

2. Натиснете Етикет клавиш за започване на нов ред в таблицата за въвеждане на данни, изберете Растителени преминете към следващата клетка вдясно, вижте дали зеленчуковите елементи са в списъка, след което изберете един елемент от зависимия падащ списък.

10s, за да направите зависим падащ списък с удобен инструмент

Kutools за Excel предоставя мощен инструмент, за да направите зависим падащ списък по-лесен и бърз, нека да видим:

Стъпка 1: Въведете записите за падащия списък

Първо, подредете данните си, както е показано на екранната снимка по-долу:

Стъпка 2: Прилагане на инструмента Kutools

1. Изберете данните, които сте създали, щракнете Kutools и щракнете върху Падащ списък за да се покаже подменюто, щракнете Динамичен падащ списък.

2. В падащия списък със зависими

• Проверете Режим B който съответства на вашия режим на данни,
• Изберете изходен диапазон, колоната за изходен диапазон трябва да е равна на колоната за диапазон от данни,
• Кликнете Ok.

Сега зависимият падащ списък е създаден.

Направете динамичен зависим падащ списък в Excel 2021 или Excel 365

Ако сте в Excel 2021 или Excel 365, има друг начин за бързо създаване на динамичен зависим падащ списък чрез използване на нови функции Универсален намлява FILTER.

Да предположим, че вашите изходни данни са подредени, както е показано на екранната снимка, моля, следвайте стъпките по-долу, за да създадете динамичния падащ списък.

Стъпка 1: Използване на формула за получаване на елементи за основния падащ списък

Изберете клетка, например клетка G3, и използвайте функциите UNIQUE и FILTER, за да извлечете уникалните стойности от Продукт списък, който ще бъде източник на основния падащ списък, и натиснете Въведете ключ.

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

Стъпка 2: Създайте основния падащ списък

1. Изберете клетка, която искате да поставите в главния падащ списък, например клетка D3, Щракнете върху Дата и отидете на Инструменти за данни група, за да щракнете Потвърждаване на данните > Потвърждаване на данните.

2. В диалоговия прозорец за проверка на данни,

• Изберете списък в Позволете раздел,
• Въведете формулата по-долу в източник бар,
• Кликнете OK.

=$G$3#

Сега основният падащ списък е създаден.

Стъпка 3: Използване на формула за получаване на елементи за зависимия падащ списък

Изберете клетка, например клетка H3, като използвате функцията FILTER, за да филтрирате елементите въз основа на стойността в клетката D3 (избраният елемент в основния падащ списък), натиснете Въведете ключ.

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

Стъпка 4: Създайте зависимия падащ списък

1. Изберете клетка, която ще постави зависимия падащ списък, например клетка E3, Щракнете върху Дата и отидете на Инструменти за данни група, за да щракнете Потвърждаване на данните > Потвърждаване на данните.

2. В диалоговия прозорец за проверка на данни,

• Изберете списък в Позволете раздел,
• Въведете формулата по-долу в източник бар,
• Кликнете 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)