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
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 Datum en 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?
Beste Office-productiviteitstools
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...
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!