Viskas apie „Excel“ pavadintus diapazonus - „Excel“ diapazono pavadinimas

Anonim

Dirbdami su „Excel“ turite būti girdėję apie „Excel“ nurodytus diapazonus. Galbūt iš draugo, kolegos ar kokios nors internetinės pamokos. Net aš tai ne kartą minėjau savo straipsniuose. Šiame straipsnyje mes sužinosime apie „Excel“ pavadintus diapazonus ir išnagrinėsime visus jo aspektus.

Kas yra pavadintas diapazonas „Excel“?

Na, pavadinti diapazonai yra ne kas kita, kaip kai kurie „Excel“ diapazonai, pažymėti reikšmingu pavadinimu. Pvz., Jei turite langelį, sakykite B1, kuriame yra kasdienis tikslas, galite pavadinti tą langelį specialiai „Tikslas“. Dabar galite naudoti „Target“, kad nurodytumėte A1, o ne rašytumėte B1.
Trumpai tariant, pavadintas diapazonas yra tik diapazonų pavadinimai.

Kaip pavadinti diapazoną „Excel“?

Nustatykite vardą rankiniu būdu:

Norėdami apibrėžti diapazono pavadinimą, galite naudoti sparčiuosius klavišus CTRL+F3. Arba galite atlikti šiuos veiksmus.

    • Eikite į skirtuką Formulė
    • Raskite skyrių „Apibrėžti vardai“ ir spustelėkite „Apibrėžti vardus“. Bus atidarytas „Name Manger“.

    • Spustelėkite Naujas.
    • Įveskite Pavadinimas.

  • Pasirinkite apimtį (darbaknygę ar lapą)
  • Jei norite, parašykite komentarą.
  • Laukelyje „Nurodo langelį“ parašykite nuorodą arba pasirinkite diapazoną naudodami pelę.
  • Spustelėkite Gerai. Padaryta.

Dabar galite kreiptis į jį tiesiog įvesdami jo pavadinimą.
Kuriant pavadinimus reikia laikytis tam tikrų taisyklių. Jie yra

  1. Pavadinimai neturėtų prasidėti nuo skaitmenų ar specialiųjų simbolių, išskyrus pabraukimą (_) ir pasvirąjį brūkšnį (\).
  2. Pavadinimuose negali būti tarpų ir jokių specialių simbolių, išskyrus _ ir \.
  3. Diapazono negalima pavadinti langelių nuorodomis. Pavyzdžiui, A1, B1 arba AZ100 ir tt pavadinimai yra neteisingi.
  4. Negalite pavadinti diapazono „r“ ir „c“, nes jie skirti eilučių ir stulpelių nuorodoms.
  5. Du pavadinti diapazonai negali turėti to paties pavadinimo darbo knygoje.
  6. Tas pats diapazonas gali turėti kelis pavadinimus.

Automatiškai apibrėžkite vardą

Na, dažniausiai dirbsite su struktūrinių duomenų lentele. Jie turės stulpelius ir eilutes su stulpelių antraštėmis ir eilučių antraštėmis. Ir dažniausiai šie pavadinimai yra reikšmingi duomenims, ir jūs norite pavadinti savo diapazoną kaip šias stulpelių antraštes. „Excel“ suteikia įrankį pavadinti diapazonus automatiškai naudojant pavadinimus. Atlikite šiuos veiksmus.

    • Diapazonai, kuriuos norite įvardyti kaip jų antraštes
    • Paspauskite CTRL+SHIFT+F3, arba Raskite apibrėžtų vardų skyrių skirtuke „Formulė“ ir spustelėkite „Sukurti iš pasirinkimo“.

    • Bus parodytas žemiau esantis parinkčių langelis. „Top Row“ pasirinkau tik todėl, kad noriu pavadinti šį diapazoną kaip antraštę ir nenoriu pavadinti eilučių.

  • Spustelėkite Gerai.

Dabar kiekvienas stulpelis pavadintas jų antrašte. Kai rašote mašinėlę formulę, šie pavadinimai bus nurodyti pasirinktyje, kurią naudoti.

Diapazono pavadinimas naudojant „Excel“ lenteles

