Salt la conținutul principal

Cum se introduc numere sau rânduri pentru numerele secvențiale lipsă în Excel?

Presupunând că aveți o listă de numere secvențiale într-o foaie de lucru, dar există câteva numere care lipsesc între secvență și acum trebuie să inserați numerele lipsă sau rândurile goale pentru a vă asigura că secvența este completă (prezentată ca următoarele capturi de ecran). Cum ați putea rezolva rapid această problemă în Excel?

doc-insert-lipsing-number1 -2 doc-insert-lipsing-number2

Introduceți numerele lipsă pentru secvență cu funcția Sortare și eliminare duplicate

Introduceți numerele lipsă pentru secvență cu codul VBA

Introduceți rânduri goale pentru secvența lipsă cu codul VBA

Introduceți numerele lipsă sau rândurile goale pentru secvență cu Kutools pentru Excel


săgeată albastru dreapta balon Introduceți numerele lipsă pentru secvență cu funcția Sortare și eliminare duplicate

Poate că puteți găsi numerele care lipsesc unul câte unul și apoi le puteți insera, dar este dificil pentru dvs. să identificați locația celor care lipsesc dacă există sute de numere secvențiale. În Excel, pot folosi funcția Sortare și eliminare duplicate pentru a face față acestei sarcini.

1. După sfârșitul listei de secvențe, completați alte numere de secvență de la 2005023001 la 2005023011. Vedeți captura de ecran:

doc-insert-lipsing-number3

2. Apoi selectați intervalul celor două numere de ordine și faceți clic Date > Sortați de la A la Z, vezi captura de ecran:

doc-insert-lipsing-number4

3. Și datele selectate au fost sortate după cum urmează:

doc-insert-lipsing-number5

4. Apoi, trebuie să eliminați duplicatele făcând clic Date > Eliminați duplicatele, și în pop-out Eliminați duplicatele caseta de dialog, verificați Coloană numele pe care doriți să eliminați duplicatele, consultați capturile de ecran:

doc-insert-lipsing-number6 -2 doc-insert-lipsing-number7

5. Apoi apasa OK, duplicatele din Coloana A a fost șters, iar numerele lipsă din lista de secvențe au fost inserate, a se vedea captura de ecran:

doc-insert-lipsing-number8


săgeată albastru dreapta balon Introduceți numerele lipsă pentru secvență cu codul VBA

Dacă credeți că există atât de mulți pași cu metodele de mai sus, aici aveți și cod VBA care vă poate ajuta să terminați această problemă. Vă rugăm să faceți următoarele:

1. Țineți apăsat butonul ALT + F11 tastele și deschide fișierul Microsoft Visual Basic pentru aplicații fereastră.

2. Clic Insera > Moduleși lipiți următorul cod în Module fereastră.

VBA: introduceți numerele lipsă pentru secvență

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. Apoi apăsați F5 tasta pentru a rula acest cod și se va afișa o casetă de solicitare, vă rugăm să selectați intervalul de date pe care doriți să introduceți numerele lipsă (nu selectați intervalul de titlu), consultați captura de ecran:

doc-insert-lipsing-number9

4. Apoi faceți clic pe OK, numerele lipsă au fost inserate în lista de secvențe. Vedeți capturi de ecran:

doc-insert-lipsing-number1 -2 doc-insert-lipsing-number2

săgeată albastru dreapta balon Introduceți rânduri goale pentru secvența lipsă cu codul VBA

Uneori, trebuie doar să localizați locul numerelor lipsă și să introduceți rânduri goale între date, astfel încât să puteți introduce informațiile după cum aveți nevoie. Desigur, și următorul cod VBA vă poate ajuta să rezolvați această problemă.

1. Țineți apăsată tasta ALT + F11 tastele și deschide un Microsoft Visual Basic pentru aplicații fereastră.

2. Clic Insera > Moduleși lipiți următorul cod în Module fereastră.

VBA: introduceți rânduri goale pentru secvența lipsă

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. Apoi apăsați F5 tasta pentru a rula acest cod și se va afișa o casetă de solicitare, apoi selectați intervalul de date pe care doriți să îl inserați rânduri goale pentru secvența lipsă (nu selectați intervalul de titlu), vedeți captura de ecran:

doc-insert-lipsing-number9

