Ga naar hoofdinhoud

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


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 Datum en 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?

Beste Office-productiviteitstools

🤖 Kutools AI-assistent: Een revolutie teweegbrengen in de data-analyse op basis van: Intelligente uitvoering   |  Genereer code  |  Aangepaste formules maken  |  Analyseer gegevens en genereer grafieken  |  Roep Kutools-functies aan...
Populaire functies: Zoek, markeer of identificeer duplicaten   |  Verwijder lege rijen   |  Combineer kolommen of cellen zonder gegevens te verliezen   |   Ronde zonder formule ...
Super opzoeken: Meerdere criteria VLookup    VLookup met meerdere waarden  |   VOpzoeken over meerdere bladen   |   Fuzzy opzoeken ....
Geavanceerde vervolgkeuzelijst: Maak snel een vervolgkeuzelijst   |  Afhankelijke vervolgkeuzelijst   |  Multi-select vervolgkeuzelijst ....
Kolom Beheerder: Voeg een specifiek aantal kolommen toe  |  Kolommen verplaatsen  |  Schakel de zichtbaarheidsstatus van verborgen kolommen in  |  Vergelijk bereiken en kolommen ...
Uitgelichte functies: Raster focus   |  Ontwerpweergave   |   Grote formulebalk    Werkmap- en bladbeheer   |  resource Library (Auto-tekst)   |  Datumkiezer   |  Combineer werkbladen   |  Cellen coderen/decoderen    Stuur e-mails per lijst   |  Super filter   |   Speciaal filter (filter vet/cursief/doorhalen...) ...
Top 15 gereedschapsets12 Tekst Tools (toe te voegen tekst, Tekens verwijderen, ...)   |   50+ tabel Types (Gantt Chart, ...)   |   40+ Praktisch Formules (Bereken leeftijd op basis van verjaardag, ...)   |   19 Invoeging Tools (QR-code invoegen, Afbeelding invoegen vanaf pad, ...)   |   12 Camper ombouw Tools (Getallen naar woorden, Currency Conversion, ...)   |   7 Samenvoegen en splitsen Tools (Geavanceerd Combineer rijen, Gespleten cellen, ...)   |   ... en meer

Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie als nooit tevoren. Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en tijd te besparen.  Klik hier om de functie te krijgen die u het meest nodig heeft...

Omschrijving


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 honderden muisklikken voor u elke dag!
Comments (4)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
really, really not helpful
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Thanks for good article
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations