Hoe maakt u een dynamisch benoemd bereik in Excel?
Normaal gesproken zijn benoemde bereiken zeer nuttig voor Excel-gebruikers. U kunt een reeks waarden in een kolom definiëren, die kolom een naam geven en vervolgens naar dat bereik verwijzen via de naam in plaats van via celverwijzingen. Meestal moet u echter nieuwe gegevens toevoegen om het bereik van uw verwijzingswaarden in de toekomst uit te breiden. In dat geval moet u terugkeren naar Formules > Naambeheerder en het bereik opnieuw definiëren om de nieuwe waarde op te nemen. Om dit te voorkomen, kunt u een dynamisch benoemd bereik maken, zodat u de celverwijzingen niet telkens hoeft aan te passen wanneer u 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 een functie
Dynamisch benoemd bereik maken in Excel met VBA-code
Dynamisch benoemd bereik maken in Excel door een tabel te maken
Als u Excel 2007 of een nieuwere versie gebruikt, is de eenvoudigste manier om een dynamisch benoemd bereik te maken het aanmaken van een benoemde Excel-tabel.
Stel dat u een bereik met de volgende gegevens hebt dat dynamisch moet worden benoemd.
1. Definieer eerst een celnaam voor dit bereik. Selecteer het bereik A1:A6 en voer de naam Datum in het Naamvak in; druk vervolgens op de toets Enter. Definieer op dezelfde manier een naam voor het bereik B1:B6: Verkoopprijs. Maak tegelijkertijd in een lege cel de formule =SOM(Verkoopprijs); zie schermafbeelding:
2. Selecteer het bereik en klik op Invoegen > Tabel; zie de schermafbeelding:
3. In het dialoogvenster Tabel maken vinkt u Mijn tabel heeft koppen aan (vink dit uit als het bereik geen koppen bevat) en klikt u op de knop OK; de bereikgegevens zijn nu omgezet naar een tabel. Zie schermafbeeldingen:
 |  |  |
4. Wanneer u nieuwe waarden na de gegevens invoert, past het benoemde bereik zich automatisch aan en wordt de bijbehorende formule meteen bijgewerkt. Zie de volgende schermafbeeldingen:
 | |  |
Opmerkingen:
1. De nieuwe gegevens die u invoert, moeten direct aansluiten op de bestaande gegevens; er mogen dus geen lege rijen of kolommen tussen de nieuwe en bestaande gegevens zitten.
2. In de tabel kunt u gegevens tussen bestaande waarden invoegen.
Dynamisch benoemd bereik maken in Excel met een functie
In Excel 2003 of een eerdere versie is de eerste methode niet beschikbaar, dus bieden we hier een alternatief. De volgende VERSCHUIVING( )-functie kan u hierbij helpen, hoewel deze enigszins omslachtig is. Stel dat u een gegevensbereik hebt met gedefinieerde celnamen: bijvoorbeeld A1:A6, waarvan de celnaam Datum is, en B1:B6, waarvan de celnaam Verkoopprijs is. Tegelijkertijd stelt u een formule op voor de Verkoopprijs. Zie de schermafbeelding:
U kunt de Celnaam wijzigen in een dynamische Celnaam met de volgende stappen:
1. Klik op Formules > Naambeheerder; zie schermafbeelding:
2. Selecteer in het dialoogvenster Naambeheerder het item dat u wilt gebruiken en klik op de knop Bewerken.
3. Voer in het verschijnende dialoogvenster Naam Bewerken de volgende formule =VERSCHUIVING(Blad1!$A$1; 0; 0; AANTALARG($A:$A); 1) in het tekstvak Verwijst naar; zie schermafbeelding:
4. Klik vervolgens op OK en herhaal stap 2 en 3 om deze formule =VERSCHUIVING(Blad1!$B$1; 0; 0; AANTALARG($B:$B), 1) in het tekstvak Verwijst naar voor de Verkoopprijs-celnaam te plakken.
5. De dynamische benoemde bereiken zijn nu aangemaakt. Zodra u nieuwe waarden na de bestaande gegevens invoert, past het benoemde bereik zich automatisch aan — en wordt ook de bijbehorende formule direct bijgewerkt. Zie de schermafbeeldingen:
| | |
Opmerking: Als er lege cellen in het midden van uw bereik zitten, klopt het resultaat van uw formule niet. Dat komt doordat de niet-lege cellen buiten beschouwing worden gelaten, waardoor uw bereik korter wordt dan bedoeld en de laatste cellen buiten het bereik vallen.
Tip: uitleg van deze formule:
- =VERSCHUIVING(referentie,rijen,kolommen,[hoogte],[breedte])
- =VERSCHUIVING(Blad1!$A$1; 0; 0; AANTALARG($A:$A); 1)
- referentiecorrespondeert met de startcelpositie; in dit voorbeeld Blad1!$A$1;
- rij verwijst naar het aantal rijen dat u naar beneden verplaatst ten opzichte van de startcel (of naar boven bij een negatieve waarde). In dit voorbeeld geeft 0 aan dat de lijst begint bij de eerste rij onder de startcel.
- kolom correspondeert met het aantal kolommen dat u naar rechts verplaatst ten opzichte van de startcel (of naar links bij een negatieve waarde). In de bovenstaande formule geeft 0 aan dat er geen kolommen naar rechts worden uitgebreid.
- [hoogte] correspondeert met de hoogte (of het aantal rijen) van het bereik dat begint op de opgegeven positie. $A:$A telt alle ingevoerde items in kolom A.
- [breedte] correspondeert met de breedte (of het aantal kolommen) van het bereik dat begint op de opgegeven positie. In de bovenstaande formule is de lijst één kolom breed.
U kunt deze argumenten naar wens aanpassen.
Dynamisch benoemd bereik maken in Excel met VBA-code
Als u meerdere kolommen hebt, kunt u voor elke overige kolom een afzonderlijke formule herhalen en invoeren, maar dat is een lang en repetitief proces. Om het gemakkelijker te maken, kunt u code gebruiken om het dynamische benoemde bereik automatisch aan te maken.
1. Activeer uw werkblad.
2. Houd de toetsen ALT + F11 ingedrukt om het venster Microsoft Visual Basic for Applications te openen.
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 toets F5 om de code uit te voeren. Er worden dan dynamische benoemde bereiken gegenereerd, genoemd naar de waarden in de eerste rij, en ook een dynamisch bereik genaamd MijnGegevens dat alle gegevens omvat.
5. Wanneer u nieuwe waarden invoert na de rijen of kolommen, wordt het bereik automatisch uitgebreid. Zie de schermafbeeldingen:
 |
 |
 |
Opmerkingen:
1. Met deze code worden de celnamen niet weergegeven in het Naamvak. Om celnamen gemakkelijk te kunnen bekijken en gebruiken, heb ik Kutools voor Excel geïnstalleerd; met de Navigatie worden de aangemaakte dynamische celnamen weergegeven.
2. Met deze code kunt u het volledige gegevensbereik zowel verticaal als horizontaal uitbreiden, maar let op: zorg ervoor dat er geen lege rijen of kolommen tussen de gegevens zitten wanneer u nieuwe waarden invoert.
3. Wanneer u deze code gebruikt, moet uw gegevensbereik beginnen bij cel A1.
Gerelateerd artikel:
Hoe werkt automatisch bijwerken van een grafiek nadat u nieuwe gegevens in Excel hebt ingevoerd?
Beste Office-productiviteitshulpmiddelen
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie zoals nooit tevoren.Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en Tijd besparen.Klik hier om de functie te krijgen die u het meest nodig heeft...
Office Tab brengt een tabbladinterface naar Office en maakt uw werk veel eenvoudiger
- Schakel tabbladbewerking en -lezen in voor Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten aan in nieuwe tabbladen binnen hetzelfde venster, in plaats van in afzonderlijke vensters.
- Verhoogt uw productiviteit met 50 % en bespaart u dagelijks honderden muisklikken!
Alle Kutools-add-ins in één installatieprogramma.
Kutools for Office bundelt add-ins voor Excel, Word, Outlook en PowerPoint, plus Office Tab Pro—ideaal voor teams die met meerdere Office-apps werken.
- Alles-in-één suite— add-ins voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro
- Één installatieprogramma, één licentie— binnen enkele minuten klaar (MSI-geschikt)
- Werkt beter samen— gestroomlijnde productiviteit in alle Office-apps
- 30 dagen volledig functionele proefversie— geen registratie, geen creditcard
- Beste prijs-kwaliteitverhouding— bespaar ten opzichte van het afzonderlijk kopen van add-ins