Kako stvoriti imenovane raspone u Excelu (Vodič korak po korak)

Što je u imenu?

Ako radite s Excel proračunskim tablicama, to bi moglo značiti veliku uštedu vremena i učinkovitost.

U ovom ćete vodiču naučiti kako stvoriti Named Ranges u Excelu i kako ga koristiti za uštedu vremena.

Imenovani rasponi u Excelu - uvod

Ako me netko mora nazvati ili uputiti na mene, upotrijebit će moje ime (umjesto da kaže da mužjak boravi na tom i takvom mjestu s toliko i toliko visine i težine).

Pravo?

Slično, u Excelu možete dati ime ćeliji ili rasponu ćelija.

Sada, umjesto da koristite referencu ćelije (poput A1 ili A1: A10), možete jednostavno upotrijebiti naziv koji ste joj dodijelili.

Na primjer, pretpostavimo da imate skup podataka kako je prikazano u nastavku:

U ovom skupu podataka, ako se morate odnositi na raspon koji ima Datum, morat ćete koristiti A2: A11 u formulama. Slično, za predstavnika prodaje i prodaju morat ćete koristiti B2: B11 i C2: C11.

Iako je u redu ako imate samo nekoliko podatkovnih točaka, ali u slučaju da imate velike složene skupove podataka, korištenje referenci ćelija za upućivanje na podatke moglo bi oduzeti mnogo vremena.

Excel Named Ranges olakšava pozivanje na skupove podataka u Excelu.

Možete stvoriti imenovani raspon u Excelu za svaku kategoriju podataka, a zatim koristiti taj naziv umjesto referenci ćelije. Na primjer, datumi se mogu nazvati "Datum", podaci prodajnog predstavnika mogu se nazvati "SalesRep", a podaci o prodaji mogu se nazvati "Prodaja".

Također možete stvoriti naziv za jednu ćeliju. Na primjer, ako u ćeliji imate postotak provizije od prodaje, toj ćeliji možete dati naziv "Provizija".

Prednosti stvaranja imenovanih raspona u Excelu

Evo prednosti korištenja imenovanih raspona u Excelu.

Koristite imena umjesto referenci na ćelije

Prilikom stvaranja Imenovanih raspona u Excelu možete koristiti te nazive umjesto referenci ćelija.

Na primjer, za gornji skup podataka možete koristiti = SUM (SALES) umjesto = SUM (C2: C11).

Pogledajte dolje navedene formule. Umjesto referenci ćelija, koristio sam Named Ranges.

• Broj prodaja s vrijednošću većom od 500: = COUNTIF (Prodaja, ”> 500 ″)
• Zbroj sve prodaje koju je obavio Tom: = SUMIF (SalesRep, ”Tom”, Sales)
• Proviziju koju je zaradio Joe (prodaja Joea pomnožena s postotkom provizije):
  = SUMIF (SalesRep, "Joe", Sales)*Provizija

