Microsoft Office Excel
uređuje Dragan Grbić

Trikovi sa validacijom

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.

Da Excel ume neprijatno da iznenadi, osvedočio sam se kada me je bliski kolega nazvao telefonom i postavio mi pitanje na koje sam mislio da znam brz i tačan odgovor. No, iskustvo mi nalaže da uvek probam ono o čemu pričam, što se ispostavilo kao korisno u ovom slučaju: pogrešio bih da sam pričao napamet.

Elem, kolegi je bilo potrebno da napravi padajuću listu mogućih vrednosti za unos u ćeliju na radnom listu u Excelu. Ništa posebno, reći ćete: otvorićemo Data / Validation (Podaci / Provera valjanosti), pa kao navesti Allow: List (Dopusti: Liste) i u polju izvora navesti moguće vrednosti podeljene tačka-zarezom kao delimiterom. Ako su moguće vrednosti već unete u neke ćelije, onda ćemo ih referencirati kao izvor dozvoljenih vrednosti. I onda je usledio hladan tuš: nije moguće referencirati ćelije sa drugog lista kao izvor! Ako pokušate da se u toku popune dijaloga postavite drugi list, nećete uspeti. Pokušao sam i pešice: unos formule =spisak!A1:A10 je završio porukom da ne mogu da iskoristim podatke na drugom listu ili drugoj radnoj svesci kao kriterijum.

Kolega i ja smo skoro istovremeno došli do pravog, mogućeg rešenja: opseg ćelija na drugom listu je bilo potrebno imenovati. To ćete najbrže učiniti tako što ćete obeležiti željene ćelije, pa ukucati novo ime u polje levo od linije za unos, neposredno ispod trake komandi. Sada je lako: u polje izvora za pravilo izbora iz liste valja uneti formulu =kriterijum i problem je rešen! Preostaje samo da na trećoj kartici dijaloga za postavljanje pravila validacije odredite scenario reagovanja na grešku. Možete isključiti reakciju programa, što je diskutabilno kao dobar izbor. bolje je da primenite jednu od tri moguće reakcije: sprečavanje pogrešnog unosa, zahtev korisniku da odluči da li zadržava pogrešan unos ili se vraća na izbor, ili samo informacija da je došlo do nekonzistentnog unosa. Preporučiću vam da uz pravilo reagovanja definišete i prikladni dijalog, kako bi korisniku bilo jasnije o kakvoj pojavi je reč.

U ovoj epizodi, postoji i jedna koincidencija. Bilo bi mi lakše da sam samo jedan dan ranije dobio jednu zanimljivu poruku, u kojoj mi je skrenuta pažnja na jednu fenomenalnu tehniku kaskadnog nasleđivanja pravila za unos sadržaja u ćelije na radnom listu. Kolega Đorđe Herceg je odlučio da nesebično podeli sa vama rešenje ovog problema: u jednoj padajućoj listi treba odabrati kategoriju proizvoda, nakon čega je očekivano da padajuća lista za unos konkretnog proizvoda u susednoj ćeliji bude popunjena samo proizvodima iz odabrane kategorije. Na prvi pogled, ovo je prilično složen problem koji bi trebalo rešiti ili VBA programiranjem (što je prilično neudobno) ili bi trebalo napustiti Excel i rešiti problem u Accessu, gde bi relacioni model već došao do izražaja.

Međutim, malo promišljanja i malo veštog kombinovanja je dovelo do jednog od najelegantnijih rešenja koje sam ikada video u Excelu... Preporučujem vam da ovog časa otvorite Excel i da izvodite korake koje opisujem: biće vam mnogo jasnije ako budete i sami probali.

Najpre, kolega Herceg je na posebnom radnom listu napravio spisak kategorija i taj opseg ćelija imenovao Kategorije. Zatim je za svaku kategoriju napravio spisak proizvoda i svaki formirani opseg imenovao tačno po imenu kategorije. Npr. ako u spisku kategorija postoji stavka RezervniDelovi, onda se i opseg stavki rezervnih delova imenuje isto tako; pažnja: mora bez razmaka. Sa ovako pripremljenim materijalom, kolega je pristupio postavljanu pravila validacije: u ćelijama koje treba da sadrže naziv kategorije, postavljeno je pravilo definisane liste, a kao izvor je navedena referenca =Kategorije. I sad sledi ključni trik: ako se kategorija nalazi u ćeliji B2, a odgovarajući proizvod treba odabrati u ćeliji C2, tada u C2 treba postaviti pravilo validacije u obliku liste, a kao izvor navesti funkciju =INDIRECT(B2)!

Reč je o vrlo zanimljivoj, ali retko korišćenoj funkciji čije upotrebe na ovom mestu se verovatno nikada ne bih setio: ona upućuje na to da se vrednost ćelije uzme kao referenca za izvođenje neke formule.

Za ilustraciju, sledi prosti primer koji možete da oprobate na radnom listu: unesite bilo kakav sadržaj u ćelije A1 i A2; u ćeliju C1 unesite tekst A1, a u ćeliju E1 unesite funkciju =INDIRECT(C1) i dobićete prenetu vrednost iz ćelije A1. Sad sadržaj u ćeliji C1 prepravite na A2 i obratite pažnju šta se dešava u ćeliji E1...

Ako ekstrapolirate ovaj prosti primer, dobićete nešto blisko pomenutom rešenju: sad u ćeliju C1 možete postaviti padajuću listu sa tekstualnim vrednostima A1 i A2 (izvor je konstanta napisana kao A1;A2) i birati te vrednosti, varirajući vrednost u E1. Dalji korak u ekstrapolaciji bi bio da imenujete ćelije sa vrednostima, a poslednji je primena funkcije INDIRECT kao izvor liste, jer će njenim izračunavanjem biti referenciran opseg sa željenim vrednostima.

Napomenuću samo još jedan detalj: s obzirom na to da pri unosu pravila validacije to obično treba učiniti nad više ćelija, učinite to odjednom. Obeležite ceo opseg, pa obratite pažnju na to koja je ćelija aktivna u obeleženom bloku; prilikom unosa pravila validacije se ponašajte kao da unosite pravilo samo u nju, koristeći relativne reference; npr. ako je aktivna ćelija C2 u obeleženom bloku C2:C25, kao izvor liste postavite samo =INDIRECT(B2). Proverite slobodno: na kraju će svaka ćelija imati svoj tačni par u validaciji.

Uz svoje rešenje, kolega Herceg daje i napomenu: pomoću dodatne tabele i funkcije VLOOKUP moglo bi se podesiti da se u padajućoj listi za izbor kategorije pojavljuju uredniji ispisi, poput Rezervni delovi, koji bi se onda transformisali u ime opsega RezervniDelovi. Reč je, naravno, o tome da pravilo imenovanja opsega ne dozvoljava razmake, pa je neophodno ili slepiti reči u jednu ili upotrebiti donju crtu umesto razmaka. Primedba na sopstveno rešenje je prihvatljiva, ali dodaću na to: već je dostignut nivo rešenja sa pet zvezdica. Ali opet, zašto da ne! Pozivam vas da sami pronađete dodatno rešenje na koje je kolega Herceg, kome se iskreno zahvaljujem na izvanrednom prilogu, ukazao u napomeni.