Struktūrinių nuorodų naudojimas dirbant su "Excel" lentelėmis

Kai kuriate "Excel" lentelę, "Excel" priskiria lentelei ir kiekvienam stulpelio antraštei pavadinimą. Įtraukus formules į „Excel“ lentelę, tie pavadinimai gali būti automatiškai rodomi įvedus formulę ir pasirinkus lentelėje langelio nuorodą, kad nereikėtų įvesti rankiniu būdu. Čia pateikiamas pavyzdys, ką daro programa „Excel“:

Naudojimas vietoj tikslių langelio nuorodų	„Excel“ naudoja lentelės ir stulpelio pavadinimus
=Sum(C2:C7)	=SUM(DeptSales[Sales Amount])

Lentelės ir stulpelio pavadinimų derinys vadinamas struktūrine nuoroda. Pavadinimai struktūrinėse nuorodose koreguojami įtraukus į lentelę duomenų arba juos pašalinus.

Be to, struktūrinė nuoroda rodoma, jei formulė sukuriama už „Excel“ lentelės, kuri nurodo lentelės duomenis, ribų. Naudojant nuorodas patogiau rasti lenteles didelėje darbaknygėje.

Norėdami įtraukti į formulę struktūrines nuorodas, pažymėkite lentelės langelius, kuriuos norite nurodyti, kad nereikėtų įvesti langelio nuorodos formulėje. Naudokime tolesnius duomenų pavyzdžius, kad įvestume formulę, kuri automatiškai naudos struktūrines nuorodas ir suskaičiuos pardavimo komisinius.

Pardavėjas	Regionas	Pardavimo suma	Komisiniai %	Komisinių suma
Juozas	Šiaurės	260	10 %
Rimantas	Pietų	660	15 %
Marija	Rytų	940	15 %
Audrius	Vakarų	410	12 %
Daina	Šiaurės	800	15 %
Tadas	Pietų	900	15 %

1. Nukopijuokite duomenų pavyzdį iš aukščiau esančios lentelės, įskaitant stulpelių antraštes, ir įklijuokite jį į naujo "Excel" darbalapio langelį A1.
2. Norėdami sukurti lentelę, pasirinkite bet kurį langelį iš duomenų diapazono ir paspauskite Ctrl + T.
3. Įsitikinkite, kad pažymėtas žymės langelis Mano lentelėje yra antraščių , ir pasirinkite Gerai.
4. Langelyje E2 įveskite lygybės ženklą (=) ir pažymėkite langelį C2.
   Formulės juostoje struktūrinės nuorodos [@[pardavimo suma]] rodoma po lygybės ženklo.
5. Iš karto po uždaromojo skliausto įveskite žvaigždutę ()* ir pažymėkite langelį D2.
   Formulės juostoje struktūrinė nuoroda [@[%komisiniai]] rodoma po lygybės ženklo.
6. Paspauskite Enter.
   „Excel“ automatiškai sukuria apskaičiuojamąjį stulpelį ir nukopijuoja formulę per visą stulpelį koreguodama kiekvienoje eilutėje.

Kas nutiks, jei naudosiu tikslias langelio nuorodas?

Jei įvesite tikslias langelio nuorodas apskaičiuotame stulpelyje, gali būti sunkiau pamatyti, ką skaičiuoja formulė.

1. Darbalapio pavyzdyje pažymėkite langelį E2
2. Formulės juostoje įveskite =C2*D2 ir paspauskite Enter.

Atminkite, kad programoje „Excel“ kopijuojant formulę žemyn stulpeliu, programa nenaudoja struktūrinių nuorodų. Jei, pavyzdžiui, tarp esamų stulpelių C ir D įterpiate dar vieną, turite peržiūrėti, ar formulė teisinga.

Ar pakeisti lentelės pavadinimą?

Sukūrus „Excel“ lentelę, „Excel“ sukuria numatytąjį lentelės pavadinimą (Lentelė1, Lentelė2 ir taip toliau), tačiau jūs galite pakeisti pavadinimą į prasmingesnį.

