Trikovi u Excelu, 12 deo: Imenovani rasponi ili Kako komplikovane formule načiniti kraćim i jasnijim

U vrijeme kada sam bio mlad i nadobudan (naučio ponešto o Excelu), mislio sam da znam mnogo. Sve do jednog dana...

Kolega mi je poslao fajl pripremljen u Excelu sa podacima o artiklima, naručenim količinama i jediničnim cijenama, sa ciljem da ja taj fajl malo vizuelno dotjeram i provjerim tačnost formula.

„Formule nisu komplikovane, lako ćeš se snaći“ – bilo je posljednje što sam čuo prije nego što je kolega otišao na godišnji odmor.

Hajd', lijepo, pomislih i bacih se na posao. Uistinu, tabela nije bila komplikovana, ali je kontrola formula počela da mi zadaje glavobolje. Da se odmah razumjemo: nije bilo mnogo kolona ni komplikovanih proračuna, ali je bilo dosta redova pa sam se u jednom momentu uhvatio kako se pitam da li ćelija $A5655 na jednom radnom listu odgovara zbiru raspona ćelija G$696:O$891 sa drugog i još četiri slična raspona sa trećeg radnog lista, podjeljenog sa nekim čudnim koeficijentom.

Pošto je rok za završetak kontrole bio „jučer“, nazovem kolegu (koji je već bio na plaži), unaprijed se radujući što ću mu bar malo pokvariti odmor.

- Ahaaaaa, znači, to te „žulja“ – vedro je odvratio. - Nikakav problem, daj mi par minuta pa ću ti poslati verziju koja te neće zbunjivati.

Ostadoh u čudu, pitajući se kako to misli pojednostaviti za nekoliko minuta. Već sam sebe zamišljao kako čitam uputstvo od 300 stranica u kome „sve lijepo piše“ i, naravno, ne završavam posao na vrijeme. Ma, kakvo vrijeme, ne završavam ga nikako!

Uistinu, za nekoliko minuta stigao mi je e-mail sa fajlom koji je izgledao identično onome kojeg sam već imao. Samo izgledao....

Umjesto slova, brojki i dolarskih znakova u formulama je pisalo: „cijena*količina“, „SUM(količina), AVERAGE(cijena)“, „cijena u eurima“, „ukupna cijena u eurima“ itd. Osjećao sam se poput čovjeka koji je mislio da je slijep a onda je odjednom skinuo sunčane naočari.

[caption id="" align="aligncenter" width="640"] Slika 3. Prikaz formula nakon „izmjena“[/caption]

Nisam mogao izdržati pa sam ga uvečer opet nazvao (nisam se usuđivao da mu još jednom pokvarim dan na plaži) kako bih saznao kako je to napravio, a naročito kako je to uspio za tako kratko vrijeme.

- Pa, vidi, u pitanju su tzv. imenovani rasponi. Odabereš raspon ćelija i daš mu neko ime. Nakon toga, u formulama koristiš ime tog raspona umjesto referenciranja na ćelije. Tako su formule mnogo kraće i jasnije. Naravno, onda ih i neko ko nije upoznat sa fajlom – poput tebe, može lako razumjeti.

- A kako praviš imenovane raspone?

- Odabereš raspon ćelija i u gornjem lijevom uglu (gdje se vidi adresa aktivne ćelije) ukucaš ime raspona (u ovom primjeru „Količina“) i pritisneš tipku ENTER. Pazi da ime raspona ne počinje donjom crtom ili brojem i da ne sadržava razmake.

[caption id="" align="aligncenter" width="640"] Slika 4 Pravljenje imenovanog raspona pomoću Address Cell[/caption]

- Onda, vjerovatno, u formuli samo ukucam ime raspona?

- Upravo tako: Excel čak prepoznaje prva slova imena raspona i automatski ti nudi raspone koji odgovaraju unesenim slovima. A spisak kreiranih imenovanih raspona možeš vidjeti ili ubaciti u formulu klikom na adresu aktivne ćelije i odabirom željenog raspona.

