Strukturēto atsauču izmantošana Excel tabulās

Veidojot Excel tabulu, Excel piešķir nosaukumu gan tabulai, gan katrai tabulas kolonnas galvenei. Excel tabulā pievienojot formulas, šie nosaukumi var automātiski parādīties, kad ievadāt formulu un atlasāt šūnu atsauces tabulā; tie nav jāievada manuāli. Excel darbības piemērs:

Precīzu šūnu atsauču izmantošanas vietā	Excel izmanto tabulu un kolonnu nosaukumus
=Sum(C2:C7)	=SUM(NodPārd[PārdSumma])

Šo tabulu un kolonnu nosaukumu kombināciju sauc par strukturētu atsauci. Nosaukumi strukturētajās atsaucēs tiek pielāgoti, kad pievienojat vai noņemat datus no tabulas.

Strukturētās atsauces tiek parādītas arī tad, kad jūs veidojat formulu ārpus Excel tabulas kas atsaucas uz tabulas datiem. Atsauces var vienkāršot tabulu atrašanu lielā darbgrāmatā.

Lai formulā iekļautu strukturētas atsauces, atlasiet tabulas šūnas, kurām vēlaties pievienot atsauci (tā vietā lai ierakstītu šo šūnu atsauci formulā). Izmantosim tālāk norādīto datu paraugu, lai ievadītu formulu, kas automātiski izmanto strukturētas atsauces, lai aprēķinātu pārdošanas komisijas maksas summu.

Pārdevējs	Reģions	Pārdošanas summa	Komisijas procenti	Komisijas summa
Vilnītis	Ziemeļi	260	10%
Ziediņš	Dienvidi	660	15%
Pētersone	Austrumi	940	15%
Ēriksons	Rietumi	410	12%
Celma	Ziemeļi	800	15%
Gailis	Dienvidi	900	15%

1. Nokopējiet datu paraugu iepriekš redzamajā tabulā, tostarp kolonnu virsrakstus, un ielīmējiet jaunas Excel darblapas šūnā A1.
2. Lai izveidotu tabulu, atlasiet jebkuru datu diapazonā esošu šūnu un nospiediet taustiņu kombināciju Ctrl+T.
3. Pārliecinieties, vai ir atzīmēta izvēles rūtiņa Manā tabulā ir galvenes , un atlasiet Labi.
4. Šūnā E2 ierakstiet vienādības zīmi (=) un atlasiet šūnu C2.
   Formulas joslā strukturētā atsauce [@[Pārdošanas summa]] ir redzama pēc vienādības zīmes.
5. Ierakstiet zvaigznīti (*) tieši pēc aizverošās iekavas un atlasiet šūnu D2.
   Formulas joslā strukturētā atsauce [@[Komisijas procenti]] redzama pēc zvaigznītes.
6. Nospiediet taustiņu Enter.
   Excel automātiski izveido aprēķināto kolonnu un kopē formulu visā kolonnā uz leju, pielāgojot to katrā rindā.

Kas notiek, ja es izmantoju precīzas šūnu atsauces?

Ja aprēķina kolonnā tiek ievadītas precīzas šūnu atsauces, var būt grūtāk saskatīt, ko aprēķina formula.

1. Piemēra darblapā atlasiet šūnu E2
2. Formulu joslā ievadiet =C2*D2 un nospiediet taustiņu Enter.

Ievērojiet, ka programma Excel iekopē jūsu formulu visā kolonnā, tomēr neizmanto strukturētas atsauces. Ja, piemēram, jūs pievienojat kolonnu starp esošajām kolonnām C un D, formula jāpārskata.

Kā var mainīt tabulas nosaukumu?

Veidojot Excel tabulu, programma Excel izveido tabulas noklusējuma nosaukumu (Tabula1, Tabula2 utt.). Lai nosaukums būtu saprotamāks, to var mainīt.

1. Atlasiet jebkuru šūnu tabulā, lai lentē tiktu rādīta cilne Tabulas noformējums .
2. Ierakstiet vēlamo nosaukumu lodziņā Tabulas nosaukums un nospiediet taustiņu Enter.

Mūsu datu paraugā tiek izmantots nosaukums NodPārd.

Tabulu nosaukumiem izmantojiet šādas kārtulas:

• Derīgu rakstzīmju izmantošana Vienmēr sāciet nosaukumu ar burtu, pasvītrojuma rakstzīmi (_) vai slīpsvītru ().\ Atlikušajam nosaukumam izmantojiet burtus, ciparus, punktus un pasvītrojuma rakstzīmes . Nosaukumam nevar izmantot "C", "c", "R" vai "r", jo šie burti ir noformēti kā saīsne kolonnas vai rindas atlasīšanai aktīvajā šūnā, ievadot šos burtus lodziņā Nosaukums vai Doties uz .

