Sådan opretter du navngivne områder i Excel (en trinvis vejledning)

Hvad står der i navnet?

Hvis du arbejder med Excel -regneark, kan det betyde meget tidsbesparelse og effektivitet.

I denne vejledning lærer du, hvordan du opretter navngivne områder i Excel, og hvordan du bruger det til at spare tid.

Navngivne områder i Excel - en introduktion

Hvis nogen er nødt til at ringe til mig eller henvise til mig, vil de bruge mit navn (i stedet for at sige, at en mand opholder sig på sådan et sted med så og så højde og vægt).

Ret?

På samme måde kan du i Excel give et navn til en celle eller en række celler.

I stedet for at bruge cellereferencen (f.eks. A1 eller A1: A10) kan du nu bare bruge det navn, du har tildelt det.

Antag f.eks., At du har et datasæt som vist herunder:

I dette datasæt, hvis du skal henvise til det område, der har datoen, skal du bruge A2: A11 i formler. Tilsvarende skal du for salgsrepræsentant og salg bruge B2: B11 og C2: C11.

Selvom det er i orden, når du kun har et par datapunkter, men hvis du har enorme komplekse datasæt, kan det være tidskrævende at bruge cellereferencer til at henvise til data.

Excel Named Ranges gør det let at henvise til datasæt i Excel.

Du kan oprette et navngivet område i Excel for hver datakategori og derefter bruge det navn i stedet for cellereferencer. For eksempel kan datoer navngives 'Dato', salgsrepræsentationsdata kan navngives 'SalesRep', og salgsdata kan navngives 'Salg'.

Du kan også oprette et navn til en enkelt celle. Hvis du f.eks. Har salgsprovisionsprocenten i en celle, kan du navngive cellen som 'provision'.

Fordele ved at oprette navngivne områder i Excel

Her er fordelene ved at bruge navngivne områder i Excel.

Brug navne i stedet for cellereferencer

Når du opretter navngivne områder i Excel, kan du bruge disse navne i stedet for cellereferencer.

For eksempel kan du bruge = SUM (SALES) i stedet for = SUM (C2: C11) til ovenstående datasæt.

Tag et kig på formlerne anført nedenfor. I stedet for at bruge cellereferencer har jeg brugt Named Ranges.

• Antal salg med en værdi på mere end 500: = COUNTIF (salg, ”> 500 ″)
• Summen af ​​alt salg foretaget af Tom: = SUMIF (SalesRep, ”Tom”, salg)
• Provision tjent af Joe (salg med Joe ganget med kommissionsprocent):
  = SUMIF (SalesRep, ”Joe”, salg)*Provision

