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

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

Verbazingwekkend! Efficiënte tabbladen gebruiken in Excel zoals Chrome, Firefox en Safari!

Bespaar 50% van uw tijd en verminder elke dag duizenden muisklikken!

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.

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:

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

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:

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:

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.

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:

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

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

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

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:

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:

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])
• 
• = 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.

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:

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.

Lees verder... Gratis download... Aankoop... 

---

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!

Lees verder... Gratis download... Aankoop...