Generieke formule
=$A$1:INDEX($A:$A,lastrow)
Samenvatting
Een manier om een dynamisch benoemd bereik in Excel te maken, is door de functie INDEX te gebruiken. In het getoonde voorbeeld wordt het benoemde bereik "gegevens" gedefinieerd door de volgende formule:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
wat wordt omgezet in het bereik $ A $ 2: $ A $ 10.
Opmerking: deze formule is bedoeld om een benoemd bereik te definiëren dat in andere formules kan worden gebruikt.
Uitleg
Deze pagina toont een voorbeeld van een dynamisch benoemd bereik gemaakt met de INDEX-functie samen met de COUNTA-functie. Dynamische benoemde bereiken worden automatisch vergroot en verkleind wanneer gegevens worden toegevoegd of verwijderd. Ze zijn een alternatief voor het gebruik van een Excel-tabel, die ook van formaat verandert naarmate gegevens worden toegevoegd of verwijderd.
De functie INDEX retourneert de waarde op een bepaalde positie in een bereik of matrix. U kunt INDEX gebruiken om afzonderlijke waarden of hele rijen en kolommen in een bereik op te halen. Wat INDEX vooral handig maakt voor dynamische benoemde bereiken, is dat het in feite een verwijzing retourneert. Dit betekent dat u INDEX kunt gebruiken om een gemengde referentie te construeren, zoals $ A $ 1: A100.
In het getoonde voorbeeld wordt het benoemde bereik "gegevens" gedefinieerd door de volgende formule:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
wat wordt omgezet in het bereik $ A $ 2: $ A $ 10.
Hoe deze formules werken
Merk allereerst op dat deze formule is samengesteld uit twee delen die aan weerszijden van de bereikoperator (:) zitten. Aan de linkerkant hebben we de startreferentie voor het bereik, hard gecodeerd als:
$A$2
Aan de rechterkant is de eindreferentie voor het bereik, gemaakt met INDEX als volgt:
INDEX($A:$A,COUNTA($A:$A))
Hier voeren we INDEX de hele kolom A in voor de array en gebruiken vervolgens de COUNTA-functie om de "laatste rij" in het bereik te achterhalen. COUNTA werkt hier goed omdat er 10 waarden in kolom A staan, inclusief een koprij. COUNTA retourneert daarom 10, die rechtstreeks naar INDEX gaat als het rijnummer. INDEX retourneert vervolgens een verwijzing naar $ A $ 10, de laatst gebruikte rij in het bereik:
INDEX($A:$A,10) // resolves to $A$10
Het uiteindelijke resultaat van de formule is dus dit bereik:
$A$2:$A$10
Een tweedimensionaal bereik
Het bovenstaande voorbeeld werkt voor een eendimensionaal bereik. Om een tweedimensionaal dynamisch bereik te creëren waarbij het aantal kolommen ook dynamisch is, kunt u dezelfde aanpak gebruiken, als volgt uitgebreid:
=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Net als voorheen wordt COUNTA gebruikt om de "laatste rij" te achterhalen, en we gebruiken COUNTA opnieuw om de "laatste kolom" te krijgen. Deze worden aan de index geleverd als respectievelijk rij_getal en kolom_getal.
Voor de array leveren we echter het volledige werkblad, ingevoerd als alle 1048576 rijen, waardoor INDEX een verwijzing in een 2D-ruimte kan retourneren.
Opmerking: Excel 2003 ondersteunt slechts 65535 rijen.
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:
- Laatste rij in gemengde gegevens met spaties
- Laatste rij in gemengde gegevens zonder spaties
- Laatste rij met tekstgegevens
- Laatste rij in numerieke gegevens
