Excel-formel: Dynamisk navngitt område med OFFSET -

Generisk formel

=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))

Sammendrag

En måte å lage et dynamisk navngitt område med en formel er å bruke OFFSET-funksjonen sammen med COUNTA-funksjonen. Dynamiske områder er også kjent som utvidende områder - de utvides automatisk og trekker seg sammen for å imøtekomme nye eller slettede data.

Merk: OFFSET er en flyktig funksjon, noe som betyr at den beregner på nytt ved hver endring av et regneark. Med en moderne maskin og mindre datasett, bør dette ikke forårsake et problem, men du kan se lavere ytelse på store datasett. I så fall bør du vurdere å bygge et dynamisk navngitt område med INDEX-funksjonen i stedet.

I eksemplet vist er formelen som brukes for det dynamiske området:

=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))

Forklaring

Denne formelen bruker OFFSET-funksjonen til å generere et område som utvides og trekkes sammen ved å justere høyde og bredde basert på antall ikke-tomme celler.

Det første argumentet i OFFSET representerer den første cellen i dataene (opprinnelsen), som i dette tilfellet er celle B5. De to neste argumentene er forskyvninger for rader og kolonner, og angis som null.

De to siste argumentene representerer høyde og bredde. Høyde og bredde genereres på fly ved å bruke COUNTA, noe som gjør den resulterende referansen dynamisk.

For høyde bruker vi COUNTA-funksjonen til å telle ikke-tomme verdier i området B5: B100. Dette forutsetter ingen tomme verdier i dataene, og ingen verdier utover B100. COUNTA returnerer 6.

For bredde bruker vi COUNTA-funksjonen til å telle ikke-tomme verdier i området B5: Z5. Dette forutsetter ingen topptekstceller, og ingen overskrifter utover Z5. COUNTA returnerer 6.

På dette tidspunktet ser formelen slik ut:

=OFFSET(B5,0,0,6,6)

Med denne informasjonen returnerer OFFSET en referanse til B5: G10, som tilsvarer et område på 6 rader med 6 kolonner på tvers.

Merk: Områdene som brukes for høyde og bredde, bør justeres for å matche regnearkoppsettet.

Variasjon med full kolonne / radreferanser

Du kan også bruke hele kolonne- og radreferanser for høyde og bredde slik:

=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))

Merk at høyden blir justert med -2 for å ta hensyn til topptekst- og tittelverdier i cellene B4 og B2. Fordelen med denne tilnærmingen er enkelheten til områdene i COUNTA. Ulempen kommer fra de enorme kolonnene og radene i stor størrelse - det må utvises forsiktighet for å forhindre feilverdier utenfor området, da de lett kan kaste bort tellingen.