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.
-
Slika 1 Tabela sa jediničnim cijenama i količinama
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 (*)
-
Slika 2 Selektovanje nizova za unos višećelijske matrične formule
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).
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.
-
Slika 4 Matričnu formulu prepoznajete po vitičastim zagradama
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?).
-
Slika 5 Odabir dva niza koji daju rezultat jednoćelijske matrične formule
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.
-
Slika 9 Formula za sabiranje 3 najveće vrijednosti iz odabranog raspona
Zbir raspona ćelija koji sadrži grešku
-
Slika 10 Formula za izračun zbira uz ignorisanje grešaka
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:
-
Slika 11 Formula za izračun prosjeka uz ignorisanje nula
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.
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.
-
Slika 14 Rezultat dvostranog VLOOKUP-a po više kriterija
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.
Autor
Almir A. Budalica
Microsoft Certified Trainer
http://www.excelbezbola.com
+38761509288 (Viber, WhatsApp)
10 Komentara
Almire, sjajn tekst. posebno ovaj poslednji deo sa index+match+matricom 😉
Nego, da li je moguće ovo napraviti i bez matričnog dela: naime imam dve tabele, povezivanje je preko tri parametra, treba povući još neke podatke iz te druge tabele (konkretno, prva tabela je zaglavlje računa, druga tabela su stavke računa, povezivanje je preko šifre prodavnice, datuma i broja računa).
Ne znam kako bi u Excelu napravio bez matričnog dijela (jer se tabele povezuju preko više od jednog kriterija). Predlažem ti da za svaki podatak koji treba dobiti iz te druge tabele (stavke računa) napraviš matričnu formulu kako je objašnjeno a da tabele povezuješ preko šifre prodavnice, datuma i broja računa. Naravno, postoji i druga opcija, ali to bi uključivalo SQL upite i baze podataka, što izlazi iz okvira Excela.
Almire, hvala na odgovoru. Na žalost, podaci su iz ‘starog’ sistema, tako da nemam mogućnost da ih ponovo ‘povučem’ sa dodatnim podacima…rešenje je da kompletno sve podatke uvučem u Access i tamo povežem preko sql upita, što sam pokšao da izbegnem preko ovog trika koji si objasnio u tekstu.
Hvala puno na pruzenim informacijama. Ali…ja bih imao jedno pitanje, mada sam preturio sve sto mi je palo na pamet. Kod formiranja nizova tipa: Januar, Februar itd, nemam problem, ali kada pokusavam da napravim niz na engleskom jeziku, dolazi samo do ponavljanja JANUARY< JANUARY itd. Isti mi je problem i kod dana u sedmici. Probao sam da idem u podesavanja i da namestim datume i vreme na EN US, ali bezuspesno. Kako jednostavno resiti problem? Hvala unapred!
Kamilo, ukucaj “Jan” a zatim “Feb”, selektuj oboje i razvuci niz. Ukoliko to ne pomogne, promjeni regionalne postavke kao što si već pokušao, ali promjeni kompletnu postavku u američki engleski a ne samo datume.
Ukoliko ni to ne pomogne, napravi “Custom Listu” za sortiranje, tako što ćeš u listu staviti oznake mjeseci: Jan, Feb, Mar itd. Nakon toga bi trebalo da se niz napravi bez problema. Objašnjenje Custom Liste je ovjde: http://www.mcb.rs/blog/2011/10/20/trikovi-u-excelu-4-deo-custom-sort/.
Odličan tekst, ja index match koristim jako često ako u jednoj tabeli imam redove koji sadrze razlicite valute a nalaze se u istoj koloni (SAP voli često da izvabaci ovakvu tabelu iz sistema)…
Meni ova f-ja sa vlookup-om ne radi. Da li je neko pokušao da je primeni, ne znam gde grešim…?
Inače, sve pohvale za rad MCB-a, samo napred!
Na moju veliku žalost, tek ovih dana sam otkrio ovaj sajt. Sad vidim koliko sam toga propustio jer ovo što vi radite je fenomenalno. Volim excel i vrlo rado radim u njemu. Vrlo cenim vaše znanje koje nesebično delite i drugima, ali imam jednu malu primedbu: naime kada je reč o postavkama u nekim tabelama (ili vašim komentarima) tipa “Cena” “Količina” “Ukupna cena”, želim da kažem da termin “Ukupna cena” nije dobar. Zašto? Zato što, po definiciji “Cena predstavlja novčani izraz vrednosti robe” i cene se NE SABIRAJU, to je kao da sabirate kruške i jabuke. Pravilno je upotrebiti izraz “Ukupna vrednost”.
Nadam se da ne zamerate na ovom ukazivanju greške, nemam loše namere, naprotiv.
Puno pozdrava i samo nastavite
@Draško, hvala na sugestijama, mea culpa. Gledaću da ubuduće ne pravim ovakve propuste. Neko zna Excel a neko finansije