1. Pažymėkite bet kurį lentelės langelį, kad juostelėje atsirastų skirtukas Lentelės dizainas .
2. Lauke Lentelės pavadinimas įveskite norimą pavadinimą ir paspauskite "Enter".

Duomenų pavyzdyje naudojome pavadinimą "DeptSales".

Naudokite toliau nurodytas lentelių pavadinimų taisykles:

• Leistinų simbolių naudojimas Pavadinimas visada pradedamas raide, pabraukimo simboliu (_) arba kairiniu pasviruoju brūkšniu (\). Likusiam pavadinimui naudokite raides, skaičius, taškus ir pabraukimo simbolius. Pavadinime negalima naudoti raidžių C, c, R arba r, nes jos jau priskirtos kaip nuoroda pasirenkant stulpelio ar eilutės aktyvų langelį, kai įvedate juos lauke Pavadinimas arba Eiti į .

• Nenaudokite langelių koordinačių Pavadinimai negali būti tokie patys kaip langelio koordinatės, pvz., Z$100 arba R1C1.
• Žodžiams atskirti nenaudokite tarpo Pavadinime negali būti tarpų. Kaip žodžių skyriklį galite naudoti pabraukimo simbolį (_) ir tašką (.). Pvz., DeptSales, Sales_Tax arba First.Quarter.
• Naudokite ne daugiau nei 255 simbolius Lentelės pavadinimą gali sudaryti ne daugiau kaip 255 simboliai.
• Naudokite unikalius lentelių pavadinimus Pasikartojantys pavadinimai yra neleidžiami. "Excel" pavadinimuose neskiria viršutinio ir apatinio registro simbolių, todėl jei įveskite "Pardavimai", bet toje pačioje darbaknygėje jau turite kitą pavadinimą PARDAVIMAI, būsite paraginti pasirinkti unikalų pavadinimą.
• Objekto identifikatoriaus naudojimas Jei planuojate naudoti lentelių, "PivotTable" ir diagramų derinį, prieš savo vardus reikėtų įvesti objekto tipą. Pvz.: tbl_Sales pardavimo lentelei, pt_Sales pardavimo "PivotTable", chrt_Sales pardavimo diagramai arba ptchrt_Sales pardavimo "PivotChart". Visi jūsų vardai saugomi tvarkingame pavadinimų tvarkytuvo sąraše.

Struktūrinių nuorodų sintaksės taisyklės

Formulėje taip pat galima rankiniu būdu įvesti arba keisti struktūrines nuorodas, bet norint tai padaryti, verta suprasti struktūrinių nuorodų sintaksę. Peržiūrėkime tolesnės formulės pavyzdį:

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

Šią formulę sudaro šie struktūrinių nuorodų komponentai:

