Lag dynamiske områder i Excel
Data i Excel endrer seg ofte, og derfor er det praktisk å etablere dynamisk definerte områder som automatisk tilpasser seg endringer i datastørrelse. La oss se hvordan dette kan gjøres.
Ved bruk av dynamisk definerte områder slipper du å justere områder for formler, diagrammer og pivottabeller manuelt hver gang dataene endrer seg. Alt dette vil skje automatisk.
To formler er sentrale for å skape dynamiske områder: OFFSET og INDEX. Denne veiledningen vil fokusere på bruken av INDEX-funksjonen, da den generelt er mer effektiv. OFFSET er en såkalt «flyktig» funksjon, og kan potensielt forsinke ytelsen i større regneark.
Hvordan skape et dynamisk definert område i Excel
I vårt første eksempel starter vi med en enkel kolonne med data, som vist nedenfor.
Målet er å gjøre dette dynamisk, slik at området automatisk oppdateres ved tilføying eller fjerning av land.
I dette eksemplet vil vi utelate overskriftscellen. Vi sikter altså etter området $A$2:$A$6, men dynamisk. Dette oppnås ved å gå til «Formler» > «Definer navn».
I feltet «Navn» skriver du «land», og i feltet «Refererer til» legger du inn formelen nedenfor:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
Det kan være enklere å skrive inn denne formelen i en regnearkcelle, og deretter kopiere den til feltet «Nytt navn».
Hvordan fungerer dette?
Den første delen av formelen angir startcellen for området (A2 i dette tilfellet), etterfulgt av områdeoperatoren (:).
=$A$2:
Bruken av områdeoperatoren tvinger INDEKS-funksjonen til å returnere et område i stedet for kun en enkelt celles verdi. INDEKS-funksjonen kombineres deretter med COUNTA-funksjonen. COUNTA teller antall celler som ikke er tomme i kolonne A (seks i vårt tilfelle).
INDEX($A:$A,COUNTA($A:$A))
Denne formelen instruerer INDEX-funksjonen til å returnere området opp til den siste ikke-tomme cellen i kolonne A ($A$6).
Det endelige resultatet er $A$2:$A$6, og ved hjelp av COUNTA-funksjonen er området dynamisk, siden den automatisk finner siste rad. Du kan nå benytte dette definerte navnet «land» i en datavalideringsregel, formel, diagram eller andre steder der vi måtte referere til listen over land.
Opprett et toveis dynamisk definert område
Det første eksemplet var kun dynamisk i høyden. Med en liten endring og en ekstra COUNTA-funksjon, kan du lage et område som er dynamisk både i høyde og bredde.
For dette eksemplet bruker vi dataene som vises nedenfor.
Denne gangen skal vi lage et dynamisk definert område som inkluderer overskriftene. Gå til «Formler» > «Definer navn».
Skriv «salg» i «Navn»-boksen og legg inn formelen under 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 hele regnearket ($1:$1048576) for å finne utgangspunkt.
En av COUNTA-funksjonene teller antall ikke-tomme rader, mens den andre teller antall ikke-tomme kolonner, og dette gjør området dynamisk i begge retninger. Selv om formelen her starter fra A1, kunne du ha valgt en annen startcelle.
Du kan nå bruke dette definerte navnet («salg») i en formel eller som dataserie i et diagram for å gjøre dem dynamiske.