V dnešním videu si ukážeme trik, který se může hodit komukoliv, kdo tvoří excelové přehledy, kdo prezentuje data v Excelu a nebo komukoliv, kdo se chce naučit trik, kterým ohromí své kolegy. Trik spočívá v tom, že rozbalovací seznam v buňce kontroluje, jaký výpočet se v buňce provede. Jedná se o skvělý způsob, jak ušetřit místo na excelovém přehledu nebo když potřebujete prezentovat hodně dat na malém prostoru nebo když chcete třeba jen zaujmout svého šéfa nebo kolegy. Navíc si ukážeme, jak tohoto triku dosáhnout s excelovými funkcemi, které už všichni znáte.
Na toto video navazuje i bonusové video na našem webu určeném pro členy, ve kterém si ukážeme, jak pomocí rozbalovacího seznamu ovládat excelový graf. Využijeme k tomu podobnou zdrojovou tabulku jako v dnešním příkladu a pomocí rozbalovacího seznamu v grafu vybereme, pro který produkt se mají v grafu zobrazit tržby.
Výpočet ovládaný rozbalovacím seznamem
Pro dnešní ukázku máme v excelovém sešitu malou zdrojovou tabulku dat, ve které máme uvedené datum tržby, tržbu, barvu produktu a produkt. Vedle tabulky potřebujeme spočítat tři veličiny, součet tržeb, počet prodaných produktů a průměrnou výši tržeb. Tyto veličiny chceme spočítat pro jednotlivé produkty, a to nejen v závislosti na vybraném produktu, ale i barvě.
Abychom si ušetřili čas, tak produkt i barvu vložíme do buněk G4 a G5 jako rozbalovací seznam. Vedle tabulky máme předpřipravený seznam barev a produktů, které máme ve zdrojové tabulce. Klikneme do buňky G4 k produktu a na kartě data vybereme Ověření dat, kde vybereme Seznam. Klikneme do pole zdroj a označíme seznam produktů. Výběr potvrdíme a do buňky se vložil rozbalovací seznam s produkty.
To samé uděláme i pro barvy. Klikneme do buňky G5 k barvě a na kartě data vybereme Ověření dat a seznam. Klikneme do pole zdroj a vybereme seznam s barvami. Výběr a potvrdíme.
Pomocí buňky výpočet (G6) bychom chtěli regulovat, jaký výpočet se pro zadanou kombinaci produktu a barvy spočítá. I do této buňky teď vložíme rozbalovací seznam, nejprve ale vytvoříme zdroje tohoto seznamu. Klikneme někam vedle seznamu produktů a barev (O1) a vytvoříme stejný seznam pro výpočet. Napíšeme seznam výpočet, kde bude Součet, Počet a Průměr. Klikneme do buňky G6 k výpočtu a opět na kartě Data vybereme Ověření dat, seznam a označíme jako zdrojový list seznam s výpočty. Potvrdíme. Základ máme hotový. Tyto tři rozbalovací seznamy budou ovládat výpočet v buňce H6.
Pro jednotlivé výpočty můžeme využít funkce pro výpočet s více podmínkami, tedy funkce SUMIFS, COUNTIFS a AVERAGEIFS. Jednotlivé výpočty si nejprve zkusíme vedle v buňkách.
Začneme s funkcí SUMIFS, která sčítá hodnoty na základě více podmínek. Do buňky G8 napíšeme funkci SUMIFS, kde jako oblast součtu označíme hodnoty, které chceme sčítat. Označíme tedy celý sloupec tržeb (B4:B75), klikneme do první buňky a pomůžeme si klávesovou kombinací CTRL+SHFT a šipka dolů. Nemáme v plánu vzorec stahovat dolů, takže buňky nemusíme fixovat.
Následuje oblast kritérií, začneme s produkty, takže označím sloupec s produkty (D4:D75). Středník a kritérium, což je hledaný produkt, který vybereme v rozbalovacím seznamu (G4). Máme ještě jedno kritérium barvu, takže pokračujeme označením druhé oblasti, což je sloupec s barvami (C4:C75) a druhým kritériem je barva (G5). Ukončíme závorku a funkci potvrdíme. Vrátil se součet tržeb pro zadanou kombinaci produktu a barvy.
Stejně funguje i funkce COUNTIFS, akorát zde není parametr oblast součtu. Rovnou se tedy označují oblasti kritérií a kritéria. Nejprve označíme sloupec s produkty (D4:D75), středník, hledaný produkt (G4), opět středník, sloupec s barvami (C4:C75) a jako druhé kritérium barva (G5). Funkci ukončíme a potvrdíme.
Zbývá si ukázat, jak funguje funkce AVERAGEIFS. Parametry funkce jsou stejné jako u funkce SUMIFS, nejprve se označuje sloupec s hodnotami, ze kterých chceme spočítat průměr, v našem případě sloupec s tržbami (B4:B75). Následuje sloupec s produkty (D4:D75), středník, hledaný produkt (G4), středník, sloupec s barvami (C4:C75), středník a vybraná barva (G5). Ukončíme závorku a funkci potvrdíme.
Teď když víme, jaké funkce pro výpočet použijeme a jak fungují, nezbývá nic jiného než je spojit dohromady. Na výběr máme z několika různých možností, ale abychom to nechali u funkcí, které zná každý, tak použijeme podmínkovou funkci KDYŽ. Abychom ve funkci KDYŽ nemuseli všechny funkce psát znovu, tak je nejprve zkopírujeme do schránky.
Schránku najdeme na kartě Domů pod Kopírováním formátu. Rozklikneme šipku pro otevření schránky. Klikneme na výpočet funkce SUMIFS, označíme výpočet a zmáčkneme CTRL+C pro kopírování a tím se vzorec nakopíruje do schránky. Abychom teď to samé mohli udělat pro vzorec COUNTIFS, tak nejprve zmáčkneme klávesu ESCAPE, abychom se dostali z označení vzorce, to nám dovolí teď označit buňku s dalším vzorcem a to samé uděláme i pro funkci COUNTIFS, klikneme na výpočet, označíme výpočet a zkopírujeme vzorec do schránky, zmáčkneme klávesu ESCAPE, klikneme do buňky se vzorcem pro výpočet AVERAGEIFS, označíme vzorec, zkopírujeme vzorec do schránky a vyskočíme ze vzorce klávesou ESCAPE.
Po nakopírování všech vzorců do schránky bude schránka vypadat jako na obrázku č.8.
Teď to konečně můžeme spojit dohromady. Klikneme do buňky, kde chceme mít výpočet a začneme s funkcí KDYŽ (H6). První podmínkou ve funkci KDYŽ bude, pokud se buňka výpočtu bude rovnat slovu „Součet“, v uvozovkách, středník, tak pokud podmínka splněná bude, tak chceme vrátit výpočet SUMIFS, klikneme do schránky a vložíme vzorec SUMIFS. Vzorec se vložil, akorát se vložil i se znaménkem rovná se, to nepotřebujeme, tak ho smažeme. Následuje druhá funkce KDYŽ, kde podmínkou bude, pokud se buňka výpočtu bude rovnat slovu „Počet“ v uvozovkách, tak chceme vrátit výpočet COUNTIFS. Opět nezapomeneme smazat znaménko rovná se před funkcí COUNTIFS. A jelikož máme jen tři možnosti, tak v opačných případech chceme vrátit výpočet pro AVERAGEIFS bez znaménka rovná se. Ukončíme závorky a funkci potvrdíme.
Zkusíme změnit produkt i barvu abychom ověřili, že funkce reagují. Zkusíme ale kombinaci produktu a barvy, která ve zdrojové tabulce neexistuje a v takovém případě bude funkce vracet nulu u součtu a počtu a chybovou hlášku u výpočtu průměr. Měli bychom tedy funkci opravit. Součet a počet vrací nulu, což je v pořádku, problém dělá jen funkce AVERAGEIFS, která vrací chybovou hlášku. Můžeme se tedy vrátit k funkci KDYŽ, najít funkce AVERAGEIFS a zabalit ji do funkce IFERROR, kde nastavíme, že v případě, že výsledkem funkce bude chyba, že chceme vrátit nulu. Ukončíme závorku u funkce a potvrdíme. Teď, když nebude zadaná kombinace ve zdrojové tabulce existovat, tak se vrátí nuly.
Poslední třešničkou na dortu, aby byl výpočet perfektní je formát čísla. Všimněte si, že v buňce máme nastavený formát měny, to ale není zrovna správně, pokud jako výpočet zvolíme počet. U součtu a průměru chceme jako formát měnu, ale u počtu chceme obecné číslo.
Jak docílíme změny formátu podle výpočtu? Využijeme podmíněného formátování. Klikneme do buňky se vzorcem (H6) a na kartě Domů vybereme Podmíněné formátování a nové pravidlo a zde formátovat pomocí vzorce. Do pole napíšeme =NEBO(G6=“Součet“;G6=“Průměr“) následně vybereme formát.
Jako formát vybereme číslo, měnu a bez desetinných míst. Pravidlo potvrdíme a teď vybereme jako výpočet v buňce H6 počet. Změníme formát buňky H6 na obecné číslo bez desetinných míst. Hotovo. Teď zkusíme vybrat v rozbalovacím seznamu Součet a formát se změnil na měnu, zkusíme vybrat v rozbalovacím seznamu Počet a máme číslo v obecném formátu, zkusíme Průměr a máme zde opět měnu bez desetinných míst.
A takto jednoduše můžete pomocí rozbalovacích seznamů ovládat, jaký výpočet se nad tabulkou provede. A to jen díky kombinaci excelových funkcí, které dobře znáte. Nezapomeňte se podívat na navazující bonusové video, kde si ukážeme, jak pomocí rozbalovacího seznamu ovládat, jaká data se v grafu zobrazí. Nezapomeňte se podívat na navazující video pro členy – Jak ovládat graf rozbalovacím seznamem.
Jedna odpověď
👍👍😊