Excel

Nimetty alueet Excelissä

Named Ranges Excel

Nimetyt alueet ovat yksi näistä vanhoista Excelin ominaisuuksista, joita harvat käyttäjät ymmärtävät. Uudet käyttäjät saattavat pitää heitä outoina ja pelottavina, ja jopa vanhat kädet saattavat välttää niitä, koska ne näyttävät turhilta ja monimutkaisilta.

Mutta nimetyt alueet ovat itse asiassa melko siisti ominaisuus. He voivat tehdä kaavoista * paljon * helpompia luoda, lukea ja ylläpitää. Ja bonuksena ne tekevät kaavojen uudelleenkäytöstä helpompaa (kannettavampaa).



Itse asiassa käytän nimettyjä alueita koko ajan testattaessa ja prototyyppejä tehtäessä. Ne auttavat minua saamaan kaavat toimimaan nopeammin. Käytän myös nimettyjä alueita, koska olen laiska, enkä halua kirjoittaa monimutkaisia ​​viitteitä :)



Nimettyjen alueiden perusteet Excelissä

Mikä on nimetty alue?

Nimetty alue on vain ihmisen luettavissa oleva nimi Excel-solualueelle. Esimerkiksi, jos nimeän alueen A1: A100 'data', voin käyttää MAX-arvoa saadaksesi maksimiarvon yksinkertaisella kaavalla:

 
 = MAX (data) // max value

Yksinkertainen nimetty alue kutsutaan



Nimettyjen alueiden kauneus on, että voit käyttää merkityksellisiä nimiä kaavoissasi ajattelematta soluviittauksia. Kun sinulla on nimetty alue, käytä sitä vain kuten soluviite. Kaikki nämä kaavat ovat kelvollisia nimetyllä alueella 'data':

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

Video: Kuinka luoda nimetty alue

Nimettyjen alueiden luominen on helppoa

Nimettyjen alueiden luominen on nopeaa ja helppoa. Valitse vain solualue ja kirjoita nimi nimiruutuun. Kun painat Return-näppäintä, nimi luodaan:



Luo nimetty alue nopeasti nimiruudulla

Testaa uusi alue nopeasti valitsemalla uusi nimi nimiruudun vieressä olevasta avattavasta valikosta. Excel valitsee alueen laskentataulukosta.

Excel voi luoda nimiä automaattisesti (ctrl + shift + F3)

Jos sinulla on hyvin jäsennelty tieto ja tunnisteita, voit pyytää Exceliä luomaan nimettyjä alueita sinulle. Valitse vain tiedot yhdessä tarrojen kanssa ja käytä valintanauhan Kaavat-välilehden Luo valinnasta -komentoa:

Luo nimiä valintakomennosta nauhalle

Voit myös käyttää pikanäppäimen ohjainta + vaihto + F3.

Tämän ominaisuuden avulla voimme luoda nimettyjä alueita 12 valtion populaatiolle yhdessä vaiheessa:

kuinka löytää päällekkäisiä tietueita Excelistä

Luo nimiä valinnasta valituilla tiedoilla ja tunnisteilla

Kun napsautat OK, nimet luodaan. Löydät kaikki uudet nimet nimiruudun vieressä olevasta avattavasta valikosta:

Uudet nimet näkyvät myös avattavassa nimikentän valikossa

Kun nimet on luotu, voit käyttää niitä tällaisissa kaavoissa

 
= SUM (MN,WI,MI)

Päivitä nimetyt alueet nimihallinnassa (Control + F3)

Kun olet luonut nimetyn alueen, käytä Nimihallinta (Control + F3) päivittää tarvittaessa. Valitse nimi, jota haluat työskennellä, vaihda sitten viite suoraan (eli muokkaa 'viittaa') tai napsauta oikealla olevaa painiketta ja valitse uusi alue.

Päivitetty nimetty alue nimihallinnalla

Viitteen päivittämiseksi ei tarvitse napsauttaa Muokkaa-painiketta. Kun napsautat Sulje, alueen nimi päivitetään.

