Sve o nazivnim rasponima programa Excel. Naziv raspona Excel

Anonim

Dok ste radili na Excelu, sigurno ste čuli za imenovane raspone u Excelu. Možda od prijatelja, kolege ili nekog online vodiča. Čak sam i ja to više puta spomenuo u svojim člancima. U ovom ćemo članku naučiti o imenovanim rasponima u Excelu i istražit ćemo svaki njegov aspekt.

Što su nazivni rasponi u Excelu?

Pa, imenovani rasponi nisu ništa drugo do neki excelovi rasponi koji su označeni nekim smislenim imenom. Na primjer, ako imate ćeliju, recimo B1, koja sadrži svakodnevni cilj, toj ćeliji možete dati naziv posebno "Cilj". Sada možete koristiti "Target" za upućivanje na A1 umjesto pisanja B1.
Ukratko, Named range je samo imenovanje raspona.

Kako nazvati raspon u Excelu?

Ručno definirajte naziv:

Da biste definirali naziv raspona, možete koristiti prečac CTRL+F3. Ili možete slijediti ove korake.

    • Idite na karticu Formula
    • Pronađite odjeljak Definirani nazivi i kliknite Definiraj imena. Otvorit će se Name Manger.

    • Kliknite na Novo.
    • Upišite ime.

  • Odaberite opseg (radna knjiga ili list)
  • Napišite komentar ako želite.
  • U okvir Odnosi se na okvir upišite referencu ili odaberite raspon pomoću miša.
  • Pritisnite OK. Učinjeno je.

Sada ga možete uputiti jednostavnim upisivanjem njegovog imena.
Prilikom stvaranja imena morate se pridržavati nekih pravila. Oni su

  1. Imena ne smiju počinjati od znamenki ili posebnih znakova osim donje crte (_) i obrnute kose crte (\).
  2. Imena ne mogu imati razmake i posebne znakove osim _ i \.
  3. Raspon se ne smije imenovati kao reference ćelija. Na primjer, nazivi A1, B1 ili AZ100 itd. Nisu valjani.
  4. Raspon ne možete nazvati "r" i "c" jer su rezervirani za reference retka i stupca.
  5. Dva imenovana raspona ne mogu imati isti naziv u radnoj knjizi.
  6. Isti raspon može imati više naziva.

Automatski definirajte naziv

Pa, većinu vremena radit ćete s tablicom strukturiranih podataka. Oni će imati stupce i retke s naslovima stupaca i naslova redaka. U većini slučajeva ti su nazivi značajni za podatke, a svoj raspon želite nazvati ovim naslovima stupaca. Excel nudi alat za imenovanje raspona pomoću naslova automatski. Prati ove korake.

    • Rasponi koje želite nazvati njihovim naslovima
    • Pritisnite CTRL+SHIFT+F3, ili Pronađite odjeljak Definirani nazivi na kartici Formula i kliknite Stvori iz odabira.

    • Pojavit će se okvir s opcijama u nastavku. Odabrao sam Top Row samo zato što želim dati naziv tom rasponu kao naslov i ne želim imenovati retke.

  • Pritisnite U redu.

Sada se svaki stupac imenuje kao njihov naslov. Kad god upišete formulu, ti će nazivi biti navedeni u opciji koju ćete koristiti.

Raspon imenovanja pomoću tablica programa Excel

Kad podatke organiziramo kao tablicu u Excelu pomoću CTRL + T, naslovi stupaca automatski se dodjeljuju kao naziv odgovarajućeg stupca. Trebali biste istražiti Excelove tablice i njihove prednosti.

Kako vidjeti sve imenovane domete?

Pa, bit će trenutaka kada biste htjeli vidjeti sve dostupne imenovane raspone u radnoj knjizi. Za pregled svih raspona imena pritisnite CTRL+F3. Ili možete otići na Kartica Formula> Upravitelj imena. Popisat će sve imenovane raspone koji su dostupni u radnoj knjizi. Dostupne imenovane raspone možete uređivati, brisati, dodavati nova imena.

Više naziva jednog raspona

