Nimetatud vahemike loomine Excelis (samm-sammuline juhend)

Mis on nimes?

Kui töötate Exceli arvutustabelitega, võib see tähendada palju aja kokkuhoidu ja tõhusust.

Selles õpetuses saate teada, kuidas Excelis nimevahemikke luua ja kuidas seda aja säästmiseks kasutada.

Nimetatud vahemikud Excelis - sissejuhatus

Kui keegi peab mulle helistama või mulle viitama, kasutab ta minu nime (selle asemel, et öelda, et isane viibib sellises kohas nii ja nii pikkuse ja kaaluga).

Õigus?

Samamoodi saate Excelis anda lahtrile või lahtrivahemikule nime.

Nüüd saate lahtri viite (nt A1 või A1: A10) asemel kasutada sellele määratud nime.

Oletame näiteks, et teil on allpool näidatud andmekogum.

Kui peate selles andmekogumis viitama vahemikule, millel on kuupäev, peate valemites kasutama A2: A11. Samamoodi peate müügiesindaja ja müügi puhul kasutama B2: B11 ja C2: C11.

Kuigi see on korras, kui teil on ainult paar andmepunkti, kuid kui teil on tohutult keerulisi andmekogumeid, võib lahtriviidete kasutamine andmetele viitamiseks olla aeganõudev.

Exceli nimega vahemikud hõlbustavad Exceli andmekogumitele viitamist.

Saate Excelis luua iga andmekategooria jaoks nimega vahemiku ja seejärel kasutada seda nime lahtriviidete asemel. Näiteks kuupäevadele saab anda nime „Kuupäev”, müügiesindaja andmetele „SalesRep” ja müügiandmetele „Müük”.

Samuti saate luua nime ühele lahtrile. Näiteks kui teil on lahtris müügitasu protsent, võite sellele lahtrile anda nime „Komisjon”.

Excelis nimevahemike loomise eelised

Siin on Excelis nimetatud vahemike kasutamise eelised.

Kasutage lahtriviidete asemel nimesid

Kui loote Excelis nimevahemikke, saate lahtriviidete asemel kasutada neid nimesid.

Näiteks võite ülaltoodud andmekogumi jaoks kasutada = SUM (MÜÜK) asemel = SUM (C2: C11).

Vaadake allpool loetletud valemeid. Lahtriviidete kasutamise asemel olen kasutanud nimega vahemikke.

• Müükide arv väärtusega üle 500: = COUNTIF (müük, ”> 500”)
• Tomi kogu müügi summa: = SUMIF (SalesRep, “Tom”, müük)
• Joe teenitud komisjonitasu (Joe müük korrutatud vahendustasuga):
  = SUMIF (SalesRep, ”Joe”, müük)*Komisjon

Nõustute, et neid valemeid on lihtne luua ja neid on lihtne mõista (eriti kui jagate seda kellegi teisega või vaatate ise uuesti üle.

Lahtrite valimiseks pole vaja andmekogumi juurde naasta

Veel üks oluline eelis nimega vahemike kasutamisel Excelis on see, et te ei pea tagasi minema ja lahtrivahemikke valima.

Võite lihtsalt sisestada paar selle nimega vahemiku tähestikku ja Excel näitab sobivaid nimevahemikke (nagu allpool näidatud):

Nimega vahemikud muudavad valemid dünaamilisteks

Kasutades Excelis nimega vahemikke, saate muuta Exceli valemid dünaamilisteks.

Näiteks müügitasu puhul saate väärtuse 2,5%asemel kasutada nimega vahemikku.

Nüüd, kui teie ettevõte otsustab hiljem komisjonitasu 3%-ni tõsta, saate lihtsalt nimevahemikku värskendada ja kogu arvutus värskendatakse automaatselt, et see kajastaks uut vahendustasu.

Kuidas luua Excelis nimevahemikke

Siin on kolm võimalust nimega vahemike loomiseks Excelis:

Meetod nr 1 - nime määratlemise kasutamine

Siin on sammud nimega vahemike loomiseks Excelis nime määratlemise abil:

• Valige vahemik, mille jaoks soovite Excelis nimega vahemiku luua.
• Avage Valemid -> Määrake nimi.
• Tippige dialoogiboksis Uus nimi nimi, mille soovite valitud andmevahemikule määrata. Saate määrata ulatuse terveks töövihikuks või konkreetseks tööleheks. Kui valite kindla lehe, poleks see nimi teistel lehtedel saadaval.
• Klõpsake nuppu OK.

See loob nimega vahemiku SALESREP.

Meetod nr 2: nimekasti kasutamine

• Valige vahemik, millele soovite nime luua (ärge valige päiseid).
• Minge valemiriba vasakul asuvasse nimekasti ja tippige selle nime nimi, millega soovite nimega vahemikku luua.
• Pange tähele, et siin loodud nimi on saadaval kogu töövihikule. Kui soovite selle piirata töölehega, kasutage 1. meetodit.

Meetod nr 3: valiku Loo abil kasutamine

See on soovitatav viis, kui teil on andmed tabeli kujul ja soovite iga veeru/rea jaoks nimega vahemiku luua.

Näiteks kui soovite allolevas andmekogumis kiiresti luua kolm nimega vahemikku (kuupäev, müügi_report ja müük), saate kasutada allpool näidatud meetodit.

Siin on sammud nimekauguste kiireks loomiseks andmestikust.

• Valige kogu andmekogum (sealhulgas päised).
• Avage Valemid -> Loo valikust (Klaviatuuri otsetee - Control + Shift + F3). See avab dialoogiboksi „Loo nimed valikust”.
• Märkige dialoogiboksis Loo nimed valikust suvandid, kus teil on päised. Sel juhul valime ülemise rea ainult siis, kui päis asub ülemises reas. Kui teil on päised nii ülemises reas kui ka vasakus veerus, saate valida mõlemad. Samamoodi, kui teie andmed on paigutatud siis, kui päised on ainult vasakpoolses veerus, siis kontrollite ainult suvandit Vasak veerg.

See loob kolm nimevahemikku - kuupäev, müügi_report ja müük.

Pange tähele, et see korjab päistest automaatselt nimed. Kui sõnade vahel on tühikuid, lisab see allakriipsutuse (kuna nimevahemikus ei saa tühikuid olla).

Nimetuskonventsioon nimega vahemikele Excelis

Excelis nimevahemike loomisel peate teadma teatavaid nimetamisreegleid:

• Nimega vahemiku esimene märk peaks olema täht ja allakriipsutus (_) või tagasilöök (\). Kui see on midagi muud, näitab see viga. Ülejäänud märgid võivad olla tähed, numbrid, erimärgid, punkt või alajoon.
• Te ei saa Excelis kasutada nimesid, mis esindavad ka lahtri viiteid. Näiteks ei saa te kasutada AB1, kuna see on ka lahtri viide.
• Nimetatud vahemike loomisel ei saa tühikuid kasutada. Näiteks ei saa te müügiesindajat nimetada vahemikuks. Kui soovite ühendada kaks sõna ja luua nimevahemiku, kasutage selle loomiseks alajoon-, punkti- või suurtähti. Näiteks võib teil olla Sales_Rep, SalesRep või SalesRep.
  • Nimetatud vahemike loomisel käsitleb Excel suuri ja väiketähti samamoodi. Näiteks kui loote nimega vahemiku SALES, ei saa te luua muud nimega vahemikku, näiteks „müük” või „Müük”.
• Nimega vahemik võib olla kuni 255 tähemärki pikk.

Excelis on liiga palju nimega vahemikke? Ärge muretsege

Mõnikord suurte andmekogumite ja keerukate mudelite korral võite Excelis luua palju nimevahemikke.

Mis siis, kui te ei mäleta oma loodud nimestiku nime?

Ärge muretsege - siin on mõned kasulikud näpunäited.

Kõigi nimepiirkondade nimede hankimine

Siin on juhised kõigi loodud vahemike loendi saamiseks:

• Minge vahekaardile Valemid.
• Klõpsake rühmas Määratletud nimed nuppu Kasuta valemis.
• Klõpsake nuppu "Kleebi nimed".

See annab teile nimekirja kõigist selle töövihiku nimega vahemikest. Nimega vahemiku kasutamiseks (valemites või lahtris) topeltklõpsake seda.

Sobivate nimevahemike kuvamine

• Kui teil on nime kohta aimu, tippige mõned esialgsed märgid ja Excel kuvab sobivate nimede rippmenüü.

Nimetatud vahemike muutmine Excelis

Kui olete juba nimega vahemiku loonud, saate seda muuta järgmiste toimingute abil.

• Minge vahekaardile Valemid ja klõpsake nimehaldurit.
• Dialoogiboksis Nimehaldur on loetletud kõik selle töövihiku nimetud vahemikud. Topeltklõpsake nimega vahemikku, mida soovite muuta.
• Tehke muudatused dialoogiboksis Nime muutmine.
• Klõpsake nuppu OK.
• Sulgege dialoogiboks Nimehaldur.

Kasulikud nimega vahemiku otseteed (F3 võimsus)

Siin on mõned kasulikud kiirklahvid, mis on Excelis nimega vahemikega töötamisel abiks:

• Kõigi nimega vahemike loendi hankimiseks ja selle valemisse kleepimiseks toimige järgmiselt. F3
• Uue nime loomiseks nimehalduri dialoogiboksi abil tehke järgmist. Control + F3
• Nimega vahemike loomiseks valikust tehke järgmist. Control + tõstuklahv + F3

Dünaamiliste nimega vahemike loomine Excelis

Siiani oleme selles õpetuses loonud staatilised nimega vahemikud.

See tähendab, et need nimega vahemikud viitavad alati samale andmekogumile.

Näiteks kui A1: A10 on nimetatud kui „Müük”, viitab see alati A1: A10 -le.

Kui lisate rohkem müügiandmeid, peate käsitsi minema ja värskendama viiteid nimetatud vahemikus.

Järjest laienevate andmekogumite maailmas võib see lõpuks võtta palju aega. Iga kord, kui saate uusi andmeid, peate võib -olla Excelis nimevahemikke värskendama.

Selle probleemi lahendamiseks saame Excelis luua dünaamilised nimevahemikud, mis võtavad automaatselt arvesse täiendavaid andmeid ja lisavad need olemasolevasse nimede vahemikku.

Näiteks kui lisan kaks täiendavat müügiandmepunkti, viitab dünaamiline nimega vahemik automaatselt A1: A12.

Sellist dünaamilist nimevahemikku saab luua funktsiooni Excel INDEX abil. Nimevahemiku loomisel lahtriviidete määramise asemel määrame valemi. Valemit värskendatakse andmete lisamisel või kustutamisel automaatselt.

Vaatame, kuidas Excelis dünaamilisi nimevahemikke luua.

Oletame, et meil on lahtris A2: A11 müügiandmed.

Dünaamiliste nimega vahemike loomiseks Excelis toimige järgmiselt.

1. 1. Minge vahekaardile Valem ja klõpsake nuppu Määra nimi.
   2. Tippige dialoogiboksis Uus nimi järgmine:
      • Nimi: Müük
      • Reguleerimisala: töövihik
      • Viitab: = $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))
   3. Klõpsake nuppu OK.

Valmis!

Nüüd on teil dünaamiline nimega vahemik nimega „Müük”. Seda värskendatakse automaatselt iga kord, kui lisate sellele andmeid või eemaldate sealt andmeid.

Kuidas dünaamilised nimega vahemikud töötavad?

Selle toimimise selgitamiseks peate teadma natuke rohkem funktsiooni Excel INDEX.

Enamik inimesi kasutab INDEX -i rea ja veeru numbri alusel loendist väärtuse tagastamiseks.

Kuid funktsioonil INDEX on ka teine ​​külg.

Seda saab kasutada tagastab lahtri viite kui seda kasutatakse lahtri viite osana.

Näiteks siin on valem, mida oleme kasutanud dünaamilise nimega vahemiku loomiseks:

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

INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””) -> Eeldatakse, et see valemiosa tagastab väärtuse (mis oleks loendis 10. väärtus, arvestades, et neid on kümme).

Siiski, kui seda kasutatakse viite ees (=2 A $:INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))) tagastab väärtuse asemel viite lahtrile.

Seega tagastab see siin = $ A $ 2: $ A $ 11

Kui lisame müügiveergu kaks lisaväärtust, tagastab see = $ A $ 2: $ A $ 13

Kui lisate loendisse uusi andmeid, tagastab funktsioon Excel COUNTIF andmete tühjade lahtrite arvu. Funktsioon INDEX kasutab seda numbrit loendi viimase üksuse lahtri viite hankimiseks.

Märge:

• See toimiks ainult siis, kui andmetes pole tühje lahtreid.
• Ülaltoodud näites olen määranud valemile Named Range suure hulga lahtreid (A2: A100). Saate seda kohandada oma andmekogumi põhjal.

Funktsiooni OFFSET saate kasutada ka Excelis dünaamiliste nimevahemike loomiseks, kuid kuna funktsioon OFFSET on kõikuv, võib see viia aeglase Exceli töövihikuni. INDEX seevastu on poollenduv, mistõttu on see parem valik Excelis dünaamiliste nimedega vahemike loomiseks.

Samuti võivad teile meeldida järgmised Exceli ressursid:

• Tasuta Exceli mallid.
• Tasuta online Exceli koolitus (7-osaline veebivideokursus).
• Kasulikud Exceli makrokoodide näited.
• 10 Täiustatud Exceli VLOOKUP -i näited.
• Excelis ripploendi loomine.
• Nimetatud vahemiku loomine Google'i arvutustabelites.
• Kuidas viidata teisele lehele või töövihikule Excelis