Huomaa: Jos valitset koko nimellisen alueen laskentataulukosta, voit vetää uuteen sijaintiin ja viite päivitetään automaattisesti. En kuitenkaan tiedä tapaa säätää alueviittauksia napsauttamalla ja vetämällä suoraan laskentataulukossa. Jos tiedät tapan tehdä tämä, soita alla!

Näytä kaikki nimetyt alueet (kontrolli + F3)

Jos haluat nähdä kaikki työkirjan nimetyt alueet, käytä nimikentän vieressä olevaa avattavaa valikkoa.

Jos haluat nähdä tarkempia tietoja, avaa nimihallinta (Control + F3), jossa luetellaan kaikki nimet ja viitteet sekä suodatin:

Nimihallinta näyttää kaikki uudet nimet

Huomaa: Macissa ei ole nimenhallintaa, joten näet sen sijaan Määritä nimi -valintaikkunan.

Kopioi ja liitä kaikki nimetyt alueet (F3)

Jos haluat pysyvämmän tietueiden nimetyistä alueista työkirjaan, voit liittää täydellisen luettelon nimistä mihin tahansa. Valitse Kaavat> Käytä kaavassa (tai käytä pikakuvaketta F3) ja valitse Liitä nimet> Liitä luettelo:

Liitä nimet -valintaikkuna

Kun napsautat Liitä luettelo -painiketta, näet nimet ja viitteet, jotka on liitetty taulukkoon:

Kun olet liittänyt nimetyt alueet taulukkoon

Katso nimet suoraan laskentataulukosta

Jos asetat zoomaustason alle 40 prosenttiin, Excel näyttää alueen nimet suoraan laskentataulukossa:

Jos zoomaustaso on <40%, Excel näyttää alueen nimet

Kiitos tästä vinkistä, Felipe!

Nimillä on säännöt

Noudata seuraavia sääntöjä luodessasi nimettyjä alueita:

  1. Nimet on aloitettava kirjaimella, alaviivalla (_) tai vinoviivalla ()
  2. Nimet eivät voi sisältää välilyöntejä ja useimpia välimerkkejä.
  3. Nimet eivät voi olla ristiriidassa soluviittausten kanssa - et voi nimetä aluetta A1 tai Z100.
  4. Yksittäiset kirjaimet ovat OK nimille ('a', 'b', 'c' jne.), Mutta kirjaimet 'r' ja 'c' on varattu.
  5. Nimet eivät erottele isoja ja pieniä kirjaimia - 'koti', 'koti' ja 'hoMe' ovat kaikki samat kuin Excelissä.

Nimetyt alueet kaavoissa

Nimettyjä alueita on helppo käyttää kaavoissa

Oletetaan esimerkiksi, että nimeät työkirjan solun päivitetyksi. Ajatuksena on, että voit lisätä nykyisen päivämäärän soluun (Ctrl +) ja viitata päivämäärään muualla työkirjassa.

Nimetyn alueen käyttö tekstikaavassa

B8: n kaava näyttää tältä:

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

Voit liittää tämän kaavan mihin tahansa työkirjaan, ja se näkyy oikein. Aina kun muutat päivämäärää päivitetyssä, viesti päivittyy kaikkialla, missä kaavaa käytetään. Katso tällä sivulla lisää esimerkkejä.

Nimetyt alueet ilmestyvät kaavaa kirjoitettaessa

Kun olet luonut nimetyn alueen, se näkyy automaattisesti kaavoissa, kun kirjoitat nimen ensimmäisen kirjaimen. Paina sarkainnäppäintä syöttääksesi nimen, kun sinulla on ottelu ja haluat, että Excel antaa nimen.

Nimetyt alueet ilmestyvät, kun syötät kaavoja

Nimetyt alueet voivat toimia kuten vakiot

Koska nimetyt alueet luodaan keskeiseen sijaintiin, voit käyttää niitä kuten vakioita ilman soluviittausta. Voit esimerkiksi luoda nimiä, kuten 'MPG' (mailia per gallona) ja 'CPG' (hinta per gallona) ja määrittää kiinteät arvot:

