Korištenje strukturiranih referenci s tablicama programa Excel

Kada stvorite tablicu programa Excel, program tablici i svakom zaglavlju stupca u tablici dodjeljuje naziv. Kada u tablicu programa Excel dodate formule, ti se nazivi automatski pojavljuju dok upisujete formulu te odabiru reference na ćelije u tablici, pa ih ne morate unositi ručno. Evo primjera kako to funkcionira u programu Excel:

Umjesto korištenja eksplicitnih referenci ćelija	Excel koristi tablicu i nazive stupaca
=Sum(C2:C7)	=SUM(ProdajaOdjela[iznos prodaje])

Ta kombinacija tablice i naziva stupaca zove se strukturirana referenca. Nazivi u strukturiranim referencama prilagođavaju se prilikom svakog dodavanja i uklanjanja podataka u tablicu.

Strukturirane reference pojavljuju se i kada stvorite formulu izvan tablice programa Excel koja se poziva na podatke u tablici. Te reference mogu olakšati pronalaženje tablica u velikoj radnoj knjizi.

Da biste u formulu uvrstili strukturirane reference, nemojte unositi reference na ćelije u formulu, već odaberite ćelije tablice na koje želite umetnuti reference. Upotrijebimo podatke iz sljedećeg primjera za unos formule koja automatski koristi strukturirane reference za izračun iznosa provizije za prodaju.

Prodavač	Regija	Iznos prodaje	% provizije	Iznos provizije
Luka	Sjever	260	10%
Roman	Jug	660	15%
Ana	Istok	940	15%
Dragan	Zapad	410	12%
Sanja	Sjever	800	15%
Ivo	Jug	900	15%

1. Kopirajte ogledne podatke iz gornje tablice, uključujući zaglavlja stupaca, i zalijepite ih u ćeliju A1 novog radnog lista programa Excel.
2. Da biste stvorili tablicu, odaberite bilo koju ćeliju u rasponu podataka, a zatim pritisnite Ctrl+T.
3. Provjerite je li potvrđen okvir Moja tablica ima zaglavlja , a zatim odaberite U redu.
4. U ćeliju E2 unesite znak jednakosti (=) pa odaberite ćeliju C2.
   Strukturirana referenca [@[Iznos prodaje]] prikazuje se u traci formule iza znaka jednakosti.
5. Upišite zvjezdicu (*) odmah nakon završne zagrade te odaberite ćeliju D2.
   Strukturirana referenca [@[% provizije]] prikazuje se u traci formule nakon zvjezdice.
6. Pritisnite Enter.
   Excel automatski stvara stupac s izračunima te formulu kopira duž cijelog stupca i prilagođava je za svaki redak.

Što se događa pri korištenju eksplicitnih referenci ćelije?

Ako u izračunati stupac upišete eksplicitne reference ćelije, možda ćete teže vidjeti što formula izračunava.

1. U oglednom radnom listu odaberite ćeliju E2
2. U traku formule unesite =C2*D2 i pritisnite Enter.

Obratite pozornost na to da Excel, kada kopira formulu niz stupac, ne koristi strukturirane reference. Ako, primjerice, dodate stupac između postojećih stupaca C i D, morat ćete izmijeniti formulu.

Promjena naziva tablice

Svaki put kada stvorite tablicu programa Excel, stvara se zadani naziv tablice (Tablica1, Tablica2 itd.). Naziv tablice možete promijeniti da biste ga učinili smislenijim.

1. Odaberite bilo koju ćeliju u tablici da bi se na vrpci prikazala kartica Dizajn tablice .
2. Upišite željeni naziv u okvir Naziv tablice , a zatim pritisnite Enter.

U oglednim se podacima koristi naziv ProdajaOdjela.

Pridržavajte se sljedećih pravila za nazive tablica:

• Koristite valjane znakove Naziv uvijek započnite slovom, podcrtom (_) ili obrnutom kosom crtom (\). Ostali znakovi u nazivu mogu biti slova, brojevi, točke i podcrte. U nazivu ne možete koristiti "C", "c", "R" ili "r" jer su to već prečaci za odabir stupca ili retka za aktivne ćelije kada ih unosite u okvir Naziv ili Idi na .

• Nemojte koristiti reference na ćelije Nazivi ne mogu biti jednaki referenci na ćelije, primjerice Z$100 ili R1C1.
• Riječi nemojte razdvajati razmakom U nazivu se ne smiju koristiti razmaci. Kao razdjelnike riječi možete koristiti podvlaku (_) i točku (.). Na primjer, ProdajaOdjela, Sales_Tax ili Prvo.Tromjesečje.
• Koristite najviše 255 znakova Naziv tablice može sadržavati najviše 255 znakova.
• Koristite jedinstvene nazive tablica Duplicirana imena nisu dopuštena. Excel u nazivima ne pravi razliku između velikih i malih slova, pa ako u istoj radnoj knjizi već imate naziv "PRODAJA", od vas će se zatražiti da odaberete jedinstveni naziv.
• Korištenje identifikatora objekta Ako planirate koristiti kombinaciju tablica, zaokretnih tablica i grafikona, preporučujemo da ispred naziva dodate prefiks vrste objekta. Na primjer: tbl_Sales za tablicu prodaje, pt_Sales za zaokretnu tablicu prodaje i chrt_Sales za grafikon prodaje ili ptchrt_Sales za zaokretni grafikon prodaje. Time ćete zadržati sva vaša imena na numeriranom popisu u Upravitelju nazivima.

Pravila sintakse strukturirane reference

Strukturirane reference možete unijeti ili promijeniti i ručno u formuli, ali da biste to učinili, potrebno je razumjeti sintaksu strukturiranih referenci. Pogledajmo sljedeći primjer formule:

