Alt om Excel navngivne områder - Navn på Excel -område

Anonim

Mens du arbejder på Excel, skal du have hørt om navngivne områder i excel. Måske fra en ven, kollega eller en online tutorial. Selv har jeg nævnt det mange gange i mine artikler. I denne artikel lærer vi om de navngivne områder i Excel og vil undersøge alle aspekter af det.

Hvad hedder område i Excel?

Nå, navngivne intervaller er ikke andet end nogle excel -intervaller, der er mærket med et meningsfuldt navn. Hvis du f.eks. Har en celle, der siger B1, indeholder det daglige mål, kan du navngive cellen som specifikt "Target". Nu kan du bruge "Target" til at henvise til A1 i stedet for at skrive B1.
I en nøddeskal er navngivet område bare navngivning af intervaller.

Hvordan navngives et område i Excel?

Definer navn manuelt:

For at definere et navn til et område kan du bruge genvej CTRL+F3. Eller du kan følge disse trin.

    • Gå til fanen Formel
    • Find sektionen Definerede navne, og klik på Definer navne. Det åbner Name Manger.

    • Klik på Ny.
    • Indtast navnet.

  • Vælg omfang (projektmappe eller ark)
  • Skriv en kommentar, hvis du vil.
  • Skriv referencen i feltet Henviser til boksen eller vælg et område med musen.
  • Hit OK. Det er gjort.

Nu kan du henvise til det ved blot at skrive dets navn.
Der er nogle regler, du skal følge, når du opretter navne. De er

  1. Navne bør ikke starte fra cifre eller specialtegn bortset fra understregning (_) og omvendt skråstreg (\).
  2. Navne må ikke have mellemrum og specialtegn undtagen _ og \.
  3. Området bør ikke navngives som cellereferencer. For eksempel er A1, B1 eller AZ100 osv. Navne ugyldige.
  4. Du kan ikke navngive et område som "r" og "c", fordi de er forbeholdt række- og kolonnereferencer.
  5. To navngivne områder kan ikke have samme navn i en projektmappe.
  6. Det samme område kan have flere navne.

Definer navn automatisk

Det meste af tiden vil du arbejde med en struktureret datatabel. De vil have kolonne og rækker med kolonneoverskrifter og rækkeoverskrifter. Og for det meste er disse navne meningsfulde for dataene, og du vil gerne navngive dit område som disse kolonneoverskrifter. Excel giver et værktøj til at navngive intervaller ved hjælp af titler automatisk. Følg disse trin.

    • De områder, du vil navngive som deres overskrifter
    • Trykke CTRL+SKIFT+F3, eller Find sektionen Definerede navne i fanen Formel, og klik på Opret fra markering.

    • Nedenstående valgfelt vises. Jeg valgte kun Top Row, da jeg vil navngive disse områder som overskriften og ikke vil navngive rækker.

  • Klik på OK.

Nu er hver kolonne navngivet som deres overskrift. Når du skriver en formel, vil dette navn blive angivet som en mulighed, der skal bruges.

Navngive område ved hjælp af Excel -tabeller

Når vi organiserer en data som en tabel i excel ved hjælp af CTRL + T, tildeles kolonneoverskrifterne automatisk navnet på den respektive kolonne. Du bør undersøge Excel -tabeller og deres fordele.

Sådan ser du alle navngivne intervaller?

Der vil være tidspunkter, hvor du gerne vil se alle tilgængelige navngivne områder i projektmappen. For at se alle navneområder Tryk på CTRL+F3. Eller du kan gå til Fanen Formel> Navneadministrator. Det vil liste alle navngivne områder, der er tilgængelige på projektmappen. Du kan redigere tilgængelige navngivne områder, slette dem og tilføje nye navne.

Et område flere navne

