Nimettyjen alueiden luominen Excelissä (vaiheittainen opas)

Mitä nimessä on?

Jos käytät Excel -laskentataulukoita, se voi tarkoittaa paljon ajan säästämistä ja tehokkuutta.

Tässä opetusohjelmassa opit luomaan nimettyjä alueita Excelissä ja kuinka käyttää sitä ajan säästämiseen.

Nimetyt alueet Excelissä - Johdanto

Jos jonkun on soitettava minulle tai viitattava minuun, hän käyttää nimeäni (sen sijaan, että sanoisi, että mies oleskelee niin ja näin paikassa, jolla on niin ja näin korkeus ja paino).

Oikein?

Samoin Excelissä voit antaa solulle tai solualueelle nimen.

Nyt voit käyttää soluviittauksen (kuten A1 tai A1: A10) käyttämisen sijaan sille määrittämääsi nimeä.

Oletetaan esimerkiksi, että sinulla on alla esitetyn kaltainen tietojoukko:

Jos tässä tietojoukossa sinun on viitattava alueeseen, jolla on päivämäärä, sinun on käytettävä kaavoissa A2: A11. Samoin myyntiedustajan ja myynnin osalta on käytettävä B2: B11 ja C2: C11.

Vaikka on hyvä, kun sinulla on vain pari datapistettä, mutta jos sinulla on valtavia monimutkaisia ​​tietojoukkoja, soluviittausten käyttäminen tietojen viittaamiseen voi olla aikaa vievää.

Excelin nimettyjen alueiden avulla on helppo viitata Excelin tietojoukkoihin.

Voit luoda nimetyn alueen Excelissä kullekin tietoluokalle ja käyttää sitten sitä nimeä soluviittausten sijaan. Esimerkiksi päivämäärille voidaan antaa päivämäärä, myyntiedustajatiedoille SalesRep ja myyntitiedoille myynti.

Voit myös luoda nimen yhdelle solulle. Jos sinulla on esimerkiksi myyntipalkkioprosentti solussa, voit nimetä sen soluksi ”Komissio”.

Nimettyjen alueiden luomisen edut Excelissä

Tässä on edut nimettyjen alueiden käyttämisestä Excelissä.

Käytä nimiä soluviittausten sijaan

Kun luot nimettyjä alueita Excelissä, voit käyttää näitä nimiä soluviittausten sijaan.

Voit esimerkiksi käyttää yllä olevaa tietojoukkoa = SUMMA (MYYNTI) = SUMMA (C2: C11) sijasta.

Katso alla lueteltuja kaavoja. Soluviittausten käytön sijaan olen käyttänyt nimettyjä alueita.

• Myynnit, joiden arvo on yli 500: = COUNTIF (myynti,> 500 ″)
• Kaiken Tomin tekemän myynnin summa: = SUMIF (SalesRep, ”Tom”, Myynti)
• Joen palkkio (Joen myynti kerrottuna provisioprosentilla):
  = SUMIF (SalesRep, ”Joe”, myynti)*Komissio

