Kako ustvariti tabelo in jo poimenovati v Excelu

V tem članku se bomo naučili, kako ustvariti tabelo in jo poimenovati 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.

Kaj so poimenovane tabele v Excelu?

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.

Samodejno določi ime

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.

Območje poimenovanja z uporabo tabel Excel

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.

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. 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

Najboljša uporaba imenovanih obsegov je raziskana s formulami. Formule postanejo res prilagodljive in berljive z Named obsegi. Poglejmo, kako.

Formule za enostavno 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 dajte obseg funkciji COUNTIF v Excelu, za katero boste morda morali najprej pogledati obseg, nato pa ga izbrati ali ga vnesti v formulo. Excel služi razpoložljivim obsegom imen.

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

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? O tem nisva razpravljala? V redu, zato hitro razumejmo, kaj je obseg imenovanih obsegov.

Kakšen je obseg imenskega obsega?

Obseg vrtine določa, kje je mogoče prepoznati obseg imen. Nobenega imena ni mogoče prepoznati izven njegovega področja uporabe. Na primer imena v delovnem zvezku1 ni mogoče prepoznati v različnih delovnih zvezkih. 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.

Upam, da je ta članek o tem, kako ustvariti tabelo in poimenovati tabelo v Excelu, razložljiv. Več člankov o izračunu vrednosti 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.

Kako uporabljati bližnjico za preklop med absolutnimi in relativnimi referencami v Excelu : F4 bližnjica za pretvorbo absolutnega v relativno referenco in enaka uporaba bližnjice za obratno v Excelu.

Kako uporabljati bližnjične tipke za Spajanje in Center v Excelu : Uporaba Nadomestni in potem sledi h, m in c za združevanje in centriranje celic v Excelu.

Kako izbrati cel stolpec in vrstico z bližnjicami na tipkovnici v Excelu : Uporaba Ctrl + Vesolje izberite cel stolpec in Premik + Vesolje za izbiro celotne vrstice z bližnjico na tipkovnici v Excelu

Prilepite posebno bližnjico v Mac in Windows : V oknih bližnjica na tipkovnici za posebna pasta je Ctrl + Nadomestni + V. Medtem ko v Macu uporabite Ctrl + UKAZ + V kombinacijo tipk za odpiranje posebna pasta pogovorno okno v Excelu.

Kako vstaviti bližnjico vrstice v Excel : Uporaba Ctrl + Premik + = odpreti Vstavi pogovorno okno, kamor lahko vstavite vrstico, stolpec ali celice v Excelu.

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.