Uporaba sestavljenih sklicev v Excelovih tabelah

Pomembno : Besedilo članka je prevedeno strojno. Glejte zavrnitev odgovornosti. Angleško različico tega članka najdete tukaj .

Ko ustvarite Excelovo tabelo, Excel dodeli ime tabele in vsako glavo stolpca v tabeli. Ko dodate formule v Excelovo tabelo, imena lahko prikažejo samodejno vnesite formulo in izberite sklice na celice v tabeli, namesto ročno vnašanje njih. Tukaj je primer delovanje Excel:

Namesto uporabe eksplicitnih sklicev na celico

Excel uporabi imena tabel in stolpcev

=Sum(C2:C7)

=SUM(ProdOdd[Znesek prodaje])

Kombinacija imena tabele in stolpca se imenuje sestavljen sklic. Imena se prilagodijo, ko dodate ali odstranite podatke iz tabele.

Sestavljeni sklici se pojavijo tudi, ko ustvarite formulo zunaj Excelove tabele, ki se sklicuje na podatke v tabeli. Sklici olajšajo iskanje tabel v velikem delovnem zvezku.

Če želite v formuli vključiti strukturirane sklice, ne vnašajte sklicev celic, na katere se želite sklicevati, temveč jih kliknite v tabeli. Za vnos formula si oglejmo ta primer, ki samodejno uporabi strukturirane sklice za izračun zneska prodajne provizije.

Prodaja Oseba

Področje

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 jo prilepite v celico A1 na nov Excelov delovni list.

  2. Če želite ustvariti tabelo, izberite katero koli celico v obsegu podatkov, in pritisnite Ctrl + T.

  3. Preverite, ali je potrjeno polje Moja tabela ima glave , in kliknite v redu.

  4. V celico E2 vnesite enačaj (=), nato pa kliknite celico C2.

    Strukturiran sklic [@[Znesek prodaje]] se prikaže za enačajem v vnosni vrstici.

  5. Vnesite zvezdico (*) neposredno za zaklepajem in kliknite celico D2.

    Strukturiran sklic [@[% provizije]] se prikaže za zvezdico v vnosni vrstici.

  6. Nato pritisnite 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. V vzorčnem delovnem listu, kliknite 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 katero koli celico v tabeli, da prikažete Orodja za tabele > načrt zavihek na traku.

  2. Vnesite želeno ime v polje Ime tabele in pritisnite tipko Enter.

V vzorčnih podatkih smo uporabili ime Prodaja_po_oddelkih.

Uporabite ta pravila za imena tabel:

  • Uporabite veljavne znake  Vedno začnejo ime s črko, podčrtaj (_) ali poševnico nazaj (\). Uporabite črke, številke, pike in podčrtaje do konca ime. Ne morete uporabiti »C« , »c« , »R« ali »r« ob imenu, ker ste že imenovali bližnjice za izbiro stolpca ali vrstice za aktivno celico, ko jih vnesete v polje ime ali Pojdi na .

  • Ne uporabite sklicev celic  Imena ne morejo biti enaka kot sklic na celico, na primer Z$ 100 ali R1C1.

  • Ne uporabljajte prostor besed  Presledkov, ni mogoče uporabiti v imenu. Znak podčrtaja (_) in piko (.) lahko uporabite kot beseda ločila. Na primer ProdajaOddelka, Prodajni_Davek ali prvo.četrtletje.

  • Uporabite lahko največ 255 znakov Ime tabele ima lahko do 255 znakov.

  • Uporabljajte enolične 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 drugo ime.

  • Uporaba identifikator predmeta  Če nameravate ob del tabele, vrtilne tabele in grafikone, je dobro, da predpona imena z vrsto predmeta. Na primer: tbl_Sales za v tabeli prodaje, pt_Sales za prodajo vrtilne tabele in chrt_Sales za prodaje grafikon ali ptchrt_Sales za prodaje vrtilnega grafikona. To ohrani vse imen v urejenega seznama v Upravitelj imen.

Pravila sintakse za strukturirane sklice

Lahko tudi vnesete ali spremenite sestavljenih sklicev ročno v formuli, vendar to narediti, bo pomagalo, če želite razumeti sintaksi sestavljenega sklica. Oglejmo si v naslednjem primeru formule:

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