• Neizmantojiet šūnu atsauces Nosaukumi nevar būt vienādi ar šūnu atsaucēm, piemēram, Z$100 vai R1C1.
• Neizmantojiet atstarpi vārdu atdalīšanai Nosaukumā nevar izmantot atstarpes. Par vārdu atdalītājiem varat izmantot pasvītras rakstzīmi (_) un punktu (.). Piemēram, NodPārd, Sales_Tax vai Pirmais.ceturksnis.
• Izmantojiet līdz 255 rakstzīmēm Tabulas nosaukumā var būt līdz 255 rakstzīmēm.
• Unikālu tabulu nosaukumu izmantošana Nosaukumu dublikāti nav atļauti. Programma Excel neatšķir lielo un mazo burtu lietojumu nosaukumos, tāpēc, ja ievadāt "Pārdošana", bet tajā pašā darbgrāmatā jau ir cits nosaukums ar nosaukumu "PĀRDOŠANA", jums tiks piedāvāts izvēlēties unikālu nosaukumu.
• Objekta identifikatora izmantošana Ja plānojat izmantot tabulu, rakurstabulu un diagrammu kombināciju, ieteicams pirms saviem vārdiem pievienot objekta tipu. Piemēram, tbl_Sales pārdošanas tabulai, pt_Sales pārdošanas rakurstabulai un chrt_Sales pārdošanas diagrammai, vai ptchrt_Sales pārdošanas rakursdiagrammai. Tādējādi visi jūsu vārdi tiek paturēti sakārtotā sarakstā nosaukumu pārvaldniekā.

Strukturētās atsauces sintakses kārtulas

Varat arī manuāli ievadīt vai mainīt strukturētās atsauces formulā, taču, lai to izdarītu, ir jāizprot strukturētās atsauces sintakse. Aplūkosim tālāk norādīto formulas piemēru.

