Korištenje strukturiranih referenci s tablicama programa Excel

Kada stvorite tablicu Excel tablicu,Excel tablici dodjeljuje naziv i svakom zaglavlju stupca u tablici. 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ć kliknite ćelije tablice na koje želite umetnuti reference. Pomoću sljedećih oglednih podataka unesite formulu 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 u gornjoj tablici, uključujući zaglavlja stupaca, a zatim ih zalijepite u ćeliju A1 novog Excel radnog lista.

  2. Da biste stvorili tablicu, odaberite bilo koju ćeliju unutar raspona podataka pa pritisnite Ctrl + T.

  3. Provjerite je li potvrđen okvir Moja tablica ima zaglavlja, a zatim kliknite U redu.

  4. U ćeliju E2 upišite znak jednakosti (=), a zatim kliknite ćeliju C2.

    Strukturirana referenca [@[Iznos prodaje]] prikazuje se u traci formule iza znaka jednakosti.

  5. Upišite zvjezdicu (*) izravno nakon završne zagrade, a zatim kliknite ć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. Na ogledni radni list kliknite ćeliju E2

  2. Na traci 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 biste na vrpci > alati za tablice.

  2. U okvir Naziv tablice upišite naziv koji želite, 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.

  • Ne koristite razmak da biste odvojili riječi  Razmake nije moguće koristiti u nazivu. Možete koristiti znak podvlake (_) i točka (.) kao razdjelnici riječi. Na primjer, DeptSales, Sales_Tax ili First.Quarter.

  • Koristite najviše 255 znakova Naziv tablice može sadržavati najviše 255 znakova.

  • Korištenje jedinstvenih naziva tablica Duplicirani nazivi nisu dopušteni. Excel ne razlikuje velika i mala slova u nazivima pa ako unesete "Prodaja", ali već imate drugi naziv naziva "PRODAJA" u istoj radnoj knjizi, od vas će se zatražiti da odaberete jedinstveni naziv.

  • Korištenje identifikatora objekta  Ako planirate imati kombinaciju tablica, zaokretnih tablica i grafikona, dobro je prefiksati imena vrstom objekta. Na primjer: tbl_Sales za prodajnu tablicu, pt_Sales za prodajnu zaokretnu tablicu i chrt_Sales za prodajni grafikon ili ptchrt_Sales za prodajnu zaokretni grafikon. Na taj se popis čuva sva vaša imena u upravitelju naziva.

Pravila sintakse strukturirane reference

Strukturirane reference možete unijeti i ručno u formulu, ali to će vam pomoći da razumijete 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:   DeptSales je prilagođeni naziv tablice. Upućuje na podatke tablice bez redaka zaglavlja ili zbroja. Možete koristiti zadani naziv tablice, kao što je Tablica1 ili ga promijeniti da biste koristili prilagođeni naziv.

  • Određivanje stupca:   [Iznos prodaje] i [Iznos provizije] određuju stupce 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.

  • Alat za određivanje stavke:   [#Totals]i [#Data] posebni su specificisti 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 ProdajaProdaja[[#Data],[Iznos provizije]] strukturirane su reference, predstavljene nizom koji počinje nazivom tablice i završava s specificerom 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 ne zahtijevaju navodnike kada se koriste u strukturiranoj referenci. Brojevi ili datumi, kao što su 2014. ili 1. 1. 2014., smatraju se i tekstnim nizovima. Ne možete koristiti izraze sa zaglavljima stupaca. Na primjer, izraz DeptSalesFYSummary[[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

  • Linijski sažetak sadržaja

  • Vraćanje prijevoza

  • Zarez (,)

  • Dvotočka (:)

  • Točka (.)

  • Lijeva zagrada ([)

  • Desna zagrada (])

  • Znak funta (#)

  • Jednostruki navodnici (')

  • Dvostruki navodnici (")

  • Lijeva zagrada ({)

  • Desna zagrada (})

  • Znak dolara ($)

  • Caret (^)

  • Ampersand (&)

  • Zvjezdica (*)

  • Znak plus (+)

  • Znak jednakosti (=)

  • Znak minus (-)

  • Simbol veće od (>)

  • Manje od simbola (<)

  • Znak dijeljenja (/)

  • 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 zagrada ([)

  • Desna zagrada (])

  • Znak za funtu(#)

  • Jednostruki navodnici (')

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 zagrade ([)

  • Ispred zadnje desnoj zagradi (]).

  • Nakon zarez.

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 #Ovaj 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

Kada stvorite izračunati stupac, za stvaranje formule često koristite strukturiranu referencu. Ta strukturirana referenca može biti nekvalificirana ili u potpunosti kvalificirana. Da biste, primjerice, stvorili izračunati stupac, pod nazivom 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

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 kao redak zaglavlja ili zbroja, to će vratiti 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 razmislite o sljedećem.

  • 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 Korištenje samodovršetka formule.

  • Odlučivanje o tome hoće li se generirati strukturirane reference za tablice u poluo odabirima    Po zadanom, kada stvorite formulu, klikom na raspon ćelija unutar tablice poluo odabire se ćelije i automatski unosi strukturiranu referencu umjesto raspona ćelija u formuli. To ponašanje poluodabira znatno olakšava ulazak u strukturiranu referencu. To ponašanje možete uključiti ili isključiti tako da potvrdite ili poništite okvir Koristi nazive tablica u formulama u dijaloškom okviru Mogućnosti > datoteka > formule > Rad s formulama.

  • Korištenje radnih knjiga s vanjskim vezama na Excel u drugim radnim knjigama    Ako radna knjiga sadrži vanjsku vezu na tablicu programa Excel u drugoj radnoj knjizi, ta povezana izvorišna radna knjiga mora biti otvorena u programu Excel da biste izbjegli #REF! pogreške u odredišnoj radnoj knjizi koja sadrži veze. Ako najprije otvorite odredišnu radnu knjigu i #REF! pojavit će se pogreške, one će se razriješiti ako otvorite izvorišnu radnu knjigu. Ako najprije otvorite izvorišnu radnu knjigu, ne biste trebali vidjeti kodove pogrešaka.

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

  • Isključivanje zaglavlja stupaca    Zaglavlja stupaca tablice možete i isključiti na kartici Dizajn tablice > redak zaglavlja. Ako isključite zaglavlja stupaca tablice, to ne utječe na strukturirane reference koje koriste nazive stupaca i svejedno ih možete koristiti u formulama. Strukturirane reference koje se odnose izravno na zaglavlja tablice (npr. =DeptSales[[#Headers],[%Commission]]) rezultirat će #REF.

  • Dodavanje stupaca i redaka u tablicu i njihovo brisanje iz tablice    Budući da se rasponi podataka u tablici često mijenjaju, reference ćelija za strukturirane reference automatski se prilagođavaju. 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. Kada kopirate, sve strukturirane reference ostaju iste, a kada ispunite formulu, potpuno kvalificirane strukturirane reference prilagođavaju specificatore stupaca kao niz kao što je sažeto u sljedećoj tablici.

Smjer ispunjavanja

Tijekom ispunjavanja pritisnite sljedeće:

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ć?

Postavite pitanje stručnjaku u tehničkoj zajednici za Excel, zatražite podršku u zajednici za odgovore ili predložite novu značajku ili poboljšanje na forumu za Excel User Voice.

Povezane teme

Pregled Excel tablica
Videozapis: Stvaranje i oblikovanje Excel tablice
Ukupan zbroj podataka u tablici Excel tablice
Oblikovanje Excel tablice
Promjena veličine tablice dodavanjem ili uklanjanjem redaka i stupaca
Filtriranje podataka u rasponu ili tablici
Pretvaranje tablice u raspon
Excel problema s kompatibilnošću tablice
Izvoz tablice Excel u SharePoint
Pregled formula u programu Excel

Potrebna vam je dodatna pomoć?

Proširite svoje vještine korištenja sustava Office
Istražite osposobljavanje

Jesu li vam ove informacije bile korisne?

×