Za točne izračune u Excelu važno je razumjeti kako funkcioniraju različite vrste referenci ćelija.
A1 vs. R1C1 Reference
Excel radni listovi sadrže mnogo ćelija i (prema zadanim postavkama) svaka je ćelija identificirana svojom slovo stupca slijedi njegov redni broj. To je poznato kao referenciranje u stilu A1. Primjeri: A1, B4, C6
Referentni stil A1
Po želji se možete prebaciti na Referentni način rada R1C1 upućivati na red ćelije & broj stupca. Umjesto upućivanja na ćeliju A1, pozvali biste se na R1C1 (redak 1, stupac 1). Ćelija C4 bi se odnosila na R4C3.
R1C1 referentni stil
Referenciranje u stilu R1C1 izuzetno je rijetko u Excelu. Osim ako nemate dobar razlog, vjerojatno biste se trebali pridržavati zadanog referentnog moda u stilu A1. Međutim, ako koristite VBA, vjerojatno ćete naići na ovaj referentni stil.
Prebacite se na R1C1 referentni stil
Da biste promijenili referentni stil, idite na Datoteka> Opcija> Formula. Potvrdite okvir pored R1C1 referentni stil.
Imenovani rasponi
Jedna od najčešće korištenih značajki programa Excel je funkcija Imenovani rasponi. Umjesto da se na ćeliju (ili skupinu ćelija) pozivate prema njezinoj ćeliji (npr. B3 ili R3C2), tom rasponu možete dati ime i jednostavno se pozivati na naziv raspona u formulama.
Da biste imenovali raspon:
- Odaberite ćeliju ili ćelije kojima želite dati ime
- Pritisnite Unutar okvira naziva raspona
- Unesite željeno ime
- Pritisnite Enter
Sada možete referencirati ćeliju A1 tako da umjesto toga upišete = range_name1. To je vrlo korisno pri radu s velikim radnim knjigama s više radnih listova.
Raspon stanica
Kada koristite Excel -ove ugrađene funkcije, možda ćete morati referencirati raspone ćelija. Rasponi ćelija pojavljuju se ovako 'A1: B3'. Ova se referenca odnosi na sve stanice između A1 i B3: stanice A1, A2, A3, B1, B2, B3.
Za odabir raspona ćelija pri unosu formule:
- Upišite raspon (početni i završni raspon odvojite točkom -zarezom)
- Mišem kliknite prvu referencu ćelije, držite pritisnutu tipku miša i povucite željeni raspon.
- Držite pritisnutu tipku shift i navigacijom tipki sa strelicama odaberite svoj raspon
Apsolutne (zamrznute) i relativne reference
Prilikom unosa referenci ćelije u formule, možete koristiti relativne ili apsolutne (zamrznute) reference. Relativne reference pomaknut će se proporcionalno kada kopirate formulu u novu ćeliju. Apsolutne reference ostat će nepromijenjene. Pogledajmo neke primjere:
Relativna referenca
Relativna referenca u Excelu izgleda ovako
= A1
Kada kopirate i zalijepite formulu s relativnim referencama, relativne reference će se pomicati proporcionalno. Osim ako ne odredite drugačije, reference ćelija će prema zadanim postavkama biti relativne (odmrznute).
Primjer: Ako kopirate "= A1" dolje u jedan redak, referenca se mijenja u "= A2".
Apsolutne (smrznute) ćelijske reference
Ako ne želite da se reference ćelija pomiču prilikom kopiranja formule, možete "zamrznuti" reference ćelija dodavanjem znakova dolara ($ s) ispred reference koju želite zamrznuti. Sada kada kopirate i zalijepite formulu, referenca ćelije će ostati nepromijenjena. Možete odabrati zamrzavanje reference retka, reference stupca ili oboje.
O1: Ništa nije smrznuto
$ A1: Stupac je zamrznut, ali redak nije zamrznut
A $ 1: Red je zamrznut, ali stupac nije zamrznut
$ A $ 1: Red i stupac su zamrznuti
Apsolutni prečac za referencu
Ručno dodavanje znakova dolara ($ s) u formule nije baš praktično. Umjesto toga, dok stvarate formulu, koristite tipku F4 za prebacivanje između apsolutnih/relativnih referenci ćelija.
Primjer apsolutne referentne ćelije
Kada biste zapravo trebali zamrznuti referencu ćelije? Jedan uobičajen primjer je kada imate ulazne ćelije na koje se često upućuje. U donjem primjeru želimo izračunati porez na promet za svaku količinu stavki izbornika. Porez na promet je konstantan za sve stavke, pa ćemo se više puta pozivati na ćeliju za porez na promet.
Da biste pronašli ukupni porez na promet, unesite formulu '= (B3*C3)*$ C $ 1' u stupac D i kopirajte formulu prema dolje.
Mješovita referenca
Možda ste čuli za mješovite reference ćelija. Mješovita referenca je kada je referenca retka ili stupca zaključana (ali ne oboje).
Mješovita referenca
Upamtite, pomoću tipke "F4" možete kružiti kroz svoje relativne, apsolutne reference ćelija.
Reference ćelija - Umetanje i brisanje redaka/stupaca
Možda se pitate što se događa s referencama ćelija kada umetnete ili izbrišete retke/stupce?
Referenca ćelije će se automatski ažurirati kako bi se odnosila na izvornu ćeliju. To je slučaj bez obzira na to je li referenca ćelije zamrznuta.
3D reference
Povremeno ćete možda morati raditi s nekoliko radnih listova s identičnim uzorcima podataka. Excel vam omogućuje upućivanje na više listova odjednom bez potrebe za ručnim unosom svakog radnog lista. Možete se pozvati na raspon listova slično kao što bi se odnosio na raspon ćelija. Primjer 'List 1: List 5! A1' upućivao bi na ćelije A1 na svim listovima od lista 1 do lista 5.
Prođimo kroz primjer:
Želite zbrajati ukupne prodane jedinice za svaki proizvod u svim trgovinama. Svaka trgovina ima svoj radni list i svi radni listovi imaju identičan format. Možete stvoriti formulu sličnu ovoj:
To nije previše teško sa samo četiri radna lista, ali što ako imate 40 radnih listova? Želite li zaista ručno dodati svaku referencu ćelije?
Umjesto toga, možete koristiti 3D referencu za referenciranje više listova odjednom s lakoćom (slično kao što možete referencirati raspon ćelija).
Budi oprezan! Redoslijed vaših radnih listova je važan. Ako premjestite drugi list između referenciranih listova (StoreA i StoreD), taj će list biti uključen. Nasuprot tome, ako pomaknete list izvan raspona listova (prije StoreA ili nakon StoreD), on više neće biti uključen.
Referenca kružne ćelije
Kružna referenca ćelije je kada se ćelija poziva na sebe. Na primjer, ako se rezultat ćelije B1 koristi kao ulaz za ćeliju B1, tada se stvara kružna referenca. Ćelija se ne mora izravno odnositi na sebe. Mogu postojati posredni koraci.
Primjer:
U ovom slučaju, formula za ćeliju B2 je “A2+A3+B2”. Budući da ste u ćeliji B2, ne smijete koristiti B2 u jednadžbi. To bi pokrenulo kružnu referencu, a vrijednost u ćeliji “B2” automatski će se postaviti na “0”.
Obično su kružne reference rezultat greške korisnika, ali u nekim slučajevima možete koristiti kružnu referencu. Primarni primjer korištenja kružne reference je izračunavanje vrijednosti iterativno. Da biste to učinili, morate otići na Datoteka> Opcije> Formule i Omogućen iterativni izračun:
Vanjske reference
S vremena na vrijeme pri izračunavanju podataka možda ćete se morati pozivati na podatke izvan radne knjige. To se naziva vanjska referenca (veza).
Da biste odabrali vanjsku referencu tijekom stvaranja formule, idite na vanjsku radnu knjigu i odaberite referencu kao što biste inače.
Za prelazak na drugu radnu knjigu možete koristiti prečac CTRL + TAB ili otići na Pogled> Promijeni Windows.
Nakon što odaberete ćeliju, vidjet ćete da vanjska referenca izgleda ovako:
Uočite da je naziv radne knjige zatvoren zagradama [].
Nakon što zatvorite referenciranu radnu knjigu, referenca će prikazati lokaciju datoteke:
Kad ponovno otvorite radnu knjigu koja sadrži vanjsku vezu, od vas će se zatražiti da omogućite automatsko ažuriranje veza. Ako to učinite, Excel će otvoriti referentnu vrijednost s trenutnom vrijednošću radne knjige. Čak i ako je zatvoreno! Budi oprezan! Ovo možete, ali i ne želite.
Imenovani rasponi i vanjske reference
Što se događa s vašom vanjskom referencom ćelije kada se retci / stupci dodaju ili izbrišu iz referentne radne knjige? Ako su obje radne knjige otvorene, reference ćelija se automatski ažuriraju. Međutim, ako obje radne knjige nisu otvorene, reference ćelija neće se ažurirati i više neće biti važeće. To je velika briga pri povezivanju s vanjskim radnim bilježnicama. To uzrokuje mnoge pogreške.
Ako se povezujete s vanjskim radnim knjigama, referenci ćelije morate dati naziv s rasponom (za više informacija pogledajte prethodni odjeljak). Sada će se vaša formula odnositi na imenovani raspon bez obzira na promjene u vanjskoj radnoj knjizi.