Složićete se da je ove formule lako stvoriti i razumjeti (posebno ako ih podijelite s nekim drugim ili ih ponovno posjetite.

Nema potrebe za povratkom na skup podataka za odabir ćelija

Druga značajna prednost korištenja Imenovanih raspona u Excelu je ta što se ne morate vraćati i birati raspone ćelija.

Možete samo unijeti nekoliko abeceda tog imenovanog raspona i Excel će pokazati odgovarajuće imenovane raspone (kao što je prikazano u nastavku):

Imenovani rasponi čine formule dinamičnima

Korištenjem Imenovanih raspona u Excelu možete Excelove formule učiniti dinamičkim.

Na primjer, u slučaju provizije od prodaje, umjesto korištenja vrijednosti 2,5%, možete koristiti Named Range.

Sada, ako vaša tvrtka kasnije odluči povećati proviziju na 3%, možete jednostavno ažurirati Named Range, a svi izračuni će se automatski ažurirati kako bi odražavali novu proviziju.

Kako stvoriti imenovane raspone u Excelu

Evo tri načina za stvaranje imenovanih raspona u Excelu:

Metoda #1 - Korištenje Define Name

Evo koraka za stvaranje imenovanih raspona u Excelu pomoću Definiraj naziv:

• Odaberite raspon za koji želite stvoriti Imenovani raspon u Excelu.
• Idite na Formule -> Definiraj naziv.
• U dijaloški okvir Novo ime upišite Ime koje želite dodijeliti odabranom rasponu podataka. Opseg možete navesti kao cijelu radnu knjigu ili određeni radni list. Ako odaberete određeni list, naziv neće biti dostupan na drugim listovima.
• Pritisnite U redu.

Ovo će stvoriti Imenovani raspon SALESREP.

Metoda 2: Korištenje okvira s imenom

• Odaberite raspon za koji želite stvoriti naziv (nemojte birati zaglavlja).
• Idite u okvir s imenom lijevo od trake formule i upišite naziv s kojim želite stvoriti imenovani raspon.
• Imajte na umu da će Ime stvoreno ovdje biti dostupno za cijelu Radnu knjigu. Ako ga želite ograničiti na radni list, upotrijebite 1. metodu.

Metoda #3: Korištenje opcije Create from Selection

Ovo je preporučeni način kada imate podatke u tabličnom obliku i želite stvoriti imenovani raspon za svaki stupac/redak.

Na primjer, u donjem skupu podataka, ako želite brzo stvoriti tri imenovana raspona (Date, Sales_Rep i Sales), tada možete upotrijebiti dolje prikazanu metodu.

Evo koraka za brzo stvaranje imenovanih raspona iz skupa podataka:

• Odaberite cijeli skup podataka (uključujući zaglavlja).
• Idite na Formule -> Stvori iz odabira (Tipkovni prečac - Control + Shift + F3). Otvorit će se dijaloški okvir "Stvori imena iz odabira".
• U dijaloškom okviru Stvaranje imena iz odabira provjerite opcije na kojima imate zaglavlja. U ovom slučaju odabiremo gornji redak samo jer je zaglavlje u gornjem retku. Ako imate zaglavlja i u gornjem retku i u lijevom stupcu, možete odabrati oboje. Slično, ako su vaši podaci raspoređeni kada su zaglavlja samo u lijevom stupcu, tada provjeravate samo opciju Lijevi stupac.

To će stvoriti tri imenovana raspona - Datum, Sales_Rep i Sales.

Imajte na umu da automatski preuzima imena iz zaglavlja. Ako postoji razmak između riječi, unosi se donja crta (jer ne možete imati razmake u imenovanim rasponima).

Konvencija o imenovanju imenovanih raspona u Excelu

Prilikom stvaranja Imenovanih raspona u Excelu morate znati određena pravila imenovanja:

• Prvi znak Imenovanog raspona trebao bi biti slovo i znak podcrtavanja (_) ili kosa crta (\). Ako je bilo što drugo, prikazat će grešku. Preostali znakovi mogu biti slova, brojevi, posebni znakovi, točka ili podcrtavanje.
• Ne možete koristiti nazive koji također predstavljaju reference ćelija u Excelu. Na primjer, ne možete koristiti AB1 jer je to i referenca ćelije.
• Ne možete koristiti razmake pri stvaranju imenovanih raspona. Na primjer, ne možete imati predstavnika prodaje kao imenovani raspon. Ako želite kombinirati dvije riječi i stvoriti Imenovani raspon, upotrijebite donju crtu, točku ili velika slova za njegovo stvaranje. Na primjer, možete imati Sales_Rep, SalesRep ili SalesRep.
  • Prilikom stvaranja imenovanih raspona, Excel na isti način tretira velika i mala slova. Na primjer, ako kreirate imenovani raspon PRODAJA, tada nećete moći stvoriti drugi imenovani raspon kao što je 'prodaja' ili 'Prodaja'.
• Imenovani raspon može imati najviše 255 znakova.

Previše imenovanih raspona u Excelu? Ne brinite

Ponekad u velikim skupovima podataka i složenim modelima možete stvoriti mnogo imenovanih raspona u Excelu.

Što ako se ne sjećate naziva Imenovanog raspona koji ste stvorili?

Ne brinite - evo nekoliko korisnih savjeta.

Dobivanje imena svih imenovanih područja

Evo koraka za dobivanje popisa svih imenovanih raspona koje ste stvorili:

• Idite na karticu Formule.
• U grupi Definirano imenovano kliknite Upotreba u formuli.
• Kliknite na "Zalijepi imena".

To će vam dati popis svih imenovanih raspona u toj radnoj knjizi. Da biste koristili imenovani raspon (u formulama ili ćeliji), dvaput kliknite na njega.

Prikaz odgovarajućih imenovanih raspona

• Ako imate neku ideju o imenu, upišite nekoliko početnih znakova i Excel će prikazati padajući popis odgovarajućih imena.

Kako urediti imenovane raspone u Excelu

Ako ste već stvorili Imenovani raspon, možete ga urediti pomoću sljedećih koraka:

• Idite na karticu Formule i kliknite Upravitelj imena.
• Dijaloški okvir Upravitelj imena ispisat će sve Imenovane raspone u toj radnoj knjizi. Dvaput kliknite na Named Range koji želite urediti.
• U dijaloškom okviru Uređivanje naziva unesite promjene.
• Pritisnite U redu.
• Zatvorite dijaloški okvir Upravitelj imena.

Korisni nazivni prečaci dometa (snaga F3)

Evo nekoliko korisnih prečaca na tipkovnici koji će vam biti korisni kada radite s Imenovanim rasponima u Excelu:

• Da biste dobili popis svih imenovanih raspona i zalijepili ga u Formulu: F3
• Za stvaranje novog imena pomoću dijaloškog okvira Upravitelj imena: Control + F3
• Za stvaranje imenovanih raspona iz odabira: Control + Shift + F3

Stvaranje dinamičkih imenovanih raspona u Excelu

Do sada smo u ovom vodiču stvorili statičke nazivne raspone.

To znači da bi se ti imenovani rasponi uvijek odnosili na isti skup podataka.

Na primjer, ako je A1: A10 nazvan "prodaja", uvijek bi se odnosio na A1: A10.

Ako dodate više podataka o prodaji, tada ćete morati ručno otići i ažurirati referencu u imenovanom rasponu.

U svijetu sve širih skupova podataka ovo vam može oduzeti puno vremena. Svaki put kad dobijete nove podatke, možda ćete morati ažurirati Named Ranges u Excelu.

Za rješavanje ovog problema možemo stvoriti dinamičke imenovane raspone u Excelu koji bi automatski uzimali u obzir dodatne podatke i uključili ih u postojeći imenovani raspon.

Na primjer, na primjer, ako dodam dvije dodatne podatkovne točke o prodaji, dinamički imenovani raspon automatski bi se odnosio na A1: A12.

Ova vrsta raspona dinamičkih naziva može se stvoriti pomoću Excel INDEX funkcije. Umjesto navođenja referenci ćelije tijekom stvaranja Imenovanog raspona, navodimo formulu. Formula se automatski ažurira pri dodavanju ili brisanju podataka.

Pogledajmo kako stvoriti dinamičke nazivne raspone u Excelu.

Pretpostavimo da imamo podatke o prodaji u ćeliji A2: A11.

Evo koraka za stvaranje dinamičkih imenovanih raspona u Excelu:

1. 1. Idite na karticu Formula i kliknite Definiraj naziv.
   2. U dijaloški okvir Novo ime upišite sljedeće:
      • Naziv: prodaja
      • Opseg: Radna bilježnica
      • Odnosi se na: = $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, "" & ""))
   3. Pritisnite U redu.

