Excel

Imenovani obsegi v Excelu

Named Ranges Excel

Imenovani obsegi so ena od teh skorjnih starih funkcij v Excelu, ki jih malo uporabnikov razume. Novi uporabniki se jim lahko zdijo čudni in strašljivi, celo stare roke se jim lahko izognejo, ker se zdijo nesmiselne in zapletene.



Toda imenovani obsegi so pravzaprav precej kul funkcija. Lahko olajšajo ustvarjanje, branje in vzdrževanje formul *. Kot bonus pa olajšajo ponovno uporabo formul (bolj prenosljive).

Pravzaprav ves čas uporabljam imenovane obsege pri testiranju in izdelavi prototipov formul. Pomagajo mi, da formule delujejo hitreje. Uporabljam tudi imenovane obsege, ker sem len in ne maram vnašanja zapletenih referenc :)





Osnove poimenovanih obsegov v Excelu

Kaj je imenovani obseg?

Imenovani obseg je le človeku berljivo ime za vrsto celic v Excelu. Če na primer obseg A1: A100 poimenujem 'podatki', lahko z največjo vrednostjo uporabim MAX s preprosto formulo:

 
 = MAX (data) // max value

Enostavno imenovano območje imenovano



Lepota poimenovanih obsegov je v tem, da lahko uporabite smiselna imena v svojih formulah, ne da bi razmišljali o referencah celic. Ko imate imenovan obseg, ga uporabite kot sklic na celico. Vse te formule so veljavne z imenovanim obsegom 'podatki':

 
= MAX (data) // max value = MIN (data) // min value = COUNT (data) // total values = AVERAGE (data) // min value

Video: Kako ustvariti poimenovan obseg

Ustvarjanje poimenovanega obsega je enostavno

Ustvarjanje poimenovanega obsega je hitro in enostavno. Samo izberite obseg celic in v polje z imenom vnesite ime. Ko pritisnete Return, se ustvari ime:

Hitro ustvarite poimenovani obseg z imenom v polju

Če želite hitro preizkusiti nov obseg, izberite novo ime v spustnem meniju poleg polja z imenom. Excel bo izbral obseg na delovnem listu.

Excel lahko samodejno ustvari imena (ctrl + shift + F3)

Če imate dobro strukturirane podatke z oznakami, lahko Excel za vas ustvari imenovane obsege. Samo izberite podatke skupaj z oznakami in uporabite ukaz 'Ustvari iz izbora' na zavihku Formule na traku:

Ustvarite imena iz ukaza za izbiro na traku

Uporabite lahko tudi nadzor bližnjice na tipkovnici + shift + F3.

S to funkcijo lahko v enem koraku ustvarimo imenovana območja za prebivalstvo 12 držav:

spremenite ta delovni list, tako da boste lahko hkrati videli štiri različna področja delovnega lista

Ustvarite imena iz izbora z izbranimi podatki in oznakami

Ko kliknete V redu, se imena ustvarijo. Vsa novonastala imena boste našli v spustnem meniju poleg polja z imenom:

Nova imena se prikažejo tudi v spustnem meniju polja z imeni

Z ustvarjenimi imeni jih lahko uporabite v takih formulah

 
= SUM (MN,WI,MI)

Posodobite imenovane obsege v upravitelju imen (Control + F3)

Ko ustvarite imenovani obseg, uporabite Upravitelj imen (Control + F3) za posodobitev po potrebi. Izberite ime, s katerim želite delati, nato neposredno spremenite sklic (tj. Uredite 'nanaša se na') ali kliknite gumb na desni in izberite nov obseg.

Poimenovani obsegi so bili posodobljeni z upraviteljem imen

Za posodobitev sklica ni treba pritisniti gumba Uredi. Ko kliknete Zapri, se bo ime obsega posodobilo.

Opomba: če na delovnem listu izberete celotno imenovano območje, ga lahko povlečete na novo lokacijo in sklic se bo samodejno posodobil. Ne vem pa, kako prilagoditi sklice na obseg s klikom in vlečenjem neposredno na delovnem listu. Če poznate način za to, se oglasite spodaj!

Ogled vseh imenovanih obsegov (control + F3)

Če želite hitro videti vse imenovane obsege v delovnem zvezku, uporabite spustni meni poleg polja z imenom.

Če želite videti več podrobnosti, odprite Upravitelj imen (Control + F3), ki navaja vsa imena s sklici in vsebuje tudi filter:

