Med delom v Excelu ste zagotovo že slišali za imenovane obsege v Excelu. Morda od prijatelja, sodelavca ali kakšne spletne vadnice. Tudi jaz sem to v svojih člankih že večkrat omenil. V tem članku bomo izvedeli o imenovanih območjih v Excelu in raziskali bomo vse njegove vidike.
Kaj so imenovani obseg v Excelu?
No, imenovani obsegi niso nič drugega kot nekateri Excelovi obsegi, ki so označeni z nekim smiselnim imenom. Na primer, če imate celico, recimo B1, ki vsebuje vsakdanji cilj, jo lahko poimenujete kot posebej »cilj«. Zdaj lahko uporabite »Target« za sklicevanje na A1 namesto za pisanje B1.
Na kratko, obseg Named pomeni samo poimenovanje obsegov.
Kako poimenovati obseg v Excelu?
Ročno določite ime:
Če želite določiti ime za obseg, uporabite bližnjico CTRL+F3. Ali pa sledite tem korakom.
-
- Pojdite na zavihek Formula
- Poiščite razdelek Določena imena in kliknite Določi imena. Odprlo se bo imenik.
-
- Kliknite na Novo.
- Vnesite Ime.
- Izberite področje uporabe (delovni zvezek ali list)
- Če želite, napišite komentar.
- V polje Omeni za polje napišite sklic ali z miško izberite obseg.
- Pritisnite OK. Je narejeno.
Zdaj se lahko sklicujete nanj, tako da vnesete njegovo ime.
Pri ustvarjanju imen je treba upoštevati nekatera pravila. So
- Imena se ne smejo začeti iz števk ali posebnih znakov, razen podčrtaja (_) in poševnice (\).
- Imena ne smejo vsebovati presledkov in posebnih znakov, razen _ in \.
- Obseg ne sme biti imenovan kot sklic na celico. Imena A1, B1 ali AZ100 itd. So na primer neveljavna.
- Obsega ne morete poimenovati kot »r« in »c«, ker sta rezervirana za sklice na vrstice in stolpce.
- Dva poimenovana obsega ne moreta imeti istega imena v delovnem zvezku.
- Isti obseg ima lahko več imen.
Samodejno določi ime
No, večino časa boste delali s tabelo strukturiranih podatkov. Imeli bodo stolpce in vrstice z naslovi stolpcev in vrsticami. Najpogosteje so ta imena pomembna za podatke, zato bi radi svoj obseg poimenovali kot te naslove stolpcev. Excel ponuja orodje za samodejno poimenovanje obsegov z uporabo naslovov. Sledite tem korakom.
-
- Obsegi, ki jih želite poimenovati kot njihove naslove
- Pritisnite CTRL+SHIFT+F3, ali Poiščite razdelek Definirana imena na zavihku Formula in kliknite Ustvari iz izbora.
-
- Prikaže se spodnje polje z možnostmi. Top Row sem izbral samo zato, ker želim poimenovati ta obseg kot naslov in ne želim imenovati vrstic.
- Kliknite V redu.
Zdaj je vsak stolpec poimenovan kot njihov naslov. Kadar koli vnesete formulo, bo to ime navedeno kot možnost, ki jo želite uporabiti.
Območje poimenovanja z uporabo tabel Excel
Ko podatke organiziramo kot tabelo v Excelu s pomočjo CTRL + T, se naslovi stolpcev samodejno dodelijo kot ime ustreznega stolpca. Raziščite Excelove tabele in njihove prednosti.
Kako videti vsa imenovana območja?
No, včasih bodo v delovnem zvezku prikazani vsi razpoložljivi imenovani obsegi. Če si želite ogledati vse obsege imen, pritisnite CTRL+F3. Ali pa pojdite na Zavihek Formula> Upravitelj imen. Navedel bo vse imenovane obsege, ki so na voljo v delovnem zvezku. Razpoložljive imenovane obsege lahko uredite, jih izbrišete, dodate nova imena.
En obseg več imen
Excel uporabnikom omogoča, da poimenujejo isti obseg z različnimi imeni. Na primer obseg A2: A10 lahko hkrati imenujemo „odjemalci“ in „odjemalci“. Oba imena se nanašata na isto območje A2: A10.
Vendar ne morete imeti enakih imen za dva različna območja. Odpravlja možnost dvoumnosti.
Pridobite seznam imenovanih območij na listu
Torej, če želite imeti seznam poimenovanih obsegov in obsegov, ki jih pokrivajo, lahko uporabite to bližnjico, da jih prilepite na mesto na listu.
-
- Izberite celico, v katero želite dobiti seznam poimenovanih obsegov.
- Pritisnite F3. S tem se odpre a Pime aste pogovorno okno.
- Kliknite na prilepi seznam gumb.
- Seznam bo prilepljen na izbrano celico in naprej.
Če dvokliknete ime imenovanega obsega v polju z imenom paste, bodo v celici zapisani kot formule. Poskusi.
Ročno posodobite imenovane obsege
No, ko celico vstavite v imenovani obseg, se samodejno posodobi in razširi. Če pa dodate podatke na koncu tabele, boste morali posodobiti imenovano polje. Če želite posodobiti imenovane obsege, sledite tem korakom.
- Pritisnite CTRL+F3, odprete upravitelja imen.
- Kliknite na imenovani obseg, ki ga želite urediti. Kliknite Uredi.
- V stolpcu Sklicevanje na stolpec vnesite obseg, ki ga želite razširiti, in pritisnite V redu.
In končano je. To je ročno posodabljanje poimenovanih obsegov. Lahko pa ga naredimo dinamičnega z uporabo nekaterih formul.
Dinamično posodabljajte imenovane obsege
Pametno je, da imenovane obsege naredite dinamične, tako da vam jih ni treba urejati, kadar koli vaši podatki presežejo vnaprej določeno območje.
O tem sem govoril v ločenem članku z naslovom Dinamični imenovani obsegi. Tu se lahko podrobno naučite in razumete njegove prednosti.
Brisanje imenovanih obsegov
Ko izbrišete del imenovanega obsega, samodejno prilagodi svoj obseg. Ko pa izbrišete celoten obseg imen, izgine s seznama imen. Vsaka formula, ki je odvisna od teh obsegov, bo pokazala napako #REF ali pa bo dala napačen izhod (funkcije štetja).
Če želite iz kakršnega koli razloga izbrisati imenovane obsege, sledite tem korakom.
- Pritisnite CTRL+F3. Odpre se upravitelj imen.
- Izberite Imenovana območja, ki jih želite izbrisati.
- Kliknite gumb Izbriši ali pritisnite tipko Izbriši na tipkovnici.
Pozor: Preden izbrišete imenovane obsege, se prepričajte, da nobena formula ni odvisna od teh imen. Če obstaja, jih najprej pretvorite v obsege. V nasprotnem primeru se prikaže napaka #REF.
Brisanje imen z napakami
Excel ponuja orodje za odstranjevanje imen samo z napakami. Vsakega od njih vam ni treba identificirati sami. Če želite izbrisati imena z napakami, sledite tem korakom:
-
- Odprite Upravitelj imen (CTRL+F3).
- Kliknite spustni meni Filter v zgornjem desnem kotu.
- Izberite "Ime z napakami"
- Izberite Vse in pritisnite gumb za brisanje.
In jih ni več. Vsa imena z napakami bodo takoj izbrisana iz zapisa.
Imenovani razponi s formulami
Najbolje se uporabljajo imenovani obsegi s formulami. Formule postanejo bolj prilagodljive in berljive z Named Ranges. Poglejmo, kako.
Enostavne formule za pisanje
Recimo, da ste obseg poimenovali »Elementi«. Zdaj seznam elementov, ki jih želite šteti za "svinčnike". Z imenom je enostavno zapisati to formulo COUNTIF. Samo napiši
= COUNTIF (postavka, "svinčnik")
Takoj, ko napišete začetne oklepaje formule, se prikaže seznam razpoložljivih imenovanih obsegov. Brez imena bi v Excelu napisali gi COUNTIF funkcijo z obsegi, za katere boste morda morali najprej pogledati obseg, nato izbrati obseg ali ga vnesti v formulo.
Excel služi razpoložljivim imenom.
Imena obsegov so prikazana kot predlogi, ko vnesete katero koli črko za znakom =. Enako kot Excel prikazuje seznam formul. Če na primer vnesete = u, bosta vsaka metoda in poimenovani obseg prikazani, začenši z u, tako da ju lahko preprosto uporabite.
Ustvarite konstante z imenovanimi območji
Doslej smo izvedeli o poimenovanju obsegov, vendar lahko dejansko imenujete tudi vrednosti. Na primer, če je ime vaše stranke Sunder Pichai, potem lahko naredite ime "Client" in se nanaša na "Sundar Pichai". Zdaj, ko v katero koli celico napišete = Client, bo prikazano Sundar Pichai.
Ne samo besedilo, lahko pa tudi številko dodelite kot konstanto za delo. Na primer, določite cilj. Ali vrednost nečesa, kar se ne bo spremenilo.
Absolutno in relativno sklicevanje na imenovane obsege
Sklicevanje na Named obsege v je zelo prilagodljivo. Če na primer ime imenovanega obsega napišete v relativno celico, se bo obnašal kot ustrezna referenca. Glej spodnjo sliko.
Ko pa ga uporabite s formulami, se bo obnašal kot absoluten. No, večino časa jih boste uporabljali s formulami, torej lahko rečete, da so privzeto absolutne, v resnici pa so prilagodljivi.
Lahko pa jih naredimo tudi relativne.
Kako narediti relativne imenovane obsege v Excelu?
Recimo, če želim poimenovati obseg "Pred", ki se bo nanašal na celico levo, kjer koli je napisana. Kako to naredim? Sledite tem korakom:
- Pritisnite CTRL+F3
- Kliknite na Novo
- V razdelek »Ime« vnesite »Befor«.
- V odsek "Nanaša se na:" na levi napišite naslov celice. Na primer, če ste v celici B1, v razdelku "Nanaša se na:" napišite "= A2". Prepričajte se, da nima znaka $.
Kjer koli boste v formulo zapisali "Befor", se bo nanašalo na celico, ki je levo od nje.
Tukaj sem že uporabljal v funkciji COLUMN. Formula vrne številko stolpca leve celice, kjer je zapisana. Na moje presenečenje A1 prikazuje številko stolpca zadnjega stolpca. Kar pomeni, da je list okrogel. Mislil sem, da bo prikazala napako #REF.
Dajte ime pogosto uporabljenim formulam?
Zdaj je ta neverjeten. Večkrat na delovnem listu vedno znova uporabite isto formulo. Na primer, morda boste želeli preveriti, ali je ime na vašem seznamu strank ali ne. In ta potreba se lahko pojavi večkrat. Za to boste vsakič napisali isto kompleksno formulo.
= IF (COUNTIF (Stranka, I3), "Na seznamu", "Ni na seznamu")
Kaj pa, če v celico vnesete samo '= IsInCustomer' in prikazalo se vam bo, ali je vrednost v levi celici na seznamu strank ali ne?
Na primer, tukaj sem pripravil tabelo. Zdaj želim samo vtipkati »= IsInCustomer« v J5 in rad bi videl, ali je vrednost v I5 je na seznamu strank ali ne. Če želite to narediti, sledite tem korakom.
-
- Pritisnite CTRL+F3
- Kliknite na Novo
- V Name napišite "IsInCustomer"
- V "Nanaša se na" napišite svojo formulo. = IF (COUNTIF (Stranka, I5), "Na seznamu", "Ni na seznamu")
- Pritisnite gumb V redu.
Kjer koli vnesete "IsInCustomer", bo preveril vrednost v levi celici na seznamu strank.
To vas ustavi, da se vedno znova ponavljate.
Uporabite imenovane obsege za formule
Tolikokrat definiramo imena za svoje obsege, potem ko smo že napisali formule na podlagi razponov. Na primer imam Celotno ceno kot celice = E2*F2. Kako ga lahko spremenimo Enote*Enota_Cena.
-
- Izberite formule.
- Pojdite na zavihek formula. Kliknite spustni meni Določi ime.
- Kliknite Uporabi imena.
- Prikazal se bo seznam vseh poimenovanih obsegov. Izberite prava imena in pritisnite OK.
Zdaj se imena uporabljajo. To lahko vidite v vrstici s formulo.
Enostavne formule za branje z imenovanimi razponi
Kot ste videli, imenovani obsegi olajšajo branje formul. Če napišem = COUNTIF (»A2: A100«, B2), nihče ne bo razumel, kaj poskušam šteti, dokler ne vidi podatkov ali jim jih kdo razloži.
Če pa napišem = COUNTIF (regija, 'vzhod'), bo večina uporabnikov takoj ugotovila, da štejemo pojav 'vzhoda' v imenovanem območju območja.
Prenosne formule
Imenovani obsegi olajšajo kopiranje in lepljenje formul, ne da bi skrbeli za spreminjanje referenc. Lahko pa vzamete eno formulo iz enega delovnega zvezka v drugega in bo delovala dobro, dokler in če ciljni delovni zvezek nima enakih imen.
Na primer, če imate formulo = COUNTIF (regija, vzhod) v distribucijski tabeli in povejte še en delovni zvezek stranke ki ima tudi imenovano območje »Regija«. Če kopirate to formulo neposredno kjer koli v delovnem zvezku, vam bo prikazala pravilne podatke. Struktura podatkov ne bo pomembna. Ni važno, kje za vraga je ta stolpec v vašem delovnem zvezku. To bo delovalo pravilno.
Na zgornji sliki sem uporabil popolnoma enako formulo v dveh različnih datotekah za štetje števila ali vzhoda na seznamu regij. Zdaj sta v različnih stolpcih, a ker sta oba imenovana kot regija, bo delovala odlično.
Preprosto krmarite po delovnem zvezku
Lažje je krmariti po delovnem zvezku z imenovanimi obsegi. V polje z imenom morate samo vnesti ime imenovanega. Excel vas bo popeljal v obseg, ne glede na to, kje ste v delovnem zvezku. Glede na to, da imenovan obseg obsega delovnega zvezka.
Na primer, če ste na listu 10 in želite dobiti seznam strank, pa ne veste, na katerem listu je. Pojdite v polje z imenom in vnesite "stranka". V imenovanem obsegu boste preusmerjeni v delčku sekunde.
Zmanjšalo bo trud pri zapomnitvi razponov.
Krmarite po hiperpovezavah z imenovanim obsegom
Ko je vaš list velik in pogosto hodite od ene točke do druge, radi uporabljate hiperpovezave za enostavno krmarjenje. Dobro poimenovani razponi lahko odlično delujejo s hiperpovezavami. Če želite dodati hiperpovezave z imenovanimi obsegi, sledite tem korakom.
-
- Izberite celico, v kateri želite hiperpovezavo
- Pritisnite CTRL+K ali pojdite na Vstavi zavihek> HyperLink, da odprete pogovorno okno Vstavi hiperpovezavo.
-
- Kliknite na Kraj v tem dokumentu.
- Pomaknite se navzdol in si oglejte razpoložljiva imenovana območja pod Definirana imena
- Izberite imenovano območje, da vstavite hiperpovezavo do tega območja.
In končano. Imate hiperpovezavo do izbranega imenovanega obsega. S tem lahko ustvarite kazalo poimenovanih obsegov, ki si ga lahko ogledate in kliknete za neposredno krmarjenje do njih. Tako bo vaš delovni zvezek resnično prijazen do uporabnika.
Imenovano območje in preverjanje podatkov
Poimenovani obsegi in preverjanje podatkov so ustvarjeni drug za drugega. Poimenovani obsegi omogočajo, da je preverjanje podatkov zelo prilagodljivo. Dodajanje preverjanja s seznama z imenovanim obsegom je veliko lažje. Poglejmo, kako…
-
- Pojdite na zavihek Podatki
- Kliknite na Preverjanje podatkov
- Izberite Seznam v razdelku »Dovoli:«
- V razdelek »Vir:« vnesite »= Stranka« (napišite poljubno imenovano območje)
- Pritisnite OK
Zdaj bo ta celica imela imena strank, ki so del obsega Imenovane stranke. Enostavno, kajne?
Odvisno ali kaskadno preverjanje podatkov z imenovanimi območji
Kaj pa, če želite kaskadno ali odvisno preverjanje podatkov. Na primer, če želite spustni seznam s kategorijami, sadje in zelenjava. Zdaj, če izberete sadje, mora drugi spustni meni prikazati samo možnost sadja, če pa izberete zelenjavo, potem samo zelenjavo.
To lahko preprosto dosežete z uporabo imenovanih obsegov. Nauči se kako.
- Odvisni spustni meni z imenovanim obsegom
- Drugi načini za kaskadno preverjanje podatkov
Ni validacije podatkov z imeni tabelaričnih podatkov
Čeprav Excelove tabele ponujajo strukturirana imena, pa jih ni mogoče uporabiti pri preverjanju podatkov in pogojnem oblikovanju. Ne vem, zakaj Excel tega ne dovoljuje.
Vendar to ne pomeni, da tega ni mogoče storiti. Obsege v tabeli lahko poimenujete in jih nato uporabite za preverjanje. Excel s tem nima težav.
Obseg imenovanih območij
Doslej smo govorili o poimenovanih obsegih z obsegom delovnega zvezka. Kaj? Ali o tem nismo razpravljali? V redu, zato hitro ugotovimo, kakšen je obseg imenovanih obsegov.
Kaj je obseg imenskega obsega?
Obseg vrtine določa, kje je mogoče prepoznati obseg imen. Nobenega imena ni mogoče prepoznati iz njegovega obsega. Na primer imena v delovnem zvezku1 ni mogoče prepoznati v drugem delovnem zvezku. Excel ponuja dve možnosti za obseg delovnega lista poimenovanih obsegov in delovnega zvezka.
Kako določiti obseg imenovanega območja?
Ko ustvarite nov obseg imen, lahko vidite razdelek »Področje uporabe«. Kliknite spustni meni in izberite obseg za obseg imen. Obsega ne morete spremeniti, ko ustvarite poimenovani obseg. Zato je bolje, da to storite prej. Privzeto je to delovni zvezek.
Področje uporabe delovnega zvezka
To je privzeti obseg imenovanega obsega. Ime, določeno z obsegom delovnega zvezka, se lahko uporabi v celotnem delovnem zvezku, v katerem je opredeljeno (ne v drugih delovnih zvezkih).
Vsi zgornji primeri so imeli obseg delovnega zvezka.
Področje uporabe delovnega lista
Ime, ki je definirano z obsegom delovnega lista, se lahko uporablja samo na opredelitvenem delovnem listu. Na primer, če za celotno celico z obsegom lista1 določim „Skupaj“. Nato se skupni znesek pripozna samo na listu 1. Drugi listi ne bodo prepoznali.
Želim obseg Excela
Excel nima globalnega ali recimo Excelovega področja uporabe. Pravzaprav bi rad opredelil nekaj imen, ki jih je mogoče prepoznati v vseh delovnih zvezkih v mojem sistemu. Če kdo ve, kako lahko to naredimo, naj mi sporoči.
Urejanje obsega po ustvarjanju imen
Ne moreš. Excel, ko ustvarite, ne dovoljuje urejanja obsega imenovanega obsega. Ker so vsi poimenovani obsegi na listu privzeto obseg delovnega zvezka, boste morda želeli spremeniti njihov obseg na list.
Če želite to narediti, naredite kopijo tega lista in Excel bo vsako ime na tem listu lokalno, da se izogne dvoumnosti. Po želji lahko izbrišete izvirni list.
Izreži obseg imen paste
Ko izrežete in prilepite imenovano območje z enega cilja na drugega, se sklic spremeni na novo lokacijo. Na primer, če imate imenovani obseg »Stranka« v A2: A10 in ga izrežete in prilepite v B2: B10, se bo ime stranke nanašalo na novo lokacijo B2: B10.
Dinamični imenovani obsegi v Excelu
17 neverjetnih lastnosti tabel Excel
Priljubljeni članki:
50 bližnjic v Excelu za večjo produktivnost
Kako uporabljati funkcijo VLOOKUP v Excelu
Kako uporabljati funkcijo COUNTIF v Excelu
Kako uporabljati funkcijo SUMIF v Excelu