Du er enig i, at disse formler er lette at oprette og lette at forstå (især når du deler den med en anden eller besøger den selv igen.

Ingen grund til at gå tilbage til datasættet for at vælge celler

En anden væsentlig fordel ved at bruge navngivne områder i Excel er, at du ikke behøver at gå tilbage og vælge celleområderne.

Du kan bare skrive et par alfabeter af det navngivne område, og Excel viser de matchende navngivne områder (som vist nedenfor):

Navngivne intervaller gør formler dynamiske

Ved at bruge navngivne områder i Excel kan du gøre Excel -formler dynamiske.

For eksempel i tilfælde af salgskommission kan du i stedet for at bruge værdien 2,5%bruge det navngivne område.

Hvis din virksomhed senere beslutter sig for at øge provisionen til 3%, kan du blot opdatere det navngivne område, og al beregning opdateres automatisk for at afspejle den nye provision.

Sådan oprettes navngivne områder i Excel

Her er tre måder at oprette navngivne områder i Excel:

Metode #1 - Brug af Definer navn

Her er trinene til at oprette navngivne områder i Excel ved hjælp af Definer navn:

• Vælg det område, du vil oprette et navngivet område for i Excel.
• Gå til formler -> Definer navn.
• I dialogboksen Nyt navn skal du indtaste det navn, du vil tildele det valgte dataområde. Du kan angive omfanget som hele projektmappen eller et specifikt regneark. Hvis du vælger et bestemt ark, ville navnet ikke være tilgængeligt på andre ark.
• Klik på OK.

Dette vil oprette et navngivet område SALESREP.

Metode #2: Brug af navneboksen

• Vælg det område, du vil oprette et navn for (vælg ikke overskrifter).
• Gå til navnefeltet til venstre for formellinjen, og skriv navnet på det, du vil oprette det navngivne område med.
• Bemærk, at det navn, der er oprettet her, vil være tilgængeligt for hele projektmappen. Hvis du ønsker at begrænse det til et regneark, skal du bruge metode 1.

Metode #3: Brug af Opret fra valgmulighed

Dette er den anbefalede måde, når du har data i tabelform, og du vil oprette navngivet område for hver kolonne/række.

For eksempel i nedenstående datasæt, hvis du hurtigt vil oprette tre navngivne områder (dato, salg_rep og salg), kan du bruge metoden vist nedenfor.

Her er trinene til hurtigt at oprette navngivne intervaller fra et datasæt:

• Vælg hele datasættet (inklusive overskrifterne).
• Gå til formler -> Opret fra markering (Tastaturgenvej - Control + Shift + F3). Det åbner dialogboksen 'Opret navne fra markering'.
• I dialogboksen Opret navne fra markering skal du markere de muligheder, hvor du har overskrifterne. I dette tilfælde vælger vi kun den øverste række, da overskriften er i den øverste række. Hvis du har overskrifter i både øverste række og venstre kolonne, kan du vælge begge. På samme måde, hvis dine data er arrangeret, når overskrifterne kun er i venstre kolonne, kontrollerer du kun indstillingen Venstre kolonne.

Dette vil oprette tre navngivne intervaller - dato, Sales_Rep og Sales.

Bemærk, at det automatisk henter navne fra overskrifterne. Hvis der er mellemrum mellem ord, indsætter det en understregning (da du ikke kan have mellemrum i navngivne områder).

Navngivningskonvention for navngivne områder i Excel

Der er visse navngivningsregler, du skal vide, når du opretter navngivne områder i Excel:

• Det første tegn i et navngivet område skal være et bogstav og understregningstegn (_) eller en omvendt skråstreg (\). Hvis det er noget andet, viser det en fejl. De resterende tegn kan være bogstaver, tal, specialtegn, punktum eller understregning.
• Du kan ikke bruge navne, der også repræsenterer cellereferencer i Excel. For eksempel kan du ikke bruge AB1, da det også er en cellereference.
• Du kan ikke bruge mellemrum, mens du opretter navngivne områder. Du kan f.eks. Ikke have Salgsrepræsentant som et navngivet område. Hvis du vil kombinere to ord og oprette et navngivet område, skal du bruge en understregning, punktum eller store bogstaver til at oprette det. Du kan f.eks. Have Sales_Rep, SalesRep eller SalesRep.
  • Mens du opretter navngivne områder, behandler Excel store og små bogstaver på samme måde. For eksempel, hvis du opretter et navngivet område SALG, kan du ikke oprette et andet navngivet område, f.eks. "Salg" eller "salg".
• Et navngivet område kan være op til 255 tegn langt.

For mange navngivne områder i Excel? Bare rolig

Nogle gange i store datasæt og komplekse modeller kan du ende med at oprette en masse navngivne områder i Excel.

Hvad hvis du ikke husker navnet på det navngivne område, du har oprettet?

Bare rolig - her er nogle nyttige tips.

Få navnene på alle de navngivne intervaller

Her er trinene til at få en liste over alle de navngivne intervaller, du har oprettet:

• Gå til fanen Formler.
• Klik på Brug i formel i gruppen Defineret navngivet.
• Klik på 'Indsæt navne'.

Dette vil give dig en liste over alle de navngivne intervaller i denne projektmappe. Hvis du vil bruge et navngivet område (i formler eller en celle), skal du dobbeltklikke på det.

Viser de matchende navngivne intervaller

• Hvis du har en idé om navnet, skal du skrive et par indledende tegn, og Excel viser en rullemenu med de matchende navne.

Sådan redigeres navngivne områder i Excel

Hvis du allerede har oprettet et navngivet område, kan du redigere det ved hjælp af følgende trin:

• Gå til fanen Formler, og klik på Name Manager.
• Dialogboksen Navneadministrator viser alle de navngivne områder i den pågældende projektmappe. Dobbeltklik på det navngivne område, du vil redigere.
• Foretag ændringerne i dialogboksen Rediger navn.
• Klik på OK.
• Luk dialogboksen Navneadministrator.

Nyttige navngivne områdegenveje (kraften til F3)

Her er nogle nyttige tastaturgenveje, der vil være nyttige, når du arbejder med navngivne områder i Excel:

• For at få en liste over alle de navngivne intervaller og indsætte den i Formel: F3
• Sådan opretter du et nyt navn ved hjælp af dialogboksen Navneadministrator: Control + F3
• Sådan opretter du navngivne intervaller fra markering: Control + Shift + F3

Oprettelse af dynamiske navngivne intervaller i Excel

Hidtil i denne vejledning har vi oprettet statiske navngivne intervaller.

Det betyder, at disse navngivne områder altid vil referere til det samme datasæt.

For eksempel, hvis A1: A10 er blevet navngivet som 'salg', vil det altid referere til A1: A10.

Hvis du tilføjer flere salgsdata, skal du manuelt gå og opdatere referencen i det navngivne område.

I en verden af ​​stadigt voksende datasæt kan dette ende med at tage meget af din tid. Hver gang du får nye data, skal du muligvis opdatere de navngivne områder i Excel.

For at løse dette problem kan vi oprette dynamiske navngivne områder i Excel, der automatisk tager højde for yderligere data og inkluderer dem i det eksisterende navngivne område.

For eksempel, hvis jeg f.eks. Tilføjer to yderligere salgsdatapunkter, refererer et dynamisk navngivet område automatisk til A1: A12.

Denne form for dynamisk navngivet område kan oprettes ved hjælp af Excel INDEX -funktion. I stedet for at specificere cellereferencer, mens du opretter det navngivne område, angiver vi formlen. Formlen opdateres automatisk, når data tilføjes eller slettes.

Lad os se, hvordan du opretter dynamiske navngivne intervaller i Excel.

Antag, at vi har salgsdataene i celle A2: A11.

Her er trinene til at oprette dynamiske navngivne intervaller i Excel:

1. 1. Gå til fanen Formel, og klik på Definer navn.
   2. Skriv følgende i dialogboksen Nyt navn:
      • Navn: Salg
      • Anvendelsesområde: Arbejdsbog
      • Henviser til: = $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))
   3. Klik på OK.

Færdig!

Du har nu et dynamisk navngivet område med navnet 'Salg'. Dette opdateres automatisk, når du tilføjer data til det eller fjerner data fra det.

Hvordan fungerer dynamiske navngivne intervaller?

For at forklare, hvordan dette fungerer, skal du vide lidt mere om Excel INDEX -funktionen.

De fleste bruger INDEX til at returnere en værdi fra en liste baseret på række og kolonnummer.

Men INDEX -funktionen har også en anden side.

Det kan bruges til returnere en cellereference når den bruges som en del af en cellereference.

Her er for eksempel formlen, som vi har brugt til at oprette et dynamisk navngivet 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 del af formlen forventes at returnere en værdi (som ville være den tiende værdi fra listen, i betragtning af at der er ti varer).

Men når den bruges foran en reference (=$ A $ 2:INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))) returnerer referencen til cellen i stedet for værdien.

Derfor vender den her tilbage = $ A $ 2: $ A $ 11

Hvis vi tilføjer to yderligere værdier til salgskolonnen, ville den derefter returnere = $ A $ 2: $ A $ 13

Når du tilføjer nye data til listen, returnerer Excel COUNTIF-funktionen antallet af ikke-tomme celler i dataene. Dette nummer bruges af INDEX -funktionen til at hente cellereferencen for det sidste element på listen.

Bemærk:

• Dette ville kun fungere, hvis der ikke er tomme celler i dataene.
• I ovenstående eksempel har jeg tildelt et stort antal celler (A2: A100) til formlen Named Range. Du kan justere dette baseret på dit datasæt.

Du kan også bruge OFFSET -funktionen til at oprette et dynamisk navngivet interval i Excel, men da OFFSET -funktionen er flygtig, kan det føre til en langsom Excel -projektmappe. INDEX er derimod semi-flygtig, hvilket gør det til et bedre valg at oprette Dynamic Named Ranges i Excel.

Du kan også lide følgende Excel -ressourcer:

• Gratis Excel -skabeloner.
• Gratis online Excel-uddannelse (7-delt online videokursus).
• Nyttige eksempler på makrokode i Excel.
• 10 Avancerede Excel VLOOKUP -eksempler.
• Oprettelse af en rulleliste i Excel.
• Oprettelse af et navngivet område i Google Sheets.
• Sådan henvises til et andet ark eller en projektmappe i Excel