Kako ustvariti poimenovana območja v Excelu (Vodnik po korakih)

Kaj je v imenu?

Če delate z Excelovimi preglednicami, bi to lahko pomenilo veliko prihranka časa in učinkovitosti.

V tej vadnici se boste naučili, kako ustvariti imenovane obsege v Excelu in kako jih uporabiti za prihranek časa.

Imenovani obsegi v Excelu - uvod

Če me mora nekdo poklicati ali se obrniti name, bo uporabil moje ime (namesto da bi rekel, da moški biva v tem in takem mestu s tako in tako višino in težo).

Prav?

Podobno lahko v Excelu daste ime celici ali obsegu celic.

Zdaj lahko namesto sklica na celico (na primer A1 ali A1: A10) preprosto uporabite ime, ki ste mu ga dodelili.

Recimo, da imate nabor podatkov, kot je prikazano spodaj:

Če se morate v tem nizu podatkov sklicevati na obseg z datumom, boste morali v formulah uporabiti A2: A11. Podobno boste pri prodajnem predstavniku in prodaji morali uporabiti B2: B11 in C2: C11.

Čeprav imate v redu le nekaj podatkovnih točk, v primeru, da imate ogromno kompleksnih podatkovnih nizov, pa lahko uporaba sklicev na celice za sklicevanje na podatke traja veliko časa.

Excel Named Ranges olajša sklicevanje na niz podatkov v Excelu.

V Excelu lahko ustvarite poimenovano območje za vsako kategorijo podatkov in nato uporabite to ime namesto sklicev na celice. Na primer, datumi se lahko imenujejo „datum“, podatki prodajnega predstavnika se lahko imenujejo „SalesRep“, podatki o prodaji pa se lahko imenujejo „prodaja“.

Ustvarite lahko tudi ime za eno celico. Če imate na primer odstotek prodajne provizije v celici, jo lahko poimenujete kot »provizija«.

Prednosti ustvarjanja imenovanih obsegov v Excelu

Tu so prednosti uporabe imenovanih obsegov v Excelu.

Namesto sklicev na celice uporabite imena

Ko ustvarite imenovane obsege v Excelu, lahko uporabite ta imena namesto sklicev na celice.

Za zgornji nabor podatkov lahko na primer uporabite = SUM (SALES) namesto = SUM (C2: C11).

Oglejte si spodaj navedene formule. Namesto referenc celic sem uporabil Named Ranges.

• Število prodaj z vrednostjo več kot 500: = COUNTIF (Prodaja, ”> 500 ″)
• Vsota vse prodaje, ki jo je opravil Tom: = SUMIF (SalesRep, »Tom«, Sales)
• Provizijo, ki jo je zaslužil Joe (prodaja Joea pomnožena z odstotkom provizije):
  = SUMIF (SalesRep, »Joe«, Sales)*Provizija