Upravitelj imen prikazuje vsa novo ustvarjena imena

Opomba: v računalniku Mac ni upravitelja imen, zato boste namesto tega videli pogovorno okno Določi ime.

Kopirajte in prilepite vse imenovane obsege (F3)

Če želite v delovnem zvezku trajnejši zapis poimenovanih obsegov, lahko prilepite celoten seznam imen kamor koli želite. Pojdite na Formule> Uporabi v formuli (ali uporabite bližnjico F3), nato izberite Prilepi imena> Prilepi seznam:

Pogovorno okno prilepi imena

Ko kliknete gumb Prilepi seznam, bodo imena in sklici prilepljeni na delovni list:

Po lepljenju imenovanih obsegov na delovni list

Oglejte si imena neposredno na delovnem listu

Če raven povečave nastavite na manj kot 40%, bo Excel prikazal imena obsegov neposredno na delovnem listu:

Pri ravni povečave <40%bo Excel prikazal imena obsegov

Hvala za ta namig, Felipe!

Imena imajo pravila

Pri ustvarjanju poimenovanih obsegov upoštevajte ta pravila:

  1. Imena se morajo začeti s črko, podčrtajem (_) ali poševnico ()
  2. Imena ne smejo vsebovati presledkov in večine ločil.
  3. Imena ne morejo biti v nasprotju z referencami celic - obsega ne morete poimenovati 'A1' ali 'Z100'.
  4. Posamezne črke so v redu za imena ('a', 'b', 'c' itd.), Vendar sta črki 'r' in 'c' rezervirani.
  5. Imena ne razlikujejo med velikimi in malimi črkami-'home', 'HOME' in 'HoMe' so za Excel enaki.

Imenovani obsegi v formulah

Imenovana območja so enostavna za uporabo v formulah

Recimo, da celici v delovnem zvezku poimenujete »posodobljeno«. Ideja je, da lahko trenutni datum vnesete v celico (Ctrl +) in se sklicujete na datum drugje v delovnem zvezku.

Uporaba poimenovanega obsega v besedilni formuli

Formula v B8 izgleda tako:

 
='Updated: '&  TEXT (updated, 'ddd, mmmm d, yyyy')

To formulo lahko prilepite kamor koli v delovni zvezek in se bo pravilno prikazala. Kadar koli spremenite datum v »posodobljeno«, se bo sporočilo posodobilo povsod, kjer se uporablja formula. Glej to stran za več primerov.

Imenovani obsegi se pojavijo pri vnosu formule

Ko ustvarite imenovano območje, se bo samodejno pojavilo v formulah, ko vnesete prvo črko imena. Pritisnite tipko tabulatorka, da vnesete ime, ko se ujemate in želite, da Excel vnese ime.

Imenovani obsegi se pojavijo pri vnosu formul

Imenovani obsegi lahko delujejo kot konstante

Ker so imenovani obsegi ustvarjeni na osrednji lokaciji, jih lahko uporabite kot konstante brez sklicevanja na celico. Ustvarite lahko na primer imena, kot sta 'MPG' (milje na galono) in 'CPG' (cena na galono), z dodeljenimi fiksnimi vrednostmi:

Poimenovani obsegi lahko delujejo kot konstante, brez sklicevanja na celico

Nato lahko ta imena uporabite kjer koli želite v formulah in posodobite njihovo vrednost na enem osrednjem mestu.

Uporaba imenovanega obsega kot konstante v formuli

Poimenovani obsegi so privzeto absolutni

Poimenovani obsegi se privzeto obnašajo kot absolutne reference. Na primer, na tem delovnem listu bi bila formula za izračun goriva:

 
=C5/$D

Standardna formula z absolutnim naslovom

Sklicevanje na D2 je absolutno (zaklenjeno), zato je formulo mogoče kopirati brez spreminjanja D2.

Če D2 poimenujemo 'MPG', bo formula postala:

 
=C5/MPG

Uporaba imenovanega obsega kot konstante v formuli

Ker je MPG privzeto absoluten, lahko formulo kopirate v stolpec D takšnega, kot je.

Poimenovana območja so lahko tudi relativna

Čeprav so imenovani obsegi privzeto absolutni, so lahko tudi relativni. Relativno imenovano območje se nanaša na obseg, ki je glede na položaj aktivne celice v času, ko je obseg ustvarjen . Posledično so relativni poimenovani obsegi uporabni pri oblikovanju splošnih formul, ki delujejo povsod, kjer se premaknejo.

