Vektory a matice v Exceli

EXCEL – Áno, aj v Exceli sa dá pracovať s vektormi a maticami. Hneď na úvod sa ospravedlňujem všetkým, ktorým spomienka na matematiku zo strednej školy dvihla tlak. Ale keď uvidíte výhody, ktoré majú výpočty s vektormi a maticami v Exceli, tak mi určite odpustíte 😉

Na úvod trošičku máličko teórie o tom, ako tieto pojmy chápeme v Exceli.

Vektor i matica môžu byť v Exceli chápané ako:

a) rozsah buniek:

• vektor = rozsah buniek v jednom riadku alebo v jednom stĺpci,
• matica = rozsah buniek vo viacerých riadkoch a stĺpcoch

b) konštanty zadané manuálne:

• vektor = napr. hodnoty pod sebou: 1; 2; 3; 4 alebo hodnoty vedľa seba: 10\2\18\6\3.
• matica = napr. 1\3;4\2;8\7 (bodkočiarkou v slovenskom Exceli oddeľujeme riadky, opačnou lomkou stĺpce).

Výpočty s vektormi a maticami v Exceli nazývame vzorce poľa (anglicky array formula). Môžete sa stretnúť aj s pojmami vektorové alebo maticové vzorce.

Vektory a matice môžete násobiť, deliť, sčítavať i odčítavať.

Príklad 1

Na jednoduchom príklade vám ukážem, ako výpočet prebieha. Na obrázku sú dva vektory: cena a počet ks. Potrebujeme vypočítať ich násobok. Môžeme to urobiť jednoduchým vzorcom =B2*C2 a tento výpočet skopírovať po posledný riadok. Alebo to môžeme urobiť násobením vektorov.

Pomocou vzorca poľa bude postup nasledovný:

1) vyznačiť výsledný vektor v stĺpci D,

2) vzorec môžeme začať štandardne znamienkom rovná sa (=) pri výbere buniek do výpočtu vyznačíme celý vektor „cena“ krát (*) celý vektor „počet ks“, t.j. =B2:B7*C2:C7

3) vzorec nepotvrdíme Enterom, ale CTRL+SHIFT+ENTER. Vypočíta sa celý vektor, kde bude vynásobená prvá hodnota jedného vektora s prvou druhého vektora, druhá hodnota s druhou, tretia s treťou atď.

Všimnite si, že vzorec je ohraničený zloženými zátvorkami {=B2:B7*C2:C7}. Ak sa niekedy stretnete s týmito zátvorkami pri výpočte v Exceli, jedná sa o vektor alebo maticu.

Tieto zátvorky pri vzorcoch poľa nepíšte z klávesnice. Nebude to fungovať. Excel si ich tam vloží sám po potvrdení CTRL+SHIFT+ENTER.

Príklad 2

V druhom príklade uvidíte, že s maticami sa pracuje úplne rovnako.

Vyznačiť výslednú maticu >> rovná sa >> vybrať prvý vektor >> krát >> vybrať maticu >> CTRL+SHIFT+ENTER. A získame vynásobenú prvú hodnotu vektora s jednotlivými hodnotami prvého riadku matice, druhá s druhým riadkom atď.

Výsledok:

Krásna matematika v Exceli…

Príklad 3

Vzorce poľa sa využívajú aj vo funkciách. Teraz to ešte len začína byť zaujímavé 🙂 Použijem prvé zadanie, kde potrebujeme vypočítať spolu tržby.

Mohli by sme použiť bežné vzorce, kde najskôr vyrátame tržby každého výrobku a potom ich sumu. Vytvoríme zbytočný stĺpec naviac.

Alebo by sme to mohli vypočítať rovno v jednej bunke. Úprimne, už pri treťom riadku ma to prestalo baviť. Našťastie tých výrobkov je iba 6, nie 60…

Keď rozkliknem vzorec, je to pekné farebné. Aspoň niečo pozitívne, keď s tým bolo toľko práce 🙂

Mohli by sme použiť aj funkciu SUMPRODUCT, ale tento článok venujeme vzorcom poľa, preto použijeme násobenie vektorov vo funkcii SUM, t.j.

