Bilo koji alat za proračunske tablice omogućuje referenciranje ćelija i raspona. Excel je najbolji i najmoćniji na svijetu.
U ovom ću vodiču pokriti sve što trebate znati o referenciranju ćelija i raspona u Excelu. Osim osnovnog upućivanja na isti list, najveći dio ovog vodiča bio bi o kako uputiti na drugi list ili radnu knjigu u Excelu.
Iako nema velike razlike u načinu na koji to funkcionira, kada referencirate drugi list u istoj datoteci ili referencirate potpuno zasebnu Excel datoteku, format te reference se malo mijenja.
Također, postoje neke važne stvari koje morate imati na umu kada upućujete na drugi list ili druge vanjske datoteke.
Ali brini … ništa previše ludo!
Kad završite s ovim vodičem, znat ćete sve što trebate znati o referenciranju ćelija i raspona u Excelu (biti u istoj radnoj knjizi ili drugoj radnoj knjizi).
Započnimo!
Pozivanje na ćeliju na istom listu
Ovo je najosnovnija razina upućivanja kada upućujete na ćeliju na istom listu.
Na primjer, ako sam u ćeliji B1 i želim se pozvati na ćeliju A1, format bi bio:
= A1
Kad to učinite, vrijednost u ćeliji u kojoj koristite ovu referencu bit će ista kao u ćeliji A1. A u slučaju da napravite bilo kakve promjene u ćeliji A1, one će se odraziti u ćeliji u kojoj ste koristili ovu referencu.
Pozivanje na ćeliju na drugom listu
Ako se morate referirati na drugi list u istoj radnoj knjizi, morate upotrijebiti donji format:
Naziv lista! Adresa ćelije
Prvo, imate naziv lista iza kojeg slijedi uskličnik iza kojeg slijedi referenca ćelije.
Dakle, ako se trebate pozvati na ćeliju A1 u listu 1, morate upotrijebiti sljedeću referencu:
= List1! A1
A ako se želite uputiti na raspon ćelija na drugom listu, morate upotrijebiti sljedeći format:
Naziv lista! Prva_ćelija: Zadnja_ćelija
Dakle, ako se želite uputiti na raspon A1: C10 na drugom listu u istoj radnoj knjizi, morate upotrijebiti donju referencu:
= List1! A1: C10
Imajte na umu da sam vam pokazao samo referencu na ćeliju ili raspon. U stvarnosti biste ih koristili u formulama. No, format gore spomenutih referenci ostat će isti
U mnogim slučajevima radni list na koji se pozivate imao bi više riječi u imenu. Na primjer, moglo bi biti Podaci o projektu ili Podaci o prodaji.
U slučaju da imate razmake ili znakove koji nisu abecedni (poput @,!, #, -itd.), Morate koristiti naziv unutar pojedinačnih navodnika.
Na primjer, ako želite uputiti ćeliju A1 na listu pod nazivom Podaci o prodaji, upotrijebit ćete donju referencu:
= 'Podaci o prodaji'! A1
A u slučaju da je naziv lista Sales-Data, tada za upućivanje na ćeliju A1 u ovom listu morate upotrijebiti donju referencu:
= 'Podaci o prodaji'! A1
Kada se pozivate na list u istoj radnoj knjizi, a zatim kasnije promijenite naziv radnog lista, ne morate se brinuti da će se referenca pokvariti. Excel će umjesto vas automatski ažurirati te reference.
Iako je sjajno znati format ovih referenci, u praksi nije pametno svaki put ručno upisivati ove reference. Bilo bi dugotrajno i vrlo sklono pogreškama.
Dopustite mi da vam pokažem bolji način za stvaranje referenci ćelija u Excelu.
Automatsko stvaranje referenci na drugi list u istoj radnoj knjizi
Mnogo bolji način za stvaranje referencije ćelije na drugi list je jednostavno usmjeriti Excel na ćeliju/raspon na koji želite stvoriti referencu i dopustiti da Excel to sam stvori.
To će osigurati da ne morate brinuti o tome da uskličnik ili navodnici nedostaju ili da se pojavi bilo koji drugi problem s formatom. Excel će automatski stvoriti ispravnu referencu za vas.
U nastavku su navedeni koraci za automatsko stvaranje reference na drugi list:
- Odaberite ćeliju u trenutnoj radnoj knjizi gdje trebate referencu
- Upišite formulu dok vam ne zatreba referenca (ili znak jednakosti ako samo želite referencu)
- Odaberite list na koji se trebate pozvati
- Odaberite ćeliju/raspon na koji se želite odnositi
- Pritisnite Enter da biste dobili rezultat formule (ili nastavite raditi na formuli)
Gore navedeni koraci automatski bi stvorili referencu na ćeliju/raspon na drugom listu. Ove ćete reference moći vidjeti i u traci s formulama. Kada završite, jednostavno pritisnite tipku enter i to će vam dati rezultat.
Na primjer, ako imate neke podatke u ćeliji A1: A10 na listu pod nazivom Podaci o prodaji, a želite dobiti zbroj ovih vrijednosti u trenutnom listu, bit će to sljedeći koraci:
- U tekuću tablicu (gdje vam je potreban rezultat) upišite sljedeću formulu: = Zbroj (
- Odaberite list "Podaci o prodaji".
- Odaberite raspon koji želite dodati (A1: A10). Čim to učinite, Excel će automatski stvoriti referencu na ovaj raspon (to možete vidjeti na traci s formulama)
- Pritisnite tipku enter.
Kada stvarate dugu formulu, možda ćete se morati pozvati na ćeliju ili raspon na drugom listu, a zatim ćete se morati vratiti na početnu stranicu i uputiti se na neku ćeliju/raspon tamo.
Kad to učinite, primijetit ćete da Excel automatski umeće referencu lista na list na kojem imate formulu. Iako je to u redu i ne šteti, nije potrebno. U tom slučaju možete zadržati referencu ili je ručno ukloniti.
Još jedna stvar koju morate znati prilikom stvaranja referenci odabirom lista, a zatim ćelije/raspona je da će Excel uvijek stvoriti datoteku relativna referenca (tj. reference sa znakom n0 $). To znači da ako kopiram i zalijepim formulu (jednu s referencom na drugi list) u neku drugu ćeliju, ona će automatski prilagoditi referencu.
Evo primjera koji objašnjava relativne reference.
Pretpostavimo da koristim sljedeću formulu u ćeliji A1 u trenutnom listu (za upućivanje na ćeliju A1 u nazivu lista SalesData)
= Podaci o prodaji! A1
Ako kopiram ovu formulu i zalijepim je u ćeliju A2, formula će se promijeniti u:
= Podaci o prodaji! A1
To se događa jer je formula relativna i kada je kopiram i zalijepim, reference će se automatski prilagoditi.
U slučaju da želim da se ova referenca uvijek odnosi na ćeliju A1 na listu SalesData, morat ću koristiti donju formulu:
= Podaci o prodaji! $ A $ 1
Znak dolara prije broja retka i stupca zaključava ove reference kako se ne bi promijenile.
Evo detaljnog vodiča u kojem možete saznati više o apsolutnim, mješovitim i relativnim referencama.
Sada kada smo razmotrili kako se uputiti na drugi list u istoj radnoj knjizi, pogledajmo kako se možemo odnositi na drugu radnu knjigu.
Kako se uputiti na drugu radnu knjigu u Excelu
Kada se pozivate na ćeliju ili raspon na drugu Excel radnu knjigu, format te reference ovisit će o tome je li ta radna knjiga otvorena ili zatvorena.
I naravno, naziv radne bilježnice i radnih listova također igraju ulogu u određivanju formata (ovisno o tome imate li u imenu razmaknute ili ne-abecedne znakove ili ne).
Pogledajmo dakle različite formate vanjskih referenci na drugu radnu knjigu u različitim scenarijima.
Vanjska referenca na otvorenu radnu knjigu
Kad se radi o upućivanju na vanjsku otvorenu radnu knjigu, morate navesti naziv radne knjige, naziv radnog lista i adresu ćelije/raspona.
Dolje je format koji morate koristiti kada se pozivate na vanjsku otvorenu radnu knjigu
= '[FileName] SheetName! CellAddress
Pretpostavimo da imate radnu knjigu ‘ExampleFile.xlsx’ i želite se uputiti na ćeliju A1 u listu 1 ove radne knjige.
Ispod je referenca za ovo:
= [ExampleFile.xlsx] Podaci o prodaji! A1
U slučaju da u nazivu vanjske radne knjige ili nazivu lista (ili oboje) ima razmaka, morate dodati naziv datoteke (u uglatim zagradama) i naziv lista u jednostruke navodnike.
Ispod su primjeri u kojima biste morali imati imena u navodnicima:
= '[Primjer datoteke.xlsx] Podaci o prodaji'! A1 = '[Primjer datoteke.xlsx] Podaci o prodaji'! A1 = '[Primjer datoteke.xlsx] Podaci o prodaji'! A1
Kako stvoriti referencu na drugu radnu knjigu (automatski)
Opet, iako je dobro znati format, najbolje je ne upisivati ga ručno.
Umjesto toga, samo usmjerite Excel u pravom smjeru i on će vam stvoriti ove reference. Ovo je puno brže s manje šanse za pogreške.
Na primjer, ako imate neke podatke u ćeliji A1: A10 u radnoj knjizi pod nazivom "Primjer datoteke" na listu pod nazivom "Podaci o prodaji", a želite dobiti zbroj ovih vrijednosti u trenutnom listu, dolje će biti navedeni koraci :
- U tekuću tablicu (gdje vam je potreban rezultat) upišite sljedeću formulu: = Zbroj (
- Idite u radnu knjigu "Primjer datoteke"
- Odaberite list "Podaci o prodaji".
- Odaberite raspon koji želite dodati (A1: A10). Čim to učinite, Excel će automatski stvoriti referencu na ovaj raspon (to možete vidjeti na traci s formulama)
- Pritisnite tipku enter.
Time bi se odmah stvorila formula s ispravnim referencama.
Jedna stvar koju biste primijetili pri stvaranju reference na vanjsku radnu knjigu je ta da će ona uvijek stvarati apsolutne reference. To znači da se ispred brojeva redaka i stupaca nalazi znak $. To znači da ako kopirate i zalijepite ovu formulu u druge ćelije, ona će se zbog apsolutne reference stalno pozivati na isti raspon.
U slučaju da želite da se to promijeni, morate ručno promijeniti reference.
Vanjska referenca na zatvorenu radnu knjigu
Kada je vanjska radna knjiga otvorena i pozivate se na ovu radnu knjigu, trebate samo navesti naziv datoteke, naziv lista i adresu ćelije/raspona.
Ali kad se ovo zatvori, Excel nema pojma gdje tražite ćelije/raspon na koje ste se pozvali.
Zato prilikom stvaranja reference na zatvorenu radnu knjigu morate također navesti put do datoteke.
Dolje je referenca koja se odnosi na ćeliju A1 na radnom listu Sheet1 u radnoj knjizi Primjer datoteke. Budući da ova datoteka nije otvorena, također se odnosi na mjesto na kojem je datoteka spremljena.
= 'C: \ Users \ sumit \ Desktop \ [Primjer datoteke.xlsx] List1'! $ A $ 1
Gornja referenca ima sljedeće dijelove:
- Putanja datoteke - mjesto na vašem sustavu ili mreži na kojem se nalazi vanjska datoteka
- Naziv datoteke - naziv vanjske radne knjige. To bi uključivalo i ekstenziju datoteke.
- Naziv lista - naziv lista u kojem govorite o ćelijama/rasponima
- Adresa ćelije/raspona - točna adresa ćelije/raspona na koju se pozivate
Kada stvorite vanjsku referencu za otvorenu radnu knjigu, a zatim je zatvorite, primijetit ćete da se referenca automatski mijenja. Nakon zatvaranja vanjske radne knjige, Excel automatski ubacuje i referencu na putanju datoteke.
Utjecaj promjene lokacije datoteke na reference
Kada stvorite referencu na ćeliju/raspon u vanjskoj Excel datoteci, a zatim je zatvorite, referenca sada koristi i put do datoteke.
Ali ako promijenite mjesto datoteke, ništa se neće promijeniti u vašoj radnoj knjizi (u kojoj stvarate referencu). No, budući da ste promijenili lokaciju, veza je sada prekinuta.
Dakle, ako zatvorite i otvorite ovu radnu knjigu, ona će vam reći da je veza neispravna i da morate ažurirati vezu ili je potpuno prekinuti. Pokazat će vam upit kao što je prikazano u nastavku:
Kada kliknete na Ažuriraj, prikazat će vam se drugi upit u kojem možete odabrati opcije za uređivanje veza (koje će vam prikazati donji dijaloški okvir)
Ako trebate zadržati ove datoteke povezanima, možete odrediti novo mjesto datoteke klikom na Ažuriraj vrijednosti. Excel vam otvara dijaloški okvir u kojem možete odrediti novo mjesto datoteke tako što ćete se kretati tamo i odabrati ga.
Pozivanje na definirano ime (u istoj ili vanjskoj radnoj knjizi)
Kada se morate odnositi na ćelije i raspone, bolji način je stvaranje definiranih naziva za raspone.
Ovo je korisno jer vam olakšava upućivanje na ove raspone pomoću naziva umjesto duge i komplicirane referentne adrese.
Na primjer, lakše je koristiti = SalesData umjesto = [Primjer datoteke.xlsx] List1 ′! $ A $ 1: $ A $ 10
A u slučaju da ste koristili ovo definirano ime u više formula i trebate promijeniti referencu, to morate učiniti samo jednom.
Evo koraka za stvaranje imenovanog raspona za niz ćelija:
- Odaberite sve ćelije koje želite uključiti u imenovani raspon
- Kliknite karticu Formule
- Kliknite na opciju Define Name (ona je u grupi Defined Names)
- U dijaloškom okviru Novi naziv dajte ovom rasponu naziv (u ovom primjeru koristim naziv SalesData). Upamtite da u imenu ne možete imati razmake
- Obuhvatite kao radnu knjigu (osim ako nemate jak razlog da to učinite na razini lista)
- Uvjerite se da je Referenca raspona točna.
- Pritisnite U redu.
Sada je vaš imenovani raspon kreiran i možete ga koristiti umjesto referenci ćelija s adresama ćelija.
Na primjer, ako želim dobiti zbroj svih ovih ćelija u rasponu SalesData, možete upotrijebiti donju formulu:
= SUM (Podaci o prodaji)
A što ako želite koristiti ovaj imenovani raspon su drugi radni listovi ili čak druge radne knjige?
Možeš!
Vi samo trebate slijediti isti format o kojem smo govorili u gornjem odjeljku.
Nema potrebe da se vraćate na početak ovog članka. Dopustite mi da vam ovdje navedem sve primjere kako biste stekli ideju.
Radna sveska Vs Radni list Imenovani rasponi: Kad kreirate naziv raspona razine radne knjige, mogao bi postojati samo jedan raspon s tim imenom. Dakle, ako se pozivate na to iz vanjskih radnih knjiga ili radnih listova u istoj radnoj knjizi, ne morate navesti naziv radnog lista (jer je dostupan za korištenje u cijeloj radnoj knjizi). U slučaju da koristite nazivni raspon razine radnog lista, moći ćete ga koristiti samo na radnom listu, a dok se pozivate na njega, morat ćete navesti i naziv radnog lista.Referenciranje definiranog imena na istom radnom listu ili radnoj knjizi
Ako ste stvorili definirano ime za razinu radne knjige, možete ga koristiti bilo gdje u radnoj knjizi samo korištenjem samog definiranog naziva.
Na primjer, ako želim dobiti zbroj svih ćelija u imenovanom rasponu koji smo stvorili (SaledData), mogu upotrijebiti donju formulu:
= SUM (SaledData)
U slučaju da ste stvorili razinu radnog lista s nazivom raspon, ovu formulu možete koristiti samo ako je imenovani raspon kreiran na istom listu na kojem koristite formulu.
U slučaju da ga želite koristiti na drugom listu (recimo Sheet2), morate upotrijebiti sljedeću formulu:
= SUM (List 1! $ A $ 1: $ A $ 10)
A u slučaju da u nazivu lista postoje razmaci ili alfanumerički znakovi, morat ćete staviti naziv lista u pojedinačne navodnike.
= SUM ('List 1'! $ A $ 1: $ A $ 10)
Pozivanje na definirano ime u drugoj radnoj knjizi (otvoreno ili zatvoreno)
Kad se želite uputiti na imenovani raspon u drugoj radnoj knjizi, morat ćete navesti naziv radne knjige, a zatim i naziv raspona.
Na primjer, ako imate radnu knjigu programa Excel s imenom ExampleFile.xlsx i imenovani raspon s imenom SalesData, tada možete upotrijebiti donju formulu za dobivanje zbroja ovog raspona iz druge radne knjige:
= SUM (Primjer datoteke.xlsx! SalesData)
U slučaju da u nazivu datoteke postoje razmaci, morate ih koristiti u pojedinačnim navodnicima.
= SUM ('Primjer datoteke.xlsx'! SalesData)
U slučaju da imate imenovane raspone na razini lista, tada morate navesti naziv radne knjige kao i radni list kada se pozivate na nju iz vanjske radne knjige.
Ispod je primjer upućivanja naziva raspona na razini lista:
= SUM ('[Primjer datoteke.xlsx] List 1'! Podaci o prodaji)
Kao što sam već spomenuo, uvijek je najbolje stvoriti nazive raspona razine radne knjige, osim ako nemate jak razlog za stvaranje razine prvog radnog lista.
U slučaju da referencirate imenovani raspon u zatvorenoj radnoj knjizi, morat ćete navesti i put do datoteke. Ispod je primjer ovoga:
= SUM ('C: \ Users \ sumit \ Desktop \ Primjer datoteke.xlsx'! SalesData)
Kada stvorite referencu na imenovani raspon u otvorenoj radnoj knjizi, a zatim zatvorite radnu knjigu, Excel automatski mijenja referencu i dodaje putanju datoteke.
Kako stvoriti referencu na imenovani raspon
Ako stvarate i radite s mnogo imenovanih raspona, nije moguće sjetiti se imena svakog od njih.
Excel vam pomaže tako što vam prikazuje popis svih imenovanih raspona koje ste stvorili i omogućuje vam da ih jednim klikom umetnete u formule.
Pretpostavimo da ste stvorili imenovani raspon SalesData koji želite koristiti u formuli za ZBIRANJE svih vrijednosti u imenovanom rasponu.
Evo koraka za to:
- Odaberite ćeliju u koju želite unijeti formulu.
- Unesite formulu do točke u kojoj trebate umetnuti imenovani raspon
- Pritisnite tipku F3 na tipkovnici. Ovo će otvoriti dijaloški okvir Zalijepi naziv sa popisom svih imena koja ste stvorili
- Dvaput kliknite na ime koje želite umetnuti.
Gore navedeni koraci umetnuli bi naziv u formulu i možete nastaviti raditi na formuli.
Napomena: Ova tehnika radi samo za nazive raspona koji se nalaze u istoj radnoj knjizi. Ako želite unijeti imena u vanjsku radnu knjigu, morat ćete otići tamo, provjeriti ime pomoću dijaloškog okvira Zalijepi naziv, a zatim se vratiti i koristiti ga u formuli (u ispravnom formatu koristeći naziv datoteke).Ovo je sve što trebate znati o upućivanju na druge listove ili radne knjige i kako stvoriti vanjsku referencu u Excelu.
Nadam se da vam je ovaj vodič bio koristan.
