V dnešním videu si ukážeme, jak můžeme v excelových grafech docílit křížového filtrovacího efektu, který je běžný například ve službách jako je Power Bi. Cílem dnešního videa bude vytvořit dva na sobě závislé grafy, které budeme ovládat pomocí průřezů a které budou křížově filtrované, podle toho, jaké produkty nebo pobočky vybereme v průřezech. To znamená, že když pomocí průřezu vyberu pobočku, tak se v druhém grafu zobrazí pro vybranou pobočku tržby u jednotlivých produktů z celkových tržeb. A když naopak vyberu produkt, tak se mi zobrazí tržby u jednotlivých poboček pro vybraný produkt z celkových tržeb.
Excelový soubor ke stažení
Jak v excelovém grafu vytvořit křížový filtr
V dnešním příkladu máme tabulku dat, kde máme uvedené produkty včetně tržeb a pobočky, ve kterých se tyto produkty prodávají. Naším cílem je vytvořit dva grafy, které by se navzájem křížově filtrovaly.
Začneme tím, že zdrojová data na listu Zdroj změníme na excelovou tabulku. Proč? Jelikož grafy budeme filtrovat pomocí průřezů, které můžeme do Excelu vložit buď ke kontingenčním tabulkám a nebo k excelovým tabulkám. Klikneme do zdrojové tabulky a pomocí klávesové kombinace CTRL+T změníme tabulku na excelovou tabulku. Excelovou tabulku pojmenujeme jako Data a pokud u ní nechceme typické proužkování, tak ho zrušíme na kartě Návrh tabulky, kde vybereme Žádný styl.
Pro vytvoření filtrovacího efektu budeme potřebovat nový list, kde si data ze zdrojové tabulky připravíme. Do Excelu tedy pomocí znaménka plus přidáme nový list, který pojmenujeme jako Příprava.
Vrátíme se na list Zdroj a do excelové tabulky přidáme pomocný sloupec, pomocí kterého budeme určovat, který produkt nebo pobočka byl pomocí průřezu vybrán. Přidáme sloupec do tabulky a pojmenujeme ho třeba Podmínka. V tomto sloupci použijeme funkci SUBTOTAL. Ve funkci SUBTOTAL vybereme jako funkci trojku, tedy POČET2. A jako odkaz vybereme jakoukoliv buňku v tabulce. Třeba tržbu. Funkci potvrdíme a excelová tabulku doplní funkci na každý řádek tabulky. V nevyfiltrované tabulce se na každém řádku objevila jednička. Excelová funkce SUBTOTAL je pro toto řešení vhodná, protože ignoruje skryté řádky. To znamená, že v nevyfiltrované tabulce je na každém řádku jednička, což značí viditelný řádek. Pokud ale tabulku vyfiltruji pro jakýkoliv produkt. Tak na viditelných řádcích zůstanou jedničky, ale na řádcích, které se skryly budou nuly. Což bude základem našeho filtrovacího řešení. Tabulku odfiltrujeme pro všechny řádky a příprava zdrojové tabulky je hotová, takže se přepneme na list Příprava.
Na listu Příprava mám předpřipravené dvě pomocné tabulky. Jedna tabulka pro produkty a druhá pro pobočky. Začneme s pomocnou tabulkou pro produkty. V prvním sloupci tabulky bude jedinečný seznam produktů. Pro vytvoření jedinečného seznamu produktů můžeme použít funkci UNIQUE, ve které označíme celý sloupec ve zdrojové tabulce. Přepneme se na list Zdroj, najedeme myší na záhlaví excelové tabulky na sloupci produkty, počkáme až se z kurzoru myši stane černá šipka směřující dolů a vybereme celý sloupec. Ukončíme závorku u funkce a potvrdíme. Ti z vás, kteří nemají přístup k funkci UNIQUE, tak mohou zkopírovat sloupec s produkty ze zdrojové tabulky, vložit ho někam bokem na list a získat jedinečný seznam hodnot pomocí odstranění duplicit. Tedy označíme seznam a na kartě Data Odstranit duplicity. Takto vytvořený seznam následně můžete vložit do prvního sloupce pomocné tabulky.
V tabulce budeme potřebovat ještě dva sloupce, které jsem pojmenovala jako Vybráno a druhý jako Nevybráno. Ve sloupci Vybráno se objeví součet tržeb pro jednotlivé produkty v případě, že bude pomocí průřezu vybraný produkt. K sečtení tržeb produktů na základě podmínek můžeme použít funkci SUMIFS. Napíšeme funkci SUMIFS, kde nejprve označíme sloupec, který chceme sčítat, tedy sloupec tržeb na listu Zdroj. Následuje první oblast kritérií. Prvním kritériem je produkt. Takže označíme sloupec s produkty, a kritérium je první produkt v pomocné tabulce. Jelikož jsme jedinečný seznam produktů vytvořili pomocí dynamické funkce UNIQUE, tak za první produkt napíšeme křížek, čímž se označí celé rozpětí buněk. Ti z vás, kteří seznam vytvořili bez funkce UNIQUE křížek nepíší a nakonec funkci stáhnou dolů. To ale není vše, máme ještě jednu podmínku. Napíšeme středník a oblast kritérií je pomocný sloupec ve zdrojové tabulce, a kritérium je, že zde bude jednička, tedy, že hodnota bude viditelná. Teď je funkce hotová, potvrdíme ji a jelikož jsme použili křížek u dynamického rozpětí tak se funkce sama doplnila na všechny řádky. Ostatní musí funkci stáhnout dolů. Funkce vrátila tržby na každý řádek, jelikož ještě nemáme vložené průřezy, pomocí kterých bychom filtrovali hodnoty. Zdrojová tabulka je tedy nevyfiltrovaná a proto se tržby ukázaly u všech produktů.
Zbývá druhý sloupec Nevybráno. V grafech je použitý skládaný pruhový graf, který zobrazuje vybranou tržbu, ale zároveň i celkovou tržbu, takže v tomto sloupci potřebujeme, aby se zobrazila zbývající tržba, v případě nevybraných produktů. Zde použijeme rovněž funkci SUMIFS, kde nejprve označíme sloupec s tržbami ve zdrojové tabulce, následuje oblast kritérií, což je sloupec s produkty a jako kritérium první produkt v pomocné tabulce. Ti, co použili na vytvoření seznamu produktů funkci UNIQUE ještě za produkt napíší opět křížek. Druhou podmínku nepotřebujeme, takže ukončíme závorku a od této funkce SUMIFS odečteme hodnotu ze sloupce Vybráno. Ti z vás, kteří mají přístup k dynamickým funkcím opět za tuto buňku napíší křížek. Teď je funkce hotová, potvrdíme ji a funkce se doplní na všechny řádky. Ostatní musejí funkci stáhnout dolů pro všechny řádky.
Abychom pochopili, co tyto sloupce počítají, tak vložíme do sešitu první průřez. Klikneme na list Zdroj, klikneme do excelové tabulky a na kartě Návrh tabulky vybereme Vložit průřez. Jako pole průřezu vybereme produkty, potvrdíme a do sešitu se vložil průřez.
Průřez označíme, pomocí CTRL+X ho vyjmeme a vložme ho ve dle pomocné tabulky na list Příprava. Průřez trochu upravíme, zvětšíme ho, ať vidíme všechny položky. Zkusíme vyfiltrovat průřez pro produkt 1. Jakmile použijeme filtr, tak se obsah pomocné tabulky změní. Ve sloupci vybráno zůstala u produktu 1 sečtená celková tržba pro produkt 1, ve sloupci nevybráno je nula a u ostatních produktů je ve sloupci nevybráno celková tržba pro jejich produkty.
Zkusíme vybrat produkt 5 a funkce v pomocné tabulce reaguje stejně. Teď máme celkovou tržbu pro produkt 5 ve sloupci Vybráno a ostatní tržby jsou ve sloupci nevybráno. Funkce tedy reagují správně.
Zbývá dodělat pomocnou tabulku pro pobočky. Systém tabulky bude naprosto identický jako u první pomocné tabulky. Začneme prvním sloupcem, kde bude jedinečný seznam poboček. Funkce UNIQUE, kde na listu Zdroj označíme sloupec s pobočkami a funkci potvrdíme. Další dva sloupce budou opět Vybráno a Nevybráno.
Ve sloupci Vybráno použijeme opět funkci SUMIFS, kde nejprve označíme sloupec tržby ve zdrojové tabulce, následuje oblast kritérií, což je tentokrát sloupec s pobočkami, prvním kritériem je první pobočka v pomocné tabulce, s křížkem pro dynamické rozpětí. Druhým kritériem je, zda je pobočka vybrána, takže pomocný sloupec ve zdrojové tabulce a jako kritérium jednička. Funkci ukončíme a potvrdíme.
Rovnou doplníme i sloupec nevybráno. Funkce SUMIFS, kde označíme sloupec s tržbami, následuje sloupec s pobočkami a první pobočka s křížkem. Ukončíme funkci a odečteme od ní první hodnotu ze sloupce vybráno s křížkem. Ukončíme funkci a potvrdíme.
Teď musíme vložit druhý průřez pro pobočky. Přepneme se na list Zdroj, klikneme do zdrojové tabulky a z Návrhu tabulky vložíme průřez, kde tentokrát bude jako pole průřezu Pobočka. Průřez označíme, vyjmeme a vložíme ho zatím na list Příprava. Odfiltrujeme oba průřezy, tak aby nebylo nic vybraného a vidíme, že v tomto případě jsou všechny sečtené tržby ve sloupcích vybráno. Zkusíme vybrat libovolný produkt. Třeba produkt 4. A vidíme, že reagují obě pomocné tabulky. V tabulce s produkty se ve sloupci vybráno zobrazila celková tržba pro vybraný produkt. Všimněte si ale, že reagovala i pomocná tabulka s pobočkami. Zde se zobrazila ve sloupci vybráno celková tržba u poboček pro vybraný produkt. To znamená, že celková tržba produktu 4 je 155 706 Kč. A největší tržbu na produktu 4 dělá Ostrava, kde se ho prodalo za necelých 95 tisíc korun. Když sečteme hodnoty pro vybrané pobočky, tak dojdeme k celkové tržbě produktu 4. Ve sloupci nevybráno u poboček je zbývající tržba, která je tvořená jinými produkty, než je produkt 4. Zkusíme vybrat jiný produkt, třeba produkt 7 a tabulky reagují obdobně.
Zrušíme filtr na produktech a zkusíme vybrat v průřezu pobočku, třeba Ostravu. Teď se zobrazila celková tržba pobočky Ostrava v tabulce s pobočkami a v tabulce s produkty se rozdělila tržba Ostravy mezi jednotlivé produkty.
Teď vložíme z tabulek excelové grafy. Grafy vložíme na samostatný list, který pojmenujeme Grafy. Označíme první tabulku s produkty a na kartě Vložení vybereme Grafy a v nabídce grafů vybereme skládaný pruhový graf. Potvrdíme a graf se vložil na list. Ještě musíme vložit druhý graf. Takže označíme druhou tabulku s pobočkami a na kartě Vložení opět vybereme Grafy a skládaný pruhový graf. Grafy a průřezy označíme, držíme klávesu CTRL a postupně je označujeme, když je máme všechny označené, tak je vyjmeme pomocí kláves CTRL+X a vložíme to všechno na list Grafy.
Teď je to jen o formátování a umístění průřezů a grafů na listu. Chceme, aby byl efekt filtrování na první pohled viditelný, takže označíme datovou sadu, která reprezentuje sloupec nevybráno a změníme u ní barevnou výplň na světle šedou, stejně jako orámování uděláme stejnou světle šedou barvou. Naopak druhou datovou sadu označíme a změníme u ní barevnou výplň na tmavě modrou. V grafu smažeme pomocné vodící čáry, legendu grafu a jelikož vložíme do grafu popisky, tak můžeme smazat i osu x. Název grafu změníme na Tržby celkem dle produktů. Ještě rozšíříme sloupce. Klikneme na sloupce a vybereme Formát datové řady a rozšíříme sloupce.
Poslední, co musíme udělat je vložit popisky do grafu, a to k řadě, která je vybrána. Označíme datovou sadu vybráno a vybereme vložit popisky dat. Ještě je můžeme zformátovat. Označíme popisky a zmenšíme u nich písmo a změníme barvu na bílou, jelikož jsou na tmavém podkladu. Ještě můžeme upravit to, že nechceme, aby se u produktů, které nejsou zrovna vybrané, nezobrazovaly tyto nuly. To můžeme udělat pomocí vlastního formátu. Označíme popisky a vybereme Formát popisků dat. Pod číslem vybereme Vlastní formát a zde nastavíme vlastní formát, speciálně, co se má zobrazovat pro nulové hodnoty. Začneme klasickým zobrazením pro kladné hodnoty ve formátu s oddělenými tisíci # ### „Kč“ a měnou v korunách. Ten samý formát bude i pro záporné hodnoty a pro nulové hodnoty dáme jen mezeru. Tím zajistíme, že se u nul nezobrazí nic. Přidáme vlastní formát a potvrdíme. Teď se u produktů, které nejsou zrovna vybrané nezobrazí žádný popisek.
Teď když máme graf zformátovaný, tak ho označíme a abychom si ušetřili čas s formátováním druhého grafu, tak formát přeneseme. Zkopírujeme celý graf, označíme druhý graf a na kartě Domů vybereme Vložit a zde Vložit jinak a Formát. Formát se přenesl na druhý graf a formátování grafů je tím pádem hotové. Ještě rychle ověříme, že filtrace funguje.
Teď usadíme průřezy a grafy na list a máme hotovo.
Jedna odpověď
😊