Strinjate se, da so te formule enostavne za ustvarjanje in razumljive (še posebej, če jih delite z nekom drugim ali jih ponovno obiščete.

Za izbiro celic ni treba iti nazaj v nabor podatkov

Druga pomembna prednost uporabe imenovanih obsegov v Excelu je, da se vam ni treba vrniti in izbrati obsegov celic.

Lahko samo vnesete nekaj abeced tega imenovanega obsega in Excel bo prikazal ujemajoča se imenovana območja (kot je prikazano spodaj):

Imenovani razponi naredijo formule dinamične

Z uporabo imenovanih obsegov v Excelu lahko formule Excel naredite dinamične.

Na primer, v primeru provizije od prodaje lahko namesto vrednosti 2,5%uporabite imenovan obseg.

Če se vaše podjetje pozneje odloči povečati provizijo na 3%, lahko preprosto posodobite Named Range in vsi izračuni se bodo samodejno posodobili, da bodo odražali novo provizijo.

Kako ustvariti imenovana območja v Excelu

Tukaj so trije načini za ustvarjanje imenovanih obsegov v Excelu:

Metoda #1 - Uporaba Define Name

Tukaj so koraki za ustvarjanje imenovanih obsegov v Excelu z uporabo Define Name:

• Izberite obseg, za katerega želite ustvariti imenovani obseg v Excelu.
• Pojdite na Formule -> Določi ime.
• V pogovorno okno Novo ime vnesite Ime, ki ga želite dodeliti izbranemu obsegu podatkov. Obseg lahko podate kot celoten delovni zvezek ali poseben delovni list. Če izberete določen list, ime ne bi bilo na voljo na drugih listih.
• Kliknite V redu.

Tako bo ustvarjen imenovani obseg SALESREP.

Metoda #2: Uporaba polja z imenom

• Izberite obseg, za katerega želite ustvariti ime (ne izberite glave).
• Pojdite v polje z imenom na levi strani vrstice formule in vnesite ime, s katerim želite ustvariti imenovano območje.
• Upoštevajte, da bo tukaj ustvarjeno ime na voljo za celoten delovni zvezek. Če ga želite omejiti na delovni list, uporabite 1. metodo.

Metoda #3: Uporaba možnosti Ustvari iz izbire

To je priporočljiv način, ko imate podatke v tabelarni obliki in želite ustvariti imenovani obseg za vsak stolpec/vrstico.

Če želite na primer v spodnjem naboru podatkov hitro ustvariti tri imenovane obsege (Date, Sales_Rep in Sales), lahko uporabite spodnjo metodo.

Tu so koraki za hitro ustvarjanje poimenovanih obsegov iz nabora podatkov:

• Izberite celoten nabor podatkov (vključno z glavami).
• Pojdite na Formule -> Ustvari iz izbire (Bližnjica na tipkovnici - Control + Shift + F3). Odprlo se bo pogovorno okno »Ustvari imena iz izbora«.
• V pogovornem oknu Ustvari imena iz izbire preverite možnosti, pri katerih imate glave. V tem primeru izberemo samo zgornjo vrstico, saj je glava v zgornji vrstici. Če imate glave v zgornji vrstici in levem stolpcu, lahko izberete oboje. Podobno, če so vaši podatki urejeni, ko so glave samo v levem stolpcu, potem preverite le možnost Levi stolpec.

Tako se ustvarijo tri imenovana območja - Datum, Prodaja_Rep in Prodaja.

Upoštevajte, da samodejno pobere imena iz glav. Če je med besedami presledek, vstavi podčrtaj (saj v imenovanih obsegih ne morete imeti presledkov).

Konvencija o imenovanju imenovanih območij v Excelu

Pri ustvarjanju imenovanih obsegov v Excelu morate poznati določena pravila poimenovanja:

• Prvi znak imenovanega obsega mora biti črka in podčrtaj (_) ali poševnica (\). Če je kaj drugega, se prikaže napaka. Preostali znaki so lahko črke, številke, posebni znaki, pika ali podčrtaj.
• V Excelu ne morete uporabljati imen, ki predstavljajo tudi sklice na celice. Na primer, AB1 ne morete uporabiti, saj je tudi referenca celice.
• Med ustvarjanjem poimenovanih obsegov ne morete uporabljati presledkov. Na primer, prodajnega predstavnika ne morete imenovati kot imenovanega obsega. Če želite združiti dve besedi in ustvariti imenovani obseg, ga ustvarite s podčrtajem, piko ali velikimi črkami. Na primer, lahko imate Sales_Rep, SalesRep ali SalesRep.
  • Med ustvarjanjem poimenovanih obsegov Excel na enak način obravnava velike in male črke. Če na primer ustvarite poimenovani obseg PRODAJA, potem ne boste mogli ustvariti drugega poimenovanega obsega, na primer „prodaja“ ali „Prodaja“.
• Imenovani obseg je lahko dolg do 255 znakov.

Preveč imenovanih obsegov v Excelu? Ne skrbite

Včasih lahko v velikih naborih podatkov in zapletenih modelih ustvarite veliko imenovanih obsegov v Excelu.

Kaj, če se ne spomnite imena imenovanega območja, ki ste ga ustvarili?

Ne skrbite - tukaj je nekaj koristnih nasvetov.

Pridobivanje imen vseh imenovanih območij

Tukaj je nekaj korakov za pridobitev seznama vseh poimenovanih obsegov, ki ste jih ustvarili:

• Pojdite na zavihek Formule.
• V skupini Defined Named kliknite Uporabi v formuli.
• Kliknite »Prilepi imena«.

Tako boste dobili seznam vseh imenovanih obsegov v tem delovnem zvezku. Če želite uporabiti imenovani obseg (v formulah ali celici), ga dvokliknite.

Prikaz ujemajočih se imenovanih obsegov

• Če imate kakšno idejo o imenu, vnesite nekaj začetnih znakov in Excel bo prikazal spustni meni ustreznih imen.

Kako urediti imenovane obsege v Excelu

Če ste že ustvarili imenovano območje, ga lahko uredite z naslednjimi koraki:

• Pojdite na zavihek Formule in kliknite Upravitelj imen.
• V pogovornem oknu Upravitelj imen bodo navedena vsa imenovana območja v tem delovnem zvezku. Dvokliknite imenovan obseg, ki ga želite urediti.
• V pogovornem oknu Urejanje imena naredite spremembe.
• Kliknite V redu.
• Zaprite pogovorno okno Upravitelj imen.

Koristne bližnjice do imenovanega obsega (moč F3)

Tukaj je nekaj uporabnih bližnjic na tipkovnici, ki vam bodo prišle prav, ko delate z Named Ranges v Excelu:

• Če želite dobiti seznam vseh imenovanih območij in ga prilepiti v formulo: F3
• Novo ime ustvarite s pomočjo pogovornega okna Upravitelj imen: Control + F3
• Če želite ustvariti imenovane obsege iz izbora: Control + Shift + F3

Ustvarjanje dinamičnih imenovanih obsegov v Excelu

Doslej smo v tej vadnici ustvarili statična imenovana območja.

To pomeni, da bi se ti imenovani obsegi vedno nanašali na isti nabor podatkov.

Na primer, če je bil A1: A10 poimenovan kot „prodaja“, bi se to vedno nanašalo na A1: A10.

Če dodate več podatkov o prodaji, boste morali ročno posodobiti sklic v imenovanem obsegu.

V svetu vedno večjih naborov podatkov vam lahko to vzame veliko časa. Vsakič, ko dobite nove podatke, boste morda morali posodobiti imenovane obsege v Excelu.

Za reševanje te težave lahko v Excelu ustvarimo dinamične imenovane obsege, ki bi samodejno upoštevali dodatne podatke in jih vključili v obstoječi imenovani obseg.

Na primer, če na primer dodam dve dodatni prodajni točki prodaje, bi se dinamični poimenovani obseg samodejno skliceval na A1: A12.

To vrsto dinamičnega imenovanega obsega lahko ustvarite s funkcijo Excel INDEX. Namesto podajanja sklicev na celice pri ustvarjanju imenovanega obsega podajamo formulo. Formula se samodejno posodobi, ko se podatki dodajo ali izbrišejo.

Poglejmo, kako ustvariti dinamična imenovana območja v Excelu.

Recimo, da imamo podatke o prodaji v celici A2: A11.

Tu so koraki za ustvarjanje dinamičnih imenovanih obsegov v Excelu:

1. 1. Pojdite na zavihek Formula in kliknite Določi ime.
   2. V pogovorno okno Novo ime vnesite naslednje:
      • Ime: Prodaja
      • Področje uporabe: Delovni zvezek
      • Nanaša se na: = $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))
   3. Kliknite V redu.