Nimetyt alueet voivat toimia kuten vakiot ilman soluviittausta

Sitten voit käyttää näitä nimiä missä tahansa kaavoissa ja päivittää niiden arvon yhdessä keskeisessä paikassa.

Käyttämällä nimettyä aluetta kuin vakio kaavassa

Nimetyt alueet ovat oletusarvoisesti absoluuttisia

Oletuksena nimetyt alueet käyttäytyvät kuin absoluuttiset viitteet. Esimerkiksi tässä laskentataulukossa polttoaineen laskentakaava olisi seuraava:

 
=C5/$D

Vakiokaava absoluuttisella osoitteella

Viittaus D2: een on absoluuttinen (lukittu), joten kaava voidaan kopioida alaspäin ilman, että D2 muuttuu.

Jos D2 nimetään MPG: ksi, kaavasta tulee:

 
=C5/MPG

Käyttämällä nimettyä aluetta kuin vakio kaavassa

Koska MPG on oletusarvoisesti absoluuttinen, kaava voidaan kopioida sarakkeesta D sellaisenaan.

Nimetyt alueet voivat myös olla suhteellisia

Vaikka nimetyt alueet ovat oletusarvoisesti absoluuttisia, ne voivat olla myös suhteellisia. Suhteellinen nimetty alue tarkoittaa aluetta, joka on suhteessa aktiivisen solun sijaintiin alueen luomishetkellä . Tämän seurauksena suhteelliset nimelliset alueet ovat hyödyllisiä rakennettaessa yleisiä kaavoja, jotka toimivat missä tahansa niitä siirretään.

Voit esimerkiksi luoda yleisen CellAbove-nimisen alueen seuraavasti:

  1. Valitse solu A2
  2. Control + F3 avaa nimihallinta
  3. Välilehti Viittaa-osioon ja kirjoita sitten: = A1

CellAbove noutaa arvon nyt yllä olevasta solusta missä tahansa sitä käytetään.

Tärkeää: Varmista ennen nimen luomista, että aktiivinen solu on oikeassa paikassa.

Käytä nimettyjä alueita olemassa oleviin kaavoihin

Jos sinulla on olemassa kaavoja, jotka eivät käytä nimettyjä alueita, voit pyytää Exceliä käyttämään nimettyjä alueita kaavoissa puolestasi. Aloita valitsemalla solut, jotka sisältävät päivitettävät kaavat. Suorita sitten Kaavat> Määritä nimet> Käytä nimiä.

Käytä nimiä -valintaikkuna

Tämän jälkeen Excel korvaa viitteet, joilla on vastaava nimetty alue, itse nimellä.

Voit myös käyttää nimiä etsimällä ja korvaamalla:

Nimialueiden käyttäminen etsinnällä ja korvaamisella

Tärkeää: Tallenna varmuuskopio laskentataulukosta ja valitse vain solut, jotka haluat muuttaa, ennen kuin etsit kaavoista Etsi ja korvaa.

Nimettyjen alueiden tärkeimmät edut

Nimetyt alueet helpottavat kaavojen lukemista

Suurin yksittäinen etu nimetyille alueille on, että ne helpottavat kaavojen lukemista ja ylläpitoa. Tämä johtuu siitä, että ne korvaavat salaiset viitteet merkityksellisillä nimillä. Harkitse esimerkiksi tätä laskentataulukkoa, joka sisältää tietoja aurinkokuntamme planeetoista. Ilman nimettyjä alueita VLOOKUP-kaava 'Paikan' hakemiseksi taulukosta on melko salaperäinen:

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

Ilman nimettyjä alueita kaavat voivat olla salaisia

kuinka löytää alennusprosenttilaskuri

B3: E11-nimisellä nimellä 'data' ja H4-nimellä 'planeetta' voimme kuitenkin kirjoittaa kaavoja seuraavasti:

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

Nimettyjen alueiden avulla kaavat voivat olla yksinkertaisia

Yhdellä silmäyksellä näet näiden kaavojen ainoan eron sarakeindeksissä.

