Přejít k hlavnímu obsahu

Prozkoumejte nové, Bezplatná verze of Kutools for Outlook dnes!

Vytvoření dynamického závislého rozevíracího seznamu v Excelu (krok za krokem)

Autor: Sun Naposledy změněno: 2025-04-14

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

Snímek obrazovky zobrazující závislé nastavení rozevíracího seznamu v aplikaci Excel

Stáhněte si zdarma ukázkový soubor Ikona pro stažení vzorového souboru pro vytváření závislých rozevíracích seznamů v Excelu

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:

Snímek obrazovky zobrazující položky pro rozevírací seznamy v aplikaci Excel, každý seznam v samostatném sloupci

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".

Snímek obrazovky ukazující, jak vytvořit tabulku v aplikaci Excel pro položky rozevíracího seznamu

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í).

Snímek obrazovky zobrazující Správce názvů s odkazy na tabulky v Excelu

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".

Snímek obrazovky ukazující, jak vytvořit název rozsahu pro hlavní rozevírací seznam v Excelu

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.

Snímek obrazovky ukazující, jak vytvořit názvy rozsahů pro seznam ovoce

Snímek obrazovky ukazující, jak vytvořit názvy oblastí pro seznam zeleniny

Všechny názvy rozsahů můžete zobrazit ve Správci názvů (stisknutím "Ctrl "+ "F3" jej otevřete).

Snímek obrazovky s názvy rozsahů pro závislé rozevírací seznamy ve Správci názvů v Excelu

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“.

Snímek obrazovky znázorňující vytvoření tabulky pro použití rozevíracího seznamu v aplikaci Excel

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“.

Snímek obrazovky ukazující, jak vložit hlavní rozevírací seznam do aplikace Excel pomocí 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

Snímek obrazovky zobrazující dialogové okno Ověření dat pro hlavní rozevírací seznam v aplikaci Excel

Můžete vidět, že byl vytvořen hlavní rozevírací seznam.

Snímek obrazovky zobrazující hlavní rozevírací seznam vytvořený v aplikaci Excel

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," ","_"))

Snímek obrazovky ukazující, jak přidat závislý rozevírací seznam v aplikaci Excel pomocí ověření dat

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“.

Snímek obrazovky zobrazující varovnou zprávu, když je hlavní rozevírací seznam v Excelu prázdný

Nyní byl vytvořen závislý rozevírací seznam.

Snímek obrazovky zobrazující dokončený závislý rozevírací seznam v aplikaci Excel

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.

Animace ukazující, jak používat závislý rozevírací seznam v Excelu

Poznámky:

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ů:

Animace ukazující, jak v Excelu vytvořit závislý rozevírací seznam pomocí Kutools

Kutools for Excel nabízí více než 300 pokročilých funkcí pro zefektivnění složitých úkolů, zvýšení kreativity a efektivity. Itegarujte se schopnostmi AI, Kutools automatizuje úkoly s přesností, čímž usnadňuje správu dat. Podrobné informace o Kutools for Excel...         Zkušební verze zdarma...

Krok 1: Zadejte položky pro rozevírací seznam

Nejprve uspořádejte data podle níže uvedeného snímku obrazovky:

Snímek obrazovky ukazující, jak uspořádat data pro vytvoření závislého rozevíracího seznamu

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“.

Snímek obrazovky zobrazující Kutools Rozbalovací nabídka seznamu v Excelu

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“.

Snímek obrazovky zobrazující dialogové okno Závislé rozevírací seznam

Nyní byl vytvořen závislý rozevírací seznam.

Snímek obrazovky zobrazující kompletní rozevírací seznam závislých prvků vytvořený pomocí Kutools

Tip:
  • "Režim B" podporuje vytvoření třetí úrovně nebo více v rozevíracím seznamu:
    Snímek obrazovky zobrazující režim B v Kutools pro vytvoření víceúrovňového závislého rozevíracího 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.
    Snímek obrazovky zobrazující režim A v Kutools pro vytvoření dvouú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.

Kutools for Excel

Plně funkční bezplatná zkušební verze na 30 dní, není nutná kreditní karta.

Více než 300 výkonných pokročilých funkcí a funkcí pro Excel.

Nepotřebujete žádné speciální dovednosti, každý den šetříte hodiny času.

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.

Snímek obrazovky zobrazující zdrojová data uspořádaná pro vytváření závislých rozevíracích seznamů v Excelu

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<>""))
Poznámka: Pokud jsou produkty ve formátu A3:A12, přidáme do pole 8 dalších buněk, abychom vyhověli možným novým záznamům. Navíc jsme do UNIQUE vložili funkci FILTER, abychom extrahovali jedinečné hodnoty bez mezer.

Snímek obrazovky zobrazující vzorec UNIQUE a FILTER používaný k extrahování položek z hlavního rozevíracího seznamu v aplikaci Excel

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#
Poznámka: Toto se nazývá odkaz na rozsah rozlití a tato syntaxe se vztahuje na celý rozsah bez ohledu na to, jak moc se rozšiřuje nebo smršťuje.

Snímek obrazovky zobrazující dialogové okno Ověření dat pro vytvoření hlavního rozevíracího seznamu v aplikaci Excel

Nyní je vytvořen hlavní rozevírací seznam.

Snímek obrazovky zobrazující vytvořený hlavní rozevírací seznam v aplikaci Excel

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)
Poznámka: Pokud je v hlavním rozevíracím seznamu prázdné místo, vzorec se vrátí na nuly.

Snímek obrazovky zobrazující vzorec FILTER používaný k extrahování závislých položek v Excelu

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#
Poznámka: Toto se nazývá odkaz na rozsah rozlití a tato syntaxe se vztahuje na celý rozsah bez ohledu na to, jak moc se rozšiřuje nebo smršťuje.

Snímek obrazovky zobrazující dialogové okno Ověření dat pro vytvoření závislého rozevíracího seznamu v Excelu

Nyní je závislý rozevírací seznam úspěšně vytvořen.

Snímek obrazovky zobrazující dokončený závislý rozevírací seznam v aplikaci Excel

Když přidáte nové položky nebo provedete nějaké změny v A3:A20, rozevírací seznamy se automaticky aktualizují.

Tip:

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.

Snímek obrazovky zobrazující seřazené závislé rozevírací seznamy v abecedním pořadí v Excelu

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í“.

Snímek obrazovky ukazující, jak tabulka automaticky aktualizuje rozevírací seznam, když jsou přidána nová data

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

🤖 Kutools AI asistent: Revoluční analýza dat založená na: Inteligentní provedení   |  Generovat kód  |  Vytvořte vlastní vzorce  |  Analyzujte data a generujte grafy  |  vzývat Kutools Funkce...
Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty   |  Odstranit prázdné řádky   |  Kombinujte sloupce nebo buňky bez ztráty dat   |   Kolo bez vzorce ...
Super vyhledávání: Více kritérií VLookup  |   VLookup s více hodnotami  |   VLookup na více listech   |   Fuzzy vyhledávání ....
Pokročilý rozevírací seznam: Rychle vytvořte rozevírací seznam   |  Závislý rozbalovací seznam   |  Vícenásobný výběr rozevíracího seznamu ....
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  |  Porovnejte rozsahy a sloupce ...
Doporučené funkce: Zaměření mřížky   |  Návrhové zobrazení   |   Velký Formula Bar   |  Správce sešitů a listů   |  Knihovna zdrojů (Automatický text)   |  Výběr data   |  Zkombinujte pracovní listy   |  Šifrovat/dešifrovat buňky   |  Odesílat e-maily podle seznamu   |  Super filtr   |   Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů:  12 Text Tools (doplnit text, Odebrat znaky, ...)   |   50+ Graf Typ nemovitosti (Ganttův diagram, ...)   |   40+ Praktické Vzorce (Vypočítejte věk na základě narozenin, ...)   |   19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty, ...)   |   12 Konverze Tools (Čísla na slova, Přepočet měny, ...)   |   7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky, ...)   |   ... a více
Použijte Kutools ve vašem preferovaném jazyce – podporuje angličtinu, španělštinu, němčinu, francouzštinu, čínštinu a více než 40 dalších!

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!