[caption id="" align="aligncenter" width="640"] Slika 5 Excel "prepoznaje" imenovani raspon[/caption]

- Mogu li se rasponi ćelija na koje se odnose imenovani rasponi promjeniti? Mislim na situacije kada npr. dodam nove redove koje želim uključiti u već imenovani raspon?

- Da, ali moraš ići na meni (tab, ribbon) „Formulas“ pa odabrati „Name Manager“. Odabereš raspon, klikneš na „Edit“, selektuješ željene ćelije, potvrdiš pritiskom na kvačicu i klikneš na dugme „Close“.

[caption id="" align="aligncenter" width="640"] Slika 6 Izmjena postojećeg imenovanog raspona[/caption]

Brisanje je također lako: odabereš imenovani raspon i klikneš na „Delete“. Naravno, brisanje raspona ne znači i brisanje podataka na koje se imenovani raspon odnosi, već samo brisanje imena. Kod brisanja zapamti da će sve formule koje koriste imenovani raspon koji brišeš javiti grešku.

- Lako je kreirati imenovani raspon za jednu grupu ćelija. Pretpostavimo da imam tabelu sa 20 kolona, moram li 20 puta ponoviti postupak za kreiranje imenovanih raspona?

- Ne, postoji mnogo brži način: selektuj kolone (uključujući i imena kolona) i na meniju „Formulas“ odaberi „Create from selection“, uključi opciju „Top Rows“ i dobićeš 20 imenovanih raspona koji se odnose na 20 kolona. Svaki raspon će dobiti ime po imenu kolone.

[caption id="" align="aligncenter" width="640"] Slika 7 Pravljenje imenovanih raspona na osnovu odabranih podataka[/caption]

- Želim promjeniti kurs eura? Vidim imenovani raspon u formulama i formule ispravno funkcionišu, ali ga ne vidim u spisku imenovanih raspona u gornjem lijevom uglu.

[caption id="" align="aligncenter" width="640"] Slika 8 Imenovani raspon "EUR" se vidi u formulama, ali se ne vidi na spisku imenovanih raspona[/caption]

- Pa izgledalo bi neprofesionalno da imamo usamljenu ćeliju na koju će se formule referencirati. Rješenje je da se napravi imenovani raspon koji neće biti nijedna ćelija već će biti pohranjen u memoriji fajla. U ovom slučaju to je kurs eura (1,95583 KM). Kada se u formuli bude koristio raspon pod imenom „EUR“, koristiće se vrijednost koju si unio (1,95583).

Na meniju „Formulas“ odaberi „Define Name“. Dodjeli ime (EUR) a u polju „Refers to“ unesi kurs eura. Jako bitna stvar je da li se imenovani raspon odnosi samo na određeni radni list ili na cijeli fajl.

[caption id="" align="aligncenter" width="489"] Slika 9 Kreiranje imenovanog raspona u memoriji fajla[/caption]

Napomene:

- Kod brisanja imenovanih raspona paziti na formule koje se referenciraju na imenovani raspon koji želiš obrisati jer formule više neće funkcionisati

- Kod kreiranja imenovanih raspona je bolje koristiti meni „Formulas“ i definisati odmah je li imenovani raspon na nivou radnog lista ili fajla. Ovo je jako osjetljivo pitanje jer mogu postojati dva imenovana raspona s istim imenom na dva radna lista. Kod komplikovanijih formula ovo lako može da zbuni. Preporučujem da se imenovani rasponi kreiraju na nivou fajla, ili bar da imaju jedinstvene nazive.

Najava: Pošto imamo sve sastojke (Data Validation, VLOOKUP i Imenovane raspone), „uz jednu žlicu“ INDIRECT funkcije, u jednom od narednih tekstova ćemo pokazati kako napraviti „pametne padajuće liste“, odnosno, kako napraviti da kada se u jednoj padajućoj listi odabere jedna opcija da se sadržaj druge padajuće liste automatski mjenja.

U narednom broju: matrične formule (ili formule nizova, array formulas) – naravno, višećelijske i jednoćelijske