Excel-formel: Dynamiskt namnområde med OFFSET -

Generisk formel

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

Sammanfattning

Ett sätt att skapa ett dynamiskt namnområde med en formel är att använda OFFSET-funktionen tillsammans med COUNTA-funktionen. Dynamiska intervall är också kända som expanderande intervall - de expanderar automatiskt och dras in för att rymma nya eller raderade data.

Obs: OFFSET är en flyktig funktion, vilket innebär att den beräknar om varje förändring till ett kalkylblad. Med en modern maskin och mindre datamängd bör detta inte orsaka problem men du kan se långsammare prestanda på stora datamängder. I så fall överväga att bygga ett dynamiskt namnområde med INDEX-funktionen istället.

I exemplet som visas är formeln som används för det dynamiska området:

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

Förklaring

Denna formel använder OFFSET-funktionen för att generera ett intervall som expanderar och dras genom att justera höjd och bredd baserat på ett antal icke-tomma celler.

Det första argumentet i OFFSET representerar den första cellen i data (ursprunget), som i detta fall är cell B5. Nästa två argument är förskjutningar för rader och kolumner och anges som noll.

De två sista argumenten representerar höjd och bredd. Höjd och bredd genereras i farten med hjälp av COUNTA, vilket gör den resulterande referensen dynamisk.

För höjd använder vi COUNTA-funktionen för att räkna icke-tomma värden i intervallet B5: B100. Detta förutsätter inga tomma värden i data och inga värden utöver B100. COUNTA returnerar 6.

För bredd använder vi COUNTA-funktionen för att räkna icke-tomma värden i området B5: Z5. Detta förutsätter inga rubrikceller och inga rubriker utöver Z5. COUNTA returnerar 6.

Vid denna punkt ser formeln ut så här:

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

Med denna information returnerar OFFSET en referens till B5: G10, vilket motsvarar ett intervall på 6 rader och 6 kolumner över.

Obs! Områdena som används för höjd och bredd bör justeras för att matcha kalkylbladets layout.

Variation med fullständiga kolumn- / radreferenser

Du kan också använda hela kolumn- och radreferenser för höjd och bredd så här:

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

Observera att höjden justeras med -2 för att ta hänsyn till rubrik- och titelvärden i cellerna B4 och B2. Fördelen med detta tillvägagångssätt är enkelheten i intervallen inom COUNTA. Nackdelen är de stora kolumnerna och raderna i stor storlek - försiktighet måste vidtas för att förhindra felaktiga värden utanför intervallet, eftersom de lätt kan kasta bort räkningen.

Fastställande av sista raden

Det finns flera sätt att bestämma den sista raden (sista relativa positionen) i en uppsättning data, beroende på strukturen och innehållet i data i kalkylbladet: