Excel-formel: Dynamisk navngivet interval med OFFSET -

Generisk formel

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

Resumé

En måde at oprette et dynamisk navngivet område med en formel er at bruge OFFSET-funktionen sammen med COUNTA-funktionen. Dynamiske områder kaldes også udvidende områder - de udvides automatisk og trækker sig sammen for at imødekomme nye eller slettede data.

Bemærk: OFFSET er en flygtig funktion, hvilket betyder, at den genberegner med hver ændring til et regneark. Med en moderne maskine og mindre datasæt bør dette ikke forårsage et problem, men du kan muligvis se langsommere ydelse på store datasæt. I så fald skal du overveje at oprette et dynamisk navngivet område med INDEX-funktionen i stedet.

I det viste eksempel er formlen anvendt til det dynamiske område:

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

Forklaring

Denne formel bruger OFFSET-funktionen til at generere et område, der udvides og trækker sig sammen ved at justere højde og bredde baseret på et antal ikke-tomme celler.

Det første argument i OFFSET repræsenterer den første celle i dataene (oprindelsen), som i dette tilfælde er celle B5. De næste to argumenter er forskydninger for rækker og kolonner og leveres som nul.

De sidste to argumenter repræsenterer højde og bredde. Højde og bredde genereres i farten ved hjælp af COUNTA, hvilket gør den resulterende reference dynamisk.

For højde bruger vi COUNTA-funktionen til at tælle ikke-tomme værdier i området B5: B100. Dette forudsætter ingen tomme værdier i dataene og ingen værdier ud over B100. COUNTA returnerer 6.

For bredde bruger vi COUNTA-funktionen til at tælle ikke-tomme værdier i området B5: Z5. Dette forudsætter ingen headerceller og ingen overskrifter ud over Z5. COUNTA returnerer 6.

På dette tidspunkt ser formlen sådan ud:

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

Med denne information returnerer OFFSET en reference til B5: G10, der svarer til et interval på 6 rækker med 6 kolonner på tværs.

Bemærk: Områderne, der bruges til højde og bredde, skal justeres for at matche regnearklayoutet.

Variation med fulde kolonne / rækkehenvisninger

Du kan også bruge fulde kolonne- og rækkehenvisninger til højde og bredde som sådan:

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

Bemærk, at højden justeres med -2 for at tage højde for header- og titelværdier i cellerne B4 og B2. Fordelen ved denne tilgang er enkelheden af intervallerne inden for COUNTA. Ulempen kommer fra de store kolonner og rækker i enorm størrelse - der skal udvises forsigtighed for at forhindre fejlagtige værdier uden for området, da de let kan kaste tællingen.