Nacházíte se: WALL.czExcel návod › Maticové vzorce II.
Kategorie: Excel návod

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

  • 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“

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.

print Formát pro tisk

Sdílet článek:

Komentáře rss

Přidat komentář >

, Počet s log. součtem odpovědět
avatar
Zdravím, asi jsem nějak zacyklený, nemohu přijít na to, jak zapsat maticový vzorec pro následující úlohu.
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
odpověděl(a)
elninoslov
Ak sú všetky bunky vyplnené:
=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
odpověděl(a)
avatar
Díky Martine za reakci, aplikoval jsem tvůj vzorec do tabulky, ale někde je problém. Výsledkem je #HODNOTA! Když vlezu do vzorce přes tlačítko funkce, tak tam je výsledek vyčíslen a správně.
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
odpověděl(a)
elninoslov
Pridám ešte príklad, aby to bolo jasnejšie.citovat
odpověděl(a)
avatar
Perfektní a názorné, moc díky...citovat
, Pěkné odpovědět
avatar
Pěkné ... ale myslím, že to trochu supluje kontingenční tabulku nemyslíte ? Další kapitoly snad budou z jiného soudku. Jinak všechna čest ... z vašich stránek čerpám často moudra.
Díky !!!citovat
, Díky odpovědět
avatar
SUPER!!!! Díky :-)citovat
, Porovnání s odečtem odpovědět
avatar
Dobrý den,

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
icon odpověděl(a)
admin
Dobrý den.
Ano, šlo pomocí funkce SVYHLEDAT http://wall.cz/excel-navod/funkce-svyhledat.citovat
, dotaz odpovědět
avatar
Dobrý den,
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
icon odpověděl(a)
admin
Tady je upravený vzorec:
{=SUMA((A2:A20>=F3)*(A2:A20<=F4)*(B2:B20=F2)*(C2:C20=F5)*(D2:D20))}
Případně může poslat upravený příklad.citovat

Uživatelské menu

Nejste přihlášen(a)
avatar\n

Menu

Formulář Faktura

Formulář Faktura IV

Oblíbený formulář Faktura byl vylepšen a rozšířen.
Více se dočtete zde.

Helios iNuvio

Používáte podnikový systém Helios iNuvio? Potřebujete pomoci se správou nebo vyvinout SQL proceduru? Více informací naleznete na stránce Helios iNuvio.

On-line nástroje