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, Excelu 365 a novějších verzích
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 do samostatného sloupce.
Všimněte si, že položky v prvním sloupci (Produkt) budou později sloužit jako názvy Excelu 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 na "Vložit "> "Tabulka" a poté v dialogu Vytvořit tabulku zaškrtněte políčko "Moje tabulka má záhlaví". Klikně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 "pro otevření).
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 objeví v hlavním seznamu ("A2:A3").
2. Poté přejděte do pole "Název" vedle "Řádek vzorců".
3. Zadejte do něj název, zde jej pojmenujte jako „Produkt“.
4. Pro dokončení stiskněte klávesu "Enter".
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ů (stisknutím "Ctrl "+ "F3" jej otevřete).
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 ("Produkt") a přejděte k buňce dalšího sloupce ("F1"), zadejte záhlaví druhého sloupce ("Položka"). Tato tabulka obsahuje rozevírací seznamy.
Poté vyberte tato dvě záhlaví („E1“ a „F1“), klikněte na kartu „Vložit“ a ve skupině Tabulky vyberte „Tabulka“.
V dialogovém okně Vytvořit tabulku zaškrtněte políčko „Moje tabulka má záhlaví“ a klikněte na „OK“.
2. Vyberte buňku „E2“, do které chcete vložit hlavní rozevírací seznam, klikněte na kartu „Data“ a přejděte do skupiny „Nástroje dat“ a klikněte na „Ověření dat“ > „Ověření dat“.
3. V dialogovém okně Ověření dat
- Vyberte "Seznam" v sekci "Povolit",
- Zadejte níže vzorec do řádku "Zdroj", produkt je název hlavního seznamu,
- Klikněte na "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é chcete přidat závislý rozevírací seznam, klikněte na kartu "Data" a přejděte do skupiny Data Tools a klikněte na "Ověření dat" > "Ověření dat".
2. V dialogovém okně Ověření dat
- Zvolte "Seznam" v sekci "Povolit",
- Zadejte níže uvedený vzorec do řádku „Zdroj“, E2 je buňka, která obsahuje hlavní rozevírací seznam.
- Klikněte na "OK".
=INDIRECT(SUBSTITUTE(E2," ","_"))
Pokud je E2 prázdný (nevyberete žádnou položku v hlavním rozevíracím seznamu), zobrazí se níže uvedená zpráva, pokračujte kliknutím na „Ano“.
Nyní byl vytvořen závislý rozevírací seznam.
Krok 5: Otestujte závislý rozevírací seznam.
1. Vyberte "Ovoce" v hlavním rozevíracím seznamu ("E2"), poté přejděte do závislého rozevíracího seznamu ("F2") a klikněte na ikonu šipky, podívejte se, zda jsou položky ovoce v seznamu, pak vyberte jednu položku ze závislého rozevíracího seznamu.
2. Stiskněte klávesu "Tab" pro začátek nového řádku v tabulce pro zadávání dat, vyberte "Zelenina" a přejděte do další buňky vpravo, podívejte se, zda jsou položky zeleniny v seznamu, pak 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 po změně výběru resetovat nebo vymazat obsah závislého rozevíracího seznamu, 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 vám pomůže: Jak vytvořit víceúrovňový závislý rozevírací seznam v aplikaci Excel?.
10s a vytvořte závislý rozevírací seznam pomocí praktického nástroje
"Kutools for Excel" poskytuje výkonný nástroj pro snazší a rychlejší vytváření závislých rozevíracích seznamů:
Krok 1: Zadejte položky pro rozevírací seznam
Nejprve uspořádejte data podle níže uvedeného snímku obrazovky:
Krok 2: Aplikace Kutools nástroj
1. Vyberte data, která jste vytvořili, klikněte na „Kutools“ a kliknutím na „Rozbalovací seznam“ zobrazte podnabídku, klikněte na „Dynamický rozbalovací seznam“.
2. V "Závislém rozevíracím seznamu":
- Zkontrolujte „Režim B“, který odpovídá vašemu datovému režimu,
- Vyberte "výstupní rozsah", sloupec výstupního rozsahu se musí rovnat sloupci rozsahu dat,
- Klikněte na „OK“.
Nyní byl vytvořen závislý rozevírací seznam.
- "Režim B" podporuje vytvoření třetí úrovně nebo více v rozevíracím seznamu:
- Pokud jsou vaše data uspořádána tak, jak ukazuje níže uvedený snímek obrazovky, musíte použít „Režim A“, který podporuje pouze vytváření 2-úrovňového závislého rozevíracího seznamu.
- Pro více informací o použití Kutools vytvořit závislý rozevírací seznam, navštivte prosím tento tutoriál.
Vytvořte dynamicky závislý rozevírací seznam v Excelu 2021, Excelu 365 a novějších verzích
Pokud používáte Excel 365, Excel 2021 nebo novější verze, existuje další způsob, jak rychle vytvořit dynamicky závislý rozevírací seznam pomocí nových funkcí „UNIQUE“ a „FILTER“.
Předpokládejme, že jsou vaše zdrojová data uspořádána tak, jak je znázorněno na snímku obrazovky, postupujte podle níže uvedených kroků a vytvořte dynamický rozevírací seznam.
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 ze seznamu "Produkt", který bude zdrojem hlavního rozevíracího seznamu, a stiskněte klávesu "Enter".
=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“, klikněte na kartu „Data“ a přejděte do skupiny Data Tools a klikněte na „Ověření dat“ > „Ověření dat“.
2. V dialogovém okně "Ověření dat"
- Vyberte "Seznam" v sekci "Povolit",
- Do řádku "Zdroj" zadejte níže uvedený vzorec,
- Klikněte na "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 pro filtrování položek na základě hodnoty v buňce "D3" (vybraná položka v hlavním rozevíracím seznamu), stiskněte klávesu "Enter".
=FILTER(B3:B20, A3:A20=D3)
Krok 4: Vytvořte závislý rozevírací seznam
1. Vyberte buňku, která umístí závislý rozevírací seznam, například buňku „E3“, klikněte na „Záložka Data“ a přejděte do skupiny „Nástroje dat“ a klikněte na „Ověření dat“ > „Ověření dat“.
2. V dialogovém okně "Ověření dat"
- Vyberte "Seznam" v sekci "Povolit",
- Do řádku "Zdroj" zadejte níže uvedený vzorec,
- Klikněte na "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í seznamy se automaticky aktualizují.
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É?
Funkce INDIRECT se používá k převodu textového řetězce na platný odkaz.
4. Jak funguje vzorec NEPŘÍMÝ(NAHRADIT(E2&F2," ",""))?
Za prvé, funkce SUBSTITUTE nahradí text jiným textem. Zde se používalo k odstranění mezer z kombinovaných názvů (E2 a F2). Potom funkce INDIRECT převede textový řetězec (složený obsah pomocí E2 a F2) na platný odkaz.
Nejlepší nástroje pro produktivitu v kanceláři
Vylepšete si své excelové dovednosti s Kutools for Excela 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 výrazně vám usnadňuje 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!
Obsah
- Video: Vytvořte rozevírací seznam závislý na Excelu
- Vytvořte dynamicky závislý rozevírací seznam
- 10s pro vytvoření závislého rozevíracího seznamu
- Vytvořte dynamicky závislý rozevírací seznam v Excelu 365/2021/Novější
- Nejčastější dotazy
- Související články
- Nejlepší kancelářské nástroje produktivity
- Komentáře