V dnešním videu se podíváme na to, jak vyřešit problém, na který můžete narazit při používání funkce SUMIFS nebo COUNTIFS s dynamickými poli včetně funkcí jako SROVNAT.SVISLE a ZVOLITSLOUPCE. Kromě toho, že si vysvětíme, proč tento problém vzniká, jelikož to na první pohled není zcela jasné, tak si rovněž ukážeme, jak tento problém efektivně vyřešit. A to hned dvěma způsoby.
Excelové soubory ke stažení:
V příkladu máme dvě malé excelové tabulky, které obsahují tržby po produktech. První tabulka je nazvaná jako Zdroj a druhá jako Data. Naším úkolem sečíst tržby z obou tabulek pro vybraný produkt, který si vybereme v rozbalovacím seznamu. Abychom mohli tržby pro produkty sečíst, tak nejprve musíme spojit tabulky dohromady. Celý příklad chceme vyřešit pomocí dynamických polí, jelikož chceme zajistit, že se nově přidané produkty zahrnou do sloučené tabulky.
Začneme tím, že sloučíme tabulky dohromady v pomocné tabulce. Klikneme do první buňky tabulky a napíšeme funkci SROVNAT.SVISLE neboli funkci VSTACK. Tato funkce srovná pod sebe oblasti, které ve funkci označíme. Takže jako první pole vybereme celou první excelovou tabulku Zdroj, středník a pod ní chceme srovnat druhou excelovou tabulku Data. Ukončíme funkci a potvrdíme. A máme vytvořenou sloučenou tabulku.
To byla ta lehčí část.
Teď musíme sečíst tržby z této sloučené tabulky pro vybraný produkt.
Samozřejmě by nás logicky mohlo napadnout použít funkci SUMIFS, která sčítá hodnoty na základě více podmínek. Začneme s funkcí SUMIFS, kde označíme nejprve oblast pro součet, což je sloupec tržba ze sloučené tabulky. Následuje oblast kritérií, což je sloupec produkty ze sloučené tabulky a následuje kritérium, což je produkt z rozbalovacího seznamu. Funkci potvrdíme a funkce SUMIFS vrátí správný výsledek. Nicméně tento postup má jednu velkou nevýhodu. Víte jakou?
Když do jedné z excelových tabulek přidáme další data pro vybraný produkt, tak se tento nový produkt zahrne do sloučené tabulky, ale nepromítne se do celkového součtu. Proč? Odpověď najdeme, když klikneme do funkce SUMIFS. Ve funkci SUMIFS jsme se nesprávně odkázali na sloučenou tabulku, takže ve funkci máme fixní rozpětí v oblastech a nový produkt je mimo toto rozpětí a proto se nezahrnul do součtu. Toto tedy není správný postup, pokud chcete zajistit, že bude vše pně dynamické a že budou všechny výpočty reagovat na nově přidané produkty do tabulek.
Funkci SUMIFS smažeme a smažeme i nový produkt, který jsme přidali do tabulky cvičně a zkusíme to jinak.
Začneme opět s funkcí SUMIFS, ale tentokrát se na sloupce sloučené tabulky odkážeme správně. Funkce SUMIFS, kde musíme nejprve označit oblast pro součet. Oblast pro součet je sloupec s tržbami ve sloučené tabulce, což je druhým sloupcem sloučené tabulky. Abychom zajistili, že se na sloupec odkážeme dynamicky, tak použijeme funkci ZVOLITSLOUPCE neboli funkci CHOOSECOLS. V této funkci označíme sloučenou tabulku pomocí křížku a chceme zvolit druhý sloupec. Následuje oblast pro kritérium, což je prvním sloupcem sloučené tabulky, takže napíšeme opět funkci ZVOLITSLOUPCE, kde se opět křížkem odkážeme na celou sloučenou tabulku a kritérium se nachází v prvním sloupci. A jako kritérium označíme produkt z rozbalovacího seznamu. Funkci ukončíme a potvrdíme a chyba. Vyskočí na nás chybová hláška, která nám sice naznačuje, že ve funkci máme chybu, ale jinak je naprosto k ničemu, jelikož nám neříká nic o tom, kde máme chybu.
Hlášku odklikneme a podíváme se na funkci. Chyba v tomto případě vzniká, jelikož zde máme nesoulad v polích. Funkce SUMIFS potřebuje jako první dva parametry oblasti neboli anglicky ranges. Nicméně funkce ZVOLITSLOUPCE vrací jako výsledek arrays neboli dynamická pole. Proto vzniká chyba. Ve funkci SUMIFS nebo i COUNTIFS a příbuzných funkcích nemůžeme v parametrech oblast použít dynamická pole a tyto nové funkce jako SROVNAT.SVISLE nebo ZVOLITSLOUPCE.
Nicméně řešení existuje a jsou rovnou dvě.
První možností je obejít to pomocí funkce INDEX. Funkce INDEX vrací řádky
nebo sloupce podle zadaných parametrů. Nejprve si to ukážeme. Napíšeme funkci
INDEX, kde jako pole označíme sloučenou tabulku s křížkem a chceme vrátit
všechny hodnoty ze druhého sloupce, takže parametr řádek přeskočíme a jako
sloupec vyplníme dvojku. Když funkci ukončíme a potvrdíme, tak se vrátí celý
sloupec hodnot ze sloučené tabulky jako dynamické pole.
Takže můžeme napsat funkci SUMIFS, kde v parametr oblast součtu použijeme funkci INDEX, kde označíme sloučenou tabulku a chceme vrátit druhý sloupec. Následuje parametr oblast kritérií, kde opět použijeme funkci INDEX, kde opět označíme celou oblast sloučené tabulky a chceme vrátit první sloupec. A jako kritérium označíme produkt z rozbalovacího seznamu. Ukončíme funkci a potvrdíme a máme správný součet. Když do tabulky přidáme nový produkt, tak součet reaguje.
To je první možnost, jak příklad vyřešit.
Druhou možností je použít funkci FILTER. A v tomto případě nemusíme ani tvořit pomocnou sloučenou tabulku. Zkusíme složit celou funkci FILTER dohromady jako kdybychom žádnou pomocnou mezi tabulku neměli. Ve funkci FILTER potřebujeme dva parametry. Prvním parametrem je pole, které chceme filtrovat, což jsou hodnoty, takže druhý sloupec tabulky. A následuje parametr zahrnuje, což je logická podmínka, podle které filtrujeme, což je, že se první sloupec tabulky s produkty musí rovnat vybranému produktu. Takže začneme tím, že určíme první parametr do funkce FILTER, tedy pole pro filtrování. Nejprve sloučíme tabulky dohromady pomocí funkce SROVNAT.SVISLE. Z této sloučené tabulky chceme filtrovat druhý sloupec, takže funkce ZVOLITSLOUPCE, kde chceme vrátit druhý sloupec. To je první parametr funkce FILTER. Takže to celé zabalíme do funkce FILTER, a přepneme se do druhého parametru, kde logické pravidlo je, že se první sloupec sloučené tabulky má rovnat vybranému produktu, takže si pomůžeme, zkopírujeme tuto část, kde jsme vytvořili odkaz na sloučenou tabulku a jen změníme odkaz ze druhého sloupce na první. A rovná se vybranému produktu. Ukončíme funkci FILTER a potvrdíme a máme správný výsledek. Samozřejmě, pokud do tabulek přidáme nový produkt a novou hodnotu, tak se vše automaticky započítá.
