Hoe cijfers of rijen invoegen voor ontbrekende opeenvolgende getallen in Excel?
Stel dat je een lijst met opeenvolgende nummers in een werkblad hebt, maar er zijn enkele ontbrekende nummers in de reeks, en nu moet je de ontbrekende cijfers of lege rijen invoegen om ervoor te zorgen dat de reeks compleet is (weergegeven als de volgende schermafbeeldingen). Hoe kon je dit probleem snel oplossen in Excel?
Voeg ontbrekende nummers in voor volgorde met de functie Sorteren en verwijderen van duplicaten
Het kan zijn dat u de ontbrekende nummers een voor een kunt vinden en ze vervolgens kunt invoegen, maar het is moeilijk voor u om de locatie van de ontbrekende nummers te identificeren als er honderden opeenvolgende nummers zijn. In Excel kan ik de functie Sorteren en verwijderen van duplicaten gebruiken om deze taak uit te voeren.
1. Vul na het einde van de reekslijst nog een reeksnummers in van 2005023001 tot 2005023011. Zie screenshot:
2. Selecteer vervolgens het bereik van de twee volgnummers en klik op Data > Sorteer A tot Z, zie screenshot:
3. En de geselecteerde gegevens zijn gesorteerd als volgende screenshot:
4. Vervolgens moet u de duplicaten verwijderen door erop te klikken Data > Verwijder duplicaten, en in de pop eruit Verwijder duplicaten dialoogvenster, controleer de Kolom naam waarvan je de duplicaten wilt verwijderen, zie screenshots:
 |
 |
 |
5. Dan klikken OK, de duplicaten in Kolom A is verwijderd en de ontbrekende nummers in de sequentielijst zijn ingevoegd, zie screenshot:
Voeg ontbrekende nummers in voor reeks met VBA-code
Als u denkt dat er zoveel stappen zijn met de bovenstaande methoden, kunt u hier ook VBA-code gebruiken om dit probleem op te lossen. Ga als volgt te werk:
1. Houd de ALT + F11 toetsen, en het opent de Microsoft Visual Basic voor toepassingen venster.
2. Klikken Invoegen > Moduleen 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 F5 toets om deze code uit te voeren, en er verschijnt een promptvenster, selecteer het gegevensbereik waarin u de ontbrekende nummers wilt invoegen (selecteer niet het titelbereik), zie screenshot:
4. En klik vervolgens op OKzijn de ontbrekende nummers toegevoegd aan de sequentielijst. Zie screenshots:
 |
|
 |
Voeg lege rijen in voor ontbrekende reeks met VBA-code
Soms hoeft u alleen maar de plaats van de ontbrekende nummers te lokaliseren en lege rijen tussen de gegevens in te voegen, zodat u de informatie naar behoefte kunt invoeren. Natuurlijk kan de volgende VBA-code u ook helpen dit probleem op te lossen.
1. Houd de toets ingedrukt ALT + F11 toetsen, en het opent een Microsoft Visual Basic voor toepassingen venster.
2. Klikken Invoegen > Moduleen plak de volgende code in het Module venster.
VBA: voeg lege rijen in voor ontbrekende reeks
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 F5 toets om deze code uit te voeren, en er verschijnt een promptvenster en selecteer vervolgens het gegevensbereik dat u lege rijen wilt invoegen voor de ontbrekende reeks (selecteer niet het titelbereik), zie screenshot:
4. En klik vervolgens op OK, zijn de lege rijen ingevoegd voor de lijst met ontbrekende sequenties. Zie screenshots:
|
|
 |
Voeg ontbrekende nummers of lege rijen in voor volgorde met Kutools voor Excel
Hier zal ik een eenvoudige en handige tool introduceren- Kutools for Excel, Met Zoek het ontbrekende volgnummer functie, kunt u snel het ontbrekende volgnummer of lege rijen tussen de bestaande gegevensreeks invoegen.
| Kutools for Excel : met meer dan 300 handige Excel-invoegtoepassingen, gratis te proberen zonder beperking in 30 dagen. |
Als je hebt geïnstalleerd Kutools for Excelgaat u als volgt te werk:
1. Selecteer de gegevensreeks waarin u de ontbrekende nummers wilt invoegen.
2. Klikken Kutools > Invoegen > Zoek het ontbrekende volgnummer, zie screenshot:
3. In de Zoek het ontbrekende volgnummer dialoogvenster, vink aan Ontbrekend volgnummer invoegen om de ontbrekende nummers in te voegen of Ilege rijen invoegen bij het tegenkomen van ontbrekende volgnummers om naar behoefte lege rijen in te voegen. Zie screenshot:
4. En klik vervolgens op OK knop, en de ontbrekende volgnummers of lege rijen zijn in de gegevens ingevoegd, zie screenshots:
 |
|
 |
|
 |
Download en gratis proef Kutools voor Excel nu!
Demo: voeg ontbrekende nummers of lege rijen in voor een reeks met Kutools voor Excel
Gerelateerd artikel:
Hoe ontbrekende getallenreeks in Excel te identificeren?
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!
