Vytvoření dynamického závislého rozevíracího seznamu v Excelu (krok za krokem)
Zde v tomto tutoriálu krok za krokem představíme, jak vytvořit závislý rozevírací seznam, který zobrazuje volby v závislosti na hodnotách vybraných v prvním rozevíracím seznamu. Jinými slovy, vytvoříme seznam pro ověření dat v Excelu na základě hodnoty jiného seznamu.
Vytvořte dynamicky závislý rozevírací seznam
10s a vytvořte závislý rozevírací seznam pomocí praktického nástroje
Vytvořte dynamicky závislý rozevírací seznam v Excelu 2021 nebo Excelu 365
Některé otázky, které se můžete zeptat na tento tutoriál
Stáhněte si zdarma ukázkový soubor 
Video: Vytvořte rozevírací seznam závislý na Excelu
Vytvořte dynamicky závislý rozevírací seznam
Krok 1: Zadejte položky pro rozevírací seznamy
1. Nejprve zadejte položky, které chcete zobrazit v rozevíracích seznamech, každý seznam ve sloupci samostatně.
Oznámení že položky v prvním sloupci (Produkt) budou později jako názvy Excel pro závislé seznamy. Například zde ovoce a zelenina budou názvy pro sloupec B2:B5 a C2:C6 samostatně.
Viz obrázek:
2. Poté vytvořte tabulky pro každý seznam dat.
Vyberte rozsah sloupců A1:A3, klikněte Vložit > Tabulkaa poté v dialogu Vytvořit tabulku zaškrtněte Můj stůl má záhlaví zaškrtávací políčko. Klepněte na OK.
Poté opakujte tento krok a vytvořte tabulky pro další dva seznamy.
Všechny tabulky a odkazy na rozsahy můžete zobrazit ve Správci názvů (stiskněte Ctrl + F3 otevřít).
Krok 2: Vytvořte názvy rozsahů
V tomto kroku musíte vytvořit jména pro hlavní seznam a každý závislý seznam.
1. Vyberte položky, které se zobrazí v hlavním seznamu (A2: A3).
2. Poté přejděte na Pole pro jméno které vedle Formule bar.
3. Zadejte do něj název, zde jej pojmenujte jako Produktový.
4. lis vstoupit klíč k dokončení.
Poté zopakujte výše uvedené kroky a vytvořte názvy pro každý závislý seznam samostatně.
Zde pojmenujeme druhý sloupec (B2:B5) jako ovoce a třetí sloupec (C2:C6) jako zelenina.
Všechny názvy rozsahů můžete zobrazit ve Správci názvů (stiskněte Ctrl + F3 otevřít).
Krok 3: Přidejte hlavní rozevírací seznam
Dále přidejte hlavní rozevírací seznam (Produkt), což je normální rozevírací seznam pro ověřování dat, nikoli závislý rozevírací seznam.
1. Nejprve vytvořte tabulku.
Vyberte buňku (E1) a zadejte záhlaví prvního sloupce (Produktový) a přejděte na buňku dalšího sloupce (F1), zadejte záhlaví druhého sloupce (Položka). Do této tabulky přidáte rozevírací seznam.
Poté vyberte tato dvě záhlaví (E1 a F1), klepněte na tlačítko Vložit Záložka a zvolte Tabulka ve skupině Tabulky.
V dialogovém okně Vytvořit tabulku zaškrtněte Můj stůl má záhlaví a klepněte na tlačítko OK.
2. Vyberte buňku E2 do kterého chcete vložit hlavní rozevírací seznam, klepněte na Data kartu a přejděte na Datové nástroje skupina kliknout Ověření dat > Ověření dat.
3. V dialogovém okně Ověření dat
- Vybrat Seznam v povolit sekce,
- Zadejte níže uvedený vzorec do Zdroj bar, produkt je název hlavního seznamu,
- klikněte OK.
=Product
Můžete vidět, že byl vytvořen hlavní rozevírací seznam.
Krok 4: Přidejte závislý rozevírací seznam
1. Vyberte buňku F2 do kterého chcete přidat závislý rozevírací seznam, klepněte na Data klepněte na kartu Datové nástroje a přejděte do skupiny Datové nástroje Ověření dat > Ověření dat.
2. V dialogovém okně Ověření dat
- Vybrat Seznam v povolit sekce,
- Zadejte níže uvedený vzorec Zdroj bar, E2 je buňka, která obsahuje hlavní rozevírací seznam.
- klikněte OK.
=INDIRECT(SUBSTITUTE(E2," ","_"))
Pokud je E2 prázdný (nevyberete žádnou položku v hlavním rozevíracím seznamu), zobrazí se zpráva, jak je uvedeno níže, klikněte Ano pokračovat.
Nyní byl vytvořen závislý rozevírací seznam.
Krok 5: Otestujte závislý rozevírací seznam.
1. vybrat Ovoce v hlavním rozevíracím seznamu (E2), pak přejděte do závislého rozevíracího seznamu (F2) klikněte na ikonu šipky, podívejte se, zda jsou položky ovoce v seznamu, a poté vyberte jednu položku ze závislého rozevíracího seznamu.
2. lis Tab pro zahájení nového řádku v tabulce pro zadávání dat vyberte Zeleninaa přejděte do další buňky vpravo, podívejte se, zda jsou položky zeleniny v seznamu, a poté vyberte jednu položku ze závislého rozevíracího seznamu.
- Pokud v hlavním rozevíracím seznamu (sloupec Produkt) není vybrána žádná položka, nebude závislý rozevírací seznam (sloupec Položka) fungovat.
- Pokud chcete resetovat nebo vymazat obsah závislého rozevíracího seznamu po výběru změněno, přejděte na tento článek Jak vymazat závislou buňku rozevíracího seznamu po výběru změny v aplikaci Excel?, zavádí kód VBA, který vám pomůže.
- Pokud chcete vytvořit 3úrovňový rozevírací seznam, tento článek Jak vytvořit víceúrovňový závislý rozevírací seznam v aplikaci Excel? pomůžu ti.
10s a vytvořte závislý rozevírací seznam pomocí praktického nástroje
Kutools pro Excel poskytuje výkonný nástroj pro snadnější a rychlejší vytváření závislého rozevíracího seznamu, podívejme se:
Než budete postupovat podle níže uvedených kroků, prosím kliknutím stáhněte Kutools pro Excel na 30denní bezplatnou zkušební verzi za prvé.
Krok 1: Zadejte položky pro rozevírací seznam
Nejprve uspořádejte data podle níže uvedeného snímku obrazovky:
Krok 2: Použití nástroje Kutools
1. Vyberte data, která jste vytvořili, klikněte Kutools kartu a klepněte na tlačítko Rozbalovací seznam pro zobrazení podnabídky klikněte na Dynamický rozevírací seznam.
2. V rozevíracím seznamu Závislý
- Zkontrolovat Režim B který odpovídá vašemu datovému režimu,
- Vybrat výstupní rozsah, sloupec výstupního rozsahu se musí rovnat sloupci rozsahu dat,
- klikněte Ok.
Nyní byl vytvořen závislý rozevírací seznam.
- Režim B podporuje vytvoření třetího nebo více rozbalovacího seznamu úrovní:
- Pokud jsou vaše data uspořádána tak, jak ukazuje níže uvedený snímek obrazovky, musíte použít režim A, režim A podporuje pouze vytvoření 2-úrovňového závislého rozevíracího seznamu.
- Další podrobnosti o tom, jak používat Kutools k vytvoření závislého rozevíracího seznamu, naleznete na adrese tento výukový program .
Vytvořte dynamicky závislý rozevírací seznam v Excelu 2021 nebo Excelu 365
Pokud používáte Excel 2021 nebo Excel 365, existuje další způsob, jak rychle vytvořit dynamicky závislý rozevírací seznam pomocí nových funkcí. UNIKÁTNÍ a FILTER.
Předpokládejme, že jsou vaše zdrojová data uspořádána jako zobrazený snímek obrazovky, vytvořte dynamický rozevírací seznam podle níže uvedených kroků.
Krok 1: Pomocí vzorce získáte položky pro hlavní rozevírací seznam
Vyberte buňku, například buňku G3, a pomocí funkcí UNIQUE a FILTER extrahujte jedinečné hodnoty z Produktový seznam, který bude zdrojem hlavního rozevíracího seznamu, a stiskněte vstoupit klíč.
=UNIQUE(FILTER(A3:A20, A3:A20<>""))
Krok 2: Vytvořte hlavní rozevírací seznam
1. Vyberte buňku, do které chcete umístit hlavní rozevírací seznam, například buňku D3, Klepněte na tlačítko Data kartu a přejděte na Datové nástroje skupina kliknout Ověření dat > Ověření dat.
2. V dialogovém okně Ověření dat
- Vybrat Seznam v povolit sekce,
- Zadejte níže uvedený vzorec do Zdroj bar,
- klikněte OK.
=$G$3#
Nyní je vytvořen hlavní rozevírací seznam.
Krok 3: Použití vzorce k získání položek pro závislý rozevírací seznam
Vyberte buňku, například buňku H3, pomocí funkce FILTER k filtrování položek na základě hodnoty v buňce D3 (vybraná položka v hlavním rozevíracím seznamu), stiskněte vstoupit klíč.
=FILTER(B3:B20, A3:A20=D3)
Krok 4: Vytvořte závislý rozevírací seznam
1. Vyberte buňku, do které se umístí závislý rozevírací seznam, například buňka E3, Klepněte na tlačítko Data kartu a přejděte na Datové nástroje skupina kliknout Ověření dat > Ověření dat.
2. V dialogovém okně Ověření dat
- Vybrat Seznam v povolit sekce,
- Zadejte níže uvedený vzorec do Zdroj bar,
- klikněte OK.
=$H$3#
Nyní je závislý rozevírací seznam úspěšně vytvořen.
Když přidáte nové položky nebo provedete nějaké změny v A3:A20, rozevírací seznam se automaticky aktualizuje.
Seřadit rozevírací seznam podle abecedy
Pokud chcete položky v rozevíracím seznamu seřadit abecedně, můžete použít níže uvedený vzorec k tabulce přípravy.Pro hlavní rozevírací seznam (vzorec v buňce G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))Pro závislý rozevírací seznam (vzorec v buňce H3):
=SORT(FILTER(B3:B20, A3:A20=D3))Nyní jsou oba rozevírací seznamy seřazeny abecedně od A do Z.
Chcete-li seřadit abecedně od Z do A, použijte níže uvedený vzorec:
Pro hlavní rozevírací seznam (vzorec v buňce G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)Pro závislý rozevírací seznam (vzorec v buňce H3):
=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)Některé otázky, které si můžete položit:
1. Proč vkládat tabulku pro každý seznam dat?
Vložení tabulky pro seznam dat vám pomůže automaticky aktualizovat rozevírací seznam na základě změn v seznamu dat. Například přidáním „Ostatní“ do prvního seznamu dat se do hlavního rozevíracího seznamu automaticky přidá „Ostatní“.
2. Proč používat tabulku k umístění rozevíracích seznamů?
Když stisknete klávesu Tab pro přidání nového řádku do tabulky, rozbalovací seznamy se automaticky přidají také na nový řádek.
3. Jak funguje funkce NEPŘÍMÉ?
NEPŘÍMÝ Funkce se používá k převodu textového řetězce na platný odkaz.
4. Jak funguje vzorec NEPŘÍMÝ(NAHRADIT(E2&F2," ",""))?
Za prvé, NÁHRADNÍ funkce nahradí text jiným textem. Zde se používalo k odstranění mezer z kombinovaných názvů (E2 a F2). Pak NEPŘÍMÝ funkce převede textový řetězec (kombinovaný obsah pomocí E2 a F2) na platný odkaz.
Nejlepší nástroje pro produktivitu v kanceláři
Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času. Kliknutím sem získáte funkci, kterou nejvíce potřebujete...
Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci
- Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
- Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
- Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
