Excel-dataene dine endres ofte, så det er nyttig å lage et dynamisk definert område som automatisk utvides og trekkes sammen til størrelsen på dataområdet. La oss se hvordan.
Ved å bruke et dynamisk definert område, trenger du ikke manuelt å redigere områdene for formler, diagrammer og pivottabeller når data endres. Dette vil skje automatisk.
To formler brukes til å lage dynamiske områder: OFFSET og INDEX. Denne artikkelen vil fokusere på å bruke INDEX-funksjonen da det er en mer effektiv tilnærming. OFFSET er en flyktig funksjon og kan bremse ned store regneark.
Innholdsfortegnelse
Opprett et dynamisk definert område i Excel
For vårt første eksempel har vi en enkelt kolonne liste over data som vises nedenfor.
Vi trenger at dette er dynamisk, slik at hvis flere land legges til eller fjernes, oppdateres området automatisk.
For dette eksemplet ønsker vi å unngå overskriftscellen. Som sådan ønsker vi området $A$2:$A$6, men dynamisk. Gjør dette ved å klikke Formler > Definer navn.
Skriv «land» i «Navn»-boksen og skriv deretter inn formelen nedenfor i «Refererer til»-boksen.
=$A$2:INDEX($A:$A,COUNTA($A:$A))
Å skrive denne ligningen inn i en regnearkcelle og deretter kopiere den inn i boksen Nytt navn er noen ganger raskere og enklere.
Hvordan virker dette?
Den første delen av formelen spesifiserer startcellen til området (A2 i vårt tilfelle), og deretter følger områdeoperatoren (:).
=$A$2:
Bruk av områdeoperatoren tvinger INDEKS-funksjonen til å returnere et område i stedet for verdien til en celle. INDEKS-funksjonen brukes da sammen med COUNTA-funksjonen. COUNTA teller antall ikke-tomme celler i kolonne A (seks i vårt tilfelle).
INDEX($A:$A,COUNTA($A:$A))
Denne formelen ber INDEX-funksjonen returnere området til den siste ikke-tomme cellen i kolonne A ($A$6).
Sluttresultatet er $A$2:$A$6, og på grunn av COUNTA-funksjonen er den dynamisk, da den finner den siste raden. Du kan nå bruke dette «land»-definerte navnet i en datavalideringsregel, formel, diagram eller hvor vi måtte henvise til navnene på alle landene.
Lag et toveis dynamisk definert område
Det første eksemplet var kun dynamisk i høyden. Men med en liten modifikasjon og en annen COUNTA-funksjon kan du lage en rekkevidde som er dynamisk både i høyde og bredde.
I dette eksemplet vil vi bruke dataene vist nedenfor.
Denne gangen vil vi lage et dynamisk definert område, som inkluderer overskriftene. Klikk på Formler > Definer navn.
Skriv «salg» i «Navn»-boksen og skriv inn formelen nedenfor i «Refererer til»-boksen.
=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Denne formelen bruker $A$1 som startcelle. INDEX-funksjonen bruker deretter en rekke av hele regnearket ($1:$1048576) for å se inn og gå tilbake fra.
En av COUNTA-funksjonene brukes til å telle de ikke-tomme radene, og en annen brukes for de ikke-tomme kolonnene som gjør den dynamisk i begge retninger. Selv om denne formelen startet fra A1, kunne du ha spesifisert hvilken som helst startcelle.
Du kan nå bruke dette definerte navnet (salg) i en formel eller som en diagramdataserie for å gjøre dem dynamiske.