Olet samaa mieltä siitä, että nämä kaavat ovat helppoja luoda ja helposti ymmärrettäviä (varsinkin kun jaat sen jonkun muun kanssa tai käyt itse uudelleen.

Ei tarvitse palata tietojoukkoon solujen valitsemiseksi

Toinen merkittävä etu nimettyjen alueiden käyttämisestä Excelissä on, että sinun ei tarvitse palata ja valita solualueita.

Voit kirjoittaa vain muutaman aakkosen kyseisestä nimetystä alueesta, ja Excel näyttää vastaavat nimetyt alueet (kuten alla):

Nimetyt alueet tekevät kaavoista dynaamisia

Käyttämällä nimettyjä alueita Excelissä voit tehdä Excel -kaavoista dynaamisia.

Esimerkiksi myyntipalkkiota käytettäessä arvon 2,5%sijaan voit käyttää nimettyä aluetta.

Jos yrityksesi päättää myöhemmin korottaa palkkion 3%: iin, voit yksinkertaisesti päivittää nimettyä aluetta, ja kaikki laskelmat päivittyvät automaattisesti vastaamaan uutta provisiota.

Nimettyjen alueiden luominen Excelissä

Tässä on kolme tapaa luoda nimettyjä alueita Excelissä:

Menetelmä #1 - Määritä nimi

Tässä on vaiheet nimettyjen alueiden luomiseksi Excelissä Määritä nimi:

• Valitse alue, jolle haluat luoda nimetyn alueen Excelissä.
• Siirry kohtaan Kaavat -> Määritä nimi.
• Kirjoita Uusi nimi -valintaikkunaan Nimi, jonka haluat määrittää valitulle tietoalueelle. Voit määrittää laajuuden koko työkirjaksi tai tietylle laskentataulukolle. Jos valitset tietyn taulukon, nimi ei ole käytettävissä muilla arkeilla.
• Napsauta OK.

Tämä luo nimellisen alueen SALESREP.

Menetelmä #2: Nimi -ruudun käyttäminen

• Valitse alue, jolle haluat luoda nimen (älä valitse otsikoita).
• Siirry kaavapalkin vasemmalla puolella olevaan Nimi -ruutuun ja kirjoita sen nimen nimi, jolla haluat luoda nimettyä aluetta.
• Huomaa, että täällä luotu nimi on saatavana koko työkirjalle. Jos haluat rajoittaa sen laskentataulukkoon, käytä menetelmää 1.

Menetelmä #3: Luo valintavaihtoehdon avulla

Tämä on suositeltava tapa, kun sinulla on tietoja taulukkomuodossa ja haluat luoda nimetyn alueen kullekin sarakkeelle/riville.

Jos esimerkiksi haluat alla olevassa tietojoukossa luoda nopeasti kolme nimettyä aluetta (päivämäärä, myyntitapahtuma ja myynti), voit käyttää alla esitettyä menetelmää.

Voit luoda nimettyjä alueita nopeasti tietojoukosta seuraavasti:

• Valitse koko tietojoukko (otsikot mukaan lukien).
• Siirry kohtaan Kaavat -> Luo valinnasta (Pikanäppäin - Control + Vaihto + F3). Se avaa Luo nimet valikoimasta -valintaikkunan.
• Valitse Luo nimet valinnasta -valintaikkunassa vaihtoehdot, joissa on otsikot. Tässä tapauksessa valitsemme vain ylärivin, koska otsikko on ylärivillä. Jos otsikot ovat sekä ylärivillä että vasemmassa sarakkeessa, voit valita molemmat. Samoin, jos tietosi on järjestetty, kun otsikot ovat vain vasemmassa sarakkeessa, tarkistat vain vasemman sarakkeen vaihtoehdon.

Tämä luo kolme nimettyä aluetta - päivämäärä, myynti_Rep ja myynti.

Huomaa, että se poimii nimet automaattisesti otsikoista. Jos sanojen välissä on välilyöntiä, se lisää alaviivan (koska välilyöntejä ei voi olla nimetyillä alueilla).

Nimeämiskäytäntö nimetyille alueille Excelissä

On olemassa tiettyjä nimeämissääntöjä, jotka sinun on tiedettävä, kun luot nimettyjä alueita Excelissä:

• Nimetyn alueen ensimmäisen merkin tulee olla kirjain ja alaviiva (_) tai vinoviiva (\). Jos se on jotain muuta, se näyttää virheen. Loput merkit voivat olla kirjaimia, numeroita, erikoismerkkejä, pisteitä tai alaviivoja.
• Et voi käyttää nimiä, jotka edustavat myös soluviittauksia Excelissä. Et esimerkiksi voi käyttää AB1: tä, koska se on myös soluviittaus.
• Et voi käyttää välilyöntejä luodessasi nimettyjä alueita. Esimerkiksi myyntiedustajaa ei voi nimetä alueeksi. Jos haluat yhdistää kaksi sanaa ja luoda nimetyn alueen, luo se alaviiva-, piste- tai isoilla kirjaimilla. Sinulla voi olla esimerkiksi Sales_Rep, SalesRep tai SalesRep.
  • Kun Excel luo nimettyjä alueita, se käsittelee isoja ja pieniä kirjaimia samalla tavalla. Jos esimerkiksi luot nimitetyn alueen SALES, et voi luoda toista nimettyä aluetta, kuten "myynti" tai "Myynti".
• Nimetty alue voi olla enintään 255 merkkiä pitkä.

Liian monta nimettyä aluetta Excelissä? Älä huoli

Joskus suurissa tietojoukoissa ja monimutkaisissa malleissa saatat joutua luomaan paljon nimettyjä alueita Excelissä.

Mitä jos et muista luomasi nimeämän alueen nimeä?

Älä huoli - Tässä on hyödyllisiä vinkkejä.

Kaikkien nimettyjen alueiden nimien saaminen

Tässä on vaiheet, joilla saat luettelon kaikista luomistasi nimetyistä alueista:

• Siirry Kaavat -välilehdelle.
• Napsauta Määritetty nimetty -ryhmässä Käytä kaavassa.
• Napsauta "Liitä nimet".

Tämä antaa sinulle luettelon kaikista työkirjan nimetyistä alueista. Jos haluat käyttää nimettyä aluetta (kaavoissa tai solussa), kaksoisnapsauta sitä.

Vastaavien nimettyjen alueiden näyttäminen

• Jos sinulla on jonkinlainen käsitys nimestä, kirjoita muutama alkumerkki, ja Excel näyttää avattavan valikon vastaavista nimistä.

Nimettyjen alueiden muokkaaminen Excelissä

Jos olet jo luonut nimetyn alueen, voit muokata sitä seuraavasti:

• Siirry Kaavat -välilehteen ja napsauta Nimenhallinta.
• Nimenhallinta -valintaikkunassa luetellaan kaikki kyseisen työkirjan nimetyt alueet. Kaksoisnapsauta muokattavaa nimettyä aluetta.
• Tee muutokset Muokkaa nimeä -valintaikkunassa.
• Napsauta OK.
• Sulje Nimenhallinta -valintaikkuna.

Hyödyllisiä nimettyjen alueiden pikanäppäimiä (F3: n teho)

Seuraavassa on muutamia hyödyllisiä pikanäppäimiä, jotka ovat käteviä, kun käytät nimettyjä alueita Excelissä:

• Luettelo kaikista nimetyistä alueista ja liittäminen kaavaan: F3
• Uuden nimen luominen Name Manager -valintaikkunan avulla: Control + F3
• Nimettyjen alueiden luominen valinnasta: Ctrl + Vaihto + F3

Dynaamisten nimettyjen alueiden luominen Excelissä

Tähän mennessä tässä opetusohjelmassa olemme luoneet staattisia nimettyjä alueita.

Tämä tarkoittaa, että nämä nimetyt alueet viittaavat aina samaan tietojoukkoon.

Jos esimerkiksi A1: A10 on nimetty myyntiin, se viittaa aina kohtaan A1: A10.

Jos lisäät lisää myyntitietoja, sinun on siirryttävä manuaalisesti päivittämään viittaus nimetyllä alueella.

Aina laajenevien tietojoukkojen maailmassa tämä saattaa viedä paljon aikaa. Aina kun saat uusia tietoja, sinun on ehkä päivitettävä Excelissä nimetyt alueet.

Tämän ongelman ratkaisemiseksi voimme luoda Excelissä dynaamisia nimettyjä alueita, jotka ottavat automaattisesti huomioon lisätiedot ja sisällyttävät ne olemassa olevaan nimettyyn alueeseen.

Jos esimerkiksi lisään kaksi muuta myyntitietopistettä, dynaaminen nimetty alue viittaa automaattisesti kohtaan A1: A12.

Tällainen dynaaminen nimetty alue voidaan luoda käyttämällä Excel INDEX -toimintoa. Sen sijaan, että määrittäisimme soluviittauksia luodessasi nimettyä aluetta, määritämme kaavan. Kaava päivittyy automaattisesti, kun tiedot lisätään tai poistetaan.

Katsotaanpa, kuinka luoda dynaamisia nimettyjä alueita Excelissä.

Oletetaan, että meillä on myyntitiedot solussa A2: A11.

Voit luoda dynaamisia nimettyjä alueita Excelissä seuraavasti:

1. 1. Siirry Kaava -välilehteen ja napsauta Määritä nimi.
   2. Kirjoita Uusi nimi -valintaikkunaan seuraava:
      • Nimi: Myynti
      • Soveltamisala: Työkirja
      • Viittaa: = $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))
   3. Napsauta OK.

Tehty!

Sinulla on nyt dynaaminen nimetty alue, jonka nimi on "Myynti". Tämä päivittyy automaattisesti aina, kun lisäät siihen tietoja tai poistat tietoja.

Miten dynaamiset nimetyt alueet toimivat?

Selittääksesi, miten tämä toimii, sinun on tiedettävä hieman enemmän Excel INDEX -toiminnosta.

Useimmat ihmiset käyttävät INDEXiä arvon palauttamiseksi luettelosta rivin ja sarakkeen numeron perusteella.

Mutta INDEX -toiminnolla on myös toinen puoli.

Se voidaan käyttää palauttaa soluviittauksen kun sitä käytetään osana soluviittausta.

Esimerkiksi tässä on kaava, jolla olemme luoneet dynaamisen nimetty alueen:

= $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, "" & ""))

INDEKSI ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””) -> Tämän kaavan osan odotetaan palauttavan arvon (joka olisi 10. arvo luettelosta, kun otetaan huomioon, että niitä on kymmenen).

Kuitenkin, kun sitä käytetään viitteen edessä (=$ 2 $:INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))) se palauttaa viitteen soluun arvon sijasta.

Näin ollen se palauttaa = $ A $ 2: $ A $ 11

Jos lisäämme kaksi lisäarvoa myyntisarakkeeseen, se palauttaa = $ A $ 2: $ A $ 13

Kun lisäät uusia tietoja luetteloon, Excel COUNTIF -toiminto palauttaa tietojen muiden kuin tyhjien solujen määrän. INDEX -toiminto käyttää tätä numeroa hakemaan luettelon viimeisen kohteen soluviittauksen.

merkintä:

• Tämä toimii vain, jos tiedoissa ei ole tyhjiä soluja.
• Yllä olevassa esimerkissä olen määrittänyt suuren määrän soluja (A2: A100) Named Range -kaavalle. Voit muokata tätä tietojoukon perusteella.

Voit myös käyttää OFFSET -toimintoa dynaamisten nimettyjen alueiden luomiseen Excelissä, mutta koska OFFSET -toiminto on epävakaa, se voi johtaa hitaaseen Excel -työkirjaan. INDEX puolestaan ​​on osittain haihtuva, mikä tekee siitä paremman valinnan luoda dynaamisia nimettyjä alueita Excelissä.

Saatat pitää myös seuraavista Excel -resursseista:

• Ilmaiset Excel -mallit.
• Ilmainen online-Excel-koulutus (7-osainen online-videokurssi).
• Hyödyllisiä Excel -makrokoodiesimerkkejä.
• 10 Excelin VLOOKUP -esimerkkejä.
• Avattavan luettelon luominen Excelissä.
• Nimetyn alueen luominen Google Sheetsissä.
• Kuinka viitata toiseen taulukkoon tai työkirjaan Excelissä