Za natančne izračune v Excelu je nujno razumeti, kako delujejo različne vrste sklicev na celice.
Reference A1 proti R1C1
Excelovi delovni listi vsebujejo veliko celic in (privzeto) je vsaka celica označena s svojo pismo v stolpcu sledi številka vrstice. To je znano kot sklicevanje v slogu A1. Primeri: A1, B4, C6
Referenčni slog A1
Po želji lahko preklopite na Referenčni način R1C1 sklicevati se na vrstico celice & številka stolpca. Namesto sklicevanja na celico A1 bi se sklicevali na R1C1 (vrstica 1, stolpec 1). Celica C4 bi se nanašala na R4C3.
Referenčni slog R1C1
Sklicevanje v slogu R1C1 je v Excelu izjemno redko. Razen če imate dober razlog, bi se verjetno morali držati privzetega referenčnega načina v slogu A1. Če pa uporabljate VBA, boste verjetno naleteli na ta referenčni slog.
Preklopite na referenčni slog R1C1
Če želite preklopiti referenčni slog, pojdite na Datoteka> Možnost> Formula. Potrdite polje zraven Referenčni slog R1C1.
Imenovani razponi
Ena izmed najbolj slabo uporabljenih funkcij Excela je funkcija imenovanih območij. Namesto da se na celico (ali skupino celic) sklicujete na njeno lokacijo (npr. B3 ali R3C2), lahko poimenujete ta obseg in se preprosto sklicujete na ime obsega v svojih formulah.
Če želite poimenovati obseg:
- Izberite celico ali celice, ki jih želite poimenovati
- Kliknite Znotraj polja z imenom območja
- Vnesite želeno ime
- Pritisnite Enter
Zdaj se lahko sklicujete na celico A1, tako da namesto tega vnesete = range_name1. To je zelo uporabno pri delu z velikimi delovnimi zvezki z več delovnimi listi.
Obseg celic
Pri uporabi vgrajenih funkcij Excela se boste morda morali sklicevati na obseg celic. Obsegi celic so videti takole 'A1: B3'. Ta sklic se nanaša na vse celice med A1 in B3: celice A1, A2, A3, B1, B2, B3.
Če želite pri vnosu formule izbrati obseg celic:
- Vnesite obseg (ločite začetni in končni obseg s podpičjem)
- Z miško kliknite prvo referenco celice, pridržite gumb miške in povlecite želeni obseg.
- Pridržite tipko shift in se s puščičnimi tipkami pomaknite do območja
Absolutne (zamrznjene) in relativne reference
Ko v formule vnašate sklice na celice, lahko uporabite relativne ali absolutne (zamrznjene) reference. Relativni sklici se bodo sorazmerno premaknili, ko boste formulo kopirali v novo celico. Absolutne reference ostanejo nespremenjene. Poglejmo nekaj primerov:
Relativna referenca
Relativna referenca v Excelu izgleda tako
= A1
Ko kopirate in prilepite formulo z relativnimi referencami, se bodo relativne reference premikale sorazmerno. Če ne določite drugače, bodo vaše sklice na celice privzeto relativne (neodmrznjene).
Primer: Če kopirate "= A1" navzdol eno vrstico, se sklic spremeni v "= A2".
Absolutne (zamrznjene) reference celic
Če ne želite, da se sklice na celice premikajo, ko kopirate formulo, jih lahko »zamrznete«, tako da pred sklicem, ki ga želite zamrzniti, dodate znake dolarja ($ s). Ko kopirate in prilepite formulo, sklic na celico ostane nespremenjen. Izberete lahko zamrznitev sklica na vrstico, sklic na stolpec ali oboje.
A1: Nič ni zamrznjeno
$ A1: Stolpec je zamrznjen, vrstica pa ni zamrznjena
A $ 1: Vrstica je zamrznjena, vendar stolpec ni zamrznjen
$ A $ 1: Vrstica in stolpec sta zamrznjena
Absolutna referenčna bližnjica
Ročno dodajanje znakov dolarja ($ s) v formule ni zelo praktično. Namesto tega med ustvarjanjem formule s tipko F4 preklapljajte med absolutnimi/relativnimi referencami celic.
Primer absolutne referenčne celice
Kdaj bi morali dejansko zamrzniti sklic na celico? En pogost primer je, če imate vhodne celice, na katere se pogosto sklicujete. V spodnjem primeru želimo izračunati prometni davek za vsako količino elementov menija. Prodajni davek je pri vseh postavkah konstanten, zato se bomo na celico prometnega davka večkrat sklicevali.
Če želite poiskati skupni prometni davek, v stolpec D vnesite formulo „= (B3*C3)*$ C $ 1“ in kopirajte formulo navzdol.
Mešana referenca
Morda ste že slišali za sklice na mešane celice. Mešani sklic je, če je sklic na vrstico ali stolpec zaklenjen (vendar ne oboje).
Mešana referenca
Ne pozabite, da s tipko »F4« lahko krožite po svojih relativnih, absolutnih referencah celic.
Reference celic - Vstavljanje in brisanje vrstic/stolpcev
Morda se sprašujete, kaj se zgodi z referencami celic, ko vstavite ali izbrišete vrstice/stolpce?
Sklic na celico se bo samodejno posodobil in se skliceval na prvotno celico. Tako je ne glede na to, ali je referenca celice zamrznjena.
3D reference
Včasih boste morali delati z več delovnimi listi z enakimi vzorci podatkov. Excel vam omogoča, da se sklicujete na več listov hkrati, ne da bi morali ročno vnesti vsak delovni list. Lahko se sklicujete na vrsto listov, podobno kot bi se nanašali na obseg celic. Primer „List1: List5! A1“ bi se skliceval na celice A1 na vseh listih od lista1 do lista5.
Poglejmo si primer:
Želite združiti skupno prodano enoto za vsak izdelek v vseh trgovinah. Vsaka trgovina ima svoj delovni list in vsi delovni listi imajo enako obliko. Ustvarite lahko podobno formulo:
To ni pretežko le s štirimi delovnimi listi, kaj pa, če bi imeli 40 delovnih listov? Bi res radi ročno dodali vsako referenco celice?
Namesto tega lahko uporabite 3D sklic, da z lahkoto nanesete več listov hkrati (podobno kot se lahko sklicujete na obseg celic).
Bodi previden! Vrstni red vaših delovnih listov je pomemben. Če med referenčnimi listi (StoreA in StoreD) premaknete drug list, bo ta list vključen. Nasprotno, če list premaknete izven obsega listov (pred StoreA ali po StoreD), ta ne bo več vključen.
Referenca krožnih celic
Referenca krožne celice je, ko se celica sklicuje nase. Na primer, če se rezultat celice B1 uporabi kot vhod za celico B1, se ustvari krožna referenca. Celici se ni treba neposredno sklicevati nase. Obstajajo lahko vmesni koraki.
Primer:
V tem primeru je formula za celico B2 "A2+A3+B2". Ker ste v celici B2, v enačbi ne smete uporabljati B2. To bi sprožilo krožno referenco in vrednost v celici »B2« bo samodejno nastavljena na »0«.
Običajno so krožne reference posledica uporabniške napake, v nekaterih primerih pa boste morda želeli uporabiti krožno referenco. Primarni primer uporabe krožne reference je iterativno izračunavanje vrednosti. Če želite to narediti, morate iti na Datoteka> Možnosti> Formule in Omogočen ponavljajoč izračun:
Zunanje reference
Včasih se boste pri izračunu podatkov morali sklicevati na podatke izven delovnega zvezka. To se imenuje zunanja referenca (povezava).
Če želite med ustvarjanjem formule izbrati zunanjo referenco, se pomaknite do zunanjega delovnega zvezka in izberite svojo referenco kot običajno.
Če se želite pomakniti do drugega delovnega zvezka, uporabite bližnjico CTRL + TAB ali pojdite na Pogled> Preklopi Windows.
Ko izberete celico, boste videli zunanjo referenco, ki izgleda takole:
Upoštevajte, da je ime delovnega zvezka obdano z oklepaji [].
Ko zaprete referenčni delovni zvezek, bo sklic prikazal lokacijo datoteke:
Ko znova odprete delovni zvezek, ki vsebuje zunanjo povezavo, boste pozvani, da omogočite samodejno posodabljanje povezav. Če to storite, bo Excel odprl referenčno vrednost s trenutno vrednostjo delovnega zvezka. Tudi če je zaprt! Bodi previden! Tega si lahko ali pa tudi ne želite.
Imenovana območja in zunanje reference
Kaj se zgodi z vašo zunanjo referenčno celico, ko se vrstice / stolpci dodajo ali izbrišejo iz referenčnega delovnega zvezka? Če sta odprta oba delovna zvezka, se sklice na celice samodejno posodobijo. Če pa oba delovna zvezka nista odprta, se sklice na celice ne posodobijo in ne bodo več veljavne. To je velika skrb pri povezovanju z zunanjimi delovnimi zvezki. Zaradi tega nastane veliko napak.
Če imate povezavo do zunanjih delovnih zvezkov, morate sklic na celico poimenovati z imenovanim obsegom (za več informacij glejte prejšnji razdelek). Zdaj se bo vaša formula nanašala na imenovano območje, ne glede na spremembe, ki se pojavijo v zunanjem delovnem zvezku.
