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 jsou automaticky uzavřeny 730 dnů po vydání článku.

Komentáře rss

stop Uzamčeno - nelze přidávat nové příspěvky.

, Porovnání s odečtem
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 icon odpověděl(a)
admin
Dobrý den.
Ano, šlo pomocí funkce SVYHLEDAT http://wall.cz/excel-navod/funkce-svyhledat.citovat
, dotaz
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 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

On-line nástroje

Novinky

Formulář Faktura III

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

Aktivní diskuse

excel dodací list

Pavlus • 20.9. 12:48

excel dodací list

tom8562 • 20.9. 7:50

Docházka

Opičák • 19.9. 14:54

Export do TXT

Milan-D • 19.9. 11:23

Export do TXT

Palooo • 19.9. 10:57

Export do TXT

Milan-D • 19.9. 9:28

Export do TXT

Hav-Ran • 19.9. 4:23