Excel omogućuje korisnicima da imenuju isti raspon s različitim imenima. Na primjer, raspon A2: A10 može se nazvati "Kupci" i "Klijenti" istovremeno. Oba naziva odnosit će se na isti raspon A2: A10.
Ali ne možete imati iste nazive za dva različita raspona. Time se uklanja mogućnost dvosmislenosti.

Dobijte popis imenovanih raspona na listu

Dakle, ako želite imati popis imenovanih raspona i raspona koje pokrivaju, možete koristiti ovaj prečac za lijepljenje na mjesto u listu.

    • Odaberite ćeliju u koju želite dobiti popis imenovanih raspona.
    • Pritisnite F3. Ovo će otvoriti a Pime aste okvir za dijalog.
    • Kliknite na popis zalijepiti dugme.

  • Popis će se zalijepiti u odabranu ćeliju i dalje.


Ako dvaput kliknete na naziv imenovanog raspona u okviru s nazivom zalijepljenja, oni će biti zapisani kao formule u ćeliji. Probaj.

Ručno ažurirajte imenovane domete

Pa, kad umetnete ćeliju unutar imenovanog raspona, ona se automatski ažurira i proširuje. No ako dodate podatke na kraj tablice, morat ćete ažurirati imenovano polje. Za ažuriranje imenovanih raspona slijedite ove korake.

  • Pritisnite CTRL+F3, za otvaranje upravitelja imena.
  • Kliknite na imenovani raspon koji želite urediti. Pritisnite Uredi.
  • U Odnosi se na stupac upišite raspon na koji se želite proširiti i pritisnite U redu.

I to je učinjeno. Ovo je ručno ažuriranje imenovanih raspona. Međutim, možemo ga učiniti dinamičnim pomoću nekih formula.

Dinamički ažurirajte imenovane domete

Mudro je vaše imenovane raspone učiniti dinamičnima tako da ih ne morate uređivati ​​kad god vaši podaci prelijevaju unaprijed definirani raspon.
O tome sam govorio u zasebnom članku pod nazivom Dinamički imenovani rasponi. Ovdje možete detaljno naučiti i razumjeti prednosti toga.

Brisanje imenovanih raspona

Kad izbrišete neki dio imenovanog raspona, on automatski prilagođava svoj raspon. Ali kad izbrišete cijeli raspon imena, nestaje s popisa imena. Svaka formula, ovisno o tim rasponima, pokazat će #REF pogrešku ili će dati netočan izlaz (funkcije brojanja).
Iz bilo kojeg razloga, ako želite izbrisati imenovane raspone, slijedite ove korake.

  • Pritisnite CTRL+F3. Otvorit će se upravitelj imena.
  • Odaberite Imenovani rasponi koje želite izbrisati.
  • Pritisnite tipku Delete ili pritisnite tipku Delete na tipkovnici.


Oprez: Prije brisanja imenovanih raspona, provjerite jesu li formule ovisne o tim nazivima. Ako ih ima, prvo ih pretvorite u raspone. U suprotnom ćete vidjeti grešku #REF.

Brisanje imena s pogreškama

Excel nudi alat za uklanjanje imena koja sadrže samo pogreške. Ne morate svaki od njih sami identificirati. Da biste izbrisali imena s pogreškama, slijedite ove korake:

    • Otvorite Upravitelj imena (CTRL+F3).
    • Kliknite padajući izbornik Filter u gornjem desnom kutu.
    • Odaberite "Ime s pogreškama"

  • Odaberite Sve i pritisnite gumb za brisanje.

I otišli su. Svi nazivi s pogreškama bit će odmah izbrisani iz zapisa.

Imenovani rasponi s formulama

Najbolje korištenje imenovanih raspona ima formula. Formule postaju fleksibilnije i čitljivije s Named Ranges. Pogledajmo kako.

Formule za jednostavno pisanje

Recimo da ste rasponu dali naziv "Stavke". Sada popis stavki koje želite izbrojati "Olovke". Uz ime, lako je napisati ovu formulu COUNTIF. Samo napiši

= COUNTIF (Stavka, "Olovka")

Čim napišete uvodnu zagradu formule, pojavit će se popis dostupnih imenovanih raspona. Bez imena napisali biste gi COUNTIF funkciju programa Excel s rasponima, za koju ćete možda morati prvo pogledati raspon, a zatim odabrati raspon ili ga upisati u formulu.