Kai „Excel“ tvarkome duomenis kaip lentelę naudodami „CTRL + T“, stulpelių antraštės automatiškai priskiriamos kaip atitinkamo stulpelio pavadinimas. Turėtumėte ištirti „Excel“ lenteles ir jų naudą.

Kaip pamatyti visus pavadintus diapazonus?

Na, kartais norėsite matyti visus galimus pavadintus diapazonus darbo knygoje. Norėdami pamatyti visus vardų diapazonus, paspauskite CTRL+F3. Arba galite eiti į Formulės skirtukas> Vardų tvarkyklė. Jame bus išvardyti visi pavadinti diapazonai, kuriuos galima rasti darbaknygėje. Galite redaguoti turimus pavadintus diapazonus, juos ištrinti, pridėti naujų pavadinimų.

Vieno diapazono keli pavadinimai

„Excel“ leidžia vartotojams tą patį diapazoną pavadinti skirtingais pavadinimais. Pavyzdžiui, diapazonas A2: A10 vienu metu gali būti pavadintas „Klientai“ ir „Klientai“. Abu pavadinimai nurodys tą patį diapazoną A2: A10.
Bet jūs negalite turėti tų pačių pavadinimų dviem skirtingiems diapazonams. Tai pašalina dviprasmybių galimybę.

Gaukite įvardytų diapazonų sąrašą lape

Taigi, jei norite turėti pavadintų diapazonų sąrašą ir diapazonus, kuriuos jie apima, galite naudoti šį spartųjį klavišą, kad įklijuotumėte juos lapo vietoje.

    • Pasirinkite langelį, kuriame norite gauti pavadintų diapazonų sąrašą.
    • Paspauskite F3. Tai atvers a Paste Pavadinimas dialogo langas.
    • Spustelėkite įklijuoti sąrašą mygtuką.

  • Sąrašas bus įklijuotas pasirinktame langelyje ir toliau.


Jei dukart spustelėsite pavadintų diapazonų pavadinimą įklijavimo pavadinimo lauke, jie bus parašyti kaip formulės langelyje. Pabandyk tai.

Rankiniu būdu atnaujinkite pavadintus diapazonus

Na, kai įterpiate langelį į pavadintą diapazoną, jis automatiškai atnaujinamas ir išplečiamas. Bet jei lentelės pabaigoje pridėsite duomenų, turėsite atnaujinti pavadintą lauką. Norėdami atnaujinti pavadintus diapazonus, atlikite šiuos veiksmus.

  • Paspauskite CTRL+F3, atidaryti vardų tvarkyklę.
  • Spustelėkite pavadintą diapazoną, kurį norite redaguoti. Spustelėkite Redaguoti.
  • Skiltyje „Nurodo stulpelį“ įveskite diapazoną, į kurį norite išplėsti, ir paspauskite Gerai.

Ir tai padaryta. Tai rankinis pavadintų diapazonų atnaujinimas. Tačiau mes galime padaryti jį dinamišką naudodami tam tikras formules.

Dinamiškai atnaujinkite pavadintus diapazonus

Išmintinga padaryti pavadintus diapazonus dinamiškus, kad nereikėtų jų redaguoti, kai duomenys viršija iš anksto nustatytą diapazoną.
Aš tai apžvelgiau atskirame straipsnyje „Dinaminiai pavadinti diapazonai“. Čia galite išsamiai sužinoti ir suprasti jo naudą.

Pavadintų diapazonų trynimas

Kai ištrinate tam tikrą pavadinto diapazono dalį, ji automatiškai koreguoja savo diapazoną. Bet kai ištrinate visą vardų diapazoną, jis išnyksta iš vardų sąrašo. Bet kuri formulė, priklausanti nuo šių diapazonų, rodys #REF klaidą arba suteiks neteisingą rezultatą (skaičiavimo funkcijos).
Dėl kokių nors priežasčių, jei norite ištrinti pavadintus diapazonus, atlikite šiuos veiksmus.

  • Paspauskite CTRL+F3. Bus atidarytas vardų tvarkytojas.
  • Pasirinkite pavadintus diapazonus, kuriuos norite ištrinti.
  • Spustelėkite mygtuką „Ištrinti“ arba klaviatūros mygtuką „Ištrinti“.


