Generisk formel
=$A$1:INDEX($A:$A,lastrow)
Sammendrag
En måte å lage et dynamisk navngitt område i Excel er å bruke INDEX-funksjonen. I det viste eksemplet er det navngitte området "data" definert av følgende formel:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
som løser seg i området $ A $ 2: $ A $ 10.
Merk: denne formelen er ment å definere et navngitt område som kan brukes i andre formler.
Forklaring
Denne siden viser et eksempel på et dynamisk navngitt område opprettet med INDEX-funksjonen sammen med COUNTA-funksjonen. Dynamiske navneområder utvides automatisk og trekkes sammen når data legges til eller fjernes. De er et alternativ til å bruke en Excel-tabell, som også endrer størrelse når data blir lagt til eller fjernet.
INDEKS-funksjonen returnerer verdien på en gitt posisjon i et område eller en matrise. Du kan bruke INDEX til å hente individuelle verdier eller hele rader og kolonner i et område. Det som gjør INDEX spesielt nyttig for dynamiske navngitte områder, er at den faktisk returnerer en referanse. Dette betyr at du kan bruke INDEX til å lage en blandet referanse som $ A $ 1: A100.
I det viste eksemplet er det navngitte området "data" definert av følgende formel:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
som løser seg i området $ A $ 2: $ A $ 10.
Hvordan denne formelen fungerer
Legg først merke til at denne formelen er sammensatt i to deler som sitter på hver side av rekkevidden (:). Til venstre har vi startreferansen for området, hardkodet som:
$A$2
Til høyre er sluttreferansen for området, opprettet med INDEX slik:
INDEX($A:$A,COUNTA($A:$A))
Her mater vi INDEKS hele kolonne A for matrisen, og bruk deretter COUNTA-funksjonen til å finne ut den "siste raden" i området. COUNTA fungerer bra her fordi det er 10 verdier i kolonne A, inkludert en topprad. COUNTA returnerer derfor 10, som går direkte inn i INDEX som radnummer. INDEX returnerer deretter en referanse til $ A $ 10, den sist brukte raden i området:
INDEX($A:$A,10) // resolves to $A$10
Så det endelige resultatet av formelen er dette området:
$A$2:$A$10
Et todimensjonalt område
Ovennevnte eksempel fungerer for et endimensjonalt område. For å lage et todimensjonalt dynamisk område hvor antall kolonner også er dynamisk, kan du bruke samme tilnærming, utvidet slik:
=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Som før brukes COUNTA for å finne ut "lastrow", og vi bruker COUNTA igjen for å få "lastcolumn". Disse leveres til indeks som henholdsvis radnummer og kolonnenummer.
For matrisen leverer vi imidlertid hele regnearket, angitt som alle 1048576 rader, som gjør at INDEX kan returnere en referanse i et 2D-rom.
Merk: Excel 2003 støtter bare 65535 rader.
Bestemme den siste raden
Det er flere måter å bestemme den siste raden (siste relative posisjon) i et datasett, avhengig av strukturen og innholdet til dataene i regnearket:
- Siste rad i blandede data med blanke
- Siste rad i blandede data uten blanke
- Siste rad i tekstdata
- Siste rad i numeriske data