Excel giver brugerne mulighed for at navngive det samme område med forskellige navne. For eksempel kan rækkevidde A2: A10 hedde 'Kunder' og 'Kunder' begge på samme tid. Begge navne vil referere til det samme område A2: A10.
Men du kan ikke have de samme navne til to forskellige intervaller. Det eliminerer chancen for tvetydighed.

Få liste over navngivne intervaller på ark

Så hvis du vil have en liste med navngivne områder og de områder, de dækker, kan du bruge denne genvej til at indsætte dem på et sted i et ark.

    • Vælg en celle, hvor du vil få listen over navngivne områder.
    • Tryk på F3. Dette åbner en Pskynd navn dialogboks.
    • Klik på indsæt liste knap.

  • Listen indsættes på den markerede celle og fremefter.


Hvis du dobbeltklikker på det navngivne områdes navn i feltet Indsæt navn, bliver de skrevet som formler i cellen. Prøv det.

Opdater navngivne områder manuelt

Nå, når du indsætter en celle inden for et navngivet område, opdateres den automatisk og udvider den. Men hvis du tilføjer data i slutningen af ​​tabellen, skal du opdatere det navngivne felt. Følg disse trin for at opdatere navngivne områder.

  • Trykke CTRL+F3, for at åbne navneadministratoren.
  • Klik på det navngivne område, du vil redigere. Klik på Rediger.
  • I Referencer til kolonne skriver du det område, du vil udvide, og trykker på OK.

Og det er gjort. Dette er manuel opdatering af navngivne intervaller. Vi kan dog gøre det dynamisk ved at bruge nogle formler.

Opdater navngivne områder dynamisk

Det er klogt at gøre dine navngivne områder dynamiske, så du ikke behøver at redigere dem, når dine data overskrider det foruddefinerede område.
Jeg har dækket det i en separat artikel kaldet Dynamic Named -intervaller. Du kan lære og forstå fordelene ved det i detaljer her.

Sletning af navngivne områder

Når du sletter en del af det navngivne område, justerer det automatisk sit område. Men når du sletter hele navneområdet, forsvinder det fra navnelisten. Enhver formel, der er afhængig af disse områder, viser #REF -fejl, eller de giver forkert output (tællefunktioner).
Af en eller anden grund skal du følge disse trin, hvis du vil slette navngivne områder.

  • Tryk på CTRL+F3. Navneadministrator åbnes.
  • Vælg Navngivne områder, som du vil slette.
  • Klik på knappen Slet, eller tryk på knappen Slet på tastaturet.


Advarsel: Inden du sletter de navngivne områder, skal du sørge for, at ingen formler er afhængige af disse navne. Hvis der er nogen, skal du først konvertere dem til intervaller. Ellers ser du #REF -fejl.

Sletning af navne med fejl

Excel giver et værktøj til at fjerne navne, der kun har fejl. Du behøver ikke at identificere dem hver for sig. Følg disse trin for at slette navne med fejl:

    • Åbn Name Manager (CTRL+F3).
    • Klik på rullemenuen Filter i øverste højre hjørne.
    • Vælg "Navn med fejl"

  • Vælg Alle, og tryk på knappen Slet.

Og de er væk. Alle navne med fejl slettes straks fra rekorden.

Navngivet intervaller med formler

Den bedste brug af navngivne områder opleves med formler. Formlerne bliver mere fleksible og læsbare med navngivne intervaller. Lad os se hvordan.

Let at skrive formler

Lad os nu sige, at du har navngivet et område som "varer". Nu er elementlisten, du vil tælle "blyanter". Med navn er det let at skrive denne COUNTIF -formel. Bare skriv

= COUNTIF (element, "blyant")

Så snart du skriver formelens åbningsparentes, vises listen over tilgængelige navngivne områder. Uden et navn ville du skrive en gi COUNTIF -funktion i Excel med områder, som du muligvis først skal kigge på området for derefter at vælge området eller skrive det i formlen.

Excel viser de tilgængelige navneområder.

