V dnešním videu se podíváme na praktický příklad, kde na základě dynamicky se měnících kritérií vyfiltrujeme zdrojovou tabulku. A použijeme k tomu funkce jako FILTER, ZVOLITSLOUPCE nebo funkci SORT, které jsou dostupné ve verzích Excelu pro předplatitele Office 365.
Excelový soubor ke stažení:
V příkladu máme rozsáhlou zdrojovou tabulku s prodejními daty, která je ve formátu excelové tabulky s názvem Data. Tato tabulka obsahuje data za posledních několik let a rovněž obsahuje spoustu sloupců.
Naším úkolem je z této zdrojové tabulky na druhý list, který je pojmenovaný jako Přehled, vyfiltrovat pouze data z určitých sloupců a na základě určitých kritérií. Samozřejmě má být vše plně dynamické, tak aby finální tabulka změnila obsah vždy, když změníme nějaké kritérium.
Kromě toho, že musíme zdrojovou tabulku vyfiltrovat podle několika různých kritérií, která musí platit zároveň, tak přehledová tabulka rovněž neobsahuje všechny sloupce jako zdrojová tabulka. Takže musíme zajistit, že se do přehledové tabulky vyfiltrují pouze data z vybraných sloupců.
Začneme nejprve tím, že ze zdrojové tabulky vyfiltrujeme pouze vybrané sloupce. Až budeme mít správné sloupce, tak z těchto sloupců teprve vyfiltrujeme data podle kritérií. K vyfiltrování správných sloupců ze zdrojové tabulky použijeme funkci ZVOLITSLOUPCE neboli funkci CHOOSECOLS. Tato funkce dovolí ze zdrojové tabulky vyfiltrovat pouze sloupce, které stanovíme. Takže napíšeme funkci ZVOLITSLOUPCE, kde nejprve musíme označit celou zdrojovou tabulku a pak následují parametry sloupce. Zde musíme určit pořadová čísla sloupců, které chceme ze zdrojové tabulky vrátit. Do přehledové tabulky chceme vrátit sloupce Datum, produkt, typ produktu, pobočku, počet a tržby. Do funkce ZVOLITSLOUPCE se píší pořadová čísla sloupců ze zdrojové tabulky, takže napíšeme čísla 1, 2, 3, 11, 10 a nakonec 7. Ve funkci ZVOLITSLOUPCE si můžete pořadí sloupců zvolit podle svých potřeb, sloupce mohou být i přeházené. Ukončíme funkci a potvrdíme. Tato funkce teď ze zdrojové tabulky vrátila pouze sloupce, které chceme mít v přehledové tabulce.
Teď když máme vyfiltrované správné sloupce, tak musíme tyto sloupce vyfiltrovat podle kritérií, která máme vede tabulky. Všechna kritéria musí platit zároveň, takže pracujeme s logickou podmínkou A. Pro filtrování použijeme funkci FILTER. Před funkci ZVOLITSLOUPCE napíšeme funkci FILTER, kde jako pole poslouží funkce ZVOLITSLOUPCE, jelikož to je pole, které chceme filtrovat.
Přepneme se do parametru zahrnuje a zde musíme stanovit podmínky. Ve funkci FILTER se logická podmínka A vyjadřuje násobením a každá podmínka musí být ve vlastních závorkách. Takže otevřeme první závorku pro první podmínku a nejprve začneme podmínkou s datumem. Přepneme se do zdrojové tabulky a označíme sloupec datum a datumy mají být vyšší než zvolené datum. Ukončíme závorku a toto je první podmínka.
První podmínku vynásobíme druhou podmínkou, kde ověříme, že produkty spadají pod vybraný druh oblečení. Takže ve zdrojové tabulce označíme sloupec druh oblečení a porovnáme, zda se rovná vybranému druhu oblečení.
Máme ještě jednu podmínku, takže opět násobíme a otevřeme závorku a ověříme, že typ obchodu ve zdrojové tabulce je roven typu obchodu, který máme vybraný v rozbalovacím seznamu.
To jsou všechny podmínky na základě kterých filtrujeme tabulku. Ukončíme funkci FILTER a funkci potvrdíme. Kombinace funkcí FILTER a ZVOLITSLOUPCE vrátila vyfiltrovanou zdrojovou tabulku pro vybrané sloupce a pro řádky, které splňují všechna kritéria. Tabulka se vrátila jako jedno velké dynamické pole, což poznáme podle toho, že když klikneme do tabulky, tak se tabulka orámuje modrou linkou, čímž poznáme, že se jedná o dynamické pole.
Tím by tento příklad mohl klidně skončit. Nicméně pomocí dynamických funkcí můžeme příklad ještě klidně dále vylepšit a přehledovou tabulku udělat daleko více dynamičtější.
Můžeme si například zvolit, zda chceme souhrnnou tabulku seřadit podle datumů nebo podle tržeb. A aby toho nebylo málo, tak si ještě chceme vybrat, zda chceme tabulku seřadit sestupně nebo vzestupně podle vybraného sloupce. Do příkladu jsme dodaly další dva rozbalovací seznamy s typem řazení a řazení podle. V těchto rozbalovacích seznamech si můžeme vybrat, zda chceme seřadit tabulku buď podle datumu nebo tržeb a zda sestupně nebo vzestupně.
U typu řazení máme navíc pomocnou buňku, kde máme funkci KDYŽ, která určuje, že když vybereme Vzestupně, že se má vrátit číslo jedna a když sestupně, tak se má vrátit číslo -1. Toto zde máme jako pomocnou buňku pro parametr ve funkci SORT.
Vrátíme se k funkci a funkci FILTER zabalíme do funkce KDYŽ, kde stanovíme, že pokud bude v rozbalovacím seznamu řazení podle vybráno datum, že chceme seřadit tabulku pode datumů, takže funkce SORT, kde polem je funkce FILTER, následně se přepneme do parametru index řazení, kde stanovíme sloupec, chceme řadit podle datumů, což je první sloupec tabulky, takže napíšeme jedničku. A pak následuje parametr pořadí řazení, kde jednička znamená vzestupně a mínus jednička sestupně. Právě proto zde máme pomocnou buňku, kde jsme stanovili čísla podle rozbalovacího seznamu. Takže zde tuto pomocnou buňku označíme. To je celá funkce SORT.
A pokud podmínka ve funkci KDYŽ splněná nebude, tak to znamená, že chceme souhrnnou tabulku seřadit podle sloupce Tržba. Takže funkce SORT, kde polem bude opět funkce FILTER. Abychom ji nemuseli psát znovu, tak ji jednoduše zkopírujeme a vložíme do funkce SORT. A tentokrát chceme řadit tabulku podle sloupce tržba, což je šestý sloupec tabulky a pořadí řazení je opět určeno pomocnou buňkou. Ukončíme funkce a potvrdíme.
A zkusíme rozbalovací seznamy.
Teď máme plně dynamickou tabulku, která nejenže filtruje zdrojovou tabulku jen pro určité sloupce, ale ještě filtruje na základě mnohonásobné podmínky a nakonec je ještě natolik dynamická, že si můžeme zvolit, podle jakého sloupce a v jakém stylu tabulku seřadíme.
