Uporaba sestavljenih sklicev v Excelovih tabelah

Ko ustvarite Excelovo tabelo, Excel dodeli ime tabeli in vsaki glavi stolpca v tabeli. Ko v Excelovo tabelo dodate formule, se ta imena prikažejo samodejno, ko v tabelo vnašate formulo in izberete sklice na celice, tako da vam jih ni treba vnašati ročno. Primer tega, kaj naredi Excel:

Namesto uporabe eksplicitnih sklicev na celico	Excel uporabi imena tabel in stolpcev
=Sum(C2:C7)	=SUM(ProdOdd[Znesek prodaje])

Ta kombinacija imena tabele ter imena stolpca se imenuje strukturiran sklic. Imena v strukturiranem sklicu se prilagodijo vsakič, ko dodate ali odstranite podatke iz tabele.

Strukturiran sklic se pojavi tudi takrat, ko ustvarite formulo zunaj Excelove tabele, ki se sklicuje na podatke v tabeli. Sklici vam olajšajo iskanje tabel v velikem delovnem zvezku.

Če želite v formulo vključiti strukturirane sklice, ne vnašajte sklicev celic, na katere se želite sklicevati, temveč izberite celice v tabeli. Za vnos formule si oglejmo naslednji primer, ki samodejno uporabi strukturirane sklice za izračun zneska prodajne provizije.

Prodajna oseba	Regija	Znesek prodaje	% provizije	Znesek provizije
Janez	Sever	260	10 %
Robert	Jug	660	15 %
Metka	Vzhod	940	15 %
Erik	Zahod	410	12 %
Dafna	Sever	800	15 %
Robi	Jug	900	15 %

1. Kopirajte vzorčne podatke v zgornjo tabelo, vključno z naslovi stolpcev, in jih prilepite v celico A1 v novem Excelovem delovnem listu.
2. Če želite ustvariti tabelo, izberite katero koli celico v obsegu podatkov in pritisnite Ctrl+T.
3. Preverite, ali je polje »Moja tabela ima glave « potrjeno in izberite »V redu«.
4. V celico E2 vnesite enačaj (=) in izberite celico C2.
   Strukturiran sklic [@[Znesek prodaje]] se prikaže za enačajem v vnosni vrstici.
5. Vnesite zvezdico (*) neposredno za zaklepajem in izberite celico D2.
   Strukturiran sklic [@[% provizije]] se prikaže za zvezdico v vnosni vrstici.
6. Pritisnite tipko Enter.
   Excel samodejno ustvari izračunan stolpec in formulo kopira po celem stolpcu tako, da jo prilagodi vsaki vrstici.

Kaj se zgodi, ko uporabim eksplicitne slikce na celico?

Če v izračunani stolpec vnesete eksplicitne sklice na celico, je težje opazovati, kaj formula izračuna.

1. Na vzorčnem delovnem listu izberite celico E2
2. V vnosno vrstico vnesite =C2*D2 in pritisnite tipko Enter.

Excel med kopiranjem formule po stolpcu ne uporabi strukturiranih sklicev. Če želite na primer med obstoječa stolpca C in D dodati še en stolpec, bi morali spremeniti formulo.

Kako spremenim ime tabele?

Kadar ustvarite tabelo v Excelu, jo Excel privzeto poimenuje (Tabela1, Tabela2 itn.), vendar lahko ime tabele spremenite v bolj pomenljivega.

1. Izberite poljubno celico v tabeli, da se na traku prikaže zavihek » Načrt tabele «.
2. V polje » Ime tabele « vnesite želeno ime in pritisnite tipko Enter.

V vzorčnih podatkih smo uporabili ime »ProdOdd«.

Za imena tabel veljajo ta pravila:

• Uporabite veljavne znake Ime naj se vedno začne s črko, podčrtajem (_) ali poševnico nazaj (\). Za preostali del imena uporabite črke, številke, pike in podčrtaje. Črk »C«, »c«, »R« ali »r« ne morete uporabiti za ime, saj sta dodeljeni kot bližnjica za izbiranje stolpca ali vrstice aktivne celice, ko ju vnesete v polje »Ime « ali »Pojdi v «.

• Ne uporabljajte sklicev na celice Imena ne morejo biti enaka sklicem, kot je Z$100 ali R1C1.
• Besed ne ločujte s presledki V imenu ni mogoče uporabiti presledkov. Za ločevanje besed lahko uporabite podčrtaj (_) in piko (.). Na primer ProdOdd, Sales_Tax ali Prvo.četrtletje.
• Uporabite največ 255 znakov . Ime tabele je lahko sestavljeno iz največ 255 znakov.
• Uporabite enolična imena tabel Podvojena imena niso dovoljena. Excel ne razlikuje med malimi in velikimi črkami v imenih. Če torej vnesete »Prodaja«, a v istem delovnem zvezku že imate ime »PRODAJA«, vas bo program pozval, da izberete enolično ime.
• Uporaba identifikatorja predmeta Če nameravate imeti kombinacijo tabel, vrtilnih tabel in grafikonov, je dobro, če pred imena dodate predpono vrste predmeta. Na primer: tbl_Sales za tabelo prodaje, pt_Sales za prodajno vrtilno tabelo in chrt_Sales za prodajni grafikon ali ptchrt_Sales za prodajni vrtilni grafikon. S tem ohranite vsa svoja imena na urejenem seznamu v upravitelju imen.

Pravila sintakse za strukturirane sklice

Strukturirane sklice lahko v formulo vnesete ali jih spremenite tudi ročno, vendar boste za to morali razumeti sintakso strukturiranega sklica. Oglejmo si primer takšne formule:

=SUM(ProdOdd[[#Skupaj],[Znesek prodaje]],ProdOdd[[#Podatki],[Znesek provizije]])

V tej formuli so te komponente strukturiranega sklica:

• **Ime tabele:**ProdOdd je ime tabele po meri. Sklicuje se na podatke tabele brez glave ali skupne vrstice. Uporabite lahko privzeto ime tabele, kot je na primer Tabela1, lahko pa ga spremenite in uporabite ime po meri.
• Določitelj stolpca:[Znesek prodaje]in[Znesek provizije] sta določitelja stolpca, ki uporabljata imena stolpcev, ki jih predstavljata. Sklicujeta se na podatke stolpca brez glave stolpca ali skupne vrstice. Določitelje vedno postavite v oklepaje, kot je prikazano.
• Določitelj elementa:[#Totals] in [#Data] sta določitelja posebnih znakov, ki se sklicujeta na določene dele tabele, na primer na vrstico »Skupno«.
• Določitelj tabele:[[#Skupaj],[Znesek prodaje]] in [[#Podatki],[Znesek provizije]] so določitelji tabele, ki predstavljajo druge dele strukturiranega sklica. Zunanji sklici sledijo imenu tabele, postavite pa jih med oglate oklepaje.
• Strukturiran sklic:(ProdOdd[[#Totals],[Znesek prodaje]] in ProdOdd[[#Data],[Znesek provizije]] so strukturirani sklici, predstavljeni z nizom, ki se začne z imenom tabele in konča z določiteljem stolpca.

Če želite ustvariti ali urediti strukturirane sklice, upoštevajte ta pravila sintakse:

• Določitelje obdajte z oklepaji Vsi določitelji tabel, stolpcev in posebnih elementov morajo biti obdani z ujemajočimi se oklepaji ([ ]). Določitelj, ki ima druge določitelje, potrebuje še zunanje ujemajoče se oklepaje, ki obdajajo notranje ujemajoče se oklepaje drugih določiteljev. Na primer: =ProdOdd[[Prodajalec]:[Območje]]
• Vse glave stolpcev so besedilni nizi Vendar pa ne potrebujejo narekovajev, ko jih uporabite v strukturiranih sklicih. Številke ali datumi, na primer 2014 ali 1/1/2014 so prav tako besedilni nizi. V glavah stolpcev ne morete uporabiti izrazov. Na primer, izraz ProdajaOddFLPovzetek[[2014]:[2012]] ne bo deloval.

Glave stolpcev s posebnimi znaki postavite v oklepaje Če obstajajo posebni znaki, morate glavo stolpca postaviti v oklepaje, kar pomeni, da morate za določitelje stolpcev uporabiti dvojne oklepaje. Na primer: =ProdajaOddeFLPovzetek[[Skupni $ znesek]]

Oglejte si seznam posebnih znakov, ki v formule potrebujejo dodatne oklepaje:

• Tabulatorka
• Vir novice
• Prelom vrstice
• Vejica (,)
• Dvopičje (:)
• Pika (.)
• Levi oklepaj ([)
• Desni oklepaj (])
• Lojtra (#)
• Enojni narekovaj (')
• Dvojni narekovaj (")
• Levi zaviti oklepaj ({)
• Desni zaviti oklepaj (})
• Znak za dolar ($)
• Strešica (^)
• Znak »in« (&)
• Zvezdica (*)
• Znak plus (+)
• Enačaj (=)
• Znak minus (-)
• Večje kot simbol (>)
• Manjše kot simbol (<)
• Znak za deljenje (/)
• Afna (@)
• Poševnica nazaj (\)
• Klicaj (!)
• Levi oklepaj (()
• Desni oklepaj ())
• Znak za odstotek (%)
• Vprašaj (?)
• Backtick (')
• Podpičje (;)
• Tilda (~)
• Podčrtaj (_)

• Za določene posebne znake v glavah stolpcev uporabite ubežne znake Določeni znaki imajo poseben pomen in zahtevajo, da za ubežni znak uporabite enojni narekovaj ('). Na primer: =ProdajaOddFLPovzetek['#Elementov]

Oglejte si seznam posebnih znakov, ki v formuli zahtevajo ubežni znak ('):

• Levi oklepaj ([)
• Desni oklepaj (])
• Lojtra (#)
• Enojni narekovaj (')
• Afna (@)

Uporabite presledek za boljšo berljivost strukturiranega sklica Presledke lahko uporabite za lažjo berljivost strukturiranih sklicev. Na primer: =ProdOdd[ [Prodajalec]:[Območje] ] ali =ProdOdd[[#Glave], [#Podatki], [% provizije]]

Priporočamo, da uporabite en presledek:

• Za prvim levim oklepajem ([)
• Pred zadnjim desnim oklepajem (]).
• Za vejico.

Operatorji sklicev

Če želite dodatno prilagodljivost pri določanju obsegov celic, uporabite te operaterje sklicev za sestavljanje določiteljev stolpcev.

Sestavljeni sklic:	Se sklicuje na	Z:	Ki je obseg celic:
=ProdOdd[[Prodajalec]:[Območje]]	Vse celice v dveh ali več sosednjih stolpcih	: (dvopičje) operator obsega	A2:B7
=ProdOdd[Znesek prodaje],ProdOdd[Znesek provizije]	Kombinacija več stolpcev	, (vejica) operator unije	C2:C7, E2:E7
=ProdOdd[[Prodajalec]:[Znesek prodaje]] ProdOdd[[Območje]:[% provizije]]	Presečišče več stolpcev	(presledek) operator presečišča	B2:C7

Določitelji posebnih elementov

Če se želite sklicevati na določene dele tabele, na primer le na vrstico z rezultati, lahko v strukturiranem sklicu uporabite enega od teh določiteljev posebnih znakov.

Ta določitelj posebnega elementa	Se sklicuje na
#Vse	Celotna tabela, vključno z glavami stolpcev, podatki in vsotami (če so prisotne).
#Podatki	Le vrstice s podatki.
#Glave	Le vrstica z glavami.
#Skupaj	Le vrstica z vsotami. Če je ni, določitelj vrne prazno vrednost.
#Ta vrstica ali @ ali @[Ime stolpca]	Le celice v isti vrstici kot formula. Teh določiteljev ne morete združevati z drugimi določitelji posebnih znakov. Uporabite jih, če želite vsiliti implicitno presečišče pri sklicevanju ali če želite preglasiti implicitno presečišče in se sklicevati na posamezne vrednosti v stolpcu. V tabelah, v katerih je več kot ena vrstica s podatki, Excel določitelje »#Ta vrstica« samodejno spremeni v krajšega določitelja »@«. Če pa je v vaši tabeli le ena vrstica, Excel določitelja #This vrstice ne zamenja, saj bi lahko prišlo do nepričakovanih rezultatov izračunavanja, ko bi dodeli več vrstic. Če se želite izogniti težavam z izračunavanjem, vstavite v celico več vrstic, preden vnesete morebitne formule s sestavljenim sklicem.

Strukturirani sklici za kvalificiranje v izračunanih stolpcih

Ko ustvarite izračunani stolpec, pogosto uporabite strukturiran sklic za ustvarjanje formule. Ta strukturiran sklic je lahko nekvalificiran ali popolnoma kvalificiran. Če želite na primer ustvariti izračunani stolpec z imenom »ZnesProviz«, v katerem so izračunani zneski provizij v evrih, uporabite te formule:

Vrsta sestavljenega sklica	Primer	Pripomba
Nekvalificiran	=[Znesek prodaje]*[% provizije]	Pomnoži ustrezne vrednosti iz trenutne vrstice.
Popolnoma kvalificiran	=ProdOdd[Znesek prodaje]*ProdOdd[% provizije]	Pomnoži ustrezne vrednosti za vsako vrstico za oba stolpca.

Splošno pravilo, ki ga morate upoštevati je: če v tabeli uporabljate strukturirane sklice, kot na primer pri ustvarjanju izračunanega stolpca, uporabite nekvalificiran strukturiran sklic, toda če uporabite strukturiran sklic zunaj tabele, pa boste morali uporabiti popolnoma kvalificiran strukturiran sklic.

Primeri uporabe strukturiranih sklicev

Nekaj primerov uporabe sestavljenih sklicev.

Sestavljeni sklic:	Se sklicuje na	Ki je obseg celic:
=ProdOdd[[#Vse],[Znesek prodaje]]	Vse celice v stolpcu »Znesek prodaje«.	C1:C8
=ProdOdd[[#Glave],[% provizije]]	Glava stolpca »% provizije«.	D1
=ProdOdd[[#Skupaj],[Območje]]	Vsota stolpca »Regija«. Če vrstice z rezultati ni, vrne nič.	B8
=ProdOdd[[#Vse],[Znesek prodaje]:[% provizije]]	Vse celice v stolpcih »Znesek prodaje« in »% provizije«.	C1:D8
=ProdOdd[[#Podatki],[% provizije]:[Znesek provizije]]	Le podatki v stolpcih »% provizije« in »Znesek provizije«.	D2:E7
=ProdOdd[[#Glave],[Območje]:[Znesek provizije]]	Le glave stolpcev med stolpcema »Območje« in »Znesek provizije«.	B1:E1
=ProdOdd[[#Skupaj],[Znesek prodaje]:[Znesek provizije]]	Vsota stolpcev od stolpca »Znesek prodaje« do stolpca »Znesek provizije«. Če vrstice »Skupno« ni, je vrnjena vrednost »null«.	C8:E8
=ProdOdd[[#Glave],[#Podatki],[% provizije]]	Le glava in podatki stolpca »% provizije«.	D1:D7
=ProdOdd[[#Ta vrstica], [Znesek provizije]] ali =ProdOdd[@Znesek provizije]	Celica v preseku trenutne vrstice in stolpca »Znesek provizije«. Če je uporabljena v isti vrstici kot glava ali vrstica »Skupno«, je vrnjena napaka #VALUE! . Če daljšo obliko tega sestavljenega sklica (#Ta vrstica) vnesete v tabelo z več vrsticami podatkov, jo Excel samodejno zamenja s krajšo obliko (@). Delujeta enako.	E5 (če je trenutna vrstica 5)

Strategije za delo s strukturiranimi klici

Pri delu s strukturiranimi sklici imejte v mislih to.

• Uporabite funkcijo »Samodokončanje formul« Morda boste opazili, da je funkcija »Samodokončanje formul« zelo uporabna, ko vnašate sestavljene sklice in da z njo zagotovite uporabo pravilne sintakse. Če želite več informacij, glejte »Uporaba funkcije samodokončanja formul«.

• Odločanje, ali ustvariti strukturirane sklice za tabele v polizborih Ko ustvarite formulo, z izbiro obsega celic v tabeli privzeto naredite polizbor celic in samodejno vnesete strukturirani sklic namesto obsega celic v formulo. S polizborom si zelo olajšate vnašanje strukturiranih sklicev. To delovanje vklopite ali izklopite tako, da potrdite ali počistite potrditveno polje »Uporabi imena tabel v formulah« v pogovornem oknu»Možnosti>datoteke>«Formule Working>with formulas.

• Uporaba delovnih zvezkov z zunanjimi povezavami do Excelovih tabel v drugih delovnih zvezkih Če delovni zvezek vsebuje zunanjo povezavo do Excelove tabele v drugem delovnem zvezku, mora biti ta povezani izvorni delovni zvezek odprt v Excelu, da preprečite napake #REF! v ciljnem delovnem zvezku, ki vsebuje povezave. Če najprej odprete ciljni delovni zvezek in se prikažejo napake #REF! , bodo napake razrešene, če nato odprete izvirni delovni zvezek. Če najprej odprete izvorni delovni zvezek, kode napak ne bi smele biti prikazane.

• Pretvarjanje obsega v tabelo in tabele v obseg Ko pretvorite tabelo v obseg, se vsi sklici na celice samodejno spremenijo v njihove sklice v obliki A1. Ko pretvorite obseg v tabelo, Excel sklicev na celice tega obsega ne spremeni samodejno v enakovredne strukturirane sklice.

• Izklop glav stolpcev Glave stolpcev tabele lahko vklopite ali izklopite na zavihku » Načrt tabele « >v vrstici z glavo. Če izklopite glave stolpcev v tabeli, to ne vpliva na strukturirane sklice, ki uporabljajo imena stolpcev, in jih lahko še vedno uporabite v formulah. Strukturirani sklici, ki se sklicujejo neposredno na glave tabel (npr. =ProdOdd[[#Headers],[%Komisija]]) bodo pripeljali do #REF.

• Dodajanje ali brisanje stolpcev in vrstic v tabeli Ker se obsegi podatkov v tabelah pogosto spreminjajo, se sklici na celice za sestavljene sklice samodejno prilagodijo. Če na primer uporabite ime tabele v formuli za preštevanje vseh celic s podatki v tabeli, in nato dodate vrstico s podatki, se sklic na celico samodejno prilagodi.

• Preimenovanje tabele ali stolpca Če preimenujete stolpec ali tabelo, Excel samodejno spremeni ime te tabele ali glave stolpca v vseh sestavljenih sklicih, ki so uporabljeni v delovnem zvezku.

• Premikanje, kopiranje in izpolnjevanje strukturiranih sklicev Ko kopirate ali premikate formulo s sestavljenim sklicem, se sestavljeni sklici ne spremenijo.

  Opomba

  Kopiranje strukturiranega sklica in zapolnjevanje strukturiranega sklica nista enaki. Ko kopirate, ostanejo vsi sestavljeni sklici enaki, ko zapolnjujete formulo, pa popolnoma kvalificirani sestavljeni sklici prilagodijo določitelje stolpcev v obliki nizov, kot je povzeto v tej tabeli.

Če je smer zapolnjevanja:	In med zapolnjevanjem pritisnete:	Potem:
Navzgor ali navzdol	Nič	Ni prilagoditve določiteljev stolpcev.
Navzgor ali navzdol	Ctrl	Določitelji stolpcev se prilagodijo v obliki niza.
Desno ali levo	Brez	Določitelji stolpcev se prilagodijo v obliki niza.
Navzgor, navzdol, desno ali levo	Shift	Namesto, da bi vrednosti v trenutnih celicah bile prepisane, se trenutne vrednosti celic premaknejo, določitelji stolpcev pa vstavijo.

Potrebujete dodatno pomoč?

Kadar koli lahko zastavite vprašanje strokovnjaku v skupnosti tehničnih strokovnjakov za Excel ali pa pridobite podporo v skupnostih.

Sorodne teme

Pregled Excelovih tabel
Ustvarjanje in oblikovanje tabel
Seštevanje podatkov v Excelovi tabeli
Oblikovanje Excelove tabele
Spreminjanje velikosti tabele z dodajanjem vrstic in stolpcev
Filtriranje podatkov v obsegu ali tabeli
Pretvorba tabele v obseg
Težave z združljivostjo Excelovih tabel
Izvoz Excelove tabele v SharePoint
Pregledi formul v Excelu