Navnene på områder vises som forslag, når du skriver et bogstav efter = tegn. Det samme som excel viser listen over formler. Hvis du f.eks. Skriver = u, vises hver metode og navngivet område, der starter med u, så du nemt kan bruge dem.

Lav konstanter ved hjælp af navngivne intervaller

Indtil videre har vi lært om navngivningsområder, men du kan faktisk også navngive værdier. For eksempel, hvis dit klientnavn er Sunder Pichai, kan du lave et navn "Client", og det refererer til at skrive "Sundar Pichai". Nu, når du skriver = Klient i en hvilken som helst celle, viser den Sundar Pichai.


Ikke kun tekst, men du kan også tildele et tal som konstant at arbejde med. For eksempel definerer du et mål. Eller værdien af ​​noget, der ikke vil ændre sig.
Absolut og relativ reference med navngivne intervaller

Henvisningen med Named -områder i er meget fleksibel. For eksempel, hvis du skriver navnet på et navngivet område i relativ celle, vil det opføre sig som en tilsvarende reference. Se billedet herunder.

Men når du bruger det med formler, vil det opføre sig som absolut. Nå, det meste af tiden vil du bruge dem med formler, så du kan sige, at de som standard er absolutte, men faktisk er de fleksible.

Men vi kan også gøre dem relative.

Hvordan laver man relative navngivne intervaller i Excel?

Lad os sige, hvis jeg vil navngive et område "Før", der vil referere til cellen, der er tilbage til, hvor det er skrevet. Hvordan gør jeg det? Følg disse trin:

  • Tryk på CTRL+F3
  • Klik på Ny
  • Skriv "Befor" i afsnittet "Navn".
  • I afsnittet 'Henviser til:' skriver du adressen på cellen i venstre. For eksempel, hvis du er i celle B1, skal du skrive "= A2" i afsnittet "Henviser til:". Sørg for, at det ikke har $ -tegnet.


Nu hvor du vil skrive “Befor” i formel, vil det referere til cellen, der er tilbage til den.

Her brugte jeg før i COLUMN -funktionen. Formlen returnerer kolonnenummeret i den venstre celle, hvor den er skrevet. Til min overraskelse viser A1 kolonnenummeret for den sidste kolonne. Hvilket betyder, at arket er cirkulært. Jeg troede, at det ville vise en #REF -fejl.

Giv navn til ofte brugte formler?

Nu er denne fantastisk. Mange gange bruger du den samme formel igen og igen i et regneark. For eksempel kan du kontrollere, om et navn er på din kundeliste eller ej. Og dette behov kan forekomme mange gange. Til dette skriver du den samme komplekse formel hver gang.

= HVIS (COUNTIF (Kunde, I3), "I liste", "Ikke på liste")

Hvad med, hvis du bare skriver '= IsInCustomer' i en celle, og det viser dig, om værdien i venstre celle er på kundelisten eller ej?

For eksempel har jeg forberedt et bord her. Nu vil jeg bare skrive “= IsInCustomer” i J5, og jeg vil gerne se, om værdien i I5 er på kundeliste eller ej. Følg disse trin for at gøre dette.

    • Tryk på CTRL+F3
    • Klik på Ny
    • Skriv i navnet 'IsInCustomer'
    • Skriv din formel i 'Henviser til'. = HVIS (COUNTIF (Kunde, I5), "I liste", "Ikke på liste")

  • Tryk på OK -knappen.

Uanset hvor du skriver 'IsInCustomer', kontrollerer den værdien i venstre celle i kundelisten.

Dette forhindrer dig i at gentage dig selv igen og igen.

Anvend navngivne områder til formler

Så mange gange definerer vi navne til vores områder, efter at vi allerede har skrevet formler baseret på områder. For eksempel har jeg Totalpris som celler = E2*F2. Hvordan kan vi ændre det til Enheder*Unit_Cost.

    • Vælg formlerne.
    • Gå til formelfanen. Klik på rullelisten Definer navn.

  • Klik på Anvend navne.
  • Liste over alle navngivne områder vises. Vælg de rigtige navne, og tryk på ok.


