• 7.5.2019
  • Ing. Jan Zedníček - Data Engineer & Controlling
  • 0

Graf v excelu asi umí udělat kde kdo. Většinou ho děláme způsobem, kdy máme tabulku s daty a v grafu tzv “na tvrdo” napojíme nějakou křivku nebo sloupce na data v tabulce. To je sice pěkné, ale co kdybychom uměli vytvořit rozbalovací seznam, který obsahuje různé položky a graf by nám automaticky na vybranou hodnotu v seznamu reagoval – tedy zobrazil pouze vybranou časovou řadu z rozbalovacího seznamu? Pokud vám vrtalo hlavou k čemu je vlastně rozbalovací seznam užitečný, tak toto je přesně typická situace kdy se používá.

Zkrátka a dobře máme několik časových řad a chtěli bychom nějakým zpsobem umožnit, aby si uživatel vybral z rozevíracího seznamu určitou volbu a následně na základě tohoto výběru učinit akci – třeba právě zobrazit data v grafu pro vybranou hodnotu. Tato technika patří k pokročilejším, ale s trochou cviku se dá rychle osvojit.

Příklad použití rozbalovacíh seznamu v Excelu

Jako data použijeme nějakou jednoduchou tabulku, např. úrokové sazby hypotečních úvěrů (viz obrázek níže). V tabulce máme pouze 3 sloupce:

  • Datum – datum ke kterému se vztahuje úroková sazba
  • Úrokové sazby pro stávající úvěry
  • Úrokové sazby pro nově poskytnuté úvěry

Rozbalovací seznam - zadání příkladu

Cílem cvičení je:

  • Vytvořit rozbalovací seznam, který bude obsahovat hodnoty (i) nové úvěry (ii) stávající úvěry
  • Vytvořit graf, který bude obsahovat pouze tu časovou řadu, která je vybrána v rozbalovacím seznamu

Kde Najdeme v Excelu Rozbalovací Seznam – Jak Zobrazit Kartu Vývojář?

Rozbalovací seznam najdeme na kartě “Vývojář” (Developer). Pokud jste tuto kartu dosud nepoužili, tak ji asi ani nemáte zobrazenou. Po instalaci MS Office je standardně karta zakrytá a musíte si ji povolit. Uděláte to následujícím způsobem – Soubor – Nastavení – Přizpůsobit pás karet – zaškrtneme kartu Vývojář

Zobrazení karty vývojář v Excelu

Vytvoření Rozbalovacího Seznamu a Naplnění Hodnotami

První krokem je vůbec vytvoření seznamu s tím, že následně seznamu musíme také vnutit, aby nám nabízel nějaké hodnoty.

Krok 1 – vytvoření seznamu

Na kartě Vývojář (Developer) vybereme vložit (Insert) a klikneme na rozbalovací seznam (combo box).

Vytvoření rozbalovacího seznamu

Krok 2 – vybereme plochu na kterou se má rozbalovací seznam zobrazit

To uděláme pomocí myši. Když si seznam rozklikneme, tak zatím nenabízí žádné hodnoty.

Rozevírací seznam je vytvořen

Krok 3 – Naplnění rozevíracího seznamu hodnotami

Klikneme na seznam pravým tlačítkem myši a následně vybereme “formát”. Vyskočí na nás okno, kde můžeme seznam nastavit. Rozbalovací seznam se nastavuje tak, že seznam odkážete na oblast buněk, ktzerá obsahuje hodnoty, které mají být v seznamu zobrazeny. Toto provedete v části Input range (Vstupní oblast). Já jsem si připravil tyto hodnoty v buňce P1 a P2.

Dále je potřeba nastavit buňku, kam vám rozbalovací seznam napíše pořadové číslo hodnoty, která je zrovna v seznamu aktivní (část cell link). To mám nastaveno na buňku P3. Pokud je v rozbalovacím seznamu vybrána první hodnota (Úrokové sazby – stávající úvěry), tak hodnota v buňce P3 bude rovna 1. V případě, že bych měl rozbalovací seznam, který má 10 hodnot a vyberu poslední, tak by v buňce byla hodnota 10, atd. Tato buňka, do které vám rozbalovací seznam vrací pořadové číslo hodnoty, která je vybrána je velmi důležitá. Budeme totiž s touto buňkou pracovat a díky této buňce si dokážeme vytvořit vzorec, který nám zobrazí správná data pro graf.

Rozbalovací seznam - naplnění seznamu hodnotami

Krok 4 – Vytvoření grafu, který komunikuje s rozbalovacím seznamem

Tato část je relativně nejsložitější. Musíme totiž vytvořit pomocný sloupec, který bude obsahovat pouze tu časovou řadu, která je zrovna vybrána v seznamu. K tomu nám slouží jak jsem již zmínil právě pomocná buňka, kterou nám rozbalovací seznam vrací pořadové číslo hodnoty, která je zobrazena (v našem případě je hodnota = 2 čili máme zobrazenou hodnotu Úrokové sazby – nové úvěry).

Pomocí této buňky a funkce INDEX jsme schopni vytvořit pomocný (žlutý sloupec). Do funkce index (viz obrázek) zadáme:

  • První argument oblast hodnot (B2:C28)
  • Druhý argument číslo řádku hodnoty, kterou má funkce vrátit
  • Třetí argument je pořadové číslo sloupce hodnoty, kterou má funkce vrátit

No číslo sloupce bude právě buňka, kterou vrací rozbalovací seznam. Tzn pokud vybereme první hodnotu, tak rozbalovací seznam vrátí 1 a následně funkce INDEX vrátí hodnotu z prvního označeného sloupce. A to je právě přesně co chceme. Pokud se vám to podařilo správně nastavit, tak si nyní můžete libovolně vybírat z rozbalovacího seznamu hodnoty a hodnoty ve žlutém slouci se budou měnit podle toho co máte zrovna vybráno.

Rozbalovací seznam - provázání hodnot s grafem

Krok 5 – Vytvoření graf a napojení na pomocný (žlutý) sloupec

Posledním krokem je vytvoření grafu, který napojíme na žlutý sloupec. Abych graf trošku zdokonalil, tak jsem si ještě vytvořil 1 sloupec navíc, který zobrazuje měziměsíční změnu. Pro obě dvě řady jsem v grafu udělal vizualizaci. Úrokové sazby jsou modrá křivka a meziměsíční změny jsou znázorněny sloupcovým (žlutým) grafem. Pokud změníme hodnotu v rozbalovacím seznamu, tak se nám automaticky přepočítají hodnoty v pomocných sloupcích a tím pádem i graf.

Pokud zvolím v rozbalovacím seznamu hodnotu Úrokové sazby – nové úvěry tak vidím toto:

Rozbalovací seznam - provázání hodnot s grafem - zkouška první hodnoty ze seznamu

Pokud zvolím v rozbalovacím seznamu hodnotu Úrokové sazby – stávající úvěry tak vidím toto:

Rozbalovací seznam - provázání hodnot s grafem - zkouška druhé hodnoty ze seznamu

5/5 - (3 votes)

Ing. Jan Zedníček - Data Engineer & Controlling

Jmenuji se Honza Zedníček a působím jako freelancer. Pracoval jsem dříve také jako BI developer, finanční controller a analytik. Vše pro společnosti z oblasti IT, bankovnictví, consultingu a výroby. Po práci si rád zahraju tenis, volejbal, šachy, zajdu do posilovny a občas neúspěšně odpálím pár balónků v golfu 🏌️

Již cca 10 let zapisuji na tento web různé návody určené zejména odborné veřejnosti, studentům a zájemcům o informace z oblastí Business intelligence, korporátních financí a reportingu.

🔥 Přihlašte se do naší Excel facebook skupiny (2.4k+ členů), kde si pomáháme Excel CZ/SK diskuse »

Leave a Reply

Your email address will not be published. Required fields are marked *