Kaip sukurti pavadintus diapazonus „Excel“ (žingsnis po žingsnio vadovas)

Kas yra pavadinime?

Jei dirbate su „Excel“ skaičiuoklėmis, tai gali reikšti daug laiko taupymo ir efektyvumo.

Šioje pamokoje sužinosite, kaip „Excel“ sukurti pavadintus diapazonus ir kaip juos naudoti norint sutaupyti laiko.

Pavadinti diapazonai „Excel“ - įvadas

Jei kas nors turi man paskambinti ar kreiptis į mane, jis naudos mano vardą (užuot sakęs, kad patinas yra tokioje ir tokioje vietoje su tokiu ir tokiu ūgiu ir svoriu).

Teisingai?

Panašiai „Excel“ galite suteikti langelio arba langelių diapazono pavadinimą.

Dabar, užuot naudoję langelio nuorodą (pvz., A1 arba A1: A10), galite tiesiog naudoti jai priskirtą pavadinimą.

Pavyzdžiui, tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau:

Jei šiame duomenų rinkinyje turite nurodyti diapazoną, kuriame yra data, formulėse turėsite naudoti A2: A11. Panašiai pardavimų atstovui ir pardavimui turėsite naudoti B2: B11 ir C2: C11.

Nors viskas gerai, kai turite tik keletą duomenų taškų, tačiau tuo atveju, jei turite didžiulius sudėtingus duomenų rinkinius, ląstelių nuorodų naudojimas duomenims nurodyti gali užtrukti.

Naudojant „Excel“ pavadintus diapazonus, lengva kreiptis į „Excel“ duomenų rinkinius.

Galite sukurti pavadintą diapazoną „Excel“ kiekvienai duomenų kategorijai ir tada naudoti šį pavadinimą, o ne ląstelių nuorodas. Pavyzdžiui, datos gali būti pavadintos „Data“, pardavimo atstovų duomenys - „SalesRep“, o pardavimo duomenys - „Pardavimai“.

Taip pat galite sukurti vieno langelio pavadinimą. Pvz., Jei langelyje yra pardavimo komisinių procentas, tą langelį galite pavadinti „Komisija“.

Įvardintų diapazonų kūrimo „Excel“ pranašumai

Čia pateikiami pavadinimų diapazonų naudojimo „Excel“ pranašumai.

Vietoj langelio nuorodų naudokite pavadinimus

Kai kuriate „Excel“ pavadintus diapazonus, galite naudoti šiuos pavadinimus, o ne langelių nuorodas.

Pavyzdžiui, aukščiau nurodytam duomenų rinkiniui galite naudoti = SUM (SALES) vietoj = SUM (C2: C11).

Pažvelkite į žemiau pateiktas formules. Vietoj langelių nuorodų naudojau pavadintus diapazonus.

• Pardavimų, kurių vertė didesnė nei 500, skaičius: = COUNTIF (pardavimai,> 500 ″)
• Visų Tomo atliktų pardavimų suma: = SUMIF („SalesRep“, „Tomas“, pardavimai)
• Džo uždirbtas komisinis mokestis (Joe pardavimai padauginti iš komisinių procentų):
  = SUMIF („SalesRep“, „Joe“, „Sales“)*Komisija

