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

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. Parauga darblapā noklikšķiniet šūnā 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 tabulas šūnu, lai lentē > cilni Tabulas rīki.

2. Ierakstiet 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ītājā  Nosaukumā nevar izmantot atstarpes. Varat izmantot pasvītrojuma rakstzīmi (_) un punktu (.) kā vārdu atdalītājus. Piemēram, NodPārd, Sales_Tax Pirmais.ceturksnis.

• Izmantot ne vairāk kā 255 rakstzīmes Tabulas nosaukumā var būt līdz 255 rakstzīmēm.

• Unikālu tabulu nosaukumu izmantošana Nav atļauti nosaukumu dublikāti. Programma Excel neatšķir lielos un mazos burtus nosaukumos, tāpēc, ja tajā pašā darbgrāmatā ievadīsit "Pārdošana", bet tajā pašā darbgrāmatā jau būs cits nosaukums "PĀRDOŠANA", saņemsit aicinājumu izvēlēties unikālu nosaukumu.

• Objekta identifikatora izmantošana  Ja plānojat izveidot vairākas tabulas, rakurstabulas un diagrammas, ieteicams pievienot nosaukumus kopā ar objekta tipu. Piemēram: tbl_Sales tabulai, pt_Sales pārdošanas rakurstabulai un chrt_Sales pārdošanas diagrammai vai ptchrt_Sales rakursdiagrammu. Šādi visi jūsu vārdi ir 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ārd ir pielāgots tabulas nosaukums. Tas atsaucas uz tabulas datiem bez galvenes un kopsummu rindām. Varat izmantot noklusējuma tabulas nosaukumu, piemēram, Tabula1, vai mainīt to, lai izmantotu pielāgotu nosaukumu.

• Kolonnas apzīmētājs.    [PārdSuma] un [KomSumma] ir kolonnu apzīmētāji, kuros tiek lietoti to attēloto kolonnu nosaukumi. Tie atsaucas uz kolonnas datiem bez kolonnas galvenes vai kopsummas rindas. Apzīmētājus vienmēr iekļaujiet iekavās, kā parādīts.

• Vienuma apzīmētājs.    [#Totals] un [#Data] ir īpašu vienumu apzīmētāji, kas norāda uz noteiktu tabulas daļu, piemēram, kopsummu rindu.

• Tabulas    apzīmētājs.[[#Totals],[PārdS summa]] un [[#Data],[KomS summa]] ir tabulas apzīmētāji, kas norāda strukturētās atsauces ārējās daļas. Ārējās atsauces seko tabulas nosaukumam, un tās tiek iekļautas kvadrātiekavās.

• Strukturētā atsauce.   (NodPārd[[#Totals],[PārdSumta]] un NodPārd[[#Data],[KomSumda]] ir strukturētās atsauces, un tās attēlo virkne, kas sākas ar tabulas nosaukumu un beidzas ar kolonnas apzīmētāju.

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ēram: =NodPārd[[Pārdevējs]:[Reģions]]

• Visas kolonnu galvenes ir teksta virknes.    Tomēr 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.

Iekavu izmantošana ap kolonnu galvenēm, izmantojot speciālās rakstzīmes    Ja ir speciālās rakstzīmes, visa kolonnas galvene ir jāiekļauj iekavās. Tas nozīmē, ka kolonnas apzīmētājā ir jāiekļauj dubultiekavas. Piemēram: =NodPārdFYSummary[[Kopsumma $ Amount]]

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

• Tabulēšanas taustiņš

• Rindiņas plūsma

• Rakstatgrieze

• Komats (,)

• Kols (:)

• Punkts (.)

• Kreisā iekava ([)

• Labā iekava (])

• Mārciņas zīme (#)

• Vienpēdiņa (')

• Dubultpēdiņas (")

• Kreisā figūriekavas ({)

• Labā figūriekavas (})

• Dolāra zīme ($)

• Caret (^)

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

• Pēc zīmes (@)

• Atpakaļvērstā slīpsvītra (\)

• Izsaukuma zīme (!)

• Kreisā iekava (()

• Labā iekava ())

• Procentu zīme (%)

• Jautājuma zīme (?)

• Atpakaļatkāve (')

• Semikols (;)

• Tilde (~)

• Pasvītrojuma zīme (_)

• 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 ir speciālo rakstzīmju saraksts, kurām formulā nepieciešama atsoļa rakstzīme ('):

• Kreisā iekava ([)

• Labā iekava (])

• Mārciņas zīme(#)

• Vienpēdiņa (')

• Pēc zīmes (@)

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:

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

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 KomS summa, kurā tiek aprēķināta komisiju summa dolāros, var izmantot šādas formulas:

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.

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

Strādājot ar strukturētām atsaucēm, ņemiet vērā tālāk norādītos.

• 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 rakstā Formulu automātiskās pabeigšanas izmantošana.

• Strukturēto atsauču izveide daļēji atlasāmām tabulām    Pēc noklusējuma, veidojot formulu un noklikšķinot tabulas šūnu diapazonā, šū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. Varat ieslēgt vai izslēgt šo darbību, atzīmējot vai notīrot izvēles rūtiņu Formulās izmantot tabulu nosaukumus sadaļā Faila > opcijas > Formulas > Darbs ar formulām.

• Darbgrāmatu ar ārējām saitēm izmantošana uz Excel tabulām citās darbgrāmatās    Ja darbgrāmatā ir ārēja saite uz Excel tabulu citā darbgrāmatā, programmai Excel ir jābūt atvērtai programmā Excel, lai izvairītos no #REF! kļūdām mērķa darbgrāmatā, kurā ir ietvertas saites. Ja vispirms atverat mērķa darbgrāmatu un #REF! kļūdas, tās tiek novērstas, atverot avota darbgrāmatu. Ja vispirms atverat avota darbgrāmatu, kļūdu kodi nav jāredzat.

• 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 ekvivalentām strukturētām atsaucēm.

• Kolonnas galvenes izslēgšana    Tabulas kolonnu galvenes varat ieslēgt un izslēgt no tabulas cilnes Noformējums , > galvenes rindu. Izslēdzot tabulas kolonnu galvenes, strukturētās atsauces, kurās izmantoti kolonnu nosaukumi, netiek ietekmētas, un tās joprojām varat izmantot formulās. Strukturētās atsauces, kas attiecas tieši uz tabulas galvenēm (piemēram, =NodPārd[[#Headers],[Kom Kom]]), izraisīs #REF.

• Kolonnu un rindu pievienošana vai dzēšana tabulā    Tā kā tabulas datu diapazoni bieži mainās, šūnu atsauces strukturēto atsauču automātiski tiek pielāgotas. 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, kas izmanto strukturētu atsauci, visas strukturētās atsauces paliek nemainīgas.

  Piezīme.: Strukturētas atsauces kopēšana un strukturētās atsauces aizpildījuma aizpildīšana nav tas pats. Kopējot, visas strukturētās atsauces paliek nemainīgas, savukārt, aizpildot formulu, pilnas strukturētās atsauces pielāgo kolonnas apzīmētājus kā sēriju kā apkopotu tālāk esošajā tabulā.

Vai nepieciešama papildu palīdzība?

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

Saistītās tēmas

Excel tabulu pārskats
Video: Excel tabulas izveide un formatēšana
Datu kopsumma 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 uz SharePoint
Pārskats par formulām programmā Excel