4. Apoi faceți clic pe OK, rândurile goale au fost inserate pentru lista de secvențe lipsă. Vedeți capturi de ecran:

doc-insert-lipsing-number1 -2 doc-insert-lipsing-number10

săgeată albastru dreapta balon Introduceți numerele lipsă sau rândurile goale pentru secvență cu Kutools pentru Excel

Aici, voi introduce un instrument ușor și la îndemână- Kutools pentru Excel, Cu său Găsiți numărul de secvență lipsă caracteristică, puteți insera rapid numărul de secvență lipsă sau rândurile goale între secvența de date existentă.

Kutools pentru Excel : cu mai mult de 300 de programe de completare Excel la îndemână, gratuit pentru a încerca fără limitări în 30 de zile

Dacă ați instalat Kutools pentru Excel, vă rugăm să faceți următoarele:

1. Selectați secvența de date pe care doriți să o inserați numerele lipsă.

2. Clic Kutools > Insera > Găsiți numărul de secvență lipsă, vezi captura de ecran:

3. În Găsiți numărul de secvență lipsă casetă de dialog, bifați Se introduce numărul secvenței lipsă pentru a insera numerele lipsă sau eunsertarea rândurilor goale atunci când întâlniți numerele de secvență lipsă pentru a insera rânduri goale după cum aveți nevoie. Vedeți captura de ecran:

doc-insert-lipsing-number10

4. Apoi faceți clic pe OK , iar numerele secvenței lipsă sau rândurile goale au fost inserate în date, consultați capturile de ecran:

doc-insert-lipsing-number10 2 doc-insert-lipsing-number10 2 doc-insert-lipsing-number10

Descărcați și proba gratuită Kutools pentru Excel acum!


săgeată albastru dreapta balon  Demo: introduceți numerele lipsă sau rândurile goale pentru secvență cu Kutools pentru Excel

Kutools pentru Excel: cu mai mult de 300 de programe de completare Excel la îndemână, încercați fără limitări în 30 de zile. Descărcați și proba gratuită acum!

Articol asociat:

Cum se identifică secvența numerelor lipsă în Excel?

Cele mai bune instrumente de productivitate de birou

Caracteristici populare: Găsiți, evidențiați sau identificați duplicatele   |  Ștergeți rândurile goale   |  Combinați coloane sau celule fără a pierde date   |   Rundă fără Formula ...
Super căutare: VLookup cu mai multe criterii    VLookup cu valori multiple  |   VLookup pe mai multe foi   |   Căutare fuzzy ....
Listă derulantă avansată: Creați rapid o listă derulantă   |  Listă drop-down dependentă   |  Listă derulantă cu selectare multiplă ....
Manager de coloane: Adăugați un număr specific de coloane  |  Mutați coloanele  |  Comutați starea vizibilității coloanelor ascunse  |  Comparați intervale și coloane ...
Caracteristici prezentate: Focus pe grilă   |  Vedere de proiectare   |   Big Formula Bar    Manager registru de lucru și foi   |  Biblioteca de resurse (Text automat)   |  Data Picker   |  Combinați foi de lucru   |  Criptare/Decriptare celule    Trimiteți e-mailuri după listă   |  Super Filtru   |   Filtru special (filtrează bold/italic/barat...) ...
Top 15 seturi de instrumente12 Text Instrumente (Adăuga text, Eliminați caractere,...)   |   50+ Diagramă Tipuri de (Gantt Chart,...)   |   40+ Practic Formule (Calculați vârsta pe baza zilei de naștere,...)   |   19 inserare Instrumente (Introduceți codul QR, Inserați imaginea din cale,...)   |   12 Convertire Instrumente (Numere la cuvinte, conversie valutara,...)   |   7 Merge & Split Instrumente (Rânduri combinate avansate, Celule divizate,...)   |   ... și altele

Îmbunătățiți-vă abilitățile Excel cu Kutools pentru Excel și experimentați eficiența ca niciodată. Kutools pentru Excel oferă peste 300 de funcții avansate pentru a crește productivitatea și a economisi timp.  Faceți clic aici pentru a obține funcția de care aveți cea mai mare nevoie...

fila kte 201905


Fila Office aduce interfața cu file în Office și vă face munca mult mai ușoară

  • Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
  • Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ​​ferestrele noi.
  • Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse pentru dvs. în fiecare zi!
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