Korištenje strukturiranih referenci s tablicama programa Excel

Važno :  Ovaj je članak strojno preveden. Pogledajteizjavu o odricanju od odgovornosti. Verziju ovog članka na engleskom potražite ovdje.

Kada stvorite tablicu programa Excel, Excel dodjeljuje naziv tablice, a za svako zaglavlje stupca u tablici. Kada dodate formule u tablicu programa Excel, nazive možete automatski se prikazuju kao unesite formulu te odaberite reference ćelija u tablici umjesto ručnog unosa. Evo primjera funkcija programa Excel:

Umjesto korištenja eksplicitnih referenci ćelija

Excel koristi tablicu i nazive stupaca

=Sum(C2:C7)

=SUM(ProdajaOdjela[iznos prodaje])

Kombinacija naziva tablice i stupaca naziva se strukturiranom referencom. Nazivi u strukturiranim referencama prilagođavaju se svaki put kada u tablicu dodatke podatke ili ih iz nje uklonite.

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. U sljedećem primjeru unijet ćemo formulu koja automatski koristi strukturirane reference za izračun iznosa provizije za prodaju.

Prodaja Osobe

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 kliknite u redu.

  4. Kliknite ćeliju E2, unesite znak jednakosti (=) pa kliknite ćeliju C2.

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

  5. Upišite zvjezdicu (*) odmah 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. U oglednom radnom listu 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 prikazali Alati za tablice > Dizajn na vrpci .

  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 počinju slovima, podvlakom (_) ili obrnutu kosu crtu (\). Korištenje slova, brojeve, točke, a podvučene znakove do kraja naziv. Ne možete koristiti "C", "c", "R" ili "r" naziv, jer su se već označen kao prečac za odabir stupca ili retka aktivnu ćeliju kada ih unosite u okvir naziv ili Idi na .

  • Nemojte koristiti reference ćelije  Nazivi ne mogu biti jednaki referenci ćelije, kao što su Z$ 100 ili R1C1.

  • Nemojte koristiti razmak za razdvajanje riječi  Razmaci se ne mogu se koristiti u nazivu. Možete koristiti znak podvlake (_) i razdoblje (.) razdjelnike riječi. Na primjer, ProdajaOdjela, prvi.kvartal ili Porez_na_prodaju.

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

  • Koristite jedinstvene nazive tablica Nije dopušteno korištenje već postojećih naziva. Excel ne razlikuje velika i mala slova u nazivima, pa ako unesete "Prodaja", a u istoj radnoj knjizi već postoji naziv tablice "PRODAJA", od vas će se tražiti da odaberete jedinstveni naziv.

  • Korištenje identifikator objekta  Ako planirate na pojavljuju kombinacije tablice, zaokretne tablice i grafikone, je dobro prefiks naziva vrsti objekta. Na primjer: tbl_Sales u tablici prodaje, pt_Sales za prodaju zaokretne tablice i chrt_Sales za prodaju grafikon ili ptchrt_Sales za prodaju zaokretni grafikon. To bez promjene zadržava naziva u numerirani popis u Upravitelj naziva.

Pravila sintakse strukturirane reference

Možete unijeti ili promjena strukturiranih referenci ručno u formuli, ali da biste to učinili, to će pomoći da biste shvatili sintakse strukturirane reference. Prođimo kroz u sljedećem primjeru formula:

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

