V tem članku se bomo naučili, kako uporabljati imena v formuli v Excelu.
Scenarij:
Med delom v Excelu ste zagotovo že slišali za imenovane obsege v Excelu. Morda od prijatelja, sodelavca ali kakšne spletne vadnice. To sem že večkrat omenil v svojih člankih. V tem članku bomo spoznali Named Range v Excelu in raziskali bomo vse njegove vidike.
Imenovani obsegi v Excelu
Formule -> Upravitelj imen -> Določi ime
No, imenovani obsegi niso nič drugega kot nekateri Excelovi obsegi, ki jim je dano smiselno ime. Na primer, če imate celico recimo B1, ki vsebuje vsakodnevne cilje, lahko to celico poimenujete kot posebej »Target«. 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:
Za določitev imena obsega lahko uporabite bližnjico CTRL + F3. Ali pa sledite tem korakom.
- Pojdi do Formula Zavihek
- Poiščite razdelek Določena imena in kliknite Določi imena. To bo odprlo datoteko Ime Manger.
- 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. Opravljeno je.
Zdaj se lahko sklicujete nanj, tako da vnesete njegovo ime.
Pri ustvarjanju imen je treba upoštevati nekatera pravila. Tukaj je nekaj.
- 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. Na primer A1, B1 ali AZ100 itd.
- 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.
No, večino časa boste delali s tabelami strukturiranih podatkov. Imeli bodo stolpce in vrstice z naslovi stolpcev in vrsticami. Najpogosteje so ta imena pomembna za podatke in svoj obseg želite poimenovati kot te naslove stolpcev. Excel ponuja orodje za samodejno poimenovanje obsegov z naslovi. 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. Izbral sem samo Top Row, 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, bodo ta imena navedena kot možnosti, do katerih lahko dostopate.
Ko tablični računalnik podate v Excelu s pomočjo CTRL + T, se naslov stolpca samodejno dodeli kot ime ustreznega stolpca. Raziščite Excelove tabele in njihove prednosti.
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. Tu bodo navedeni vsi imenovani obsegi, 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. To je dobro. To 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.
- Izberite celico, v kateri želite dobiti seznam poimenovanih obsegov.
- Pritisnite F3. S tem se odpre a prilepi seznam pogovorno okno.
- Kliknite na prilepi seznam gumb.
- Seznam bo prilepljen na izbrane celice 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 imenovano območje, se samodejno posodobi in razširi. Če pa dodate podatke na koncu tabele, boste morali posodobiti imenovani obseg. Če želite posodobiti imenovane obsege, sledite tem korakom.
- Pritisnite CTRL+F3 odpreti upravitelj 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 vsote imenovanega obsega, samodejno prilagodi svoj obseg. Ko pa izbrišete celoten obseg imen, izgine s seznama imen. Vsaka formula, odvisna od teh obsegov, bo prikazala napako #REF ali pa bo dala napačen izhod (funkcije štetja).
Iz katerega koli razloga, če želite 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 orodja za brisanje 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
Najboljša uporaba imenovanih obsegov je raziskana s formulami. Formule postanejo res prilagodljive in berljive z Named obsegi. Poglejmo, kako.
Enostavne formule za pisanje
Recimo, da ste obseg poimenovali »Elementi«. Zdaj seznam elementov, ki jih želite šteti za "svinčnike". Z imeni je enostavno zapisati to formulo COUNTIF. Samo napiši
= COUNTIF (Artikel, "Svinčnik")
Takoj, ko napišete začetne oklepaje formule, se prikaže seznam razpoložljivih imenovanih obsegov
Brez imena bi napisali podaj obseg funkciji COUNTIF v Excelu, za katero boste morda morali najprej pogledati obseg, nato pa ga izbrati ali vnesti v formulo.
Excel služi razpoložljivim imenom.
Poimenovani obsegi so prikazani kot predlogi, ko vnesete katero koli črko. Kot Excel prikazuje seznam formul. Na primer, če vnesete = u, bosta vsaka formula in imenovani obseg prikazani začenši z u, tako da jih 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, lahko naredite ime "Client" in se nanaša na "Sundar Pichai". Zdaj, ko v kateri koli celici napišete = Client, bo prikazano Sundar Pichai.
Ne samo besedilo, lahko pa tudi številke dodelite kot konstantne. 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 v relativno celico napišete imenovanemu obsegu, se bo obnašal kot relativna 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 »Befor«, ki se bo nanašal na celico levo, kjer koli je zapisano. 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 ga prej 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.
| = ČE (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. = ČE (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 vam preprečuje, 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 formule. 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 formulami.
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 pojavitve 'vzhoda' v imenovanem območju.
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, dokler in razen če ima ciljni delovni zvezek isto ime.
Na primer, če imate formulo = COUNTIF (regija, vzhod) v tabeli distribucije in povedali boste š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 poimenovana kot regije, bo delovalo 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 imena. 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 je. 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 v tej celici imena kupcev, ki so del obsega imen strank. 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
Upam, da je ta članek o tem, kako uporabljati imena v formuli v Excelu, razložljiv. Več člankov o poimenovanju obsegov in sorodnih formulah Excel najdete tukaj. Če so vam bili naši blogi všeč, jih delite s prijatelji na Facebooku. Prav tako nas lahko spremljate na Twitterju in Facebooku. Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo, dopolnimo ali inoviramo svoje delo in ga izboljšamo. Pišite nam na spletnem mestu e -pošte.
Polje z imenom v Excelu : Excelovo polje z imenom ni nič drugega kot majhno območje prikaza zgoraj levo na Excelovem listu, ki prikazuje ime aktivne celice ali obsege v Excelu. Za sklice lahko celico ali matriko preimenujete.
Kako pridobiti ime lista delovnega lista v Excelu : Funkcija CELL v Excelu vam posreduje informacije o katerem koli delovnem listu, kot so stolpec, vsebina, ime datoteke … itd. Tukaj se naučite, kako dobiti ime lista s funkcijo CELL.
Kako dobiti zaporedno številko vrstice v Excelu: Včasih moramo v tabelo dobiti zaporedno številko vrstice, lahko je to za serijsko številko ali karkoli drugega. V tem članku se bomo naučili, kako oštevilčiti vrstice v Excelu od začetka podatkov.
Povečajte število v besedilnem nizu v Excelu: Če imate velik seznam elementov in morate povečati zadnjo številko besedila starega besedila v Excelu, boste potrebovali pomoč dveh funkcij BESEDILO in DESNO.
Priljubljeni članki:
Kako uporabljati funkcijo IF v Excelu : Stavek IF v Excelu preveri pogoj in vrne določeno vrednost, če je pogoj TRUE, ali vrne drugo posebno vrednost, če je FALSE.
Kako uporabljati funkcijo VLOOKUP v Excelu : To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati funkcijo SUMIF v Excelu : To je še ena bistvena funkcija nadzorne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.
Kako uporabljati funkcijo COUNTIF v Excelu : Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.