Microsoft Office Excel
uređuje Dragan Grbić

Referenca kriterijuma za COUNTIF

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.

Jedan naizgled sasvim običan, školski primer upotrebe funkcija u Excelu dovodi nas do razmišljanja o tome kako se uobičajeno rešavaju problemi u tom programu. Prisećamo se i predavača koji insistiraju na jednoznačnom rešenju. Upravo takvima u inat, prikazujem nekoliko potpuno različitih rešenja istog problema, jer jedno od važnih nasleđa primene Excela je sloboda u pristupu.

Pre nego što vam prepričam jednu tipičnu situaciju traženja rešenja problema u Excelu, moram da se osvrnem na jedan drugi, nimalo prijatan problem. Negde u proleće 2007. godine, jedan student me je zamolio da mu pomognem da uradi vežbu u Excelu. Kada dođe do takvih situacija, povodim se za japanskom izrekom "daj mi ribu i nahranićeš me jednom; nauči me da lovim ribu i nahranićeš me za ceo život". Trudim se, dakle, da ukažem na rešenje tako da onaj ko pita na kraju uspe sam da odgovori na svoje pitanje. Ponekad samo pomenem tehniku rada ili naglasim koju funkciju je potrebno upotrebiti. Ako ide malo teže, namerno napravim neki drugačiji primer i demonstriram ga, a zatim navedem da onaj ko pita sam otkrije rešenje koje je tražio. Međutim, tom prilikom se desilo da je predavač suspendovao jedno tačno rešenje, izvedeno u traženim okvirima prema mom uputstvu, samo zato što u rešenju nije upotrebljena tehnika koju je dotični zamislio. Bio sam zapanjen takvim razvojem događaja, a sva sreća da nisam bio u prilici da sretnem tu osobu, jer verovatno ne bih birao reči dok bih mu saopštio mišljenje o kvalitetu njegovog rada sa studentima.

Kada mi se nedavno ulučila slična prilika, setio sam se tog nesrećnog slučaja. U kontaktu sa vrednim studentom koji mi se obratio za savet, nisam propustio da pitam da li postoji neka smernica za rad. I dobro je da sam pitao, jer bih inače opet uradio zadatak "kako ne treba". Međutim, problem je bio i u tome što je zadatak bio zamalo neizvodljiv u zadatim okvirima; zapravo, primer iz vežbe je loš kao ilustracija zato što se u praksi radi drugačije. Ta pojedinost je bila dovoljno zanimljiva da bih ovu priču podelio sa vama.

Problem je bio sledeći: u jednoj koloni se nalaze neki slučajni brojevi. Zadatak je bio dvodelni: najpre je trebalo da se u pomoćnoj koloni izoluju decimalni delovi brojeva, a zatim je u označenoj ćeliji trebalo prebrojati koliko ima tih brojeva koji su manji od broja upisanog u jednoj ćeliji. Naizgled, sve je jasno: u prvom delu postoji dobro poznata gimnastika. Izraz

=ABS(A2-TRUNC(A2))

će izolovati pozitivne decimalne delove bez obzira na predznak polaznog broja. Ova formula se iskopira iz ćelije B2 do krajnje ćelije B21 i prvi deo je rešen. U drugom delu, koristi se funkcija COUNTIF. Kandidat je pokušao prema preporuci:

=COUNTIF(B2:B21;<B23)

Dok čitate ove redove, učiniće vam se da je sve u redu, ali ako probate u Excelu, videćete da program daje poruku o grešci. Zavirite li u sistem pomoći, shvatićete zašto je tako: drugi argument, koji služi kao kriterijum, treba da bude naveden kao tekstualni zapis. Dakle, tako ne ide. Prvi impuls ne pomaže: ako bismo zapisali

=COUNTIF(B2:B21;"<B23")

referenca bi bila izgubljena, nego bi formula pokušavala da nađe sadržaje manje od vrednosti "B23" (moguće je porediti i tekstove po ASCII vrednosti prvog znaka) i rezultat bi bio nula. Prema kratkoj skripti sa vežbi koju je imao, student je imao saznanje da bi za prebrojavanje jednakosti bilo jednostavno: tada bi mogao da zapiše