Formula sadrži sljedeće komponente strukturiranih referenci:

  • Naziv tablice:   ProdajaOdjela je naziv prilagođene tablice. Odnosi se podatke u tablici, bez bilo koji redak zaglavlja i ukupni zbroj. Možete koristiti zadani naziv tablice, kao što su Tablica1, ili promijeniti da biste koristili prilagođeni naziv.

  • Specifikator stupca:   [Iznos prodaje]i[Iznos provizije] se Određivači stupca koji koriste nazivi stupaca predstavljaju. Koje se odnose ćete podatke iz stupca, bez bilo koji stupac zaglavlja ili zbroja retka. Uvijek stavite Određivači u 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, stupca i Određivači posebnih stavki moraju biti zatvoreni u odgovarajuće zagrade ([]). Određivač koji sadrži druge određivače zahtjeva jednake vanjske zagrade da prije i nakon zagrade drugih određivača. Na primjer: = ProdajaOdjela [[Prodavač]: [Regija]]

  • Sva zaglavlja stupaca tekstni su nizovi    No ne trebaju ponude kada se koriste u strukturiranoj referenci. Brojeve ili datume, kao što su 2014 ili 1/1/2014., također se smatraju tekstnih nizova. Ne možete koristiti izraze sa zaglavljima stupaca. 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 funtu (#)

  • jednostruki navodnik (')

  • dvostruki navodnik (")

  • lijeva vitičasta zagrada ({)

  • desna vitičasta zagrada (})

  • znak za dolar ($)

  • karet (^)

  • ampersand (&)

  • zvjezdica (*)

  • znak plus (+)

  • znak jednakosti (=)

  • znak minus (-)

  • znak veće od (>)

  • znak manje od (<)

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

  • desna uglata zagrada (])

  • znak za funtu (#)

  • jednostruki navodnik (')

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

Prilikom stvaranja izračunatog stupca formula se često stvara pomoću strukturirane reference, koja 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

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 presjeku trenutnog retka i stupca iznos provizije. Ako se koristi u isti redak kao zaglavlje ili redak s ukupnim zbrojem, to će vratiti na #VALUE! pogreške.

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 razmotrite sljedeće.

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

  • Odluka o stvaranju strukturiranih referenci za tablice u točkom sa odabira    Po zadanome, prilikom stvaranja formule, pritisnite ćeliju unutar tablice odabire za točka u rasponu ćelija i automatski unosi strukturirane reference umjesto raspon ćelija u formuli. Takvo ponašanje djelomice odabrane olakšava mnogo unesite strukturirane reference. Takvo ponašanje isključivanje možete pretvoriti tako da odaberete ili poništite potvrdni okvir koristi nazive tablica u formulama u datoteku > Mogućnosti > formule > Rad s formulama dijaloški okvir.

  • Korištenje radnih knjiga s vanjskim vezama na tablice programa Excel u drugim radnim knjigama    Sadrži li radna knjiga vanjske veze u tablicu programa Excel u drugoj radnoj knjizi, taj povezane izvorišna radna knjiga mora biti otvorena u programu Excel da biste izbjegli #REF! pogrešaka u odredišnu radnu knjigu koja sadrži veze. Ako prvi put otvorite odredišnu radnu knjigu i #REF! pojavljuju pogreške, oni će biti riješen ako pa otvorite izvorišnu radnu knjigu. Ako prvi put otvorite izvorišnu radnu knjigu, trebali biste vidjeti bez šifre pogrešaka.

  • Pretvaranje raspona u tablicu i tablice u raspon    Kada pretvorite tablicu u raspon, sve reference ćelija će se promijeniti njihove apsolutne reference stila A1. Kada pretvaranje raspona u tablicu, Excel automatski ne promijenite reference ćelija tog raspona u ekvivalentne strukturiranih referenci.

  • Isključivanje zaglavlja stupca    Uključivanje i isključivanje zaglavlja stupaca tablice uključiti i isključiti na kartici Dizajn tablice > Redak zaglavlja. Ako isključite zaglavlja stupaca tablice ne utječe na strukturiranim referencama koje se koriste nazivi stupaca pa ih i dalje možete koristiti u formulama. Strukturiranim referencama koje se izravno pozivaju zaglavlja tablice (npr. = ProdajaOdjela [[#Headers], [% provizije]]) rezultirat će #REF.

  • Dodavanje ili brisanje stupaca i redaka iz tablice    Budući da se rasponi podataka tablice često mijenjaju, reference ćelija za strukturirane reference prilagođavaju se automatski. Na primjer, ako koristite naziv tablice u formuli za brojanje sve ćelije s podacima u tablici, a zatim dodajte redak podataka, referencu na ćeliju automatski 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 kad kopirate ili premjestite formulu koja koristi strukturirane reference.

    Napomena : Kopiranje strukturirane reference i način unosa strukturirane reference nisu isto. Kada kopirate, strukturirane reference ostaju iste, tijekom kad ispunite formulu, u potpunosti kvalificirana strukturirana referenca određivače stupca 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

Pomak

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 tablica programa Excel
videozapis: Stvaranje i oblikovanje tablice programa Excel
Zbrajanje podataka u tablici programa Excel
Oblikovanje tablice programa Excel
Promjena veličine tablice dodavanjem ili uklanjanjem retke i stupce
Filtriranje podataka u rasponu ili tablici
Pretvaranje tablice u raspon
Problemi kompatibilnosti tablice programa Excel
Izvoz tablice programa Excel u sustavu SharePoint
Pregled formula u Excel

Napomena : Izjava o odricanju od odgovornosti za strojni prijevod: ovaj je članak preveo računalni sustav bez ljudske intervencije. Microsoft nudi strojne prijevode da bi korisnicima koji ne razumiju engleski omogućio čitanje sadržaja o Microsoftovim proizvodima, uslugama i tehnologijama. Budući da je preveden strojno, članak možda sadrži pogreške u vokabularu, sintaksi ili gramatici.

Proširite svoje vještine
Istražite osposobljavanje

Jesu li vam ove informacije bile korisne?

Hvala vam na povratnim informacijama!

Hvala vam na povratnim informacijama! Čini se da bi vam pomoglo kad bismo vas povezali s nekim od naših agenata podrške za Office.

×