Microsoft Office Excel
uređuje Dragan Grbić

Nove funkcije u staroj verziji Excela?

Nivo:
NIVO 3 - Prilozi na ovom nivou su namenjeni naprednim korisnicima, onima koji se već dobro snalaze u programima i onima koji već imaju iskustva u naprednim tehnikama. Ovom nivou pripadaju preporuke i trikovi koje bi trebalo da savlada svako ko teži dugotrajnom i efikasnom radu u Office programima.

Iskoristili ste neke nove funkcije u Excelu 2007, odahnuvši što ne morate da pravite komplikovane konstrukcije kao nekad. I kao za inat, vaše rešenje postane potrebno nekom korisniku starije verzije Excela, gde nema pomenutih funkcija. Više nema mesta za međukorake. Šta sad?

Posetilac Praktikuma na Webu, potpisan samo kao moj imenjak, skreće pažnju na problem koji je sreo nakon upotrebe jedne funkcije koja je nova u Excelu 2007. COUNTIFS je zanimljiv razvoj odavno postojeće funkcije COUNTIF, koja prebrojava pojavljivanja članova liste koji zadovoljavaju neki kriterijum. Na primer:

=COUNTIF(A1:100;">100")

prebrojava sve vrednosti koje su veće od 100 u zadatom opsegu. Međutim, COUNTIFS dozvoljava da izvedete vrlo složeni kriterijum koji se sastavlja iz više parcijalnih, čak do 127 uslova! Oblik pisanja je

=COUNTIFS(opseg1;krit1;opseg2;krit2;...)

što se tumači kao konjunkcija, zapravo potrebno je da budu zadovoljeni svi kriterijumi u svim opsezima da bi stavka u listi bila prebrojana.

Da bi sve ovo bilo jasnije, a da bih vam kasnije lakše objasnio kako da rešite problem koji može da nastane, napravićemo jednu konstrukciju nalik onoj koja mi je upućena kao problematična.

Dakle, recimo da imate nekakvu listu sa kolonama dan, serija i vrednost. Dani se obeležavaju troslovnim oznakama dana u nedelji (pon, uto, sre, ...), serije imaju obeležja slovima A-D, a vrednosti su neki brojevi, za ovu priču proizvoljni.

I odmah: da bi formule koje slede bile čitljivije (a verujte, biće potrebno da pazimo na to), kolone sa vrednostima ćemo imenovati: obeležja dana u opsegu npr. A4:A70 ćemo imenovati rečju dan (kliknite u polje levo od linije za unos formula i tu unesite novo ime), a tako ćemo i ostale dve kolone, baš kao što glasi i ime u zaglavlju kolone. Da vas podsetim: prilikom imenovanja opsega, nemojte uključivati i zaglavlje kolone!

Kad smo postavili ove preduslove, hajde da vidimo kako funkcioniše funkcija COUNTIFS. Recimo, želimo da prebrojimo koliko puta je uz obeležje "sub" (subota) zapisano pojavljivanje serije D:

=COUNTIFS(dan;"sub";serija;"D")

Ako zatreba da prebrojimo oba dana vikenda za istu seriju, to ne treba trpati u jednu funkciju, jer dan ne može da bude istovremeno i subota i nedelja (upamtite: potrebno je logičko "I"), nego ćemo ovako:

=COUNTIFS(dan;"sub";serija;"D")+COUNTIFS(dan;"ned";serija;"D")

Upravo ovako nešto je izveo kolega koji mi se obratio; to je bilo lako i valjano, bez posredno upotrebljenih ćelija i bez izvedene tabele (u kojoj "nema da nema" ako umete da manipulišete njome).

A onda se desio neželjeni slučaj: trebalo je preneti sadržaj za upotrebu u ranijoj verziji Excela; kolega je shvatio da će imati problema već u času kad je forsirano sačuvao datoteku u starom formatu XLS, jer je Compatibility Checker prijavio mogući problem sa novom funkcijom. I šta sad?

Elegantno rešenje podrazumeva utrošak jedne jedine ćelije u kojoj se nalazi formula, bez nekih posrednih zahvata koje bi zahtevale angažovanje par dodatnih kolona sa privremenim međurezultatima i bez pisanja nekakve VBA funkcije (što je uvek poslednje rešenje, ali izaziva druge potencijalne probleme). Da bi problem bio idealno rešen, bilo je potrebno pronaći jednu jedinu formulu koja će funkcionisati uz isti utrošak samo jedne ćelije za traženi rezultat.

Istraživanje ovog problema je otkrilo još neke interesantne pojedinosti za koje, priznajem, ranije nisam znao!
 

