Skip i'r prif gynnwys

Sut i fewnosod rhifau neu resi ar gyfer rhifau dilyniannol coll yn Excel?

Gan dybio bod gennych chi restr o rifau dilyniannol mewn taflen waith, ond mae yna rai rhifau coll ymhlith y dilyniant, ac nawr mae angen i chi fewnosod y rhifau coll neu'r rhesi gwag i sicrhau bod y dilyniant yn gyflawn (a ddangosir fel y sgrinluniau canlynol). Sut allech chi ddatrys y broblem hon yn Excel yn gyflym?

doc-insert-coll-rhif1 -2 doc-insert-coll-rhif2

Mewnosodwch rifau coll ar gyfer dilyniant gyda'r nodwedd Trefnu a Dileu Dyblygu

Mewnosod rhifau coll ar gyfer dilyniant gyda chod VBA

Mewnosod rhesi gwag ar gyfer dilyniant coll gyda chod VBA

Mewnosodwch rifau coll neu resi gwag ar gyfer dilyniant gyda Kutools ar gyfer Excel

swigen dde glas saeth Mewnosodwch rifau coll ar gyfer dilyniant gyda'r nodwedd Trefnu a Dileu Dyblygu

Efallai y gallwch ddod o hyd i'r rhifau coll fesul un, ac yna eu mewnosod, ond mae'n anodd i chi nodi lleoliad y rhai sydd ar goll os oes cannoedd o rifau dilyniannol. Yn Excel, gallaf ddefnyddio'r nodwedd Trefnu a Dileu Dyblygu i ddelio â'r dasg hon.

1. Yn dilyn diwedd y rhestr ddilyniannau, llenwch rifau dilyniant arall o 2005023001 i 2005023011. Gweler y screenshot:

doc-insert-coll-rhif3

2. Yna dewiswch ystod y ddau rif dilyniant a chlicio Dyddiad > Trefnu A i Z., gweler y screenshot:

doc-insert-coll-rhif4

3. Ac mae'r data a ddewiswyd wedi'i ddidoli fel y screenshot canlynol:

doc-insert-coll-rhif5

4. Yna mae angen i chi gael gwared ar y dyblygu trwy glicio Dyddiad > Tynnwch y Dyblygion, ac yn y popped allan Tynnwch y Dyblygion blwch deialog, gwiriwch y Colofn enwwch eich bod am gael gwared ar y dyblygu, gweler sgrinluniau:

doc-insert-coll-rhif6 -2 doc-insert-coll-rhif7

5. Yna cliciwch OK, y dyblygu yn Colofn A. wedi'i ddileu, ac mae'r rhifau coll yn y rhestr dilyniant wedi'u mewnosod, gweler y screenshot:

doc-insert-coll-rhif8

swigen dde glas saeth Mewnosod rhifau coll ar gyfer dilyniant gyda chod VBA

Os ydych chi'n teimlo bod cymaint o gamau gyda'r dulliau uchod, yma hefyd mae cod VBA yn gallu'ch helpu chi i orffen y broblem hon. Gwnewch fel a ganlyn:

1. Daliwch i lawr y ALT + F11 allweddi, ac mae'n agor y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.

2. Cliciwch Mewnosod > Modiwlau, a gludwch y cod canlynol yn y Modiwlau ffenestr.

VBA: rhowch rifau coll ar gyfer y dilyniant

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. Yna pwyswch F5 allwedd i redeg y cod hwn, a bydd blwch prydlon yn popio allan, dewiswch yr ystod ddata rydych chi am fewnosod y rhifau coll (peidiwch â dewis yr ystod teitl), gweler y screenshot:

doc-insert-coll-rhif9

4. Ac yna cliciwch OK, mae'r rhifau coll wedi'u mewnosod ar y rhestr ddilyniannau. Gweler sgrinluniau:

doc-insert-coll-rhif1 -2 doc-insert-coll-rhif2

swigen dde glas saeth Mewnosod rhesi gwag ar gyfer dilyniant coll gyda chod VBA

Weithiau, does ond angen i chi ddod o hyd i le'r rhifau coll a mewnosod rhesi gwag rhwng y data, fel y gallwch chi nodi'r wybodaeth yn ôl yr angen. Wrth gwrs, gall y cod VBA canlynol hefyd eich helpu i ddatrys y broblem hon.

1. Dal i lawr y ALT + F11 allweddi, ac mae'n agor a Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.

2. Cliciwch Mewnosod > Modiwlau, a gludwch y cod canlynol yn y Modiwlau ffenestr.

