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

Kai kuriate Excel ,Excel priskiria pavadinimą lentelei ir kiekvienai lentelės stulpelio antraštei. Į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, spustelėkite lentelės langelius, kuriuos norite nurodyti, kad nereikėtų įvesti langelio nuorodos formulėje. Naudokite toliau pateiktą duomenų pavyzdį, kad įveskime formulę, kuri automatiškai naudoja struktūrines nuorodas pardavimo komisinių sumai apskaičiuoti.

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žius aukščiau pateiktoje lentelėje, įskaitant stulpelių antraštes, ir įklijuokite juos į naujo darbalapio Excel langelį A1.

  2. Norėdami sukurti lentelę, pažymėkite bet kurį duomenų diapazono langelį ir paspauskite Ctrl + T.

  3. Įsitikinkite, kad pažymėtas laukas Mano lentelė su antraštėmis ir spustelėkite Gerai.

  4. Langelyje E2 įveskite lygybės ženklą (=), tada spustelėkite langelį C2.

    Formulės juostoje struktūrinės nuorodos [@[pardavimo suma]] rodoma po lygybės ženklo.

  5. Įveskite žvaigždutę (*) tiesiai po uždaromojo skliausto ir spustelė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 spustelė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 būtų > skirtukas Dizainas.

  2. Lauke Lentelės pavadinimas įveskite norimą pavadinimą ir paspauskite "Enter".

Duomenų pavyzdyje naudojome pavadinimą DeptSales.

Naudokite toliau nurodytas lentelių pavadinimų taisykles:

  • Naudoti galiojančius simbolius  Visada pradėkite vardą raide, pabraukimo simboliu (_), arba pasviru 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 į.

  • Langelių nuorodų nenaudokite  Pavadinimai negali būti tokie patys kaip langelio nuoroda, pvz., Z$100 arba R1C1.

  • Nenaudokite tarpo žodžiams atskirti  Tarpų pavadinime naudoti negalima. Galite naudoti pabraukimo simbolį (_) ir laikotarpį (.) kaip žodžių skyrikliai. Pvz., DeptSales, Sales_Tax arba First.Quarter.

  • Naudoti ne daugiau kaip 255 simbolius Lentelės pavadinime gali būti iki 255 simbolių.

  • Unikalių lentelių pavadinimų naudojimas Pasikartojančių pavadinimų naudoti negalima. „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 turėti lentelių, "PivotTable" ir diagramų derinį, būtų gerai priešdėlioti pavadinimus pagal objekto tipą. Pvz.: tbl_Sales lentelė, pt_Sales "PivotTable" pardavimo ir chrt_Sales diagramos arba ptchrt_Sales pardavimo PivotChart. Taip visus vardus išlaikysite vardų tvarkytuvės sąraše su eilės tvarka.

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

