Excel

Navngivne områder i Excel

Named Ranges Excel

Navngivne områder er en af ​​disse crusty gamle funktioner i Excel, som kun få brugere forstår. Nye brugere kan finde dem underlige og skræmmende, og selv gamle hænder kan undgå dem, fordi de virker meningsløse og komplekse.

Men navngivne intervaller er faktisk en ret sej funktion. De kan gøre formler * meget * lettere at oprette, læse og vedligeholde. Og som en bonus gør de formler lettere at genbruge (mere bærbare).



Faktisk bruger jeg navngivne intervaller hele tiden, når jeg tester og prototyper formler. De hjælper mig med at få formler til at fungere hurtigere. Jeg bruger også navngivne områder, fordi jeg er doven og ikke kan lide at skrive komplekse referencer :)



Grundlæggende om navngivne områder i Excel

Hvad er et navngivet område?

Et navngivet område er bare et menneskeligt læsbart navn for en række celler i Excel. For eksempel, hvis jeg navngiver området A1: A100 'data', kan jeg bruge MAX til at få den maksimale værdi med en simpel formel:

 
 = MAX (data) // max value

Simpel navngivet rækkevidde kaldet



Det skønne ved navngivne områder er, at du kan bruge meningsfulde navne i dine formler uden at tænke på cellereferencer. Når du har et navngivet område, skal du bare bruge det ligesom en cellehenvisning. Alle disse formler er gyldige med det navngivne interval 'data':

 
= MAX (data) // max value = MIN (data) // min value = COUNT (data) // total values = AVERAGE (data) // min value

Video: Sådan oprettes et navngivet område

Det er nemt at oprette et navngivet område

Det er hurtigt og nemt at oprette et navngivet område. Vælg bare et celleområde, og skriv et navn i navnefeltet. Når du trykker på retur, oprettes navnet:



Opret et navngivet område hurtigt med navnefeltet

For hurtigt at teste det nye interval skal du vælge det nye navn i rullemenuen ud for navnefeltet. Excel vælger området på regnearket.

Excel kan oprette navne automatisk (ctrl + shift + F3)

Hvis du har velstrukturerede data med etiketter, kan du få Excel til at oprette navngivne intervaller til dig. Vælg bare dataene sammen med etiketterne, og brug kommandoen 'Opret fra markering' på fanen Formler på båndet:

Opret navne fra markeringskommandoen på båndet

Du kan også bruge tastaturgenvejskontrol + shift + F3.

Ved hjælp af denne funktion kan vi oprette navngivne områder for befolkningen i 12 stater i et trin:

hvordan man laver en simpel søjlediagram i Excel

Opret navne fra valg med data og etiketter valgt

Når du klikker på OK, oprettes navnene. Du finder alle nyoprettede navne i rullemenuen ud for navnefeltet:

Nye navne vises også i rullemenuen Navneboks

Med oprettede navne kan du bruge dem i formler som denne

 
= SUM (MN,WI,MI)

Opdater navngivne områder i Name Manager (Control + F3)

Når du har oprettet et navngivet område, skal du bruge Navn Manager (Control + F3) for at opdatere efter behov. Vælg det navn, du vil arbejde med, og skift derefter referencen direkte (dvs. rediger 'refererer til'), eller klik på knappen til højre og vælg et nyt interval.

Opdaterede navngivne intervaller med Name Manager

Det er ikke nødvendigt at klikke på knappen Rediger for at opdatere en reference. Når du klikker på Luk, opdateres rækkevidden.

Bemærk: Hvis du vælger et helt navngivet område på et regneark, kan du trække til en ny placering, og referencen opdateres automatisk. Jeg kender dog ikke en måde at justere områdereferencer på ved at klikke og trække direkte på regnearket. Hvis du kender en måde at gøre dette på, kan du ringe nedenunder!

Se alle navngivne områder (kontrol + F3)

Hvis du hurtigt vil se alle navngivne områder i en projektmappe, skal du bruge rullemenuen ud for navnefeltet.

