วิธีแทรกตัวเลขหรือแถวสำหรับหมายเลขลำดับที่ขาดหายไปใน Excel

สมมติว่าคุณมีรายการหมายเลขลำดับในแผ่นงาน แต่มีตัวเลขที่ขาดหายไปในลำดับและตอนนี้คุณต้องแทรกตัวเลขที่ขาดหายไปหรือแถวว่างเพื่อให้แน่ใจว่าลำดับนั้นสมบูรณ์ (แสดงเป็นภาพหน้าจอต่อไปนี้) คุณจะแก้ปัญหานี้อย่างรวดเร็วใน Excel ได้อย่างไร

แทรกตัวเลขที่ขาดหายไปสำหรับลำดับด้วยคุณสมบัติเรียงลำดับและลบรายการที่ซ้ำกัน แทรกตัวเลขที่ขาดหายไปหรือแถวว่างสำหรับลำดับด้วย Kutools for Excel ใส่หมายเลขที่ขาดหายไปสำหรับลำดับด้วยรหัส VBA แทรกแถวว่างสำหรับลำดับที่ขาดหายไปด้วยรหัส VBA

แทรกตัวเลขที่ขาดหายไปสำหรับลำดับด้วยคุณสมบัติเรียงลำดับและลบรายการที่ซ้ำกัน

อาจเป็นไปได้ว่าคุณสามารถค้นหาหมายเลขที่หายไปทีละหมายเลขแล้วใส่เข้าไป แต่เป็นการยากสำหรับคุณที่จะระบุตำแหน่งของหมายเลขที่หายไปหากมีตัวเลขเรียงตามลำดับหลายร้อยหมายเลข ใน Excel ฉันสามารถใช้คุณลักษณะเรียงลำดับและลบรายการที่ซ้ำกันเพื่อจัดการกับงานนี้

1. หลังจากสิ้นสุดรายการลำดับแล้วให้กรอกหมายเลขลำดับอื่นจาก 2005023001 ถึง 2005023011 ดูภาพหน้าจอ:

2. จากนั้นเลือกช่วงของตัวเลขสองลำดับแล้วคลิก ข้อมูล > เรียงลำดับ A ถึง Zดูภาพหน้าจอ:

3. และข้อมูลที่เลือกได้รับการจัดเรียงเป็นภาพหน้าจอต่อไปนี้:

4. จากนั้นคุณต้องลบรายการที่ซ้ำกันโดยคลิก ข้อมูล > ลบรายการที่ซ้ำกันและในโผล่ออกมา ลบรายการที่ซ้ำกัน ให้ทำเครื่องหมายที่ คอลัมน์ ชื่อที่คุณต้องการลบรายการที่ซ้ำกันดูภาพหน้าจอ:

5. จากนั้นคลิก OKที่ซ้ำกันใน คอลัมน์ก ถูกลบและใส่หมายเลขที่ขาดหายไปในรายการลำดับดูภาพหน้าจอ:

แทรกตัวเลขที่ขาดหายไปหรือแถวว่างสำหรับลำดับด้วย Kutools for Excel

ที่นี่ฉันจะแนะนำเครื่องมือที่ง่ายและสะดวก - Kutools สำหรับ Excelเดียวกันกับที่ ค้นหาหมายเลขลำดับที่ขาดหายไป คุณสามารถแทรกหมายเลขลำดับที่ขาดหายไปหรือแถวว่างระหว่างลำดับข้อมูลที่มีอยู่ได้อย่างรวดเร็ว

ถ้าคุณได้ติดตั้ง Kutools สำหรับ Excelโปรดดำเนินการดังนี้:

1. เลือกลำดับข้อมูลที่คุณต้องการแทรกตัวเลขที่ขาดหายไป

2. คลิก Kutools > สิ่งที่ใส่เข้าไป > ค้นหาหมายเลขลำดับที่ขาดหายไปดูภาพหน้าจอ:

3. ใน ค้นหาหมายเลขลำดับที่ขาดหายไป กล่องโต้ตอบตรวจสอบ การใส่หมายเลขลำดับที่ขาดหายไป เพื่อแทรกตัวเลขที่ขาดหายไปหรือ Inserting แถวว่างเมื่อพบหมายเลขลำดับที่ขาดหายไป เพื่อแทรกแถวว่างตามที่คุณต้องการ ดูภาพหน้าจอ:

4. จากนั้นคลิก OK และมีการแทรกหมายเลขลำดับที่ขาดหายไปหรือแถวว่างลงในข้อมูลดูภาพหน้าจอ:

ดาวน์โหลดและทดลองใช้ Kutools for Excel ฟรีทันที!

ใส่หมายเลขที่ขาดหายไปสำหรับลำดับด้วยรหัส VBA

หากคุณรู้สึกว่ามีหลายขั้นตอนด้วยวิธีการข้างต้นที่นี่ยังมีรหัส VBA สามารถช่วยคุณแก้ปัญหานี้ได้ โปรดดำเนินการดังนี้:

1. กด ALT + F11 และจะเปิดไฟล์ Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่าง

2. คลิก สิ่งที่ใส่เข้าไป > โมดูลและวางรหัสต่อไปนี้ในไฟล์ โมดูล หน้าต่าง

VBA: ใส่ตัวเลขที่ขาดหายไปสำหรับลำดับ

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. จากนั้นกด F5 คีย์เพื่อเรียกใช้รหัสนี้และกล่องพร้อมต์จะปรากฏขึ้นโปรดเลือกช่วงข้อมูลที่คุณต้องการแทรกตัวเลขที่ขาดหายไป (อย่าเลือกช่วงหัวเรื่อง) ดูภาพหน้าจอ:

4. จากนั้นคลิก OKหมายเลขที่หายไปได้ถูกแทรกลงในรายการลำดับ ดูภาพหน้าจอ:

แทรกแถวว่างสำหรับลำดับที่ขาดหายไปด้วยรหัส VBA

บางครั้งคุณเพียงแค่ต้องค้นหาตำแหน่งของตัวเลขที่ขาดหายไปและแทรกแถวว่างระหว่างข้อมูลเพื่อให้คุณสามารถป้อนข้อมูลได้ตามที่คุณต้องการ แน่นอนรหัส VBA ต่อไปนี้สามารถช่วยคุณแก้ปัญหานี้ได้

1. ค้างไว้ ALT + F11 และจะเปิดไฟล์ Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่าง

2. คลิก สิ่งที่ใส่เข้าไป > โมดูลและวางรหัสต่อไปนี้ในไฟล์ โมดูล หน้าต่าง

VBA: แทรกแถวว่างสำหรับลำดับที่ขาดหายไป

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. จากนั้นกด F5 คีย์เพื่อเรียกใช้รหัสนี้และกล่องพร้อมต์จะปรากฏขึ้นจากนั้นเลือกช่วงข้อมูลที่คุณต้องการแทรกแถวว่างสำหรับลำดับที่ขาดหายไป (อย่าเลือกช่วงหัวเรื่อง) ดูภาพหน้าจอ:

4. จากนั้นคลิก OKมีการแทรกแถวว่างสำหรับรายการลำดับที่ขาดหายไป ดูภาพหน้าจอ:

บทความที่เกี่ยวข้อง:

วิธีระบุลำดับตัวเลขที่ขาดหายไปใน Excel