Dynamiske områder i Excel: Slik lager du automatisk oppdaterende data!

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.