Excel služi raspoloživim rasponima imena.

Nazivi raspona prikazuju se kao prijedlozi kada upišete bilo koje slovo iza znaka =. Isto kao i Excel prikazuje popis formula. Na primjer, ako upišete = u, svaka metoda i imenovani raspon bit će prikazani počevši od u, tako da ih možete jednostavno koristiti.

Učinite konstante pomoću imenovanih raspona

Do sada smo učili o imenovanju raspona, ali zapravo možete imenovati i vrijednosti. Na primjer, ako je ime vašeg klijenta Sunder Pichai, onda možete napraviti ime "Client", a odnosi se na pisanje "Sundar Pichai". Sada, kad god napišete = Client u bilo kojoj ćeliji, prikazat će se Sundar Pichai.


Ne samo tekst, već možete i dodijeliti broj kao konstantu za rad. Na primjer, definirate cilj. Ili vrijednost nečega što se neće promijeniti.
Apsolutno i relativno upućivanje s imenovanim rasponima

Referenciranje s Named rasponima u vrlo je fleksibilno. Na primjer, ako upišete naziv imenovanog raspona u relativnu ćeliju, on će se ponašati kao odgovarajuća referenca. Pogledajte sliku ispod.

Ali kada ga koristite s formulama, on će se ponašati kao apsolutan. Pa, većinu vremena ćete ih koristiti s formulama, pa možete reći da su prema zadanim postavkama apsolutni, ali zapravo su fleksibilni.

Ali možemo ih učiniti i relativnim.

Kako napraviti relativne imenovane raspone u Excelu?

Recimo, ako želim imenovati raspon "Prije" koji će se odnositi na lijevu ćeliju gdje god je napisana. Kako da to učinim? Prati ove korake:

  • Pritisnite CTRL+F3
  • Pritisnite Novo
  • Upišite "Befor" u odjeljak "Ime".
  • U odjeljak "Odnosi se na" upišite adresu ćelije s lijeve strane. Na primjer, ako ste u ćeliji B1, napišite „= A2“ u odjeljak „Odnosi se na:“. Uvjerite se da nema znak $.


Gdje god da u formuli napišete “Befor”, to će se odnositi na ćeliju koja joj je ostavljena.

Ovdje sam ranije koristio u funkciji COLUMN. Formula vraća broj stupca lijeve ćelije u kojoj je zapisana. Na moje iznenađenje, A1 prikazuje broj stupca posljednjeg stupca. Što znači da je list kružan. Mislio sam da će pokazati grešku #REF.

Dajte ime često korištenim formulama?

Sada je ovaj nevjerojatan. Mnogo puta uvijek koristite istu formulu na radnom listu. Na primjer, možda ćete htjeti provjeriti nalazi li se ime na vašem popisu kupaca ili ne. A ta se potreba može pojaviti mnogo puta. Za to ćete svaki put napisati istu složenu formulu.

= IF (COUNTIF (Korisnik, I3), "Na popisu", "Nije na popisu")

Kako bi bilo da samo upišete ‘= IsInCustomer’ u ćeliju i ona će vam pokazati je li vrijednost u lijevoj ćeliji na popisu kupaca ili ne?

Na primjer, ovdje sam pripremio stol. Sada samo želim upisati "= IsInCustomer" u J5 i želio bih vidjeti je li vrijednost u I5 je na popisu kupaca ili nije. Da biste to učinili, slijedite ove korake.

    • Pritisnite CTRL+F3
    • Pritisnite Novo
    • U ime napišite "IsInCustomer"
    • U "Odnosi se na" napišite svoju formulu. = IF (COUNTIF (Korisnik, I5), "Na popisu", "Nije na popisu")

  • Pritisnite tipku OK.

Gdje god upišete "IsInCustomer", provjerit će vrijednost u lijevoj ćeliji na popisu kupaca.

Ovo vas sprječava da se uvijek iznova ponavljate.

Primijenite imenovane raspone na formule

