ไปยังเนื้อหาหลัก

วิธีแทรกแถวว่างเมื่อค่าเปลี่ยนแปลงใน Excel

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

แทรกแถวว่างเมื่อค่าเปลี่ยนแปลงด้วยฟังก์ชันผลรวมย่อย

แทรกแถวว่างเมื่อค่าเปลี่ยนแปลงด้วยคอลัมน์ตัวช่วย

แทรกแถวว่างเมื่อค่าเปลี่ยนแปลงด้วยรหัส VBA

แทรกจำนวนแถวว่างที่เฉพาะเจาะจงเมื่อค่าเปลี่ยนแปลงด้วยคุณสมบัติที่มีประสิทธิภาพ


แทรกแถวว่างเมื่อค่าเปลี่ยนแปลงด้วยฟังก์ชันผลรวมย่อย

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

1. เลือกช่วงข้อมูลที่คุณต้องการใช้

2. คลิก ข้อมูล > ราคาทั้งหมด เพื่อเปิด ราคาทั้งหมด กล่องโต้ตอบและใน ราคาทั้งหมด โปรดทำตามตัวเลือกต่อไปนี้:

1: เลือกชื่อคอลัมน์ที่คุณต้องการแทรกแถวว่างตามเมื่อค่าเปลี่ยนแปลงภายใต้ ในการเปลี่ยนแปลงแต่ละครั้ง ในส่วน;
2: เลือก นับ จาก ฟังก์ชั่นการใช้งาน รายการแบบหล่นลง
3: ตรวจสอบชื่อคอลัมน์ที่คุณต้องการแทรกผลรวมย่อยในไฟล์ เพิ่มผลรวมย่อยลงใน กล่องรายการ

3. จากนั้นคลิก OKมีการแทรกบรรทัดผลรวมย่อยระหว่างผลิตภัณฑ์ต่างๆและสัญลักษณ์เค้าร่างจะแสดงที่ด้านซ้ายของตารางดูภาพหน้าจอ:

4. จากนั้นคลิกหมายเลข 2 ที่ด้านบนของสัญลักษณ์เค้าร่างเพื่อแสดงเฉพาะบรรทัดผลรวมย่อยเท่านั้น

5. จากนั้นเลือกข้อมูลช่วงผลรวมย่อยแล้วกด Alt +; ปุ่มลัดเพื่อเลือกแถวที่มองเห็นเท่านั้นดูภาพหน้าจอ:

6. หลังจากเลือกเฉพาะแถวที่มองเห็นได้แล้วให้กดปุ่ม ลบ บนแป้นพิมพ์และลบแถวผลรวมย่อยทั้งหมดแล้ว

7. จากนั้นคลิกเซลล์อื่นแล้วย้อนกลับ ข้อมูล > ยกเลิกการจัดกลุ่ม > ล้างโครงร่าง เพื่อลบโครงร่างดูภาพหน้าจอ:

8. สัญลักษณ์เค้าร่างถูกล้างในครั้งเดียวและคุณสามารถเห็นแถวว่างถูกแทรกระหว่างข้อมูลเมื่อค่าเปลี่ยนแปลงดูภาพหน้าจอ:

9. ในที่สุดคุณสามารถลบคอลัมน์ A ได้ตามต้องการ


แทรกตัวแบ่งหน้าแถวว่างขอบด้านล่างหรือเติมสีเมื่อค่าเปลี่ยนแปลงอย่างรวดเร็ว

ถ้าคุณมี Kutools สำหรับ Excel's แยกแยะความแตกต่าง คุณสามารถแทรกตัวแบ่งหน้าแถวว่างเส้นขอบด้านล่างหรือเติมสีได้อย่างรวดเร็วเมื่อค่าเปลี่ยนไปตามที่คุณต้องการ ดูการสาธิตด้านล่าง         คลิกเพื่อดาวน์โหลด Kutools for Excel!


แทรกแถวว่างเมื่อค่าเปลี่ยนแปลงด้วยคอลัมน์ตัวช่วย

ด้วยคอลัมน์ตัวช่วยคุณสามารถแทรกสูตรก่อนจากนั้นจึงใช้ ค้นหาและแทนที่ ในที่สุดฟังก์ชันแทรกแถวว่างระหว่างค่าที่เปลี่ยนแปลง โปรดดำเนินการดังนี้:

1. ในเซลล์ว่าง C3 โปรดป้อนสูตรนี้ = A3 = A2และในเซลล์ D4 ให้ป้อนสูตรนี้ = A4 = A3ดูภาพหน้าจอ:

2. จากนั้นเลือก C3: D4 แล้วลากจุดจับเติมไปยังช่วงที่คุณต้องการใช้สูตรเหล่านี้และคุณจะได้รับ จริง or เท็จ ในเซลล์ดูภาพหน้าจอ:

3. จากนั้นกด Ctrl + F คีย์เพื่อเปิด ค้นหาและแทนที่ ในกล่องโต้ตอบที่โผล่ออกมาให้ป้อน FALSE เข้าไปใน สิ่งที่ค้นหา กล่องข้อความภายใต้ หา แล้วคลิก Options เพื่อขยายกล่องโต้ตอบนี้และเลือก ความคุ้มค่า จาก มองใน รายการแบบเลื่อนลงดูภาพหน้าจอ:

4. คลิก ค้นหาทั้งหมด จากนั้นกด Ctrl + เพื่อเลือกผลลัพธ์ทั้งหมดของการค้นหาเซลล์ FALSE ทั้งหมดได้ถูกเลือกในครั้งเดียวดูภาพหน้าจอ:

6. ปิด ค้นหาและแทนที่ ขั้นตอนต่อไปคุณสามารถแทรกแถวว่างได้โดยคลิก หน้าแรก > สิ่งที่ใส่เข้าไป > แทรกแถวแผ่นงานและมีการแทรกแถวว่างลงในข้อมูลเมื่อค่าเปลี่ยนแปลงตามคอลัมน์ A ดูภาพหน้าจอ:

7. สุดท้ายคุณสามารถลบคอลัมน์ตัวช่วย C และ D ได้ตามที่คุณต้องการ


แทรกแถวว่างเมื่อค่าเปลี่ยนแปลงด้วยรหัส VBA

หากคุณเบื่อที่จะใช้วิธีการข้างต้นนี่คือโค้ดที่สามารถช่วยคุณในการแทรกแถวว่างระหว่างค่าที่เปลี่ยนแปลงพร้อมกัน

1. กด ALT + F11 คีย์เพื่อเปิด หน้าต่าง Microsoft Visual Basic for Applications.

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

รหัส VBA: แทรกแถวว่างเมื่อค่าเปลี่ยนแปลง

Sub InsertRowsAtValueChange()
'Update by Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 2 Step -1
    If WorkRng.Cells(i, 1).Value <> WorkRng.Cells(i - 1, 1).Value Then
        WorkRng.Cells(i, 1).EntireRow.Insert
    End If
Next
Application.ScreenUpdating = True
End Sub

3. จากนั้นกด F5 คีย์เพื่อเรียกใช้รหัสนี้และกล่องพร้อมต์จะปรากฏขึ้นเพื่อให้คุณเลือกข้อมูลคอลัมน์หนึ่งคอลัมน์ที่คุณต้องการแทรกแถวว่างเมื่อค่าเปลี่ยนแปลงตามที่คุณใช้ดูภาพหน้าจอ:

4. จากนั้นคลิก OKมีการแทรกแถวว่างระหว่างข้อมูลเมื่อค่าเปลี่ยนไปตามคอลัมน์ A


แทรกจำนวนแถวว่างที่เฉพาะเจาะจงเมื่อค่าเปลี่ยนแปลงด้วยคุณสมบัติที่มีประสิทธิภาพ

หากคุณกำลังลองใช้วิธีการที่ยุ่งยากข้างต้นฉันจะแนะนำเครื่องมือที่มีประโยชน์ Kutools สำหรับ Excel's แยกแยะความแตกต่าง สามารถช่วยให้คุณแทรกตัวแบ่งหน้าแถวว่างขอบด้านล่างหรือเติมสีเมื่อค่าของเซลล์เปลี่ยนแปลงอย่างรวดเร็วและง่ายดาย

ทิปส์:ที่จะใช้สิ่งนี้ แยกแยะความแตกต่าง ประการแรกคุณควรดาวน์โหลดไฟล์ Kutools สำหรับ Excelแล้วใช้คุณสมบัตินี้อย่างรวดเร็วและง่ายดาย

หลังจากการติดตั้ง Kutools สำหรับ Excelโปรดทำตามนี้:

1. คลิก Kutools > รูปแบบ > แยกแยะความแตกต่างดูภาพหน้าจอ:

2. ใน แยกแยะความแตกต่างตามคอลัมน์หลัก โปรดดำเนินการดังต่อไปนี้:

  • เลือกช่วงข้อมูลที่คุณต้องการใช้จากนั้นเลือกคอลัมน์หลักที่คุณต้องการแทรกแถวว่างตาม
  • จากนั้นตรวจสอบ แถวว่าง ตัวเลือกจาก Options และป้อนจำนวนแถวว่างที่คุณต้องการแทรก

3. จากนั้นคลิก Ok และจำนวนแถวว่างที่ระบุได้ถูกแทรกลงในข้อมูลหากค่าของเซลล์เปลี่ยนแปลงโปรดดูภาพหน้าจอ:

คลิกเพื่อดาวน์โหลด Kutools สำหรับ Excel และทดลองใช้ฟรีทันที!

สุดยอดเครื่องมือเพิ่มผลผลิตในสำนักงาน

คุณสมบัติยอดนิยม: ค้นหา เน้น หรือระบุรายการที่ซ้ำกัน   |  ลบแถวว่าง   |  รวมคอลัมน์หรือเซลล์โดยไม่สูญเสียข้อมูล   |   รอบโดยไม่มีสูตร ...
การค้นหาขั้นสูง: VLookup หลายเกณฑ์    VLookup หลายค่า  |   VLookup ข้ามหลายแผ่น   |   การค้นหาที่ไม่ชัดเจน ....
รายการแบบเลื่อนลงขั้นสูง: สร้างรายการแบบหล่นลงอย่างรวดเร็ว   |  รายการแบบหล่นลงขึ้นอยู่กับ   |  เลือกหลายรายการแบบหล่นลง ....
ผู้จัดการคอลัมน์: เพิ่มจำนวนคอลัมน์เฉพาะ  |  ย้ายคอลัมน์  |  สลับสถานะการมองเห็นของคอลัมน์ที่ซ่อนอยู่  |  เปรียบเทียบช่วงและคอลัมน์ ...
คุณสมบัติเด่น: กริดโฟกัส   |  มุมมองการออกแบบ   |   บาร์สูตรใหญ่    สมุดงานและตัวจัดการชีต   |  ห้องสมุดทรัพยากร (ข้อความอัตโนมัติ)   |  เลือกวันที่   |  รวมแผ่นงาน   |  เข้ารหัส/ถอดรหัสเซลล์    ส่งอีเมลตามรายการ   |  ซุปเปอร์ฟิลเตอร์   |   ตัวกรองพิเศษ (กรองตัวหนา/ตัวเอียง/ขีดทับ...) ...
ชุดเครื่องมือ 15 อันดับแรก12 ข้อความ เครื่องมือ (เพิ่มข้อความ, ลบอักขระ, ... )   |   50 + แผนภูมิ ประเภท (แผนภูมิ Gantt, ... )   |   40+ ใช้งานได้จริง สูตร (คำนวณอายุตามวันเกิด, ... )   |   19 การแทรก เครื่องมือ (ใส่ QR Code, แทรกรูปภาพจากเส้นทาง, ... )   |   12 การแปลง เครื่องมือ (ตัวเลขเป็นคำ, การแปลงสกุลเงิน, ... )   |   7 ผสานและแยก เครื่องมือ (แถวรวมขั้นสูง, แยกเซลล์, ... )   |   ... และอื่น ๆ

เพิ่มพูนทักษะ Excel ของคุณด้วย Kutools สำหรับ Excel และสัมผัสประสิทธิภาพอย่างที่ไม่เคยมีมาก่อน Kutools สำหรับ Excel เสนอคุณสมบัติขั้นสูงมากกว่า 300 รายการเพื่อเพิ่มประสิทธิภาพและประหยัดเวลา  คลิกที่นี่เพื่อรับคุณสมบัติที่คุณต้องการมากที่สุด...

kte แท็บ 201905


แท็บ Office นำอินเทอร์เฟซแบบแท็บมาที่ Office และทำให้งานของคุณง่ายขึ้นมาก

  • เปิดใช้งานการแก้ไขและอ่านแบบแท็บใน Word, Excel, PowerPoint, ผู้จัดพิมพ์, Access, Visio และโครงการ
  • เปิดและสร้างเอกสารหลายรายการในแท็บใหม่ของหน้าต่างเดียวกันแทนที่จะเป็นในหน้าต่างใหม่
  • เพิ่มประสิทธิภาพการทำงานของคุณ 50% และลดการคลิกเมาส์หลายร้อยครั้งให้คุณทุกวัน!
Comments (16)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi, Hassan,

This vba is amazing, whereas I need one more alternate code for insert single blank row after changes in sequencing numbers. Example:- In column having number series like 1, 2, 3, 5, 6, 9 & etc...
Need to add single blank row between 3 & 5 as well as 6 & 9.
Please can you with this.
This comment was minimized by the moderator on the site
Can the VBA method be adapted to ignore blank cells? I have a file which I need to insert rows in based on two different columns but when I run the macro on the second column I end up with three blank lines where the first macro run inserted rows.
Or can it run on two columns at the same time?
This comment was minimized by the moderator on the site
Hello,
Is very usefull in case i need to insert 1 row, but if i need to insert 145 rows in every time the data change in spwcific column, how can i do it??
This comment was minimized by the moderator on the site
Hi, Hassan,
To insert multiple blank rows when value changes in a specific column, you should apply the following VBA code:

Note: In the below code, you should change the number 99 to your need, for example, when you insert 145 blank rows, you should change the number 99 to 144. Please try it, hope it can help you!

Sub InsertRowsAtValueChange()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 2 Step -1
If WorkRng.Cells(i, 1).Value <> WorkRng.Cells(i - 1, 1).Value Then
Range(WorkRng.Cells(i, 1).EntireRow, WorkRng.Cells(i + 99, 1).EntireRow).Insert
End If
Next
Application.ScreenUpdating = True
End Sub
This comment was minimized by the moderator on the site
Very helpful. The code that skyyang shows above worked perfectly. Just make sure that the data doesn't already have spaces in it.

I don't understand VBA, but I believe if you wanted to add more rows underneath data that already had the spacing, there should be a way to ignore spaces.

Could a line be added to ignore or skip over blank lines? That might make this code more universal and repeatable if needed. Also a delete function that is similar to this may be useful so undo isn't necessary.
This comment was minimized by the moderator on the site
RE: insert blank rows when value changes with vba code
Is there a way that I can save the Range & not have to pick it every time I run it?
This comment was minimized by the moderator on the site
The VBA code worked 1st time and did exactly what I was trying to do. Thank you so much!
This comment was minimized by the moderator on the site
I've been using my own solution for some time.
1. Insert a helper column into Column A
2. In A2, type "if(B2=B1,A1,A1+1)"
3. Copy that formula down to the last row
4. Copy all the populated cells in column A and Paste Special (Values) over them
5. Copy all the cells again and paste them into column A in the first unpopulated cell (e.g. if you have 104 rows of data plus a header row you would paste into cell A106)
6. Click on Data and Remove Duplicates (only on the cells you just pasted in Step 5; not on all rows)
7. Sort all of Column A
8. Delete Column A

Seems like a lot of steps but only takes a few seconds.
This comment was minimized by the moderator on the site
[quote]Hi all thank you!! its awesome , can you guys also let me how to insert 2 rows when the value changes in VBA or through excel.By Hudson[/quote] Please let me know how to insert more than 1 row.
This comment was minimized by the moderator on the site
Hi there, These are almost useful! The first method doesn't work for me because when I follow the steps explicitly, the the data that I delete in the subtotal panes deletes the entire columns that I've sorted. In the second method when I get to the step where I insert sheet rows, the rows are inserted ABOVE the FALSE cells which breaks up the data, but the last selection of every group is then added to the group below. Any advice???
This comment was minimized by the moderator on the site
Hi all thank you!! its awesome , can you guys also let me how to insert 2 rows when the value changes in VBA or through excel.
This comment was minimized by the moderator on the site
Where in the code would I need to modify to include more than one row, I need to add 10 after each break... Thanks
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations