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

Veidojot tabulu Excel ,Excel piešķir nosaukumu tabulai un 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, noklikšķiniet uz tām tabulas šūnām, 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 naudas summu.

Pārdevēj s

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 iepriekš minētās tabulas parauga datus, ieskaitot kolonnu virsrakstus, un ielīmējiet jaunas darblapas Excel šūnā A1.

  2. Lai izveidotu tabulu, atlasiet jebkuru datu diapazona šū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 noklikšķiniet uz Labi.

  4. Šūnā E2 ierakstiet vienādības zīmi (=) un noklikšķiniet šūnā 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 noklikšķiniet šūnā 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. 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. 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 pārdošanas rakursdiagramma. Šā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ārdSumma]un [KomSumma] ir kolonnu apzīmētāji, kuros tiek lietoti to attēloto kolonnu nosaukumi. Tie atsaucas uz kolonnas datiem bez kolonnas galvenes un 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, ko 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.    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.

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

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

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.

Excel tabulās, kurās #This vairāk nekā viena datu rinda, apzīmētājus automātiski maina uz īsāko apzīmētāju @. Bet, ja jūsu tabulai ir tikai Excel rinda, tas neaizstāj #This rindas apzīmētāju, kas var izraisīt neparedzētus aprēķinu rezultātus, pievienojot vairāk rindu. Lai izvairītos no aprēķinu problēmām, pirms strukturēto atsauču formulu ievadīšanas tabulā noteikti ievadiet 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 KomS summa, 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 KomS summa krustpunkta šūna. Ja tiek izmantota vienā rindā ar galveni vai kopsummas rindu, tiks atgriezta #VALUE! kļūda.

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ē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. Šo iespēju var ieslēgt vai izslēgt, dialoglodziņā Faila > opcijas atzīmējot vai notīrot izvēles rūtiņuFormulās izmantot tabulu nosaukumus> Formulas > Darbs ar formulām.

  • Darbgrāmatu ar ārējām saitēm izmantošana Excel tabulām citās darbgrāmatās    Ja darbgrāmatā ir ārēja saite uz Excel tabulu citā darbgrāmatā, tai 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, 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 rinda. 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īš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ā.

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ālistiem Excel Tech kopienā, saņemt atbalstu Answers kopienā vai ieteikt jaunu līdzekli vai uzlabojumu vietnē Excel User Voice.

Saistītās tēmas

Pārskats Excel tabulām
Video: tabulas izveide Excel formatēšana
Datu kopsummu Excel tabulā
Tabulas Excel 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
Tabulas Excel eksportēšana SharePoint
Formulas pārskats programmā Excel

Vai nepieciešama papildu palīdzība?

Paplašiniet savas Office prasmes
Iepazīties ar apmācību
Esiet pirmais, kas saņem jaunās iespējas
Pievienoties Office Insider programmai

Vai šī informācija bija noderīga?

Paldies par atsauksmēm! Šķiet, ka jums varētu būt noderīgi sazināties ar kādu no mūsu Office atbalsta speciālistiem.

×