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ů:
- 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ů).
- 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á hodnotaNEPRAVDA
.
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 maticiF2:F14
maticíD2:D14
a získáme maticiH2:H14
, jejímž součtem v buňce H15 získáme celkový součet tržeb, které získal obchodník Jirka. - 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:
- Jirka
- v období leden 2012 – březen 2012
- 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:
- 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á
- 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.
Komentáře
Nemáte oprávnění přidávat příspěvky.
Registrace >
Ztracené heslo >
Potřebuji spočítat u kolika řádků v tabulce je splněna podmínka hodnoty v jednom nebo v druhém sloupci. Prakticky se jedná o uplatnění logického součtu v maticovém vzorci.
První sloupec hodnot je v buňkách A3:A103, kriterium v buňce A1. Druhý sloupec hodnot je v buňkách B3:B103, kriterium v buňce B1.
Takto to nejde:
{=SUMA(($A$3:$A$103<=$A$1)+($B$3:$B$103=$B$1))}
Děkuji předem za radu nebo inspiraci.citovat
=SUM(--(((A3:A103<=A1)+(B3:B103=B1))>0))
=SUMA(--(((A3:A103<=A1)+(B3:B103=B1))>0))
Ak nemá započítavať nevyplnené bunky (lebo prázdna bunka je inak ako 0):
=SUM(--((((A3:A103<>"")*(A3:A103<=A1))+((B3:B103<>"")*(B3:B103=B1)))>0))
=SUMA(--((((A3:A103<>"")*(A3:A103<=A1))+((B3:B103<>"")*(B3:B103=B1)))>0))
Prípadne SUM/SUMA zamente za SUMPRODUCT/SOUČIN.SKALÁRNÍ a nemusí to byť maticový vzoreccitovat
Zkusil jsem tohle a zdá se, že to je funkční, ale poněkud složité:
{=SUMA(KDYŽ(($F$7:$F$261<=$P$1)=($M$7:$M$261=$P$5);($F$7:$F$261<=$P$1)*($M$7:$M$261=$P$5);($F$7:$F$261<=$P$1)+($M$7:$M$261=$P$5)))}
Vzorec, jak jsem ho sem vložil, je zkopírován z reálné tabulky.
Ještě pro doplnění, ve sloupci F jsou hodnoty v podobě datumu, ve sloupci M pak textové hodnoty "ano"/"ne".
Když tvůj vzorec zadám jako maticový, tak funguje, prve jsem jej zadal jako standardní vzorec...citovat
Díky !!!citovat
mohl byste mi prosím poradit.
Mám dva ceníky s rozdílnými cenami, stejnými názvy položek a potřebuji je mezi sebou porovnat, abych viděl rozdíl cen u položek, jednoduše je dát vedle sebe nejde, protože v tom novém jsou vždy nějaké položky navíc.
Vždy jsou dvě pole v každém ceníku 1)název, 2)cena já bych potřeboval, aby excel projel název a v případě, že najde v shodu, aby mezi sebou odečetl ceny ze sloupce ceníku 1 a 2 a já u té dané položky viděl rozdíl.
Myslíte, že to jde realizovat ?
Děkujicitovat
Ano, šlo pomocí funkce SVYHLEDAT http://wall.cz/excel-navod/funkce-svyhledat.citovat
můžete mi prosím poradit, jak poskládat vzorec, kdyby bylo stejné zadání s podmínkou, že chci období např. 5.2.-10.3? Děkujicitovat
{=SUMA((A2:A20>=F3)*(A2:A20<=F4)*(B2:B20=F2)*(C2:C20=F5)*(D2:D20))}
Případně může poslat upravený příklad.citovat