Na primer, lahko ustvarite splošni imenovani obseg 'CellAbove', kot je ta:

  1. Izberite celico A2
  2. Pritisnite Control + F3, da odprete Upravitelja imen
  3. Zavijte v razdelek »Nanaša se na« in vnesite: = A1

CellAbove bo zdaj pridobila vrednost iz celice zgoraj, kjer koli se uporablja.

Pomembno: pred ustvarjanjem imena se prepričajte, da je aktivna celica na pravilnem mestu.

Uporabite imenovane obsege za obstoječe formule

Če imate obstoječe formule, ki ne uporabljajo imenovanih obsegov, lahko od Excela zahtevate, da za vas uporablja imenovane obsege. Začnite tako, da izberete celice, ki vsebujejo formule, ki jih želite posodobiti. Nato zaženite formule> Določi imena> Uporabi imena.

Pogovorno okno Uporabi imena

Excel bo nato sklice, ki imajo ustrezen poimenovan obseg, zamenjal s samim imenom.

Uporabite lahko tudi imena s funkcijo poišči in zamenjaj:

Uporaba obsegov imen s funkcijo poišči in zamenjaj

Pomembno: Shranite varnostno kopijo svojega delovnega lista in izberite samo celice, ki jih želite spremeniti, preden uporabite formule za iskanje in zamenjavo.

Ključne prednosti imenovanih razponov

Imenovani obsegi olajšajo branje formul

Največja edina korist imenovanih obsegov je, da formule olajšajo branje in vzdrževanje. To je zato, ker kriptične reference nadomestijo s smiselnimi imeni. Na primer, razmislite o tem delovnem listu s podatki o planetih v našem sončnem sistemu. Brez poimenovanih obsegov je formula VLOOKUP za pridobivanje 'položaja' iz tabele precej skrivnostna:

 
= VLOOKUP ($H,$B:$E,2,0)

Brez imenovanih obsegov so lahko formule skrivnostne

kako nastaviti celico kot absolutno referenco celice

Vendar lahko z B3: E11 z imenom 'podatki' in H4 z imenom 'planet' lahko zapišemo formule, kot je ta:

 
= VLOOKUP (planet,data,2,0) // position = VLOOKUP (planet,data,3,0) // diameter = VLOOKUP (planet,data,4,0) // satellites

Formule z imenovanimi območji so lahko preproste

Na prvi pogled lahko vidite edino razliko v teh formulah v indeksu stolpca.

Imenovani obsegi naredijo formule prenosne in za večkratno uporabo

Poimenovani obsegi lahko olajšajo ponovno uporabo formule na drugem delovnem listu. Če imena vnaprej določite na delovnem listu, lahko prilepite formulo, ki uporablja ta imena, in bo 'samo delovala'. To je odličen način, da formula hitro deluje.

Ta formula na primer šteje edinstvene vrednosti v vrsti numeričnih podatkov:

 
= SUM (--( FREQUENCY (data,data)>0))

Če želite hitro 'prenesti' to formulo na svoj delovni list, poimenujte obseg 'podatki' in prilepite formulo na delovni list. Dokler 'podatki' vsebujejo številske vrednosti, bo formula delovala takoj.

Nasvet: priporočam, da v ciljnem delovnem zvezku najprej ustvarite potrebna imena obsegov *, nato pa formulo kopirate samo kot besedilo (tj. Ne kopirajte celice, ki vsebuje formulo, v drug delovni list, samo kopirajte besedilo formule ). To preprečuje Excelu, da bi ustvarjal imena med letom in l vam omogoča, da v celoti nadzirate postopek ustvarjanja imena. Če želite kopirati samo besedilo formule, kopirajte besedilo iz vrstice s formulami ali kopirajte prek druge aplikacije (npr. Brskalnika, urejevalnika besedil itd.).

Imenovana območja se lahko uporabljajo za navigacijo

Poimenovani razponi so odlični za hitro navigacijo. Samo izberite spustni meni poleg polja z imenom in izberite ime. Ko spustite miško, bo izbran obseg. Če imenovani obseg obstaja na drugem listu, boste samodejno preusmerjeni na ta list.

Imenovani obsegi omogočajo preprosto navigacijo

Imenovani obsegi dobro delujejo s hiperpovezavami

Imenovani obsegi olajšajo hiperpovezave. Če na primer A1 v Sheet1 poimenujete 'home', lahko ustvarite hiperpovezavo nekje drugje, ki vas popelje tja.