Hvis du vil se flere detaljer, skal du åbne Name Manager (Control + F3), der viser alle navne med referencer og også giver et filter:

Navneadministratoren viser alle nyoprettede navne

Bemærk: på en Mac er der ingen Name Manager, så du kan se dialogboksen Definer navn i stedet.

Kopier og indsæt alle navngivne områder (F3)

Hvis du vil have en mere vedholdende registrering af navngivne områder i en projektmappe, kan du indsætte den fulde liste med navne hvor som helst du vil. Gå til formler> Brug i formel (eller brug genvejen F3), og vælg derefter Indsæt navne> Indsæt liste:

Dialogboksen Indsæt navne

Når du klikker på knappen Indsæt liste, ser du navnene og referencerne indsat i regnearket:

Efter at have indsat navngivne intervaller i regneark

Se navne direkte på regnearket

Hvis du indstiller zoomniveauet til mindre end 40%, viser Excel rækkevidde navne direkte på regnearket:

På zoomniveau <40% viser Excel navne på rækkevidde

Tak for dette tip, Felipe!

Navne har regler

Når du opretter navngivne områder, skal du følge disse regler:

  1. Navne skal begynde med et bogstav, en understregning (_) eller et tilbageslag ()
  2. Navne kan ikke indeholde mellemrum og de fleste tegnsætningstegn.
  3. Navne kan ikke komme i konflikt med cellereferencer - du kan ikke navngive et interval 'A1' eller 'Z100'.
  4. Enkeltbogstaver er OK for navne ('a', 'b', 'c' osv.), Men bogstaverne 'r' og 'c' er reserveret.
  5. Navne er ikke store og små bogstaver - 'home', 'HOME' og 'HoMe' er alle de samme med Excel.

Navngivne intervaller i formler

Navngivne områder er nemme at bruge i formler

Lad os f.eks. Sige, at du navngiver en celle i din projektmappe 'opdateret'. Ideen er, at du kan placere den aktuelle dato i cellen (Ctrl +) og henvise til datoen andetsteds i projektmappen.

Brug af et navngivet område inde i en tekstformel

Formlen i B8 ser sådan ud:

 
='Updated: '&  TEXT (updated, 'ddd, mmmm d, yyyy')

Du kan indsætte denne formel hvor som helst i projektmappen, og den vises korrekt. Hver gang du ændrer datoen i 'opdateret', opdateres meddelelsen, uanset hvor formlen bruges. Se denne side for flere eksempler.

Navngivne områder vises, når du skriver en formel

Når du har oprettet et navngivet område, vises det automatisk i formler, når du skriver det første bogstav i navnet. Tryk på tabulatortasten for at indtaste navnet, når du har et match, og at Excel skal indtaste navnet.

Navngivne områder vises, når du indtaster formler

Navngivne områder kan fungere som konstanter

Fordi navngivne områder oprettes på en central placering, kan du bruge dem som konstanter uden en cellehenvisning. For eksempel kan du oprette navne som 'MPG' (miles pr. Gallon) og 'CPG' (pris pr. Gallon) med og tildele faste værdier:

Navngivne områder kan fungere som konstanter uden cellehenvisning

Derefter kan du bruge disse navne hvor som helst i formler og opdatere deres værdi på en central placering.

Brug af et navngivet område som en konstant i en formel

Navngivne områder er absolutte som standard

Som standard opfører navngivne områder sig som absolutte referencer. For eksempel vil formlen til beregning af brændstof i dette regneark være:

 
=C5/$D

Standardformel med absolut adresse

Henvisningen til D2 er absolut (låst), så formlen kan kopieres ned, uden at D2 ændres.

Hvis vi navngiver D2 'MPG', bliver formlen:

 
=C5/MPG

Brug af et navngivet område som en konstant i en formel

Da MPG som standard er absolut, kan formlen kopieres ned i kolonne D som den er.

Navngivne områder kan også være relative

Selvom navngivne områder er absolutte som standard, kan de også være relative. Et relativt navngivet område henviser til et interval, der er relativt til placeringen af ​​den aktive celle på det tidspunkt, hvor området oprettes . Som et resultat er relative navngivne områder nyttige bygningsgeneriske formler, der fungerer, uanset hvor de flyttes.