Jedno rešenje sam izvukao zahvaljujući mustri na koju me je odveo Google, a jedno sam uspeo da izvedem samostalno. U primeni oba rešenja postoje mogući problemi u razumevanju ili primeni, jer nepažljivim unosom ćete napraviti grešku; ali, poenta je da obe varijante daju dobar rezultat i prikladne su kao zamena nove funkcije u starijem Excelu. Da ne bih komplikovao, zadržaću se na kratkoj interpretaciji polazne funkcije: izolujemo samo instance gde je dan subota i serija je D. Prvo rešenje koje radi u starijim verzijama Excela glasi ovako:

=SUMPRODUCT(--(dan="sub");--(serija="D"))

Ovo zahteva malo više objašnjenja: reč je o jedinoj "običnoj" funkciji u Excelu koja direktno radi sa nizom brojeva. Kada navedete npr. =SUMPRODUCT(A1:A3;B1:B3), dobićete zbir proizvoda: A1*B1+A2*B2+A3*B3. Da bi funkcija radila ispravno, levi i desni niz treba da budu jednakih dimenzija. Izraz dan="sub" je logički; u opštoj upotrebi, sličan izraz glasio =A1="sub", a mogući rezultat bi glasio TRUE ili FALSE.

I ovde dolazim do pikanterije za koju priznajem da je nisam znao ranije: logičke konstante nisu baš primerene za uvođenje u numeričke operacije, pa je poželjno da se TRUE nekako pretvori u 1, a FALSE u 0. Prva ideja je da se to učini funkcijom IF, ali može i lakše: upravo tome služi to neobično pisanje dva minusa ispred zagrade. Napravite sami test na radnom listu: ako napišete =--TRUE, kao rezultat ćete dobiti 1.

I sad stvari postaju jasnije: funkcija SUMPRODUCT, zahvaljujući ovom manevru, formira parove umnožaka nula i jedinica; kada su zadovoljena oba uslova, parcijalni rezultat umnoška će biti 1, a u toku prolaska kroz niz, funkcija će sabrati sve jedinice i dati traženi broj pojavljivanja. Naravno, da biste dobili vrednost za dva dana, saberite u formuli vrednosti dve ove funkcije, menjajući samo obeležje dana u prvom kriterijumu. Ako smo razvili prethodno rešenje na oba dana vikenda, onda ono glasi:

=SUMPRODUCT(--(dan="sub");--(serija="D"))+SUMPRODUCT(--(dan="ned");--(serija="D"))

Jedan detalj u ovom rešenju mi je zapao za oko i naveo me da pronađem drugo rešenje; bila je to mala provera da li se snalazim sa funkcijama niza. Reč je o posebnom obliku pisanja funkcija, gde jedan argument koji je uobičajeno jedna ćelija zamenjujete opsegom ćelija; na kraju, pisanje funkcije obavezno morate da završite prečicom Ctrl+Shift+Enter, a funkcija se tada pojavljuje ispisana unutar vitičastih zagrada.

Važan detalj: te zagrade nikada ne kucate sami. Ako treba da izmenite sadržaj formule, morate ponovo da je završite pomenutom prečicom.

Tako napisana funkcija pretvara taj hibridni argument u niz, a onda interno generiše skup rezultata od pojedinačnih "pravih" funkcija, akumulirajući konačni rezultat kao da ste napisali gomilu pojedinačnih funkcija, pa ih negde konačno sabrali.

U literaturi sam imao prilike da vidim i "nemoguća" rešenja upotrebom funkcija niza i moram da priznam da je to tema koja me apsolutno najviše zanima u Excelu; zato koristim svaku raspoloživu priliku da uvežbam takvu primenu, što mi je uspelo i ovom prilikom. Čisto, beleške radi, onaj pomenuti primer

=SUMPRODUCT(A1:A3;B1:B3)

možete izvesti i ovako:

{=SUM(A1:A3*B1:B3)}

A ova školska konstrukcija me je upravo dovela do sledećeg rešenja:

{=SUM(IF(dan="sub";1;0)*IF(serija="D";1;0))}

Ali, samo trenutak! Sad znamo i za trik sa dva minusa umesto funkcije IF:

{=SUM(--(dan="sub")*--(serija="D"))}

Prisetićemo se da funkcija SUM može da ima do 31 sabirka, pa će rešenje za oba dana glasiti:

{=SUM(--(dan="sub")*--(serija="D"); (--(dan="ned")*--(serija="D"))}

I, posle silnog slaloma po detaljima, to bi bilo to...

PROVOKACIJA: sve zainteresovane izazivam da pronađu i prijave ekvivalentno rešenje koje menja funkciju SUMIFS starijim funkcijama.

Ovaj prilog možete komentarisati ako pristupite najavi priloga na blogu Praktikuma na Webu