Generisk formel
=$A$1:INDEX($A:$A,lastrow)
Resumé
En måde at oprette et dynamisk navngivet område i Excel er at bruge INDEX-funktionen. I det viste eksempel defineres det navngivne interval "data" med følgende formel:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
der løser sig til området $ A $ 2: $ A $ 10.
Bemærk: denne formel er beregnet til at definere et navngivet område, der kan bruges i andre formler.
Forklaring
Denne side viser et eksempel på et dynamisk navngivet område oprettet med INDEX-funktionen sammen med COUNTA-funktionen. Dynamiske navngivne områder udvides automatisk og trækker sig sammen, når data tilføjes eller fjernes. De er et alternativ til at bruge en Excel-tabel, som også ændrer størrelse, når data tilføjes eller fjernes.
INDEX-funktionen returnerer værdien på en given position i et område eller array. Du kan bruge INDEX til at hente individuelle værdier eller hele rækker og kolonner i et interval. Hvad der gør INDEX især nyttigt for dynamiske navngivne områder, er at det faktisk returnerer en reference. Dette betyder, at du kan bruge INDEX til at konstruere en blandet reference som $ A $ 1: A100.
I det viste eksempel defineres det navngivne interval "data" med følgende formel:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
der løser sig til området $ A $ 2: $ A $ 10.
Sådan fungerer denne formel
Bemærk først, at denne formel er sammensat i to dele, der sidder på hver side af rækkeviddeoperatøren (:). Til venstre har vi startreferencen for området, hårdt kodet som:
$A$2
Til højre er slutreferencen for området, oprettet med INDEX som denne:
INDEX($A:$A,COUNTA($A:$A))
Her fodrer vi INDEX hele kolonne A til arrayet, og brug derefter COUNTA-funktionen til at finde ud af "sidste række" i området. COUNTA fungerer godt her, fordi der er 10 værdier i kolonne A, inklusive en header-række. COUNTA returnerer derfor 10, som går direkte ind i INDEX som række nummer. INDEX returnerer derefter en henvisning til $ A $ 10, den sidst anvendte række i området:
INDEX($A:$A,10) // resolves to $A$10
Så det endelige resultat af formlen er dette interval:
$A$2:$A$10
Et todimensionelt interval
Ovenstående eksempel fungerer i et endimensionelt interval. For at oprette et todimensionalt dynamisk område, hvor antallet af kolonner også er dynamisk, kan du bruge den samme tilgang, udvidet således:
=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Som før bruges COUNTA til at finde ud af "lastrow", og vi bruger COUNTA igen for at få "lastcolumn". Disse leveres til indeks som henholdsvis række og kolonne.
For arrayet leverer vi dog det fulde regneark, indtastet som alle 1048576 rækker, hvilket gør det muligt for INDEX at returnere en reference i et 2D-rum.
Bemærk: Excel 2003 understøtter kun 65535 rækker.
Bestemmelse af den sidste række
Der er flere måder at bestemme den sidste række (sidste relative position) i et datasæt afhængigt af strukturen og indholdet af dataene i regnearket:
- Sidste række i blandede data med blanke
- Sidste række i blandede data uden blanke
- Sidste række i tekstdata
- Sidste række i numeriske data
