Přejít k hlavnímu obsahu

Jak vytvořit závislé rozevírací seznamy s jedinečnými hodnotami pouze v Excelu?

Může být pro nás snadné vytvořit závislý rozevírací seznam v aplikaci Excel, ale pokud zdrojová data obsahují duplicitní hodnoty, budou použity i všechny duplicitní hodnoty, což může být nepříjemné. Chcete-li vyloučit duplicitní hodnoty a zachovat pouze ty jedinečné při vytváření závislého rozevíracího seznamu, tento tutoriál vám představí několik triků.

Vytvářejte závislé rozevírací seznamy s jedinečnými hodnotami pouze pomocí funkcí aplikace Excel

Vytvářejte závislé rozevírací seznamy s jedinečnými hodnotami pouze pomocí úžasné funkce


Vytvářejte závislé rozevírací seznamy s jedinečnými hodnotami pouze pomocí funkcí aplikace Excel

Je poněkud problematické vytvářet závislé rozevírací seznamy s jedinečnými hodnotami pouze v Excelu, měli byste použít níže uvedenou operaci krok za krokem:

Krok 1: Vytvořte názvy rozsahů pro data prvního a druhého rozevíracího seznamu

1, klikněte Vzorce > Definujte jméno, viz screenshot:

2. V Nové jméno dialogovém okně zadejte název rozsahu Kategorie do Příjmení textové pole (můžete zadat jakýkoli jiný název, který potřebujete) a poté zadejte tento vzorec =OFFSET($A$2,0,0,COUNTA($A$2:$A$100)) do Odkazuje na textové pole a nakonec klikněte na OK Tlačítko:

Poznámka: A2: A100 je seznam dat, na základě kterého vytvoříte první rozevírací seznam, pokud máte velká data, stačí změnit odkaz na buňku, který potřebujete.

3. Pokračujte ve vytváření názvu rozsahu pro druhý rozevírací seznam a klikněte Vzorce > Definujte jméno Chcete-li otevřít dialogové okno Nový název, zadejte název rozsahu Jídlo do Příjmení textové pole (můžete zadat jakýkoli jiný název, který potřebujete) a poté zadejte tento vzorec =OFFSET($B$2,0,0,COUNTA($B$2:$B$100)) do Odkazuje na textové pole a nakonec klikněte na OK Tlačítko:

Poznámka: B2: B100 je seznam dat, na základě kterého vytvoříte závislý rozevírací seznam, pokud máte velká data, stačí změnit odkaz na buňku, který potřebujete.

Krok 2: Extrahujte jedinečné hodnoty a vytvořte první rozevírací seznam

4. Nyní byste měli extrahovat jedinečné hodnoty pro data prvního rozevíracího seznamu zadáním následujícího vzorce do buňky a stisknutím tlačítka Ctrl + Shift + Enter klávesy k sobě a poté přetáhněte úchyt výplně dolů k buňkám, dokud se nezobrazí chybové hodnoty, viz snímek obrazovky:

=INDEX(Category,MATCH(0,COUNTIF($D$1:D1,Category),0))
Poznámka: Ve výše uvedeném vzorci, Kategorie je název rozsahu, který jste vytvořili v kroku 2, a D1 je výše uvedená buňka vaší buňky vzorce, změňte je podle potřeby.

5. Poté prosím vytvořte název rozsahu pro tyto nové jedinečné hodnoty, klikněte Vzorce > Definujte jméno k otevření Nové jméno dialogovém okně zadejte název rozsahu Jedinečná kategorie do Příjmení textové pole (můžete zadat jakýkoli jiný název, který potřebujete) a poté zadejte tento vzorec =POSUN($D$2; 0; 0, COUNT(IF($D$2:$D$100="", "", 1)), 1) do Odkazuje na textové pole, konečně klikněte OK zavřete dialogové okno.

Poznámka: D2: D100 je jedinečný seznam hodnot, který jste právě extrahovali, pokud máte velká data, stačí změnit odkaz na buňku, který potřebujete.

6. V tomto kroku můžete vložit první rozevírací seznam. Klikněte na buňku, do které chcete vložit rozevírací seznam, a poté klikněte na Data > Ověření dat > Ověření dat, a v Ověření dat dialogové okno vyberte Seznam z povolit rozbalte a zadejte tento vzorec: =Unikátní kategorie do Zdroj textové pole, viz screenshot:

7. Potom klepněte na tlačítko OK vytvoří se první rozevírací seznam bez duplicitních hodnot, jak je uvedeno níže:

Krok 3: Extrahujte jedinečné hodnoty a vytvořte závislý rozevírací seznam

8. Extrahujte jedinečné hodnoty pro sekundární rozevírací seznam, zkopírujte a vložte níže uvedený vzorec do buňky a poté stiskněte Ctrl + Shift + Enter kláves současně a poté přetáhněte úchyt výplně dolů k buňkám, dokud se nezobrazí chybové hodnoty, viz snímek obrazovky:

=INDEX(Food,MATCH(0,COUNTIF($E$1:E1,Food)+(Category<>$H$2),0))
Poznámka: Ve výše uvedeném vzorci, Jídlo je název rozsahu, který jste vytvořili pro data závislého rozevíracího seznamu, Kategorie je název rozsahu, který jste vytvořili pro data prvního rozevíracího seznamu, a E1 je výše uvedená buňka vaší buňky vzorce, H2 je buňka, kterou jste vložili do prvního rozevíracího seznamu, změňte je podle svých potřeb.

9. Poté pokračujte ve vytváření názvu rozsahu pro tyto sekundární jedinečné hodnoty, klikněte Vzorce > Definujte jméno k otevření Nové jméno dialogovém okně zadejte název rozsahu Jedinečné jídlo do Příjmení textové pole (můžete zadat jakýkoli jiný název, který potřebujete) a poté zadejte tento vzorec =POSUN($E$2; 0; 0, COUNT(IF($E$2:$E$100="", "", 1)), 1) do Odkazuje na Textové pole. Nakonec klikněte OK zavřete dialogové okno.

Poznámka: E2: E100 je sekundární seznam jedinečných hodnot, který jste právě extrahovali, pokud máte velká data, stačí změnit odkaz na buňku, který potřebujete.

10. Po vytvoření názvu rozsahu pro sekundární jedinečné hodnoty nyní můžete vložit závislý rozevírací seznam. prosím klikněte Data > Ověření dat > Ověření dat, a v Ověření dat dialogové okno vyberte Seznam z povolit rozbalte a zadejte tento vzorec: = Jedinečné jídlo do Zdroje textové pole, viz snímek obrazovky:

11, klikněte OK tlačítko, závislé rozevírací seznamy pouze s jedinečnými hodnotami se úspěšně vytvoří, jak je ukázáno níže:


Vytvářejte závislé rozevírací seznamy s jedinečnými hodnotami pouze pomocí úžasné funkce

Výše uvedená metoda může většinu z nás bolet, zde představím šikovný nástroj – Kutools pro Excel, S jeho Dynamický rozevírací seznam můžete tento problém snadno vyřešit.

Po instalaci Kutools pro Excel, udělejte prosím toto:

1, klikněte Kutools > Rozbalovací seznam > Dynamický rozevírací seznam, viz screenshot:

2. V Závislý rozevírací seznam dialogovém okně proveďte následující operace:

  • vybrat Režim B: Rozevírací seznam závislý na 2–5 úrovních z režim sekce;
  • Vyberte data, na jejichž základě chcete vytvořit závislý rozevírací seznam Rozsah dat krabice;
  • Poté vyberte výstupní rozsah, kam chcete umístit závislý rozevírací seznam z Výstupní rozsah box.

3. Potom klepněte na tlačítko Ok tlačítko, závislé rozevírací seznamy se vloží do výběru a duplicitní hodnoty se vyloučí. Viz níže ukázka:

Klikněte a stáhněte si nyní Kutools pro Excel!


Více relativních článků:

  • Vytvořte rozevírací seznam s obrázky v aplikaci Excel
  • V aplikaci Excel můžeme rychle a snadno vytvořit rozevírací seznam s hodnotami buněk, ale zkusili jste někdy vytvořit rozevírací seznam s obrázky, to znamená, když kliknete na jednu hodnotu z rozevíracího seznamu, její relativní obrázek se zobrazí najednou, jak je ukázáno níže. V tomto článku budu hovořit o tom, jak vložit rozevírací seznam s obrázky v aplikaci Excel.
  • Vytvořte rozevírací seznam s více zaškrtávacími políčky v aplikaci Excel
  • Mnoho uživatelů aplikace Excel má tendenci vytvářet rozevírací seznam s více zaškrtávacími políčky, aby vybrali více položek ze seznamu najednou. Ve skutečnosti nemůžete vytvořit seznam s více zaškrtávacími políčky pomocí Ověření dat. V tomto kurzu vám ukážeme dvě metody vytvoření rozevíracího seznamu s více zaškrtávacími políčky v aplikaci Excel.
  • Vytvořte víceúrovňový závislý rozevírací seznam v aplikaci Excel
  • V aplikaci Excel můžete rychle a snadno vytvořit závislý rozevírací seznam, ale pokusili jste se někdy vytvořit víceúrovňový závislý rozevírací seznam, jak je znázorněno na následujícím obrázku? V tomto článku budu hovořit o tom, jak vytvořit víceúrovňový závislý rozevírací seznam v aplikaci Excel.
  • Vytvořte rozevírací seznam, ale zobrazujte různé hodnoty v aplikaci Excel
  • V listu aplikace Excel můžeme rychle vytvořit rozevírací seznam s funkcí Ověření dat, ale zkusili jste někdy po kliknutí na rozevírací seznam zobrazit jinou hodnotu? Například mám následující dvě data sloupce ve sloupci A a sloupci B, teď musím vytvořit rozevírací seznam s hodnotami ve sloupci Název, ale když vyberu název z vytvořeného rozevíracího seznamu, odpovídající hodnota ve sloupci Číslo se zobrazí jako následující snímek obrazovky. Tento článek představí podrobnosti k vyřešení tohoto úkolu.

Nejlepší nástroje pro produktivitu v kanceláři

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

karta kte 201905


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
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations