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, 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ē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% |
-
Nokopējiet iepriekš minētās tabulas parauga datus, ieskaitot kolonnu virsrakstus, un ielīmējiet tos jaunas Excel darblapas šūnā A1.
-
Lai izveidotu tabulu, atlasiet jebkuru datu diapazona šūnu un nospiediet taustiņu kombināciju Ctrl+T.
-
Pārliecinieties, vai ir atzīmēta izvēles rūtiņa Manā tabulā ir galvenes, un noklikšķiniet uz Labi.
-
Šū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.
-
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.
-
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.
-
Parauga darblapā noklikšķiniet šūnā E2
-
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.
-
Atlasiet jebkuru tabulas šūnu, lai lentē > cilni Tabulas rīki.
-
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:
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 #This vairāk nekā viena datu rinda, programma Excel apzīmētājus automātiski maina uz īsāko apzīmētāju @. Bet, ja tabulā ir tikai viena rinda, programma Excel neaizstāj papildu #This 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. 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ā.
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 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