Kada kreirate Excel tabelu, Excel dodeljuje ime toj tabeli, kao i svakom zaglavlju kolone u tabeli. Kada dodajete formule u Excel tabelu, ta imena se mogu automatski prikazati dok unosite formulu i birate reference ćelija u tabeli umesto da ih ručno unosite. Evo šta Excel radi:
| Umesto korišćenja eksplicitnih referenci ćelija | Excel koristi imena tabele i kolona |
|---|---|
| =Sum(C2:C7) | =SUM(ProdajaSekt[Iznos prodaje]) |
Ta kombinacija imena tabela i kolona naziva se strukturirana referenca. Imena u strukturiranim referencama prilagođavaju se svakom dodavanju ili uklanjanju podataka iz tabele.
Strukturirane reference se takođe pojavljuju kada izvan Excel tabele kreirate formulu koja referencira podatke iz tabele. Reference mogu da olakšaju lociranje tabela u velikoj radnoj svesci.
Da biste u formulu uključili strukturirane reference, izaberite ćelije tabele na koje želite da ukažete umesto da otkucate njihove reference ćelija u formulu. Hajde da iskoristimo sledeći primer podataka da biste uneli formulu koja automatski koristi strukturirane reference za izračunavanje iznosa provizije prodaje.
| Prodavac | Region | Iznos prodaje | % provizije | Iznos provizije |
|---|---|---|---|---|
| Jovan | Sever | 260 | 10% | |
| Robert | Jug | 660 | 15% | |
| Milica | Istok | 940 | 15% | |
| Emir | Zapad | 410 | 12% | |
| Danica | Sever | 800 | 15% | |
| Rade | Jug | 900 | 15% |
- Kopirajte uzorak podataka iz gorenavedene tabele, uključujući naslove kolona, i nalepite ga u ćeliju A1 novog Excel radnog lista.
- Da biste kreirali tabelu, izaberite bilo koju ćeliju u opsegu podataka i pritisnite kombinaciju tastera Ctrl+T.
- Uverite se da je potvrđen izbor u polju za potvrdu " Moja tabela ima zaglavlja " i kliknite na dugme "U redu".
- U ćeliji E2 otkucajte znak jednakosti (=) i izaberite ćeliju C2.
U traci formula posle znaka jednakosti se pojavljuje strukturirana referenca [@[Iznos prodaje]]. - Otkucajte zvezdicu (*) odmah posle zatvorene uglaste zagrade i izaberite ćeliju D2.
U traci formula posle zvezdice se pojavljuje strukturirana referenca [@[% provizije]]. - Pritisnite Enter.
Excel automatski kreira izračunatu kolonu i kopira formulu kroz čitavu kolonu za vas, uz njeno prilagođavanje za svaki red.
Šta se dešava ako koristim eksplicitne reference ćelija?
Ako unosite eksplicitne reference ćelija u izračunatu kolonu, teže ćete videti šta formula izračunava.
- U uzorku radnog lista izaberite ćeliju E2
- U polje za formulu unesite =C2*D2 i pritisnite taster Enter.
Primetićete da Excel ne koristi strukturirane reference dok kopira formulu kroz kolonu. Ako, na primer, dodate kolonu između postojećih kolona C i D, moraćete da korigujete formulu.
Kako mogu da promenim ime tabele?
Kad kreirate Excel tabelu, Excel kreira podrazumevano ime tabele (Tabela1, Tabela2 i tako dalje), ali možete da ga promenite kako bi imalo više smisla.
- Izaberite bilo koju ćeliju u tabeli da biste prikazali karticu " Dizajn tabele " na traci.
- Otkucajte željeno ime u polju " Ime tabele " i pritisnite taster Enter.
U primerima podataka koristili smo ime ProdajaSekt.
Koristite sledeća pravila za imena tabela:
- Koristite važeće znakove Ime uvek započnite slovom, podvlakom (_) ili obrnutom kosom crtom\(). Za ostatak imena koristite slova, brojeve, tačke i podvlake. Ne možete da koristite slova "C", "c", "R" ili "r" za ime zato što su već označeni kao prečica za izbor kolone ili reda za aktivnu ćeliju kada ih unesete u polje " Ime " ili "Idi na ".
- Nemojte koristiti reference ćelija Imena ne mogu biti ista kao referenca ćelije, npr. Z$100 ili R1C1.
- Nemojte koristiti razmak za razdvajanje reči U imenu nije moguće koristiti razmake. Možete da koristite podvlaku (_) i tačku (.) kao znakove za razdvajanje reči. Na primer, ProdajaSekt, Sales_Tax ili Prvi.kvartal.
- Nemojte koristiti više od 255 znakova Ime tabele može da ima najviše 255 znakova.
- Korišćenje jedinstvenih imena tabela Duplirana imena nisu dozvoljena. Excel ne razlikuje velika i mala slova u imenima, tako da ako unesete "Prodaja", ali već imate drugo ime koje se zove "PRODAJA" u istoj radnoj svesci, bićete upitani da odaberete jedinstveno ime.
- Korišćenje identifikatora objekta Ako planirate da sadrži kombinaciju tabela, izvedenih tabela i grafikona, preporučuje se da dodate prefiks tipu objekta ispred imena. Na primer: tbl_Sales za tabelu prodaje, pt_Sales za izvedenu tabelu prodaje i chrt_Sales za grafikon prodaje ili ptchrt_Sales za izvedeni grafikon prodaje. Na ovaj način će se sva vaša imena zadržati na uređenoj listi u Menadžeru Imena.
Sintaksna pravila strukturiranih referenci
Strukturirane reference možete da unesete ili promenite i ručno u formuli, ali da biste to uradili, koristiće vam da razumete sintaksu strukturiranih referenci. Prođimo kroz sledeći primer formule:
=SUM(ProdajaSekt[[#Ukupne vrednosti],[Iznos prodaje]],ProdajaSekt[[#Podaci],[Iznos provizije]])
Ova formula ima sledeće komponente strukturiranih referenci:
- **Ime tabele:**ProdajaSekt" je prilagođeno ime tabele. Ono upućuje na podatke tabele, bez zaglavlja ili reda sa ukupnim vrednostima. Možete da koristite podrazumevano ime tabele, kao što je Tabela1 ili da ga promenite tako da koristi prilagođeno ime.
- Određivač kolone:[Iznos prodaje]and[Iznos provizije] su određivači kolona koji koriste imena kolona koje predstavljaju. Oni upućuju na podatke iz kolona, bez zaglavlja kolona ili redova sa ukupnim vrednostima. Određivače uvek navedite u zagradama kao što je prikazano.
- Određivač stavke:[#Totals] i [#Data] su određivači specijalnih stavki koji se odnose na određene delove tabele, kao što je red sa ukupnim vrednostima.
- Određivač tabele:[[#Ukupne vrednosti],[Iznos prodaje]] i [[#Podaci],[Iznos provizije]] su određivači tabele koji predstavljaju spoljašnji deo strukturirane reference. Spoljašnje reference prate ime tabele i stavljaju se u uglaste zagrade.
- Strukturirana referenca:(ProdajaSekt[[#Totals],[Iznos prodaje]] i ProdajaSekt[[#Data],[Iznos provizije]] su strukturirane reference, predstavljene niskom koja počinje imenom tabele i završava se određivačem kolone.
Da biste ručno kreirali ili uredili strukturirane reference, koristite ova sintaksička pravila:
- Koristite zagrade oko uređivača Svi određivači tabele, kolona i specijalnih stavki treba da stoje u podudarnim zagradama ([ ]). Određivač koji sadrži druge određivače zahteva spoljašnje podudarne zagrade unutar kojih se nalaze unutrašnje podudarne zagrade drugih određivača. Na primer: =ProdajaSekt[[Prodavač]:[Region]]
- Zaglavlja svih kolona su tekstualne niske Ali oni ne zahtevaju navodnike kada se koriste u strukturiranoj referenci. Brojevi ili datumi, kao što je 2014. ili 1.1.2014, takođe se smatraju tekstualnim niskama. Sa zaglavljima kolona ne možete da koristite izraze. Na primer, izraz ProdajaSektPGRezime[[2014]:[2012]] neće raditi.
Koristite zagrade oko zaglavlja kolona sa specijalnim znakovima Ako sadrži specijalne znakove, celo zaglavlje kolone mora da bude u zagradama, što znači da su u određivaču kolone neophodne dvostruke zagrade. Na primer: =ProdajaSektPGRezime[[Ukupan $ iznos]]
Evo spiska specijalnih znakova koji zahtevaju dodatne zagrade u formuli:
- Tab
- Znak za položaj u sledećem redu
- Znak za povratak na početak reda
- Zarez (,)
- Dvotačka (:)
- Tačka (.)
- Leva uglasta zagrada ([)
- Desna uglasta zagrada (])
- Znak funte (#)
- Jednostruki navodnik (')
- Dvostruki navodnik (")
- Leva velika zagrada ({)
- Desna velika zagrada (})
- Znak dolara ($)
- Karet (^)
- Znak "ampersand" (&)
- Zvezdica (*)
- Znak plus (+)
- Znak jednakosti (=)
- Znak minus (-)
- Znak "veće od" (>)
- Simbol "manje od" (<)
- Znak deljenja (/)
- Znak za znak (@)
- Obrnuta kosa crta (\)
- Znak uzvika (!)
- Leva zagrada (()
- Desna zagrada ())
- Znak procenta (%)
- Znak pitanja (?)
- Backtick (')
- Tačka i zarez (;)
- Znak tilda (~)
- Donja crta (_)
- Koristite komandni znak za neke specijalne znakove u zaglavljima kolona Neki znakovi imaju specijalno značenje i zahtevaju korišćenje jednostrukog navodnika (') kao komandnog znaka. Na primer: =ProdajaSektPGRezime['#stavki]
Evo spiska specijalnih znakova koji zahtevaju komandni znak (') u formuli:
- Leva uglasta zagrada ([)
- Desna uglasta zagrada (])
- Znak funte (#)
- Jednostruki navodnik (')
- Znak za znak (@)
Koristite razmak radi poboljšanja čitljivosti u strukturiranoj referenci Znakove razmaka možete koristiti da biste poboljšali čitljivost strukturirane reference. Na primer: =ProdajaSekt[ [Prodavac]:[Region] ] ili =ProdajaSekt[[#Zaglavlja], [#Podaci], [% provizije]]
Preporučuje se upotreba jednog razmaka:
- Iza prve leve uglaste zagrade ([)
- ispred poslednje desne uglaste zagrade (]).
- posle zareza.
Operatori za reference
Radi veće fleksibilnosti u navođenju opsega ćelija možete da koristite sledeće operatore za reference da biste kombinovali određivače kolona.
| Ova strukturirana referenca: | Upućuje na sledeće: | Pomoću sledećeg: | Što predstavlja opseg ćelija: |
|---|---|---|---|
| =ProdajaSekt[[Prodavac]:[Region]] | Sve ćelije u dve ili više susednih kolona | : (dvotačka) operator opsega | A2:B7 |
| =ProdajaSekt[IznosProdaje],ProdajaSekt[Iznos provizije] | Kombinacija dveju ili više kolona | , (zarez) operator za združivanje | C2:C7, E2:E7 |
| =ProdajaSekt[[Prodavac]:[Iznos prodaje]] ProdajaSekt[[Region]:[% provizije]] | Presek dveju ili više kolona | (razmak) operator za presek | B2:C7 |
Određivači specijalnih stavki
Da biste uputili na određene delove tabele, kao što su samo redovi sa ukupnim vrednostima, u strukturiranim referencama možete da koristite bilo koji od sledećih određivača specijalnih stavki.
| Ovaj određivač specijalnih stavki: | Upućuje na sledeće: |
|---|---|
| #Sve | Cela tabela, uključujući zaglavlja kolona, podatke i ukupne vrednosti (ukoliko postoje). |
| #Podaci | Samo redovi podataka. |
| #Zaglavlja | Samo red zaglavlja. |
| #Ukupne vrednosti | Samo red sa ukupnim vrednostima. Ako red ne postoji, daje nulu. |
| #Ovaj red ili @ ili @[Ime kolone] |
Samo ćelije koje su u istom redu u kom se nalazi i formula. Ove određivače nije moguće kombinovati sa drugim određivačima specijalnih stavki. Koristite ih da biste nametnuli ponašanje implicitnog preseka za referencu ili da biste zamenili ponašanje implicitnog preseka i uputili na pojedinačne vrednosti iz kolone. Excel automatski menja određivače #Ovaj red u kraći @ određivač u tabelama koje imaju više redova podataka. Ako tabela ima samo jedan red, Excel neće zameniti određivač #This Red, što može dovesti do neočekivanih rezultata izračunavanja kad dodate još redova. Da biste izbegli probleme sa izračunavanjem, uverite se da ste u tabelu uneli više redova pre unošenja formula strukturiranih referenci. |
Kvalifikovanje strukturiranih referenci u izračunatim kolonama
Kada kreirate izračunatu kolonu, često koristite strukturiranu referencu za kreiranje formule. Ta strukturirana referenca može biti nekvalifikovana ili potpuno kvalifikovana. Na primer, za kreiranje izračunate kolone sa imenom "Iznos provizije", koja izračunava iznos provizije u dolarima, možete da koristite sledeće formule:
| Tip strukturirane reference | Primer | Komentar |
|---|---|---|
| Nekvalifikovana | =[Iznos prodaje]*[% provizije] | Množi odgovarajuće vrednosti iz trenutnog reda. |
| Potpuno kvalifikovana | =ProdajaSekt[Iznos prodaje]*ProdajaSekt[% provizije] | Množi odgovarajuće vrednosti iz svakog reda za obe kolone. |
Opšte pravilo koje treba slediti glasi: ako unutar tabele koristite strukturirane reference, na primer prilikom kreiranja izračunate kolone, možete koristiti nekvalifikovanu strukturiranu referencu, ali ako koristite strukturiranu referencu izvan tabele, potrebno je da koristite potpuno kvalifikovanu strukturiranu referencu.
Primeri korišćenja strukturiranih referenci
Evo nekih načina kako da koristite strukturirane reference.
| Ova strukturirana referenca: | Upućuje na sledeće: | Što predstavlja opseg ćelija: |
|---|---|---|
| =ProdajaSekt[[#Sve],[Iznos prodaje]] | Sve ćelije u koloni „Iznos Prodaje“. | C1:C8 |
| =ProdajaSekt[[#Zaglavlja],[% provizije]] | Zaglavlje kolone „% provizije“. | D1 |
| =ProdajaSekt[[#Ukupne vrednosti],[Region]] | Ukupna vrednost kolone „Region“. Ako ne postoji red „Ukupne vrednosti“, daje nulu. | B8 |
| =ProdajaSekt[[#Sve],[Iznos prodaje]:[% provizije]] | Sve ćelije u kolonama „Iznos prodaje“ i „% provizije“. | C1:D8 |
| =ProdajaSekt[[#Podaci],[% provizije]:[Iznos provizije]] | Samo podaci kolona „% provizije“ i „Iznos provizije“. | D2:E7 |
| =ProdajaSekt[[#Zaglavlja],[Region]:[Iznos provizije]] | Samo zaglavlja kolona između kolona „Region“ i „Iznos provizije“. | B1:E1 |
| =ProdajaSekt[[#Ukupne vrednosti],[Iznos prodaje]:[Iznos provizije]] | Ukupne vrednosti od kolone „Iznos prodaje“ do kolone „Iznos provizije“. Ako ne postoji red „Ukupne vrednosti“, daje nulu. | C8:E8 |
| =ProdajaSekt[[#Zaglavlja],[#Podaci],[% provizije]] | Samo zaglavlje i podaci kolone „% provizije“. | D1:D7 |
| =ProdajaSekt[[#Ovaj red], [Iznos provizije]] ili =ProdajaSekt[@Iznos provizije] |
Ćelija u preseku trenutnog reda i kolone "Iznos provizije". Ako se koristi u istom redu u kom se nalaze i zaglavlje ili red sa ukupnim vrednostima, vratiće grešku #VALUE! . Ako u tabeli sa više redova podataka otkucate duži oblik ove strukturirane reference (#Ovaj red), Excel ga automatski zamenjuje kraćim oblikom (@). Oba oblika isto funkcionišu. |
E5 (ako je trenutni red 5) |
Strategije za rad sa strukturiranim referencama
Prilikom rada sa strukturiranim referencama razmotrite sledeće:
Koristite automatsko dovršavanje formula Možda ćete uvideti da je korišćenje automatskog dovršavanja formula vrlo korisno pri unosu strukturiranih referenci i da biste se uverili da koristite ispravnu sintaksu. Više informacija potražite u članku Korišćenje automatskog dovršavanja formule.
Odlučite da li ćete generisati strukturirane reference za tabele u polu-selekcijama Kada kreirate formulu, izborom opsega ćelija u okviru tabele podrazumevano se vrši polu-selekcija ćelija i u formulu se automatski unosi strukturirana referenca umesto opsega ćelija. Ovo ponašanje polu-selekcije umnogome olakšava unos strukturirane reference. Možete ga uključiti ili isključiti tako što ćete potvrditi ili opozvati izbor u polju za potvrdu Koristi imena tabela u formulama u dijalogu "Opcije>datoteke>: Formule">Rad sa formulama.
Korišćenje radnih svezaka sa spoljnim vezama prema Excel tabelama u drugim radnim sveskama Ako radna sveska sadrži spoljnu vezu prema Excel tabeli u drugoj radnoj svesci, ta povezana izvorna radna sveska mora biti otvorena u programu Excel da bi se izbeglo #REF! greške u odredišnoj radnoj svesci koja sadrži veze. Ako prvo otvorite odredišnu radnu svesku i pojave se #REF! , one će se ukloniti ako otvorite izvornu radnu svesku. Ako prvo otvorite izvornu radnu svesku, ne bi trebalo da vidite kodove greške.
Konvertujte opseg u tabelu i tabelu u opseg Kada konvertujete tabelu u opseg, sve reference ćelija menjaju se u ekvivalentne apsolutne reference stila A1. Kada opseg konvertujete u tabelu, Excel ne menja automatski reference ćelija tog opsega u ekvivalentne strukturirane reference.
Isključivanje zaglavlja kolona Zaglavlja kolona tabele možete da uključite i isključite sa kartice > "Red zaglavlja tabele". Ako isključite zaglavlja kolona tabele, to ne utiče na strukturirane reference koje koriste imena kolona i možete da ih i dalje koristite u formulama. Strukturirane reference koje upućuju direktno na zaglavlja tabele (npr. =ProdajaSekt[[#Headers],[%Komisija]]) rezultiraće #REF.
Dodajte kolone i redove u tabelu ili ih izbrišite iz nje Budući da se opsezi podataka tabela često menjaju, strukturirane reference ćelija se automatski prilagođavaju. Na primer, ako koristite ime tabele u formuli za prebrojavanje svih ćelija podataka u tabeli, a zatim dodate red podataka, referenca ćelije se automatski prilagođava.
Preimenujte tabelu ili kolonu Ako preimenujete kolonu ili tabelu, Excel automatski menja upotrebu te tabele i zaglavlja kolone u svim strukturiranim referencama koje se koriste u radnoj svesci.
Premeštajte, kopirajte i popunjavajte strukturirane reference Sve strukturirane reference ostaju iste kada kopirate ili premestite formulu koja koristi strukturiranu referencu.
Napomena
Kopiranje strukturirane reference i popunjavanje strukturirane reference nije ista stvar. Kada kopirate, sve strukturirane reference ostaju iste, dok kada popunite formulu, potpuno kvalifikovane strukturirane reference prilagođavaju određivače kolona kao grupu, kao što je rezimirano u sledećoj tabeli.
| Ako je smer popunjavanja: | I tokom popunjavanja pritisnete taster: | Onda dolazi do sledećeg: |
|---|---|---|
| Nagore ili nadole | Ništa | Ne postoji prilagođavanje određivača kolone. |
| Nagore ili nadole | Ctrl | Određivači kolona prilagođavaju se kao grupa. |
| Nadesno ili nalevo | Ništa | Određivači kolona prilagođavaju se kao grupa. |
| Nagore, nadole, nadesno ili nalevo | Shift | Umesto zamene trenutnih vrednosti u ćelijama, trenutne vrednosti u ćelijama se premeštaju i umeću se određivači kolona. |
Potrebna vam je dodatna pomoć?
Možete uvek da postavite pitanje stručnjaku u Excel Tech zajednici ili da potražite pomoć u zajednicama.
Srodne teme
Pregled Excel tabela
Pravljenje i oblikovanje tabela
Ukupne vrednosti podataka u Excel tabeli
Oblikovanje Excel tabele
Promena veličine tabele dodavanjem ili uklanjanjem redova i kolona
Filtriranje podataka u opsegu ili tabeli
Konvertovanje tabele u opseg
Problemi sa kompatiblinošću u vezi sa Excel tabelom
Izvoz Excel tabele u SharePoint
Pregledi formula u programu Excel