Dine Excel-data ændres ofte, så det er nyttigt at oprette et dynamisk defineret område, der automatisk udvides og trækkes sammen til størrelsen af dit dataområde. Lad os se hvordan.
Ved at bruge et dynamisk defineret område behøver du ikke manuelt at redigere områderne for dine formler, diagrammer og pivottabeller, når data ændres. Dette vil ske automatisk.
To formler bruges til at skabe dynamiske områder: OFFSET og INDEX. Denne artikel vil fokusere på at bruge INDEX-funktionen, da det er en mere effektiv tilgang. OFFSET er en flygtig funktion og kan bremse store regneark.
Indholdsfortegnelse
Opret et dynamisk defineret område i Excel
For vores første eksempel har vi en enkelt kolonne liste over data vist nedenfor.
Vi har brug for, at dette er dynamisk, så hvis flere lande tilføjes eller fjernes, opdateres rækkevidden automatisk.
I dette eksempel ønsker vi at undgå overskriftscellen. Som sådan ønsker vi intervallet $A$2:$A$6, men dynamisk. Gør dette ved at klikke på Formler > Definer navn.
Skriv “lande” i feltet “Navn”, og indtast derefter formlen nedenfor i feltet “Refererer til”.
=$A$2:INDEX($A:$A,COUNTA($A:$A))
At skrive denne ligning i en regnearkscelle og derefter kopiere den til boksen Nyt navn er nogle gange hurtigere og nemmere.
Hvordan virker det?
Den første del af formlen angiver startcellen for området (A2 i vores tilfælde), og derefter følger områdeoperatoren (:).
=$A$2:
Brug af områdeoperatoren tvinger INDEX-funktionen til at returnere et område i stedet for værdien af en celle. INDEX-funktionen bruges derefter sammen med COUNTA-funktionen. COUNTA tæller antallet af ikke-tomme celler i kolonne A (seks i vores tilfælde).
INDEX($A:$A,COUNTA($A:$A))
Denne formel beder INDEX-funktionen om at returnere området for den sidste ikke-tomme celle i kolonne A ($A$6).
Det endelige resultat er $A$2:$A$6, og på grund af COUNTA-funktionen er den dynamisk, da den finder den sidste række. Du kan nu bruge dette “lande” definerede navn inde i en datavalideringsregel, formel, diagram eller hvor vi nu skal henvise til navnene på alle landene.
Opret et tovejs dynamisk defineret område
Det første eksempel var kun dynamisk i højden. Men med en lille ændring og en anden COUNTA-funktion kan du skabe et område, der er dynamisk både i højden og bredden.
I dette eksempel vil vi bruge dataene vist nedenfor.
Denne gang vil vi oprette et dynamisk defineret område, som inkluderer overskrifterne. Klik på Formler > Definer navn.
Skriv ‘”salg” i feltet “Navn”, og indtast formlen nedenfor i feltet “Refererer til”.
=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Denne formel bruger $A$1 som startcelle. INDEX-funktionen bruger derefter en række af hele regnearket ($1:$1048576) til at kigge ind og vende tilbage fra.
En af COUNTA-funktionerne bruges til at tælle de ikke-tomme rækker, og en anden bruges til de ikke-tomme kolonner, hvilket gør den dynamisk i begge retninger. Selvom denne formel startede fra A1, kunne du have angivet en hvilken som helst startcelle.
Du kan nu bruge dette definerede navn (salg) i en formel eller som en diagramdataserie for at gøre dem dynamiske.