For eksempel kan du oprette et generisk 'CellAbove' navngivet område som dette:

  1. Vælg celle A2
  2. Control + F3 for at åbne Name Manager
  3. Tab til 'Henviser til' sektion, og skriv derefter: = A1

CellAbove henter nu værdien fra cellen ovenfor, uanset hvor den bruges.

Vigtigt: Sørg for, at den aktive celle er på det rigtige sted, inden du opretter navnet.

Anvend navngivne områder på eksisterende formler

Hvis du har eksisterende formler, der ikke bruger navngivne områder, kan du bede Excel om at anvende de navngivne områder i formlerne for dig. Start med at vælge de celler, der indeholder formler, du vil opdatere. Kør derefter formler> Definer navne> Anvend navne.

Dialogboksen Anvend navne

Excel erstatter derefter referencer, der har et tilsvarende navngivet område med selve navnet.

Du kan også anvende navne med find og erstat:

Anvendelse af navne varierer med find og erstat

Vigtigt: Gem en sikkerhedskopi af dit regneark, og vælg kun de celler, du vil ændre, før du bruger find and erstatt på formler.

De vigtigste fordele ved navngivne intervaller

Navngivne intervaller gør formler lettere at læse

Den største fordel ved navngivne områder er, at de gør formler lettere at læse og vedligeholde. Dette skyldes, at de erstatter kryptiske referencer med meningsfulde navne. Overvej for eksempel dette regneark med data om planeter i vores solsystem. Uden navngivne intervaller er en VLOOKUP-formel til at hente 'Position' fra tabellen ret kryptisk:

 
= VLOOKUP ($H,$B:$E,2,0)

Uden navngivne områder kan formler være kryptiske

hvordan man søger efter en stjerne i Excel

Men med B3: E11 med navnet 'data' og H4 med navnet 'planet' kan vi skrive formler som denne:

 
= VLOOKUP (planet,data,2,0) // position = VLOOKUP (planet,data,3,0) // diameter = VLOOKUP (planet,data,4,0) // satellites

Med navngivne områder kan formler være enkle

Med et hurtigt overblik kan du se den eneste forskel i disse formler i kolonneindekset.

Navngivne intervaller gør formler bærbare og genanvendelige

Navngivne områder kan gøre det meget nemmere at genbruge en formel i et andet regneark. Hvis du definerer navne på forhånd i et regneark, kan du indsætte en formel, der bruger disse navne, og det fungerer 'bare'. Dette er en fantastisk måde at hurtigt få en formel til at fungere.

For eksempel tæller denne formel unikke værdier i en række numeriske data:

 
= SUM (--( FREQUENCY (data,data)>0))

For hurtigt at 'portere' denne formel til dit eget regneark, navngiv en række 'data' og indsæt formlen i regnearket. Så længe 'data' indeholder numeriske værdier, fungerer formlen med det samme.

Tip: Jeg anbefaler, at du opretter de nødvendige rækkevidde * først * i destinationsarbejdsmappen og derefter kun kopierer i formlen som tekst (dvs. ikke kopierer cellen, der indeholder formlen, i et andet regneark, bare kopier teksten til formlen ). Dette forhindrer Excel i at oprette navne on-the-fly og l ets dig til fuldt ud at styre navnet oprettelse af processen. For kun at kopiere formeltekst skal du kopiere tekst fra formellinjen eller kopiere via en anden applikation (dvs. browser, teksteditor osv.).

Navngivne områder kan bruges til navigation

Navngivne områder er gode til hurtig navigation. Vælg bare rullemenuen ud for navnefeltet, og vælg et navn. Når du slipper musen, vælges rækkevidden. Når der findes et navngivet område på et andet ark, føres du automatisk til det ark.

Navngivne områder giver mulighed for enkel navigation

Navngivne områder fungerer godt med hyperlinks

Navngivne intervaller gør hyperlinks lette. For eksempel, hvis du navngiver A1 i Ark1 'hjem', kan du oprette et hyperlink et andet sted, der fører dig tilbage derhen.

Oprettelse af et hyperlink til et navngivet område

Eksempel på navngivet intervalhyperlink på regnearket

For at bruge et navngivet område inden for HYPERLINK-funktionen skal du tilføje et pundsymbol foran det navngivne interval:

 
= HYPERLINK ('#home','take me home')

Bemærk: mærkeligt, kan du ikke hyperlink til en tabel, som du kan en normal rækkevidde navn. Du kan dog definere et navn svarende til en tabel (dvs. = tabel1) og hyperlink til det. Hvis nogen kender en måde at linke direkte til et bord, så ring ind!

Navngivne intervaller til datavalidering

Navneområder fungerer godt til datavalidering, da de lader dig bruge en logisk navngivet reference til at validere input med en rullemenu. Nedenfor er området G4: G8 navngivet 'statusliste', og anvend derefter datavalidering med en sådan linket liste:

Brug af et navngivet område til datavalidering med liste

Resultatet er en rullemenu i kolonne E, der kun tillader værdier i det navngivne interval:

Datavalidering med navngivet rækkeeksempel

Dynamiske navngivne områder

Navneområder er yderst nyttige, når de automatisk tilpasser sig nye data i et regneark. Et område, der er indstillet på denne måde, kaldes et 'dynamisk navngivet område'. Der er to måder at gøre en række dynamisk: formler og tabeller.

Dynamisk navngivet rækkevidde med en tabel

En tabel er den nemmeste måde at oprette et dynamisk navngivet interval på. Vælg en hvilken som helst celle i dataene, og brug derefter genvejen Control + T:

Oprettelse af en Excel-tabel

Når du opretter en Excel-tabel, oprettes der automatisk et navn (f.eks. Tabel1), men du kan omdøbe tabellen, som du vil. Når du har oprettet en tabel, udvides den automatisk, når data tilføjes.

Tabeller udvides automatisk og kan omdøbes

Dynamisk navngivet rækkevidde med en formel

Du kan også oprette et dynamisk navngivet område med formler ved hjælp af funktioner som OFFSET og INDEX. Selvom disse formler er moderat komplekse, giver de en let løsning, når du ikke vil bruge en tabel. Links nedenfor giver eksempler med fulde forklaringer:

  • Eksempel på formel for dynamisk område med INDEX
  • Eksempel på formel for dynamisk område med OFFSET

Tabelnavne i datavalidering

Da Excel-tabeller giver et automatisk dynamisk interval, ser de ud til at være en naturlig pasform til datavalideringsregler, hvor målet er at validere mod en liste, der altid kan ændre sig. Et problem med tabeller er dog, at du ikke kan bruge strukturerede referencer direkte til at oprette datavalidering eller betingede formateringsregler. Med andre ord kan du ikke bruge et tabelnavn i betingede formaterings- eller datavalideringsinputområder.

Som en løsning kan du dog definere navngivet et navngivet område, der peger på en tabel, og derefter bruge det navngivne område til datavalidering eller betinget formatering. Videoen nedenfor gennemgår denne tilgang i detaljer.

Video: Sådan bruges navngivne intervaller med tabeller

Sletning af navngivne områder

Bemærk: Hvis du har formler, der refererer til navngivne intervaller, kan du først opdatere formlerne, før du fjerner navne. Ellers kan du se #NAME? fejl i formler, der stadig henviser til slettede navne. Gem altid dit regneark, inden du fjerner navngivne områder, hvis du har problemer og har brug for at vende tilbage til originalen.

Navngivne områder justeres, når celler slettes og indsættes

Når du sletter * del * af et navngivet område, eller hvis du indsætter celler / rækker / kolonner inden for et navngivet område, justeres rækkevidde i overensstemmelse hermed og forbliver gyldig. Men hvis du sletter alle de celler, der omslutter et navngivet område, mister det navngivne interval referencen og viser en #REF-fejl. For eksempel, hvis jeg navngiver A1 'test' og derefter sletter kolonne A, viser navneadministratoren 'henviser til' som:

 
=Sheet1!#REF!

Slet navne med Name Manager

For at fjerne navngivne områder fra en projektmappe manuelt skal du åbne navnehåndteringen, vælge et interval og klikke på knappen Slet. Hvis du vil fjerne mere end et navn på samme tid, kan du Skift + Klik eller Ctrl + Klik for at vælge flere navne og derefter slette i et trin.

Slet navne med fejl

Hvis du har mange navne med referencefejl, kan du bruge filterknappen i navnehåndteringen til at filtrere efter navne med fejl:

Navnemanager filtermenu

Skift derefter + klik for at vælge alle navne og slette.

Navngivne områder og rækkevidde

Navngivne områder i Excel har noget, der kaldes 'omfang', som bestemmer, om et navngivet område er lokalt for et givet regneark eller globalt på tværs af hele projektmappen. Globale navne har en rækkevidde af 'projektmappe', og lokale navne har et omfang svarende til det arknavn, de findes på. For eksempel kan omfanget af et lokalt navn være 'Sheet2'.

Formålet med anvendelsesområdet

Navngivne områder med et globalt anvendelsesområde er nyttige, når du vil have, at alle ark i en projektmappe skal have adgang til bestemte data, variabler eller konstanter. For eksempel kan du bruge et globalt navngivet område en skatteprocent antagelse brugt i flere forskellige regneark.

Lokalt omfang

Lokalt omfang betyder, at et navn kun fungerer på det ark, det blev oprettet på. Dette betyder, at du kan have flere regneark i den samme projektmappe, der alle bruger det samme navn. For eksempel har du måske en projektmappe med månedlige sporingsark (en pr. Måned), der bruger navngivne områder med samme navn, alt sammen lokaliseret. Dette kan give dig mulighed for at genbruge de samme formler i forskellige ark. Det lokale omfang tillader, at navnene på hvert ark fungerer korrekt uden at kollidere med navne i de andre ark.

For at henvise til et navn med et lokalt anvendelsesområde kan du forud for arknavnet til områdets navn:

 
Sheet1!total_revenue Sheet2!total_revenue Sheet3!total_revenue

Område navne oprettet med navneboks automatisk har globalt anvendelsesområde. For at tilsidesætte denne adfærd skal du tilføje arknavnet, når du definerer navnet:

 
Sheet3!my_new_name

Globalt anvendelsesområde

Globalt omfang betyder, at et navn vil fungere hvor som helst i en projektmappe. For eksempel kan du navngive en celle 'sidste_opdatering', indtaste en dato i cellen. Derefter kan du bruge formlen nedenfor til at vise datoen sidst opdateret i ethvert regneark.

 
=last_update

Globale navne skal være unikke i en projektmappe.

Lokalt omfang

Lokalt afgrænsede navngivne områder giver mening for regneark, der kun bruger navngivne områder til lokale antagelser. For eksempel har du måske en projektmappe med månedlige sporingsark (en pr. Måned), der bruger navngivne områder med samme navn, alt sammen lokaliseret. Det lokale omfang tillader, at navnene på hvert ark fungerer korrekt uden at kollidere med navne i de andre ark.

Håndtering af navngivet rækkevidde

Som standard er nye navne oprettet med navneboksen globale, og du kan ikke redigere omfanget af et navngivet område, når det er oprettet. Som en løsning kan du dog slette og genskabe et navn med det ønskede omfang.

Hvis du vil ændre flere navne på én gang fra globalt til lokalt, er det nogle gange fornuftigt at kopiere det ark, der indeholder navnene. Når du duplikerer et regneark, der indeholder navngivne områder, kopierer Excel de navngivne områder til det andet ark og ændrer omfanget til lokalt på samme tid. Når du har det andet ark med lokalt afgrænsede navne, kan du eventuelt slette det første ark.

Jan Karel Pieterse og Charles Williams har udviklet et værktøj kaldet Name Manager, der giver mange nyttige operationer til navngivne områder. Du kan download Name Manager-værktøjet her .

Forfatter Dave Bruns


^