Og navnene er nu anvendt. Du kan se det i formellinjen.

Let at læse formler med navngivne intervaller

Som du har set, gør navngivne områder det let at læse formlerne. Hvis jeg skriver = COUNTIF (“A2: A100”, B2), vil ingen forstå, hvad jeg prøver at tælle, før de ser dataene, eller nogen forklarer det for dem.

Men hvis jeg skriver = COUNTIF (region, 'øst'), vil de fleste brugere med det samme få det, at vi tæller forekomst af 'øst' i område navngivet område.

Bærbare formler

Navngivne områder gør det meget let at kopiere og indsætte formler uden at bekymre sig om at ændre referencer. Og du kan tage en formel fra en projektmappe til en anden, og det vil fungere fint, indtil og medmindre destinationens projektmappe har samme navne.

For eksempel hvis du har en formel = COUNTIF (region, øst) i distributionstabellen, og du har en anden projektmappe at sige kunder der også har et navngivet område "Region". Hvis du nu kopierer denne formel direkte hvor som helst på projektmappen, viser den dig korrekte oplysninger. Datastrukturen har ingen betydning. Det er ligegyldigt, hvor fanden er den kolonne i din projektmappe. Dette vil fungere korrekt.

I billedet ovenfor har jeg brugt nøjagtig samme formel i to forskellige filer til at tælle antal eller øst forekomme i regionliste. Nu er de i forskellige kolonner, men da begge derefter er navngivet som region, vil det fungere perfekt.

Naviger let i projektmappe

Det bliver lettere at navigere i projektmappe med navngivne områder. Du skal bare skrive navnet på den navngivne i navneboksen. Excel tager dig til området, uanset hvor du er i projektmappen. I betragtning af at det navngivne område er af projektmappens omfang.
For eksempel, hvis du er på ark10, og du vil have en kundeliste, og du ikke ved, på hvilket ark det er. Gå bare til navne boksen og skriv 'kunde'. Du bliver dirigeret til det navngivne område på brøkdele af et sekund.


Det vil reducere indsatsen for at huske rækkevidden.

Naviger ved hjælp af hyperlinks med navngivet område

Når dit ark er stort, og du ofte går fra et punkt til et andet, kan du lide at bruge hyperlinks til at navigere let. Godt navngivne intervaller kan fungere perfekt med hyperlinks. Følg disse trin for at tilføje hyperlinks ved hjælp af navngivne områder.

    • Vælg en celle, hvor du vil have hyperlink
    • Tryk på CTRL+K eller gå til fanen Indsæt> Hyperlink for at åbne dialogboksen Indsæt hyperlink.

    • Klik på Placer i dette dokument.
    • Rul ned for at se tilgængelige navngivne områder under definerede navne

  • Vælg det navngivne område for at indsætte et hyperlink til dette område.

Og det er gjort. Du har dit hyperlink til dit valgte navngivne område. Ved hjælp af dette kan du oprette et indeks over navngivne områder, som du kan se og klikke for at navigere direkte til dem. Dette vil gøre din projektmappe virkelig brugervenlig.

Navngivet område og datavalidering

Navngivne områder og datavalidering er formet til hinanden. Navngivne områder gør datavalidering meget tilpasselig. Det bliver meget lettere at tilføje en validering fra en liste ved hjælp af navngivet område. Lad os se, hvordan…

    • Gå til fanen Data
    • Klik på Datavalidering
    • Vælg Liste i sektionen ‘Tillad:’
    • I afsnittet 'Kilde:' skal du skrive "= Kunde" (skriv det navngivne område, du har)

  • Hit OK

Nu vil denne celle have navne på kunder, der er en del af kundens navngivne område. Let, ikke sandt.

