Hoe voeg je nummers of rijen in voor ontbrekende opeenvolgende nummers in Excel?

Stel dat je een lijst met opeenvolgende nummers hebt in een werkblad, maar dat er enkele ontbrekende nummers in de reeks zitten, en nu moet je de ontbrekende nummers of lege rijen invoegen om ervoor te zorgen dat de reeks compleet is (zoals te zien is in de volgende schermafbeeldingen). Hoe kun je dit probleem snel oplossen in Excel?

Voeg ontbrekende nummers in voor de reeks met de functie Sorteren en Dubbele items verwijderen Voeg ontbrekende nummers of lege rijen in voor de reeks met Kutools voor Excel Voeg ontbrekende nummers in voor de reeks met VBA-code Voeg lege rijen in voor ontbrekende reeksen met VBA-code

Voeg ontbrekende nummers in voor de reeks met de functie Sorteren en Dubbele items verwijderen

Misschien kun je de ontbrekende nummers één voor één vinden en ze dan invoegen, maar het is moeilijk om de locatie van de ontbrekende nummers te identificeren als er honderden opeenvolgende nummers zijn. In Excel kan ik de functie Sorteren en Dubbele items verwijderen gebruiken om deze taak uit te voeren.

1. Vul na het einde van de reekslijst nog een reeks nummers in, van 2005023001 tot 2005023011. Zie screenshot:

2. Selecteer vervolgens het bereik van de twee reeksen nummers en klik op Gegevens > Sorteren A naar Z, zie screenshot:

3. En de geselecteerde gegevens zijn als volgt gesorteerd, zie screenshot:

4. Vervolgens moet je de duplicaten verwijderen door te klikken op Gegevens > Dubbele items verwijderen, en in het pop-upvenster Dubbele items verwijderen vink je de kolomnaam aan waarvan je de duplicaten wilt verwijderen, zie screenshots:

5. Klik vervolgens op OK, de duplicaten in Kolom A zijn verwijderd, en de ontbrekende nummers in de reekslijst zijn ingevoegd, zie screenshot:

Voeg ontbrekende nummers of lege rijen in voor de reeks met Kutools voor Excel

Hier introduceer ik een eenvoudig en handig hulpmiddel - Kutools voor Excel, met de functie Ontbrekende reeks vinden kun je snel de ontbrekende reeksnummers of lege rijen tussen de bestaande datasequentie invoegen.

Als je Kutools voor Excel hebt geïnstalleerd, doe dan het volgende:

1. Selecteer de datasequentie waarvoor je de ontbrekende nummers wilt invoegen.

2. Klik op Kutools > Invoegen > Ontbrekende reeks vinden, zie screenshot:

3. Vink in het dialoogvenster Ontbrekende reeks vinden aan: Ontbrekende reeks invoegen om de ontbrekende nummers in te voegen of Lege rijen invoegen bij het tegenkomen van ontbrekende reeksen om lege rijen in te voegen zoals je nodig hebt. Zie screenshot:

4. Klik vervolgens op de knop OK, en de ontbrekende reeksnummers of lege rijen zijn ingevoegd in de gegevens, zie screenshots:

Download en probeer Kutools voor Excel nu gratis uit!

Voeg ontbrekende nummers in voor de reeks met VBA-code

Als je vindt dat er te veel stappen zijn met de bovenstaande methoden, hier is ook een VBA-code die je kan helpen dit probleem op te lossen. Doe het volgende:

1. Houd de toetsen ALT + F11 ingedrukt, en het venster Microsoft Visual Basic for Applications wordt geopend.

2. Klik op Invoegen > Module, en plak de volgende code in het Module-venster.

VBA: voeg ontbrekende nummers in voor de reeks

Sub InsertValueBetween()
'Updateby Extendoffice
Dim WorkRng As Range
Dim Rng As Range
Dim outArr As Variant
Dim dic As Variant
Set dic = CreateObject("Scripting.Dictionary")
'On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
num1 = WorkRng.Range("A1").Value
num2 = WorkRng.Range("A" & WorkRng.Rows.Count).Value
interval = num2 - num1
ReDim outArr(1 To interval + 1, 1 To 2)
For Each Rng In WorkRng
    dic(Rng.Value) = Rng.Offset(0, 1).Value
Next
For i = 0 To interval
    outArr(i + 1, 1) = i + num1
    If dic.Exists(i + num1) Then
        outArr(i + 1, 2) = dic(i + num1)
    Else
        outArr(i + 1, 2) = ""
    End If
Next
With WorkRng.Range("A1").Resize(UBound(outArr, 1), UBound(outArr, 2))
    .Value = outArr
    .Select
End With
End Sub

3. Druk vervolgens op de toets F5 om deze code uit te voeren, en er verschijnt een pop-upvenster. Selecteer het gegevensbereik waarvoor je de ontbrekende nummers wilt invoegen (selecteer niet het titelbereik), zie screenshot:

4. Klik vervolgens op OK, de ontbrekende nummers zijn ingevoegd in de reekslijst. Zie screenshots:

Voeg lege rijen in voor ontbrekende reeksen met VBA-code

Soms wil je alleen de plaats van de ontbrekende nummers aangeven en lege rijen tussen de gegevens invoegen, zodat je de informatie kunt invoeren zoals je nodig hebt. Natuurlijk kan de volgende VBA-code je ook helpen dit probleem op te lossen.

1. Houd de toetsen ALT + F11 ingedrukt, en er opent zich een venster Microsoft Visual Basic for Applications.

2. Klik op Invoegen > Module, en plak de volgende code in het Module-venster.

VBA: voeg lege rijen in voor ontbrekende reeksen

Sub InsertNullBetween()
'Updateby Extendoffice
Dim WorkRng As Range
Dim Rng As Range
Dim outArr As Variant
Dim dic As Variant
Set dic = CreateObject("Scripting.Dictionary")
'On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
num1 = WorkRng.Range("A1").Value
num2 = WorkRng.Range("A" & WorkRng.Rows.Count).Value
interval = num2 - num1
ReDim outArr(1 To interval + 1, 1 To 2)
For Each Rng In WorkRng
    dic(Rng.Value) = Rng.Offset(0, 1).Value
Next
For i = 0 To interval
    If dic.Exists(i + num1) Then
        outArr(i + 1, 1) = i + num1
        outArr(i + 1, 2) = dic(i + num1)
    Else
        outArr(i + 1, 1) = ""
        outArr(i + 1, 2) = ""
    End If
Next
With WorkRng.Range("A1").Resize(UBound(outArr, 1), UBound(outArr, 2))
    .Value = outArr
    .Select
End With
End Sub

3. Druk vervolgens op de toets F5 om deze code uit te voeren, en er verschijnt een promptvenster. Selecteer vervolgens het gegevensbereik waarvoor je lege rijen wilt invoegen voor de ontbrekende reeks (selecteer niet het titelbereik), zie screenshot:

4. Klik vervolgens op OK, de lege rijen zijn ingevoegd voor de lijst met ontbrekende reeksen. Zie screenshots:

Gerelateerd artikel:

Hoe identificeer je ontbrekende nummerreeksen in Excel?