U Microsoft Excel-u, uobičajen je postupak referenciranja ćelija koje se nalaze na drugim radnim listovima, ili čak u sasvim drugim Excel datotekama. Na početku, ovakav zadatak može delovati zastrašujuće i zbunjujuće, ali kada se jednom razume princip rada, ispostavlja se da nije tako komplikovano.
U ovom tekstu ćemo detaljno objasniti kako se referencira drugi list unutar iste Excel datoteke, kao i kako se referencira ćelija u drugoj Excel datoteci. Takođe, obradićemo teme kao što su referenciranje opsega ćelija unutar funkcije, kako pojednostaviti stvari korišćenjem definisanih imena, i kako primeniti VLOOKUP za dinamičke reference.
Kako referencirati drugi list u okviru iste Excel datoteke
Osnovni oblik reference ćelije se sastoji od slova kolone, za kojim sledi broj reda.
Dakle, referenca ćelije B3 upućuje na ćeliju koja se nalazi u preseku kolone B i reda 3.
Kada se referencirate na ćelije koje se nalaze na drugim listovima, ispred ove reference ćelije dodaje se naziv drugog lista. Na primer, ispod je prikazana referenca na ćeliju B3 koja se nalazi na listu pod nazivom „januar“.
=January!B3
Znak uzvika (!) služi kao separator između imena lista i adrese ćelije.
Ako naziv lista sadrži razmake, onda je potrebno da naziv obuhvatite jednostrukim navodnicima unutar reference.
='January Sales'!B3
Za kreiranje ovakvih referenci, moguće ih je direktno unositi u ćeliju. Međutim, jednostavnije i pouzdanije je prepustiti Excel-u da automatski generiše referencu.
U ćeliju unesite znak jednakosti (=), zatim kliknite na jezičak (tab) željenog lista, a potom kliknite na ćeliju koju želite da referencirate.
Dok to radite, Excel automatski unosi referencu u traku sa formulama.
Pritisnite Enter da biste finalizovali formulu.
Kako referencirati drugu Excel datoteku
Možete se pozivati na ćelije iz druge radne sveske, koristeći identičan metod. Jedino je važno da druga Excel datoteka bude otvorena pre nego što počnete unositi formulu.
Upišite znak jednakosti (=), pređite na drugu datoteku, i potom kliknite na ćeliju u toj datoteci koju želite da referencirate. Kada završite, pritisnite Enter.
Kompletirana unakrsna referenca obuhvata ime druge radne sveske, smešteno unutar uglastih zagrada, a za njim sledi naziv lista i adresa ćelije.
=[Chicago.xlsx]January!B3
U slučaju da naziv datoteke ili lista sadrži razmake, moraćete da stavite referencu datoteke (uključujući i uglaste zagrade) unutar jednostrukih navodnika.
='[New York.xlsx]January'!B3
U ovom primeru, možete primetiti znakove dolara ($) unutar adrese ćelije. To predstavlja apsolutnu referencu ćelije (više o apsolutnim referencama ćelije možete saznati ovde).
Prilikom referenciranja ćelija i opsega unutar različitih Excel datoteka, reference su po default-u apsolutne. Ovo se može izmeniti u relativnu referencu ukoliko je to potrebno.
Ukoliko pogledate formulu u situaciji kada je referentna radna sveska zatvorena, ona će sadržati kompletan put do te datoteke.
Iako je kreiranje referenci prema drugim radnim sveskama jednostavan postupak, one su osetljivije na potencijalne probleme. Korisnici koji kreiraju, preimenuju ili premeštaju foldere i datoteke mogu oštetiti ove reference i prouzrokovati greške.
Stoga je, ukoliko je moguće, pouzdanije čuvati podatke u jednoj radnoj svesci.
Kako unakrsno referencirati opseg ćelija u okviru funkcije
Referenciranje pojedinačne ćelije može biti vrlo korisno. Međutim, možda ćete želeti da koristite funkciju (kao što je SUM) koja se odnosi na opseg ćelija smešten na drugom radnom listu ili radnoj svesci.
Pokrenite funkciju kao i obično, a potom kliknite na list i odaberite opseg ćelija, na isti način kao u prethodnim primerima.
U sledećem primeru, funkcija SUM sabira vrednosti iz opsega B2:B6 na radnom listu pod imenom Prodaja.
=SUM(Sales!B2:B6)
Kako koristiti definisana imena za jednostavnije unakrsne reference
U okviru Excel-a, imate opciju da dodelite ime određenoj ćeliji ili opsegu ćelija. To je mnogo smislenije od korišćenja adrese ćelije ili opsega kada se referencirate na njih. Ukoliko koristite veliki broj referenci unutar tabele, davanje imena tim referencama može značajno olakšati razumevanje onoga što ste uradili.
Još bolje, to ime je jedinstveno za sve radne listove u okviru te Excel datoteke.
Na primer, mogli bismo ćeliju nazvati ‘ChicagoTotal’, i tada bi unakrsna referenca glasila:
=ChicagoTotal
Ovo je mnogo smislenija alternativa standardnoj referenci, kao što je ova:
=Sales!B2
Kreiranje definisanog imena je jednostavno. Počnite tako što ćete selektovati ćeliju ili opseg ćelija koje želite da imenujete.
Kliknite na polje za ime koje se nalazi u gornjem levom uglu, unesite željeno ime, i potom pritisnite Enter.
Prilikom kreiranja definisanih imena, ne možete koristiti razmake. Zbog toga su u ovom primeru reči spojene unutar imena i razdvojene velikim slovom. Takođe, reči možete razdvojiti i znakovima kao što su crtica (-) ili donja crta (_).
Excel takođe poseduje „Name Manager“ (Menadžer imena) koji olakšava praćenje ovih imena u budućnosti. Kliknite na „Formulas“ (Formule) > „Name Manager“ (Menadžer imena). Unutar prozora „Name Manager“ možete videti spisak svih definisanih imena unutar radne sveske, njihove lokacije, i vrednosti koje trenutno čuvaju.
Zatim, možete koristiti dugmiće na vrhu kako biste uredili ili obrisali definisana imena.
Kako formatirati podatke kao tabelu
Kada radite sa obimnom listom povezanih podataka, korišćenje Excel-ove funkcije „Format as Table“ (Formatiraj kao tabelu) može pojednostaviti način na koji referencirate podatke u njoj.
Uzmite sledeću jednostavnu tabelu kao primer.
Ovo se može formatirati kao tabela.
Kliknite na bilo koju ćeliju unutar liste, prebacite se na jezičak „Home“ (Početna), kliknite na dugme „Format as Table“ (Formatiraj kao tabelu), i zatim odaberite željeni stil.
Potvrdite da je opseg ćelija ispravan, i da vaša tabela ima zaglavlja.
Nakon toga možete dodeliti smisleno ime vašoj tabeli na jezičku „Design“ (Dizajn).
Zatim, ukoliko želimo da saberemo prodaju u Čikagu, mogli bismo da referenciramo tabelu po njenom imenu (sa bilo kog lista), a zatim uneti uglastu zagradu ([) kako bismo videli spisak kolona u tabeli.
Odaberite kolonu duplim klikom na nju u spisku, i unesite zatvorenu uglastu zagradu. Rezultujuća formula bi izgledala otprilike ovako:
=SUM(Sales[Chicago])
Možete videti kako tabele olakšavaju referenciranje podataka za agregacijske funkcije kao što su SUM i AVERAGE u odnosu na standardne reference na listove.
Ova tabela je mala radi potreba demonstracije. Što je tabela veća i što više listova imate u radnoj svesci, to ćete više koristi videti.
Kako koristiti VLOOKUP funkciju za dinamičke reference
Sve reference koje smo koristili u prethodnim primerima bile su fiksirane za određenu ćeliju ili opseg ćelija. To je odlično, i često je dovoljno za vaše potrebe.
Međutim, šta ako se ćelija na koju se referencirate potencijalno menja kada se unesu novi redovi?