=SUM(ProdajaOdjela[[#Zbrojevi],[Iznos prodaje]],ProdajaOdjela[[#Podaci],[Iznos provizije]])

Formula sadrži sljedeće komponente strukturiranih referenci:

• **Naziv tablice:**ProdajaOdjela prilagođeni je naziv tablice. Referira se na podatke iz tablice bez redaka zaglavlja ili zbroja. Možete koristiti zadani naziv tablice, npr. Tablica1, ili ga pak promijeniti i unijeti vlastiti naziv.
• Određivač stupca:[Iznos prodaje] i [Iznos provizije] određivači su stupaca koji koriste nazive stupaca koje predstavljaju. Sadrže referencu na podatke u stupcu, bez retka zaglavlja i zbroja. Specifikatore uvijek omeđite uglatim zagradama kao što je prikazano.
• Specifikator stavke:[#Totals] i [#Data] posebni su specifikatori stavki koji se odnose na određene dijelove tablice, kao što je redak zbroja.
• Specifikator tablice:[[#Zbrojevi],[Iznos prodaje]] i [[#Podaci],[Iznos provizije]] specifikatori su tablice koji predstavljaju vanjske dijelove strukturirane reference. Vanjske reference usklađene su s nazivom tablice, a omeđuju se uglatim zagradama.
• Strukturirana referenca:(ProdajaOdjela[[#Totals],[Iznos prodaje]] i ProdajaOdjela[[#Data],[Iznos provizije]] strukturirane su reference u obliku niza koji započinje nazivom tablice te završava specifikatorom stupca.

Pri stvaranju ili uređivanju strukturiranih referenci koristite se ovim pravilima sintakse:

• Korištenje zagrada oko specifikatora Sve tablice, stupci i specifikatori posebnih stavki moraju biti zatvoreni u uglate zagrade ([]). Specifikator koji sadrži druge specifikator mora sadržavati vanjske uglate zagrade koje zatvaraju unutarnje uglate zagrade drugih specifikatora. Na primjer: =ProdajaOdjela[[Prodavač]:[Regija]]
• Sva zaglavlja stupaca tekstni su nizovi No nije ih potrebno stavljati navodnike kad se koriste u strukturiranoj referenci. I brojevi i datumi, primjerice 2014. ili 1.01.2014., smatraju se tekstnim nizovima. U zaglavljima stupaca ne mogu se koristiti izrazi. Na primjer, izraz ProdajaOdjelaIzvješćeFiskGodine[[2014.]: [2012.]] neće funkcionirati.

Korištenje uglatih zagrada za zaglavlja stupaca koja sadrže posebne znakove Ako postoje posebni znakovi, cijelo zaglavlje stupca mora biti u uglatim zagradama, što znači da su za specifikator stupca potrebne dvostruke uglate zagrade. Na primjer: =ProdajaOdjelaIzvješćeFiskGodine [[ukupni $ iznos]]

Slijedi popis posebnih znakova za koje su potrebne dodatne uglate zagrade u formuli:

• Tabulator
• Polje retka
• Prelazak u novi red
• Zarez (,)
• Dvotočka (:)
• Točka (.)
• lijeva uglata zagrada ([)
• desna uglata zagrada (])
• Znak za ljestve (#)
• Jednostruki navodnik (')
• Dvostruki navodnik (")
• Lijeva vitičasta zagrada ({)
• Desna vitičasta zagrada (})
• znak dolara ($)
• Caret (^)
• ampersand (&)
• Zvjezdica (*)
• Znak plus (+)
• znak jednakosti (=)
• Znak minus (-)
• Znak veće od (>)
• Znak manje od (<)
• Znak dijeljenja (/)
• U znak (@)
• Obrnuta kosa crta (\)
• uskličnik (!)
• lijeva zagrada ()
• desna zagrada ())
• Znak postotka (%)
• Upitnik (?)
• Backtick (')
• točka sa zarezom (;)
• Tilda (~)
• Podvlaka (_)

• Korištenje prespojnog znaka za neke posebne znakove u zaglavljima stupaca Neki znakovi imaju posebno značenje i uz njih je kao prespojni znak potrebno koristiti jednostruki navodnik ('). Na primjer: =ProdajaOdjelaIzvješćeFiskGodine['#Stavki]

Slijedi popis posebnih znakova za koje je potreban prespojni znak (') u formuli:

• lijeva uglata zagrada ([)
• desna uglata zagrada (])
• Znak za ljestve (#)
• Jednostruki navodnik (')
• U znak (@)

Korištenje znaka razmaka za poboljšanje čitljivosti u strukturiranoj referenci Pomoću znakova razmaka možete strukturiranu referencu učiniti čitljivijom. Na primjer: =ProdajaOdjela[ [Prodavač]:[Regija] ] ili =ProdajaOdjela[[#Zaglavlja], [#Podaci], [% provizije]]

Preporučuje se koristiti jedan razmak:

• Nakon prve lijeve uglate zagrade ([)
• Prije posljednje desne uglate zagrade (]).
• Nakon zareza.

Operatori reference

Radi dodatne fleksibilnosti pri navođenju raspona ćelija možete koristiti sljedeće operatore referenci da biste spojili specifikatore stupca.

Strukturirana referenca	Elementi na koje se referencira	Operator	Raspon ćelija:
=ProdajaOdjela[[Prodavač]:[Regija]]	Sve ćelije u dva ili više susjednih stupaca	: (dvotočka) operatora raspona	A2:B7
=ProdajaOdjela[Iznos prodaje],ProdajaOdjela [Iznos provizije]	Kombinacija dva ili više stupaca	, (zarez) operatora spajanja	C2:C7, E2:E7
=ProdajaOdjela[[Prodavač]:[Iznos prodaje]] ProdajaOdjela[[Regija]:[% provizije]]	Presjek dva ili više stupaca	(razmak) operatora presjeka	B2:C7

Specifikatori posebnih stavki

Da biste se pozvali na određene dijelove tablice, primjerice samo redak zbroja, u strukturiranim referencama možete koristiti bilo koji od sljedećih specifikatora posebnih stavki.

Određivač posebne stavke	Elementi na koje se referencira
#Sve	Cijela tablica, uključujući zaglavlja stupaca, podatke i zbrojeve (ako ih ima).
#Podaci	Samo reci s podacima.
#Zaglavlja	Samo redak zaglavlja.
#Zbrojevi	Samo redak zbroja. Ako ga nema, vraća vrijednost null.
#Ovaj redak ili @ ili @[Naziv stupca]	Samo ćelije koje se nalaze u istom retku kao i formula. Ti se specifikatori ne mogu kombinirati s drugim specifikatorima posebnih stavki. Koristite ih da biste nametnuli implicitni presjek za referencu ili da biste nadjačali implicitni presjek i pozvali pojedinačne vrijednosti iz stupca. U tablicama koje sadrže više redaka podataka Excel automatski mijenja specifikatore #Ovaj redak u kraći oblik sa znakom @. No ako tablica sadrži samo jedan redak, Excel neće zamijeniti specifikator #This redak, što može izazvati neočekivane izračune ako dodate još redaka. Da biste izbjegli probleme s izračunima, u tablicu unesite više redaka prije unošenja formula sa strukturiranim referencama.

Kvalificiranje strukturiranih referenci u izračunatim stupcima

Prilikom stvaranja izračunatog stupca formula se često stvara pomoću strukturirane reference. Ta strukturirana referenca može biti nekvalificirana ili u potpunosti kvalificirana. Da biste, primjerice, stvorili izračunati stupac Iznos provizije koji izračunava iznos provizije u dolarima, možete koristiti sljedeće formule:

Vrsta strukturirane reference	Primjer	Komentar
Nekvalificirana	=[Iznos prodaje]*[% provizije]	Množi odgovarajuće vrijednosti iz trenutnog retka.
U potpunosti kvalificirana	=ProdajaOdjela[Iznos prodaje]*ProdajaOdjela [% provizije]	Množi odgovarajuće vrijednosti za svaki redak i oba stupca.

Općenito vrijedi sljedeće pravilo: ako u tablici koristite strukturirane reference, primjerice prilikom stvaranja izračunatog stupca, možete koristiti nekvalificiranu strukturiranu referencu, ali ako ih koristite izvan tablice, morate koristiti u potpunosti kvalificiranu strukturiranu referencu.

Primjeri korištenja strukturiranih referenci

Evo nekoliko mogućih načina primjene strukturiranih referenci.

Strukturirana referenca	Elementi na koje se referencira	A to je raspon ćelija:
=ProdajaOdjela[[#Sve],[Iznos prodaje]]	Sve ćelije u stupcu Iznos prodaje.	C1:C8
=ProdajaOdjela[[#Zaglavlja],[% Provizije]]	Zaglavlje stupca % provizije.	D1
=ProdajaOdjela[[#Zbrojevi],[Regija]]	Zbroj stupca Regija. Ne postoji li redak Zbrojevi, vraća nulu.	B8
=ProdajaOdjela[[#Sve],[Iznos prodaje]:[% provizije]]	Sve ćelije iz stupaca Iznos prodaje i % provizije.	C1:D8
=ProdajaOdjela[[#Podaci],[% provizije]:[Iznos provizije]]	Samo podaci iz stupaca % provizije i Iznos provizije.	D2:E7
=ProdajaOdjela[[#Zaglavlja],[Regija]:[Iznos provizije]]	Samo zaglavlja stupaca između stupca Regija i Iznos provizije.	B1:E1
=ProdajaOdjela[[#Zbrojevi],[Iznos prodaje]:[Iznos provizije]]	Zbrojeve stupaca Iznos prodaje do Iznos provizije. Ako nema retka Zbrojevi, vraća se vrijednost null.	C8:E8
=ProdajaOdjela[[#Zaglavlja],[#Podaci],[% Provizije]]	Samo zaglavlje i podaci stupca % provizije.	D1:D7
=ProdajaOdjela[[#Ovaj redak], [Iznos provizije]] ili =ProdajaOdjela[@Iznos provizije]	Ćelija na sjecištu trenutnog retka i stupca Iznos provizije. Ako se koristi u istom retku u kojem se nalazi zaglavlje ili redak zbrojeva, vratit će pogrešku #VALUE! . Ako u tablicu s više redaka s podacima unesete dulji oblik ove strukturirane reference (#Ovaj redak), Excel će ga automatski zamijeniti kraćim oblikom (@). Oba oblika funkcioniraju na isti način.	E5 (ako je trenutni redak 5)

Strategije rada sa strukturiranim referencama

Prilikom rada sa strukturiranim referencama imajte u vidu sljedeće:

• Korištenje značajke samodovršetka formule Značajka samodovršetka formule vrlo je praktična za unos strukturiranih referenci te omogućivanje korištenja pravilne sintakse. Dodatne informacije potražite u članku Samodovršavanje formule.

• Odluka o stvaranju strukturiranih referenci za djelomice odabrane tablice Po zadanom, kada stvorite formulu, odabir raspona ćelija unutar tablice polovično će odabrati ćelije i automatski ući u strukturiranu referencu umjesto u raspon ćelija u formuli. To ponašanje poluodabira znatno olakšava ulazak u strukturiranu referencu. Možete ga uključiti ili isključiti potvrđivanjem ili poništavanjem potvrdnog okvira Koristi nazive tablica u formulama u dijaloškom okviruMogućnosti>datoteke>Formule Rad>s formulama.

• Korištenje radnih knjiga s vanjskim vezama na tablice programa Excel u drugim radnim knjigama Ako radna knjiga sadrži vanjsku vezu na tablicu programa Excel u drugoj radnoj knjizi, povezana izvorišna radna knjiga mora biti otvorena u programu Excel da se u odredišnoj radnoj knjizi koja sadrži veze ne bi pojavile pogreške #REF! . Ako najprije otvorite odredišnu radnu knjigu i pojave se pogreške #REF! , razriješit će se ako potom otvorite izvorišnu radnu knjigu. Ako najprije otvorite izvorišnu radnu knjigu, pogreške se ne bi smjele pojaviti.

• Pretvaranje raspona u tablicu i tablice u raspon Ako pretvorite tablicu u raspon, sve reference ćelija promijenit će se u odgovarajuće apsolutne reference stila A1. Ako pretvorite raspon u tablicu, Excel ne mijenja automatski reference na ćelije tog raspona u njihove ekvivalentne strukturirane reference.

• Isključivanje zaglavlja stupaca Zaglavlja stupaca tablice možete uključiti i isključiti s retka zaglavlja kartice >Dizajn tablice. Ako isključite zaglavlja stupaca tablice, to neće utjecati na strukturirane reference koje koriste nazive stupaca te ih i dalje možete koristiti u formulama. Strukturirane reference koje se pozivaju izravno na zaglavlja tablice (npr. =ProdajaOdjela[[#Headers],[%Komisija]]) rezultirat će #REF.

• Dodavanje stupaca i redaka u tablicu i njihovo brisanje iz tablice Budući da se rasponi podataka tablice često mijenjaju, reference ćelija za strukturirane reference prilagođavaju se automatski. Ako, primjerice, u formuli koja prebrojava sve podatkovne ćelije u tablici koristite naziv tablice, a potom dodate redak podataka, referenca ćelije automatski se prilagođava.

• Promjena naziva tablice ili stupca Promijenite li naziv stupca ili tablice, Excel automatski mijenja korištenje tog zaglavlja tablice ili stupca u svim strukturiranim referencama koje se koriste u radnoj knjizi.

• Premještanje, kopiranje i popunjavanje strukturiranih referenci Sve strukturirane reference ostaju iste prilikom kopiranja ili premještanja formule u kojoj se koristi strukturirana referenca.

  Napomena

  Kopiranje strukturirane reference i ispunjavanje strukturirane reference nije isto što i to. Kada kopirate, sve strukturirane reference ostaju iste, a kada ispunite formulu, u potpunosti kvalificirane strukturirane reference prilagođavaju specifikatore stupaca kao niz, što je prikazano u sljedećoj tablici.

Smjer ispunjavanja	Tipka koju je potrebno pritisnuti tijekom ispunjavanja	Rezultat
Gore ili dolje	Ništa	Nema poravnanja određivača stupaca.
Gore ili dolje	Ctrl	Određivači stupca poravnavaju se kao niz.
Desno ili lijevo	Ništa	Određivači stupca poravnavaju se kao niz.
Gore, dolje, desno ili lijevo	Shift	Umjesto prebrisivanja vrijednosti u trenutnim ćelijama premještaju se trenutne vrijednosti ćelija i umeću se određivači stupca.

Je li vam potrebna dodatna pomoć?

Uvijek možete postaviti pitanje stručnjaku u tehničkoj zajednici za Excel ili zatražiti podršku u zajednicama.

Povezane teme

Pregled tablica programa Excel
Stvaranje i oblikovanje tablica
Zbrajanje podataka u tablici programa Excel
Oblikovanje tablice programa Excel
Promjena veličine tablice dodavanjem ili uklanjanjem redaka i stupaca
Filtriranje podataka u rasponu ili tablici
Pretvaranje tablice u raspon
Problemi vezani uz kompatibilnost tablica u programu Excel
Izvoz tablice programa Excel u SharePoint
Pregledi formula u programu Excel