Nimetyt alueet tekevät kaavoista kannettavia ja uudelleenkäytettäviä

Nimetyt alueet voivat helpottaa kaavan uudelleenkäyttöä eri laskentataulukossa. Jos määrität nimet etukäteen laskentataulukossa, voit liittää kaavaan, joka käyttää näitä nimiä, ja se 'vain toimii'. Tämä on hieno tapa saada kaava toimimaan nopeasti.

Esimerkiksi tämä kaava laskee yksilölliset arvot numeeristen tietojen alueelle:

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

Jos haluat nopeasti 'siirtää' tämän kaavan omaan laskentataulukkoon, nimeä alue 'data' ja liitä kaava laskentataulukkoon. Niin kauan kuin 'data' sisältää numeerisia arvoja, kaava toimii heti.

Vinkki: Suosittelen, että luot tarvittavat alueiden nimet * ensin * kohdetyökirjaan ja kopioit sitten kaavan vain tekstinä (ts. Älä kopioi kaavan sisältävää solua toiseen laskentataulukkoon, vaan kopioi kaavan teksti ). Tämä estää Exceliä luomasta nimiä lennossa ja l voit hallita nimen luomisprosessia täysin. Jos haluat kopioida vain kaavatekstin, kopioi teksti kaavapalkista tai kopioi toisen sovelluksen kautta (esim. Selain, tekstieditori jne.).

Nimettyjä alueita voidaan käyttää navigointiin

Nimetyt alueet ovat erinomaisia ​​nopeaan navigointiin. Valitse vain nimiruudun vieressä oleva avattava valikko ja valitse nimi. Kun vapautat hiiren, alue valitaan. Kun toisella taulukolla on nimetty alue, sinut ohjataan kyseiselle taulukolle automaattisesti.

Nimetyt alueet mahdollistavat helpon navigoinnin

Nimetyt alueet toimivat hyvin hyperlinkkien kanssa

Nimetyt alueet tekevät hyperlinkeistä helppoja. Esimerkiksi, jos nimeät A1 Taulukko1: ssä 'koti', voit luoda hyperlinkin jonnekin muualle, joka vie sinut takaisin sinne.

Hyperlinkin luominen nimettyyn alueeseen

Esimerkki nimellisestä alueen hyperlinkistä laskentataulukossa

Jos haluat käyttää nimettyä aluetta HYPERLINK-toiminnon sisällä, lisää punnan symboli nimellisen alueen eteen:

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

Huomaa: oudolla tavalla et voi linkittää taulukkoon kuten normaalin alueen nimen. Voit kuitenkin määrittää nimen, joka on yhtä suuri kuin taulukko (ts. = Taulukko1), ja hyperlinkin siihen. Jos joku tietää tapaa linkittää suoraan pöytä, soita sisään!

Nimetyt alueet tietojen validointia varten

Nimialueet toimivat hyvin tietojen validoinnissa, koska niiden avulla voit vahvistaa loogisesti nimettyä viittausta syötteen vahvistamiseen avattavassa valikossa. Alueella alue G4: G8 on nimeltään 'statuslist', ja käytä sitten tietojen validointia tällä linkitetyllä listalla:

Nimettyjen alueiden käyttö tietojen validointiin luettelon kanssa

Tulos on sarakkeen E pudotusvalikko, joka sallii vain nimetyn alueen arvot:

Tietojen vahvistus nimettyjen alueiden esimerkillä

Dynaamiset nimetyt alueet

Nimialueet ovat erittäin hyödyllisiä, kun ne mukautuvat automaattisesti laskentataulukon uusiin tietoihin. Tällä tavalla asetettua aluetta kutsutaan nimellä 'dynaaminen nimetty alue'. Alueen muuttamiseksi dynaamiseksi on kaksi tapaa: kaavat ja taulukot.

Dynaaminen nimetty alue taulukon kanssa

Taulukko on helpoin tapa luoda dynaaminen nimetty alue. Valitse mikä tahansa solu tiedoista ja käytä pikanäppäintä Control + T:

Am Excel -taulukon luominen

