Matice a výpočty - Excel

Úvodem do počtu s maticemi

Matice jsou úžasné pro tvorbu výpočtu, kdy si chcete ušetřit prostor pro zbytečné sloupce s pomocnými výpočty. Pro přehlednost je článek rozdělen na jednotlivé kapitoly

• Trocha teorie - zápis, vložení (klávesová zkratka)
  • Jak vkládat v IOS (Apple)
  • Jak vkládat v Google tabulkách
  • Jak vkládat OpenOffice
  • ...
• Základní výpočty v řádku + - * /
• SUMA základy
• SUMA pokročilý - podmínky, výpočty atd.
• PRŮMĚR
• MEDIÁN
• LARGE, SMALL se SUMA
• LINTREND, ...
• Poslední hodnota ve sloupci
• Funkce pro práci s maticemi INVERZE, SOUČIN
• Ke stažení
• Další články

Trocha teorie

Zápis matice dle verze Excel

Nejprve musíme umět matici zapsat. Nebyl by to Microsoft, aby to trochu nezamotal. Záleží na verzi Excel nejen u vzniku ale i jazykové.

Excel CZ do 2007

={1;2|"leden";"únor"|PRAVDA;NEPRAVDA}

Excel CZ od 2010

={1\2;"leden"\"únor";PRAVDA\NEPRAVDA}

Excel EN do 2007

= {1/2, "leden"/"únor", TRUE/FALSE}

Excel EN od 2010

={1,2;"leden","únor";TRUE,FALSE}

Vložení matice

Vkládání matic nejen v Excel, ale i u "konkurenčních" produktů ;)

Excel Pokud zadáváte matici je nejprve potřeba mít označenou požadovanou oblast a pro vložení použít klávesovou zkratku Ctrl + Shift + Enter
Apple Pro Apple (aby to nebylo jednoduché) je klávesová zkratka ^ + [šipka nahoru] + Return
Google tabulky Pro google tabulky funguje stejná klávesová zkratka jako pro Excel Ctrl + Shift + Enter. Jen do vzorce nedá složené závorky {} ale vloží funkci ArrayFormula(). Dokonce ani nemusíte označovat oblast. Vložíte funkci, vzorec do první buňky a Excel již roztáhne sám, podle velikosti vstupních dat.
Open Office Opět funguje stejná klávesová zkratka jako pro Excel Ctrl + Shift + Enter a stejně jako o Google tabulek není potřeba označovat oblast do které se bude vkládat.

Označení matice

Tady nastává problém, na první pohled nepoznáte jak je velká matice, ale lze to zjistit.

Mít označenou buňku z matice. Karta Domů sekce Úpravy ikona Najít a vybrat z menu Přejít na ... V dialogovém okně Přejít na vybrat klik na Jinak a v dalším dialogovém okně Aktuální matice a následně OK.

Urychlit lze jen zobrazením dialogového okna Přejít na klávesovou zkratkou F5.

Dílčí výpočty v matici

Při testování lze využít možnosti dílčích výpočtu, buňku nebo oblast stačí označit a klik na F9. A dojde k částečnému (úplnému výpočtu). Například:

=SUMA((C5:C8="M")*(F5:F8)) =SUMA(({"M";"F";"F";"M"}="M")*(F5:F8)) =SUMA(({"M";"F";"F";"M"}="M")*({10000;11000;12000;9000})) =SUMA({10000;0;0;9000})

Vrátit původní dynamické odkazy, lze stiskem ESC, nebo o krok zpět přes Ctrl + Z.

Podrobněji

V samostatném článku : Úvod do matic

Prosba

Základní výpočty v řádku

Příklady na * + / - v řádku

Máme tabulku obsahující sloupce počet ks a cenu potřebujeme celkovou hodnotu v řádku. Můžeme použít =C6+D6, ale mina ukázku a procvičení použijeme maticovou funkci, tj.:

=C6:C9*D6:D9

Nezapomenout klávesovou zkratku. Podobně lze použít pro součet rozdíl ...

=C13:C16+D13:D16 =C27:C30-D27:D30

Ukázka základy pro + * / -

Příklady na * + / - () v řádku

Klasickou přednost operátoru v matematice nemusím představovat (násobení má přednost před sčítáním...), naštěstí Excel respektuje, takže si můžete ušetřit závorky. není problém násobit více sloupců.

=C6:C9*D6:D9*E6:E9

Používat závorky, pokud potřebujete dát přednost sčítání před násobením.

=(C13:C16+D13:D16)*E13:E16

Ukázka základy více sloupců pro + * / - ()

Chyby

Nesourodá velikost (jedna z matic má jinou velikost než druhá), nezadáno jako matice, nevhodné závorkování. Bude ukázáno podrobněji v následující kapitole u SUMA.

Poznámky

U řádku je význam rozporuplný, raději člověk zadá klasický vzorec než maticový, ale jde o ukázku, jak maticové funkce fungují a na řádcích se to pěkně ukazuje.

SUMA základy

Tady už je použití maticových funkcí smysluplnější. Ušetří se "pomocný" výpočtový sloupec, který není potřeba, všechno si "zprocesuje" sama funkce (podrobněji o SUMA). Mějme tabulku s několika výrobky.

Výrobky	Poček ks	Cena
Výrobek 1	5	2
...	...	...

Úkolem je zjistit celkovou prodejní cenu za všechny prodané výrobky. Klasickým postupem, se přidá sloupec Celkem, který zjistí kolik se utržilo za daný počet kusů které se prodali v dané ceně. a na konci tabulky se přidá SUMA.

Využitím maticových funkcí tento pomocný sloupec nepotřebujete, matice vše zajistísama ;)

{=SUMA(C6:C9*D6:D9)}

Ukázka SUMA

SUMA pokročilý

Tak SUMA máme za sebou, ale co takhle do SUMA přidat podmínku? To také jde a několika způsoby.

Buď zjištění jen pomocí podmínky, nebo využitím funkce KDYŽ a u použití funkce můžeme KDYŽ lze mít několik syntaxí:

=SUMA((C5:C8="M")*(F5:F8)) =SUMA(KDYŽ(C5:C8="M";1;0)*(F5:F8)) =SUMA(KDYŽ(C5:C8="M";(F5:F8);0)) =SUMA(KDYŽ(C5:C8="M";(F5:F8)))

Další ukázky

Další praktické ukázky v přípravě.

PRŮMĚR

Podobně jako SUMA, lze využít i u výpočtu průměru (více o funkci PRŮMĚR). Pozor na zadání funkce KDYŽ, aby nedošlo k výpočtu průměru z jiných údajů, než potřebujete. Mějte tabulku:

Zaměstnanec	Pohlaví	Log hodnota	Plat	Věk
Ivo	M	PRAVDA	10 000	30
...	...	...	...	...

Potřebujete vypočíst Součet platu pro muže, řešením je například:

=PRŮMĚR(KDYŽ((C17:C23)="M";(E17:E23)))

Pozor na chybné použití:

=PRŮMĚR(KDYŽ((C17:C23)="M";(E17:E23);0))

Pokud si zobrazíte jak funkce počítá F9, zjistíte, že započítává do průměru i nulové hodnoty!

=PRŮMĚR(KDYŽ(({""M"";""F"";""F"";""M"";""M"";""F"";""M""})=""M"";({10000;11000;12000;9000;2000;3000;1000});0)) =PRŮMĚR({10000;0;0;9000;2000;0;1000})"

Ukázka PRŮMĚR

Další ukázky

Další praktické ukázky v přípravě.

MEDIÁN

S funkci MEDIÁN se pracuje úplně stejně jako s PRŮMĚR.

Ukázky jsou v přípravě.

LARGE, SMALL se SUMA

Zajímavé je použití ve spojení s funkcemi LARGE, SMALL (najdou x-tou nejvyšší / nejnižší hodnotu). Takže lze vypsat sumu pěti nejvyšších/nejnižších hodnot. Pro náš příklad si vybereme tři.

Součet tří nejmenších platů

=SUMA(SMALL(E5:E11;{1;2;3}))

Součet tří největších platů

=SUMA(LARGE(E5:E11;{1;2;3}))

Poslední hodnota ve sloupci

=INDEX(A:A;MAX((A:A<>"")*(ŘÁDEK(A:A))))

LINTREND, ...

Funkce LINTREND bude popsána v samostatném článku.

Funkce pro práci s maticemi

Přímo v Excel obsahuje funkce pro práce s maticemi, například:

• INVERZE
• SOUČIN.MATIC

Podrobněji jsem tyto funkce popsal v samostatném článku: VYužití INVERZE a SOUČIN.MATIC ve výpočtu lineárních rovnic.

Ke stažení

Soubor Matice - praktické příklady - Excel ke stažení zdarma. Další praktické příklady v přípravě.

Další články

Články související s funkcemi:

• SUMA
• PRŮMĚR
• KDYŽ
• Matice úvod
• Matice transpozice
• Matice s komplexními čísly

Závěrem

Využívate maticové funkce? Máte nějakou vychytávku, kterou jsem zatím nezmínil? Můžete se zmínit v komentářích.

Článek byl aktualizován: 19.09.2020 11:02

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.