Gotovo!

Sada imate dinamički imenovani raspon s imenom "Prodaja". To će se automatski ažurirati kad god mu dodate podatke ili uklonite podatke iz njih.

Kako funkcioniraju dinamički imenovani rasponi?

Da biste objasnili kako ovo funkcionira, morate znati nešto više o Excel INDEX funkciji.

Većina ljudi koristi INDEX za vraćanje vrijednosti s popisa na temelju broja retka i stupca.

No, funkcija INDEX ima i drugu stranu.

Na to se može naviknuti vratiti referencu ćelije kada se koristi kao dio reference ćelije.

Na primjer, evo formule koju smo koristili za stvaranje dinamičkog imenovanog raspona:

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

INDEKS ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””) -> Očekuje se da će ovaj dio formule vratiti vrijednost (koja bi bila deseta vrijednost s popisa, s obzirom da postoji deset stavki).

Međutim, kada se koristi ispred reference (=$ A $ 2:INDEKS ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))) vraća referencu u ćeliju umjesto vrijednosti.

Dakle, ovdje se vraća = $ A $ 2: $ A $ 11

Ako stupcu prodaje dodamo dvije dodatne vrijednosti, onda bi se vratilo = $ A $ 2: $ A $ 13

Kada na popis dodate nove podatke, funkcija Excel COUNTIF vraća broj ćelija koje nisu prazne u podacima. Ovaj broj funkcija INDEX koristi za dohvaćanje reference ćelije posljednje stavke na popisu.

Bilješka:

• To bi funkcioniralo samo ako u podacima nema praznih ćelija.
• U gore navedenom primjeru dodijelio sam veliki broj ćelija (A2: A100) za formulu Named Range. To možete prilagoditi na temelju vašeg skupa podataka.

Funkciju OFFSET možete koristiti i za stvaranje dinamičkih imenovanih raspona u Excelu, međutim, budući da je funkcija OFFSET promjenjiva, može dovesti do spore radne knjige programa Excel. S druge strane, INDEX je poluhlapni, što ga čini boljim izborom za stvaranje dinamičkih imenovanih raspona u Excelu.

Možda će vam se svidjeti i sljedeći Excel izvori:

• Besplatni predlošci programa Excel.
• Besplatna internetska Excel obuka (7-dijelni video tečaj u 7 dijelova).
• Primjeri korisnih Excel makro kodova.
• 10 primjera naprednog Excel VLOOKUP -a.
• Izrada padajućeg popisa u Excelu.
• Stvaranje imenovanog raspona u Google tablicama.
• Kako uputiti na drugi list ili radnu knjigu u Excelu