Kun luot Excel-taulukon, nimi luodaan automaattisesti (esim. Taulukko1), mutta voit nimetä taulukon uudelleen haluamallasi tavalla. Kun olet luonut taulukon, se laajenee automaattisesti, kun tietoja lisätään.

Pöydät laajenevat automaattisesti ja ne voidaan nimetä uudelleen

Dynaaminen nimetty alue kaavalla

Voit myös luoda dynaamisen nimellisen alueen kaavoilla käyttämällä funktioita, kuten OFFSET ja INDEX. Vaikka nämä kaavat ovat kohtalaisen monimutkaisia, ne tarjoavat kevyen ratkaisun, kun et halua käyttää taulukkoa. Alla olevat linkit tarjoavat esimerkkejä täydellisistä selityksistä:

  • Esimerkki dynaamisen alueen kaavasta, jossa on INDEX
  • Esimerkki dynaamisen alueen kaavasta, jossa on OFFSET

Taulukoiden nimet tietojen validoinnissa

Koska Excel-taulukot tarjoavat automaattisen dynaamisen alueen, ne näyttävät olevan luonnollinen sovitus tietojen validointisääntöihin, joissa tavoitteena on validointi luettelon mukaan, joka saattaa muuttua jatkuvasti. Yksi ongelma taulukoissa on, että et voi käyttää jäsenneltyjä viitteitä suoraan tietojen validoinnin tai ehdollisen muotoilun sääntöjen luomiseen. Toisin sanoen, et voi käyttää taulukon nimeä ehdollisessa muotoilussa tai tietojen vahvistamisen syöttöalueilla.

Kiertotapana voit kuitenkin määrittää nimetyn alueen, joka osoittaa taulukkoa, ja käyttää sitten nimettyä aluetta tietojen validointiin tai ehdolliseen muotoiluun. Alla olevassa videossa käsitellään tätä lähestymistapaa yksityiskohtaisesti.

Video: Kuinka käyttää nimettyjä alueita taulukoiden kanssa

Nimettyjen alueiden poistaminen

Huomautus: Jos sinulla on kaavoja, jotka viittaavat nimettyihin alueisiin, kannattaa ehkä päivittää kaavat ensin ennen nimien poistamista. Muussa tapauksessa näet #NIMI? virheet kaavoissa, jotka viittaavat edelleen poistettuihin nimiin. Tallenna aina laskentataulukko ennen nimettyjen alueiden poistamista, jos sinulla on ongelmia ja sinun on palattava alkuperäiseen.

Nimetyt alueet muuttuvat, kun soluja poistetaan ja lisätään

Kun poistat nimetyn alueen * osan * tai jos lisäät soluja / rivejä / sarakkeita nimettyyn alueeseen, alueen viite muuttuu vastaavasti ja pysyy voimassa. Jos kuitenkin poistat kaikki nimetyn alueen ympäröivät solut, nimetty alue menettää viitteen ja näyttää #REF-virheen. Esimerkiksi, jos annan nimeksi A1 testiksi ja poistan sitten sarakkeen A, nimihallinta näyttää 'viittaa' seuraavasti:

 
=Sheet1!#REF!

Poista nimet Nimihallinnalla

Jos haluat poistaa nimetyt alueet työkirjasta manuaalisesti, avaa nimihallinta, valitse alue ja napsauta Poista-painiketta. Jos haluat poistaa useamman kuin yhden nimen samanaikaisesti, voit valita useita nimiä Vaihto + Napsautus tai Ctrl + Napsauta ja poistaa sitten yhdessä vaiheessa.

Poista virheelliset nimet

Jos sinulla on paljon nimivirheitä sisältäviä nimiä, voit suodattaa virheiden sisältäviä nimien hallintaohjelman suodatinpainiketta:

Nimihallinta-suodatinvalikko

Valitse sitten kaikki nimet ja poista siirto + napsauttamalla.

Nimetyt alueet ja soveltamisala

Nimetyillä alueilla Excelissä on jotain, jota kutsutaan 'laajuudeksi', joka määrittää, onko nimetty alue paikallinen tietylle laskentataulukolle vai globaali koko työkirjassa. Yleisillä nimillä on 'työkirja', ja paikallisten nimien laajuus on sama kuin niiden arkkien nimet, joissa ne esiintyvät. Esimerkiksi paikallisen nimen laajuus voi olla 'Sheet2'.

Soveltamisalan tarkoitus

Nimetyt alueet, joilla on yleinen kattavuus, ovat hyödyllisiä, kun haluat, että kaikilla työkirjan taulukoilla on pääsy tiettyihin tietoihin, muuttujiin tai vakioihin. Voit esimerkiksi käyttää globaalia nimettyä aluetta veroaste-olettamusta, jota käytetään useissa useissa laskentataulukoissa.

Paikallinen soveltamisala

Paikallinen laajuus tarkoittaa, että nimi toimii vain sillä arkilla, jolla se luotiin. Tämä tarkoittaa, että sinulla voi olla useita laskentataulukoita samassa työkirjassa, jotka kaikki käyttävät samaa nimeä. Esimerkiksi, sinulla on ehkä työkirja, jossa on kuukausittaiset seurantataulukot (yksi kuukaudessa) ja joissa käytetään samannimisiä nimettyjä alueita, jotka kaikki määritetään paikallisesti. Tämän avulla voit käyttää samoja kaavoja uudelleen eri taulukoissa. Paikallinen laajuus antaa jokaisen taulukon nimien toimia oikein törmäämättä muiden arkkien nimiin.

Jos haluat viitata paikallisen laajuuden omaavaan nimeen, voit lisätä etuliitteen taulukkoon alueen nimen:

 
Sheet1!total_revenue Sheet2!total_revenue Sheet3!total_revenue

Alueiden nimet, jotka on luotu nimiruutu automaattisesti maailmanlaajuinen. Voit ohittaa tämän toiminnan lisäämällä taulukon nimen määritettäessä nimeä:

 
Sheet3!my_new_name

Globaali soveltamisala

Globaali laajuus tarkoittaa, että nimi toimii missä tahansa työkirjassa. Voit esimerkiksi nimetä solun 'last_update', kirjoittaa päivämäärän soluun. Sitten voit käyttää alla olevaa kaavaa näyttääksesi viimeksi päivitetyn päivämäärän missä tahansa laskentataulukossa.

 
=last_update

Yleisten nimien on oltava yksilöllisiä työkirjassa.

Paikallinen soveltamisala

Paikallisesti rajatut nimetyt alueet ovat järkeviä laskentataulukoille, jotka käyttävät nimettyjä alueita vain paikallisiin oletuksiin. Esimerkiksi, sinulla on ehkä työkirja, jossa on kuukausittaiset seurantataulukot (yksi kuukaudessa) ja joissa käytetään samannimisiä nimettyjä alueita, jotka kaikki määritetään paikallisesti. Paikallinen laajuus antaa jokaisen taulukon nimien toimia oikein törmäämättä muiden arkkien nimiin.

Nimettyjen alueiden hallinta

Oletusarvon mukaan nimikentällä luodut uudet nimet ovat yleisiä, etkä voi muokata nimettyjen alueiden laajuutta sen luomisen jälkeen. Kiertotapana voit kuitenkin poistaa ja luoda haluamasi laajuuden nimen.

Jos haluat muuttaa useita nimiä kerralla globaaleista paikallisiksi, on joskus järkevää kopioida nimet sisältävä taulukko. Kun kopioit nimettyjä alueita sisältävän laskentataulukon, Excel kopioi nimetyt alueet toiseen taulukkoon muuttamalla laajuuden samalla paikalliseksi. Kun sinulla on toinen taulukko, jolla on paikallisesti määriteltyjä nimiä, voit halutessasi poistaa ensimmäisen arkin.

Jan Karel Pieterse ja Charles Williams ovat kehittäneet apuohjelman nimeltä Name Manager, joka tarjoaa monia hyödyllisiä toimintoja nimetyille alueille. Sinä pystyt lataa Name Manager -apuohjelma täältä .

Kirjailija Dave Bruns


^