=COUNTIF(B2:B21;B23)

i funkcija bi radila valjano; razlog te valjanosti je što Excel lako prevede broj u tekst kad god zatreba. U očaju, došao je do "polurešenja": u ćeliji B23 je bio zapisan broj 0,5 i radno rešenje je glasilo

=COUNTIF(B2:B21;"<0,5")

To rešenje radi ispravno, ali ne odgovara zahtevu zadatka - šta ako treba da se promeni vrednost kriterijuma?

Moram da priznam da sam i ja bio zbunjen i da u prvi mah nisam mogao da vidim rešenje. Ali, ono je bilo očigledno i video bih ga samo da sam slušao šta govorim: Excel lako prevede broj u tekst kad god zatreba. I sad je, naravno, rešenje postalo očigledno. Sastavićemo tekst i broj:

=COUNTIF(B2:B21;"<"&B23)

Među čitaocima će se naći i oni koji su ovo rešenje videli odmah. Međutim, upravo je opisani put razmišljanja veoma čest kod mnogih korisnika, a tada očigledne stvari umeju da postanu zamršene.

Istina, moj problem je bio u tome što ovakvo rešenje u praksi nije odraz bogzna koliko mudrog postavljanja rešenja na radnom listu. U većini slučajeva, kad god treba uslovno da prebrojim neki sadržaj, ja pribegavam korišćenju funkcije DCOUNT (i ostalih D-funkcija). To je daleko fleksibilnije rešenje, jer postoji pomoćna tabela u kojoj se nalazi obeležje imena kolone i ceo kriterijum. Čitanjem kriterijuma, videlo bi se na šta se rezultat odnosi (izraz ne bi bio deo funkcije koja se možda ne vidi), a moguće je menjati i ime kolone i kriterijum; štaviše, tada mogu da se prebrojavaju vrednosti u jednoj koloni prema uslovu u nekoj drugoj!

Evo rešenja: ako se kolone polazne tabele zovu broj i decimale, tada u B22 mogu da unesem reč decimale, a u B23 ću uneti tekst "<0,5". U ciljnu ćeliju ću da unesem sledeće:

=DCOUNT(A1:B21;2;B22:B23)

Prvi argument upućuje na polaznu "bazu", drugi daje redni broj kolone koja se prebrojava, a treći daje adresu tabele kriterijuma. Uzgred, u praksi bih malo odmakao tabelu kriterijuma i rešenje, da ne budu slepljeni jedan za drugog. I najzad, imao bih najbolje moguće rešenje za upit: uvek mogu da ga promenim po potrebi. Moguća su i složenija rešenja, jer tabela kriterijuma može da bude i veća, dajući kombinacije logičkih "i" i "ili"; ali, o tome drugi put.

Da mi se nije desio onaj problem godinu dana ranije, predložio bih jedno atipično rešenje. Elem, sve češće upražnjavam praksu igranja sa funkcijama niza. Nakon što sam ovladao njima, prebrojavanje poput ovog je za mene postalo trivijalno, sa rešenjem nalik ovome:

{=SUM(IF(B2:B21<$B$23;1;0))}

Podsetiću vas samo kratko: funkcija niza koristi opseg ćelija na mestu gde se u praksi očigledno koristi adresa jedne ćelije (u ovom slučaju, to je deo B2:B21); tada program napravi petlju kroz sve ćelije i daje agregatno rešenje. U ovom slučaju, bile bi sabrane sve jedinice koje označavaju tačno poređenje u dvadeset ciklusa. Vitičaste zagrade u funkcijama niza ne kucate sami, nego uobičajeni unos formule završavate pritiskom kombinacije Ctrl+Shift+Enter.

A za kraj priče, evo i male egzibicije moći funkcije niza. Može i bez korišćenja pomoćne kolone - celo rešenje zadatka se nalazi u jednoj jedinoj formuli:

{=SUM(IF(ABS(A2:A21-TRUNC(A2:A21))<$B$23;1;0))}

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