VBA: mewnosod rhesi gwag ar gyfer dilyniant coll

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. Yna pwyswch F5 allwedd i redeg y cod hwn, a bydd blwch prydlon yn ei arddangos, ac yna dewiswch yr ystod ddata rydych chi am ei mewnosod rhesi gwag ar gyfer y dilyniant coll (peidiwch â dewis yr ystod teitl), gweler y screenshot:

doc-insert-coll-rhif9

4. Ac yna cliciwch OK, mae'r rhesi gwag wedi'u mewnosod ar gyfer y rhestr dilyniant goll. Gweler sgrinluniau:

doc-insert-coll-rhif1 -2 doc-insert-coll-rhif10

swigen dde glas saeth Mewnosodwch rifau coll neu resi gwag ar gyfer dilyniant gyda Kutools ar gyfer Excel

Yma, byddaf yn cyflwyno teclyn hawdd a defnyddiol- Kutools ar gyfer Excel, Gyda'i Dewch o Hyd i Rif Dilyniant Ar Goll nodwedd, gallwch fewnosod y rhif dilyniant coll neu'r rhesi gwag yn gyflym rhwng y dilyniant data presennol.

Kutools ar gyfer Excel : gyda mwy na 300 o ychwanegiadau Excel defnyddiol, am ddim i geisio heb unrhyw gyfyngiad mewn 30 diwrnod. 
Ewch i Lawrlwytho
Treial Am Ddim 30 diwrnodprynu
PayPal / MyMasnach

Os ydych chi wedi gosod Kutools ar gyfer Excel, gwnewch fel a ganlyn:

1. Dewiswch y dilyniant data rydych chi am fewnosod y rhifau coll.

2. Cliciwch Kutools > Mewnosod > Dewch o Hyd i Rif Dilyniant Ar Goll, gweler y screenshot:

3. Yn y Dewch o Hyd i Rif Dilyniant Ar Goll blwch deialog, gwirio Mewnosod rhif dilyniant coll i fewnosod y rhifau coll neu I.traddodi rhesi gwag wrth ddod ar draws rhifau dilyniant coll i fewnosod rhesi gwag yn ôl yr angen. Gweler y screenshot:

doc-insert-coll-rhif10

4. Ac yna cliciwch OK botwm, ac mae'r rhifau dilyniant coll neu'r rhesi gwag wedi'u mewnosod yn y data, gweler sgrinluniau:

doc-insert-coll-rhif10 2 doc-insert-coll-rhif10 2 doc-insert-coll-rhif10

Dadlwythwch a threial am ddim Kutools ar gyfer Excel Nawr !

swigen dde glas saeth  Demo: Mewnosodwch rifau coll neu resi gwag ar gyfer dilyniant gyda Kutools ar gyfer Excel

Kutools ar gyfer Excel: gyda mwy na 300 o ychwanegiadau Excel defnyddiol, am ddim i geisio heb unrhyw gyfyngiad mewn 30 diwrnod. Dadlwythwch a threial am ddim Nawr!

Erthygl gysylltiedig:

Sut i nodi dilyniant rhifau coll yn Excel?

Offer Cynhyrchiant Swyddfa Gorau

🤖 Kutools AI Aide: Chwyldro dadansoddi data yn seiliedig ar: Cyflawniad Deallus   |  Cynhyrchu Cod  |  Creu Fformiwlâu Personol  |  Dadansoddi Data a Chynhyrchu Siartiau  |  Invoke Swyddogaethau Kutools...
Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog  |   VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr   |  Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd   |  Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau:  12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools ar gyfer Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed. Kutools ar gyfer Excel Yn Cynnig Dros 300 o Nodweddion Uwch i Hybu Cynhyrchiant ac Arbed Amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

Disgrifiad

Mae Office Tab yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
Comments (12)
No ratings yet. Be the first to rate!
melanie.m
melanie.m
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.
Reply
Report
0
0
skyyang
skyyang     melanie.m
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!
Reply
Report
0
0
Manouk
Manouk
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?
Reply
0
0
Rob
Rob
This comment was minimized by the moderator on the site
Thank you amazing
Reply
0
0
Gavin Cooke
Gavin Cooke
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
Reply
0
0
phani
phani
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
Reply
0
0
phani
phani
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 ?
Reply
0
0
Shahab
Shahab
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
Reply
0
0
Francois
Francois
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()
Reply
0
0
jonny
jonny
This comment was minimized by the moderator on the site
this worked and was very easy to complete the task. Thank you.
Reply
0
0
Denis
Denis
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?
Reply
0
0
Amir
Amir     Denis
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
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations