Skip to main content

Conas uimhreacha nó sraitheanna d’uimhreacha seicheamhacha atá in easnamh a chur isteach in Excel?

Má cheaptar go bhfuil liosta d’uimhreacha seicheamhacha agat i mbileog oibre, ach tá roinnt uimhreacha in easnamh i measc na seicheamh, agus anois ní mór duit na huimhreacha nó na sraitheanna bána atá in easnamh a chur isteach chun a chinntiú go bhfuil an seicheamh iomlán (taispeántar mar na scáileáin scáileáin seo a leanas). Conas a d’fhéadfá an fhadhb seo a réiteach go tapa in Excel?

doc-insert-in easnamh-uimhir1 -2 doc-insert-in easnamh-uimhir2

Cuir isteach uimhreacha atá in easnamh le haghaidh seicheamh leis an ngné Sórtáil agus Bain Dúblach

Cuir isteach uimhreacha atá in easnamh le haghaidh seicheamh le cód VBA

Cuir isteach sraitheanna bána le haghaidh seicheamh in easnamh le cód VBA

Cuir isteach uimhreacha nó sraitheanna bána in easnamh le haghaidh seicheamh le Kutools for Excel

mboilgeog cheart gorm saighead Cuir isteach uimhreacha atá in easnamh le haghaidh seicheamh leis an ngné Sórtáil agus Bain Dúblach

B’fhéidir go bhfaighidh tú na huimhreacha atá in easnamh ceann ar cheann, agus ansin iad a chur isteach, ach tá sé deacair duit suíomh na ndaoine atá in easnamh a aithint má tá na céadta uimhreacha seicheamhach ann. In Excel, is féidir liom an ghné Sórtáil agus Bain Dúblaigh a úsáid chun déileáil leis an tasc seo.

1. Tar éis dheireadh an liosta seicheamh, líon uimhreacha seicheamh eile ó 2005023001 go 2005023011. Féach an pictiúr:

doc-insert-in easnamh-uimhir3

2. Ansin roghnaigh raon an dá uimhir seicheamh agus cliceáil Dáta > Sórtáil A go Z., féach ar an scáileán:

doc-insert-in easnamh-uimhir4

3. Agus tá na sonraí roghnaithe curtha in eagar mar a leanas:

doc-insert-in easnamh-uimhir5

4. Ansin ní mór duit na dúblacha a bhaint trí chliceáil Dáta > Bain Dúblaigh, agus sa popped amach Bain Dúblaigh bosca dialóige, seiceáil an Colún ainm a theastaíonn uait na dúbailtí a bhaint, féach screenshots:

doc-insert-in easnamh-uimhir6 -2 doc-insert-in easnamh-uimhir7

5. Ansin cliceáil OK, na dúblacha i Colún A. Scriosadh, agus cuireadh na huimhreacha atá in easnamh ar an liosta seicheamh isteach, féach an scáileán:

doc-insert-in easnamh-uimhir8

mboilgeog cheart gorm saighead Cuir isteach uimhreacha atá in easnamh le haghaidh seicheamh le cód VBA

Má bhraitheann tú go bhfuil an oiread sin céimeanna leis na modhanna thuasluaite, tá cód VBA anseo freisin a chabhróidh leat an fhadhb seo a chríochnú. Déan mar a leanas le do thoil:

1. Coinnigh síos an ALT + F11 eochracha, agus osclaíonn sé an Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

2. Cliceáil Ionsáigh > Modúil, agus greamaigh an cód seo a leanas sa Modúil fhuinneog.

VBA: cuir isteach uimhreacha atá in easnamh don seicheamh

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. Ansin brúigh F5 eochair chun an cód seo a rith, agus tiocfaidh bosca pras amach, roghnaigh an raon sonraí a theastaíonn uait na huimhreacha atá in easnamh a chur isteach (ná roghnaigh an raon teidil), féach an scáileán:

doc-insert-in easnamh-uimhir9

4. Agus ansin cliceáil OK, cuireadh na huimhreacha atá in easnamh ar an liosta seicheamh. Féach screenshots:

doc-insert-in easnamh-uimhir1 -2 doc-insert-in easnamh-uimhir2

mboilgeog cheart gorm saighead Cuir isteach sraitheanna bána le haghaidh seicheamh in easnamh le cód VBA

Uaireanta, ní gá duit ach áit na n-uimhreacha atá in easnamh a aimsiú agus sraitheanna bána a chur isteach idir na sonraí, ionas gur féidir leat an fhaisnéis a iontráil de réir mar is gá duit. Ar ndóigh, is féidir leis an gcód VBA seo a leanas cabhrú leat an fhadhb seo a réiteach.

1. Coinnigh síos an ALT + F11 eochracha, agus osclaíonn sé a Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

2. Cliceáil Ionsáigh > Modúil, agus greamaigh an cód seo a leanas sa Modúil fhuinneog.

VBA: cuir isteach sraitheanna bána le haghaidh seicheamh in easnamh

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. Ansin brúigh F5 eochair chun an cód seo a rith, agus taispeánfar bosca pras, agus ansin roghnaigh an raon sonraí a theastaíonn uait sraitheanna bána a chur isteach don seicheamh atá in easnamh (ná roghnaigh an raon teidil), féach an scáileán:

doc-insert-in easnamh-uimhir9

4. Agus ansin cliceáil OK, tá na sraitheanna bána curtha isteach don liosta seicheamh atá in easnamh. Féach screenshots:

doc-insert-in easnamh-uimhir1 -2 doc-insert-in easnamh-uimhir10

mboilgeog cheart gorm saighead Cuir isteach uimhreacha nó sraitheanna bána in easnamh le haghaidh seicheamh le Kutools for Excel

Anseo, tabharfaidh mé uirlis éasca agus áisiúil isteach Kutools le haghaidh Excel, Lena Faigh Uimhir Seicheamh ar Iarraidh gné, is féidir leat an uimhir seicheamh nó na sraitheanna bána atá in easnamh a chur isteach go tapa idir an seicheamh sonraí atá ann.

Kutools le haghaidh Excel : le níos mó ná 300 breiseán áisiúil Excel, saor in aisce le triail gan aon teorannú i 30 lá. 
Téigh go dtí Íoslódáil
Triail In Aisce 30 láCeannach
PayPal / MyCommerce

Má tá tú suiteáilte agat Kutools le haghaidh Excel, déan mar a leanas le do thoil:

1. Roghnaigh an t-ord sonraí a theastaíonn uait na huimhreacha atá in easnamh a chur isteach.

2. Cliceáil Kutools > Ionsáigh > Faigh Uimhir Seicheamh ar Iarraidh, féach ar an scáileán:

3. Sa an Faigh Uimhir Seicheamh ar Iarraidh bosca dialóige, seiceáil Uimhir seicheamh in easnamh a chur isteach chun na huimhreacha atá in easnamh a chur isteach nó I.sraitheanna bána a chur isteach nuair a bhíonn uimhreacha seicheamh in easnamh chun sraitheanna bána a chur isteach de réir mar is gá duit. Féach an pictiúr:

doc-insert-in easnamh-uimhir10

4. Agus ansin cliceáil OK cnaipe, agus na huimhreacha seicheamh atá in easnamh nó na sraitheanna bána curtha isteach sna sonraí, féach scáileáin scáileáin:

doc-insert-in easnamh-uimhir10 2 doc-insert-in easnamh-uimhir10 2 doc-insert-in easnamh-uimhir10

Íoslódáil agus triail saor in aisce Kutools le haghaidh Excel Now!

mboilgeog cheart gorm saighead  Taispeántas: Cuir isteach uimhreacha atá in easnamh nó sraitheanna bána le haghaidh seicheamh le Kutools for Excel

Kutools le haghaidh Excel: le níos mó ná 300 breiseán áisiúil Excel, saor in aisce le triail gan aon teorannú i 30 lá. Íoslódáil agus triail saor in aisce Anois!

Airteagal gaolmhar:

Conas seicheamh uimhreacha atá in easnamh a aithint in Excel?

Uirlisí Táirgiúlachta Oifige is Fearr

🤖 Kutools AI Aide: anailís sonraí a réabhlóidiú bunaithe ar: Forghníomhú Chliste   |  Gin Cód  |  Cruthaigh Foirmlí Saincheaptha  |  Anailís a dhéanamh ar Sonraí agus Cairteacha a Ghin  |  Feidhmeanna Kutools a agairt...
Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup  |   VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle   |  Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla   |  Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí:  12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

Tuairisc

Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
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