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
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ář
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).
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.
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.
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.
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:
Pokud zvolím v rozbalovacím seznamu hodnotu Úrokové sazby – stávající úvěry tak vidím toto: