Trikovi u Excelu 17. DEO – Matrične formule (formule nizova, “array” formule): Nemoguće kalkulacije su (ipak) moguće

O matričnim formulama (formulama nizova ili „array“ formulama).

Formule nizova, formule matrica ili „array“ formule (sve su ovo sinonimi) spadaju među najmoćnije a opet najmanje poznate i korištene Excelove alate. One se zasnivaju na korištenju niza ćelija (raspona) umjesto jedne ćelije u formuli. Na taj način moguće je napraviti kalkulacije za koje su inače potrebne dodatne kolone, ili ih je nemoguće napraviti.

Ovdje ćemo zagrebati samo vrh ledenog brijega jer bi detaljna obrada ove teme zahtjevala poseban blog. Dakle, imajte na umu da ono što slijedi nikako nije sve već tek mali dio onoga što je moguće postići ovim formulama.

Dvije osnovne vrste  matričnih formula su višećelijske i jednoćelijske. Višećelijska matrična formula  se unosi u više ćelija odjednom, dok se jednoćelijska unosi samo u jednu ćeliju.

Na slici 1 je primjer nabavke kancelarijskog materijala s opisom artikala, jediničnim cijenama i  količinom. Pretpostavimo da želite da izračunate ukupnu cijenu nabavke. Vjerovatno ćete u novoj koloni ubaciti formulu koja množi cijenu sa količinom. Nakon toga biste napravili zbir vrijednosti u toj koloni.

Istu stvar možete napraviti i pomoću matrične formule na sljedeći način:

–           odaberite raspon ćelija (dakle, ne samo jednu) u koji želite da ubacite rezultat i otkucajte znak jednakosti (=)

–          Odaberite ćelije sa količinama (C9:C13) a zatim otkucajte znak za množenje (*)

Odaberite ćelije sa cijenama (D9:D13). Trebalo bi da piše:  =C9:C13*D9:D13.  Pritisnite kombinaciju tipki CTRL+SHIFT+ENTER  (zato što se matrične formule ubacuju pomoću ove kombinacije tipki a ne pomoću tipke ENTER).

Slika 3 Rezultat višećelijske matrične formule

Dobili ste isti rezultat. Vitičaste zagrade ne ukucavate već ih Excel sam dodaje kada pritisnete CTRL+SHIFT+ENTER. Po njima prepoznajete da je u pitanju matrična formula.

Kako ova vrsta formula radi? Upoređuje dva odabrana niza i množi svaku ćeliju iz jednog niza sa ćelijom u istom redu u drugom nizu. Ovo je bio primjer višećelijskih matričnih formula.

Ah, da, već čujem pitanje: zašto bih to radio baš tako? Paaaaa, ima nekoliko prilično čvrstih argumenata a nabrojaću samo najvažnije:

–          Na ovaj način ste sigurni da je u svim ćelijama ista formula,

–          Matrična formula se ne može lako slučajno obrisati zato što se ne može mjenjati dio matrične formule, već samo cijeli niz odjednom,

–          Ne mogu se dodati ni novi redovi ni obrisati postojeći unutar formule nizova (ovo može biti i mana pa treba paziti gdje koristiti formulu nizova),

–          Ovakvu formulu ne može promjeniti baš svako  a naročito ne neki početnik itd.

Dobro, ali zadatak je bio dobiti zbir svih troškova a ne po artiklima. Naravno, i to možete dobiti pomoću matrične formule, ovaj put jednoćelijske.  Naročito je bitno da vam za ovaj eksperiment nije potrebna dodatna kolona  pa ćemo je odmah odbaciti (vidi vraga, šta je sad ovo?).

U željenu ćeliju otkucajte :  =SUM(C9:C13*D9:D13) i pritisnite kombinaciju tipki CTRL+SHIFT+ENTER. Dobićete zbir proizvoda cijena i količina.

I, taman kad sam pomislio da sam ušutkao dosadnog pametnjakovića sa početka priče, javlja se drugi: „oprostite, ali ja mislim da smo isti rezultat mogli dobiti i pomoću funkcije SUMPRODUCT. Znate, ta funkcija množi dva niza ćelija i daje zbir proizvoda“.

Svakako, mogli ste, mogli ste (grrrrrrrrrrrrr… cenzurisano). Inače, već dugo muku mučim sa kolegom u kancelariji da prestane koristiti SUMPRODUCT za sve moguće izračune, ali uzalud. Uhvatio se k’o pijan plota i ne pušta. Ne osporavam, SUMPRODUCT ima odličnu osobinu: računa i podatke iz linkovanih fajlova bez potrebe da ih se otvara. Ali to je sve.  SUMPRODUCT je samo to: zbir proizvoda i ništa više.

Sa druge strane, pomoću matričnih formula možete koristiti bilo koju funkciju i primjeniti je na nizove ćelija (SUM, AVERAGE, VLOOKUP (!) itd.).  E, sad… ako vam je potreban samo zbir proizvoda, u redu, eto vam SUMPRODUCT (baš kao u onom vicu kada čovjek odgovara samoubicu koji pokušava da se baci sa mosta i govori mu kako je vrijeme lijepo a ovaj kaže da mrzi vrućine, pa mu govori o lijepim ženama koje šetaju okolo a on kaže kako ih mrzi jer ga je njegova napustila pa… Na kraju nesuđeni spasilac ispaljuje posljednji adut: „Evo, i Zvezda će osvojiti titulu“. Na to samoubica odgovara: „E, baš me briga za Zvezdu!“  – E, skači onda, p… ti materina grobarska!“).

Gdje sam ono stao? Aha, da: =SUM(C9:C13*D9:D13) a zatim CTRL+SHIFT+ENTER. Vjerovatno ste primjetili da se ova formula unosi samo u jednu ćeliju. Ovakve formule nizova se nazivaju jednoćelijske formule nizova. Inače, ma koliko višećelijska formula niza sa početka izgledala atraktivnije, jednoćelijska je mnogo moćnija, kao što ćete vidjeti u nastavku.

Kad god učim neku novu funkcionalnost, volim da znam kako radi. Dakle, hajde da se zavučemo „pod haubu“ posljednje formule =SUM(C9:C13*D9:D13) i da vidimo kako to ona radi a da joj nije potrebna dodatna kolona.

Kada posmatrate jednoćelijsku formulu niza, gledajte uvijek od sredine prema van. U našem slučaju =SUM(C9:C13*D9:D13) u sredini je množenje ćelija iz dva raspona: C9 se množi sa D9, C10 se množi sa D10, C11 se množi sa D11, C12 se množi sa D12 a C13 se množi sa D13. Rezulati tih množenja čine niz brojeva (125;300;62.5;18.75;29)). Ovaj niz međurezultata nije vidljiv i postoji samo u Excelovoj memoriji, a izgleda ovako: =SUM({125;300;62.5;18.75;29}).

Sada idemo prema van i dolazimo do funkcije sabiranja (SUM). Ona  uzima prethodno dobijeni niz proizvoda  i pravi zbir njegovih članova. Na ovaj način dobija se zbir proizvoda dva niza (količine i cijene).  Dakle, Excel prvo na nizu ćelija ili više njih napravi kalkulaciju a onda se na niz tako dobijenih rezultata primjeni druga funkcija.

Selektovanje cijelog niza kod višećelijskih matričnih formula:

Kada iz bilo kojeg razloga želite promjeniti ili obrisati višećelijsku matričnu formulu, morate je prethodno cijelu odabrati jer ne možete obrisati ili promjeniti samo jedan njen dio. Ukoliko ne znate koje su dimenzije željenog niza, kliknite bilo gdje unutar niza sa višećelijskom matričnom formulom, pritisnite tipku F5 a zatim dugme „Special“.

Odaberite „Current Array“ (aktivni niz) i kliknite na dugme „OK“. Tako ćete odabrati cijeli niz koji sadrži matričnu formulu.

Izmjena postojeće formule niza

Nekada će biti potrebno promjeniti (smanjiti, povećati i sl.) niz ili nizove koji se koriste u formuli. Odaberite cijelu matričnu formulu (kao što je objašnjeno u prethodnom pasusu), pritisnite tipku F2, napravite izmjene i pritisnite kombinaciju tipki CTRL+SHIFT+ENTER. Pazite: ako slučajno zaborative pristisnuti CTRL+SHIFT+ENTER pa pritisnete samo ENTER ili CTRL+ENTER, dobićete poruku sa greškom ili pogrešne rezultate. U tom slučaju, samo pritisnite tipku F2 a zatim CTRL+SHIFT+ENTER.

Mane matričnih formula /formula niza/“array“ formula

Na nekoliko mjesta sam naišao na napomene da ova vrsta formula usporava rad kompjutera, ali to još nikad nisam primjetio (a radim sa jako velikim nizovima i kompleksnim formulama), tako da mislim da je ovdje u pitanju prilično slabašan kompjuter. Kako jednom reče Lane Gutović: „Imala je toliki nos da kada bi kiša padala, cigareta ne bi pokisnula… A pušila je na muštiklu.“

Stvarni problem nastaje kada fajl sa takvim formulama date nekome ko nije upoznat sa njima a poželi da nešto mijenja a ne prihvata ponuđenu pomoć. Kad god se to desi, ponesemo mezu i piće i jako se dobro zabavimo.

Isto tako, lako se može desiti da nakon nekog vremena ni sami ne znate kako radi formula koju ste napravili. Zato je dobro dokumentovati, koliko god to dosadno bilo. Za zgodan način dokumentovanja unutar same formule pogledajte ovdje.

Ukratko

Kako reče jedan moj prijatelj: „Pa, da remiziramo“. (Nije greška u kucanju, on tako kaže a ja nemam srca da ga ispravljam. Samo: psssssssssst!).

–          Formule niza (matrične formule, „array“ formule) služe sa kalkulacije koje koriste nizove ćelija i za koje inače trebaju dodatne kolone ili ih je nemoguće izvesti

–          Kod višećelijskih formula se unosi u više ćelija odjednom a kod jednoćelijskih u jednu ćeliju

–          Formula se potrvđuje pritiskom na kombinaciju tipki CTRL+SHIFT+ENTER.

–          Prepoznaju se po vitičastim zagradama. Inače, njih se ne ukucava već ih Excel sam dodaje

–          Višećelijske se mogu mjenjati ili brisati isključivo kada se odabere cijeli niz koji se koristi

–          Mogu usporiti rad kompjutera ako je riječ o jako velikim nizovima (među nama, stalno ih koristim, ali takvu situaciju još nisam imao), ali inače su brže i od VBA (makro) koda koji ima istu funkcionalnost.

Mala napomena: formule će biti puno čitljivije ako koristite imenovane raspone umjesto nerazumljivih adresa ćelija. Za objašnjenje imenovanih raspona pogledajte ovdje.

Korisni primjeri („a sad, spektakl!“)

U nastavku je nekoliko primjera jednoćelijskih matričnih formula koje bi vam mogle poslužiti, ili dati ideju za nove. Ukoliko ste pročitali tekst dovde a još ne vidite prednosti formula nizova, nastavite čitati. Vjerujte mi na riječ, kada primjenite neku od ovih formula, neupućeni će misliti da ste čarobnjak.

Zbir 3 najmanje vrijednosti u rasponu

 Funkcija LARGE nalazi 3 najveće vrijednosti i pohranjuje ih u međuniz. Zatim funkcija SUM sabira sve članove tog međuniza.

  Zbir raspona ćelija koji sadrži grešku

Ova formula sabira ćelije u zadanom rasponu i ignoriše ćelije sa greškama (tretira ih kao prazne ćelije).

Prosjek raspona tako da ne uzima u obzir nule

Problem sa funkcijom AVERAGE (prosjek) je što u obzir uzima i ćelije koje sadrže nulu. Jedino ne uzima u obzir potpuno prazne ćelije. U slučaju da imate nule u nekim ćelijama a treba vam prosjek koji ih ne uzima u obzir, možete probati ovu formulu:

Rješenje problema kod sabiranja zaokruženih vrijednosti

Zaokruživanje je čest problem a jednostavan primjer je na slici 12. Sve formule su tačne, ali ukupna cijena (obojena crveno)  nije identična zbiru ukupnih cijena po artiklima. Razlog je što vidimo samo dvije decimale a u stvari ih ima više, tako da one u zbiru daju različit iznos. Ta je razlika najčešće 0,01 i zna se da nastaje zbog zaokruženja pa joj se ne daje prevelika važnost. Ipak, može postati bitna ako npr. prikazujete iznose u hiljadama.

Rješenje je formula ispod (crna boja) koja zaokružuje ukupne cijene po proizvodu a zatim daje zbir tog međuniza: =SUM(ROUND(F4:F8;2)) postaje: =SUM({122.66;6074.4;182.86;22.8;184.9}), što na kraju daje 6,587.62.

Slika 12 Formula za tačno sabiranje zaokruženih iznosa

VLOOKUP sa više kriterija (naravno, radi i u lijevu stranu)

Za kraj, primjer koji vjerovatno ne bi ugledao svjetlo dana da nije bilo Nenada i molbe da rješimo VLOOKUP po više kriterija pa mu ovim putem još jednom zahvaljujem. Ovdje je primjer VLOOKUP funkcionalnosti i na desnu i na lijevu stranu te po 4 kriterija. Nadam se da sam odgovoro zahtjevu.

Usput, ovdje možete vidjeti objašnjenje funkcije VLOOKUP.

Kao što vjerovatno znate, funkcija VLOOKUP radi samo u desnu stranu i na osnovu jednog zajedničkog kriterija između dvije tabele. Funkcije INDEX i MATCH omogućavaju rad i u lijevu stranu a matrična formula u ovom primjeru omogućava i povezivanje po više kriterija (u našem primjeru 4 kriterija). Formula radi na sljedeći način (opet, naravno, idemo od sredine prema van):

Kolona H se provjerava i traži se red koji je jednak ćeliji A6 (H5:H261=A6).

Kolona I se provjerava i traži se red koji je jednak ćeliji B6. (I5:I261=A6)

Kolona J se provjerava i traži se red koji je jednak ćeliji C6. (J5:J261=A6)

Kolona K se provjerava i traži se red koji je jednak ćeliji D6. (K5:K261=A6)

Kada se u odabranom nizu (INDEX(G5:K261)) nađe red koji zadovoljava sva 4 nabrojana uslova (oznaka 1 u „MATCH(1“, što je u binarnom sistemu jednako „Da“ ili „Tačno“ ), onda se kao rezultat vraća sadržaj prve kolone u tom redu ( „0);1)“ ).

 U ovom primjeru  postoje 4 osobe sa kombinacijom imena, srednjeg imena i prezimena „Peter Jeremiah Willamson“, ali samo jedan u dobi od 21 godine. Postoje 4 „Petera Williamsona“ u dobi od 21 godine, ali samo jedan kome je srednje ime „Jeremiah“. Postoje 4 „Petera“ kojima je srednje ime „Jeremiah“ a dob 21 godina, ali se samo jedan od njih  preziva „Williamson“ itd.

 Zahvaljujući matričnoj formuli, funkcije INDEX  i MATCH nalaze onoga koji zadovoljava sva 4 uslova (ime, srednje ime, prezime i dob) i kao rezultat vraćaju krajnju lijevu kolonu (broj mobitela) u ćeliji E6.

Drugim riječima: formula očitava u odabranom rasponu sadržaj prve kolone (ili bilo koje) iz onog reda u kome su sve 4 kolone jednake zadanim uslovima.

Eto, znam da je ovaj posljednji primer prilično komplikovan, ali mislim da nije loše da vidite šta se sve može napraviti uz malo truda. Onako, lično, kad pravim formule, ne umirem u ljepoti (mrzim Farselonu), ali volim rješenja koja štede vrijeme i koja omogućavaju da se izbjegne „pješadija“ (ručno računanje, odnosno „pješke“), a to je ono što formule nizova upravo i rade.