Hvordan lage navngitte områder i Excel (en trinnvis veiledning)

Hva står i navnet?

Hvis du jobber med Excel -regneark, kan det bety mye tidsbesparelse og effektivitet.

I denne opplæringen lærer du hvordan du oppretter navngitte områder i Excel og hvordan du bruker den til å spare tid.

Navngitte områder i Excel - En introduksjon

Hvis noen må ringe meg eller henvise til meg, vil de bruke navnet mitt (i stedet for å si at en mann bor på det og det stedet med så og så høyde og vekt).

Ikke sant?

På samme måte kan du i Excel gi et navn til en celle eller en rekke celler.

Nå, i stedet for å bruke cellereferansen (for eksempel A1 eller A1: A10), kan du ganske enkelt bruke navnet du tildelte den.

Anta for eksempel at du har et datasett som vist nedenfor:

I dette datasettet, hvis du må referere til området som har datoen, må du bruke A2: A11 i formler. På samme måte, for salgsrepresentant og salg, må du bruke B2: B11 og C2: C11.

Selv om det er greit når du bare har et par datapunkter, men hvis du har enorme komplekse datasett, kan det være tidkrevende å bruke cellereferanser for å referere til data.

Excel Named Ranges gjør det enkelt å referere til datasett i Excel.

Du kan opprette et navngitt område i Excel for hver datakategori, og deretter bruke det navnet i stedet for cellereferansene. For eksempel kan datoer bli kalt "Dato", salgsrepresentantdata kan hete "SalesRep" og salgsdata kan hete "Salg".

Du kan også opprette et navn for en enkelt celle. For eksempel, hvis du har salgsprovisjonsprosent i en celle, kan du nevne cellen som "provisjon".

Fordeler med å lage navngitte områder i Excel

Her er fordelene ved å bruke navngitte områder i Excel.

Bruk navn i stedet for cellereferanser

Når du oppretter navngitte områder i Excel, kan du bruke disse navnene i stedet for cellereferansene.

For eksempel kan du bruke = SUMME (SALG) i stedet for = SUMME (C2: C11) for datasettet ovenfor.

Ta en titt på formlene som er oppført nedenfor. I stedet for å bruke cellereferanser, har jeg brukt Named Ranges.

• Antall salg med en verdi på mer enn 500: = COUNTIF (salg, ”> 500 ″)
• Summen av alt salg gjort av Tom: = SUMIF (SalesRep, ”Tom”, salg)
• Kommisjon opptjent av Joe (salg med Joe multiplisert med provisjonsprosent):
  = SUMIF (SalesRep, ”Joe”, Sales)*Kommisjon