Atsargiai: Prieš ištrindami pavadintus diapazonus, įsitikinkite, kad jokios formulės nepriklauso nuo šių pavadinimų. Jei tokių yra, pirmiausia konvertuokite juos į diapazonus. Priešingu atveju pamatysite #REF klaidą.

Vardų ištrynimas su klaidomis

„Excel“ suteikia įrankį pašalinti tik klaidas turinčius pavadinimus. Jums nereikia identifikuoti kiekvieno iš jų. Jei norite ištrinti pavadinimus su klaidomis, atlikite šiuos veiksmus:

    • Atidarykite vardų tvarkyklę (CTRL+F3).
    • Spustelėkite išskleidžiamąjį meniu Filtras viršutiniame dešiniajame kampe.
    • Pasirinkite „Vardas su klaidomis“

  • Pasirinkite Visi ir paspauskite ištrynimo mygtuką.

Ir jų nebėra. Visi pavadinimai su klaidomis bus nedelsiant ištrinti iš įrašo.

Pavadinti diapazonai su formulėmis

Geriausiai pavadintus diapazonus galima naudoti naudojant formules. Formulės tampa lankstesnės ir lengviau skaitomos naudojant pavadintus diapazonus. Pažiūrėkime, kaip.

Lengva rašyti formules

Dabar tarkime, kad pavadinote diapazoną kaip „Elementai“. Dabar elementų sąraše norite suskaičiuoti „Pieštukai“. Su pavadinimu lengva parašyti šią COUNTIF formulę. Tiesiog rašyk

= COUNTIF (elementas, „pieštukas“)

Kai tik parašysite formulės pradinį skliaustą, pasirodys galimų pavadintų diapazonų sąrašas. Be pavadinimo jūs parašytumėte „Excel“ „gi COUNTIF“ funkciją su diapazonais, kuriems gali tekti pirmiausia pažvelgti į diapazoną, tada pasirinkti diapazoną arba įvesti jį į formulę.

„Excel“ aptarnauja galimus pavadinimų diapazonus.

Diapazonų pavadinimai rodomi kaip pasiūlymai, kai įvedate bet kurią raidę po = ženklo. Tas pats kaip „Excel“ rodo formulių sąrašą. Pavyzdžiui, jei įvedate = u, kiekvienas metodas ir pavadintas diapazonas bus rodomi pradedant u, kad galėtumėte juos lengvai naudoti.

Sudarykite konstantas naudodami pavadintus diapazonus

Iki šiol mes sužinojome apie diapazonų pavadinimus, tačiau iš tikrųjų galite įvardyti ir vertes. Pvz., Jei jūsų kliento vardas yra Sunder Pichai, galite pavadinti „Client“ ir tai reiškia „Sundar Pichai“. Dabar, kai bet kurioje ląstelėje rašysite = klientas, bus rodomas Sundar Pichai.


Ne tik tekstas, bet ir numeris, kurį galite priskirti pastoviam darbui. Pavyzdžiui, jūs nustatote tikslą. Arba to, kas nesikeis, vertė.
Absoliuti ir santykinė nuoroda su pavadintais diapazonais

Nuoroda į pavadintus diapazonus yra labai lanksti. Pavyzdžiui, jei santykiniame langelyje įrašysite pavadinto diapazono pavadinimą, jis elgsis kaip atitinkama nuoroda. Žiūrėkite žemiau esantį paveikslėlį.

Tačiau kai jį naudosite su formulėmis, jis elgsis kaip absoliutus. Na, dažniausiai juos naudosite su formulėmis, taigi galite pasakyti, kad jie pagal nutylėjimą yra Absoliutus, bet iš tikrųjų jie yra lankstūs.

Bet mes taip pat galime juos padaryti santykiniais.

Kaip sukurti santykinius pavadintus diapazonus „Excel“?

Tarkime, jei noriu pavadinti diapazoną „Prieš“, kuris nurodys langelį kairėje, kur jis parašytas. Kaip tai padaryti? Atlikite šiuos veiksmus:

  • Paspauskite CTRL+F3
  • Spustelėkite Naujas
  • Skiltyje „Vardas“ įveskite „Befor“.
  • Skiltyje „Nurodo:“ kairėje parašykite langelio adresą. Pavyzdžiui, jei esate B1 langelyje, skiltyje „Nurodo:“ parašykite „= A2“. Įsitikinkite, kad jame nėra $ ženklo.


Dabar, kad ir kur formulėje rašytumėte „Befor“, ji nurodys langelį, paliktą jai.

Čia aš anksčiau naudoju stulpelio funkciją. Formulė grąžina kairiojo langelio, kuriame jis parašytas, stulpelio numerį. Mano nuostabai, A1 rodo paskutinio stulpelio stulpelio numerį. Tai reiškia, kad lapas yra apskritas. Maniau, kad bus rodoma #REF klaida.

Suteikti pavadinimą dažnai naudojamoms formulėms?

Dabar šis yra nuostabus. Dažnai darbalapyje vėl ir vėl naudojate tą pačią formulę. Pavyzdžiui, galbūt norėsite patikrinti, ar vardas yra jūsų klientų sąraše, ar ne. Ir šis poreikis gali atsirasti daug kartų. Tam kiekvieną kartą parašysite tą pačią sudėtingą formulę.

= IF (COUNTIF (klientas, I3), „Sąraše“, „Ne sąraše“)

Ką daryti, jei langelyje tiesiog įvedate „= IsInCustomer“ ir jis parodys, ar kairėje esančio langelio vertė yra klientų sąraše, ar ne?

Pavyzdžiui, aš čia paruošiau lentelę. Dabar aš tiesiog noriu J5 įvesti „= IsInCustomer“ ir norėčiau pamatyti, ar vertė yra I5 yra klientų sąraše arba ne. Norėdami tai padaryti, atlikite šiuos veiksmus.

    • Paspauskite CTRL+F3
    • Spustelėkite Naujas
    • Varde parašykite „IsInCustomer“
    • Skiltyje „Nurodo“ parašykite savo formulę. = IF (COUNTIF (klientas, I5), „Sąraše“, „Ne sąraše“)

  • Paspauskite mygtuką Gerai.

Dabar, kad ir kur rašytumėte „IsInCustomer“, jis patikrins vertę kairiajame klientų sąrašo langelyje.

Tai neleidžia jums kartoti savęs vėl ir vėl.

Formulėms taikyti pavadintus diapazonus

Tiek kartų mes apibrėžiame savo diapazonų pavadinimus po to, kai jau parašėme formules pagal diapazonus. Pavyzdžiui, aš turiu bendrą kainą kaip langelius = E2*F2. Kaip mes galime tai pakeisti Vienetai*Vieneto_kaina.

    • Pasirinkite formules.
    • Eikite į formulės skirtuką. Spustelėkite išskleidžiamąjį meniu Apibrėžti vardą.

  • Spustelėkite Taikyti vardus.
  • Bus rodomas visų pavadintų diapazonų sąrašas. Pasirinkite tinkamus vardus ir spustelėkite Gerai.


Ir dabar pavadinimai taikomi. Tai galite pamatyti formulės juostoje.

Lengvai skaitomos formulės su pavadintais diapazonais

Kaip matėte, pavadinti diapazonai leidžia lengvai skaityti formules. Jei rašysiu = COUNTIF („A2: A100“, B2), niekas nesupras, ką aš bandau suskaičiuoti, kol nepamatys duomenų arba kas nors jiems to nepaaiškins.

Bet jei parašysiu = COUNTIF (regionas, „rytai“), dauguma vartotojų iškart supras, kad skaičiuojame „rytų“ atsiradimą regione, pavadintame diapazone.

Nešiojamos formulės

Įvardyti diapazonai leidžia labai lengvai kopijuoti ir įklijuoti formules, nesirūpinant dėl ​​nuorodų keitimo. Ir jūs galite perkelti vieną formulę iš vienos darbaknygės į kitą ir ji veiks gerai, kol paskirties darbaknygė neturės tų pačių pavadinimų.

Pavyzdžiui, jei turite formulę = COUNTIF (regionas, rytai) paskirstymo lentelėje ir turite kitą darbaknygę klientų kuris taip pat turi pavadintą diapazoną „Regionas“. Dabar, jei nukopijuosite šią formulę bet kur toje darbaknygėje, ji parodys teisingą informaciją. Duomenų struktūra nebus svarbi. Nesvarbu, kur, po velnių, yra jūsų darbo knygos stulpelis. Tai veiks teisingai.

