Přejít k hlavnímu obsahu

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

Autor: Sun Naposledy změněno: 2023-08-15

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

rozevírací seznam závislý na dokumentu 1 1 1

Stáhněte si zdarma ukázkový soubor ukázka dokumentu


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:

rozevírací seznam závislý na dokumentu 1 2

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.

rozevírací seznam závislý na dokumentu 1 3

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

rozevírací seznam závislý na dokumentu 1 4

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

rozevírací seznam závislý na dokumentu 1 5

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.

rozevírací seznam závislý na dokumentu 1 15

rozevírací seznam závislý na dokumentu 1 6

Všechny názvy rozsahů můžete zobrazit ve Správci názvů (stiskněte Ctrl + F3 otevřít).

rozevírací seznam závislý na dokumentu 1 7

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.

rozevírací seznam závislý na dokumentu 1 8

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.

rozevírací seznam závislý na dokumentu 1 9

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

rozevírací seznam závislý na dokumentu 1 10

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

rozevírací seznam závislý na dokumentu 1 11

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

rozevírací seznam závislý na dokumentu 1 12

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.

rozevírací seznam závislý na dokumentu 1 13

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

rozevírací seznam závislý na dokumentu 1 14

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.

gif 1

Poznámky:

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:

kte gif 1

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:

Dynamický rozevírací seznam doc kutools 1

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.

Dynamický rozevírací seznam doc kutools 2

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.

Dynamický rozevírací seznam doc kutools 3

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

Dynamický rozevírací seznam doc kutools 4

Tipy:
  • Režim B podporuje vytvoření třetího nebo více rozbalovacího seznamu úrovní:
    Dynamický rozevírací seznam doc kutools 5 1
  • 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.
    Dynamický rozevírací seznam doc kutools 6
  • 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 .

Kutools pro 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 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<>""))
Poznámka: Když jsou produkty v 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.

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#
Poznámka: Říká se tomu 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.

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

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#
Poznámka: Říká se tomu 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.

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.

Tipy:

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.

rozbalovací seznam závislý na dokumentu 365 8

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

aktualizace rozevíracího seznamu v závislosti na dokumentu

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

🤖 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  |  Vyvolejte funkce Kutools...
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

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

Popis


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!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations