Rozbalovací menu se vzájemnou závislost vyklápěcích položek

S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.

Tento článek je o tom, jak vytvořit (pomocí ověřování dat) seznamy, které jsou na sebe vzájemně napojené. Tedy podle toho, co se vybere v prvním, se pak vybere ve druhém.

Příklad

V našem příkladu si uživatel v jednom rozbalovacím menu vybere ze seznamu zemí Francii, a následně si pak ve druhém může vybírat už jen francouzská města.

hotové

Návod

Následující příklad je ke stažení je tady

Jak na to?

Začít můžeme tím prvním menu. To vytvoříme úplně jednoduše, pomocí ověření dat. Většinou je šikovné si tyto seznamy odkládat na jiný list.

první buňka

první ověření dat

Pak si připravíme pro různé státy seznamy měst. Důležité je, aby nahoře vždy byl název státu nazvaný přesně tak, jako je to v původním seznamu států.

tabulka s městy

Teď musíme správně pojmenovat oblasti. Např. oblast C1 až C4 potřebujeme pojmenovat Francie atd. Abychom to nedělali týden, použijeme automatické pojmenování oblasti. 

Tedy označíme všechno najednou, pak Vzorce / Vytvořit z výběru / a pak necháme zaškrtnutou alespoň první možnost – pojmenování podle prvního řádku.

hromadné pojmenování

Tím se všechny sloupce pojmenovaly podle prvního řádku. Teď si přidáme další výběrové pole a nastavíme mu ověření dat. Použíme funkci NEPŘÍMÝ ODKAZ, která bude odkazovat na hodnotu z předchozího pole. Tím říkáme Excelu, aby do vyklápěcího seznamu načetl hodnoty z oblasti pojmenované tak, jak je vybráno v buňce A2.

nepřímý odkaz

Od teď už si v buňce B2 můžeme vybrat jen to, co dává smysl podle obsahu buňky A2.

hotové

S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.

1 Komentář

  1. Dobrý den,
    chtěla bych Vás poprosit o detailnější popis funkce nepřímého dokazu, stále mi to hází chybu. Mohl byste mi prosím napsat návod, co přesně označit a vyplnit do funkce, aby podseznam fungoval? Děkuji

  4. Dobrý den, je prosím nějaké řešení pro případ, kdy na výběru z nadřazené úrovně závisí výběr ve dvou různých buňkách? Například pokud by na výběru kontinentu závisela nabídka států v jedné buňce a nabídka pohoří v druhé buňce? Děkuji

  5. Dobrý den, asi bych zkusil pomocí funkce concatenate poskládat různé řetězce, které zpracuje funkce nepřímý.odkaz. Tak, aby se pokaždé odkazoval na různou oblast… Ale nezkoušel jsem to, tak netvrdím na 100% že je to správná cesta.

  7. Dobrý den,
    funkce samotná je skvělá, ale chci se zeptat, je možné udělat na sebe závislé buňky s roletkou se stejnou prioritou? Např. ve Vašem příkladě bych chtěl, abych si v první roletce vybral Francii a ta se mi ukázala i v druhé roletce, vybral bych si v druhé roletce Španělsko a ukázalo by se mi Španělsko v té první, a pod. Představte si to spíše jako možnost mít každou roletku na odlišném listě a pořád nepřeklíkavat obě roletky, na kterých jsou závislé další údaje.
    Díky.

  9. Dobrý den, toto je přesně to, co potřebuji. Jen mi to hlásí chybu při použití Nepřímého odkazu, že “Hodnota v poli Zdroj je v současnosti hodnocena jako chyba”. Neumíte mi poradit prosím? Děkuji

  10. Zkontrolujte syntaxi všeho a také to, že máte všude přesně stejné názvy oblastí…

  13. Dobrý den, mám dotaz. Pokusím se ho co nejlépe popsat. V jednom sloupci A mám vypsaný kompletní kusovník dílů, tak jak následuje. Tzn. první: A1 DVEŘE KOMPLETNÍ, pod tím z čeho se dveře skládají: A2 SVAŘENEC, svařenec se skládá z A3 PLECH, A4 NÝTY, A5 KULATINA. A6 je pak RÁMEČEK DVEŘÍ, který je vlastně na stejné úrovni jako svařenec. Protože kompletní dveře se skládají ze svařence a rámečku dveří. Rámeček dveří se zase skládá z A7 POSUVNÉ SKLO, A8 PEVNÉ SKLO. Takto bych mohl dále pokračovat. Z tohoto bych rád vytvořil rozevírací seznam. To jsem udělal a v seznamu se objevili všechny díly ze seznamu. Já bych rád seznam upravil tak, abych mohl vybírat pouze ze sestav a ne všech dílů ze seznamu. Tzn. mít v seznamu pouze SESTAVY a když si ji vyberu, ukáže se mi daná sestava vč. podsestavy. Takže např. u výběru SVAŘENEC ze seznamu budu mít pod sebou SVAŘENEC-PLECH-NÝTY-KULATINA. Je to vlastně takový seznam definovaných buněk. Tímto bych docílil toho, že vždy budu vědět z jakých dílů sestává vybraná sestava. Pokud máte někdo zájem mi pomoci ať už tímto nebo jiným způsobem, budu samozřejmě rád. Pokud budete chtít, tak vám případně zašlu vzorovou tabulku. Podotýkám, že veškeré sestavy a díly sestav musím být v prvním sloupci pod sebou. Za případný zájem předem děkuji..

  14. Dobrý večer,
    řeším stejný problém jako Marek v předešlém dotazu. Už v tom ležím několik dní a nemůžu se pohnout z místa. Nemá prosím někdo nějaký tip?
    Děkuji

  15. Dobrý den,
    chtěla bych se zeptat, jestli je možné nepřímým odkazem také napodmínkovat ověření dat v jiných než sousedících sloupcích. Připravuji výkaz práce kde na C6 mám ověření dat pozice a na B13 mám náplň práce, kterou bych chtěla ověřit podle toho, kterou pozici si vyberou v C6. Bohužel mi to hází chybu a říká, že “HODNOTA V POLI ZDROJ JE V SOUČASNOSTI HODNOCENA JAKO CHYBA”.

    Děkuji za pomoc.

  17. Dobrý den,

    Nelze vytvořit rozbalovací seznam se zdrojovými daty na jiném listě.
    Můj případ:
    Mám list FAKTURA ze kterého chci brát zdrojová data pro pro rozbalovací seznam na listu 001. Samotné tlačítko pro výber oblasti pro zdroj dat mě na jiný list nepustí. tak jsem to zkoušel napsat vzorcem: =Faktura!$X$35-$X$45.
    při potvrzení okna Ověření dat mi vyběhne chybová hláška: U kritérií pro Ověření dat nelze použít odkazy na jiné listy nebo sešity.

    Dá se to nějak vyřešit?

    Děkuji

  18. Místo pomlčky tam má být dvojtečka. Ale je zbytečné vzorečky psát, stačí je “roztahovat”…

  19. Reakce na Anonym 8.4.2017 at 19:47. Autorem jsem ja a Jiří Beran 9.4.2017 at 14:37

    Mnohokrát děkuji.
    5 hodin jsem vytvářel onen sešit a asi už jsem byl moc unavený a nevšiml jsem si takové blbosti…

    PS: Nakonec jsem to včera vyřešil tak, že jsem na listu 001 udělal odkazy na buňky v pomocném listu, kde byla ona zdrojová data, takže jsem zdrojová data dostal na stejný list 🙂

    Ještě jednou děkuji

  21. Dobrý den,
    měl bych dotaz.
    Problém:
    Potřebuji, aby po výběru názvu z rozbalovacího seznamu mi excel pod tento vybraný název vložil číslo a text, resp. asi celou tabulku, protože pro každou položku z rozbalovacího seznamu jsou hodnoty různé.
    Příklad:
    rozbal. seznam (výběr A, B, C) a když vyberu A, pod tuto buňku mi to vyhodí to, co má být u A, tedy:
    1. a
    2. b
    3. b
    4. c
    když vyberu B, bude tabulka např. taková:
    1. b
    2. a
    3. a
    4. d
    Číslo mám v jenom sloupci, text v druhém sloupci.
    Lze toto nějak pořešit pomocí rezevíracího seznamu? Můžete poradit?
    Děkuji mnohokrát

  23. Vyklápěcí položky? Já znám jen z dětství oranžovou sklápěcí Tatru. Proboha, co je to za terminologii?

  24. Dobrý den. vytvářím rozbalovací menu dle Vašich instrukcí. Mám seznam výrobku např. EI045 a k tomu vedle dle návodu vytvořený seznam výrobních linek, na kterých se může položka vyrábět. Při pojmenování oblasti (přes Vytvořit z výběru) se mi sice názvy oblastí vytvoří, ale k číslu výrobku mi přibude podtržítko EI045_ a seznam linek v menu se při vybrání tohoto výrobku neobjeví. Myslím, že je to právě kvůli podtržítku (není identický název oblasti a výrobku) Poradíte? Díky.

  25. EI045 je také adresa buňky v listu Excelu.
    Z důvodu odstranění kolize a jednoznačnosti přidává podtržítko.
    Budete muset upravit seznam výrobků

  26. Zdravím, prosím, je možné zvětšit formát rozevíracího seznamu, resp. popisu hodnot, které se v rozevíracím seznamu objevují. Potřebuji pro starší uživatele…. Děkuji

  28. Dobrý den,
    je možné definovat seznamy na více úrovní? Např při výběru Francie se v dalším výběru zobrazí jen Fr. města a já bych potřebovala aby se při výběru města na další úrovní zobrazili části města a na další úrovni třeba ulice z této části a potom třeba čísla popisná v té ulici. Pořád mi nejde do hlavy jak poskládat seznamy, tak aby toto fungovalo 🙂
    Děkuji za odpověď

  29. dobrý den
    dělám přesně to co máte výše popsáno akorát s čísly a ty to nebere děkuji za radu

  30. Dobrý den, potřebuju ve sloučené buňce udělat rozevírací seznam (dosadit 5 variant textu). Ale
    rozevírací tlačíko nejde ve sloučené bunce. Dá se to nějak obejít??

    Díky

  31. Dobrý den, taky jsem měl problém s tím, že mi to házelo hlášku „Hodnota v poli Zdroj je v současnosti hodnocena jako chyba“ a přišel jsem na to, že když mám dvouslovný název, tak mezi musí být podtržítko, po zadání podrtžítka místo mezer začalo vše fungovat a hlášku už to nehází.

  32. A jde to, aby se mezi víceslovné názvy nemusela psát pomlčka (podtržítko)?

  34. Dobrý den, jde nějak ošetřit, aby se v závislém rozevíracím seznamu (2) smazal výběr, pokud provedu změnu v seznamu prvním(1), na kterém je seznám (2) závislí?
    Děkuji za pomoc

  35. Dobrý den, potřebuji poradit, zda jde do rozevíracího seznamu umožnit i vkládat vlastní text. Tedy například ve vašem případě jiné město, než je vypsáno v seznamu. Nebo to lze řešit jinak?

  36. Petr, psajner@seznam.cz, https://exceltown.com/navody/postupy-a-spinave-triky/overeni-dat-vyklapeci-seznamy/kaskadove-rozbalovaci-menu-se-vzajemnou-zavislost-vyklapecich-menu/ says:

    Dobrý den, potřeboval bych se zeptat zda se dá někde nastavit počet řádků v rozbalovacím tlačítku. Zobrazí se 7 řádek a já bych jich potřeboval vidět více. Děkuji Petr

Komentáře není možné přidávat