เนื้อหา
แถวและคอลัมน์ที่ว่างเปล่าอาจทำให้ตารางเสียหายได้ในหลายกรณี ฟังก์ชันมาตรฐานสำหรับการเรียงลำดับ การกรอง การสรุป การสร้างตารางสาระสำคัญ ฯลฯ รับรู้แถวและคอลัมน์ที่ว่างเปล่าเป็นตัวแบ่งตาราง โดยไม่ต้องรับข้อมูลที่อยู่ด้านหลัง หากมีช่องว่างดังกล่าวจำนวนมาก การลบออกด้วยตนเองอาจมีค่าใช้จ่ายสูงมาก และจะไม่สามารถลบทั้งหมดในครั้งเดียว "เป็นกลุ่ม" โดยใช้การกรอง เนื่องจากตัวกรองจะ "สะดุด" เมื่อขาดเช่นกัน
ลองดูหลายวิธีในการแก้ปัญหานี้
วิธีที่ 1 ค้นหาเซลล์ว่าง
นี่อาจไม่ใช่วิธีที่สะดวกที่สุด แต่แน่นอนว่าวิธีที่ง่ายที่สุดที่ควรค่าแก่การกล่าวถึง
สมมติว่าเรากำลังจัดการกับตารางดังกล่าวที่มีแถวและคอลัมน์ว่างจำนวนมากอยู่ภายใน (เน้นเพื่อความชัดเจน):
สมมติว่าเราแน่ใจว่าคอลัมน์แรกของตารางของเรา (คอลัมน์ B) มีชื่อเมืองเสมอ จากนั้นเซลล์ว่างในคอลัมน์นี้จะเป็นสัญญาณของแถวว่างที่ไม่จำเป็น หากต้องการลบทั้งหมดอย่างรวดเร็ว ให้ทำดังนี้:
- เลือกช่วงที่มีเมือง (B2:B26)
- กดปุ่ม F5 จากนั้นกด เน้น (ไปที่พิเศษ) หรือเลือกบนแท็บ หน้าแรก — ค้นหาและเลือก — เลือกกลุ่มเซลล์ (หน้าแรก — ค้นหา&เลือก — ไปที่พิเศษ).
- ในหน้าต่างที่เปิดขึ้น ให้เลือกตัวเลือก เซลล์ว่าง (ช่องว่าง) และกด OK – ควรเลือกเซลล์ว่างทั้งหมดในคอลัมน์แรกของตารางของเรา
- ตอนนี้เลือกบนแท็บ หน้าแรก คำสั่ง ลบ – ลบแถวออกจากแผ่นงาน (ลบ — ลบแถว) หรือกดแป้นพิมพ์ลัด Ctrl+ลบ – และงานของเราได้รับการแก้ไขแล้ว
แน่นอน คุณสามารถกำจัดคอลัมน์ว่างด้วยวิธีเดียวกัน โดยใช้ส่วนหัวของตารางเป็นพื้นฐาน
วิธีที่ 2: ค้นหาแถวว่าง
ตามที่คุณอาจทราบแล้ว วิธีก่อนหน้านี้จะใช้ได้ก็ต่อเมื่อข้อมูลของเราจำเป็นต้องมีแถวและคอลัมน์ที่กรอกข้อมูลครบถ้วน ซึ่งสามารถนำไปใช้งานได้เมื่อค้นหาเซลล์ว่าง แต่ถ้าไม่มีความมั่นใจเช่นนั้น และข้อมูลก็อาจมีเซลล์ว่างด้วยล่ะ?
ดูตารางต่อไปนี้ ตัวอย่างเช่น สำหรับกรณีดังกล่าว:
วิธีการนี้จะยากขึ้นเล็กน้อย:
- ป้อนฟังก์ชันในเซลล์ A2 COUNT (นับ)ซึ่งจะคำนวณจำนวนเซลล์ที่เติมในแถวทางด้านขวาและคัดลอกสูตรนี้ลงไปที่ตารางทั้งหมด:
- เลือกเซลล์ A2 และเปิดตัวกรองด้วยคำสั่ง ข้อมูล – ตัวกรอง (ข้อมูล — ตัวกรอง) หรือแป้นพิมพ์ลัด Ctrl+เปลี่ยน+L.
- มากรองค่าศูนย์ตามคอลัมน์จากการคำนวณกัน นั่นคือแถวทั้งหมดที่ไม่มีข้อมูล
- ยังคงเลือกบรรทัดที่กรองแล้วลบออกด้วยคำสั่ง หน้าแรก — ลบ -' ลบแถวออกจากแผ่นงาน (หน้าแรก — ลบ — ลบแถว) หรือแป้นพิมพ์ลัด Ctrl+ลบ.
- เราปิดตัวกรองและรับข้อมูลโดยไม่มีบรรทัดว่าง
น่าเสียดายที่เคล็ดลับนี้ไม่สามารถทำได้กับคอลัมน์อีกต่อไป – Excel ยังไม่ได้เรียนรู้วิธีการกรองตามคอลัมน์
วิธีที่ 3 มาโครเพื่อลบแถวและคอลัมน์ว่างทั้งหมดบนแผ่นงาน
คุณยังสามารถใช้มาโครอย่างง่ายเพื่อทำให้งานนี้เป็นแบบอัตโนมัติได้ กดแป้นพิมพ์ลัด อื่น ๆ+F11 หรือเลือกจากแท็บ ผู้พัฒนา — วิชวลเบสิก (ผู้พัฒนา — ตัวแก้ไข Visual Basic). ถ้าแท็บ ผู้พัฒนา มองไม่เห็น คุณสามารถเปิดใช้งานผ่าน ไฟล์ – ตัวเลือก – การตั้งค่าริบบอน (ไฟล์ — ตัวเลือก — กำหนด Ribbon เอง).
ในหน้าต่างตัวแก้ไข Visual Basic ที่เปิดขึ้น ให้เลือกคำสั่งเมนู แทรก – โมดูล และในโมดูลว่างที่ปรากฏขึ้น ให้คัดลอกและวางบรรทัดต่อไปนี้:
Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count ถ้า Application.CountA(Rows(r)) = 0 ถ้าเช่นนั้น rng Is Nothing แล้ว Set rng = Rows(r) Else Set rng = Union(rng, Rows(r)) End If Next r If Not rng Is Nothing จากนั้น rng.Delete 'удаляем пустые столбцы Set rng = Nothing For r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count ถ้า Application.CountA(Columns(r)) = 0 ถ้า rng ไม่มีอะไร ให้ตั้งค่า rng = Columns(r) Else Set rng = Union(rng, Columns( r)) จบถ้าถัดไป r ถ้าไม่ rng ไม่มีอะไรแล้ว rng.Delete End Sub
ปิดตัวแก้ไขและกลับไปที่ Excel
ตอนนี้ตีรวมกัน อื่น ๆ+F8 หรือปุ่ม แมโคร แถบ ผู้พัฒนา. หน้าต่างที่เปิดขึ้นจะแสดงรายการมาโครทั้งหมดที่คุณสามารถเรียกใช้ได้ในปัจจุบัน รวมถึงมาโครที่คุณเพิ่งสร้างขึ้น ลบEmpty. เลือกและคลิกปุ่ม วิ่ง (วิ่ง) – แถวและคอลัมน์ว่างทั้งหมดบนแผ่นงานจะถูกลบออกทันที
วิธีที่ 4: Power Query
อีกวิธีหนึ่งในการแก้ปัญหาของเราและสถานการณ์ทั่วไปคือการเอาแถวและคอลัมน์ว่างใน Power Query ออก
ขั้นแรก ให้โหลดตารางของเราลงใน Power Query Query Editor คุณสามารถแปลงเป็น "สมาร์ท" แบบไดนามิกด้วยแป้นพิมพ์ลัด Ctrl+T หรือเพียงแค่เลือกช่วงข้อมูลของเราและตั้งชื่อ (เช่น ข้อมูล) ในแถบสูตร แปลงเป็นชื่อ:
ตอนนี้เราใช้คำสั่ง ข้อมูล – รับข้อมูล – จากตาราง / ช่วง (ข้อมูล – รับข้อมูล – จากตาราง / ช่วง) และโหลดทุกอย่างลงใน Power Query:
จากนั้นทุกอย่างก็ง่าย:
- เราลบบรรทัดว่างด้วยคำสั่ง หน้าแรก - ลดบรรทัด - ลบบรรทัด - ลบบรรทัดว่าง (หน้าแรก - ลบแถว - ลบแถวว่าง)
- คลิกขวาที่ส่วนหัวของคอลัมน์เมืองแรกและเลือกคำสั่ง Unpivot Other Columns จากเมนูบริบท ตารางของเราจะเป็นตามที่เรียกอย่างถูกต้องในทางเทคนิค ปกติ – แปลงเป็นสามคอลัมน์: เมือง เดือน และค่าจากจุดตัดของเมือง และเดือนจากตารางเดิม ลักษณะเฉพาะของการดำเนินการนี้ใน Power Query คือการข้ามเซลล์ว่างในข้อมูลต้นฉบับ ซึ่งเป็นสิ่งที่เราต้องการ:
- ตอนนี้เราดำเนินการย้อนกลับ - เราเปลี่ยนตารางผลลัพธ์กลับเป็นตารางสองมิติเพื่อคืนกลับเป็นฟอร์มดั้งเดิม เลือกคอลัมน์ที่มีเดือนและบนแท็บ การแปลง เลือกทีม คอลัมน์เดือย (แปลง — คอลัมน์ Pivot). ในหน้าต่างที่เปิดขึ้น เป็นคอลัมน์ของค่า เลือกสุดท้าย (ค่า) และในตัวเลือกขั้นสูง – การดำเนินการ อย่ารวมกัน (อย่ารวม):
- มันยังคงอัปโหลดผลลัพธ์กลับไปที่ Excel ด้วยคำสั่ง หน้าแรก — ปิดและโหลด — ปิดและโหลดใน… (หน้าแรก — ปิด&โหลด — ปิด&โหลดไปที่…)
- มาโครคืออะไร ทำงานอย่างไร คัดลอกข้อความของมาโครได้ที่ไหน วิธีเรียกใช้แมโคร
- เติมเซลล์ว่างทั้งหมดในรายการด้วยค่าของเซลล์หลัก
- การลบเซลล์ว่างทั้งหมดออกจากช่วงที่กำหนด
- การลบแถวว่างทั้งหมดในแผ่นงานด้วย PLEX add-on