V dnešním videu navážeme na předešlé video na Akademii Excelu, kde jsme si ukazovali, jak vytvořit vyhledávací rozbalovací seznam v buňce. Mnozí, kteří toto video viděli, vznesli navazující dotaz, kde se ptali, jak vyhledávací rozbalovací seznam použít na více řádků. Původní řešení vyhledávacího rozbalovacího seznamu totiž funguje jen v jedné buňce a nejde ho jen tak nakopírovat do více buněk. V dnešním videu si ukážeme řešení, jak použít vyhledávací rozbalovací seznam na více řádcích, a to s použitím funkce FILTER.
Pro ty z vás, kteří nemají funkci FILTER, máme řešení, jak vytvořit vyhledávací rozbalovací seznam na více řádcích na webu Akademie Excelu, ve videu, které je určené pro členy Akademie. Ve videu si ukážeme, jak vyhledávací seznam vytvořit na více řádcích, a to za pomoci funkcí, které jsou dostupné ve všech verzích Excelu.
Řešení v dnešním videu pomocí funkce FILTER budou tak moci zatím využít pouze ti, kteří mají tuto funkci ve své verzi Excelu dostupnou. Kromě toho, že vytvoříme vyhledávací rozbalovací seznam na každém řádku, tak pomocí funkce FILTER zjednodušíme i samotné vytvoření vyhledávacího rozbalovacího seznamu.
Excelový soubor ke stažení
Vyhledávací rozbalovací seznam v Excelu na více řádcích
Ještě než se pustíme do tvorby vyhledávacího rozbalovacího seznamu, tak je potřeba upozornit na to, že ti z vás kteří si platí předplatné Excelu 365, tak že mají výhodu v tom, že rozbalovací seznamy jsou v základu vždy vyhledávací. Pokud jste tedy předplatiteli této verze Excelu, a do buňky vložíte rozbalovací seznam ze seznamu jmen, tak v seznamu můžete pohodlně vyhledávat. Pokud seznam potřebujete na více řádcích, tak ho jednoduše zkopírujete, vložíte tam, kam potřebujete a seznam bude stále vyhledávat.
Pro ostatní, co nejsou předplatiteli Excelu 365 je následující návod.
Jako zdroj k vyhledávacímu rozbalovacímu seznamu nám dnes poslouží tabulka se jmény, kterou nejprve změníme na excelovou tabulku. Tabulku změníme na excelovou tabulku z důvodu toho, že chceme, aby funkce FILTER i vyhledávací rozbalovací seznam reagovali na nově přidaná data do seznamu. Klikneme do zdrojové tabulky a zmáčkneme klávesovou kombinaci CTRL+T, potvrdíme, že tabulka má záhlaví a z obyčejné tabulky se stala excelová tabulka. Zrušíme toto typické proužkování a necháme původní styl tabulky, takže klikneme do tabulky a na kartě Návrh tabulky vybereme mezi styly tabulky žádný styl. Vyhledávací rozbalovací seznam chceme vytvořit ve světlé modré buňce. Cvičně zde napíšeme část slova nebo písmeno, které budeme hledat mezi seznamem jmen. Třeba písmeno š.
Začneme tím, že vytvoříme funkci, kterou použijeme jak zdroj pro vyhledávací rozbalovací seznam. Rozdíl oproti původnímu způsobu, kde jsme k vytvoření vyhledávacího rozbalovacího seznamu potřebovali tři pomocné sloupce, je v tom, že pomocí funkce FILTER stačí jeden pomocný sloupec. Celý vzorec je tedy o dost jednodušší. Začneme stejně jako minule, tedy funkcí HLEDAT, anglicky funkcí SEARCH, kde budeme hledat to, co máme v buňce rozbalovacího seznamu, tedy písmeno š. Následuje oblast, kde tuto hodnotu hledáme, a tu hledáme v celém sloupci se jmény a jelikož pracujeme s excelovou tabulkou, tak označíme celý sloupec tabulky. Myší najedeme na záhlaví sloupce a počkáme až se z kurzoru stane černá šipka směřující dolů a potvrdíme. Tím se vybere celý sloupec. Ukončíme závorku u funkce HLEDAT a funkci potvrdíme. Na řádcích, kde funkce HLEDAT našla hledanou hodnotu se vrátilo číslo jedna, na ostatních řádcích se vrátila chybová hláška.
Abychom se zbavili chybových hlášek, tak zabalíme funkci HLEDAT do funkce JE.ČISLO, anglicky funkce ISNUMBER. Funkci potvrdíme a teď se řádky, kde byla jednička změnily na pravdu a ostatní řádky na nepravdu. V původním řešení jsme tyto dvě funkce zabalili do funkce KDYŽ v kombinaci s funkcí MAX. Následně jsme ve druhém pomocném sloupci vyhledali pomocí INDEX & POZVYHLEDAT hodnoty a pomocí funkce POSUN jsme vytvořily seznam vyhovujících hodnot. Teď toto vše můžeme nahradit jednou funkcí FILTER.
Funkci JE.ČISLO zabalíme do funkce FILTER, kde první parametr pole je seznam hodnot, které chceme vrátit zpátky, což je seznam jmen, označíme tedy opět celý sloupec jmen. A parametr zahrnuje je celá funkce JE.ČISLO. V základu druhý parametr funkce FILTER vrátí pouze hodnoty, které jsou pravdivé, nemusíme zde tedy vymýšlet žádné logické pravidlo, jelikož my už na řádcích pravdy a nepravdy máme. Poslední nepovinný parametr vyplňovat nemusíme. Ukončíme závorku a funkci potvrdíme. Funkce FILTER vrátila seznam jmen, které vyhovují zadání v buňce, kde bude rozbalovací seznam. Změníme písmeno v buňce na něco jiného, a vidíme, že funkce FILTER vrací správná jména.
Funkce, která bude základem vyhledávacího rozbalovacího seznamu je hotová. Klikneme do buňky, kde chceme mít rozbalovací seznam, smažeme její obsah a na kartě Data vybereme Ověření dat a zde vybereme seznam. Zdrojem rozbalovacího seznamu bude funkce FILTER, ale funkci nemusíme do zdroje kopírovat, můžeme pouze označit první buňku v pomocném seznamu a použít křížek, který označuje dynamickou oblast. To je celé, křížek zajistí, že do seznamu budou zahrnuté všechny řádky, které vrátí funkce FILTER, jelikož ta vrací rovněž dynamickou oblast. Ještě než vytvoření seznamu potvrdíme, tak musíme odškrtnout chybové hlášení u rozbalovacího seznamu. Pokud bychom to neudělali, tak bychom v seznamu nemohli vyhledávat. Potvrdíme vytvoření seznamu a zkontrolujeme rozbalovací seznam v buňce.
Výhodou používání Excelu 365 je to, že nemusíte tvořit žádný speciální rozbalovací seznam proto, aby byl vyhledávací. Každý rozbalovací seznam v této verzi Excelu totiž funguje na principu vyhledávání. Stačí tedy do rozbalovacího seznamu napsat, co hledáme a funkce FILTER vrátí výsledky, které splňují zadání. Nicméně stejně jako v přechozím řešení, ani tento rozbalovací seznam nemůžeme jen tak zkopírovat a vložit na více řádků. Narazíme na stejný problém, a to je ten, že rozbalovací seznam bude vracet obsah prvního rozbalovacího seznamu.
Řešením je použít pro každou buňku rozbalovacího seznamu vlastní pomocný seznam. Řekněme, že chceme vložit vyhledávací rozbalovací seznam na devět řádků. Pomocné seznamy vytvoříme vedle buněk, ve skutečnosti si je ale můžete vytvořit kdekoliv v sešitu Excelu a pomocný list s těmito seznamy třeba i skrýt. Funkce, kterou použijeme je ta samá, co vytvořila první rozbalovací seznam. Zkopírujeme tedy tuto funkci FILTER a vložíme ji opět k prvnímu řádku. Zkontrolujeme, že se funkce stále odkazují na správné buňky. Abychom mohli pomocný list vytvořit pro každý řádek, tak potřebujeme, aby se seznam nezobrazoval svisle ale vodorovně. K tomu můžeme použít funkci TRANSPOZICE. Funkci FILTER zabalíme do funkce TRANSPOZICE a potvrdíme. Teď máme list jmen horizontálně. Teď je funkce hotová a můžeme ji stáhnout dolů.
V první buňce máme vložený rozbalovací seznam, který se ovšem stále odkazuje na původní pomocný seznam. Klikneme tedy na buňku a na kartě Data v Ověření dat opravíme odkaz ve zdroji rozbalovacího seznamu. Toto smažeme a odkážeme se místo toho na nový pomocný seznam vedle buňky, tedy na funkci FILTER, teď zabalenou ve funkci TRANSPOZICE. Víme, že se ale funkce musí posouvat dolů, takže buňku ve zdroji zafixujeme pouze pro sloupec. A opět za buňkou použijeme křížek pro dynamické rozpětí. Před potvrzením funkce se ujistíme, že máme odškrtnutou chybovou hlášku. Potvrdíme vytvoření seznamu.
Teď zkopírujeme tuto první buňku a označíme ostatní buňky, kde chceme mít rozbalovací seznam a klikneme pravým tlačítkem myši a vybereme Vložit jinak. Zde vybereme Ověření. Potvrdíme a tím se na každý řádek vloží rozbalovací seznam, kde se buňka zdroje posouvá a každý řádek tak má vlastní funkci FILTER, která filtruje hodnoty.
Zkusíme, zda rozbalovací seznamy vyhledávají. Pokud v buňce nemáme nic napsaného, tak se nabídce celý seznam jmen. Jakmile ale začneme psát, tak se začnou nabízet jména, která vyhovují zadání. Zkusíme to na druhém řádku a jak vidíme, tak rozbalovací seznam reaguje na vyhledávání. Zkusíme další řádek a i ten reaguje na vyhledávání v seznamu.
Tak toto byl způsob, jak vytvořit vyhledávací rozbalovací seznam na více řádcích v Excelu, pro ty, kteří mají přístup k funkci FILTER. Pokud se vám video líbilo, budeme rádi, pokud se stanete členem Akademie Excelu na našem webu a odběratelem kanálu Akademie zde na Youtube. A já se zatím budu těšit u dalšího videa.
2 komentáře
Dobrý den. Díky za video. Mám office 2021 a zkusil jsem si vytvořit rozbalovací vyhledávací seznam. Bohužel, když začnu psát do buňky s vyhledávacím seznamem, nic mi nenabízí. Pouze se mění seznam v pomocném sloupci.
Zkusil jsem stáhnout Váš soubor, bohužel se stejným výsledkem. Buď mám chybu mezi uživatelem a klávesnicí :-), nebo to funguje jen u office 365. To ale jak tvrdíte má fungovat standardně bez pomocných sloupců.
Tak či tak, moc děkuji. Petr
Dobrý dne ještě jednou. Funguje to tak, že začnu psát, napíšu např. dvě písmena, potom musím myší rozbalit seznam a teprve se mi zobrazí vyfiltrovaná nabídka. Není to tak, že bych psal a zároveň by se mi otevřela a filtrovala nabídka. Díky.