Přepínač je další interaktivní prvek z Ovládacích prvků na kartě Vývojář. Díky přepínači můžete tvořit interaktivní přehledy neboli dashboardy, tak jak jsme to udělali ve videu o Prodejním excelovém přehledu, kde jsme pomocí přepínače ovládali, jaká data se mají zobrazit v excelovém grafu. V dnešním videu si vytvoříme další dva interaktivní grafy, které budou reagovat na přepínače z ovládacích prvků. Navíc si v průběhu videa ukážeme trik, jak v jednom excelovém sešitu použít několik na sobě zcela nezávislých přepínačů.
Excelový soubor ke stažení
Excelový graf ovládaný přepínačem – změna zobrazených dat
Jako zdrojová data pro první graf poslouží jednoduchá tabulka, ve které máme uvedené produkty a jejich tržby a marže. Tato data chceme zobrazit ve sloupcovém grafu. V grafu ovšem chceme přepínat mezi zobrazením tržeb a marže.
Aktivace karty Vývojář
Začneme tím, že do sešitu vložíme z ovládacích prvků přepínač. Přepínač najdeme na horní liště v Excelu na záložce Vývojář. Pokud záložku Vývojáře nemáte aktivovanou, tak ji zapnete tak, že pravým tlačítkem myši kliknete na horní lištu a vyberete Přizpůsobit pás karet. V příkazech vyberete z rozbalovacího seznamu Hlavní karty a mezi nimi najdete i možnost Vývojář. Vývojáře přidáte do pásu karet tak, že na něj kliknete a vyberete Přidat. Nakonec výběr potvrdíte.
Nastavení přepínače
Když máme kartu Vývojáře přidanou na liště, tak vybereme možnost Vývojář a možnost Vložit a vybereme možnost Přepínač. Přepínač se stejně jako ostatní ovládací prvky vloží do excelového sešitu tak, že ho nakreslíme. V grafu se budeme chtít přepínat mezi tržbou a marží, takže potřebujeme dva přepínače.
Nakreslíme nejprve první přepínač. Označíme text v přepínači, smažeme ho a napíšeme slovo Tržby. Zkopírujeme přepínač a text opět přepíšeme, tentokrát na slovo Marže. Klikneme na kterýkoliv z přepínačů a vybereme Formát ovládacího prvku. Nejdůležitějším polem v nastavení přepínače je pole Propojení s buňkou. V excelovém sešitu musíme vybrat buňku, kterou napojíme na přepínač. Vybereme například buňku G1. Oba přepínače se automaticky propojí s touto buňkou.
Pokud vybereme v přepínači Tržby, tak se v poli G1 objeví jednička, pokud vybereme Marže, tak se v poli G1 objeví 2. Tato buňka G1 bude ovládat pomocnou tabulku, kterou teď vytvoříme.
Proto, aby přepínač ovládal, která veličina se v grafu zobrazí, tak musíme vytvořit pomocnou tabulku. K jejímu vytvoření použijeme funkci INDEX.
Pomocná tabulka pro přepínač
Vedle zdrojové tabulky vytvoříme pomocnou tabulku, kde na řádky zkopírujeme produkty (I2:I15) a v první buňce záhlaví použijeme funkci INDEX. V parametru pole označíme záhlaví zdrojové tabulky (B1:C1), a v poli řádek označíme buňku G1, kterou zafixujeme jak pro sloupce, tak řádky ($G$1). Funkci potvrdíme a stáhneme dolů. Pokud v přepínači vybereme tržby, tak se v buňce G1 zobrazí jednička a v pomocné tabulce se objeví hodnoty pro tržby. Pokud přepneme na Marže, tak se v buňce G1 objeví dvojka a v pomocné tabulce se objeví hodnoty pro Marže.
Teď stačí jen pomocnou tabulku označit a vložit jednoduchý sloupcový graf. Rychlá kontrola, zda přepínač opravdu ovládá zobrazení grafu. V přepínači vybereme Tržby a graf zobrazuje tržby, změníme výběr na Marže a graf se změní.
Excelový graf ovládaný přepínačem – změna zobrazených dat a SUMIF
Pomocí přepínače vytvoříme i druhý interaktivní graf. Tentokrát máme ve zdrojové tabulce produkty, které se opakují. Data potřebujeme zobrazit ve sloupcovém grafu, ale sečtené za jednotlivé produkty. Navíc chceme v grafu tentokrát přepínat mezi zobrazením roku 2020 a roku 2021.
Problém s nezávislými přepínači v jednom excelovém sešitu
Než vytvoříme pomocnou tabulku, tak musíme vložit ovládací prvek Přepínač. Na kartě Vývojář vybereme Vložit a Přepínač. Opět budeme potřebovat dva přepínače. Nakreslíme první přepínač a jeho název změníme na Rok 2020. Přepínač zkopírujeme a text přepíšeme na Rok 2021. Na libovolný přepínač klikneme pravým tlačítkem myši a vybereme Formát ovládacího prvku. Všimněte si, že se druhý přepínač automaticky propojil s buňkou G1, což je buňka, která ovládá první přepínač u prvního grafu. Excel totiž automaticky propojí všechny přepínače v excelovém sešitu. Někdy to může být užitečné, ale v tomto případě potřebujeme zcela nezávislý druhý přepínač. Na to existuje jednoduchý trik.
Tyto druhé přepínače smažeme a začneme tím, že do excelového sešitu vložíme z ovládacích prvků Skupinový rámeček. Nakreslíme rámeček a smažeme jeho název. Tím, že přepínače umístíme do Skupinového rámečku zajistíme, že přepínače budou na sobě nezávislé.
Do rámečku umístíme první přepínač, přejmenujeme ho na Rok 2020, přepínač označíme a zkopírujeme a pojmenujeme druhý přepínač jako Rok 2021. Na libovolný přepínač klikneme pravým tlačítkem myši a vybereme Formát ovládacího prvku. V buňce, kde nastavujeme propojení teď nemáme vyplněno nic a můžeme tak tuto skupinu přepínačů propojit s buňkou H21.
Pomocná tabulka pro přepínač
Zbývá vytvořit pomocnou tabulku, kterou budou ovládat přepínače. Tentokrát musíme jednotlivé položky posčítat. Pro vyselektováni jedinečného seznamu produktů můžeme produkty zkopírovat, vložit tam, kde budeme chtít pomocnou tabulku a odstranit duplicity z karty Data. Tím zůstane pouze jedinečný seznam produktů. Předplatitelé služby Office 365 a Microsoft Excel 2021 mohou použít funkci UNIQUE, která vrátí jedinečný seznam položek. V tom případě bychom napsali funkce UNIQUE a jako pole označili seznam s produkty. Tak jako tak se vrátil jedinečný seznam produktů.
Na záhlaví tabulky použijeme stejně jako v předcházejícím příkladě funkci INDEX. Kde v poli označíme záhlaví zdrojové tabulky a do parametru řádky označíme buňku, která ovládá přepínače. Tím zajistíme, že se po výběru v přepínači zobrazí správný nadpis sloupce. Na tělo samotné tabulky, ale funkci INDEX použít nemůžeme, jelikož potřebujeme jednotlivé hodnoty sčítat. K tomu můžeme použít funkci SUMIF. Začneme tím, že posčítáme produkty pro rok 2020. Napíšeme funkci SUMIF, kde jako oblast označíme seznam produktů ve zdrojové tabulce, zafixujeme klávesou F4 jak pro sloupce, tak řádky, jako kritérium označíme první produkt v pomocné tabulce a jako oblast součtu označíme sloupec s rokem 2020.
Aby pomocná tabulka reagovala na změny přepínače, tak funkci SUMIF zabalíme do funkce KDYŽ. Před funkci SUMIF napíšeme funkci KDYŽ, kde podmínkou bude, pokud se buňka H21 bude rovnat jedničce, tak chceme počítat tuto funkci SUMIF, jelikož jednička v buňce H21 znamená, že máme vybraný rok 2020. A pokud podmínka ve funkci KDYŽ splněná nebude, tak chceme spočítat funkci SUMIF pro rok 2021. Tedy funkce SUMIF, jako oblast označíme opět produkty ve zdrojové tabulce, plně zafixujeme, jako kritérium označíme první produkt v pomocné tabulce a jako oblast součtu označíme hodnoty pro rok 2021. Ukončíme závorky a zkusíme, zda funkce reaguje na změny.
Teď stačí označit pomocnou tabulku a vložit jednoduchý sloupcový graf.
Hotovo. Ještě zkontrolujeme, že jsou přepínače na sobě opravdu nezávislé. Zkusíme změnit výběr v přepínači u prvního grafu a vidíme, že druhý graf nereaguje. Zkusíme to u druhého grafu a každá skupina přepínačů je na sobě nezávislá.
Pro členy Akademie Excelu máme ještě doplňující video, ve kterém vám ukáži, jak můžeme pomocí přepínače změnit typ grafu, který se vám zobrazí. Díky přepínači tak budeme moci vybrat, zda chcete data zobrazit ve sloupcovém, spojnicovém nebo plošném grafu. Pro shlédnutí videa se podívejte na web Akademie Excelu, členství je samozřejmě zdarma a můžete ho kdykoliv zrušit.
2 komentáře
👍👌😊
Děkuji za Vaše videa. Naučil jsem se pomocí nich dostat do kontingenčních grafů zobrazení normy a přepínat grafy mezi ročním a měsíčním zobrazením.
Dobrý den, to mám velkou radost 🙂 moc děkuji 🙂