=SUM(B2:B7*C2:C7) a potvrdiť CTRL+SHIFT+ENTER.

A máme výsledok v jednej bunke.

Vzorce poľa majú využitie aj v komplikovanejších prípadoch, kde bežné vzorce nestačia.

Rovnako ako všetko, tiež majú svoje výhody aj nevýhody:

Výhody:

• bezpečnosť – samostatne sa nedá prepísať iba jedna hodnota vektora/matice. Pri úprave treba vyznačiť všetky bunky vektora/matice >> upraviť >> a nezabudnúť CTRL+SHIFT+ENTER. Takto sa nemôže stať, že niekto omylom prepíše výpočet v niektorej bunke,
• menej výpočtov – viď príklad 3. K dosiahnutému výsledku nám postačila funkcia SUM v jednej bunke,
• menšia veľkosť súborov – keďže je obsadených menej buniek, tak aj celkový excelovský súbor zaberie menej miesta na disku vo vašom počítači.

Nevýhody:

• zmätok, chaos a panika – ak niekto tieto vzorce nepozná, tak to pre neho môže byť mätúce. Nebude si vedieť výpočty upraviť podľa potreby. Vo všetkých bunkách vektora je rovnaký zápis (ešte aj s divnými zátvorkami) a zakaždým iný výsledok,
• nešťastné CTRL+SHIFT+ENTER pre potvrdenie – často sa na neho zabudne,
• môžu byť prácne – vyznačovanie prázdnych buniek pred zadávaním výpočtu môže byť zdĺhavé. Samozrejme nejaké fintičky sa nájdu, ale tiež to nie je len klik klik.

Bez ohľadu na výhody a nevýhody si myslím, že vzorce poľa sú výborné, pretože vďaka nim dokážeme to, čo pomocou iných výpočtov nie.

Posledný príklad bude venovaný vektorom a maticiam, ktoré sú zadané ako konštanty z klávesnice (nie odkazy na bunky).

Príklad 4

Zo stĺpca A potrebujeme vypočítať sumu, ale iba troch najväčších hodnôt. V uvedenom príklade to je 4+5+6=15. Hodnôt je málo, ale predstavte si, že máte 100 riadkov a potrebujete iba sumu troch najväčších.

Toto by sme vedeli vypočítať aj pomocou funkcie SUMIFS. Ani sa mi nechce vytvárať ukážku, ako by to vyzeralo, lebo je to veľa práce… Potrebovali by sme tri funkcie SUMIFS a k tomu 3xLARGE. Ak vám napadlo jednoduchšie riešenie pomocou bežných funkcií, napíšte dolu do komentára, alebo mi pošlite hotový excelovský súbor s príkladom na mail zuzana@zuzanapogranova.sk. Poteším sa kreativite 🙂

Teraz použijeme iba dve funkcie SUM a LARGE (o funkcii LARGE viac tu>>).

=SUM(LARGE(A2:A8;{1;2;3}))

Funkcia LARGE vytvorí vektor, ktorý bude pozostávať z troch najväčších hodnôt, a funkcia SUM ich spočíta.

Vektor je argumentom funkcie, preto treba zátvorky napísať ručne. Na anglickej klávesnici nájdete tieto zátvorky pri písmenách ú a ä (so stlačeným SHIFTom) alebo si ich skopírujte z tohto článku.

Hlavná funkcia SUM je obyčajnou funkciou, tak sa môže potvrdiť iba Enterom.

Šikovný príklad, však? Možno sa niekomu aj zíde 😉

To boli štyri príklady na využitie matíc a vektorov v Exceli. Vzorce poľa sú málo používané. Je to veľká škoda, pretože sa s nimi dajú robiť naozaj divy. Práca s nimi nie je jednoduchá, ale ako povedala nedávno jedna pani na školení: „Nie je to až také ťažké, keď sa to človek naučí“. 😀

Ďakujem za prečítanie tohto článku. Verím, že vektory a matice už máte v malíčku 🙂