Excel-formule: dynamisch benoemd bereik met OFFSET -

Generieke formule

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

Samenvatting

Een manier om een dynamisch benoemd bereik met een formule te maken, is door de functie OFFSET samen met de functie COUNTA te gebruiken. Dynamische bereiken worden ook wel uitbreidende bereiken genoemd: ze worden automatisch vergroot en verkleind om plaats te bieden aan nieuwe of verwijderde gegevens.

Opmerking: OFFSET is een vluchtige functie, wat betekent dat het bij elke wijziging in een werkblad opnieuw wordt berekend. Met een moderne machine en een kleinere gegevensset zou dit geen probleem moeten veroorzaken, maar u ziet mogelijk tragere prestaties bij grote gegevenssets. Overweeg in dat geval om een dynamisch benoemd bereik op te bouwen met de INDEX-functie.

In het getoonde voorbeeld is de formule die wordt gebruikt voor het dynamisch bereik:

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

Uitleg

Deze formule gebruikt de OFFSET-functie om een bereik te genereren dat groter en kleiner wordt door de hoogte en breedte aan te passen op basis van een aantal niet-lege cellen.

Het eerste argument in OFFSET vertegenwoordigt de eerste cel in de gegevens (de oorsprong), in dit geval cel B5. De volgende twee argumenten zijn offsets voor rijen en kolommen, en worden geleverd als nul.

De laatste twee argumenten vertegenwoordigen hoogte en breedte. Hoogte en breedte worden direct gegenereerd door COUNTA te gebruiken, waardoor de resulterende referentie dynamisch wordt.

Voor hoogte gebruiken we de COUNTA-functie om niet-lege waarden te tellen in het bereik B5: B100. Dit veronderstelt geen lege waarden in de gegevens en geen waarden buiten B100. COUNTA geeft 6 terug.

Voor breedte gebruiken we de COUNTA-functie om niet-lege waarden in het bereik B5: Z5 te tellen. Dit veronderstelt geen koptekstcellen en geen kopteksten buiten Z5. COUNTA geeft 6 terug.

Op dit punt ziet de formule er als volgt uit:

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

Met deze informatie retourneert OFFSET een verwijzing naar B5: G10, wat overeenkomt met een bereik van 6 rijen hoogte en 6 kolommen breed.

Opmerking: de bereiken die voor hoogte en breedte worden gebruikt, moeten worden aangepast aan de lay-out van het werkblad.

Variatie met volledige kolom- / rijverwijzingen

U kunt ook volledige kolom- en rijverwijzingen gebruiken voor hoogte en breedte, zoals:

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

Merk op dat de hoogte wordt aangepast met -2 om rekening te houden met de koptekst- en titelwaarden in de cellen B4 en B2. Het voordeel van deze benadering is de eenvoud van de reeksen binnen COUNTA. Het nadeel komt van de enorme grootte van volledige kolommen en rijen - er moet voor worden gezorgd dat er geen foutieve waarden buiten het bereik vallen, omdat ze de telling gemakkelijk kunnen weggooien.

Bepalen van de laatste rij

Er zijn verschillende manieren om de laatste rij (laatste relatieve positie) in een set gegevens te bepalen, afhankelijk van de structuur en inhoud van de gegevens in het werkblad: