Hoe maak je een dynamisch benoemd bereik in Excel?

Normaal gesproken zijn Benoemde Bereiken erg handig voor Excel-gebruikers. Je kunt een reeks waarden in een kolom definiëren, die kolom een naam geven en vervolgens naar dat bereik verwijzen met de naam in plaats van met celverwijzingen. Maar meestal moet je in de toekomst nieuwe gegevens toevoegen om de waarden van het bereik uit te breiden. In dit geval moet je terugkeren naar Formules > Namenbeheer en het bereik opnieuw definiëren om de nieuwe waarde op te nemen. Om dit te voorkomen, kun je een dynamisch benoemd bereik maken, wat betekent dat je niet elke keer celverwijzingen hoeft aan te passen wanneer je een nieuwe rij of kolom aan de lijst toevoegt.

Dynamisch benoemd bereik maken in Excel door een tabel te maken

Dynamisch benoemd bereik maken in Excel met Functie

Dynamisch benoemd bereik maken in Excel met VBA-code

Dynamisch benoemd bereik maken in Excel door een tabel te maken

Als je Excel 2007 of latere versies gebruikt, is de gemakkelijkste manier om een dynamisch benoemd bereik te maken door een benoemde Excel-tabel te maken.

Stel dat je een bereik hebt met de volgende gegevens die dynamisch moeten worden gemaakt.

1. Allereerst zal ik bereiknamen definiëren voor dit bereik. Selecteer het bereik A1:A6 en voer de naam Datum in het Naamvak in, druk vervolgens op de Enter-toets. Definieer op dezelfde manier een naam voor het bereik B1:B6 als Verkoopprijs. Tegelijkertijd maak ik een formule =som(Verkoopprijs) in een lege cel, zie screenshot:

2. Selecteer het bereik en klik op Invoegen > Tabel, zie screenshot:

3. In het dialoogvenster Tabel maken, vink My table has headers aan (als het bereik geen kopteksten heeft, vink het dan uit), klik op de OK-knop, en de bereikgegevens zijn omgezet in een tabel. Zie screenshots:

4. En wanneer je nieuwe waarden invoert na de gegevens, zal het benoemde bereik automatisch aanpassen en zal de gemaakte formule ook veranderen. Zie onderstaande screenshots:

Opmerkingen:

1. Je nieuwe ingevoerde gegevens moeten aangrenzend zijn aan de bovenliggende gegevens, wat betekent dat er geen lege rijen of kolommen mogen zijn tussen de nieuwe gegevens en de bestaande gegevens.

2. In de tabel kun je gegevens tussen de bestaande waarden invoegen.

Dynamisch benoemd bereik maken in Excel met Functie

In Excel 2003 of eerdere versies zal de eerste methode niet beschikbaar zijn, dus hier is een andere manier voor jou. De volgende OFFSET( ) functie kan dit voor je doen, maar het is enigszins lastig. Stel dat ik een bereik heb met gegevens dat de bereiknamen bevat die ik heb gedefinieerd, bijvoorbeeld A1:A6 heeft de bereiknaam Datum, en B1:B6 heeft de bereiknaam Verkoopprijs, tegelijkertijd maak ik een formule voor de Verkoopprijs. Zie screenshot:

Je kunt de bereiknamen wijzigen in dynamische bereiknamen met de volgende stappen:

1. Ga naar Formules > Namenbeheer, zie screenshot:

2. Selecteer in het dialoogvenster Namenbeheer het item dat je wilt gebruiken en klik op de Bewerk-knop.

3. Voer in het pop-up venster Bewerk naam deze formule in =VERSCHUIVING(Blad1!$A$1, 0, 0, AANTALARG($A:$A), 1) in het tekstvak Verwijst naar, zie screenshot:

4. Klik vervolgens op OK, en herhaal stap 2 en stap 3 om deze formule =VERSCHUIVING(Blad1!$B$1, 0, 0, AANTALARG($B:$B), 1) in het tekstvak Verwijst naar te kopiëren voor de bereiknaam Verkoopprijs.

5. En het dynamisch benoemde bereik is gemaakt. Wanneer je nieuwe waarden invoert na de gegevens, zal het benoemde bereik automatisch aanpassen en zal de gemaakte formule ook veranderen. Zie screenshots:

Opmerking: Als er lege cellen in het midden van je bereik zitten, zal het resultaat van je formule foutief zijn. Dat komt omdat de niet-lege cellen niet worden geteld, dus je bereik zal korter zijn dan het zou moeten zijn en de laatste cellen in het bereik zullen worden weggelaten.

Tip: uitleg voor deze formule:

• =VERSCHUIVING(referentie,rijen,kolommen,[hoogte],[breedte])
• 
• =VERSCHUIVING(Blad1!$A$1, 0, 0, AANTALARG($A:$A), 1)

• referentie correspondeert met de startpositie van de cel, in dit voorbeeld Blad1!$A$1;
• rij verwijst naar het aantal rijen dat je naar beneden gaat bewegen, relatief ten opzichte van de startcel (of naar boven, als je een negatieve waarde gebruikt.), in dit voorbeeld, 0 geeft aan dat de lijst vanaf de eerste rij begint.
• kolom correspondeert met het aantal kolommen dat je naar rechts gaat bewegen, relatief ten opzichte van de startcel (of naar links, met een negatieve waarde.), in de bovenstaande voorbeeldformule, 0 geeft aan dat het 0 kolommen naar rechts wordt uitgebreid.
• [hoogte] correspondeert met de hoogte (of het aantal rijen) van het bereik dat begint bij de aangepaste positie. $A:$A, het telt alle items die in kolom A zijn ingevoerd.
• [breedte] correspondeert met de breedte (of het aantal kolommen) van het bereik dat begint bij de aangepaste positie. In de bovenstaande formule zal de lijst 1 kolom breed zijn.

Je kunt deze argumenten naar behoefte wijzigen.

Dynamisch benoemd bereik maken in Excel met VBA-code

Als je meerdere kolommen hebt, zou je individuele formules voor alle overige kolommen kunnen herhalen en invoeren, maar dat zou een langdurig, repetitief proces zijn. Om het gemakkelijker te maken, kun je een code gebruiken om het dynamisch benoemde bereik automatisch te maken.

1. Activeer je werkblad.

2. Houd de toetsen ALT + F11 ingedrukt, en het venster Microsoft Visual Basic for Applications wordt geopend.

3. Klik op Invoegen > Module, en plak de volgende code in het Modulevenster.

Vba-code: dynamisch benoemd bereik maken

Sub CreateNamesxx()
'Update 20131128
Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long
Dim myName As String, Start As String
Const Rowno = 1
Const Colno = 1
Const Offset = 1
On Error Resume Next
Set wb = ActiveWorkbook
Set ws = ActiveSheet
lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
Start = Cells(Rowno, Colno).Address
wb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno & ")"
wb.Names.Add Name:="myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"
For i = Colno To lcol
    myName = Replace(Cells(Rowno, i).Value, " ", "_")
    If myName <> "" Then
        wb.Names.Add Name:=myName, RefersToR1C1:="=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
    End If
Next
End Sub

4. Druk vervolgens op de F5-toets om de code uit te voeren, en er zullen enkele dynamisch benoemde bereiken worden gegenereerd die zijn vernoemd naar de waarden in de eerste rij en het maakt ook een dynamisch bereik genaamd MyData dat de hele dataset omvat.

5. Wanneer je nieuwe waarden invoert na de rijen of kolommen, zal het bereik ook worden uitgebreid. Zie screenshots:

Opmerkingen:

1. Met deze code worden de bereiknamen niet weergegeven in het Naamvak. Om de bereiknamen gemakkelijk te bekijken en te gebruiken, heb ik Kutools voor Excel geïnstalleerd. Met het Navigatiepaneel worden de gemaakte dynamische bereiknamen weergegeven.

2. Met deze code kan het hele bereik van de gegevens verticaal of horizontaal worden uitgebreid, maar onthoud dat er geen lege rijen of kolommen tussen de gegevens mogen zitten wanneer je nieuwe waarden invoert.

3. Wanneer je deze code gebruikt, moet je gegevensbereik beginnen bij cel A1.

Gerelateerd artikel:

Hoe automatisch een grafiek bijwerken na het invoeren van nieuwe gegevens in Excel?