Nimetyt alueet ovat yksi näistä Excelin rapeista ominaisuuksista, joita harvat käyttäjät ymmärtävät. Uudet käyttäjät voivat pitää niitä outoina ja pelottavina, ja jopa vanhat kädet voivat välttää niitä, koska ne näyttävät turhilta ja monimutkaisilta.
Mutta nimetyt alueet ovat itse asiassa aika hieno ominaisuus. Ne voivat tehdä kaavoista * paljon * helpompia luoda, lukea ja ylläpitää. Ja bonuksena ne helpottavat kaavojen uudelleenkäyttöä (kannettavampia).
Käytän itse asiassa nimettyjä alueita koko ajan kaavojen testauksessa ja prototyyppien laatimisessa. Ne auttavat minua saamaan kaavat toimimaan nopeammin. Käytän myös nimettyjä alueita, koska olen laiska enkä tykkää kirjoittaa monimutkaisia viitteitä :)
Excelin nimettyjen alueiden perusteet
Mikä on nimetty alue?
Nimetty alue on vain ihmisen luettavissa oleva nimi Excel-solualueelle. Jos esimerkiksi annan alueen A1: A100 'dataksi', voin käyttää maksimiarvoa MAX yksinkertaisella kaavalla:
= MAX (data) // max value
Nimettyjen alueiden kauneus on, että voit käyttää merkityksellisiä nimiä kaavoissasi ajattelematta soluviittauksia. Kun sinulla on nimetty alue, käytä sitä aivan kuten soluviittausta. Kaikki nämä kaavat pätevät nimetyllä alueella 'data':
= MAX (data) // max value = MIN (data) // min value = COUNT (data) // total values = AVERAGE (data) // min value
Video: Kuinka luoda nimetty alue
Nimetyn alueen luominen on helppoa
Nimetyn alueen luominen on nopeaa ja helppoa. Valitse vain solualue ja kirjoita nimi nimikenttään. Kun painat paluuta, nimi luodaan:
Jos haluat testata uuden alueen nopeasti, valitse uusi nimi nimikentän vieressä olevasta avattavasta valikosta. Excel valitsee alueen laskentataulukosta.
Excel voi luoda nimet automaattisesti (ctrl + shift + F3)
Jos sinulla on hyvin jäsenneltyjä tietoja tarroilla, voit pyytää Exceliä luomaan sinulle nimettyjä alueita. Valitse vain tiedot ja tarrat ja käytä Luo valinnasta -komentoa valintanauhan Kaavat -välilehdessä:
Voit käyttää myös pikanäppäinohjainta + vaihto + F3.
Tämän ominaisuuden avulla voimme luoda nimettyjä alueita 12 osavaltion väestölle yhdessä vaiheessa:
kuinka löytää päällekkäisiä tietueita Excelistä
Kun napsautat OK, nimet luodaan. Löydät kaikki uudet nimet nimikentän vieressä olevasta avattavasta valikosta:
Kun nimet on luotu, voit käyttää niitä tällaisissa kaavoissa
= SUM (MN,WI,MI)
Päivitä nimialueet Name Managerissa (Control + F3)
Kun olet luonut nimetyn alueen, käytä Nimipäällikkö (Control + F3) päivittää tarvittaessa. Valitse nimi, jonka kanssa haluat työskennellä, muuta sitten viittausta suoraan (eli muokkaa 'viittaa') tai napsauta oikealla olevaa painiketta ja valitse uusi alue.
Sinun ei tarvitse napsauttaa Muokkaa -painiketta päivittääksesi viitteen. Kun napsautat Sulje, alueen nimi päivitetään.
Huomautus: jos valitset laskentataulukosta koko nimetyn alueen, voit vetää uuteen paikkaan ja viite päivitetään automaattisesti. En kuitenkaan tiedä tapaa säätää alueviittauksia napsauttamalla ja vetämällä suoraan laskentataulukosta. Jos tiedät tavan tehdä tämä, ilmoita alla!
Näytä kaikki nimetyt alueet (control + F3)
Jos haluat nähdä kaikki työkirjan nimetyt alueet nopeasti, käytä nimiruudun vieressä olevaa avattavaa valikkoa.
Jos haluat nähdä enemmän yksityiskohtia, avaa Nimienhallinta (Control + F3), joka luettelee kaikki nimet viittauksineen ja tarjoaa myös suodattimen:
Huomautus: Macissa ei ole Name Manageria, joten näet sen sijaan Määritä nimi -valintaikkunan.
Kopioi ja liitä kaikki nimetyt alueet (F3)
Jos haluat pysyvämmän tietueen nimetyistä alueista työkirjassa, voit liittää koko nimiluettelon haluamaasi paikkaan. Valitse Kaavat> Käytä kaavassa (tai käytä pikakuvaketta F3) ja valitse Liitä nimet> Liitä luettelo:
Kun napsautat Liitä luettelo -painiketta, näet laskentataulukkoon liitetyt nimet ja viitteet:
Katso nimet suoraan laskentataulukosta
Jos asetat zoomaustason alle 40%, Excel näyttää alueiden nimet suoraan laskentataulukkoon:
Kiitos tästä vinkistä, Felipe!
Nimillä on säännöt
Kun luot nimettyjä alueita, noudata seuraavia sääntöjä:
- Nimet on aloitettava kirjaimella, alaviivalla (_) tai vinoviivalla ()
- Nimet eivät voi sisältää välilyöntejä ja useimpia välimerkkejä.
- Nimet eivät voi olla ristiriidassa soluviittausten kanssa - et voi nimetä aluetta A1 tai Z100.
- Yksittäiset kirjaimet sopivat nimille ('a', 'b', 'c' jne.), Mutta kirjaimet 'r' ja 'c' on varattu.
- Nimet eivät erota kirjainkokoa-koti, HOME ja HoMe ovat samat Excelille.
Nimetyt alueet kaavoissa
Nimettyjä alueita on helppo käyttää kaavoissa
Oletetaan esimerkiksi, että annat työkirjasi solulle nimen 'päivitetty'. Ajatuksena on, että voit laittaa nykyisen päivämäärän soluun (Ctrl +) ja viitata päivämäärään muualla työkirjassa.
B8: n kaava näyttää tältä:
='Updated: '& TEXT (updated, 'ddd, mmmm d, yyyy')
Voit liittää tämän kaavan mihin tahansa työkirjan kohtaan ja se näkyy oikein. Aina kun muutat päivämäärää päivitetyssä kohdassa, viesti päivittyy kaikkialla, missä kaavaa käytetään. Katso tämä sivu lisää esimerkkejä.
Nimetyt alueet näkyvät kaavaa kirjoitettaessa
Kun olet luonut nimetyn alueen, se näkyy automaattisesti kaavoissa, kun kirjoitat nimen ensimmäisen kirjaimen. Paina sarkainnäppäintä kirjoittaaksesi nimen, kun sinulla on haku, ja haluat Excelin kirjoittavan nimen.
Nimetyt alueet voivat toimia vakioiden tavoin
Koska nimettyjä alueita luodaan keskeiseen sijaintiin, voit käyttää niitä kuten vakioita ilman soluviittausta. Voit esimerkiksi luoda nimiä, kuten 'MPG' (mailia / gallona) ja 'CPG' (hinta / gallona), ja määrittää kiinteät arvot:
Sitten voit käyttää näitä nimiä missä tahansa haluamassasi kaavoissa ja päivittää niiden arvon yhdessä keskeisessä paikassa.
Nimetyt alueet ovat oletusarvoisesti absoluuttisia
Oletusarvoisesti nimetyt alueet toimivat absoluuttisina viittauksina. Esimerkiksi tässä laskentataulukossa polttoaineen laskentakaava olisi:
=C5/$D
Viittaus D2: een on absoluuttinen (lukittu), joten kaava voidaan kopioida ilman, että D2 muuttuu.
Jos nimeämme D2 'MPG', kaavasta tulee:
=C5/MPG
Koska MPG on oletusarvoisesti absoluuttinen, kaava voidaan kopioida sarakkeeseen D sellaisenaan.
Nimetyt alueet voivat olla myös suhteellisia
Vaikka nimetyt alueet ovat oletusarvoisesti absoluuttisia, ne voivat olla myös suhteellisia. Suhteellinen nimetty alue viittaa alueeseen, joka on suhteessa aktiivisen solun sijaintiin kun alue luodaan . Tämän seurauksena suhteelliset nimetyt alueet ovat hyödyllisiä yleisten kaavojen rakentamisessa, jotka toimivat kaikkialla, missä niitä siirretään.
Voit esimerkiksi luoda yleisen CellAbove -nimisen alueen seuraavasti:
- Valitse solu A2
- Avaa Nimenhallinta painamalla Control + F3
- Sarkain Viittaa -osioon ja kirjoita sitten: = A1
CellAbove hakee nyt arvon yllä olevasta solusta missä tahansa sitä käytetään.
Tärkeää: varmista, että aktiivinen solu on oikeassa paikassa ennen nimen luomista.
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 päivitettävät kaavat sisältävät solut. Suorita sitten kaavat> Määritä nimet> Käytä nimiä.
Excel korvaa sitten viittaukset, joilla on vastaava nimetty alue, nimen kanssa.
Voit myös käyttää nimiä etsimällä ja korvaamalla:
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 viittaukset merkityksellisillä nimillä. Ajattele esimerkiksi tätä laskentataulukkoa, jossa on tietoja aurinkokuntamme planeetoista. Ilman nimettyjä alueita VLOOKUP -kaava, joka hakee 'Position' taulukosta, on varsin salainen:
= VLOOKUP ($H,$B:$E,2,0)
kuinka löytää alennusprosenttilaskuri
Kuitenkin, kun B3: E11 on nimeltään 'data' ja H4 'planeetta', voimme kirjoittaa seuraavanlaisia kaavoja:
= VLOOKUP (planet,data,2,0) // position = VLOOKUP (planet,data,3,0) // diameter = VLOOKUP (planet,data,4,0) // satellites
Näet yhdellä silmäyksellä näiden kaavojen ainoan eron sarakehakemistossa.
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 loistava tapa saada kaava nopeasti toimimaan.
Esimerkiksi tämä kaava laskee yksilölliset arvot numeerisen datan alueella:
= SUM (--( FREQUENCY (data,data)>0))
Jos haluat 'siirtää' tämän kaavan nopeasti omaan laskentataulukkoosi, 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 * kohdekirjaan ja kopioit sitten kaavan vain tekstinä (eli älä kopioi kaavaa sisältävää solua toiselle laskentataulukolle, vaan kopioi kaavan teksti ). Tämä estää Excelin luomasta nimiä lennossa ja l voit hallita nimen luomista kokonaan. Jos haluat kopioida vain kaavan tekstiä, kopioi teksti kaavapalkista tai kopioi toisen sovelluksen (esim. Selaimen, tekstieditorin jne.) Kautta.
Nimettyjä alueita voidaan käyttää navigointiin
Nimetyt alueet sopivat erinomaisesti nopeaan navigointiin. Valitse vain nimiruudun vieressä oleva pudotusvalikko ja valitse nimi. Kun vapautat hiiren, alue valitaan. Kun toisella taulukolla on nimetty alue, sinut ohjataan automaattisesti kyseiselle taulukolle.
Nimetyt alueet toimivat hyvin hyperlinkkien kanssa
Nimetyt alueet helpottavat hyperlinkkejä. Jos esimerkiksi annat A1 -nimen taulukossa 1 'koti', voit luoda hyperlinkin jonnekin muualle, joka vie sinut takaisin sinne.
Jos haluat käyttää nimettyä aluetta HYPERLINK -toiminnon sisällä, lisää punnan symboli nimetyn alueen eteen:
= HYPERLINK ('#home','take me home')
Huomautus: outoa kyllä, et voi hyperlinkittää taulukkoon tavallisen alueen nimen tapaan. Voit kuitenkin määrittää taulukkoa vastaavan nimen (eli = Taulukko1) ja hyperlinkin siihen. Jos joku tietää tavan linkittää pöytä suoraan, ilmoittaudu mukaan!
Nimetyt alueet tietojen validointia varten
Nimialueet toimivat hyvin tietojen validoinnissa, koska niiden avulla voit käyttää loogisesti nimettyä viitettä syötteen vahvistamiseen avattavasta valikosta. Alue G4: G8 on nimeltään 'statuslist', ja käytä sitten tietojen validointia linkitetyllä luettelolla seuraavasti:
Tuloksena on sarakkeen E avattava valikko, joka sallii vain nimetyn alueen arvot:
Dynaamiset nimetyt alueet
Nimialueet ovat erittäin hyödyllisiä, kun ne mukautuvat automaattisesti laskentataulukon uusiin tietoihin. Tällä tavalla asetettua aluetta kutsutaan 'dynaamiseksi nimetyksi alueeksi'. On kaksi tapaa tehdä alueesta dynaaminen: kaavat ja taulukot.
Dynaaminen nimetty alue taulukon kanssa
Taulukko on helpoin tapa luoda dynaaminen nimetty alue. Valitse mikä tahansa solu tiedoista ja käytä sitten pikanäppäintä Control + T:
Kun luot Excel -taulukon, nimi luodaan automaattisesti (esim. Taulukko 1), mutta voit nimetä taulukon uudelleen haluamallasi tavalla. Kun olet luonut taulukon, se laajenee automaattisesti, kun tietoja lisätään.
Dynaaminen nimetty alue, jolla on kaava
Voit myös luoda dynaamisen nimetty alueen kaavoilla käyttämällä toimintoja, kuten OFFSET ja INDEX. Vaikka nämä kaavat ovat kohtalaisen monimutkaisia, ne tarjoavat kevyen ratkaisun, kun et halua käyttää taulukkoa. Alla olevista linkeistä löytyy esimerkkejä ja täydelliset selitykset:
- Esimerkki dynaamisen alueen kaavasta INDEXin kanssa
- Esimerkki dynaamisen alueen kaavasta OFFSETilla
Taulukon nimet tietojen validoinnissa
Koska Excel -taulukot tarjoavat automaattisen dynaamisen alueen, ne näyttävät sopivan luonnollisesti tietojen validointisääntöihin, joiden tavoitteena on validoida luettelo, joka saattaa aina muuttua. Yksi taulukoiden ongelma on kuitenkin se, että et voi käyttää strukturoituja viittauksia suoraan tietojen validointi- tai ehdollisen muotoilusääntöjen luomiseen. Toisin sanoen, et voi käyttää taulukon nimeä ehdollisen muotoilun tai tietojen validoinnin syöttöalueilla.
Voit kuitenkin kiertotapaksi määrittää määritetyn alueen, joka osoittaa taulukkoon, ja käyttää sitten nimettyä aluetta tietojen validointiin tai ehdolliseen muotoiluun. Alla oleva video kulkee tämän lähestymistavan läpi yksityiskohtaisesti.
Video: Nimettyjen alueiden käyttäminen taulukoiden kanssa
Nimettyjen alueiden poistaminen
Huomautus: Jos sinulla on kaavoja, jotka viittaavat nimettyihin alueisiin, saatat haluta päivittää kaavat ennen nimien poistamista. Muussa tapauksessa näet #NAME? virheitä kaavoissa, jotka edelleen viittaavat poistettuihin nimiin. Tallenna laskentataulukko aina ennen nimettyjen alueiden poistamista, jos sinulla on ongelmia ja haluat palata alkuperäiseen.
Nimetyt alueet muuttuvat, kun poistetaan ja lisätään soluja
Kun poistat * osan * nimetystä alueesta tai lisäät soluja/rivejä/sarakkeita nimetyn alueen sisälle, alueviite muuttuu vastaavasti ja pysyy voimassa. Jos kuitenkin poistat kaikki solut, jotka sulkevat nimetyn alueen, nimetty alue menettää viitteen ja näyttää #REF -virheen. Jos esimerkiksi annan A1 -nimen testiksi ja poistan sarakkeen A, nimenhallinta näyttää 'viittaa' muotoon:
=Sheet1!#REF!
Poista nimet Name Managerilla
Jos haluat poistaa nimetyt alueet työkirjasta manuaalisesti, avaa nimenhallinta, valitse alue ja napsauta Poista -painiketta. Jos haluat poistaa useamman kuin yhden nimen samanaikaisesti, voit valita useita nimiä vaihtonäppäimellä + napsauttamalla tai Ctrl + napsauttamalla ja poistaa sen sitten yhdessä vaiheessa.
Poista virheellisiä nimiä
Jos sinulla on paljon nimiä, joissa on viittausvirheitä, voit suodattaa virheellisiä nimiä käyttämällä nimienhallinnan suodatinpainiketta:
Valitse sitten kaikki nimet ja poista ne vaihtamalla+napsauttamalla.
Nimetyt alueet ja laajuus
Excelissä nimetyillä alueilla on jotain nimeltään 'laajuus', joka määrittää, onko nimetty alue paikallinen tietylle laskentataulukolle vai maailmanlaajuinen koko työkirjassa. Globaaleilla nimillä on 'työkirjan' soveltamisala ja paikallisilla nimillä sama kuin taulukon nimi, jossa ne ovat. Paikallisen nimen laajuus voi olla esimerkiksi Sheet2.
Soveltamisalan tarkoitus
Nimetyt alueet, joilla on yleinen laajuus, ovat hyödyllisiä, kun haluat, että työkirjan kaikki taulukot voivat käyttää tiettyjä tietoja, muuttujia tai vakioita. Voit esimerkiksi käyttää globaalia nimettyä aluetta verokantaoletuksena, jota käytetään useissa useissa laskentataulukoissa.
Paikallinen soveltamisala
Paikallinen laajuus tarkoittaa, että nimi toimii vain sillä arkilla, jolle se on luotu. Tämä tarkoittaa, että samassa työkirjassa voi olla useita laskentataulukoita, joilla kaikilla on sama nimi. Esimerkiksi sinulla voi olla esimerkiksi työkirja, jossa on kuukausittaiset seurantataulukot (yksi kuukaudessa), jotka käyttävät samannimisiä nimettyjä alueita, jotka kaikki on määritetty paikallisesti. Tämä voi antaa sinun käyttää samoja kaavoja uudelleen eri arkeissa. Paikallinen laajuus sallii jokaisen taulukon nimet toimia oikein ilman, että törmää muiden lehtien nimiin.
Jos haluat viitata paikallisen laajuuden omaavaan nimeen, voit liittää taulukon nimen etusijalle alueen nimen:
Sheet1!total_revenue Sheet2!total_revenue Sheet3!total_revenue
Alueiden nimet, jotka on luotu nimilaatikko on automaattisesti maailmanlaajuinen. Voit ohittaa tämän toiminnon lisäämällä arkin nimen määriteltäessä nimeä:
Sheet3!my_new_name Globaali soveltamisala
Yleinen laajuus tarkoittaa, että nimi toimii missä tahansa työkirjassa. Voit esimerkiksi antaa solulle nimen 'last_update', kirjoittaa päivämäärän soluun. Sitten voit käyttää alla olevaa kaavaa näyttääksesi viimeisimmän päivityksen missä tahansa laskentataulukossa.
=last_update Globaalien nimien on oltava työkirjassa yksilöllisiä.
Paikallinen soveltamisala
Paikallisesti laajennetut nimetyt alueet ovat järkeviä laskentataulukoille, jotka käyttävät nimettyjä alueita vain paikallisille oletuksille. Esimerkiksi sinulla voi olla esimerkiksi työkirja, jossa on kuukausittaiset seurantataulukot (yksi kuukaudessa), jotka käyttävät samannimisiä nimettyjä alueita, jotka kaikki on määritetty paikallisesti. Paikallinen laajuus sallii jokaisen taulukon nimet toimia oikein ilman, että törmää muiden lehtien nimiin.
Nimitetyn alueen laajuuden hallinta
Nimilaatikolla luodut uudet nimet ovat oletuksena maailmanlaajuisia, etkä voi muokata nimetyn alueen laajuutta sen luomisen jälkeen. Voit kuitenkin kiertää ongelman poistamalla ja luomalla nimen uudelleen halutulla laajuudella.
Jos haluat muuttaa useita nimiä kerralla globaalista paikalliseksi, joskus on järkevää kopioida nimet sisältävä taulukko. Kun monistat laskentataulukon, joka sisältää nimettyjä alueita, Excel kopioi nimetyt alueet toiselle arkille ja muuttaa samalla laajuuden paikalliseksi. Kun sinulla on toinen arkki, jossa on paikallisesti määritellyt nimet, voit 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
