Microsoft Office Excel
uređuje Dragan Grbić

Nekoliko trikova sa formulama

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.

"Površinu" sistema za računanje u Excelu čini sintaksa formula. Kako stvari stoje, ta površina je vrlo uzburkana: pogledajte šta se može učiniti sa vešto napisanom formulom...

Jednom prilikom mi je zatrebalo da u jednoj dugoj listi ispitam da li postoje duplikati. Naravno, mogao sam da sortiram listu, pa da lagano pregledam da li postoje isti susedni članovi. Ali, hteo sam da ispitam kako mi Excel može pomoći na manje grub način. Ne postoji ugrađena funkcija koja može direktno pomoći, pa sam pokušao da kombinujem prebrojavanje sa logičkim funkcijama. Uspelo mi je posle malo igranja: upotrebio sam funkciju COUNTIF, koja prebrojava broj članova opsega prema datom kriterijumu. Pored prvog elementa sam napisao:

=IF(COUNTIF(lista;A1)=1;"JEDINSTVEN";"DUPLIKAT - "&COUNTIF(lista;A1)&" PUTA")

(pripazite da se ova formula unosi u ćeliju kao jedan red)

Potom sam duplim klikom na fill handler (mali crni kvadratić u donjem desnom uglu ćelije) "prevukao" formulu do kraja liste i - posao je bio gotov.

Ova konstrukcija deluje malo nekonvencionalno, ali izgleda da postoji celo područje za slične trikove. Neki su vrlo jednostavni: recimo, želite da saberete sve vrednosti u nekom opsegu koje su veće od neke vrednosti. Ovaj posao radi funkcija SUMIF, ali obično se kao drugi argument navodi deo logičkog izraza sa konstantom (npr. "=100"). Ako želite da povremeno menjate tu vrednost, treba menjati sadržaj formule, ali može i drugačije: vrednost kriterijuma se može upisati u neku ćeliju, a u formuli se kriterijum može formirati posredno:

=SUMIF(A1:A100,">" & B1)

Formule se mogu koristiti i na neočekivanim mestima. Recimo, ako ste imenovali opseg ćelija u nekoj koloni, možete imati kasnije probleme ako dopišete neku stavku, a zaboravite da ažurirate granice opsega. Excel može o tome sam da se brine, ako blok definišete kao formulu! Recimo, treba da definišete blok baza_ime u koloni A, počev od ćelije A2; otvorite dijalog Insert/Name/Define, unesite ime baza_ime, a u polje Refers to unesite sledeću formulu:

=OFFSET($A$2;0;0;COUNTA($A:$A);1)

Kad god dopišete novu vrednost u nastavku kolone, imenovani blok će se automatski produžiti tako da uključi i nju. Interesantna posledica ove vrlo korisne opcije je da se imena ovako definisanih opsega neće pojavljivati u listi imenovanih polja, ali ako u nju unesete ime opsega, on će biti uredno označen.

Evo trika na koji sam jednom prilikom "natrčao" na Internetu i koji me je ostavio bez teksta. Zamislite tabelu sa tri kolone: u prvoj koloni su imena, u drugoj su dani u nedelji, u trećoj neki brojevi. Zadatak je sabrati samo one brojeve u koloni C kojima odgovara da je ime "Laza" i dan u nedelji "utorak". Šta je prvi impuls? Ja bih napravio izvedenu (pivot) tabelu i pročitao rezultat. Ali, šta ako hoću da je ta vrednost negde na radnom listu? Funkcija SUMIF bi pomogla samo ako je dovoljan kriterijum u jednoj koloni, pa ovde nije primenljiva. Pomoći će funkcija sabiranja niza:

=SUM((A1:A100="Laza")*(B1:B100="utorak")*C1:C100)

Važan detalj: kada se unese ova formula, umesto Enter treba "lupiti" Ctrl+Shift+Enter; cela formula će se pojaviti u vitičastim zagradama, što označava funkciju čiji domen je niz: eto rezultata!