• **Lentelės pavadinimas:**DeptSales yra pasirinktinės lentelės pavadinimas. Jis nurodo į lentelės duomenis be jokios antraštės arba sumos eilučių. Galite naudoti numatytąjį lentelės pavadinimą, pvz., Lentelė1, arba pakeisti jį pasirinktiniu pavadinimu.
• Stulpelio specifikatorius:[Sales Amount] ir [Commission Amount] yra stulpelių specifikatoriai, kurie naudoja savo žymimų stulpelių pavadinimus. Jie nurodo į stulpelio duomenis be jokios antraštės arba sumos eilutės. Specifikatorius visada įrašykite skliaustuose, kaip parodyta.
• Elemento specifikatorius:[#Totals] ir [#Data] yra specialūs elemento specifikatoriai, kurie nurodo konkrečias lentelės dalis, pvz., sumos eilutę.
• Lentelės specifikatorius:[[#Totals], [pardavimo suma]] ir [[#Data], [komisinių suma]] yra lentelės specifikatoriai, kurie žymi išorines struktūrinės nuorodos dalis. Išorinės nuorodos susietos su lentelės pavadinimu ir rašomos laužtiniuose skliaustuose.
• Struktūrinė nuoroda:(DeptSales[[#Totals],[Sales Amount]] ir DeptSales[[#Data],[Commission Amount]] yra struktūrinės nuorodos, nurodančios eilutę, prasidedančią lentelės pavadinimu ir pasibaigiančią stulpelio specifikatoriumi.

Norėdami rankomis sukurti arba redaguoti struktūrines nuorodas, naudokite šias sintaksės taisykles:

• Aplink specifikatorius naudokite skliaustus Visi lentelių, stulpelių ir specialiųjų elementų specifikatoriai turi būti vienoduose skliausteliuose ([ ]). Specifikatoriui, kuriame yra kiti specifikatoriai, reikalingi išorėje vienodi skliausteliai, kad kitų specifikatorių viduje būtų galima įrašyti vienodus skliaustus. Pavyzdys: =DeptSales[[Pardavėjas]:[Regionas]]
• Visos stulpelių antraštės yra teksto eilutės Tačiau jie nereikalauja kabučių, kai jie naudojami struktūrinėse nuorodose. Skaičiai ar datos, pvz., 2014 arba 2014-01-01, taip pat laikomos teksto eilutėmis. Išraiškų negalima naudoti su stulpelių antraštėmis. Pavyzdžiui, išraiška DeptSalesFYSummary [[2014]: [2012]] neveiks.

Aplink stulpelių antraštes su specialiais simboliais naudokite skliaustus Jei yra specialių simbolių, visa stulpelio antraštė turi būti rašoma skliausteliuose, o tai reiškia, kad stulpelio specifikatoriuje reikia dvigubų skliaustų. Pavyzdys: = DeptSalesFYSummary [[Total $ Amount]]

Čia pateikiamas specialiųjų simbolių, kuriems formulėse reikia papildomų skliaustų, sąrašas:

• Klavišas Tab
• Eilutės tiekimas
• Grįžimas į eilutės pradžią
• Kablelis (,)
• Dvitaškis (:)
• Taškas (.)
• Kairysis skliaustas ([)
• Dešinysis skliaustas (])
• Svaro ženklas (#)
• Viengubos kabutės ženklas (')
• Dvigubos kabutės ženklas (")
• Kairysis riestinis skliaustas ({)
• Dešinysis riestinis skliaustas (})
• Dolerio ženklas ($)
• Intarpas (^)
• Ženklas &
• Žvaigždutė (*)
• Pliuso ženklas (+)
• Lygybės ženklas (=)
• Minuso ženklas (-)
• Simbolis "daugiau nei" (>)
• Ženklas "mažiau nei" (<)
• Dalybos ženklas (/)
• Ženklas (@)
• Pasvirasis kairinis brūkšnys (\)
• Šauktukas (!)
• Kairysis skliaustas (()
• Dešinysis skliaustas ())
• Procento ženklas (%)
• Klaustukas (?)
• Pažymėtas kairinis ženklas (')
• Kabliataškis (;)
• Tildė (~)
• Pabraukimo simbolis (_)

• Kaitos simbolio naudojimas su kai kuriais specialiaisiais ženklais stulpelių antraštėse Kai kurie simboliai turi specialią reikšmę ir juos reikia naudoti su vienguba kabute (') kaip kaitos simboliu. Pavyzdys: =DeptSalesFYSummary['#OfItems]

Čia pateikiamas specialiųjų simbolių, kuriems formulėse reikia kaitos simbolio ('), sąrašas:

• Kairysis skliaustas ([)
• Dešinysis skliaustas (])
• Svaro ženklas (#)
• Viengubos kabutės ženklas (')
• Ženklas (@)

Tarpo simbolio naudojimas, norint pagerinti struktūrinių nuorodų skaitomumą Norėdami pagerinti struktūrinių nuorodų skaitomumą, galite naudoti tarpo simbolį. Pavyzdys:=DeptSales[ [Sales Person]:[Region] ] arba =DeptSales[[#Headers], [#Data], [% Commission]]

Rekomenduojama naudoti vieną tarpą:

• Po pirmojo kairiojo skliausto ([)
• Prieš paskutinį dešinįjį skliaustą (]).
• Po kablelio.

Nuorodų operatoriai

Norėdami padidinti langelių diapazonų nurodymo galimybes, galite naudoti toliau pateikiamus nuorodų operatorius, kad sujungtumėte stulpelių specifikatorius.

Struktūrinė nuoroda:	Nurodo:	Naudojama:	Langelių diapazonas:
=DeptSales[[Pardavėjas]:[Regionas]]	Visi langeliai dviejuose ar keliuose gretimuose stulpeliuose	: (dvitaškis) diapazono operatorius	A2:B7
=DeptSales[Pardavimo suma],DeptSales[Komisinių suma]	Dviejų ar kelių stulpelių kombinacija	, (kablelis) sąjungos operatorius	C2:C7, E2:E7
=DeptSales[[Pardavėjas]:[Pardavimo suma]] DeptSales[[Regionas]:[Komisiniai %]]	Dviejų ar kelių stulpelių sankirta	(tarpas) sankirtos operatorius	B2:C7

Specialių elementų specifikatoriai

Norėdami nurodyti konkrečias lentelės dalis, pvz., tik sumos eilutę, savo struktūrinėse nuorodose galite naudoti bet kurį iš šių specialiųjų elementų specifikatorių.

Specialusis elemento specifikatorius:	Nurodo:
#Visi	Visa lentelė, įskaitant stulpelių antraštes, duomenis ir sumas (jei tokių yra).
#Duomenys	Tik duomenų eilutės.
#Antraštės	Tik antraštės eilutė.
#Bendrosios sumos	Tik sumos eilutė. Jei tokios nėra, grąžinamas nulis.
#Ši eilutė arba @ arba @[Stulpelio pavadinimas]	Tik toje pačioje eilutėje esantys langeliai kaip formulė. Šių specifikatorių negalima jungti su jokiais kitais specialiųjų elementų specifikatoriais. Naudokite juos norėdami nutraukti numanomą nuorodos sankirtą arba nepaisyti numanomos sankirtos ir nurodyti pavienes stulpelio reikšmes. Lentelėse, kuriose yra daugiau nei viena duomenų eilutė, „Excel“ automatiškai pakeičia specifikatorių #Ši eilutė į trumpesnius @ specifikatorių. Bet jei lentelėje yra tik viena eilutė, programa "Excel" nepakeičia #This eilutės specifikatoriaus, todėl įtraukus daugiau eilučių gali būti gauti netikėti skaičiavimo rezultatai. Norėdami išvengti skaičiavimo problemų, prieš įvesdami struktūrinių nuoroda formules, įsitikinkite, kad įvedėte lentelėje kelias eilutes.

Kriterijus atitinkančios struktūrinės nuorodos apskaičiuojamuosiuose stulpeliuose

Kai kuriate apskaičiuotą stulpelį, dažniausiai naudojate struktūrinę nuorodą, kad sukurtumėte formulę. Ši struktūrinė nuoroda gali būti neatitinkanti kriterijų arba visiškai atitinkanti kriterijus. Pavyzdžiui, kad sukurtumėte apskaičiuojamąjį stulpelį, pavadintą Komisinių suma, kuris skaičiuoja komisinių sumą litais, galite naudoti šias formules:

Struktūrinės nuorodos tipas	Pavyzdys	Komentaras
Neatitinkanti kriterijų	=[Pardavimo suma]*[Komisiniai %]	Sudaugina atitinkamas reikšmes iš dabartinės eilutės.
Visiškai atitinkanti kriterijus	=DeptSales[Pardavimo suma]*DeptSales[Komisiniai %]	Sudaugina atitinkamas kiekvienos abiejų stulpelių eilutės reikšmes.

Pagrindinė taisyklė: jei naudojate struktūrines nuorodas lentelėje, pvz., kai kuriate apskaičiuojamąjį stulpelį, galite naudoti neatitinkančią kriterijų struktūrinę nuorodą. Jei struktūrinę nuorodą naudosite ne lentelėje, turite naudoti atitinkančią kriterijus struktūrinę nuorodą.

Struktūrinių nuorodų naudojimo pavyzdžiai

Čia pateikiami keli struktūrinių nuorodų naudojimo pavyzdžiai.

Struktūrinė nuoroda:	Nurodo:	Langelių diapazonas:
=DeptSales[[#Visi],[Pardavimo suma]]	Visi stulpelio „Pardavimo suma“ langeliai.	C1:C8
=DeptSales[[#Antraštės],[Komisiniai %]]	Stulpelio „Komisiniai %“ antraštė.	D1
=DeptSales[[#Totals],[Region]]	Regiono stulpelio suma. Jei nėra Sumų eilutės, grąžinamas nulis.	B8
=DeptSales[[#Visi],[Pardavimo suma]]]:[Komisiniai %]]	Visi „Pardavimo suma“ ir „Komisiniai %“ langeliai.	C1:D8
=DeptSales[[#Duomenys],[Komisiniai %]:[Komisinių suma]]	Tik stulpelių „Komisiniai %“ ir „Komisinių suma“ duomenys.	D2:E7
=DeptSales[[#Antraštės], [Regionas]: [Komisinių suma]]	Tik stulpelių nuo „Regionas“ iki „Komisinių suma“ antraštės.	B1:E1
=DeptSales[[#Bendrosios sumos], [Pardavimo suma]:[Komisinių suma]]	Stulpelių nuo „Pardavimo suma“ iki „Komisinių suma“ suma. Jei sumos eilutės nėra, grąžinamas nulis.	C8:E8
=DeptSales[[#Antraštės],[#Duomenys],[Komisiniai %]]	Tik antraštė ir „Komisinių %“ duomenys.	D1:D7
=DeptSales[[#Ši eilė], [Komisinių suma]] arba =DeptSales[@Komisinių suma]	Dabartinės eilutės ir stulpelio „Komisinių suma“ sankirtos langelis. Jei naudojama toje pačioje eilutėje, kurioje yra antraštė arba bendroji suma, grąžinama #VALUE! klaida. Jei struktūrinę nuorodą lentelėje su keliomis duomenų eilutėmis įvedate ilgesne forma (#Ši eilutė), „Excel“ automatiškai pakeičia ją trumpesne forma (@). Jos abi veikia taip pat.	E5 (jei dabartinė eilutė yra 5)

Darbo su struktūrinėmis nuorodomis strategijos

Kai dirbate su struktūrinėmis nuorodomis, atsižvelkite į toliau pateiktą informaciją.

• Formulės automatinio vykdymo naudojimas Galite pastebėti, kad formulių automatinio vykdymo naudojimas yra labai naudingas struktūrinėms nuorodoms įvesti ir užtikrinti teisingos sintaksės naudojimą. Daugiau informacijos ieškokite Formulės automatinio vykdymo naudojimas.

• Sprendimas, ar kurti lentelių pusiau pasirinkimo struktūrines nuorodas Pagal numatytuosius parametrus, kai kuriate formulę, lentelėje pasirinkus langelių diapazoną iš dalies pasirenkami langai ir vietoj langelio formulėje automatiškai įvedama struktūrinė nuoroda. Šis dalinis pasirinkimas palengvina struktūrinės nuorodos įvedimą. Jūs galite įjungti arba išjungti šią funkciją pažymėdami arba išvalydami žymės langelį Lentelių pavadinimų naudojimas formulėse, esantį dialogo lange Failo>parinktys>Formulės>.

• Darbaknygių su išoriniais saitais naudojimas su "Excel" lentelėmis, esančiomis kitose darbaknygėse Jei darbaknygėje yra išorinis saitas su "Excel" lentele, esančia kitoje darbaknygėje, ta susieto šaltinio darbaknygė turi būti atidaryta naudojant "Excel", kad būtų išvengta klaidų #REF! paskirties darbaknygėje, kurioje yra saitų. Jei pirmiau atidarysite paskirties darbaknygę, #REF bus rodomos klaidos, kurias galėsite ištaisyti atidarę šaltinio darbaknygę. Jei pirmiau atidarysite šaltinio darbaknygę, klaidos kodų turėtų nebūti.

• Diapazono konvertavimas į lentelę ir atvirkščiai Kai konvertuojate lentelę į diapazoną, visos langelių nuorodos konvertuojamos į lygiavertes absoliučiojo A1 stiliaus nuorodas. Kai konvertuojate diapazoną į lentelę, Excel automatiškai nepakeičia jokių šio diapazono langelių nuorodų į atitinkamas struktūrines nuorodas.

• Stulpelių antraščių išjungimas Galite įjungti arba išjungti lentelės stulpelių antraštes Lentelės dizaino skirtuko >antraštės eilutėje. Jei išjungsite lentelės stulpelių antraštes, struktūrinės nuorodos, naudojančios stulpelių pavadinimus, nebus paveiktos ir galėsite jas naudoti formulėse. Naudojant struktūrines nuorodas, kurios tiesiogiai nurodo lentelės antraštes (pvz., =DeptSales[[#Headers],[%Commission]]), gaunamas #REF.

• Stulpelių ir eilučių pridėjimas arba pašalinimas lentelėje Lentelės duomenų diapazonai dažnai keičiasi, todėl į struktūrinių nuorodų langelių nuorodas koreguojamos automatiškai. Pavyzdžiui, jei formulėje naudojate lentelės pavadinimą, kad suskaičiuotumėte duomenų langelius, ir jei tuomet pridedate duomenų eilutę, langelio nuoroda automatiškai koreguojama.

• Lentelės ar stulpelio pervardijimas Jei pervardijate stulpelį ar lentelę, „Excel“ automatiškai pakeičia tos lentelės ar stulpelio antraštės naudojimą visose struktūrinėse darbaknygės nuorodose.

• Struktūrinių nuorodų perkėlimas, kopijavimas ir užpildymas Kopijuojant arba perkeliant formulę, kurioje naudojamos struktūrinės nuorodos, visos struktūrinės nuorodos lieka tokios pačios.

  Pastaba

  Struktūrinės nuorodos kopijavimas ir struktūrinės nuorodos užpildymas nėra tas pats. Kai kopijuojate, visos struktūrinės nuorodos lieka tokios pačios, o pildant formulę, visiškai atitinkančios struktūrinės nuorodos koreguoja stulpelio specifikatorius kaip seką, kaip apibendrinta toliau pateiktoje lentelėje.

Jei pildymo kryptis yra:	Kol pildote, spauskite:	Tada:
Aukštyn arba žemyn	Nieko	Nėra jokio stulpelio specifikatoriaus koregavimo.
Aukštyn arba žemyn	Ctrl	Stulpelių specifikatoriai koreguojami kaip seka.
Dešinėn arba kairėn	Nėra	Stulpelių specifikatoriai koreguojami kaip seka.
Aukštyn, žemyn, dešinėn arba kairėn	Shift	Vietoj reikšmių dabartiniuose laukeliuose perrašymo, dabartinės laukelio reikšmės yra perkeliamos ir įterpiami stulpelio specifikatoriai.

Reikia daugiau pagalbos?

Visada galite kreiptis eksperto į "Excel" technologijų bendruomenę arba gauti pagalbos bendruomenėse.

Susijusios temos

„Excel“ lentelių apžvalga
Lentelių kūrimas ir formatavimas
„Excel“ lentelės duomenų suma
„Excel“ lentelės formatavimas
Lentelės dydžio keitimas įtraukiant stulpelių ir eilučių
Duomenų diapazono arba duomenų lentelėje filtravimas
Lentelės konvertavimas į diapazoną
"Excel" lentelių suderinamumo problemos
"Excel" lentelės eksportavimas į "SharePoint"
Programos "Excel" formulių apžvalgos