Maticové vzorce II.

V prvním dílu našeho miniseriálu Maticové vzorce I. jsme si ukázali základy a možnosti maticových vzorců obecně. Dnes se už podíváme na příklady praktického využití; konkrétně na to, jak maticové vzorce použít k podmíněným součtům a počtům.

Můžete namítnout, že toto vám spolehlivě nahradí vestavěné funkce Excelu SUMIFS a COUNTIFS, ale pokud budete chtít použít jako kritérium výsledek funkce konkrétních záznamů, už se dostanete do problémů, a to určitě není výjimečný případ.

Nejdůležitější je pochopit logiku, protože pak už lze odvodit cokoliv. Jak tedy podmínky v maticových vzorcích vlastně fungují?

V následujícím příkladu budeme chtít zjistit, jaké tržby přinesl společnosti obchodník Jirka.

Logika podmíněných součtů:

1. Protože nám jde o Jirku, napíšeme si jeho jméno do buňky F1 (pokud tuto buňku potom přepíšeme na David, hned se vzorce přepočtou a zjistíme, jakých tržeb dosáhl David – je tedy lepší používat odkazy na buňky, než „natvrdo“ napsané hodnoty do vzorců).
2. Abychom vybrali pouze tržby, které přinesl do společnosti Jirka, potřebujeme získat matici, která bude odpovídat oblasti B2:B14=F1 –> tu máme ve sloupci F (jak vytvořit matici uloženou v oblasti jsme se naučili v prvním dílu miniseriálu).
   V řádcích, kde je ve sloupci B hodnota Jirka, je logická hodnota PRAVDA; a kde je jiná hodnota než Jirka, tam je logická hodnota NEPRAVDA.
   Logické hodnoty PRAVDA a NEPRAVDA lze zapsat také jako 1 a 0 (pro názornost je toto uvedeno ve sloupci G).
   Protože máme v řádcích, kde je JIRKA, hodnoty 1 a v ostatních řádcích hodnoty 0, můžeme jen vynásobit matici F2:F14 maticí D2:D14 a získáme matici H2:H14, jejímž součtem v buňce H15 získáme celkový součet tržeb, které získal obchodník Jirka.
3. Celý tento postup je ovšem jen pro ilustraci toho, jak matice fungují. Spočítat příspěvek Jirky k tržbám lze v jediné buňce J2 vzorcem, který je vlastně jen sloučením všech zmíněných vzorců (výsledný vzorec můžete vidět na obrázku výše v řádku vzorců): =SUMA((B2:B14=F1)*(D2:D14)) - a protože je to maticový vzorec, musí být ukončen trojhmatem Ctrl+Shift+Enter.

POZOR! lze násobit pouze matice, které mají stejný počet řádků (jednosloupcové matice)!

Logika podmíněných počtů:

• Podmíněné počty jsou vlastně jen jednodušší variantou podmíněných součtů, protože jde vlastně jen o součet našich logických hodnot ve sloupci F (protože ale ve sloupci F máme hodnoty PRAVDA a NEPRAVDA, musíme matici F2:F14 ještě vynásobit konstantou 1). Počet uzavřených obchodů, které provedl Jirka lze tedy zjistit tímto vzorcem: =SUMA((B2:B14="Jirka")*(1))

Více podmínek

V praxi se setkáváme především s případy, kdy potřebujeme získat výsledky na základě více podmínek než pouze jedné (často 5 i více).

V našem příkladu budeme chtít zjistit, jaké tržby získal:

1. Jirka
2. v období leden 2012 – březen 2012
3. v oblasti „sport“

V řádku vzorců na obrázku vidíte vzorec zapsaný v buňce H2. Jde jen o „naskládání“ podmínek za sebe, čímž se jednotlivé matice navzájem vynásobí a zůstanou jen ty ‚jedničky‘, které nás zajímají. Tyto matice jsou pak vynásobeny maticí ve sloupci D.
Všimněte si také toho, že ve vzorci nejsou matice definovány jako např. D2:D14, ale D2:D20, ačkoliv poslední zaplněný řádek je řádek č.14

Výhody:

1. kdykoliv můžete změnit kritéria pouhým přepsáním (v buňkách F2:F4) a hned se výsledek přepočítá
2. jelikož může být oblast matice větší, než je počet hodnot (poslední zaplněná buňka není D20, ale D14), lze tedy do řádků jen doplňovat další záznamy a vzorec bude stále korektní – maticové vzorce lze tedy úspěšně používat na výjezdy z databází a nezáleží na počtu záznamů (pokud máte dostatečně nastavenou délku matic)

Jako v prvním dílu, bych rád připomněl, že s maticovými vzorci můžeme použít daleko více funkcí než jen SUMA, velmi časté jsou funkce PRŮMĚR, SMALL, LARGE nebo např. ŘÁDEK. Jak lze tyto funkce navzájem kombinovat a získat tak zajímavé a universální řešení si povíme příště…

Soubor s příklady maticových vzorců z článku excel_maticove_vzorce_2.zip.