Korištenje strukturiranih referenci s tablicama programa Excel

Š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 oglednim radnim listovima kliknite ć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 > alati za tablice prikazali kartica Dizajn.

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

• Nemojte razdvojiti riječi razmakom  U nazivu se ne mogu koristiti razmaci. Možete koristiti znak podvlake (_) i točku (.) kao razdjelnici riječi. Na primjer, ProdajaOdjela, Sales_Tax ili Prvo.Tromjesečje.

• 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 u istu radnu knjigu unesete "Prodaja", ali već imate drugi naziv pod nazivom "PRODAJA", 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 prefiks naziva prefiksom uvrstiti objekt. Na primjer: tbl_Sales za prodajnu tablicu, pt_Sales zaokretnu tablicu prodaje, a chrt_Sales za prodajni grafikon ili ptchrt_Sales za zaokretni grafikon prodaje. Time se sva imena čuvaju na numeriranom popisu u upravitelju naziva.

Pravila sintakse strukturirane reference

Strukturirane reference možete unijeti ili promijeniti ručno u formuli, no da biste to mogli učiniti, lakše ćete 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 je prilagođeni naziv tablice. Referencira podatke tablice bez redaka zaglavlja ili ukupnog zbroja. Možete koristiti zadani naziv tablice, kao što je Tablica1, ili ga promijeniti tako da koristi 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.

• Specificator stavki:    [#Totals] i [#Data] posebni su određuječi 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, predstavljene nizom koji počinje nazivom tablice i završava s fikatorom 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 1. 2014. ili 1. 2014., smatraju se i tekstnim nizovima. Ne možete koristiti izraze sa zaglavljima stupaca. Na primjer, izraz ProdajaOdjelaFYSummary[[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

• Povratak u kočiju

• Zarez (,)

• Dvotočka (:)

• Točka (.)

• Lijeva uglata zagrada ([)

• Desna uglata zagrada (])

• Znak za funtu (#)

• Jednostruki navodnik (')

• Dvostruki navodnik (")

• Lijeva vitičasta zagrada ({)

• Desna vitičasta zagrada (})

• Znak dolara ($)

• Karet (^)

• Ampersand (&)

• Zvjezdica (*)

• Znak plus (+)

• Znak jednakosti (=)

• Znak minus (-)

• Simbol veće od (>)

• Manje od simbola (<)

• Znak dijeljenja (/)

• Na znak (@)

• Obrnuta kosa crta (\)

• Uskličnik (!)

• Lijeva zagrada (()

• Desna zagrada ())

• Znak postotka (%)

• Upitnik (?)

• Backtick (')

• Točka-zarez (;)

• 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]

Evo popisa posebnih znakova za koje je u formuli potreban escape znak ('):

• Lijeva uglata zagrada ([)

• Desna uglata zagrada (])

• Znak za funtu(#)

• Jednostruki navodnik (')

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

• Ispred 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.

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.

Kvalificiranje strukturiranih referenci u izračunatim stupcima

Prilikom stvaranja izračunatog stupca formula se često koristi strukturiranom referencom. 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:

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.

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čite hoće li se generirati strukturirane reference za tablice u poluo odabirima    Kada stvorite formulu, klikom na raspon ćelija unutar tablice po zadanom poluo odabire ć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 potvrdni 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 tablice programa 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 bi se izbjegle #REF! pogreške u odredišnoj radnoj knjizi koja sadrži veze. Ako najprije otvorite odredišnu radnu knjigu i #REF! pogreške, one će se razriješiti ako otvorite izvorišnu radnu knjigu. Ako prvo 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 odgovarajuće apsolutne reference stila A1. Kada raspon pretvorite u tablicu, Excel neće automatski promijeniti reference ćelija tog raspona u odgovarajuće strukturirane reference.

• Isključivanje zaglavlja stupaca    Zaglavlja stupaca tablice možete uključiti i isključiti na kartici Dizajn tablice > redak zaglavlja. Ako isključite zaglavlja stupaca tablice, to neće utjecati na strukturirane reference koje koriste nazive stupaca, a i dalje ih možete koristiti u formulama. Strukturirane reference koje se odnose izravno na zaglavlja tablica (npr. =ProdajaOdjela[[#Headers],[%Provizija]]) 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 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.

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
Videozapis: Stvaranje i oblikovanje tablice programa Excel
Ukupan zbroj 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 s kompatibilnošću tablice programa
ExcelIzvoz tablice programa Excel u SharePoint
Pregled formula u programu Excel