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

Svarīgi! :  Šis raksts ir mašīntulkots, skatiet atrunu. Lūdzu, skatiet šī raksta versiju angļu valodā šeit jūsu informācijai.

Kad esat izveidojis Excel tabulu, programma Excel piešķir nosaukumu tabulai, un katrā tabulā kolonnas virsraksta. Pievienojot formulas pievienošana Excel tabulai, tie nosaukumi, var parādīties automātiski, ievadiet formulu un atlasiet šūnu atsauces, nevis manuāli ievadot tos tabulā. Lūk, piemērs Excel darbības:

Precīzu šūnu atsauču izmantošanas vietā

Excel izmanto tabulu un kolonnu nosaukumus

=Sum(C2:C7)

=SUM(NodPārd[PārdSumma])

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

Strukturētas atsauces tiek parādītas arī tad, kad jūs veidojat formulu ārpus Excel tabulas, atsaucoties uz tabulas datiem. Atsauces var vienkāršo 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 maksas summu.

Pārdošanas Personas

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. Datu paraugu iepriekš esošajā tabulā, tostarp kolonnu virsrakstus, kopējiet un ielīmējiet jaunas Excel darblapas šūnā A1.

  2. Lai izveidotu tabulu, atlasiet jebkuru šūnu datu diapazonā 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 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ā 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 šūnu tabulā, lai tiktu rādīta cilne Tabulas rīki > lentes cilne 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  Nosaukumu vienmēr sākas ar burtu, pasvītrojuma rakstzīmi (_) vai slīpsvītru (\). Izmantojiet burti, cipari, pieturzīmes un pasvītrojuma rakstzīmes pārējā nosaukumu. Nevar izmantot "C", "c", "R" vai "r" nosaukumu, jo tie jau esat paredzējis kā saīsne atlasīšanas kolonnu vai rindu aktīvās šūnas, ievadot to 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 var izmantot pasvītrojuma rakstzīmi (_) un punktu (.). Piemēram, NodPārd, punktu vai ceturksnis.

  • Izmantojiet līdz 255 rakstzīmēm. Tabulas nosaukumā var būt līdz 255 rakstzīmēm.

  • Izmantojiet unikālus tabulu nosaukumus. Nav atļauti dublēti nosaukumi. Excel nevar atšķirt lielos un mazos burtus nosaukumos, tāpēc, ja ievadīsit "Pārdošana", bet tajā pašā darbgrāmatā jau būs izmantots nosaukums "PĀRDOŠANA", saņemsit aicinājumu izvēlēties unikālu nosaukumu.

  • Izmantojiet objekta identifikators  Ja domājat par to, vai tabulas, rakurstabulas un diagrammas kombināciju, ieteicams pievienot prefiksu jūsu vārdus ar objekta tips. Piemērs: pārdošanas tabulā, pt_Sales pārdošanas PivotTable un chrt_Sales pārdošanas diagrammas vai rakursdiagrammas pārdošanas ptchrt_Sales tbl_Sales. Tas uztur visus vārdu sarakstu Nosaukumu pārvaldnieks.

Strukturētās atsauces sintakses kārtulas