Jūs sutiktumėte, kad šias formules lengva sukurti ir jas lengva suprasti (ypač kai jomis dalinatės su kuo nors kitu arba patys peržiūrite.

Nereikia grįžti į duomenų rinkinį, kad pasirinktumėte langelius

Kitas svarbus privalumas naudojant „Excel“ pavadintus diapazonus yra tas, kad jums nereikia grįžti ir pasirinkti langelių diapazonų.

Galite tiesiog įvesti keletą to pavadinto diapazono abėcėlių, o „Excel“ parodys atitinkamus pavadintus diapazonus (kaip parodyta žemiau):

Pavadinti diapazonai daro formules dinamiškas

Naudodami „Excel“ pavadintus diapazonus, galite padaryti „Excel“ formules dinamiškas.

Pvz., Pardavimo komisinio mokesčio atveju vietoj 2,5%vertės galite naudoti pavadintą diapazoną.

Dabar, jei jūsų įmonė vėliau nuspręs padidinti komisinį mokestį iki 3%, galite tiesiog atnaujinti pavadintą diapazoną ir visi skaičiavimai bus automatiškai atnaujinami, kad atspindėtų naują komisinį mokestį.

Kaip sukurti pavadintus diapazonus „Excel“

Štai trys būdai, kaip sukurti pavadintus diapazonus „Excel“:

1 metodas - apibrėžti vardą

Štai žingsniai, kaip sukurti pavadintus diapazonus „Excel“ naudojant „Define Name“:

• Pasirinkite diapazoną, kuriam norite sukurti pavadintą diapazoną „Excel“.
• Eikite į Formulės -> Apibrėžkite vardą.
• Dialogo lange Naujas vardas įveskite vardą, kurį norite priskirti pasirinktam duomenų diapazonui. Apimtį galite nurodyti kaip visą darbaknygę arba konkretų darbalapį. Jei pasirinksite tam tikrą lapą, pavadinimo nebus kituose lapuose.
• Spustelėkite Gerai.

Tai sukurs pavadintą diapazoną SALESREP.

2 metodas: pavadinimo langelio naudojimas

• Pasirinkite diapazoną, kuriam norite sukurti pavadinimą (nesirinkite antraščių).
• Eikite į pavadinimo laukelį, esantį formulės juostos kairėje, ir įveskite pavadinimą, su kuriuo norite sukurti pavadintą diapazoną.
• Atminkite, kad čia sukurtas vardas bus prieinamas visai darbo knygai. Jei norite apsiriboti darbalapiu, naudokite 1 metodą.

3 metodas: naudokite parinktį „Sukurti iš pasirinkimo“

Tai yra rekomenduojamas būdas, kai turite duomenų lentelės pavidalu ir norite kiekvienam stulpeliui/eilutei sukurti pavadintą diapazoną.

Pavyzdžiui, žemiau esančiame duomenų rinkinyje, jei norite greitai sukurti tris pavadintus diapazonus (Data, Sales_Rep ir Sales), galite naudoti toliau nurodytą metodą.

Štai veiksmai, kaip greitai sukurti pavadintus diapazonus iš duomenų rinkinio:

• Pasirinkite visą duomenų rinkinį (įskaitant antraštes).
• Eikite į Formulės -> Sukurti iš pasirinkimo (Spartusis klavišas - „Control“ + „Shift“ + F3). Bus atidarytas dialogo langas „Sukurti vardus iš pasirinkimo“.
• Dialogo lange Sukurti vardus iš pasirinkimo pažymėkite parinktis, kuriose yra antraštės. Šiuo atveju mes pasirenkame tik viršutinę eilutę, nes antraštė yra viršutinėje eilutėje. Jei antraštes turite viršutinėje eilutėje ir kairiajame stulpelyje, galite pasirinkti abi. Panašiai, jei jūsų duomenys yra sutvarkyti, kai antraštės yra tik kairiajame stulpelyje, pažymėkite tik parinktį Kairysis stulpelis.

Tai sukurs tris pavadintus diapazonus - Data, Sales_Rep ir Sales.

Atminkite, kad jis automatiškai renka pavadinimus iš antraščių. Jei tarp žodžių yra tarpas, jis įterpia pabraukimą (nes tu negali turėti tarpų pavadintuose diapazonuose).

Įvardintų diapazonų pavadinimo konvencija „Excel“

Kurdami pavadintus diapazonus „Excel“, turite žinoti tam tikras pavadinimo taisykles:

• Pirmasis pavadinto diapazono simbolis turėtų būti raidė ir pabraukimo simbolis (_) arba pasviroji brūkšnys (\). Jei tai dar kažkas, tai parodys klaidą. Likę simboliai gali būti raidės, skaičiai, specialieji simboliai, taškas arba pabraukimas.
• Negalite naudoti pavadinimų, kurie taip pat reiškia „Excel“ langelių nuorodas. Pavyzdžiui, negalite naudoti AB1, nes tai taip pat yra langelio nuoroda.
• Kurdami pavadintus diapazonus, negalite naudoti tarpų. Pavyzdžiui, jūs negalite turėti pardavimo atstovo kaip pavadinto diapazono. Jei norite sujungti du žodžius ir sukurti pavadintą diapazoną, naudokite pabraukimą, tašką arba didžiąsias raides. Pavyzdžiui, galite turėti Sales_Rep, SalesRep arba SalesRep.
  • Kurdama pavadintus diapazonus, „Excel“ vienodai elgiasi su didžiosiomis ir mažosiomis raidėmis. Pvz., Jei sukuriate pavadinimą „SALES“, negalėsite sukurti kito pavadinto diapazono, pvz., „Pardavimai“ arba „Pardavimai“.
• Pavadinto diapazono ilgis gali būti iki 255 simbolių.

„Excel“ per daug pavadintų diapazonų? Nesijaudinkite

Kartais dideliuose duomenų rinkiniuose ir sudėtinguose modeliuose „Excel“ galite sukurti daug pavadintų diapazonų.

Ką daryti, jei neprisimenate sukurto pavadinto diapazono pavadinimo?

Nesijaudink - Štai keletas naudingų patarimų.

Visų pavadintų diapazonų pavadinimų gavimas

Atlikite šiuos veiksmus, kad gautumėte visų sukurtų pavadintų diapazonų sąrašą:

• Eikite į skirtuką Formulės.
• Grupėje „Apibrėžtas pavadinimas“ spustelėkite Naudoti formulėje.
• Spustelėkite „Įklijuoti vardus“.

Tai suteiks jums visų šios darbo knygos pavadintų diapazonų sąrašą. Norėdami naudoti pavadintą diapazoną (formulėse ar langelyje), dukart spustelėkite jį.

Rodomi atitinkantys pavadinti diapazonai

• Jei turite idėjų apie vardą, įveskite kelis pradinius simbolius, o „Excel“ parodys atitinkamų pavadinimų išskleidžiamąjį meniu.

Kaip redaguoti pavadintus diapazonus „Excel“

Jei jau sukūrėte pavadintą diapazoną, galite jį redaguoti atlikdami šiuos veiksmus:

• Eikite į skirtuką Formulės ir spustelėkite Vardų tvarkytuvė.
• Dialogo lange Pavadinimų tvarkyklė bus išvardyti visi tos darbaknygės pavadinti diapazonai. Dukart spustelėkite norimą redaguoti pavadintą diapazoną.
• Dialogo lange Redaguoti pavadinimą atlikite pakeitimus.
• Spustelėkite Gerai.
• Uždarykite dialogo langą Pavadinimų tvarkyklė.

Naudingi pavadinimo diapazono spartieji klavišai (F3 galia)

Štai keletas naudingų sparčiųjų klavišų, kurie bus naudingi dirbant su „Excel“ pavadintais diapazonais:

• Norėdami gauti visų pavadintų diapazonų sąrašą ir įklijuoti jį į formulę: F3
• Norėdami sukurti naują pavadinimą naudodami Vardų tvarkyklės dialogo langą: „Control“ + F3
• Norėdami sukurti pavadintus diapazonus iš pasirinkimo: „Control“ + „Shift“ + F3

Dinaminių pavadintų diapazonų kūrimas „Excel“

Iki šiol šioje pamokoje mes sukūrėme statinius pavadintus diapazonus.

Tai reiškia, kad šie pavadinti diapazonai visada nurodytų tą patį duomenų rinkinį.

Pvz., Jei A1: A10 buvo įvardytas kaip „Pardavimas“, tai visada reiškia A1: A10.

Jei pridėsite daugiau pardavimo duomenų, turėsite rankiniu būdu pereiti ir atnaujinti nurodytą diapazoną.

Nuolat besiplečiančių duomenų rinkinių pasaulyje tai gali užimti daug laiko. Kiekvieną kartą, kai gaunate naujų duomenų, gali tekti atnaujinti „Excel“ pavadintus diapazonus.

Norėdami išspręsti šią problemą, „Excel“ galime sukurti dinaminius pavadintus diapazonus, kurie automatiškai įtrauktų papildomus duomenis ir įtrauktų juos į esamą pavadintų diapazoną.

Pavyzdžiui, pavyzdžiui, jei pridėsiu du papildomus pardavimo duomenų taškus, dinaminis pavadintas diapazonas automatiškai nurodys A1: A12.

Tokį dinaminį pavadintą diapazoną galima sukurti naudojant „Excel INDEX“ funkciją. Užuot nurodę langelių nuorodas kurdami pavadintą diapazoną, mes nurodome formulę. Formulė automatiškai atnaujinama, kai duomenys pridedami arba ištrinami.

Pažiūrėkime, kaip sukurti dinaminius pavadintus diapazonus „Excel“.

Tarkime, kad pardavimo duomenys yra ląstelėje A2: A11.

Štai „Excel“ dinaminių pavadintų diapazonų kūrimo veiksmai:

1. 1. Eikite į skirtuką Formulė ir spustelėkite Apibrėžti vardą.
   2. Dialogo lange Naujas vardas įveskite:
      • Pavadinimas: Pardavimai
      • Taikymo sritis: darbo knyga
      • Nurodo: = $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100 “) ir„ ”)
   3. Spustelėkite Gerai.

Padaryta!

Dabar turite dinaminį pavadintą diapazoną pavadinimu „Pardavimas“. Tai būtų automatiškai atnaujinama, kai prie jo pridedate duomenų arba pašalinate duomenis.

Kaip veikia dinaminiai pavadinti diapazonai?

Norėdami paaiškinti, kaip tai veikia, turite šiek tiek daugiau sužinoti apie „Excel INDEX“ funkciją.

Dauguma žmonių naudoja INDEX, kad grąžintų vertę iš sąrašo pagal eilutės ir stulpelio numerį.

Tačiau funkcija INDEX turi ir kitą pusę.

Jis gali būti naudojamas grąžinti langelio nuorodą kai jis naudojamas kaip langelio nuorodos dalis.

Pavyzdžiui, čia yra formulė, kurią naudojome kurdami dinaminį pavadintą diapazoną:

= $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ 100 $, "" ir ""))

INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””)) -> Tikimasi, kad ši formulės dalis grąžins vertę (kuri būtų 10 -oji vertė iš sąrašo, atsižvelgiant į tai, kad yra dešimt elementų).

Tačiau, kai naudojamas prieš nuorodą (=2 USD:INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, “” & ””)))) grąžina nuorodą į langelį, o ne vertę.

Taigi, čia jis grįžta = $ A $ 2: $ A $ 11