Taip pat galite įvesti arba keisti struktūrines nuorodas rankiniu būdu formulėje, bet norėdami tai padaryti, tai padės suprasti struktūrinės nuorodos 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 pasirinktinis 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:   [Pardavimo suma] ir [Komisiniųsuma] yra stulpelių specifikatoriai, naudojantys jų rodomi 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 specialieji elementų 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],[Pardavimo suma]] ir DeptSales[[#Data],[Komisinių suma]] yra struktūrinės nuorodos, vaizduojamos eilute, kuri prasideda lentelės pavadinimu ir baigiasi stulpelio specifika.

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. Pavyzdžiui: =DeptSales[[Sales Person]:[Region]]

  • Visos stulpelių antraštės yra teksto eilutės    Tačiau jiems nereikia kabučių, kai jos naudojamos struktūrinėje nuorodoje. 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

  • Linijinė informacijos santrauka

  • Gabenimo grąžinimas

  • Kablelis (,)

  • Dvitaškis (:)

  • Taškas (.)

  • Kairysis skliaustas ([)

  • Dešinis skliaustas (])

  • Svaro ženklas (#)

  • Viengulė kabutė (')

  • Dvigubos kabutės (")

  • Kairysis riestinis skliaustas ({)

  • Dešine riestinis skliaustas (})

  • Dolerio ženklas ($)

  • Injektas (^)

  • Ženklas &

  • Žvaigždutė (*)

  • Pliuso ženklas (+)

  • Lygybės ženklas (=)

  • Minuso ženklas (-)

  • Didesnis už simbolį (>)

  • Mažiau nei simbolis (<)

  • Dalybos ženklas (/)

  • 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šinis skliaustas (])

  • Svaras ženklas(#)

  • Viengulė kabutė (')

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į 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ų. Tačiau jei lentelėje yra tik viena eilutė, programa „Excel“ nekeičia specifiaktoriaus #Ši eilutė, todėl įtraukus daugiau eilučių skaičiavimo rezultatai gali būti netikėti. 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, norėdami sukurti apskaičiuojamąjį stulpelį, vadinamą Komisinių suma, kuris apskaičiuoja komisinių sumą doleriais, 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 kaip antraštė arba sumos eilutė, bus #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

Atsižvelkite į šiuos dalykus, kai dirbate su struktūrinėmis nuorodomis.

  • 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 žr. Formulės automatinio užbaigimo naudojimas.

  • Nuspręskite, ar generuoti struktūrines lentelių nuorodas pusiau pasirinkimuose    Pagal numatytuosius nustatymus kuriant formulę, spustelėjus langelių diapazoną lentelėje, langeliai iš dalies pažymimi ir formulėje automatiškai įvedama struktūrinė nuoroda, o ne langelių diapazonas. Šis dalinis pasirinkimas palengvina struktūrinės nuorodos įvedimą. Šį veikimą galite įjungti arba išjungti pažymėdami arba išvalydami žymės langelį Naudoti lentelių pavadinimus formulėse dialogo lange > Parinktys > Formulės > Darbas su formulėmis.

  • Darbaknygių naudojimas su išoriniais saitais Excel lentelių kitose darbaknygėse    Jei darbaknygėje yra išorinis saitas su kitos darbaknygės Excel lentele, susieta šaltinio darbaknygė turi būti atidaryta "Excel", kad būtų išvengta #REF! paskirties darbaknygės klaidų, kurioje yra saitai. Jei pirmiausia atidarysite paskirties darbaknygę ir #REF! bus rodomos klaidos, jos bus išspręstos, jei atidarysite šaltinio darbaknygę. Jei pirmiausia atidarysite šaltinio darbaknygę, nematysite jokių klaidų kodų.

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

  • Stulpelių antraščių išjungimas    Galite įjungti ir išjungti lentelės stulpelių antraštes lentelės skirtuke Dizainas > Antraštės eilutė. Jei išjungsite lentelės stulpelių antraštes, struktūrinės nuorodos, kurios naudoja stulpelių pavadinimus, nebus paveiktos ir vis tiek galėsite jas naudoti formulėse. Struktūrinės nuorodos, tiesiogiai susijusios su lentelės antraštėmis (pvz., =DeptSales[[#Headers],[%Commission]]), bus #REF.

  • Stulpelių ir eilučių pridėjimas arba pašalinimas lentelėje    Lentelės duomenų diapazonai dažnai keičia, todėl struktūrinių nuorodų langelių nuorodos 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 vienodos, o pildant formulę, visiškai apibrėžtos struktūrinės nuorodos koreguoja stulpelių specifikacijas, pvz., sekas, 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 paprašyti specialisto iš „Excel“ technologijų bendruomenės, gauti pagalbos iš Atsakymų bendruomenės arba siūlyti naują funkciją ar patobulinimą „Excel“ vartotojo balse.

Susijusios temos

Lentelių Excel apžvalga
Vaizdo įrašas: lentelės kūrimas ir Excel formatavimas
Lentelės duomenų Excel suma
Lentelės Excel formatavimas
Lentelės dydžio keitimas įtraukiant arba šalinant eilutes ir stulpelius
Duomenų filtravimas diapazone arba lentelėje
Lentelės konvertavimas į diapazoną
Excel lentelės suderinamumo problemos
Eksportuoti lentelę Excel į SharePoint
Formulių apžvalgos Excel

Reikia daugiau pagalbos?

Tobulinkite savo „Office“ įgūdžius
Ieškoti mokymo
Pirmiausia gaukite naujų funkcijų
Prisijunkite prie „Office Insider“ dalyvių

Ar ši informacija buvo naudinga?

Dėkojame už jūsų atsiliepimus!

Dėkojame už jūsų atsiliepimą! Panašu, kad gali būti naudinga jus sujungti su vienu iš mūsų „Office“ palaikymo agentų.

×