Varat arī ievadīt vai mainīt manuāli formulas strukturētās atsauces, taču šim nolūkam palīdzēs saprast, strukturētās atsauces sintakses. Aplūkosim aiziet formulas piemērā:

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

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

  • Tabulas nosaukums:    Pielāgotas tabulas nosaukums ir NodPārd . Tas atsauces tabulas datus, bez visas galvenes vai kopsummas rindas. Varat izmantot noklusējuma tabulas nosaukums, piemēram, Tabula1, vai mainīt, lai izmantotu pielāgotu nosaukumu.

  • Kolonnas apzīmētājs:   [PārdSumma]un[KomSumma] ir kolonnu apzīmētāji, kas izmantot tās attēlo kolonnu nosaukumus. Tās atsauces kolonnas dati bez kolonnas galveni vai kopsummas rindas. Vienmēr iekļaujiet apzīmētāji iekavas, 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ārdSumma]] un [[#Data],[KomSumma]] ir tabulas apzīmētāji, kas norāda uz strukturētās atsauces ārējo daļu. Ārējās atsauces veidotas atbilstoši tabulas nosaukumam un tiek iekļautas kvadrātiekavās.

  • Strukturētas atsauces:   (NodPārd [[#Totals], [PārdSumma]] un NodPārd [[#Data], [KomSumma]] ir strukturētās atsauces pārstāv virkni, kas sākas ar tabulas nosaukumu un beidzas ar kolonnas apzīmētājs.

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

  • Izmantojiet iekavas ap apzīmētājiem.    Visu tabulu, kolonnu un īpašu vienumu apzīmētāji ir jāietver atbilstošās kvadrātiekavās ([]). Apzīmētājs, kurā ir iekļauti citi apzīmētāji nepieciešama ārējās atbilstošajās kvadrātiekavās, lai iekļaut iekšējās atbilstošajās kvadrātiekavās citi apzīmētāji. Piemēram: = NodPārd [[pārdevējs]: [Reģions]]

  • Visas kolonnu galvenes ir teksta virknes.    Bet tie nav nepieciešama piedāvājumi, ja viņi izmanto strukturētajās atsaucēs. Arī skaitļus vai datumus, piemēram, 2014. gada vai 1/1/2014. gada, tiek uzskatīta par teksta virkni. Nevar izmantot izteiksmes ar kolonnu galvenēm. Piemēram, izteiksme NodPārdFGKopsavilkums [[2014. gada]: [2012]] nedarbosies.

Iekavas izmantošana ap kolonnu galvenēm, kurās ir 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ājs ir jāiekļauj dubultajās iekavās. Piemēram: =NodPārdFGKopsavilkums[[$ Kopsumma]]

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

  • Tabulēšana

  • Līnijas padeve

  • Rakstatgriezes

  • 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ņš (^)

  • & zīme

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

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 Row. Šādā gadījumā, pievienojot vairāk rindu, iespējams, radīsies 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āk rindu.

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. Ja izmantota tajā pašā rindā kā galvene vai kopsummas rinda, tas tiek atgriezta vērtība #VALUE! kļūdas.

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ā norādīto darbību izmantošanas iespējas.

  • Formulu automātiskās pabeigšanas izmantošana    Izmantojot formulu automātiskā pabeigšana ir noderīga, kad ievadāt strukturēto atsauci un nodrošināt, ka tiek izmantota pareizā sintakse. Lai iegūtu papildinformāciju, skatiet rakstā Formulu automātiskās pabeigšanas izmantošana.

  • Strukturēto atsauču tabulas daļēji atlasi    Pēc noklusējuma, veidojot formulu, noklikšķinot uz šūnas diapazons laikā tabulas daļēji atlasa šūnas un automātiski ievada formulu strukturētās atsauces, nevis šūnu diapazons. Šī daļējas atlases darbība atvieglo Strukturēto atsauču. Šī rīcība ieslēgšana vai izslēgšana var ieslēgt, atzīmējot vai notīrot izvēles rūtiņu izmantot tabulu nosaukumus formulāsFails > Opcijas > Formulas > dialoglodziņa darbs ar formulām .

  • Izmantot darbgrāmatas, kurās ir ārējas saites uz Excel tabulām citās darbgrāmatās    Ja darbgrāmatā ir ārēja saite uz Excel tabulu citā darbgrāmatā, ka saistīto avota darbgrāmata ir jābūt atvērtai programmā Excel, lai izvairītos no #REF! kļūdu atrisināšana mērķa darbgrāmatu, kurā ir ietvertas saites. Ja vispirms atveriet mērķa darbgrāmatu un #REF! tiek parādītas kļūdas, tās tiks atrisināta, ja jūs pēc tam atveriet avota darbgrāmatu. Ja jums vispirms atveriet avota darbgrāmatu, jums vajadzētu redzēt bez kļūdu kodiem.

  • Pārveidot diapazonu tabulu un tabulu par diapazonu    Kad tabulas pārvēršana par diapazonu, visas šūnu atsauces mainās uz to ekvivalentu absolūta A1 stila atsaucēm. Ja diapazona pārvērst par tabulu Excel automātiski nemaina jebkuru šī diapazona šūnu atsauces, lai to ekvivalentu strukturētajās atsaucēs.

  • Kolonnas galvenes izslēgšana    Varat izmantot tabulas kolonnu galvenes ieslēgšana un izslēgšana no tabulas cilne noformējums > Galvenes rinda. Ja izslēdzat tabulas kolonnu galvenes, strukturētās atsauces, kurās izmantotas kolonnu nosaukumi netiek ietekmēti, un varat izmantot formulās. Strukturētās atsauces, kurās ir ietvertas atsauces tieši kā tabulas galvenes (piem. = NodPārd [[#Headers], [KomProc]]) izraisīs #REF.

  • Pievienot vai dzēst kolonnas un rindas, lai tabulas    Tā kā tabulas datu diapazoni bieži mainās, šūnu atsauces strukturētajās atsaucēs tiek pielāgotas automātiski. Piemēram, ja izmantojat tabulas nosaukumu formulā, lai saskaitītu visas datu šūnas tabulā 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.

  • Pārvietot, kopēt, un aizpildītu strukturētās atsauces    Strukturētās atsauces netiek mainītas, kad kopējat vai pārvietojat formulu, kas izmanto strukturētas atsauces.

    Piezīme : Strukturētās atsauces kopēšana un veicot aizpildījuma strukturētas atsauces nav tas pats. Kopējot, strukturētās atsauces netiek mainītas, laikā kad aizpildāt formulu, pilnībā kvalificēta strukturētā atsauce pielāgot kolonnas apzīmētājus kā sērijas kā apkopots nākamajā 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

Excel tabulu pārskats
Video: izveidojiet un formatējiet Excel tabulu
datu saskaitīšana programmas Excel tabulā
Excel tabulas formatēšana
tabulas, pievienojot vai noņemot rindas un kolonnas izmēru
Datu filtrēšana diapazonā vai tabulā
Pārvērst tabulu par diapazonu
Excel tabulas saderības problēmas
SharePoint Excel tabulas eksportēšana
apskati formulās Excel

Piezīme : Mašīntulkošanas atruna. Šo rakstu ir tulkojusi datorsistēma bez cilvēka iejaukšanās. Microsoft piedāvā šos mašīntulkojumus, lai palīdzētu angliski nerunājošajiem lietotājiem izmanot saturu par Microsoft produktiem, pakalpojumiem un tehnoloģijām. Tā kā šis raksts ir mašīntulkots, tajā var būt leksikas, sintakses un gramatikas kļūdas.

Paplašiniet savas 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 jūsu atsauksmēm!

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

×