Toliko puta definiramo imena svojim rasponima nakon što smo već napisali formule na temelju raspona. Na primjer, imam ukupnu cijenu kao ćelije = E2*F2. Kako to možemo promijeniti Jedinice*Jedinica_trošak.

    • Odaberite formule.
    • Idite na karticu formula. Pritisnite padajući izbornik Define Name.

  • Kliknite Primijeni imena.
  • Pojavit će se popis svih imenovanih raspona. Odaberite prava imena i pritisnite OK.


I nazivi su sada primijenjeni. Možete ga vidjeti u traci s formulama.

Lako čitljive formule s imenovanim rasponima

Kao što ste vidjeli da imenovani rasponi olakšavaju čitanje formula. Ako napišem = COUNTIF (“A2: A100”, B2), nitko neće razumjeti što pokušavam izbrojiti, sve dok ne vide podatke ili im netko to ne objasni.

Ali ako napišem = COUNTIF (regija, 'istok'), većina korisnika će odmah shvatiti da računamo pojavu 'istok' u imenovanom rasponu regije.

Prijenosne formule

Imenovani rasponi olakšavaju kopiranje i lijepljenje formula bez brige o promjeni referenci. Možete uzeti jednu formulu iz jedne radne knjige u drugu i ona će dobro funkcionirati sve dok i ako odredišna radna knjiga nema iste nazive.

Na primjer, ako imate formulu = COUNTIF (regija, istok) u Tablici distribucije i imate još jednu radnu knjigu kupcima koji također ima imenovani raspon "Regija". Ako kopirate ovu formulu izravno bilo gdje u radnoj knjizi, prikazat će vam se točne informacije. Struktura podataka neće biti važna. Nije važno gdje je, dovraga, taj stupac u vašoj radnoj bilježnici. Ovo će ispravno funkcionirati.

Na gornjoj slici sam koristio potpuno istu formulu u dvije različite datoteke za brojanje broja ili istoka koji se pojavljuju na popisu regija. Sada su u različitim stupcima, ali budući da su oboje tada imenovani kao regija, to će raditi savršeno.

Lako se krećite po radnoj knjizi

Lakše se snalazi u radnoj knjizi s imenovanim rasponima. Samo trebate unijeti ime imenovanog u okvir s imenom. Excel će vas odvesti u raspon, bez obzira gdje se nalazite u radnoj knjizi. S obzirom da je imenovani raspon opsega radne knjige.
Na primjer, ako ste na listu 10 i želite dobiti popis kupaca, a ne znate na kojem je to listu. Samo idite na okvir s imenom i upišite "kupac". Bit ćete preusmjereni na imenovani raspon u djeliću sekunde.


To će smanjiti napore pamćenja raspona.

Krećite se pomoću hiperveza s imenovanim rasponom

Kad je vaš list velik i često idete s jedne točke na drugu, volite koristiti hiperveze za jednostavno kretanje. Dobro imenovani rasponi mogu savršeno funkcionirati s hipervezama. Za dodavanje hiperveza pomoću imenovanih raspona slijedite ove korake.

    • Odaberite ćeliju u kojoj želite hipervezu
    • Pritisnite CTRL+K ili idite na Umetni karticu> HyperLink da otvorite dijaloški okvir Umetni hipervezu.

    • Kliknite na Mjesto u ovom dokumentu.
    • Pomaknite se prema dolje da vidite dostupne Imenovane raspone pod Definirani nazivi

  • Odaberite Imenovani raspon za umetanje hiperveze do tog raspona.

I to je učinjeno. Imate svoju hipervezu na odabrani imenovani raspon. Pomoću ovoga možete stvoriti indeks imenovanih raspona koji možete vidjeti i kliknuti za izravno kretanje do njih. Time će vaša radna knjiga biti zaista prilagođena korisnicima.

Imenovani raspon i provjera valjanosti podataka

Imenovani rasponi i provjera valjanosti podataka stvoreni su jedno za drugo. Imenovani rasponi čine provjeru podataka vrlo prilagodljivom. Mnogo je lakše dodati provjeru valjanosti s popisa pomoću imenovanog raspona. Da vidimo kako…

    • Idite na karticu Podaci
    • Kliknite na Validacija podataka
    • Odaberite Popis u odjeljku "Dopusti:"
    • U odjeljak "Izvor:" upišite "= kupac" (napišite koji god imenovani raspon imate)

  • Pritisnite OK

Sada će ova ćelija imati imena kupaca koji su dio raspona imena kupaca. Lako, zar ne?

Ovisna ili kaskadna provjera podataka s imenovanim rasponima

Što ako želite kaskadnu ili ovisnu provjeru podataka. Na primjer, ako želite padajući popis s kategorijama, voće i povrće. Sada, ako odaberete voće, drugi padajući izbornik trebao bi prikazati samo opciju voća, a ako odaberete Povrće onda samo povrće.

To se lako može postići korištenjem Named raspona. Nauči kako.

  • Ovisno, padajući izbornik pomoću imenovanog raspona
  • Drugi načini kaskadne provjere valjanosti podataka

Nema provjere valjanosti podataka s imenima tabličnih podataka

Iako tablice programa Excel sadrže strukturirana imena, ne mogu se koristiti za provjeru valjanosti podataka i uvjetno oblikovanje. Ne znam zašto Excel to ne dopušta.
Ali to ne znači da se to ne može učiniti. Možete imenovati raspone unutar tablice, a zatim ih koristiti za provjeru valjanosti. Excel s tim nema problema.

Opseg imenovanih raspona

Do sada smo govorili o imenovanim rasponima koji su imali opseg radne knjige. Što? Nismo o tome razgovarali? U redu, pa brzo shvatimo koji je opseg imenovanih raspona.

Što je opseg raspona imena?

Opseg bušotine definira gdje se raspon imena može prepoznati. Nijedno ime ne može se prepoznati izvan njegovog opsega. Na primjer, ime u radnoj knjizi1 ne može se prepoznati u drugoj radnoj knjizi. Excel nudi dvije mogućnosti za opseg radnog lista i radne knjige imenovanih raspona.

Kako definirati opseg imenovanog raspona?

Kad stvorite novi raspon naziva, vidjet ćete odjeljak "Opseg:". Pritisnite padajući izbornik i odaberite opseg za raspon imena. Ne možete promijeniti opseg nakon što ste stvorili imenovani raspon. Zato je bolje da to učinite prije. Prema zadanim postavkama to je radna knjiga.

Opseg radne bilježnice

Ovo je zadani opseg za imenovani raspon. Ime definirano s opsegom radne knjige može se koristiti u cijeloj radnoj knjizi u kojoj je definirano (ne u drugim radnim knjigama).
Svi gornji primjeri imali su opseg radne knjige.

Opseg radnog lista

Naziv koji je definiran s opsegom radnog lista može se koristiti samo na definiranju radnog lista. Na primjer, ako definiram 'Ukupno' za ukupnu ćeliju s opsegom lista1. Tada će se ukupan iznos prepoznati samo na listu 1. Ostali listovi neće se prepoznati.

Želim opseg programa Excel

Excel nema globalno ili recimo Excel opseg. Zapravo, želio bih definirati neka imena koja se mogu prepoznati u svim radnim bilježnicama na mom sustavu. Ako netko zna kako to možemo učiniti, neka mi se javi.

Uređivanje opsega nakon stvaranja imena

Ne možete. Excel vam ne dopušta uređivanje opsega imenovanog raspona nakon što ste stvorili. Budući da su svi imenovani rasponi na listu prema zadanim postavkama opseg radne knjige, a možda ćete htjeti promijeniti njihov opseg u list.
Da biste to učinili, samo napravite kopiju tog lista i Excel će svako ime na tom listu učiniti lokalnim kako bi se izbjegle nejasnoće. Sada možete izbrisati izvorni list ako želite.

Izreži raspon naziva paste

Kad izrežete i zalijepite imenovani raspon s jednog odredišta na drugo, referenca se mijenja na novo mjesto. Na primjer, ako imate imenovani raspon "Kupac" u A2: A10 i izrežete ga i zalijepite u B2: B10, tada će se ime kupca odnositi na novu lokaciju B2: B10.

Dinamički imenovani rasponi u Excelu

17 zadivljujućih značajki Excel tablica

Popularni članci:

50 Excel prečaca za povećanje vaše produktivnosti

Kako koristiti funkciju VLOOKUP u Excelu

Kako koristiti funkciju COUNTIF u Excelu

Kako koristiti funkciju SUMIF u Excelu