Navngivne områder er en af disse gamle crusty funktioner i Excel er, at få brugerne til at forstå. Nye brugere kan finde dem underlige og skræmmende, og selv gamle hænder kan undgå dem, fordi de synes meningsløse og komplekse.
men navngivne områder er faktisk en temmelig cool 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ærbar).faktisk bruger jeg navngivne intervaller hele tiden, når jeg tester og prototypeformler. De hjælper mig med at få formler til at arbejde hurtigere., Jeg bruger også navngivne intervaller, fordi jeg er doven og ikke kan lide at skrive i komplekse referencer:)
det grundlæggende i navngivne intervaller i E ?cel
Hvad er et navngivet område?
et navngivet område er bare et menneskeligt læsbart navn for en række celler i E .cel. For eksempel, hvis jeg navnet området A1:A100 “data”, jeg kan bruge MAX til at få den maksimale værdi med en simpel formel:
=MAX(data) // max value
skønhed af navngivne områder er, at du kan bruge meningsfulde navne i dine formler uden at tænke over, cellereferencer., Når du har et navngivet område, skal du bare bruge det ligesom en cellehenvisning. Alle disse formler er gældende med det navngivne område “data”:
=MAX(data) // max value=MIN(data) // min value=COUNT(data) // total values=AVERAGE(data) // min valueVideo: Hvordan man kan skabe et navngivet område
Oprettelse af et navngivet område er let
Oprettelse af et navngivet område er hurtigt og nemt. Vælg blot en række celler, og skriv et navn i feltet Navn. Når du trykker på return, oprettes navnet:
for hurtigt at teste det nye område skal du vælge det nye navn i rullemenuen ved siden af feltet Navn. E .cel vælger området på regnearket.,
Excel, kan du oprette navne automatisk (ctrl + shift + F3)
Hvis du har vel strukturerede data med etiketter, kan du have Excel oprette navngivne områder for dig. Vælg bare dataene sammen med etiketterne og brug kommandoen “Opret fra valg” på fanen Formler på båndet:
Du kan også bruge tastaturgenvejen control + shift + F3.ved hjælp af denne funktion kan vi oprette navngivne intervaller for befolkningen i 12 stater i et trin:
Når du klikker på OK, oprettes navnene., Du vil finde alle nyoprettede navne i drop-down menuen ved siden af feltet navn:
Med navne, der oprettes, kan du bruge dem i formler som denne
=SUM(MN,WI,MI)Update navngivne områder i Navnet Manager (Ctrl + F3)
Når du opretter et navngivet område, skal du bruge Navnet Manager (Ctrl + F3) for at opdatere som det er nødvendigt. 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.,
Der er ingen grund til at klikke på knappen Rediger for at opdatere en reference. Når du klikker på Luk, opdateres områdenavnet.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 rækkehenvisninger ved at klikke og trække direkte på regnearket. Hvis du kender en måde at gøre dette på, kime ind nedenfor!
se alle navngivne områder (control + F3)
Hvis du hurtigt vil se alle navngivne områder i en projektmappe, skal du bruge rullemenuen ved siden af feltet Navn.,
Hvis du vil se flere detaljer, kan du åbne Navn Manager (Ctrl + F3), som indeholder alle navne med referencer, og giver et filter så godt:
Bemærk: på en Mac, der er ingen Navn Manager, så vil du se dialogboksen Definer Navn i stedet.
Kopier og indsæt alle navngivne områder (F3)
Hvis du vil have en mere vedvarende registrering af navngivne områder i en projektmappe, kan du indsætte den fulde liste over Navne, hvor som helst du vil., Gå til Formler > Brug i Formel (eller bruge genvejen F3), og vælg derefter Indsæt navne > Indsæt Liste:
Når du klikker på Indsæt Liste-knappen, kan du se de navne og referencer, der er indsat i regnearket:
Se navne direkte på regnearket
Hvis du angiver zoomniveauet til mindre end 40%, vil Excel vise vifte navne direkte på regnearket:
Tak for dette tip, Felipe!,
Navne har regler
Når du opretter navngivne områder, skal du følge disse regler:
Navngivne områder i formler
Navngivne områder er nem at bruge i formler
For eksempel, lad os sige, at du navngive en celle i din projektmappe “opdateret”. Ideen er, at du kan sætte den aktuelle dato i cellen (Ctrl + 😉 og henvise til datoen andetsteds i projektmappen.
formlen i B8 ser ud som dette:
="Updated: "& TEXT(updated, "ddd, mmmm d, yyyy")Du kan indsætte i denne formel vilkårligt sted i projektmappen, og det vil vise korrekt., Når du ændrer datoen i “Opdateret”, opdateres meddelelsen, uanset hvor formlen bruges. Se denne side for flere eksempler.
navngivne intervaller 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å tab-tasten for at indtaste navnet, når du har en kamp og ønsker, at e .cel skal indtaste navnet.,
Navngivne områder kan arbejde som konstanter
Fordi navngivne områder er skabt i en central beliggenhed, kan du bruge dem som konstanter uden en cellereference. For eksempel, kan du oprette navne som “MPG” (miles per gallon) og “CPG” (cost-per-gallon) med og tildele faste værdier:
Så kan du bruge disse navne, hvor som helst, du ønsker, i formler, og opdatere deres værdi i en central beliggenhed.,
Navngivne områder er absolut som standard
Som standard, navngivne områder opfører sig som absolutte referencer. For eksempel, i dette regneark, formlen til at beregne brændsel ville være:
=C5/$D$2
Hvis vi navn D2 “MPG” formlen bliver:
=C5/MPG
Da MPG er absolut som standard, kan formlen kopieres nedad i kolonne D, som de er.
navngivne områder kan også være relative
selvom navngivne områder er absolutte som standard, kan de også være relative. En relativ navngivet rækkevidde refererer til et område, der er i forhold til placeringen af den aktive celle på det tidspunkt, hvor området oprettes. Som et resultat er relative navngivne intervaller nyttige ved at opbygge generiske formler, der fungerer, uanset hvor de flyttes.,
For eksempel, kan du oprette en generisk “CellAbove” navngivne område som dette:
- Vælg celle A2
- Ctrl + F3 for at åbne Navn Manager
- Fanen i ‘Henviser til’ afsnit, og skriv derefter: =A1
CellAbove vil nu hente værdien fra cellen oven, hvor det er, det bruges.
Vigtigt: Sørg for, at den aktive celle er på det rigtige sted, før du opretter navnet.
Anvend navngivne intervaller på eksisterende formler
Hvis du har eksisterende formler, der ikke bruger navngivne intervaller, kan du bede e .cel om at anvende de navngivne intervaller 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.
e .cel erstatter derefter referencer, der har et tilsvarende navngivet interval med selve navnet.
Du kan også anvende navne med søg og erstat:
Vigtigt: Gem en sikkerhedskopi af dine regneark, og vælg netop de celler, du vil ændre, før brug af søg og erstat på formler.,nøglefordele ved navngivne områder
navngivne områder gør formler lettere at læse
den største enkeltfordel for 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 områder, en VLOOKUP formel til at hente “Position” fra tabellen er ret kryptisk:
=VLOOKUP($H$4,$B$3:$E$11,2,0)
Men, med B3:E11 hedder “data”, og H4 navnet “planet”, vi kan skrive formler som denne:
=VLOOKUP(planet,data,2,0) // position=VLOOKUP(planet,data,3,0) // diameter=VLOOKUP(planet,data,4,0) // satellites
et overblik, kan du se den eneste forskel på disse formler i kolonne indeks.
navngivne intervaller gør formler bærbare og genanvendelige
navngivne intervaller kan gøre det meget lettere at genbruge en formel i et andet regneark., Hvis du definerer navne på forhånd i et regneark, kan du indsætte i en formel, der bruger disse navne, og det vil “bare fungere”. Dette er en fantastisk måde at hurtigt få en formel til at fungere.
For eksempel denne formel tæller unikke værdier i et område af numeriske data:
=SUM(--(FREQUENCY(data,data)>0))hurtigt “port” denne formel til dit eget regneark, nævne en række “data”, og indsætte formlen i regnearket. Så længe “data” indeholder numeriske værdier, fungerer formlen straks.,Tip: Jeg anbefaler, at du opretter de nødvendige områdenavne *først* i destinationsarbejdsbogen og derefter kopierer i formlen som kun tekst (dvs.kopier ikke cellen, der indeholder formlen i et andet regneark, bare kopier teksten til formlen). Dette forhindrer E .cel i at oprette navne på farten og giver dig mulighed for fuldt ud at kontrollere navneoprettelsesprocessen. Hvis du kun vil kopiere formeltekst, skal du kopiere tekst fra formellelinjen eller kopiere via et andet program (dvs.bro .ser, teksteditor osv.).
navngivne områder kan bruges til navigation
navngivne områder er gode til hurtig navigation., Vælg blot rullemenuen ved siden af feltet Navn, og vælg et navn. Når du slipper musen, vælges området. Når et navngivet område findes på et andet ark, føres du automatisk til det ark.
Navngivne områder fungerer godt med hyperlinks
Navngivne områder gøre hyperlinks let. For eksempel, hvis du navngiver A1 i Sheet1″ home”, kan du oprette et hyperlink et andet sted, der tager dig tilbage der.,
for At bruge et navngivet område inde HYPERLINK-funktion, skal du tilføje et pund symbol foran det navngivne område:
=HYPERLINK("#home","take me home")Bemærk: ulige, du kan ikke hyperlink til et bord, som du kan for det normale område navn. Du kan dog definere et navn, der er lig med en tabel (dvs. =Tabel1) og hyperlink til det. Hvis nogen kender en måde at linke direkte en tabel, kime i!,
navngivne intervaller til datavalidering
Navne intervaller fungerer godt til datavalidering, da de giver dig mulighed for at bruge en logisk navngivet reference til at validere input med en rullemenu. Nedenfor række G4:G8, er navngivet “statuslist”, og derefter anvende data validering med en Liste, der er knyttet som dette:
resultatet er en dropdown menu i kolonne E, der gør at kun værdier i det navngivne område:
Dynamisk Navngivne områder
Navne intervaller er særdeles nyttige, når de juster automatisk til nye data i et regneark., En rækkevidde, der er oprettet på denne måde, kaldes et “dynamisk navngivet interval”. Der er to måder at gøre en rækkevidde dynamisk på: formler og tabeller.
dynamisk navngivet område med en tabel
en tabel er den nemmeste måde at oprette et dynamisk navngivet område på. Vælg en hvilken som helst celle i dataene, og brug derefter genvejen Control + T:
Når du opretter en e .cel-tabel, oprettes et navn automatisk (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.,
Dynamisk navngivne område med en formel
Du kan også oprette en dynamisk navngivne område med formler, ved hjælp af funktioner som OFFSET og INDEKS. Selvom disse formler er moderat komplekse, giver de en letvægtsløsning, når du ikke vil bruge et bord., Nedenstående links giver eksempler med fuld forklaringer:
- Eksempel på dynamic range formel med INDEX
- Eksempel på dynamic range formel med OFFSET
Tabel navne i data validering
Da Excel Tabeller indeholder en automatisk dynamisk område, ville de synes at være et naturligt valg for datavalideringsregler, hvor målet er at validere imod en liste, der kan være altid er under forandring. Et problem med tabeller er imidlertid, at du ikke kan bruge strukturerede referencer direkte til at oprette datavalidering eller betingede formateringsregler., Med andre ord kan du ikke bruge et bordnavn i betinget formatering eller data validering input områ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 løber gennem denne tilgang i detaljer.
Video: Sådan bruges navngivne intervaller med tabeller
sletning af navngivne intervaller
Bemærk: Hvis du har formler, der henviser til navngivne intervaller, kan du først opdatere formlerne, før du fjerner navne. Ellers vil du se #NAME?, fejl i formler, der stadig henviser til slettede navne. Gem altid dit regneark, før du fjerner navngivne intervaller, hvis du har problemer og skal vende tilbage til originalen.
navngivne intervaller juster, når du sletter og indsætter celler
Når du sletter *del* af et navngivet område, eller hvis Indsæt celler/rækker / kolonner inden for et navngivet område, justeres områdehenvisningen i overensstemmelse hermed og forbliver gyldig. Hvis du imidlertid sletter alle de celler, der omslutter et navngivet område, mister det navngivne område referencen og viser en #REF-fejl., For eksempel, hvis jeg navnet A1 “test”, og derefter slette En kolonne navn manager vil vise “henviser til” som:
=Sheet1!#REF!Slette navne med Navn Manager
for At fjerne navngivne områder fra en projektmappe manuelt, skal du åbne navn manager, vælg en række, og klik på knappen Slet. Hvis du vil fjerne mere end et navn på samme tid, kan du skifte + klik eller Ctrl + klik for at vælge flere navne og derefter slette i et trin.,
Slette navne med fejl
Hvis du har en masse af navne med reference fejl, kan du bruge knappen filter i navnet manager, der skal filtreres efter navne med fejl:
og Derefter på skift+klik for at vælge alle navne og slette.
Navngivne områder og Omfanget
Navngivne områder i Excel har noget, der hedder “anvendelsesområde”, der bestemmer, om et navngivet område er lokale for en given regneark, eller globalt på tværs af hele projektmappen. Globale navne har et omfang af” projektmappe”, og lokale navne har et omfang svarende til det arknavn, de findes på., For eksempel kan muligheden for et lokalt navn være “Sheet2”.
formålet med scope
navngivne områder med et globalt omfang er nyttige, når du vil have alle ark i en projektmappe for at få adgang til bestemte data, variabler eller konstanter. For eksempel kan du bruge et globalt navngivet interval en skatteprocent antagelse brugt i flere flere regneark.
lokalt omfang
lokalt omfang betyder, at et navn kun fungerer på det ark, det blev oprettet på. Det betyder, at du kan have flere regneark i samme projektmappe, som alle bruger samme navn., For eksempel har du måske en projektmappe med månedlige sporingsark (en om måneden), der bruger navngivne intervaller med samme navn, alle scoped lokalt. Dette kan give dig mulighed for at genbruge de samme formler i forskellige ark. Det lokale omfang gør det muligt for navnene i hvert ark at fungere korrekt uden at kollidere med navne i de andre ark.
for At henvise til et navn med en lokal rækkevidde, kan du præfiks arket navn til det område navn:
Sheet1!total_revenueSheet2!total_revenueSheet3!total_revenueRække navne, der er oprettet med navnet max automatisk har global rækkevidde., For at tilsidesætte denne opførsel skal du tilføje arknavnet, når du definerer navnet:
Sheet3!my_new_nameglobalt omfang
globalt omfang betyder, at et navn fungerer hvor som helst i en projektmappe. For eksempel kan du navngive en celle “last_update”, indtaste en dato i cellen. Derefter kan du bruge formlen nedenfor til at vise datoen sidst opdateret i ethvert regneark.
=last_updateglobale navne skal være unikke i en projektmappe.
lokalt omfang
lokalt scoped navngivne intervaller giver mening for regneark, der kun bruger navngivne intervaller til lokale antagelser., For eksempel har du måske en projektmappe med månedlige sporingsark (en om måneden), der bruger navngivne intervaller med samme navn, alle scoped lokalt. Det lokale omfang gør det muligt for navnene i hvert ark at fungere korrekt uden at kollidere med navne i de andre ark.
håndtering af navngivet rækkevidde
som standard er nye navne oprettet med namebo.globale, og du kan ikke redigere omfanget af et navngivet område, efter at 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å globaln gang fra globalt til lokalt, er det nogle gange fornuftigt at kopiere arket, der indeholder navnene. Når du duplikerer et regneark, der indeholder navngivne intervaller, kopierer e .cel de navngivne intervaller til det andet ark og ændrer omfanget til lokalt på samme tid. Når du har det andet ark med lokalt scoped navne, kan du eventuelt slette det første ark.Jan Karel Pieterse og Charles .illiams har udviklet et værktøj kaldet The Name Manager, der giver mange nyttige operationer til navngivne områder. Du kan do .nloade værktøjet Name Manager her.,