V tej formuli so te komponente strukturiranega sklica:

  • Ime tabele:   ProdajaOddelka je ime tabele po meri. To se sklicuje na podatke tabele, ne da bi vse glave ali vsote vrstic. Uporabite privzeto ime tabele, na primer Tabela1, ali spremenite, če želite uporabiti ime po meri.

  • Določitelj stolpcev:   [Znesek prodaje]in[Znesek provizije] so določiteljev stolpcev, ki uporabljajo imena stolpcev, ki predstavljajo. Se sklicujejo na stolpec podatkov, brez stolpec glava ali vsote vrstice. Vedno morate določitelji v oklepajih, kot je prikazano.

  • Določitelj elementa:   [#Totals] in [#Data] so posebni določitelji, ki se sklicujejo na posebne dele tabele, kot so vrstice skupaj.

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

  • Sestavljeni sklic:   (ProdajaOddelka [[#Totals], [znesek prodaje]] in ProdajaOddelka [[#Data], [znesek provizije]] so strukturirani sklici, ki se začnejo z imenom tabele in konča z določiteljem stolpca.

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

  • Uporaba oklepajev določitelje obdajte    Vse tabele, stolpce in določitelji posebnih elementov morate postaviti v ujemanje oklepajev ([]). Določitelj, ki vsebuje druge določitelje zahteva zunanje ujemajoče se oklepaje, ki obdajajo notranje ujemajoče se oklepaje drugih določiteljev. Na primer: = ProdajaOddelka [[prodaja oseba]: [Regija]]

  • Vse glave stolpcev so besedilni nizi    Vendar ne zahtevajo narekovajev, ko se uporabljajo v sestavljeni sklic. Števil ali datumov, kot je na primer 2014 ali 1/1/2014, prav tako obravnavani kot besedilne nize. Ne morete uporabljati izraze z glavami stolpcev. Primer izraza ProdajaOddelkaZLPovzetek [[2014]: [2012]] ne bodo delovali.

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:

  • Zavihek

  • 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 (>)

  • Manjše kot (<)

  • Znak za deljenje (/)

  • 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 zahtevajo ubežni znak (') v formuli:

  • Levi oklepaj ([)

  • Desni oklepaj (])

  • Lojtra (#)

  • Enojni narekovaj (')

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 »#Ta vrstica« 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 se uporablja v isto vrstico kot glavo ali vrstica »skupno« , to bo vrnila na #VALUE! napake.

Č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, razmislite o tem.

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

  • Se odločite, ali ustvariti sestavljene sklice za tabele v delno izbire    Pri ustvarjanju formule, da kliknete celico obsega v tabelo delno izbere celice in privzeto samodejno vnese sestavljeni sklic namesto obsega celic v formuli. Tako delovanje polizboru omogoča veliko lažje, če želite vnesti sestavljenega sklica. Tako delovanje ali izklopite lahko spremenite tako, da potrdite ali počistite potrditveno polje uporabi imena tabel v formulah v datoteko > možnosti > formule > pogovorno okno za delo s formulami .

  • 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 povezanih izvorni delovni zvezek odprt v Excelu, da se izognete #REF! napak v ciljni delovni zvezek, ki vsebuje povezave. Če boste najprej odprite ciljni delovni zvezek in #REF! se prikažejo napake, bodo odpravljena, če ga nato odprite izvorni delovni zvezek. Če izvorni delovni zvezek odprete prvič, vidite brez kode napak.

  • Pretvarjanje obsega v tabelo in tabele v obseg    Ko pretvorite tabelo v obseg, vse sklice na celice spremenite v enakovredno absolutne sklice sloga A1. Ko obsega pretvoriti v tabelo, ne Excel samodejno spremeni vse sklice na celice v tem obsegu v svojih enakovredno sestavljenih sklicev.

  • Izklop glav stolpcev    Lahko preklapljate glave stolpcev v tabeli vklop in izklop na zavihku načrt tabele > Vrstica glave. Če izklopite glave stolpcev v tabeli, sestavljenih sklicev, ki uporabljajo imena stolpcev ne vplivajo in jih lahko še vedno uporabite v formulah. Strukturirani sklici, ki se sklicujejo neposredno v glavo tabele (npr. = ProdajaOddelka [[#Headers], [% provizije]]) bo imelo za posledico #REF.

  • Dodajanje ali brisanje stolpcev in vrstic v tabeli    Ker obsegi podatkov tabele se pogosto spreminjajo, sklice na celice za sestavljenih sklicev samodejno prilagodi. Na primer, če uporabljate ime tabele v formuli, če želite prešteti vse podatke celice v tabeli, in nato dodate vrstico podatkov, 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 sestavljeni sklici ostanejo isti, ko kopirate ali premikate formulo, ki uporablja sestavljeni sklic.

    Opomba : Kopiranje sestavljeni sklic in tako polnilo sestavljenega sklica ni enaka. Ko kopirate, vsi sestavljeni sklici ostanejo isti, medtem ko ko zapolnjujete formulo, popolnoma kvalificirani sestavljeni sklici prilagodite 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, pridobite podporo skupnosti Answers ali predlagate novo funkcijo oziroma izboljšavo na spletnem mestu Excel User Voice.

Sorodne teme

Pregled Excelovih tabel
Video: ustvarjanje in oblikovanje Excelove tabele
seštevanje podatkov v Excelovi tabeli
oblikovanje Excelove tabele
Spreminjanje velikosti tabele tako, da dodate ali odstranite vrstice in stolpce
Filtriranje podatkov v obsegu ali tabeli
Pretvorba tabele v obseg
težav z združljivostjo Excelovo tabelo
izvoz Excelove tabele v SharePoint
pregledi formul v Excel

Opomba : Strojni prevod – zavrnitev odgovornosti: Ta članek je bil preveden z računalniškim programom brez človeškega posredovanja. Microsoft skuša s strojno prevedenimi članki vsebino o Microsoftovih izdelkih, storitvah in tehnologijah približati osebam, ki ne razumejo angleščine. Ker je bil članek strojno preveden, so lahko v njem jezikovne, slovnične in pravopisne napake.

Razširite svoja znanja
Oglejte si izobraževanje
Prvi dobite nove funkcije
Pridružite se programu Office Insider

Vam je bila informacija v pomoč?

Zahvaljujemo se vam za povratne informacije.

Zahvaljujemo se vam za povratne informacije. Videti je, da bi vam prišla prav pomoč enega od naših Officeovih agentov za podporo.

×