Jei prie pardavimo stulpelio pridėsime dvi papildomas vertes, tai grąžins = $ A $ 2: $ A $ 13

Kai įtraukiate į sąrašą naujus duomenis, „Excel COUNTIF“ funkcija pateikia duomenų tuščių langelių skaičių. Funkcija INDEX naudoja šį skaičių, kad gautų paskutinio sąrašo elemento langelio nuorodą.

Pastaba:

• Tai veiktų tik tuo atveju, jei duomenyse nebūtų tuščių langelių.
• Anksčiau paimtame pavyzdyje aš priskyriau daug langelių (A2: A100) formulei pavadintam diapazonui. Tai galite koreguoti pagal savo duomenų rinkinį.

Taip pat galite naudoti funkciją OFFSET, kad sukurtumėte dinaminius pavadintus diapazonus programoje „Excel“, tačiau, kadangi OFFSET funkcija yra nepastovi, tai gali sukelti lėtą „Excel“ darbaknygę. Kita vertus, INDEX yra pusiau nepastovus, todėl tai yra geresnis pasirinkimas sukurti dinaminius pavadintus diapazonus „Excel“.

Jums taip pat gali patikti šie „Excel“ ištekliai:

• Nemokami „Excel“ šablonai.
• Nemokamas internetinis „Excel“ mokymas (7 dalių internetinis vaizdo kursas).
• Naudingi „Excel“ makrokomandų kodų pavyzdžiai.
• 10 Išplėstinių „Excel“ VLOOKUP pavyzdžių.
• Išskleidžiamojo sąrašo kūrimas „Excel“.
• Pavadinto diapazono kūrimas „Google“ skaičiuoklėse.
• Kaip nurodyti kitą „Excel“ lapą ar darbaknygę