Aukščiau esančiame paveikslėlyje aš naudoju tą pačią formulę dviejuose skirtinguose failuose, kad suskaičiuotų regionų sąraše esantį skaičių arba rytus. Dabar jie yra skirtinguose stulpeliuose, tačiau kadangi abu jie buvo pavadinti regionu, jis veiks puikiai.

Lengvai naršykite darbaknygėje

Darosi lengviau naršyti darbaknygėje su pavadintais diapazonais. Jums tiesiog reikia įvesti pavadinimą į pavadinimo lauką. „Excel“ nuves jus į diapazoną, nesvarbu, kur esate darbaknygėje. Atsižvelgiant į tai, kad nurodytas diapazonas yra darbaknygės apimtis.
Pvz., Jei esate 10 lape ir norite gauti klientų sąrašą ir nežinote, kuriame lape jis yra. Tiesiog eikite į pavadinimo laukelį ir įveskite „klientas“. Per sekundės dalį būsite nukreipti į nurodytą diapazoną.


Tai sumažins pastangas prisiminti diapazonus.

Naršykite naudodami hipersaitus su įvardytu diapazonu

Kai jūsų lapas yra didelis ir dažnai pereinate iš vieno taško į kitą, norėdami lengvai naršyti, mėgstate naudoti hipersaitus. Gerai pavadinti diapazonai gali puikiai veikti su hipersaitais. Norėdami pridėti hipersaitų naudodami pavadintus diapazonus, atlikite šiuos veiksmus.

    • Pasirinkite langelį, kuriame norite hipersaito
    • Paspauskite CTRL+K arba eikite į skirtuką Įterpti> HyperLink, kad atidarytumėte dialogo langą Įterpti hipersaitą.

    • Spustelėkite Vieta šiame dokumente.
    • Slinkite žemyn, kad pamatytumėte galimus pavadintus diapazonus skiltyje „Apibrėžti vardai“

  • Pasirinkite pavadintą diapazoną, kad įterptumėte hipersaitą į tą diapazoną.

Ir padaryta. Jūs turite savo hipersaitą į pasirinktą pavadintą diapazoną. Naudodami tai galite sukurti pavadintų diapazonų indeksą, kurį galite pamatyti ir spustelėti, jei norite tiesiogiai pereiti prie jų. Tai padarys jūsų darbaknygę tikrai patogią vartotojui.

Pavadintas diapazonas ir duomenų patvirtinimas

Įvardyti diapazonai ir duomenų patvirtinimas yra sukurti vienas kitam. Įvardyti diapazonai leidžia labai pritaikyti duomenų patvirtinimą. Daug lengviau pridėti patvirtinimą iš sąrašo naudojant pavadintą diapazoną. Pažiūrėkime, kaip…

    • Eikite į skirtuką Duomenys
    • Spustelėkite Duomenų patvirtinimas
    • Skiltyje „Leisti:“ pasirinkite Sąrašas
    • Skiltyje „Šaltinis:“ įveskite „= Klientas“ (parašykite bet kurį pavadintą diapazoną)

  • Spustelėkite Gerai

Dabar šioje ląstelėje bus klientų, priklausančių klientų pavadinimų diapazonui, vardai. Lengva, ar ne.

Priklausomas arba pakopinis duomenų patvirtinimas su įvardytais diapazonais

O kas, jei norite pakopinio ar priklausomo duomenų patvirtinimo. Pavyzdžiui, jei norite išskleidžiamojo sąrašo, kuriame yra kategorijos „Vaisiai ir daržovės“. Dabar, jei pasirinksite vaisius, kitame išskleidžiamajame meniu turėtų būti rodoma tik vaisių parinktis, o jei pasirinksite daržoves, tada tik daržovės.

Tai galima lengvai pasiekti naudojant pavadintus diapazonus. Išmokti kaip.

  • Priklausomas išskleidimas naudojant pavadintą diapazoną
  • Kiti pakopinių duomenų patvirtinimo būdai

