Microsoft Office Excel
uređuje Dragan Grbić

Posredni minimum

Nivo:
NIVO 4 - Grupa priloga na ovom nivou predstavlja razne ekspertske tehnike rada u Office programima, među kojima se nalaze i one koje nisu uobičajene među većinom korisnika. Većina ovih priloga zahteva veoma napredno poznavanje Windowsa i Officea.

Preda mnom se našao realan problem iz života koji me je dobro oznojao. Iskustvo stečeno na putu do rešenja problema je za mene blo dragoceno, pa ga ovde delim sa vama.

Kontakti sa vama predstavljaju izuzetni izvor priloga za moju rubriku i ovaj sajt, a moram vam priznati da neka od vaših zanimljivih pitanja predstavljaju prave glavolomke. Jedno od takvih pitanja poslao mi je Žolt Kokrehel, koji je tražio rešenje naizgled banalnog, ali realno tvrdog problema u Excelu. Reč je o tabeli sa dve kolone brojeva prikupljenih nekim proračunima. Brojevi u levoj koloni nisu složeni ni po kakvom redosledu; potrebno je pronaći najmanju vrednost u toj koloni, a zatim preneti susednu vrednost iz desne kolone. S obzirom na to da tabela povremeno prima različite vrednosti, treba naći način da neka ćelija uvek izrazi tačnu vrednost "posrednog minimuma". Gospodin Kokrehel je naglasio da je pokušao da reši problem primenom funkcije VLOOKUP, ali je i sam uočio da ta funkcija ne može da pomogne u uslovima kada brojevi u levoj koloni nisu uredno sortirani. Uz pretpostavku da postoji neko trivijalno rešenje problema, obratio mi se sa molbom za pomoć.

Ovo je zaista vrlo zanimljiv problem. Rešenje nije baš trivijalno, ali nije ni nedostižno. Naravno, u Excelu uvek postoje "zadnja vrata" za rešavanje ovakvih stvari uz pomoć VBA petlji, ali ova postavka je dušu dala da iskušam svoju veštinu primene formula na radnom listu. Štaviše, ovde sam nehotice izveo dobru ilustraciju opšteg načina analize problema, pa ću sa vama podeliti ceo tok nalaženja optimalnog rešenja.

Prvo rešenje sam izveo veoma brzo. Recimo da se podaci u tabeli nalaze u opsegu A2:B20 (ignorišemo zaglavlja). Rezultat koji se nalazi pored najmanje vrednosti možemo dobiti ovim manevrom:

=SUMIF(A2:A20;MIN(A2:A20);B2:B20)

Prosto, sabiram sve vrednosti u koloni B koje kao susednu imaju minimalnu vrednost u koloni A. Ali, uz pretpostavku da mogu da se pojave dva ili više jednakih vrednosti minimuma, rešenje neće valjati - sve vrednosti u desnoj koloni će biti sabrane. Pošto odavno više nemam nikakvih skrupula da napravim "kobasicu" od formule, ispravljam mogući problem metodom grube sile:

=SUMIF(A2:A20;MIN(A2:A20);B2:B20)/COUNTIF(A2:A20;MIN(A2:A20))

Dakle, ako se zateknu tri jednake minimalne vrednosti, tri desne vrednosti će biti sabrane, pa ću podeliti rezultat sa tri... Ali opet, postoji očigledna slabost rešenja: ne znam da li vrednosti u levoj i desnoj koloni korespondiraju nekom funkcijom, pa zato ne znam ni da li tri desne vrednosti bivaju iste; ako nisu iste, formula apsolutno ne valja.

Univerzalno rešenje je posredno i ja ga često primenjujem u sličnim slučajevima. Prosto, koristim pomoćne kolone da bih napravio indikatore, a onda kolekcijom funkcija IF izvlačim šta mi treba. To bih uradio ovako:

U ćeliju C2 postavljam formulu:

=IF(A2=MIN($A$2:$A$20);"*";"")

Sad koristim ručicu za popunu (fill handle, crni kvadratić u donjem desnom uglu ćelije) da popunim celu kolonu C do dna liste (dovoljan je dupli klik na njega). Pored vrednosti koju tražim, pojaviće se zvezdica. Ako mi nađena vrednost ne treba za dalji proračun, pročitaću je u tabeli pored zvezdice i to mi je dovoljno. Ali, šta ako je tražena vrednost potrebna za neki dalji proračun? Ipak moram da nađem formulu...

Tada sam zasukao rukave i okušao se u tzv. funkcijama nizova, za koje John Walkenbach, velemajstor spreadsheet formula, tvrdi da su najveća snaga Excela. Trik u primeni tih funkcija je u tome što na mestu gde se uobičajeno navodi referenca jedne ćelije treba navesti opseg, što važi i za sve odgovarajuće argumente; ako neka funkcija podrazumeva korišćenje opsega, to će se koristiti uobičajeno (npr. MIN(A2:A20)). Kada napišete takvu funkciju, morate je završiti pritiskom na CTRL+SHIFT+ENTER.

Vizuelno, takvu formulu ćete prepoznati po vitičastim zagradama na krajevima; upamtite da ne treba sami da upisujete te zagrade, nego to čini Excel. Takođe, posle svake izmene u formuli, morate ponoviti tu prečicu za definisanje niza.

Pritiskom na prečicu, Excel će napraviti petlju tako što će u opsezima uzimati prvo prve ćelije , pa druge, i tako redom do kraja, dajući sumarni rezultat, već u zavisnosti od matične funkcije koja figuriše nad nizom. Recimo da imate tabelu od dve kolone i treba da saberete proizvode brojeva u istom redu. Mogli biste napraviti proizvode u trećoj koloni, pa ih sabrati, ali najbrže bi bilo da napišete ovako nešto:

{=SUM(A1:A100*B1:B100)}

To je isto što i =A1*B1+A2*B2+...+A100*B100 - može li lakše? Ceo rezultat dobićete bez stotinu posrednih rezultata!

Bio sam apsolutno uveren da funkcija niza može da reši zadati problem - i nisam se prevario.

Prvo sam opsegu A2:A20 dao ime niz, samo da bih olakšao sebi pisanje. Ideja koju sam primenio je sledeća: ako je vrednost u ćeliji jednaka minimumu opsega (niz=MIN(niz)), utvrdiću u kom redu se ta vrednost nalazi (ROW(niz)); jednom IF pitalicom ću upamtiti vrednost ili ću postaviti prazan string (""). Kada vrednost u petlji niza bude nađena, uhvatiću je funkcijom MAX (funkcija ROW daje broj); taj broj i broj 2 (kolona B) daju tačnu adresu ćelije u koloni B pored minimuma (ADDRESS(red,kolona)). Vrednost sa te adrese mogu da pročitam funkcijom INDIRECT(adresa).

A sad fanfare... Evo rezultujuće formule:

{=INDIRECT(ADDRESS(MAX(IF(niz=MIN(niz);ROW(niz);""));2))}

Jasno mi je da sam ovom "šahovskom" analizom upravo zbunio većinu čitalaca, ali predlažem vam da i sami malo proanalizirate ovaj postupak. Tome ima bar dva razloga: shvatićete da dekompozicija problema i nije tako problematična ako raspolažete znanjem o mogućnosti Excelovih funkcija; pride, funkcije niza su zaista sjajna stvar kad jednom razumete kako funkcionišu. A pride, izazivam najveštije među vama da otkriju propust koji sam ovde svesno ostavio, prepuštajući vam da sami rešite problem do kraja.