=SUM(DeptSales[[#Totals],[Sales Amount]],DeptSales[[#Data],[Commission Amount]])

Šai formulai ir šādi strukturētās atsauces komponenti.

• **Tabulas nosaukums:**NodPārdIr pielāgots tabulas nosaukums. Tā atsaucas uz tabulas datiem bez galvenes vai kopsummu rindām. Varat izmantot tabulas noklusējuma nosaukumu, piemēram, Tabula1, vai mainīt to, lai izmantotu pielāgotu nosaukumu.
• Kolonnas apzīmētājs:[PārdSumma] un [KomSumma] ir kolonnu apzīmētāji, kuros izmantoti attiecīgo kolonnu nosaukumi. Tās norāda uz kolonnas datiem bez kolonnas galvenes un kopsummas rindas. Apzīmētājus vienmēr ietveriet iekavās, kā parādīts.
• Vienuma apzīmētājs:[#Totals] un [#Data] ir īpašo vienumu apzīmētāji, kas norāda uz noteiktām tabulas daļām, piemēram, kopsummas rindu.
• Tabulas apzīmētājs.[[#Totals],[PārdSumma]] un [[#Data],[KomSumma]] ir tabulas apzīmētāji, kas attēlo strukturētās atsauces ārējās daļas. Ārējās atsauces ir aiz tabulas nosaukuma, un tās tiek iekļautas kvadrātiekavās.
• Strukturēta atsauce:(NodPārd[[#Totals],[PārdSumma]] un NodPārd[[#Data],[KomSumma]] ir strukturētās atsauces. Tās attēlo virkne, kuras sākumā ir tabulas nosaukums un beigās — kolonnas apzīmētājs.

Manuāli izveidojot vai rediģējot strukturētās atsauces, izmantojiet šādas sintakses kārtulas.

• Lietojiet iekavas ap apzīmētājiem. Visi tabulu, kolonnu un īpašo vienumu apzīmētāji jāiekļauj atbilstošās kvadrātiekavās ([ ]). Apzīmētājs, kurā ir iekļauti citi apzīmētāji, jāraksta ārējās atbilstošajās kvadrātiekavās, lai citus apzīmētājus varētu iekļaut iekšējās atbilstošajās kvadrātiekavās. Piemērs: =NodPārd[[Pārdevējs]:[Reģions]].
• Visas kolonnu galvenes ir teksta virknes. Bet tām nav nepieciešamas pēdiņas, ja tās tiek izmantotas strukturētā atsaucē. Skaitļi vai datumi, piemēram, 2014 vai 1/1/2014, arī tiek uzskatīti par teksta virknēm. Nevarat izmantot izteiksmes ar kolonnu galvenēm. Piemēram, izteiksme NodPārdFGKopsavilkums[[2014]:[2012]] nedarbosies.

Izmantojiet iekavas ap kolonnu galvenēm ar speciālajām rakstzīmēm Ja programmā ir speciālās rakstzīmes, visa kolonnas galvene ir jāievieto iekavās, tas nozīmē, ka kolonnas apzīmētājā ir jābūt dubultiekavām. Piemērs: =NodPārdFGKopsavilkums[[Kopējā $ summa]]

Šeit sniegts speciālo rakstzīmju saraksts, kurām formulā jāizmanto papildu iekavas.

• Tabulēšanas taustiņš
• Līnijas padeve
• Rakstatgrieze
• Komats (,)
• Kols (:)
• Punkts (.)
• Kreisā iekava ([)
• Labā iekava (])
• Mārciņas zīme (#)
• Vienpēdiņa (')-
• Dubultpēdiņas (")
• Kreisā figūriekava ({)
• Labā figūriekava (})
• Dolāra zīme ($)
• Jumtiņa rakstzīme (^)
• Zīme “un” (&)
• Zvaigznīte (*)
• Pluszīme (+)
• Vienādības zīme (=)
• Mīnuszīme (-)
• Lielāks par simbols (>)
• Mazāks par simbols (<)
• Dalīšanas zīme (/)
• Zīme @
• Atpakaļvērstā slīpsvītra (\)
• Izsaukuma zīme (!)
• Kreisā iekava (()
• Labā iekava ())
• Procentu zīme (%)
• Jautājuma zīme (?)
• Atpakaļvērstā atzīme (')
• Semicolon (;)
• Tilde (~)
• Pasvītrojums (_)

• Atsoļa rakstzīmes izmantošana dažām speciālajām rakstzīmēm kolonnu galvenēs Dažām rakstzīmēm ir īpaša nozīme, un tām jāizmanto vienpēdiņa (') kā atsoļa rakstzīme. Piemērs: =NodPārdFGKopsavilkums['#OfItems]

Šeit sniegts speciālo rakstzīmju saraksts, kurām formulā jāizmanto atsoļa rakstzīme (').

• Kreisā iekava ([)
• Labā iekava (])
• Mārciņas zīme (#)
• Vienpēdiņa (')-
• Zīme @

Atstarpes lietošana, lai strukturētās atsauces būtu vieglāk lasāmas Lai strukturētā atsauce būtu vieglāk lasāma, izmantojiet atstarpes rakstzīmes. Piemērs: =NodPārd[[Pārdevējs]:[Reģions] ] vai =NodPārd[[#Headers], [#Data], [KomProc]].

Ieteicams izmantot vienu atstarpi:

• pēc pirmās kreisās iekavas ([)
• pirms pēdējās labās iekavas (]).
• pēc komata.

Atsauces operatori

Lai iegūtu plašākas šūnu diapazona norādīšanas iespējas, kombinējot kolonnu apzīmētājus, var izmantot šādus atsauces operatorus:

Strukturētā atsauce	Attiecas uz:	Izmantotā rakstzīme	Kāds ir šūnu diapazons:
=NodPārd[[Pārdevējs]:[Reģions]]	Divu vai vairāku blakus esošu kolonnu visas šūnas	: (kols) diapazona operators	A2:B7
=NodPārd[PārdSumma],NodPārd[KomSumma]	Divu vai vairāku kolonnu kombinācija	, (komats) savienojuma operators	C2:C7, E2:E7
=NodPārd[[Pārdevējs]:[PārdSumma]] NodPārd[[Reģions]:[KomProc]]	Divu vai vairāku kolonnu krustpunkts	(atstarpe) krustpunkta operators	B2:C7

Īpašu vienumu apzīmētāji

Lai izveidotu atsauci uz noteiktu tabulas daļu, piemēram, tikai kopsummu rindu, strukturētajās atsaucēs varat izmantot kādu no šiem īpašo vienumu apzīmētājiem.

Īpašā vienuma apzīmētājs	Attiecas uz:
#All	Visa tabula — ar kolonnu galvenēm, datiem un kopsummām (ja tādas ir)
#Data	Tikai datu rindas
#Headers	Tikai galvenes rinda
#Totals	Tikai kopsummu rinda. Ja tās nav, tiek atgriezts nulles rezultāts.
#This Row vai @ vai @[KolNos]	Tikai šūnas tajā pašā rindā, kurā atrodas formula. Šos apzīmētājus nevar kombinēt ar citiem īpašo vienumu apzīmētājiem. Izmantojiet tos, lai noteikti lietotu vai ignorētu netiešu atsauces krustpunkta darbību un norādītu uz atsevišķām kolonnas vērtībām. Tabulās, kurās ir vairāk par vienu datu rindu, programma Excel apzīmētājus #This Row automātiski maina uz īsākajiem apzīmētājiem @. Ja tabulai ir tikai viena rinda, programma Excel neaizstāj apzīmētāju #This Rinda, un šādā gadījumā, pievienojot vairāk rindu, var rasties neparedzēti aprēķinu rezultāti. Lai izvairītos no aprēķinu problēmām, pirms strukturētu atsauču formulu ievadīšanas pievienojiet tabulai vairākas rindas.

Strukturēto atsauču kvalificēšana aprēķinātajās kolonnās

Izveidojot aprēķinātu kolonnu, bieži vien formulas izveidošanai tiek izmantota strukturētā atsauce. Šī atsauce var būt nekvalificēta vai pilnībā kvalificēta. Piemēram, lai izveidotu aprēķinātu kolonnu ar nosaukumu KomSumma, kurā tiek aprēķināta komisiju summa dolāros, var izmantot šādas formulas:

Strukturētās atsauces tips	Piemērs	Komentārs
Nekvalificēta	=[PārdSumma]*[KomProc]	Sareizina pašreizējās rindas attiecīgās vērtības
Pilnībā kvalificēta	=NodPārd[PārdSumma]*NodPārd[KomProc]	Sareizina abu kolonnu katras rindas attiecīgās vērtības

Iegaumējiet! Ja tabulā izmantojat strukturēto atsauci, piemēram, lai izveidotu aprēķināto kolonnu, varat izmantot nekvalificētu atsauci, bet, ja strukturēto atsauci izmantojat ārpus tabulas, tai jābūt pilnībā kvalificētai.

Strukturēto atsauču izmantošanas piemēri

Tālāk ir sniegti daži strukturēto atsauču izmantošanas veidi.

Strukturētā atsauce	Attiecas uz:	Kāds ir šūnu diapazons:
=NodPārd[[#All],[PārdSumma]]	Visas kolonnas PārdSumma šūnas	C1:C8
=NodPārd[[#Headers],[KomProc]]	Kolonnas KomProc galvene	D1
=NodPārd[[#Totals],[Reģions]]	Kolonnas Reģions kopsumma. Ja nav rindas Kopsummas, tiek atgriezts nulles rezultāts.	B8
=NodPārd[[#All],[PārdSumma]:[KomProc]]	Visas kolonnu PārdSumma un KomProc šūnas	C1:D8
=NodPārd[[#Data],[KomProc]:[KomSumma]]	Tikai kolonnu KomProc un KomSumma dati	D2:E7
=NodPārd[[#Headers],[Reģions]:[KomSumma]]	Tikai kolonnu nosaukumi starp kolonnām Reģions un KomSumma	B1:E1
=NodPārd[[#Totals],[PārdSumma]:[KomSumma]]	Kopsummas no kolonnas PārdSumma līdz kolonnai KomSumma. Ja nav rindas Kopsummas, tiek atgriezts nulles rezultāts.	C8:E8
=NodPārd[[#Headers],[#Data],[KomProc]]	Tikai galvene un kolonnas KomProc dati	D1:D7
=NodPārd[[#This Row], [KomSumma]] vai =NodPārd[@KomSumma]	Pašreizējās rindas un kolonnas KomSumma krustpunkta šūna. Izmantojot tajā pašā rindā, kurā atrodas galvene, vai kopsummas rindā, tiks atgriezta kļūda #VALUE! . Ja tabulā ar vairākām datu rindām ierakstīsit šīs strukturētās atsauces garāko formu (#This Row), programma Excel to automātiski aizstās ar īsāko formu (@). Abas formas darbojas vienādi.	E5 (ja pašreizējā rinda ir 5. rinda)

Stratēģijas darbam ar strukturētajām atsaucēm

Strādājot ar strukturētajām atsaucēm, ņemiet vērā tālāk norādīto darbību izmantošanas iespējas.

• Formulu automātiskās pabeigšanas izmantošana Formulu automātiskā pabeigšana ir noderīga, kad ievadāt strukturēto atsauci, un tā nodrošināt, ka tiek izmantota pareizā sintakse. Papildinformāciju skatiet sadaļā Formulu automātiskās pabeigšanas izmantošana.

• Strukturēto atsauču izveide daļēji atlasītām tabulām Pēc noklusējuma, izveidojot formulu un atlasot tabulas šūnu diapazonu, šūnas tiek atlasītas daļēji un formulā automātiski tiek ievadītas strukturētās atsauces, nevis šūnu diapazons. Šī daļējas atlases darbība atvieglo strukturēto atsauču ievadīšanu. To var ieslēgt vai izslēgt, dialoglodziņā Faila>opcijas> atzīmējot vai notīrot izvēles rūtiņu Formulās lietot tabulu nosaukumus.Formulas>Working with formulas dialog.

• Tādu darbgrāmatu izmantošana, kurās ir ārējas saites uz Excel tabulām citās darbgrāmatās Ja darbgrāmatā iekļauta ārēja saite uz kādu Excel tabulu citā darbgrāmatā, šai saistītajai avota darbgrāmatai jābūt atvērtai programmā Excel, lai izvairītos no #REF! kļūdām mērķa darbgrāmatā, kurā iekļautas saites. Ja vispirms atverat mērķa darbgrāmatu un parādās kļūdas #REF! , tās tiek novērstas, atverot avota darbgrāmatu. Ja vispirms atverat avota darbgrāmatu, kļūdu kodi nav redzami.

• Diapazona pārvēršana par tabulu un pretēji Pārvēršot tabulu par diapazonu, visas šūnu atsauces mainās uz to ekvivalentajām absolūtajām A1 stila atsaucēm. Pārvēršot diapazonu par tabulu, programma Excel automātiski nemaina šī diapazona šūnu atsauces uz to ekvivalentajām strukturētajām atsaucēm.

• Kolonnas galvenes izslēgšana Varat ieslēgt un izslēgt tabulas kolonnu galvenes cilnē Tabulas noformējums>Galvenes rindā. Izslēdzot tabulas kolonnu galvenes, strukturētās atsauces, kurās izmantotas kolonnu nosaukumus, netiek ietekmētas, un tās var izmantot formulās. Strukturētas atsauces, kas tieši atsaucas uz tabulas virsrakstiem (piem., =NodPārd[[#Headers],[%Komisija]]), radīs #REF.

• Kolonnu un rindu pievienošana vai dzēšana tabulā Tā kā tabulas datu diapazoni bieži mainās, šūnu atsauces strukturētajās atsaucēs tiek pielāgotas automātiski. Piemēram, ja formulā izmantojat tabulas nosaukumu, lai saskaitītu visas tabulas datu šūnas un pēc tam pievienojat datu rindu, šūnas atsauce automātiski tiek pielāgota.

• Tabulas vai kolonnas nosaukuma maiņa Ja maināt kolonnas vai tabulas nosaukumu, programma Excel automātiski maina šīs tabulas vai kolonnas galvenes lietojumu visās darbgrāmatā izmantotajās strukturētajās atsaucēs.

• Strukturēto atsauču pārvietošana, kopēšana un aizpildīšana Kopējot vai pārvietojot formulu, kurā izmantotas strukturētas atsauces, šīs atsauces netiks mainītas.

  Piezīme

  Strukturētas atsauces kopēšana un strukturētas atsauces aizpildīšana nav viens un tas pats. Kopējot, visas strukturētās atsauces paliek nemainīgas, savukārt, aizpildot formulu, pilnās strukturētās atsauces pielāgo kolonnu apzīmētājus kā sēriju (skat. tālāk esošo tabulu).

Aizpildīšanas virziens	Taustiņš, kas jānospiež aizpildot	Darbība
Uz augšu vai uz leju	–	Netiek veikta kolonnas apzīmētāja pielāgošana.
Uz augšu vai uz leju	Ctrl	Kolonnu apzīmētāji tiek pielāgoti kā sērija.
Pa labi vai pa kreisi	–	Kolonnu apzīmētāji tiek pielāgoti kā sērija.
Uz augšu, uz leju, pa labi vai pa kreisi	Shift	Lai nebūtu jāpārraksta pašreizējo šūnu vērtības, tās tiek pārvietotas, un tiek ievietoti kolonnu apzīmētāji.

Vai nepieciešama papildu palīdzība?

Vienmēr varat pajautāt speciālistam Excel tehnoloģiju kopienā vai saņemt atbalstu kopienās.

Saistītās tēmas

Excel tabulu pārskats
Tabulu izveide un formatēšana
Datu saskaitīšana programmas Excel tabulā
Excel tabulas formatēšana
Tabulas izmēru maiņa, pievienojot vai noņemot rindas un kolonnas
Datu filtrēšana diapazonā vai tabulā
Tabulas pārvēršana par diapazonu
Excel tabulas saderības problēmas
Excel tabulas eksportēšana programmā SharePoint
Formulu pārskati programmā Excel