Excel-data förändras ofta, därför är det praktiskt att skapa ett dynamiskt definierat område som automatiskt anpassar sig i storlek till ditt dataområde. Låt oss utforska hur man gör detta.
Genom att använda ett dynamiskt definierat område behöver du inte manuellt justera intervall för dina formler, diagram och pivottabeller när datan ändras. Detta sker automatiskt.
Två formler kan användas för att skapa dynamiska intervall: FÖRSKJUTNING (OFFSET) och INDEX. Denna artikel fokuserar på användningen av INDEX-funktionen, eftersom det är en mer effektiv metod. FÖRSKJUTNING är en funktion som kan sakta ner stora kalkylblad.
Hur man skapar dynamiska intervall i Excel
I vårt första exempel har vi en kolumnlista med data som visas nedan.
Vi vill att detta ska vara dynamiskt, så att om fler länder läggs till eller tas bort, kommer intervallet att uppdateras automatiskt.
I det här exemplet vill vi exkludera rubrikcellen. Vi vill alltså ha intervallet $A$2:$A$6, men dynamiskt. För att göra detta, klicka på Formler > Definiera namn.
Skriv ”länder” i rutan ”Namn” och ange sedan följande formel i rutan ”Refererar till”.
=$A$2:INDEX($A:$A,ANTALV($A:$A))
Att skriva in denna ekvation i en kalkylbladscell och sedan kopiera den till rutan Nytt namn kan ibland vara snabbare och enklare.
Hur fungerar detta?
Den första delen av formeln specificerar startcellen för intervallet (A2 i vårt fall) följt av intervalloperatorn (:).
=$A$2:
Användningen av intervalloperatorn tvingar INDEX-funktionen att returnera ett intervall istället för värdet i en cell. INDEX-funktionen används sedan tillsammans med ANTALV-funktionen. ANTALV räknar antalet icke-tomma celler i kolumn A (sex i vårt exempel).
INDEX($A:$A,ANTALV($A:$A))
Den här formeln instruerar INDEX-funktionen att returnera intervallet fram till den sista icke-tomma cellen i kolumn A ($A$6).
Slutresultatet blir $A$2:$A$6, och tack vare ANTALV-funktionen är det dynamiskt, eftersom den hittar den sista raden. Du kan nu använda det här definierade namnet ”länder” i en datavalideringsregel, formel, diagram, eller varhelst vi behöver referera till alla länders namn.
Skapa ett tvådimensionellt dynamiskt definierat intervall
Det första exemplet var endast dynamiskt på höjden. Men med en liten ändring och en extra ANTALV-funktion, kan du skapa ett intervall som är dynamiskt både på höjden och bredden.
I det här exemplet använder vi data som visas nedan.
Denna gång skapar vi ett dynamiskt definierat intervall som inkluderar rubrikerna. Klicka på Formler > Definiera namn.
Skriv ”försäljning” i rutan ”Namn” och ange följande formel i rutan ”Refererar till”.
=$A$1:INDEX($1:$1048576,ANTALV($A:$A),ANTALV($1:$1))
Den här formeln använder $A$1 som startcell. Funktionen INDEX använder sedan ett intervall av hela kalkylbladet ($1:$1048576) för att söka och hämta data från.
En av ANTALV-funktionerna används för att räkna antalet icke-tomma rader, och den andra för antalet icke-tomma kolumner, vilket gör det dynamiskt i båda riktningarna. Även om den här formeln startade från A1, hade du kunnat specificera vilken startcell som helst.
Du kan nu använda detta definierade namn (”försäljning”) i en formel eller som en dataserie i ett diagram för att göra dem dynamiska.