Věděli jste, že rozbalovací seznam z Ověření dat můžete vytvořit i vyhledávací? Vyhledávací rozbalovací seznam oceníte zejména tehdy, pokud v rozbalovacím seznamu musíte listovat dlouhým seznamem hodnot. Jednou z nevýhod rozbalovacího seznamu je totiž to, že se otevře vždy na začátku. Pomocí několika málo jednoduchých kroků si v buňce vytvoříte vyhledávací rozbalovací seznam, který vám nejen ušetří práci, ale ještě vám dovolí vyhledat všechny možnosti, které vyhovují vámi zadanému výběru.
Pro ty z vás, kteří preferují textový návod před videem, je pod videem podrobný návod, jak vyhledávací rozbalovací seznam vytvořit.
Excelový soubor ke stažení
Jak vytvořit vyhledávací rozbalovací seznam v Excelu
Do obarvené buňky F4 chceme vložit vyhledávací rozbalovací seznam. Postup vytvoření vyhledávacího rozbalovacího seznamu si ukážeme vedle tabulky v jednotlivých krocích. Začneme tím, že na ukázku do buňky F4 napíšeme libovolnou slabiku ze slova v seznamu, zkusíme třeba slabiku Fi. Tato slabika se vyskytuje ve slovech Finsko, Fidži a Filipíny.
Vyhledávací rozbalovací seznam vytvoříme v šesti krocích. Poslední tři kroky jsou dobrovolné, jedná se spíše o estetické vylepšení vyhledávacího rozbalovacího seznamu.
Krok 1: Najít, ve kterém slově se zvolená slabika vyskytuje
K vyhledání slabiky použijeme funkci HLEDAT. Vedle zdrojové tabulky do pomocného sloupce C napíšeme tedy funkci HLEDAT.
= HLEDAT(co; kde; [start])
Prvním parametrem funkce HLEDAT, je co hledáme. Hledáme slabiku, kterou máme napsanou v buňce F4, takže označíme buňku F4 a zafixujeme ji jak pro sloupce, tak řádky. Druhým parametrem funkce HLEDAT, je kde hledáme. Jelikož slabiku hledáme v seznamu zemí, takže označíme první buňku B5. Nepovinný argument nepotřebujeme, takže ho nevyplníme. Funkci stáhneme pro všechny buňky dolů. U slov, které obsahují námi zvolenou slabiku se objevila jednička, na ostatních řádcích se vrátila chybová hláška.
Krok 2: Zabalení funkce HLEDAT do funkce JE.ČISLO
Ve druhém kroku se musíme zbavit chybových hlášek na řádcích, které nesplňují kritérium. Pomůžeme si funkcí JE.ČÍSLO, do které zabalíme funkci HLEDAT.
= JE.ČISLO(hodnota)
Funkce JE.ČISLO má pouze jeden parametr, kterým je hodnota. Na řádcích, kde je číslo vrátí tato funkce slovo PRAVDA a na řádcích, kde číslo není, včetně chybových hlášek, vrátí tato funkce slovo NEPRAVDA.
Krok 3: Převod s podmínkovou funkcí KDYŽ a MAX
V dalším kroku musíme určit pořadové číslo státu, které splňují zadanou podmínku, že obsahují námi zvolenou slabiku. K tomu použijeme funkci KDYŽ v kombinaci s funkcí MAX. Funkci JE.ČISLO a HLEDAT zabalíme do funkce KDYŽ. Podmínka ve funkci KDYŽ je celá funkce JE.ČISLO. Do parametru ano ve funkci KDYŽ napíšeme funkci MAX.
Funkce MAX bude mít jako parametr rozpětí buněk C4:C4, ke kterému přičteme ještě číslo jedna. První buňka v rozpětí C4 musí být zafixována pro sloupce, tak řádky. Co funkce MAX v tomto tvaru zajistí je to, že se vytvoří dynamické rozpětí buněk, kde funkce MAX najde každou hodnotu, která splňuje podmínku a postupně k ní přičte číslo jedna, čímž u slov, které splňují podmínku vytvoří pořadová čísla.
Do posledního parametru ne ve funkci KDYŽ napíšeme nulu.
Krok 4: Přiřazení hledaných slov pomocí INDEX & POZVYHLEDAT
K pořadovým číslům musíme přiřadit správné názvy ze seznamu. Nejprve to zkusíme ve sloupci E, kde napíšeme funkci INDEX, kde jako pole označíme seznam slov. Místo parametru řádek napíšeme funkci POZVYHLEDAT, kde jako parametr, co hledáme bude funkce ŘÁDKY. Hledáme totiž nejprve společnost s jedničkou, následně s dvojkou atd. V parametru co ve funkci POZVYHLEDAT tedy musíme vytvořit dynamické rozpětí pořadí, k čemuž právě slouží funkce ŘÁDKY. Ve funkci ŘÁDKY označíme buňku, ve které se právě nacházíme, dvojtečka a ta samá buňka. První buňku zafixujeme jak pro sloupce, tak řádky. Druhým parametrem funkce POZVYHLEDAT, je, kde tyto hodnoty hledáme. To je pomocný sloupec C. Hledáme přesnou shodu.
Krok 5: Zabalení do funkce IFERROR
Na řádcích, kde není splněná podmínka vrátí funkce INDEX & POZVYHLEDAT chybové hlášky. Abychom se jich zbavili, zabalíme celou funkci do funkce IFERROR. Celá tato funkce bude základem pro rozbalovací seznam.
Krok 6: Vložení rozbalovacího seznamu
Klikneme do buňky, kam chceme vložit rozbalovací seznam a na horní liště Data najdeme Ověření dat. V nastavení vybereme Seznam a jako zdroj rozbalovacího seznamu označíme celý sloupec E. Než ale vytvoření rozbalovacího seznamu potvrdíme, tak v nastavení Chybového hlášení musíme odškrtnout pole Po zadání neplatných dat zobrazovat chybové hlášení.
Do buňky se vloží vyhledávací rozbalovací seznam, který vyhledá pouze slova, která splňují zadané parametry. Nicméně pod vybranými slovy se zobrazí prázdná místa. Pokud byste v rozbalovacím seznamu tato prázdná místa nechtěli, tak pokračujte kroky 7-9.
Krok 7: Odstranění prázdných buněk z rozbalovacího seznamu
Prázdné buňky z rozbalovacího seznamu odstraníme, pokud použijeme funkci POSUN.
= POSUN(odkaz; řádky; sloupce; [výška] ;[šířka])
Do dalšího pomocného sloupce napíšeme funkci POSUN, kde jako odkaz označíme první buňku nad seznamem. Jelikož se jedná o styčný bod, od kterého se funkce POSUN bude posouvat, tak ho zafixujeme jak pro sloupce, tak řádky. Parametr řádky je 1, jelikož seznam bude vždy začínat v řádku o jednom níže než je styčný bod. Jako parametr sloupce vyplníme nulu, jelikož chceme zůstat v daném sloupci. Ještě potřebujeme vyplnit nepovinný argument výška. V parametru výška použijeme funkci COUNTIF, kde jako oblast označíme celý pomocný sloupec a kritériem je jakýkoliv text. K vyjádření jakéhokoliv textu použijeme wildcards.
Symbolem pro jakýkoliv text je “?*“. Takže jako kritérium ve funkci COUNTIF vyplníme wildcard “?*“.
Další nepovinný parametr ve funkci POSUN nepotřebujeme, takže ho nevyplníme.
Krok 8: Správce názvů – pojmenování seznamu
Funkce POSUN poslouží jako zdrojová funkce pro vytvoření vyhledávacího rozbalovacího seznamu. Klikneme do první buňky, kde máme funkci POSUN a zkopírujeme vzorec. Po zkopírování vzorce musíte zmáčknout klávesu ENTER, abyste se dostali z aktivního pole pro změnu funkce. Na kartě Vzorce vyberete Správce názvů. Zde vytvoříme Nový název. V poli Název si pojmenujte funkci, třeba slovem Státy, a zkopírovanou funkci vložte do pole Odkaz na. Potvrdíme.
Krok 9: Pojmenovaná oblast jako zdroj Vyhledávacího rozbalovacího seznamu
Název pojmenované oblasti může být zdrojem rozbalovacího seznamu. Klikneme do buňky, kam chceme vložit rozbalovací seznam a na kartě Data se přepneme přes Ověření dat do okna Ověření dat. V nabídce vybereme Seznam a do Zdroje napíšeme =Státy (název, kterým jsme pojmenovali oblast). Potvrdíme.
Do buňky se vložit rozbalovací seznam, který je vyhledávací a navíc zobrazí pouze hodnoty, které splňují kritérium bez přebytečných prázdných buněk.