Nėra duomenų patvirtinimo naudojant pateiktus duomenų pavadinimus

Nors „Excel“ lentelėse pateikiami struktūriniai pavadinimai, tačiau jų negalima naudoti su duomenų patvirtinimu ir sąlyginiu formatavimu. Nežinau, kodėl „Excel“ to neleidžia.
Bet tai nereiškia, kad to negalima padaryti. Galite pavadinti diapazonus lentelėje ir naudoti juos patvirtinimui. „Excel“ neturi jokių problemų.

Pavadintų diapazonų apimtis

Iki šiol kalbėjome apie pavadintus diapazonus, turinčius darbo knygos apimtį. Ką? Ar mes apie tai nekalbėjome? Gerai, todėl greitai suprasime, kas yra pavadintų diapazonų apimtis.

Kas yra vardų diapazonas?

Apimtis apibrėžia, kur galima atpažinti vardų diapazoną. Bet koks vardas negali būti atpažintas iš jo apimties. Pavyzdžiui, darbaknygėje1 esantis vardas negali būti atpažįstamas skirtingose ​​darbaknygėse. „Excel“ suteikia dvi parinktis nurodytų diapazonų darbams ir darbaknygėms.

Kaip apibrėžti pavadinto diapazono apimtį?

Kai sukuriate naują pavadinimų diapazoną, galite pamatyti skyrių „Apimtis:“. Spustelėkite išskleidžiamąjį meniu ir pasirinkite savo vardų diapazono apimtį. Sukūrę pavadintą diapazoną, apimties pakeisti negalite. Taigi geriau tai padaryti anksčiau. Pagal numatytuosius nustatymus tai yra darbaknygė.

Darbo knygos apimtis

Tai numatytoji diapazonas pavadintam diapazonui. Pavadinimas, apibrėžtas naudojant darbaknygės apimtį, gali būti naudojamas visoje darbo knygoje, kurioje jis apibrėžtas (ne kitose darbaknygėse).
Visi aukščiau pateikti pavyzdžiai turėjo darbo knygos apimtį.

Darbo lapas Apimtis

Pavadinimą, kuris yra apibrėžtas naudojant darbalapio apimtį, galima naudoti tik apibrėžti darbalapį. Pavyzdžiui, jei aš apibrėžiu „Iš viso“ visam langeliui su 1 lapo apimtimi. Tada suma bus pripažinta tik 1 lape. Kiti lapai neatpažins.

Noriu „Excel“ apimties

„Excel“ neturi „Global“ ar „Excel“ apimties. Tiesą sakant, norėčiau apibrėžti kai kuriuos vardus, kuriuos galima atpažinti visose mano sistemos darbo knygose. Jei kas nors žino, kaip tai padaryti, praneškite man.

Redagavimo apimtis sukūrus vardus

Jūs negalite. „Excel“ neleidžia redaguoti pavadinto diapazono apimties, kai tik sukuriate. Kadangi visi lape nurodyti diapazonai pagal numatytuosius nustatymus yra taikomi darbaknygei, todėl galbūt norėsite pakeisti jų apimtį į lapą.
Norėdami tai padaryti, tiesiog padarykite to lapo kopiją ir „Excel“ padarys kiekvieną to lapo pavadinimą vietiniu, kad išvengtumėte neaiškumų. Dabar, jei norite, galite ištrinti originalų lapą.

Iškirpti įklijuoti pavadinimų diapazoną

Kai supjaustote ir įklijuojate pavadintą diapazoną iš vienos paskirties vietos į kitą, nuoroda pasikeičia į naują vietą. Pavyzdžiui, jei A2: A10 turite pavadintą diapazoną „Klientas“ ir iškirpate bei įklijuojate į B2: B10, kliento vardas nurodys naują vietą B2: B10.

Dinaminiai pavadinti diapazonai „Excel“

17 nuostabių „Excel“ lentelių savybių

Populiarūs straipsniai:

50 „Excel“ nuorodų, skirtų produktyvumui padidinti

Kaip naudotis „VLOOKUP“ funkcija „Excel“

Kaip naudoti funkciją „COUNTIF“ programoje „Excel“

Kaip naudotis „SUMIF“ funkcija „Excel“