Přejít k hlavnímu obsahu

Jak vložit čísla nebo řádky pro chybějící pořadová čísla v aplikaci Excel?

Předpokládejme, že máte v listu seznam sekvenčních čísel, ale mezi sekvencemi jsou některá chybějící čísla a nyní musíte vložit chybějící čísla nebo prázdné řádky, abyste se ujistili, že je sekvence kompletní (zobrazeno jako následující screenshoty). Jak byste mohli rychle vyřešit tento problém v aplikaci Excel?

doc-insert-missing-number1 -2 doc-insert-missing-number2

Vložte chybějící čísla pro sekvenci pomocí funkce Třídit a Odstranit duplikáty

Vložte chybějící čísla pro sekvenci pomocí kódu VBA

Vložte prázdné řádky pro chybějící sekvenci s kódem VBA

Vložte chybějící čísla nebo prázdné řádky pro sekvenci pomocí programu Kutools pro Excel


šipka modrá pravá bublina Vložte chybějící čísla pro sekvenci pomocí funkce Třídit a Odstranit duplikáty

Možná můžete najít chybějící čísla po jednom a poté je vložit, ale je těžké identifikovat umístění chybějících čísel, pokud existují stovky po sobě jdoucích čísel. V aplikaci Excel mohu k řešení tohoto úkolu použít funkci Seřadit a odebrat duplikáty.

1. Na konci seznamu sekvencí vyplňte další čísla sekvencí od 2005023001 do 2005023011. Viz snímek obrazovky:

doc-insert-missing-number3

2. Poté vyberte rozsah dvou pořadových čísel a klikněte Data > Třídit od A do Z., viz screenshot:

doc-insert-missing-number4

3. A vybraná data byla tříděna jako následující snímek obrazovky:

doc-insert-missing-number5

4. Poté musíte duplikáty odstranit kliknutím Data > Odebrat duplikátya vyskočil Odebrat duplikáty V dialogovém okně zaškrtněte Sloupec název, který chcete odstranit duplikáty, viz screenshoty:

doc-insert-missing-number6 -2 doc-insert-missing-number7

5. Pak klikněte na tlačítko OK, duplikáty v Sloupec A byl odstraněn a byla vložena chybějící čísla v seznamu sekvencí, viz screenshot:

doc-insert-missing-number8


šipka modrá pravá bublina Vložte chybějící čísla pro sekvenci pomocí kódu VBA

Pokud máte pocit, že s výše uvedenými metodami existuje tolik kroků, zde máte také kód VBA, který vám pomůže tento problém dokončit. Postupujte prosím následovně:

1. Podržte ALT + F11 klíče a otevře se Microsoft Visual Basic pro aplikace okno.

2, klikněte Vložit > Modula vložte následující kód do Modul okno.

VBA: vložte chybějící čísla pro sekvenci

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. Pak stiskněte tlačítko F5 klíč ke spuštění tohoto kódu a zobrazí se výzva, vyberte datový rozsah, do kterého chcete vložit chybějící čísla (nevybírejte rozsah názvu), viz screenshot:

doc-insert-missing-number9

4. A pak klikněte na tlačítko OK, chybějící čísla byla vložena do seznamu sekvencí. Zobrazit snímky obrazovky:

doc-insert-missing-number1 -2 doc-insert-missing-number2

šipka modrá pravá bublina Vložte prázdné řádky pro chybějící sekvenci s kódem VBA

Někdy stačí najít místo chybějících čísel a mezi data vložit prázdné řádky, abyste mohli zadat potřebné informace. Následující kód VBA vám samozřejmě pomůže vyřešit tento problém.

1. Podržte stisknuté tlačítko ALT + F11 klíče a otevře se a Microsoft Visual Basic pro aplikace okno.

2, klikněte Vložit > Modula vložte následující kód do Modul okno.

VBA: vložte prázdné řádky pro chybějící sekvenci

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. Pak stiskněte tlačítko F5 klíč ke spuštění tohoto kódu a zobrazí se výzva a poté vyberte rozsah dat, do kterého chcete vložit prázdné řádky pro chybějící sekvenci (nevybírejte rozsah názvu), viz screenshot:

doc-insert-missing-number9

4. A pak klikněte na tlačítko OK, byly vloženy prázdné řádky pro seznam chybějících sekvencí. Zobrazit snímky obrazovky:

doc-insert-missing-number1 -2 doc-insert-missing-number10

šipka modrá pravá bublina Vložte chybějící čísla nebo prázdné řádky pro sekvenci pomocí programu Kutools pro Excel

Zde představím snadný a praktický nástroj - Kutools pro Excel, S jeho Najděte chybějící pořadové číslo Pomocí funkce můžete rychle vložit chybějící pořadové číslo nebo prázdné řádky mezi stávající datovou sekvenci.

Kutools pro Excel : s více než 300 praktickými doplňky Excel, můžete si je vyzkoušet bez omezení do 30 dnů

Pokud jste nainstalovali Kutools pro Excel, postupujte následovně:

1. Vyberte sekvenci dat, do které chcete vložit chybějící čísla.

2, klikněte Kutools > Vložit > Najděte chybějící pořadové číslo, viz screenshot:

3. V Najděte chybějící pořadové číslo v dialogovém okně zaškrtněte Vkládání chybějícího pořadového čísla vložit chybějící čísla nebo Ivložit prázdné řádky, když narazíte na chybějící pořadová čísla podle potřeby vložit prázdné řádky. Viz snímek obrazovky:

doc-insert-missing-number10