Du er enig i at disse formlene er enkle å lage og enkle å forstå (spesielt når du deler den med noen andre eller besøker den selv.

Du trenger ikke gå tilbake til datasettet for å velge celler

En annen betydelig fordel ved å bruke navngitte områder i Excel er at du ikke trenger å gå tilbake og velge celleområder.

Du kan bare skrive inn et par alfabeter med det navngitte området, og Excel vil vise de matchende navngitte områdene (som vist nedenfor):

Navngitte områder gjør formler dynamiske

Ved å bruke navngitte områder i Excel kan du gjøre Excel -formler dynamiske.

For eksempel når det gjelder salgskommisjon, i stedet for å bruke verdien 2,5%, kan du bruke det navngitte området.

Hvis din bedrift senere bestemmer seg for å øke provisjonen til 3%, kan du bare oppdatere det navngitte området, og all beregning oppdateres automatisk for å gjenspeile den nye provisjonen.

Hvordan lage navngitte områder i Excel

Her er tre måter å opprette navngitte områder i Excel:

Metode #1 - Bruke Definer navn

Her er trinnene for å lage navngitte områder i Excel ved hjelp av Definer navn:

• Velg området du vil opprette et navngitt område for i Excel.
• Gå til Formler -> Definer navn.
• I dialogboksen Nytt navn skriver du inn navnet du vil tilordne det valgte dataområdet. Du kan angi omfanget som hele arbeidsboken eller et bestemt regneark. Hvis du velger et bestemt ark, vil navnet ikke være tilgjengelig på andre ark.
• Klikk OK.

Dette vil opprette et Named Range SALESREP.

Metode 2: Bruke navneboksen

• Velg området du vil opprette et navn for (ikke velg overskrifter).
• Gå til navnefeltet til venstre på formellinjen, og skriv inn navnet på det du vil lage det navngitte området med.
• Vær oppmerksom på at navnet som er opprettet her, vil være tilgjengelig for hele arbeidsboken. Hvis du vil begrense det til et regneark, bruker du metode 1.

Metode 3: Bruk alternativet Opprett fra utvalg

Dette er den anbefalte måten når du har data i tabellform, og du vil opprette et navngitt område for hver kolonne/rad.

For eksempel, i datasettet nedenfor, hvis du raskt vil opprette tre navngitte områder (Dato, Sales_Rep og Sales), kan du bruke metoden vist nedenfor.

Her er trinnene for å raskt opprette navngitte områder fra et datasett:

• Velg hele datasettet (inkludert overskriftene).
• Gå til formler -> Opprett fra utvalg (Tastatursnarvei - Control + Shift + F3). Det åpner dialogboksen "Opprett navn fra utvalg".
• I dialogboksen Opprett navn fra utvalg, sjekk alternativene der du har overskriftene. I dette tilfellet velger vi bare den øverste raden ettersom overskriften er i den øverste raden. Hvis du har overskrifter i både øverste rad og venstre kolonne, kan du velge begge. På samme måte, hvis dataene dine er ordnet når overskriftene bare er i venstre kolonne, sjekker du bare alternativet Venstre kolonne.

Dette vil opprette tre navngitte områder - dato, Sales_Rep og Sales.

Vær oppmerksom på at den automatisk henter navn fra overskriftene. Hvis det er mellomrom mellom ord, setter det inn en understreking (ettersom du ikke kan ha mellomrom i navngitte områder).

Navnekonvensjon for navngitte områder i Excel

Det er visse navneregler du trenger å vite når du oppretter navngitte områder i Excel:

• Det første tegnet i et navngitt område bør være en bokstav og understreketegn (_), eller en omvendt skråstrek (\). Hvis det er noe annet, viser det en feil. De resterende tegnene kan være bokstaver, tall, spesialtegn, punktum eller understreking.
• Du kan ikke bruke navn som også representerer cellereferanser i Excel. For eksempel kan du ikke bruke AB1, da det også er en cellereferanse.
• Du kan ikke bruke mellomrom mens du oppretter navngitte områder. For eksempel kan du ikke ha Salgsrepresentant som et navngitt område. Hvis du vil kombinere to ord og lage et navngitt område, bruker du en understreking, punktum eller store bokstaver for å lage det. Du kan for eksempel ha Sales_Rep, SalesRep eller SalesRep.
  • Mens du oppretter navngitte områder, behandler Excel store og små bokstaver på samme måte. For eksempel, hvis du oppretter et navngitt område SALG, vil du ikke kunne opprette et annet navngitt område, for eksempel "salg" eller "salg".
• Et navngitt område kan inneholde opptil 255 tegn.

For mange navngitte områder i Excel? Ikke bekymre deg

Noen ganger i store datasett og komplekse modeller kan du ende opp med å opprette mange navngitte områder i Excel.

Hva om du ikke husker navnet på det navngitte området du opprettet?

Ikke bekymre deg - her er noen nyttige tips.

Få navnene på alle navngitte områder

Her er trinnene for å få en liste over alle de navngitte områdene du opprettet:

• Gå til fanen Formler.
• Klikk på Bruk i formel i gruppen Defined Named.
• Klikk på "Lim inn navn".

Dette vil gi deg en liste over alle navngitte områder i arbeidsboken. For å bruke et navngitt område (i formler eller en celle), dobbeltklikker du på det.

Viser matchende navngitte områder

• Hvis du har en ide om navnet, skriver du inn noen innledende tegn, og Excel viser en nedtrekksmeny med de matchende navnene.

Slik redigerer du navngitte områder i Excel

Hvis du allerede har opprettet et navngitt område, kan du redigere det ved å følge disse trinnene:

• Gå til kategorien Formler og klikk på Navnebehandler.
• Navnebehandlingsdialogboksen viser alle navngitte områder i arbeidsboken. Dobbeltklikk på det navngitte området du vil redigere.
• Gjør endringene i dialogboksen Rediger navn.
• Klikk OK.
• Lukk dialogboksen Navnebehandler.

Nyttige snarveier med navngitt område (kraften til F3)

Her er noen nyttige hurtigtaster som vil komme godt med når du jobber med navngitte områder i Excel:

• For å få en liste over alle navngitte områder og lime den inn i Formel: F3
• Slik oppretter du et nytt navn ved hjelp av Name Manager Dialogboks: Kontroll + F3
• Slik oppretter du navngitte områder fra utvalg: Control + Shift + F3

Opprette dynamiske navngitte områder i Excel

Så langt i denne opplæringen har vi laget statiske navngitte områder.

Dette betyr at disse navngitte områdene alltid vil referere til det samme datasettet.

For eksempel, hvis A1: A10 har blitt kalt "salg", vil det alltid referere til A1: A10.

Hvis du legger til flere salgsdata, må du manuelt gå og oppdatere referansen i det navngitte området.

I en verden av stadig voksende datasett kan dette ende opp med å ta mye tid. Hver gang du får nye data, må du kanskje oppdatere de navngitte områdene i Excel.

For å løse dette problemet kan vi opprette dynamiske navngitte områder i Excel som automatisk tar høyde for ytterligere data og inkluderer det i det eksisterende navngitte området.

For eksempel, for eksempel, hvis jeg legger til to ytterligere salgsdatapunkter, vil et dynamisk navngitt område automatisk referere til A1: A12.

Denne typen dynamisk navngitt område kan opprettes ved hjelp av Excel INDEX -funksjonen. I stedet for å spesifisere cellereferansene mens du oppretter det navngitte området, spesifiserer vi formelen. Formelen oppdateres automatisk når dataene legges til eller slettes.

La oss se hvordan du oppretter dynamiske navngitte områder i Excel.

Anta at vi har salgsdataene i celle A2: A11.

Her er trinnene for å lage dynamiske navngitte områder i Excel:

1. 1. Gå til Formel -fanen og klikk på Definer navn.
   2. Skriv følgende i dialogboksen Nytt navn:
      • Navn: Salg
      • Omfang: Arbeidsbok
      • Henviser til: = $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))
   3. Klikk OK.

Ferdig!

Du har nå et dynamisk navngitt område med navnet 'Salg'. Dette oppdateres automatisk hver gang du legger til data eller fjerner data fra den.

Hvordan fungerer dynamiske navngitte områder?

For å forklare hvordan dette fungerer, må du vite litt mer om Excel INDEX -funksjonen.

De fleste bruker INDEX for å returnere en verdi fra en liste basert på rad- og kolonnummeret.

Men INDEX -funksjonen har også en annen side.

Det kan brukes til returnere en cellereferanse når den brukes som en del av en cellereferanse.

Her er for eksempel formelen vi har brukt for å lage et dynamisk navngitt område:

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

INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””) -> Denne delen av formelen forventes å returnere en verdi (som ville være den tiende verdien fra listen, med tanke på at det er ti varer).

Men når den brukes foran en referanse (=$ A $ 2:INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))) returnerer referansen til cellen i stedet for verdien.

Derfor returnerer den her = $ A $ 2: $ A $ 11

Hvis vi legger til ytterligere to verdier i salgskolonnen, vil den returnere = $ A $ 2: $ A $ 13

Når du legger til nye data i listen, returnerer Excel COUNTIF-funksjonen antallet ikke-tomme celler i dataene. Dette nummeret brukes av INDEX -funksjonen for å hente cellereferansen til det siste elementet i listen.

Merk:

• Dette fungerer bare hvis det ikke er tomme celler i dataene.
• I eksemplet ovenfor har jeg tilordnet et stort antall celler (A2: A100) for Named Range -formelen. Du kan justere dette basert på datasettet ditt.

Du kan også bruke OFFSET -funksjonen til å opprette et dynamisk navngitt område i Excel, men siden OFFSET -funksjonen er flyktig, kan det føre til en treg Excel -arbeidsbok. INDEX, derimot, er halvflyktig, noe som gjør det til et bedre valg å lage Dynamic Named Ranges i Excel.

Du kan også like følgende Excel -ressurser:

• Gratis Excel -maler.
• Gratis online Excel-opplæring (7-delt online videokurs).
• Nyttige eksempler på makrokode i Excel.
• 10 Avanserte Excel VLOOKUP -eksempler.
• Opprette en nedtrekksliste i Excel.
• Opprette et navngitt område i Google Regneark.
• Hvordan referere til et annet ark eller en arbeidsbok i Excel