Afhængig eller cascading datavalidering med navngivne intervaller

Hvad nu hvis du vil have en cascading eller afhængig datavalidering. For eksempel, hvis du vil have en rulleliste, der har kategorier, Frugt og grøntsager. Nu, hvis du vælger frugt end en anden drop -down skal kun vise frugt mulighed, og hvis du vælger grøntsager, så kun grøntsager.

Dette kan let opnås ved at bruge navngivne områder. Lær hvordan.

  • Afhængig drop -down ved hjælp af navngivet område
  • Andre måder til validering af kaskadedata

Ingen datavalidering med navne på tabulerede data

Selvom Excel -tabeller giver strukturerede navne, men de kan ikke bruges med datavalidering og betinget formatering. Jeg ved ikke, hvorfor excel ikke tillader det.
Men det betyder ikke, at det ikke kan lade sig gøre. Du kan navngive områder inden for en tabel og derefter bruge dem til validering. Excel har ikke noget problem med det.

Omfang af navngivne områder

Hidtil talte vi om navngivne områder, der havde Workbook -omfang. Hvad? Vi diskuterede det ikke? Ok, så lad os hurtigt forstå, hvad omfanget af navngivne områder er.

Hvad er omfanget af et navneområde?

Brøndomfang definerer, hvor et navneinterval kan genkendes. Ethvert navn kan ikke genkendes uden for dets anvendelsesområde. For eksempel kan et navn i projektmappe1 ikke genkendes i forskellige projektmapper. Excel giver to muligheder for omfanget af navngivne områder Regneark og projektmappe.

Hvordan defineres et omfang af navngivet område?

Når du opretter et nyt navneområde, kan du se en sektion 'Omfang:'. Klik på rullemenuen, og vælg omfanget for dit navneområde. Du kan ikke ændre omfang, når du har oprettet et navngivet område. Så hellere gøre det før. Som standard er det projektmappe.

Arbejdsbog Omfang

Dette er standardområdet for et navngivet område. Et navn, der er defineret med projektmappens omfang, kan bruges i hele projektmappen, hvor det er defineret (ikke andre projektmapper).
Alle ovenstående eksempler havde projektmappeomfang.

Arbejdsark Omfang

Et navn, der defineres med et regnearksomfang, kan kun bruges på definere -regneark. For eksempel hvis jeg definerer 'Total' for en samlet celle med omfang af ark1. Så vil totalværdien kun blive registreret på ark1. Andre ark genkendes ikke.

Jeg vil have et Excel -omfang

Excel har ikke Global eller sig Excel Scope. Faktisk vil jeg gerne definere nogle navne, der kan genkendes i alle projektmapper på mit system. Hvis nogen ved, hvordan vi kan gøre dette, så lad mig det vide.

Redigering af omfang efter oprettelse af navne

Du kan ikke. Excel tillader dig ikke at redigere omfanget af et navngivet område, når du har oprettet. Da alle navngivne områder på et ark som standard er Workbook Scoped, og du måske vil ændre deres omfang til et ark.
For at gøre det skal du bare lave en kopi af det ark, og Excel vil gøre hvert navn på dette ark lokalt for at undgå tvetydighed. Du kan nu slette det originale ark, hvis du vil.

Cut Paste Name Range

Når du klipper og indsætter et navngivet område fra en destination til en anden, ændres referencen til en ny placering. For eksempel, hvis du har et navngivet område "Kunde" i A2: A10 og klipper og indsætter det på B2: B10, refererer kundens navn til det nye sted B2: B10.

Dynamiske navngivne områder i Excel

17 fantastiske funktioner i Excel -tabeller

Populære artikler:

50 Excel -genveje til at øge din produktivitet

Sådan bruges VLOOKUP -funktionen i Excel

Sådan bruges funktionen COUNTIF i Excel

Sådan bruges SUMIF -funktionen i Excel