Končano!

Zdaj imate dinamično poimenovano območje z imenom 'Prodaja'. To se bo samodejno posodobilo vsakič, ko mu dodate podatke ali odstranite podatke.

Kako delujejo dinamična imenovana območja?

Če želite razložiti, kako to deluje, morate vedeti nekaj več o funkciji Excel INDEX.

Večina ljudi uporablja INDEX za vrnitev vrednosti s seznama glede na številko vrstice in stolpca.

Toda funkcija INDEX ima tudi drugo plat.

Lahko je vajen vrne sklic na celico kadar se uporablja kot del sklica na celico.

Tu je na primer formula, ki smo jo uporabili za ustvarjanje dinamičnega poimenovanega obsega:

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

INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””) -> Ta del formule naj bi vrnil vrednost (ki bi bila deseta vrednost s seznama, glede na to, da obstaja deset elementov).

Če pa se uporablja pred referenco (=$ 2 $:INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))) vrne sklic na celico namesto vrednosti.

Zato se tukaj vrne = $ A $ 2: $ A $ 11

Če v stolpec za prodajo dodamo dve dodatni vrednosti, bi se vrnilo = $ A $ 2: $ A $ 13

Ko na seznam dodate nove podatke, funkcija Excel COUNTIF vrne število nepraznih celic v podatkih. To številko funkcija INDEX uporablja za pridobivanje sklica na celico zadnjega elementa na seznamu.

Opomba:

• To bi delovalo le, če v podatkih ni praznih celic.
• V zgornjem primeru sem formuli imenovanega območja dodelil veliko število celic (A2: A100). To lahko prilagodite glede na nabor podatkov.

Funkcijo OFFSET lahko uporabite tudi za ustvarjanje dinamičnih imenovanih obsegov v Excelu, ker pa je funkcija OFFSET nestanovitna, lahko vodi počasen delovni zvezek Excel. Po drugi strani je INDEX polhlapna, zato je boljša izbira za ustvarjanje dinamičnih imenovanih obsegov v Excelu.

Morda vam bodo všeč tudi naslednji viri programa Excel:

• Brezplačne predloge programa Excel.
• Brezplačno spletno usposabljanje za Excel (7-delni spletni video tečaj).
• Primeri uporabnih Excelovih makro kod.
• 10 naprednih primerov naprednega Excelovega VLOOKUP -a.
• Ustvarjanje spustnega seznama v Excelu.
• Ustvarjanje imenovanega obsega v Google Preglednicah.
• Kako se sklicevati na drug list ali delovni zvezek v Excelu