Ustvarjanje hiperpovezave do poimenovanega obsega

Primer hiperpovezave imenovanega obsega na delovnem listu

Če želite uporabiti imenovano območje znotraj funkcije HYPERLINK, dodajte simbol funta pred poimenovanim obsegom:

 
= HYPERLINK ('#home','take me home')

Opomba: nenavadno ne morete imeti hiperpovezave do tabele, kot pri običajnem imenu obsega. Lahko pa določite ime, ki je enako tabeli (tj. = Tabela1) in hiperpovezavo do nje. Če kdo ve, kako neposredno povezati mizo, naj se javi!

Imenovani obsegi za preverjanje podatkov

Razponi imen dobro delujejo pri preverjanju podatkov, saj vam omogočajo, da uporabite logično imenovano referenco za preverjanje vnosa s spustnim menijem. Spodaj je obseg G4: G8 poimenovan 'statuslist', nato uporabite potrditev podatkov s tako povezanim seznamom:

Uporaba poimenovanega obsega za preverjanje podatkov s seznamom

Rezultat je spustni meni v stolpcu E, ki dovoljuje samo vrednosti v imenovanem obsegu:

Validacija podatkov z imenom imenovanega obsega

Dinamična imenovana območja

Obsegi imen so izjemno uporabni, če se samodejno prilagodijo novim podatkom na delovnem listu. Tako nastavljen obseg se imenuje 'dinamično imenovano območje'. Obstajata dva načina za dinamično določanje obsega: formule in tabele.

Dinamično imenovano območje s tabelo

Tabela je najlažji način za ustvarjanje dinamičnega poimenovanega obsega. Izberite katero koli celico v podatkih, nato uporabite bližnjico Control + T:

Ustvarjanje tabele Excel

Ko ustvarite tabelo Excel, se samodejno ustvari ime (npr. Tabela1), vendar lahko tabelo preimenujete, kot želite. Ko ustvarite tabelo, se bo samodejno razširila, ko dodate podatke.

Tabele se bodo samodejno razširile in jih lahko preimenujete

Dinamično imenovano območje s formulo

Ustvarite lahko tudi dinamično poimenovano območje s formulami z uporabo funkcij, kot sta OFFSET in INDEX. Čeprav so te formule zmerno zapletene, zagotavljajo lahkotno rešitev, če ne želite uporabiti mize. Spodnje povezave ponujajo primere s popolnimi razlagami:

  • Primer formule dinamičnega obsega z INDEX
  • Primer formule dinamičnega obsega z OFFSET

Imena tabel pri preverjanju podatkov

Ker Excelove tabele ponujajo samodejni dinamični razpon, se zdi, da so naravne primernosti za pravila preverjanja veljavnosti podatkov, pri katerih je cilj preverjanje glede na seznam, ki se lahko vedno spreminja. Vendar je ena težava s tabelami v tem, da strukturiranih referenc ne morete uporabiti neposredno za ustvarjanje pravil za preverjanje ali pogojno oblikovanje podatkov. Z drugimi besedami, v tabelah za pogojno oblikovanje ali preverjanje podatkov ni mogoče uporabiti imena tabele.

Kot rešitev pa lahko določite poimenovano imenovano območje, ki kaže na tabelo, nato pa imenovano območje uporabite za preverjanje podatkov ali pogojno oblikovanje. Spodnji video podrobno opisuje ta pristop.

Video: Kako uporabljati imenovane obsege s tabelami

Brisanje poimenovanih obsegov

Opomba: Če imate formule, ki se nanašajo na imenovane obsege, jih boste morda želeli najprej posodobiti, preden odstranite imena. V nasprotnem primeru boste videli #NAME? napake v formulah, ki se še vedno nanašajo na izbrisana imena. Pred odstranitvijo imenovanih obsegov vedno shranite svoj delovni list, če imate težave in se morate vrniti na izvirnik.

Imenovani obsegi se pri brisanju in vstavljanju celic prilagajajo

Ko izbrišete * del * imenovanega obsega ali če znotraj imenovanega obsega vstavite celice/vrstice/stolpce, se bo sklic na obseg ustrezno prilagodil in ostal veljaven. Če pa izbrišete vse celice, ki vsebujejo imenovani obseg, bo imenovani obseg izgubil sklic in prikazal napako #REF. Če na primer A1 poimenujem 'test', potem izbrišem stolpec A, bo upravitelj imen prikazal 'nanaša se' kot:

 
=Sheet1!#REF!

Brisanje imen z upraviteljem imen

Če želite ročno odstraniti imenovane obsege iz delovnega zvezka, odprite upravitelja imen, izberite obseg in kliknite gumb Izbriši. Če želite hkrati odstraniti več kot eno ime, lahko s kombinacijo tipk Shift + klik ali Ctrl + klik izberete več imen in nato v enem koraku izbrišete.

Izbrišite imena z napakami

Če imate veliko imen z referenčnimi napakami, lahko z gumbom za filtriranje v upravitelju imen filtrirate imena z napakami:

Meni filtra upravitelja imen

Nato pritisnite Shift+klik, da izberete vsa imena in izbrišete.

Poimenovani obsegi in področje uporabe

Poimenovani obsegi v Excelu imajo nekaj, kar se imenuje 'obseg', ki določa, ali je imenovano območje lokalno na danem delovnem listu ali globalno v celotnem delovnem zvezku. Globalna imena imajo obseg 'delovnega zvezka', lokalna imena pa obseg, enak imenu lista, na katerem obstajajo. Obseg lokalnega imena je lahko na primer 'Sheet2'.

Namen področja uporabe

Poimenovani obsegi z globalnim obsegom so uporabni, če želite, da imajo vsi listi v delovnem zvezku dostop do določenih podatkov, spremenljivk ali konstant. Na primer, lahko uporabite globalno imenovano območje in predpostavko o davčni stopnji, ki se uporablja v več delovnih listih.

Lokalni obseg

Lokalni obseg pomeni, da ime deluje samo na listu, na katerem je bilo ustvarjeno. To pomeni, da imate lahko v istem delovnem zvezku več delovnih listov, ki vsi uporabljajo isto ime. Morda imate na primer delovni zvezek z mesečnimi listi za sledenje (enega na mesec), ki uporabljajo poimenovane obsege z istim imenom, vse z lokalnim obsegom. To vam lahko omogoči ponovno uporabo istih formul na različnih listih. Lokalni obseg omogoča, da imena na vsakem listu delujejo pravilno, ne da bi pri tem trčila z imeni na drugih listih.

Če se želite sklicevati na ime z lokalnim področjem uporabe, lahko ime lista prednastavite pred imenom obsega:

 
Sheet1!total_revenue Sheet2!total_revenue Sheet3!total_revenue

Imena razponov, ustvarjena z polje z imenom samodejno imajo globalni obseg. Če želite preglasiti to vedenje, pri določanju imena dodajte ime lista:

 
Sheet3!my_new_name

Globalni obseg

Globalni obseg pomeni, da bo ime delovalo kjer koli v delovnem zvezku. Celico lahko na primer poimenujete 'last_update', v celico vnesete datum. Nato lahko uporabite spodnjo formulo za prikaz datuma, nazadnje posodobljenega na katerem koli delovnem listu.

 
=last_update

Globalna imena morajo biti edinstvena v delovnem zvezku.

Lokalni obseg

Poimenovani obsegi z lokalnim obsegom so smiselni za delovne liste, ki uporabljajo imenovane obsege samo za lokalne predpostavke. Morda imate na primer delovni zvezek z mesečnimi listi za sledenje (enega na mesec), ki uporabljajo poimenovane obsege z istim imenom, vse z lokalnim obsegom. Lokalni obseg omogoča, da imena na vsakem listu delujejo pravilno, ne da bi pri tem trčila z imeni na drugih listih.

Upravljanje obsega imenovanega obsega

Privzeto so nova imena, ustvarjena z imenskim poljem, globalna in po ustvarjanju ne morete urejati obsega imenovanega obsega. Kot rešitev pa lahko izbrišete in znova ustvarite ime z želenim obsegom.

Če želite spremeniti več imen hkrati iz globalnih v lokalna, je včasih smiselno kopirati list, ki vsebuje imena. Ko podvojite delovni list, ki vsebuje imenovane obsege, Excel kopira imenovane obsege na drugi list, hkrati pa obseg spremeni v lokalno. Ko imate drugi list z imeni z lokalnim obsegom, lahko prvi list po želji izbrišete.

Jan Karel Pieterse in Charles Williams so razvili pripomoček, imenovan Upravitelj imen, ki ponuja številne uporabne operacije za imenovana območja. Ti lahko tukaj naložite pripomoček Name Manager .

Avtor Dave Bruns


^