Jak v Excelu vytvořit závislé rozbalovací seznamy?

V dnešním videu si ukážeme, jak v Excelu vytvořit závislé rozbalovací seznamy. V předchozích videích jsme si už ukázali, jak rozbalovací seznamy vytvořit a jak s nimi pracovat, vytvořili jsme si rovněž rozbalovací seznam, ve kterém můžete vyhledávat. V dnešním videu se posuneme zase o kousek dál a vytvoříme dva na sobě závislé rozbalovací seznamy. To znamená, že první rozbalovací seznam určí, co se zobrazí ve druhém rozbalovacím seznamu. Kromě základní techniky s funkcí NEPŘÍMÝ.ODKAZ si dneska ukážeme i další dvě flexibilnější techniky, díky kterým budete znát tři různé způsoby, jak závislé rozbalovací seznamy vytvořit.

Excelový soubor ke stažení

Závislé rozbalovací seznamy v Excelu

V buňce kontinent (A4) chceme mít rozbalovací seznam, ve kterém si budeme moci zvolit kontinent (Evropa, Amerika, Afrika nebo Asie). Vedle v buňce (B4), která se jmenuje stát chceme, aby se nám v rozbalovacím seznamu zobrazily státy, které patří pod příslušný kontinent (F4:I9). 

Závislé rozbalovací seznamy
Obrázek č.1 Vstupní data

Příprava rozbalovacího seznamu kontinent

Začneme tím, že vytvoříme první rozbalovací seznam v buňce kontinent (A4). Zde se jedná o základní rozbalovací seznam, takže klikneme do buňky A4 a na kartě Data vybereme Ověření dat. Zde vybereme v rozbalovacím seznamu možnost Seznam a jako zdroj rozbalovacího seznamu můžeme označit záhlaví pomocného seznamu (F3:I3). Potvrdíme a do buňky se vložil seznam kontinentů.

Závislé rozbalovací seznamy
Obrázek č.2 Vložení rozbalovacího seznamu kontinent

Závislé rozbalovací seznamy pomocí funkce NEPŘÍMÝ.ODKAZ (INDIRECT)

Začneme tím, že si jednotlivé oblasti kontinentů pojmenujeme. Pokud bychom měli všechny seznamy stejně dlouhé, tak bychom mohli postupovat tak, že bychom si celou pomocnou tabulku včetně záhlaví označili, vybrali kartu Vzorce a zde Vytvořit z výběru. A zde bychom vybrali horní řádek, takže by se jednotlivé sloupce dat pojmenovali podle příslušného záhlaví.

Závislé rozbalovací seznamy
Obrázek č.3 Pojmenování oblastí buněk pomocí výběru

Nicméně, jelikož máme každý seznam jinak dlouhý, tak to není nejlepší postup, jelikož by se nám potom v rozbalovacím seznamu u kontinentů, kde je méně států zobrazovaly prázdné řádky.

V tomto případě by tedy bylo mnohem lepší označit si každou oblast zvlášť a zvlášť ji pojmenovat. Nejprve si tedy označíme státy Evropy (F4:F9) a klikneme do buňky vedle příkazového řádku, ze kterého můžeme pojmenovávat buňky nebo oblasti buněk, a seznam pojmenujeme jako Evropa. To samé uděláme i pro ostatní kontinenty.

Závislé rozbalovací seznamy
Obrázek č.4 Pojmenování jednotlivých oblastí buněk

A právě tyto pojmenované oblasti využijeme ve funkci NEPŘÍMÝ.ODKAZ, anglicky funkce INDIRECT. Funkce NEPŘÍMÝ.ODKAZ totiž mění textový řetězec na odkaz.

Funkci si nejprve zkusím napsat někde v excelovém sešitu, abychom ověřili, že funkce funguje předtím než ji nakopíruji jako zdroj do rozbalovacího seznamu. Zdrojový řádek v rozbalovacím seznamu rovněž neposkytuje excelovou nápovědu k funkcím, takže je mnohem snazší vytvořit funkci v sešitu, zjistit, zda funguje, tak jak má a následně ji nakopírovat do zdroje rozbalovacího seznamu. 

Do buňky B6 tedy napíšeme funkci NEPŘÍMÝ.ODKAZ, v angličtině funkce INDIRECT. Ve funkci NEPŘÍMÝ.ODKAZ označíme jako parametr odkaz buňku rozbalovacího seznamu s kontinentem (A4).

Závislé rozbalovací seznamy
Obrázek č.5 Funkce NEPŘÍMÝ.ODKAZ (funkce INDIRECT)

Ti z vás, kteří používají Office 365 a Microsoft Excel 2021 se seznam států rozbalí do jednotlivých buněk (B6:B11). U starších Excelů se po potvrzení funkce s největší pravděpodobností vrátí chybová hláška #HODNOTA. A je to proto, že se Excel snaží vrátit všechny státy do jedné buňky, což není možné. Nicméně jedná se o chybu pouze na oko. Správnost funkce si můžeme ověřit pomocí klávesy F9. V příkazovém řádku si označte celý vzorec a zmáčkněte klávesu F9. Rozbalí se vám výsledek funkce. Pokud jste vše udělali správně, tak by se vám v příkazovém řádku měl rozbalit seznam států. Takže i přesto, že vizuálně funkce kvůli chybové hlášce nevypadá správně, tak to v rozbalovacím seznamu bude fungovat. Nezapomeňte se z tohoto zobrazení přepnout zpátky do vzorce stisknutím kláves CTRL+Z.

Když víme, že máme funkci správně, tak ji použijeme jako zdroj v rozbalovacím seznamu. Funkci z příkazového řádku zkopírujeme a abychom ji nerozbili, tak hned po zkopírování potvrdíme ENTER. Klikneme do buňky, kde chceme mít rozbalovací seznam se státy (B4), na kartě Data vybereme Ověření dat a rozbalovací seznam. Do zdroje rozbalovacího seznamu vložíme zkopírovanou funkci NEPŘÍMÝ.ODKAZ.

Potvrdíme a zkontrolujeme, zda se nám do buňky vložil rozbalovací seznam. 

Závislé rozbalovací seznamy pomocí funkce KDYŽ (IF)

K vytvoření závislých rozbalovacích seznamů se nabízí i řešení pomocí několika funkcí KDYŽ. Opět si někde v excelovém sešitu vybereme cvičnou buňku (B6), ve které si postup zkusíme. Začneme vícenásobnou funkcí KDYŽ. Do buňky B6 napíšeme první funkci KDYŽ, kde jako podmínku napíšeme, pokud se buňka v kontinentu (A4) rovná první buňce v záhlaví pomocné tabulky (F3), tak chceme vrátit celý seznam států (F4:F9). A pokud podmínka splněná nebude, tak chceme druhou funkci KDYŽ. Všimněte si, že tím, že nebudeme buňky nikam přetahovat, tak se nemusíme zabývat ani fixováním buněk. Následuje druhá funkce KDYŽ, kde jako podmínka je, pokud se buňka v kontinentu (A4) bude rovnat druhému kontinentu v záhlaví (G3), tak chceme vrátit celý seznam států (G4:G5) a pokud ne, tak následuje třetí funkce KDYŽ. Zde opět jako podmínku napíšeme, že se kontinent v buňce A4 musí rovnat kontinentu v záhlaví (H3), pokud se budou rovnat, tak chceme vrátit celý seznam (H4:H9). Zbývá nám poslední seznam. Pro něj nemusíme psát vlastní funkci KDYŽ, jelikož pokud ani jedna z těchto tří podmínek splněná nebude, tak budeme chtít vrátit poslední seznam (I4:I8). Ukončíme závorky u všech funkcí KDYŽ.

Závislé rozbalovací seznamy
Obrázek č.6 Závislé rozbalovací seznamy pomocí mnohonásobné funkce KDYŽ (funkce IF)

Pokud teď funkci potvrdíme ENTEREM, tak u Office 365 a Microsoft Excel 2021 se rozbalí celý seznam států (B6:B11). U starších Excelů se vrátí chybová hláška #HODNOTA a je to proto, že se Excel opět snaží zobrazit všechny státy v jedné buňce. Tím, že jsme ve funkci KDYŽ použili odkaz na oblasti buněk, tak se ze vzorce stal maticový vzorec, který bychom normálně museli potvrdit stisknutím kláves CTRL+SHIFT+ENTER. Nicméně nám ta chyba až tak nevadí stejně jakou funkce NEPŘÍMÝ.ODKAZ, jelikož to bude zdrojem rozbalovacího seznamu. 

Správnost vzorce si opět můžete ověřit klávesou F9. 

Když máme funkci KDYŽ hotovou, tak funkci zkopírujeme, a vložíme funkci jako zdroj rozbalovacího seznamu. 

Závislé rozbalovací seznamy pomocí funkce IFS

U verzí Excelu 2019 a výše máte i novou funkci IFS, která usnadňuje psaní několikanásobných funkcí KDYŽ. Stačí do buňky B13 napsat funkci IFS, kde jako logický test označíme, zda se vybraný kontinent (A4) rovná prvnímu kontinentu v záhlaví (F3), pokud ano, tak označíme, že chceme vrátit celou oblast u prvního kontinentu (F4:F9). Následuje středník a druhý logický test, který bude, zda se kontinent v rozbalovacím seznamu (A4) rovná druhému kontinentu v záhlaví (G3), pokud ano, tak chceme vrátit seznam příslušných států (G4:G5). Takto pokračujeme i pro další dva kontinenty, když jsme s funkcí hotoví, potvrdíme a zkontrolujeme a nakopírujeme jako zdroj pro rozbalovací seznam. 

Rozdíl mezi funkcí IFS a mnohonásobnými funkcemi KDYŽ je v tom, že funkce IFS nahrazuje psaní funkcí KDYŽ. Jinak je princip fungování funkce v zásadě stejný. Rozdíl mezi funkcemi je vidět na Obrázku č.7. 

Závislé rozbalovací seznamy
Obrázek č.7 Závislé rozbalovací seznamy pomocí funkce IFS

Závislé rozbalovací seznamy pomocí funkce POSUN (OFFSET)

Pro vytvoření závislých rozbalovacích seznamů můžeme využít i funkce POSUN, anglicky funkce OFFSET v kombinaci s vyhledávací funkcí POZVYHLEDAT neboli funkcí MATCH. Toto řešení, i když na první pohled trochu komplikovanější, je určitě flexibilnější a hodí se tam, kde byste měli větší množství zdrojových sloupců.

Výsledná funkce vypadá takto. 

=POSUN($E$4;1;POZVYHLEDAT($A$4;$F$4:$I$4;0);POČET2(POSUN($F$4;1;POZVYHLEDAT($A$4;$F$4:$I$4;0)-1;10;1)))

Vytvoření funkce si ale ukážeme krok po kroku.

 

Krok 1. Funkce POZVYHLEDAT pro najití správného sloupce

Začneme tím, že potřebujeme najít správný sloupec kontinentu, ze kterého chceme vrátit seznamu států. K tomu poslouží funkce POZVYHLEDAT. Opět si někam do cvičné buňky (B6) napíšeme funkci POZVYHLEDAT, kde jako co hledáme označíme buňku rozbalovacího seznamu s kontinentem (A4). Kde tento kontinent hledáme? Hledáme ho v záhlaví pomocné tabulky, takže označíme celé záhlaví (F4:I4). Hledáme přesnou shodu, takže napíšeme nulu. Funkci potvrdíme. Vrátilo se pořadové číslo sloupce ve zdrojové tabulce, ve kterém je hledaný kontinent. Amerika je druhým sloupcem ve zdrojové tabulce. Toto číslo poslouží k určení parametru sloupce ve funkci POSUN.

Závislé rozbalovací seznamy
Obrázek č.8 Krok I. Funkce POZVYHLEDAT

Krok 2. Funkce POSUN 

Celou funkci POZVYHLEDAT zabalíme do funkce POSUN (OFFSET), kde je jako první parametr odkaz neboli styčný bod, od kterého se funkce POSUN začne posouvat. Měla by to být buňka blízko zdrojové tabulky, nicméně zde není jedno správné řešení. My označíme třeba buňku E4, která je první buňkou vedle záhlaví zdrojové tabulky. To bude náš styčný bod. 

Druhým parametrem funkce POSUN je parametr řádky. Tedy o kolik řádků se chceme posunou níže nebo výše než je styčný bod. Jelikož jsme jako styčný bod označili buňku na úrovni záhlaví tabulky, tak naše odpovědi budou vždy o jednu buňku níže, vyplníme tedy číslo 1. Tím se funkce POSUN posune z buňky E4 na E5.

Dalším parametrem je sloupec. Zde využijeme funkci POZVYHLEDAT, která nám dynamicky určí pořadové číslo sloupce podle kontinentu, který je zrovna vybraný v rozbalovacím seznamu. Momentálně máme vybranou Ameriku, takže funkce POZVYHLEDAT vrátí číslo 2. Funkce POSUN se tedy ze styčného bodu E4 nejdříve posune o jeden řádek níže do E5 a následně o dva sloupce doprava na G5, kde začíná první stát kontinentu Amerika.

Ke správnému vyřešení úkolu potřebujeme ještě nepovinný parametr výška, jelikož nechceme vrátit pouze první stát, ale celé rozpětí buněk se všemi státy. První možností je, vyplnit argument výška natvrdo, a to nejvyšším počtem řádků, který máte ve zdrojové tabulce. Nejvíce států je u Evropy a Afriky, celkem 6 států, takže vyplníme do parametru výška číslo 6 a funkci potvrdíme. 

U starších verzí Excelu se vrátí chybová hláška #HODNOTA, jelikož se Excel snaží vrátit všechny odpovědi do jedné buňky. U Excelů 365 a 2021 se vrátí všechny státy (B6:B11). Těm, kterým se vrátila chyba, mohou správnou funkce ověřit klávesou F9. 

Vzorec POSUN můžeme zkopírovat a vložit jako zdroj pro rozbalovací seznam. 

Závislé rozbalovací seznamy
Obrázek č.9 Krok II. Funkce POSUN

Takto vytvořený rozbalovací seznam bude plně funkční, nicméně u kontinentů, které mají méně než 6 států se objeví i prázdné řádky. A je to kvůli tomu, že jsme do funkce POSUN natvrdo napsali číslo 6 do parametru výška. U každého kontinentu teď funkce vrací 6 řádků. Na funkčnost rozbalovacího seznamu to nemá vliv, takže některým z vás toto řešení bude stačit.

 

Krok 3. Odstranění prázdných řádků z rozbalovacího seznamu

Pokud byste chtěli ale rozbalovací seznam bez těchto prázdných řádků, tak budete muset použít ještě jednu funkci POSUN v kombinaci s funkcí POČET2 a POZVYHLEDAT. Tato druhá funkce nahradí toto číslo 6 v parametru výška.

Zkusíme si tuto druhou část nejdříve napsat vedle (B15), a když budeme mít jistotu, že máme vzorec správně, tak ho vložíme do funkce.

Začneme opět funkcí POSUN. Důvodem je, že musíme opět najít správný sloupec, ve kterém musíme spočítat počet států, tak, aby první funkce vrátila správný počet států bez prázdných řádků. Napíšeme funkci POSUN, kde jako styčný bod opět označíme buňku E4, opět se posuneme o jeden řádek níže, v parametru sloupce opět použijeme funkci POZVYHLEDAT proto, abychom našli správný sloupec. Takže hledáme opět kontinent (A4), hledáme ho v záhlaví zdrojové tabulky (F4:I4) a hledáme přesnou shodu. A přichází parametr výška. Zde si zvolte číslo, které je klidně vyšší než je nejvyšší počet řádků ve zdrojové tabulce, například číslo 10. Funkci ukončíme a potvrdíme. Vrátil se opět seznam států s nulami. Nicméně teď tuto celou funkci zabalíme do funkce POČET2, anglicky funkce COUNTA, která počítá pouze textové hodnoty. Potvrdíme a vrátil se správný počet států u vybrané kontinentu (B15). 

Závislé rozbalovací seznamy
Obrázek č.10 Krok III. Odstranění prázdných řádků z rozbalovacího seznamu

Tuto celou funkci z buňky B15 zkopírujeme, potvrdíme enterem a nakopírujeme do první funkce místo čísla 6. Funkci potvrdíme a zkusíme, zda nám to celé funguje.

Závislé rozbalovací seznamy
Obrázek č.11 Závislé rozbalovací seznamy bez prázdných řádků

Teď když máme ověřeno, že funkce funguje, tak ji celou zkopírujeme a vložíme jako zdroj pro rozbalovací seznam.

MOHLO BY VÁS ZAJÍMAT

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *