Note: The other languages of the website are Google-translated. Back to English
Inloggen  \/ 
x
or
x
Registreer  \/ 
x

or

Hoe maak je een dynamisch benoemd bereik in Excel?

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

Maak een dynamisch benoemd bereik in Excel door een tabel te maken

Maak een dynamisch benoemd bereik in Excel met Function

Maak een dynamisch benoemd bereik in Excel met VBA-code

Tabblad Office Bewerking en browsen met tabbladen in Office inschakelen en uw werk veel gemakkelijker maken ...
Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%
  • Hergebruik alles: Voeg de meest gebruikte of complexe formules, grafieken en al het andere toe aan uw favorieten en gebruik ze in de toekomst snel opnieuw.
  • Meer dan 20 tekstfuncties: Nummer uit tekststring halen; Extract of verwijder een deel van teksten; Converteer cijfers en valuta's naar Engelse woorden.
  • Tools samenvoegen: Meerdere werkmappen en bladen in één; Meerdere cellen / rijen / kolommen samenvoegen zonder gegevens te verliezen; Voeg dubbele rijen en som samen.
  • Hulpmiddelen splitsen: Gegevens splitsen in meerdere bladen op basis van waarde; Eén werkmap naar meerdere Excel-, PDF- of CSV-bestanden; Eén kolom naar meerdere kolommen.
  • Plakken overslaan Verborgen / gefilterde rijen; Tel en som op achtergrondkleur; Stuur gepersonaliseerde e-mails in bulk naar meerdere ontvangers.
  • Superfilter: Maak geavanceerde filterschema's en pas deze toe op elk blad Soort per week, dag, frequentie en meer; Filteren door vetgedrukt, formules, commentaar ...
  • Meer dan 300 krachtige functies; Werkt met Office 2007-2019 en 365; Ondersteunt alle talen; Eenvoudig te implementeren in uw onderneming of organisatie.

pijl blauw rechts bel Maak een dynamisch benoemd bereik in Excel door een tabel te maken


Als u Excel 2007 of latere versies gebruikt, is de eenvoudigste manier om een ​​dynamisch benoemd bereik te maken, een Excel-tabel met de naam te maken.

Laten we zeggen dat u een reeks van volgende gegevens heeft die een dynamisch benoemde reeks moeten worden.

doc-dynamisch bereik1

1. Ten eerste zal ik bereiknamen voor dit bereik definiëren. Selecteer het bereik A1: A6 en voer de naam in Datum in de Naam Box, Druk dan op Enter sleutel. Om een ​​naam voor bereik B1 te definiëren: B6 als Saleprijs op dezelfde manier. Tegelijkertijd maak ik een formule = som (Saleprijs) zie screenshot in een lege cel:

doc-dynamisch bereik2

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

doc-dynamisch bereik3

3. In de Tabel maken promptvenster, vink aan Mijn tabel heeft kopteksten (als het bereik geen kopteksten heeft, schakel het dan uit), klik op OK knop, en de bereikgegevens zijn geconverteerd naar een tabel. Zie screenshots:

doc-dynamisch bereik4 -2 doc-dynamisch bereik5

4. En wanneer u nieuwe waarden invoert na de gegevens, wordt het benoemde bereik automatisch aangepast en wordt de gemaakte formule ook gewijzigd. Zie volgende screenshots:

doc-dynamisch bereik6 -2 doc-dynamisch bereik7

Opmerkingen:

1. Uw nieuwe ingevoerde gegevens moeten naast de bovenstaande gegevens staan, dit betekent dat er geen lege rijen of kolommen zijn tussen de nieuwe gegevens en de bestaande gegevens.

2. In de tabel kunt u gegevens invoegen tussen de bestaande waarden.


pijl blauw rechts bel Maak een dynamisch benoemd bereik in Excel met Function

In Excel 2003 of eerdere versie is de eerste methode niet beschikbaar, dus hier is een andere manier voor u. Het volgende OFFSET () functie kan dit plezier voor u doen, maar het is enigszins lastig. Stel dat ik een gegevensbereik heb dat de bereiknamen bevat die ik heb gedefinieerd, bijvoorbeeld A1: A6 de bereiknaam is Datumen B1: B6 bereiknaam is Verkoopprijs, tegelijkertijd maak ik een formule voor de Verkoopprijs. Zie screenshot:

doc-dynamisch bereik2

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

1. Ga om te klikken Formules > Name Manager, zie screenshot:

doc-dynamisch bereik8

2. In de Name Manager dialoogvenster, selecteer het item dat u wilt gebruiken en klik op Edit knop.

doc-dynamisch bereik9

3. In de pop-out Bewerk naam dialoogvenster, voer deze formule in = VERSCHUIVING (Blad1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1) in de Verwijst naar tekstvak, zie screenshot:

doc-dynamisch bereik10

4. Dan klikken OK, en herhaal vervolgens step2 en step3 om deze formule te kopiëren = VERSCHUIVING (Blad1! $ B $ 1, 0, 0, COUNTA ($ B: $ B), 1) in de Verwijst naar tekstvak voor de Verkoopprijs bereiknaam.

5. En de dynamische benoemde bereiken zijn gemaakt. Wanneer u nieuwe waarden invoert na de gegevens, wordt het benoemde bereik automatisch aangepast en wordt de gemaakte formule ook gewijzigd. Zie screenshots:

doc-dynamisch bereik6 -2 doc-dynamisch bereik7

Opmerking: Als er in het midden van uw bereik lege cellen staan, is het resultaat van uw formule verkeerd. Dat komt omdat de niet-lege cellen niet worden geteld, dus uw bereik zal korter zijn dan zou moeten, en de laatste cellen in het bereik blijven weg.

Tip: uitleg voor deze formule:

  • = OFFSET (referentie, rijen, kolommen, [hoogte], [breedte])
  • -1
  • = VERSCHUIVING (Blad1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1)
  • referentie komt in dit voorbeeld overeen met de startcelpositie Blad1! $ A $ 1;
  • rij verwijst naar het aantal rijen dat u naar beneden gaat verplaatsen ten opzichte van de startcel (of naar boven als u een negatieve waarde gebruikt.). In dit voorbeeld geeft 0 aan dat de lijst begint vanaf de eerste rij naar beneden
  • kolom komt overeen met het aantal kolommen dat u naar rechts verplaatst ten opzichte van de startcel (of naar links, met een negatieve waarde). In de bovenstaande voorbeeldformule geeft 0 aan dat 0 kolommen naar rechts uitvouwen.
  • [hoogte] komt overeen met de hoogte (of aantal rijen) van het bereik vanaf de aangepaste positie. $ A: $ A, het telt alle items die zijn ingevoerd in kolom A.
  • [breedte] komt overeen met de breedte (of het aantal kolommen) van het bereik dat begint bij de aangepaste positie. In de bovenstaande formule is de lijst 1 kolom breed.

U kunt deze argumenten naar wens aanpassen.


pijl blauw rechts bel Maak een dynamisch benoemd bereik in Excel met VBA-code

Als u meerdere kolommen heeft, kunt u de afzonderlijke formule herhalen en invoeren voor alle overige kolommen, maar dat zou een lang, repetitief proces zijn. Om dingen gemakkelijker te maken, kunt u een code gebruiken om automatisch het dynamische benoemde bereik te maken.

1. Activeer je werkblad.

2. Houd de ALT + F11 toetsen, en het opent de Microsoft Visual Basic for Applications-venster.

3. Klikken Invoegen > moduleen plak de volgende code in het Module Venster.

Vba-code: maak een dynamisch benoemd bereik

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 F5 sleutel om de code uit te voeren, en er zullen enkele dynamische benoemde bereiken worden gegenereerd die worden genoemd met de eerste rijwaarden en het creëert ook een dynamisch bereik met de naam Mijn data die alle gegevens omvat.

5. Als u nieuwe waarden invoert na de rijen of kolommen, wordt het bereik ook uitgebreid. Zie screenshots:

doc-dynamisch bereik12
-1
doc-dynamisch bereik13

Opmerkingen:

1. Met deze code worden de bereiknamen niet weergegeven in de Naam Box, om de bereiknamen gemakkelijk te kunnen bekijken en gebruiken, heb ik Kutools for Excel, Met Navigatiedeelvenster, worden de gemaakte dynamische bereiknamen weergegeven.

2. Met deze code kan het hele bereik van de gegevens verticaal of horizontaal worden uitgebreid, maar vergeet niet dat er geen lege rijen of kolommen tussen de gegevens mogen staan ​​als u nieuwe waarden invoert.

3. Als u deze code gebruikt, moet uw gegevensbereik beginnen bij cel A1.


Gerelateerd artikel:

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


De beste tools voor kantoorproductiviteit

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

  • visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
  • Voeg cellen / rijen / kolommen samen zonder gegevens te verliezen; Gespleten cellen inhoud; Combineer dubbele rijen / kolommen... Voorkom dubbele cellen; Vergelijk Ranges...
  • Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
  • Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
  • Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Gratis proefperiode van 30 dagen met volledige functies. 60 dagen geld-terug-garantie.
kte tabblad 201905

Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en vermindert elke dag honderden muisklikken!
officetab onderkant
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    mozart777 · 1 years ago
    really, really not helpful
  • To post as a guest, your comment is unpublished.
    loyiso · 5 years ago
    please help i am trying to create a dynamic named range on excel 2016 typing "=offset(DATAENTRY!$B$6,,,counta(DATAENTRY!$B$6:$B$13))" but still it gives me an error saying it is not a formula.
  • To post as a guest, your comment is unpublished.
    marge · 6 years ago
    You are a very good teacher: 1) step-by-step approach; 2) you do not bore the student with obvious material or conclusions; 3) yet you include all necessary material. I look forward to more tutorials from you.
  • To post as a guest, your comment is unpublished.
    Iran · 7 years ago
    Thanks for good article