Navngitte områdene er en av disse crusty gamle funksjonene i Excel som få brukere til å forstå. Nye brukere kan finne dem rare og skremmende, og selv gamle hender kan unngå dem fordi de synes meningsløst og komplekse.
Men navngitte områdene er faktisk en ganske kul funksjon. De kan lage formler *mye* enklere å opprette, lese, og opprettholde. Og som en bonus, de gjør formler enklere å gjenbruke (mer portable).
faktisk, jeg bruker heter varierer hele tiden når testing og utvikling av nye formler. De hjelper meg med å få formler for å jobbe raskere., Jeg bruker også navngitte områdene fordi jeg er lat, og ikke liker å skrive i komplekse referanser 🙂
Det grunnleggende av navngitte områdene i Excel
Hva er et navngitt område?
Et navngitt område er bare en lesbar navn for en rekke celler i Excel. Hvis jeg For eksempel navn området A1:A100 «data», kan jeg bruke MAKS for å få maksimal verdi med en enkel formel:
=MAX(data) // max value
skjønnheten av navngitte områdene er at du kan bruke meningsfulle navn i formler uten å tenke på cellereferanser., Når du har et navngitt område, bare bruke den akkurat som en cellereferanse. Alle disse formlene er gyldig med navngitt område «data»:
=MAX(data) // max value=MIN(data) // min value=COUNT(data) // total values=AVERAGE(data) // min valueVideo: Slik oppretter du et navngitt område
Opprette en navngitt område er lett
Opprette en navngitt område er rask og enkel. Bare å velge et område med celler, og skriv inn et navn i navn-boksen. Når du trykker på retur, navnet er opprettet:
for Å raskt teste den nye serien, velger du det nye navnet i rullegardinlisten ved siden av navn-boksen. Excel vil celleområdet i regnearket.,
Excel kan skape navn automatisk (ctrl + shift + F3)
Hvis du har godt strukturerte data med etiketter, kan du opprette Excel navngitte områdene for deg. Bare velg data, sammen med selskapene, og bruk «Opprett fra Utvalg» – kommandoen på Formler kategorien på båndet:
Du kan også bruke hurtigtasten ctrl + skift + F3.
ved Hjelp av denne funksjonen, kan vi skape navngitte områdene for befolkningen i 12 stater i ett trinn:
Når du klikker OK, navnene er opprettet., Du vil finne alle nyopprettede navn i rullegardinmenyen ved siden av navn-boksen:
Med navn som er opprettet, kan du bruke dem i formler som dette
=SUM(MN,WI,MI)Oppdater navngitte områdene i Navnet Manager (Ctrl + F3)
Når du oppretter et navngitt område, bruke Navnet Manager (Ctrl + F3) for å oppdatere etter behov. Velg navnet du ønsker å jobbe med, og deretter endre referansen direkte (dvs. rediger «refererer til») eller klikk på knappen til høyre og velge et nytt område.,
Det er ingen grunn til å klikke på Rediger-knappen for å oppdatere en referanse. Når du klikker Lukk, omfanget navn vil bli oppdatert.
Merk: hvis du vil velge en hel navngitt område i et regneark, kan du dra til et nytt sted og referanse vil bli oppdatert automatisk. Men, jeg vet ikke en måte å justere utvalg referanser ved å klikke og dra direkte på regnearket. Hvis du kjenner en måte å gjøre dette på, klokkespill i nedenfor!
Se alle navngitte områdene (ctrl + F3)
Hvis du raskt vil se alle navngitte områdene i en arbeidsbok, kan du bruke rullegardinmenyen ved siden av navn-boksen.,
Hvis du ønsker å se flere detaljer, åpne Navn Manager (Ctrl + F3), som lister opp alle navn med referanser, og gir et filter som godt:
Merk: på en Mac, det er ingen Navn Manager, så vil du se Angi Navn dialog i stedet.
Kopier og lim inn alle navngitte områdene (F3)
Hvis du ønsker en mer vedvarende oppføring av navngitte områder i en arbeidsbok, kan du lime inn den fullstendige listen over navn hvor som helst., Gå til Formler > Bruk i Formel (eller bruk snarveien F3), og velg deretter Lim inn navn > Lim inn Listen:
Når du klikker Lim inn Liste-knappen, vil du se navn og referanser limes inn i regnearket:
Se navnene direkte på arbeidsarket
Hvis du vil angi zoomnivået til mindre enn 40%, Excel vil vise spekter navn direkte i regnearket:
Takk for dette tipset, Felipe!,
Navn har regler
Når du oppretter navngitte områdene, må du følge disse reglene:
Navngitte områdene i formler
Navngitte områdene er lett å bruke i formler
For eksempel, la oss si at du navn på en celle i arbeidsboken «oppdatert». Ideen er at du kan sette inn gjeldende dato i cellen (Ctrl + 😉 og se dato andre steder i arbeidsboken.
formelen i B8 ser ut som dette:
="Updated: "& TEXT(updated, "ddd, mmmm d, yyyy")Du kan lime inn denne formelen hvor som helst i arbeidsboken, og det vil vises på riktig måte., Når du endrer dato i «oppdatert», vil meldingen oppdatering hvor formelen som er brukt. Se denne siden for flere eksempler.
Navngitte områdene vises når du skriver inn en formel
Når du har opprettet et navngitt område, det vil dukke opp automatisk i formler når du skriver inn den første bokstaven i navnet. Trykk tab-tasten til å angi navnet når du har en kamp og ønsker Excel til å angi navnet.,
Navngitte områdene kan fungere som konstanter
Fordi navngitte områdene er opprettet i en sentral beliggenhet, og du kan bruke dem som konstanter uten en cellereferanse. For eksempel, du kan opprette nye navn som «MPG» (miles per gallon) og «CPG» (kostnad per gallon) med og gi faste verdier:
Deretter kan du bruke disse navnene hvor som helst i formler, og oppdatere sin verdi i en sentral beliggenhet.,
Navngitte områdene er absolutt standard
som standard, navngitte områder oppfører seg som absolutte referanser. For eksempel, i dette regnearket, formelen for å beregne drivstoff vil være:
=C5/$D$2
referanse til D2 er absolutt (låst), så formelen kan kopieres ned uten D2 endring.,
Hvis vi D2 navn «MPG» formelen blir:
=C5/MPG
Siden MPG er absolutt standard formel kan kopieres nedover i kolonne D as-er.
Navngitte områdene kan også være relativ
Selv om navngitte områdene er absolutt standard, men de kan også være relativ. En relativ navngitt område refererer til et område som er i forhold til plasseringen av den aktive cellen på den tiden serien er skapt. Som et resultat, i forhold navngitte områdene er nyttig bygningen generisk formler som fungerer uansett hvor de er flyttet.,
For eksempel, du kan opprette en generisk «CellAbove» navngitt område som dette:
- Velg celle A2
- Control + F3 for å åpne Navn Manager
- Kategorien i ‘Refererer til’ – delen, så skriv: =A1
CellAbove vil nå hente verdien fra cellen over hvor det er det som brukes.
Viktig: kontroller at den aktive cellen er i riktig posisjon før du oppretter navnet.
Gjelde navngitte områdene til eksisterende formler
Hvis du har eksisterende formler som ikke bruker navngitte områdene, kan du be Excel til å bruke de navngitte områdene i formler for deg., Start ved å velge cellene som inneholder formler som du ønsker å oppdatere. Deretter kjører Formler > Angi Navn > Bruk Navn.
Excel vil da erstatte referanser som har en tilsvarende navngitt område med navnet i seg selv.
Du kan også bruke navn med søk og erstatt:
Viktig: du kan Lagre en sikkerhetskopi av regnearket, og velger bare de cellene du vil endre før du bruker søk-og-erstatt på formler.,– Tasten fordeler av navngitte områdene
Navngitte områdene lage formler som er lettere å lese
Den største fordelen til navngitte områdene er de lage formler som er lettere å lese og vedlikeholde. Dette er fordi de erstatte kryptiske referanser med meningsfulle navn. For eksempel, tenk på dette regnearket med dataene på planetene i vårt solsystem., Uten navngitte områdene, en VLOOKUP formel for å hente «Posisjon» fra tabellen er ganske kryptisk:
=VLOOKUP($H$4,$B$3:$E$11,2,0)
Imidlertid med B3:E11 kalt «data», og H4 heter «planet», kan vi skrive formler som dette:
=VLOOKUP(planet,data,2,0) // position=VLOOKUP(planet,data,3,0) // diameter=VLOOKUP(planet,data,4,0) // satellites
På et øyeblikk, kan du se den eneste forskjellen i disse formlene i kolonne indeks.
Navngitte områdene lage formler bærbare og gjenbrukbare
Navngitte områdene kan gjøre det mye enklere å gjenbruke en formel i et annet regneark., Hvis du definerer navn i forkant av tid i et regneark, kan du lime inn i en formel som bruker disse navnene, og det vil «bare fungerer». Dette er en god måte å raskt få en formel som fungerer.
For eksempel, denne formelen teller unike verdier i et utvalg av numeriske data:
=SUM(--(FREQUENCY(data,data)>0))for Å raskt «port» denne formelen til dine egne regneark, navn en rekke «data» og limer formelen inn i regnearket. Så lenge «data» som inneholder numeriske verdier, formelen vil arbeide straks.,
Tips: jeg anbefaler at du opprette den nødvendige rekkevidde navn *først* i destinasjon arbeidsboken, og deretter kopiere i formel som bare tekst (dvs. ikke kopiere cellen som inneholder formelen i et annet regneark, bare kopier teksten i formelen). Dette stopper Excel fra å lage navn på-the-fly, og gjør at du fullt ut kan kontrollere navn prosessen. Hvis du bare vil kopiere formelen tekst, kopiere tekst fra formelen bar, eller kopiere via et annet program (dvs. nettleser, tekst editor, etc.).
Navngitte områdene kan brukes for navigasjon
Navngitte områdene er stor for rask navigering., Bare velg nedtrekksmenyen ved siden av navn-boksen, og velg et navn. Når du slipper museknappen, utvalget vil bli valgt. Når et navngitt område finnes på et annet ark, vil du bli ført til at arket automatisk.
Navngitte områdene fungerer godt med hyperkoblinger
Navngitte områdene lage hyperkoblinger lett. Hvis du For eksempel navnet A1 i Ark1 «home», kan du opprette en hyperkobling til et annet sted som tar deg tilbake til det.,
for Å bruke et navngitt område inne i HYPERKOBLING-funksjonen, kan du legge et pund symbol foran navnet utvalg:
=HYPERLINK("#home","take me home")Merk: merkelig, du kan ikke lenke til et bord som du kan en normal rekkevidde navn. Du kan imidlertid angi et navn som tilsvarer en tabell (dvs. =Table1) og hyperkobling til det. Hvis noen vet om en måte å lenke direkte til en tabell, klokkespill i!,
Navngitte områdene for datavalidering
Navn områder fungerer godt for datavalidering, siden de lar deg bruke en logisk heter referanse for å validere input med en drop-down menyen. Nedenfor har utvalget G4:G8 heter «statuslist», deretter bruke datavalidering med en Liste linket som dette:
resultatet er en dropdown meny i kolonne E som kun tillater verdier i det navngitte området:
Dynamisk Navngitte Områdene
Navn områdene er svært nyttig når de automatisk justere seg til nye data i et regneark., Et utvalg satt opp på denne måten er referert til som en «dynamisk navngitt område». Det er to måter å lage en dynamisk rekkevidde: formler og tabeller.
Dynamisk navngitt område med Bord
En Tabell er den enkleste måten å opprette en dynamisk navngitt område. Velg en hvilken som helst celle i dataene, og deretter bruke hurtigtasten Ctrl + T:
Når du opprette en Excel-Tabell, et navn som er opprettet automatisk (f.eks. Table1), men du kan endre navnet på tabellen som du vil. Når du har opprettet en tabell, det vil utvides automatisk når data er lagt til.,
Dynamisk navngitt område med en formel
Du kan også opprette en dynamisk navngitt område med formler, ved hjelp av funksjoner som OFFSET og STIKKORDREGISTER. Selv om disse formlene er moderat komplekse, de gir en lett løsning når du ikke ønsker å bruke en tabell., Koblingene nedenfor gir eksempler med full forklaringer:
- Eksempel på dynamisk område formel med INDEKS
- Eksempel på dynamisk område formel med OFFSET
Tabell navn i data validering
Siden Excel Tabeller gir en automatisk dynamisk område, ville de synes å være et naturlig valg for data valideringsregler, hvor målet er å validere mot en liste som kan være alltid i endring. Imidlertid ett problem med tabeller er at du ikke kan bruke strukturerte referanser direkte for å lage data-validering eller betinget formatering regler., Med andre ord, du kan ikke bruke en tabell navn i betinget formatering eller data validering av input områder.
Imidlertid, som en midlertidig løsning, kan du definere et navngitt et navngitt område som peker til en tabell, og deretter bruke det navngitte området for datavalidering eller betinget formatering. Videoen nedenfor går gjennom denne tilnærmingen i detalj.
Video: Hvordan bruke navngitte områdene med tabeller
Slette navngitte områdene
Merk: Hvis du har formler som refererer til navngitte områdene, kan du ønsker å oppdatere oppskriftene først, før du fjerner navn. Ellers, du vil se #NAVN?, feil i formler som fortsatt se slettet navn. Alltid lagre regnearket før du fjerner navngitte områdene i tilfelle du har problemer og trenger å gå tilbake til det opprinnelige.
Navngitte områdene justere når du slette og sette inn celler
Når du sletter *del* av et navngitt område, eller hvis du vil sette inn celler/rader/kolonner inne i et navngitt område, cellereferansene vil justere i henhold til dette og forbli gyldig. Imidlertid, hvis du vil slette alle celler som omslutter et navngitt område, det navngitte området vil miste referanse og viser en #REF feil., Hvis jeg For eksempel navn A1 «test», og deretter slette En kolonne, navnet manager vil vise «refererer til» som:
=Sheet1!#REF!Slett navn med Navn Manager
for Å fjerne navnet varierer fra en arbeidsbok manuelt, åpne navn manager, velger du et utvalg, og klikk på Slett-knappen. Hvis du vil fjerne mer enn ett navn på samme tid, kan du Shift + Klikk eller Ctrl + Klikk for å velge flere navn, slett deretter i ett trinn.,
Slett navn med feil
Hvis du har mange navn med referanse feil, kan du bruke filter-knappen i navnet manager til å filtrere på navn med feil:
Deretter shift+klikk for å velge alle navn og slett.
Navngitte områdene og Omfanget
Navngitte områdene i Excel har noe som heter «scope», som avgjør om et navngitt område er lokale for en gitt regneark, eller globale over hele arbeidsboken. Global navn har et omfang på «arbeidsbok», og lokale navn har et omfang som tilsvarer ark navnet finnes de på., For eksempel er omfanget for et lokalt navn kan være «Ark2».
formålet med omfanget
Navngitte områdene med et globalt omfang er nyttig når du vil at alle ark i en arbeidsbok for å få tilgang til visse data, variabler eller konstanter. Du kan For eksempel bruke en global navngitt område en skattesats forutsetning brukt i flere av flere regneark.
Lokale omfang
Lokale omfang betyr at et navn er bare fungerer på arket det ble opprettet på. Dette betyr at du kan ha flere regneark i samme arbeidsbok som alle bruker samme navn., For eksempel, kanskje du har en arbeidsbok med månedlige sporing ark (én per måned) som bruker navngitte områdene med samme navn, alle omfattet lokalt. Dette kan tillate deg å bruke de samme formler i ulike ark. Den lokale omfang tillater navn i hvert enkelt ark for å fungere på riktig måte uten å kollidere med navn i andre ark.
for Å se et navn med en lokal omfang, du kan prefiks arket navn til spekter navn:
Sheet1!total_revenueSheet2!total_revenueSheet3!total_revenueSpekter navn, som er opprettet med navn-boksen automatisk har globale omfang., Til å overstyre dette problemet ved å legge til arket navn når du definerer navn:
Sheet3!my_new_nameGlobale omfang
Globale omfang betyr et navn vil arbeide hvor som helst i en arbeidsbok. Du kan For eksempel navnet på en celle «last_update», skriver du inn en dato i cellen. Deretter kan du bruke formelen nedenfor for å vise dato sist oppdatert i alle regneark.
=last_updateGlobal navn må være unikt innenfor en arbeidsbok.
Lokale omfang
Lokalt omfattet navngitte områdene fornuftig for regneark som bruker navngitte områdene for lokale forutsetninger bare., For eksempel, kanskje du har en arbeidsbok med månedlige sporing ark (én per måned) som bruker navngitte områdene med samme navn, alle omfattet lokalt. Den lokale omfang tillater navn i hvert enkelt ark for å fungere på riktig måte uten å kollidere med navn i andre ark.
Administrere navngitt område omfang
som standard, nytt navn, som er opprettet med namebox er globale, og du kan ikke redigere omfanget av et navngitt område etter at den er opprettet. Men, som en midlertidig løsning, kan du slette og gjenopprette et navn med ønsket omfang.,
Dersom du ønsker å endre flere navn samtidig fra global til lokal, noen ganger er det fornuftig å kopiere ark som inneholder navnene. Når du kopierer et regneark som inneholder navngitte områdene, Excel kopier de navngitte områdene til andre ark, endre rammen til lokale på samme tid. Etter at du har andre ark med lokalt omfattet navn, kan du eventuelt sletter den første arket.
Jan Karel Pieterse og Charles Williams har utviklet et verktøy kalt Navnet Manager som gir mange nyttige operasjoner for navngitte områder. Du kan laste ned Navnet Manager-verktøyet her.,
