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 reference, izberite celice tabele, na katere se želite sklicevati, namesto da v formulo vnesete sklic na njihovo celico. S temi primeri podatkov vnesemo formulo, ki samodejno uporabi strukturirane reference 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 % |
- Kopirajte vzorčne podatke v zgornjo tabelo, vključno z naslovi stolpcev, in jih prilepite v celico A1 novega Excelovega delovnega lista.
- Če želite ustvariti tabelo, izberite poljubno celico v obsegu podatkov in pritisnite Ctrl+T.
- Prepričajte se, da je polje Moja tabela ima glave potrjeno, in izberite V redu.
- V celico E2 vnesite enačaj (=) in izberite celico C2.
Strukturiran sklic [@[Znesek prodaje]] se prikaže za enačajem v vnosni vrstici. - Neposredno za oklepajem vnesite zvezdico (*) in izberite celico D2.
Strukturiran sklic [@[% provizije]] se prikaže za zvezdico v vnosni vrstici. - 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.
- Na vzorčnem delovnem listu izberite celico E2
- 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.
- Izberite poljubno celico v tabeli, da na traku prikažete zavihek Načrt tabele .
- Vnesite želeno ime v polje Ime tabele in pritisnite tipko Enter.
V naših vzorčnih podatkih smo uporabili ime DeptSales.
Za imena tabel veljajo ta pravila:
- Uporaba veljavnih znakov Ime vedno začnite s črko, podčrtajem (_) ali obrnilno poševnico (\). Za preostali del imena uporabite črke, številke, pike in podčrtaje. Za ime ne morete uporabiti »C«, »c«, »R« ali »r«, ker so že označeni kot bližnjica za izbiro stolpca ali vrstice za aktivno celico, ko ju vnesete v polje Ime ali Pojdi na.
- Ne uporabljajte sklicev na celice Imena ne smejo biti enaka sklicu na celico, kot je Z$100 ali R1C1.
- Ne uporabljajte presledka za ločevanje besed V imenu ni mogoče uporabiti presledkov. Kot ločila besed lahko uporabite znak podčrtaja (_) in piko (.). Na primer DeptSales, Sales_Tax ali First.Quarter.
- Ne uporabljajte več kot 255 znakov Ime tabele ima lahko največ 255 znakov.
- Uporaba enoličnih imen tabel Podvojena imena niso dovoljena. Excel ne razlikuje med velikimi in malimi črkami v imenih, zato če vnesete »Prodaja«, vendar že imate drugo ime, imenovano »PRODAJA« v istem delovnem zvezku, boste pozvani, da izberete enolično ime.
- Uporaba identifikatorja predmeta Če nameravate imeti kombinacijo tabel, vrtilnih tabel in grafikonov, je priporočljivo, da imena označite z vrsto predmeta. Na primer: tbl_Sales za prodajno tabelo, pt_Sales za prodajno vrtilno tabelo in chrt_Sales za prodajni grafikon ali ptchrt_Sales za prodajni vrtilni grafikon. S tem ohranite vsa vaša imena na urejenem seznamu v upravitelju imen.
Pravila sintakse za strukturirane sklice
V formulo lahko tudi ročno vnesete ali spremenite strukturirane reference, vendar bo to pomagalo razumeti sintakso strukturiranega sklicevanja. Poglejmo naslednji primer formule:
=SUM(ProdOdd[[#Skupaj],[Znesek prodaje]],ProdOdd[[#Podatki],[Znesek provizije]])
V tej formuli so te komponente strukturiranega sklica:
- **Ime tabele:**DeptSales 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 stolpcev:[Znesek prodaje] in [Znesek provizije] sta določitelja stolpcev, 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 posebna določitelja elementov, ki se sklicujeta na določene dele tabele, na primer vrstico skupnega zneska.
- 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.
- Strukturirano sklic:(ProdajaOddelka[[#Totals],[Znesek prodaje]] in ProdajaOddelka[[#Data],[Znesek provizije]] so strukturirani sklici, ki jih predstavlja niz, ki se začne z imenom tabele in konča z določitvijo stolpca.
Če želite ročno ustvariti ali urediti strukturirane sklice, uporabite ta pravila sintakse:
- Uporaba oklepajev okoli določitevalcev Vsi določitelji tabele, stolpcev in posebnih elementov morajo biti vključeni v ujemajoče se oklepaje ([ ]). 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: =ProdajaOddelka[[Prodajalec]:[Regija]]
- Vse glave stolpcev so besedilni nizi Vendar ne zahtevajo narekovajev, če se uporabljajo v strukturirani referenci. Številke ali datumi, na primer 2014 ali 1/1/2014 so prav tako besedilni nizi. Izrazov ne morete uporabiti z glavami stolpcev. Izraz DeptSalesFYSummary[[2014]:[2012]] na primer ne bo deloval.
Uporaba oklepajev okoli glav stolpcev s posebnimi znaki Če obstajajo posebni znaki, mora biti celotna glava stolpca vključena v oklepaje, kar pomeni, da so v določitelju stolpca potrebni dvojni oklepaji. Na primer: =ProdajaOddeFLPovzetek[[Skupni $ znesek]]
Tukaj je seznam posebnih znakov, ki potrebujejo dodatne oklepaje v formuli:
- Tabulatorka
- Podajanje vrvice
- Vračilo prevoza
- Vejica (,)
- Dvopičje (:)
- Pika (.)
- Levi oklepaj ([)
- Desni oklepaj (])
- Znak za funt (#)
- Enojni narekovaji (')
- Dvojni narekovaji (")
- Levi oklepaj ({)
- Desna opornica (})
- Znak dolarja ($)
- Caret (^)
- Znak »in« (&)
- Zvezdica (*)
- Znak plus (+)
- Enačaj (=)
- Znak minus (-)
- Simbol Večje od (>)
- Simbol manj kot (<)
- Znak delitve (/)
- Pri znaku (@)
- Poševnica nazaj (\)
- Klicaj (!)
- Levi oklepaj ()
- Desni oklepaj ())
- Znak odstotka (%)
- Vprašaj (?)
- Povratna kljukica (')
- Podpičje (;)
- Tilda (~)
- Podčrtaj (_)
- Uporaba ubežnega znaka za nekatere posebne znake v glavah stolpcev Nekateri znaki imajo poseben pomen in zahtevajo uporabo enega narekovaja (') kot odhodnega znaka. Na primer: =ProdajaOddFLPovzetek['#Elementov]
Tukaj je seznam posebnih znakov, ki potrebujejo ubežni znak (') v formuli:
- Levi oklepaj ([)
- Desni oklepaj (])
- Znak za funt (#)
- Enojni narekovaji (')
- Pri znaku (@)
Uporaba znaka presledka za izboljšanje berljivosti v strukturiranem sklicu S presledki lahko izboljšate berljivost strukturiranega sklica. Na primer: =ProdOdd[ [Prodajalec]:[Območje] ] ali =ProdOdd[[#Glave], [#Podatki], [% provizije]]
Priporočljivo je, da uporabite en presledek:
- Za prvim levim oklepajem ([)
- Pred zadnjim desnim oklepajem (]).
- Po vejici.
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 specifikacij ni mogoče kombinirati z drugimi specifikacijami posebnih elementov. 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 ima tabela le eno vrstico, Excel ne zamenja določitnika #This vrstice, kar lahko povzroči nepričakovane rezultate izračuna, ko dodate 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, imenovan Znesek provizije, ki izračuna znesek provizije v dolarjih, lahko 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 naslednje: Če uporabljate strukturirane sklice v tabeli, na primer ko ustvarjate izračunani stolpec, lahko uporabite nekvalificiran strukturirani sklic, če pa strukturirani sklic uporabljate zunaj tabele, morate uporabiti popolnoma kvalificiran strukturirani 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 na presečišču trenutne vrstice in stolpca Znesek provizije. Če je uporabljena v isti vrstici kot glava ali vrstica z vsoto, se vrne 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 upoštevajte naslednje.
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 formule.
Odločitev o ustvarjanju strukturiranih sklicev za tabele v delnem izboru Ko ustvarite formulo, če izberete obseg celic v tabeli, privzeto izberete celice in samodejno vnesete strukturiran sklic namesto obsega celic v formuli. S polizborom si zelo olajšate vnašanje strukturiranih sklicev. To vedenje lahko vklopite ali izklopite tako, da potrdite ali počistite potrditveno polje Uporabi imena tabel v formulah v pogovornemoknuFormule>z možnostmi>datoteke>.
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 se izognete napakam #REF! v ciljnem delovnem zvezku, ki vsebuje povezave. Če najprej odprete ciljni delovni zvezek in se prikažejo #REF! napake, bodo odpravljene, če nato odprete izvorni delovni zvezek. Če najprej odprete izvorni delovni zvezek, ne bi smeli videti nobenih kod napak.
Pretvarjanje obsega v tabelo in tabele v obseg Ko pretvorite tabelo v obseg, se vsi sklici na celice spremenijo v enakovredne absolutne sklice v slogu A1. Ko pretvorite obseg v tabelo, Excel ne spremeni samodejno sklicev na celice tega obsega v enakovredne strukturirane sklicevanja.
Izklop glav stolpcev Glave stolpcev tabele lahko vklopite in izklopite na zavihku >Načrt tabeleVrstica glave. Če izklopite glave stolpcev tabele, to ne vpliva na strukturirane sklice, ki uporabljajo imena stolpcev, in jih lahko še vedno uporabljate v formulah. Strukturirani sklici, ki se sklicujejo neposredno na glave tabele (npr. =DeptSales[[#Headers],[%Commission]]), bodo dobili #REF.
Dodajanje ali brisanje stolpcev in vrstic v tabelo Ker se obsegi podatkov tabele pogosto spreminjajo, se sklici na celice za strukturirane 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 Vsi strukturirani sklici ostanejo enaki, ko kopirate ali premaknete formulo, ki uporablja strukturirani sklic.
Opomba
Kopiranje strukturiranega sklica in izpolnjevanje strukturiranega sklica nista ista stvar. Ko kopirate, vsi strukturirani sklici ostanejo enaki, ko pa izpolnite formulo, popolnoma kvalificirani strukturirani sklici prilagodijo določitelce stolpcev kot niz, kot je povzeto v spodnji 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 prepisovanja vrednosti v trenutnih celicah se trenutne vrednosti celic premaknejo in vstavijo določitelji stolpcev. |
Potrebujete dodatno pomoč?
Kadar koli se lahko obrnete na strokovnjaka v Excelovi tehnični skupnosti ali 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