Zdroj: https://wall.cz/excel-navod/maticove-vzorce-ii • Vydáno: 24.5.2012 12:00 • Autor: Poki

Maticové vzorce II.

ExcelMaticové vzorce dokáží jednoduše sčítat hodnoty podle mnoha kritérií nebo zjišťovat počet hodnot, které zadaným kritériím odpovídají. Jejich použití je navíc velice jednoduché a universální.

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. Maticový vzorec

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ů:


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“

Maticový vzorec

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.