4. A pak klikněte na tlačítko OK tlačítko a do dat byla vložena chybějící pořadová čísla nebo prázdné řádky, viz screenshoty:

doc-insert-missing-number10 2 doc-insert-missing-number10 2 doc-insert-missing-number10

Stáhněte si zdarma zkušební verzi Kutools pro Excel!


šipka modrá pravá bublina  Ukázka: Vložte chybějící čísla nebo prázdné řádky pro sekvenci pomocí programu Kutools pro Excel

Kutools pro Excel: s více než 300 praktickými doplňky aplikace Excel, můžete vyzkoušet bez omezení do 30 dnů. Stáhněte si a vyzkoušejte zdarma hned teď!

Související článek:

Jak identifikovat sekvenci chybějících čísel v aplikaci Excel?

Nejlepší nástroje pro produktivitu v kanceláři

Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty   |  Odstranit prázdné řádky   |  Kombinujte sloupce nebo buňky bez ztráty dat   |   Kolo bez vzorce ...
Super vyhledávání: Více kritérií VLookup    VLookup s více hodnotami  |   VLookup na více listech   |   Fuzzy vyhledávání ....
Pokročilý rozevírací seznam: Rychle vytvořte rozevírací seznam   |  Závislý rozbalovací seznam   |  Vícenásobný výběr rozevíracího seznamu ....
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  |  Porovnejte rozsahy a sloupce ...
Doporučené funkce: Zaměření mřížky   |  Návrhové zobrazení   |   Velký Formula Bar    Správce sešitů a listů   |  Knihovna zdrojů (Automatický text)   |  Výběr data   |  Zkombinujte pracovní listy   |  Šifrovat/dešifrovat buňky    Odesílat e-maily podle seznamu   |  Super filtr   |   Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů12 Text Tools (doplnit text, Odebrat znaky, ...)   |   50+ Graf Typ nemovitosti (Ganttův diagram, ...)   |   40+ Praktické Vzorce (Vypočítejte věk na základě narozenin, ...)   |   19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty, ...)   |   12 Konverze Tools (Čísla na slova, Přepočet měny, ...)   |   7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky, ...)   |   ... a více

Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času.  Kliknutím sem získáte funkci, kterou nejvíce potřebujete...

karta kte 201905


Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci

  • Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
  • Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
  • Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
Comments (12)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I have used the code for "VBA: insert blank rows for missing sequence" as listed above and works great - but i need it to insert rows across the all columns it only adds rows to the first 2 columns of my selection - not my entire table.
This comment was minimized by the moderator on the site
Hello, Melanie,

To solve your problem, maybe the following code can help you: (Note: A indicates the column contains the missing sequence, please change it to your need.)
Sub InsertBlankRowsForMissingSequence()
    Dim i As Long
    On Error Resume Next
    Application.ScreenUpdating = False
    For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
        If IsNumeric(Cells(i, "A").Value) And IsNumeric(Cells(i - 1, "A").Value) And Cells(i, "A").Value <> "" And Cells(i - 1, "A").Value <> "" Then
            If Cells(i, "A").Value - Cells(i - 1, "A").Value > 1 Then
                Debug.Print Cells(i, "A").Value - Cells(i - 1, "A").Value - 1
                Rows(i).Resize(Cells(i, "A").Value - Cells(i - 1, "A").Value - 1).Insert
            End If
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
I am trying to use the VBA for sequential numbers. I have several columns next to the numbers of which numbers too. I.e.
1. HL Meter 34
2. HL Watermeter 40
4. HL CO2meter 24

When I use the code it works for the first 3 columns but it gets mixed up if I include the 4th column since it includes numbers too.
How can I change the code to make sure the numbers in column 4 stay the same?
This comment was minimized by the moderator on the site
Thank you amazing
This comment was minimized by the moderator on the site
What if i want to select 6 columns and then check 1st column for dates and if dates are missing add a row(blank cells) for all 6 columns
This comment was minimized by the moderator on the site
I want to use "Inserting missing sequence Number" feature but it's not supporting for digits more than 12 ? there are many sets in which I want to insert the sequence between (it's a alpha-numeric digit) can you help
This comment was minimized by the moderator on the site
Hi, I want to use "Inserting Missing Sequence Number", but it's not supporting if the no. of digits are more than 12 can you help ?
This comment was minimized by the moderator on the site
What if i want to select 6 columns and then check 1st column for dates and if dates are missing add a row(blank cells) for all 6 columns
This comment was minimized by the moderator on the site
Thank you very much. How do i change the script if the increments is only 0.02 and not 1 This is for the script InsertNullBetween()
This comment was minimized by the moderator on the site
this worked and was very easy to complete the task. Thank you.
This comment was minimized by the moderator on the site
Thanks ! Great script ! How i can modify this script if i say we need to process not only ID column + NAME column, but ID column + NAME column + NEW column ? How i can add new columns in this script?
This comment was minimized by the moderator on the site
The following is the modified macro to include an added column - Another important point is that when prompted to select the range, you should only select the first column - these took me a few hours! hope to save others' time

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")
Dim dic2 As Variant
Set dic2 = 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 3)
For Each Rng In WorkRng
dic(Rng.Value) = Rng.Offset(0, 1).Value
dic2(Rng.Value) = Rng.Offset(0, 2).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)
outArr(i + 1, 3) = dic2(i + num1)
Else
outArr(i + 1, 2) = ""
outArr(i + 1, 3) = ""

End If
Next
With WorkRng.Range("A1").Resize(UBound(outArr, 1), UBound(outArr, 2))
.Value = outArr
.Select
End With
End Sub
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations