บทความนี้จะอธิบายวิธีที่มีคุณค่าบางอย่างเกี่ยวกับวิธีลบ เซลล์ว่าง (blanks) ออกจาก รายการตรวจสอบข้อมูล (data validation list) ใน Excel เมื่อเรามีเซลล์ว่างในรายการของแผนภูมิ Excel มันจะคงอยู่ใน รายการตรวจสอบข้อมูล ซึ่งเป็นสิ่งที่ไม่พึงประสงค์
ดังนั้นผมจะทำงานบนชุดข้อมูลต่อไปนี้เพื่อแสดงวิธีการลบ เซลล์ว่าง ออกจาก รายการตรวจสอบข้อมูล ใน Excel
ปัญหาเมื่อสร้างรายการตรวจสอบข้อมูลพร้อมเซลล์ว่าง
ลองดูว่าจะเกิดอะไรขึ้นถ้าเราสร้าง รายการแบบเลื่อนลง (drop down list) รวมเซลล์ว่าง ขั้นแรกเราต้องสร้าง รายการแบบเลื่อนลง
ขั้นตอน:
- เลือกเซลล์ C5
- จากนั้นเลือก ข้อมูล (Data) >> เครื่องมือข้อมูล (Data Tools) >> ตรวจสอบความถูกต้องของข้อมูล (Data Validation)
- กล่องโต้ตอบ dialog box จะแสดงขึ้น เลือก รายการ (List) จากแถบ อนุญาต (Allow) (แสดงในรูปต่อไปนี้)
- คลิกที่ ไอคอน ที่ทำเครื่องหมาย
- หลังจากนั้น เลือกเซลล์ B5 ถึง B14 และคลิกที่ ไอคอน ที่ทำเครื่องหมาย
- จากนั้น คลิก ตกลง (OK)
ด้วยวิธีนี้ เราสร้าง รายการแบบเลื่อนลง ของเราแล้ว
ที่นี่ คุณจะสังเกตว่า รายการแบบเลื่อนลง ถูกสร้างพร้อมกับ เซลล์ว่าง ตอนนี้ผมจะอธิบายวิธีการตัดเซลล์ว่างเหล่านี้ออกจาก รายการแบบเลื่อนลง
5 วิธีลบเซลล์ว่างออกจากรายการตรวจสอบข้อมูลใน Excel
1. ลบเซลล์ว่างจากรายการตรวจสอบข้อมูลโดยใช้ฟังก์ชัน OFFSET
นี่เป็นวิธีที่คุณสามารถสร้างพื้นที่มากขึ้นสำหรับ รายการแบบเลื่อนลง โดยไม่มีเซลล์ว่างในคอลัมน์นั้น ขั้นแรกคุณต้อง กรอง (filter) เซลล์ว่าง (blanks) ออกจากข้อมูลของคุณ มาสนทนากันเกี่ยวกับกระบวนการ
เราต้องปรับเปลี่ยนชุดข้อมูลของเราบ้าง
- เพิ่ม คอลัมน์ใหม่ หน้าคอลัมน์ที่ใช้สำหรับ รายการแบบเลื่อนลง เราตั้งชื่อคอลัมน์ใหม่และคอลัมน์ที่ใช้สำหรับรายการแบบเลื่อนลงว่า Name List with Blanks และ List without Blanks ตามลำดับ (ดูวิธีสร้าง รายการแบบเลื่อนลง ที่ ส่วน 1)
ขั้นตอน:
- ขั้นแรก พิมพ์สูตรต่อไปนี้ในเซลล์ C5
=FILTER(B5:B14,B5:B14<>"")
ที่นี่ ฟังก์ชัน FILTER จะรับ ช่วง B5:B14 และตรวจสอบ เซลล์ว่าง ระหว่าง ช่วง จากนั้น กรอง เซลล์ว่างหรือเปล่า ออกจาก รายการ
- กด ENTER คุณจะเห็น รายชื่อ โดยไม่มี เซลล์ว่าง
- หลังจากนั้น เลือก Name Manager จาก แท็บ Formula และคลิก New
- ตั้งชื่อให้ ช่วง (range) ผมจะใช้ NameNonBlanks เป็นชื่อของ ช่วง
- และเขียนสูตรต่อไปนี้ใน Refers to
=OFFSET(offset!$C$5,0,0,COUNTA(offset!$C$4:$C$16)-1,1)
ในสถานการณ์นี้ เราต้องการใช้เซลล์เพิ่มเติมสำหรับป้อนชื่อใหม่ แต่ไม่ต้องการ เซลล์ว่าง ใน รายการแบบเลื่อนลง สำหรับพื้นที่เหล่านั้น ที่นี่เราสร้างรายการใหม่สำหรับ รายการตรวจสอบข้อมูล จาก C12 ถึง C16 โดยใส่สูตรนั้น จดไว้ว่า 'offset! ' หมายถึงชื่อ แผ่นงาน (sheet) ที่เราใช้
- คลิก ตกลง (OK) จะเห็น หน้าต่าง (Window) แค่ ปิด มัน
- จากนั้น เลือกเซลล์ D5 และเลือก ข้อมูล (Data) >> รายการตรวจสอบข้อมูล (Data Validation List)
- เปลี่ยน ชื่อแหล่งที่มา (Source Name) เป็น =NameNonBlanks
- คลิก ตกลง (OK)
- เลือกแถบ รายการแบบเลื่อนลง ในเซลล์ D5 คุณจะเห็นรายการ ชื่อ ที่เราใช้
- ตอนนี้ พิมพ์ชื่อใหม่ๆ ตั้งแต่เซลล์ C12 ถึง C16
- จากนั้น เลือกเซลล์ data validation list D5
คุณจะเห็น ชื่อใหม่ ใน รายการแบบเลื่อนลง ของคุณ คุณไม่สามารถเห็นรายการใหม่ภายใต้เซลล์ C16 ได้ เพราะมันไม่อยู่ใน ช่วง (range) ของคุณ
ด้วยการปฏิบัติตามวิธีนี้ คุณสามารถสร้าง พื้นที่ว่าง สำหรับรายการใหม่ใน รายการตรวจสอบข้อมูล โดยไม่ทำให้มี เซลล์ว่าง ในมัน
อ่านเพิ่มเติม: วิธีสร้างรายการแบบเลื่อนลง Excel สำหรับการตรวจสอบข้อมูล (8 วิธี)
2. ใช้คำสั่ง Go To Special เพื่อลบเซลล์ว่างออกจากรายการ
เราสร้าง รายการแบบเลื่อนลง (ส่วน 1) คุณเห็นว่ามี เซลล์ว่าง คงอยู่ ในการลบพวกมัน เราสามารถทำตามขั้นตอนต่อไปนี้ได้อย่างง่ายดาย
ขั้นตอน:
- เลือกเซลล์ B5 ถึง B14 แล้วเลือก หน้าแรก (Home) >> ค้นหาและเลือก (Find & Select) >> Go To Special
- หลังจากนั้น เลือก เซลล์ว่าง (Blanks) แล้วคลิก ตกลง (OK)
- การดำเนินการนี้จะเลือก เซลล์ว่าง
- ตอนนี้ เลือกเซลล์ว่างใดๆ คลิกขวา และเลือก ลบ (Delete) เพื่อ ลบเซลล์ว่าง
- คุณจะเห็น กล่องโต้ตอบ เลือก Shift Cells Up และคลิก ตกลง (OK)
- การดำเนินการนี้จะลบ เซลล์ว่าง ออกจาก รายการเดิม และ รายการแบบเลื่อนลง ด้วย
ด้วยการปฏิบัติตามวิธีนี้ คุณสามารถลบ เซลล์ว่าง หรือ เซลล์เปล่า ออกจาก รายการแบบเลื่อนลง ได้อย่างง่ายดาย
อ่านเพิ่มเติม: สร้างรายการตรวจสอบข้อมูลแบบเลื่อนลงพร้อมการเลือกหลายรายการใน Excel
3. ใช้ฟังก์ชัน FILTER ของ Excel เพื่อลบเซลล์ว่างออกจากรายการตรวจสอบข้อมูล
เราสามารถใช้ฟังก์ชัน FILTER ในด้านนี้ เราจะใช้ชุดข้อมูลจาก ส่วน 2 ดูวิธีสร้าง รายการแบบเลื่อนลง ที่ ส่วน 1
ขั้นตอน:
- พิมพ์สูตรต่อไปนี้ในเซลล์ C5
=FILTER(B5:B14,B5:B14<>"")
ที่นี่ ฟังก์ชัน FILTER จะรับ ช่วง B5:B14 และตรวจสอบ เซลล์ว่าง ระหว่าง ช่วง จากนั้น กรอง เซลล์ว่างหรือเปล่า ออกจาก รายการ
- กดปุ่ม ENTER และคุณจะเห็น รายชื่อ โดยไม่มี เซลล์ว่าง
- แต่ถ้าคุณไปที่ Drop Down List คุณจะยังเห็นว่ามันมี เซลล์ว่าง จาก คอลัมน์ C
- ดังนั้น เพื่อลบ เซลล์ว่าง เหล่านี้ ไปที่ Data Validation จาก แท็บ Data
- เปลี่ยนเซลล์สุดท้ายของ ช่วง (range) เป็น C11 เนื่องจาก รายการที่กรองแล้ว มี ช่วง C5 ถึง C11 ใน Source
- คลิก ตกลง (OK) ตอนนี้คุณจะไม่มี เซลล์ว่าง ใน รายการแบบเลื่อนลง แล้ว
นี่เป็นวิธيที่มีประสิทธิภาพค่อนข้างสูงในการลบ เซลล์ว่าง ออกจาก รายการแบบเลื่อนลง
อ่านเพิ่มเติม: Excel Data Validation Drop Down List with Filter (2 Examples)
บทความคล้ายกัน:
- วิธีใช้สูตร VLOOKUP แบบกำหนดเองใน Data Validation ของ Excel
- [Fixed] Data Validation Not Working for Copy Paste in Excel (with Solution)
- วิธีสร้างรายการตรวจสอบข้อมูลจากตารางใน Excel (3 วิธี)
- ใช้ Data Validation หลายอย่างในเซลล์เดียวใน Excel (3 ตัวอย่าง)
- Excel Data Validation Alphanumeric Only (Using Custom Formula)
4. รวมฟังก์ชัน IF, COUNTIF, ROW, INDEX และ SMALL เพื่อลบเซลล์ว่างจากรายการตรวจสอบข้อมูล
เรายังสามารถใช้การรวมของฟังก์ชัน IF, COUNTIF, ROW, INDEX และ SMALL เพื่อลบเซลล์ว่างออกจาก รายการตรวจสอบข้อมูล มันจะซับซ้อนนิดหน่อย เราจะใช้ชุดข้อมูลจาก ส่วน 2 และดูวิธีสร้าง รายการแบบเลื่อนลง ที่ ส่วน 1
ขั้นตอน:
- ขั้นแรก พิมพ์สูตรต่อไปนี้ในเซลล์ C5
=IF(COUNTIF($B$5:$B$14,"?*")<ROW(B5)-4,"",INDEX(B:B,SMALL(IF(B$5:B$14<>"",ROW(B$5:B$14)),ROWS(B$5:B5))))
สูตรมี 2 ส่วนหลัก ส่วนแรกคือ COUNTIF($B$5:$B$14,"?*")<ROW(B5)-4,"" และส่วนที่สองคือ INDEX(B:B,SMALL(IF(B$5:B$14<>"",ROW(B$5:B$14)),ROWS(B$5:B5)))
- ฟังก์ชัน COUNTIF นับ ข้อความที่ไม่ใช่เซลล์ว่าง ที่นี่ และ đóคือเหตุผลที่เราได้ 7 ชื่อ ใน คอลัมน์ C
- ฟังก์ชัน ROW คืน หมายเลขแถว ของ เซลล์ และเซลล์ว่างของเราอยู่ที่ตำแหน่ง 5 จากเซลล์ B5 เราลบ 4 เพราะเราต้องการให้มันน้อยกว่านั้น
- กด ENTER
- ใช้ Fill Handle เพื่อ AutoFill เซลล์ด้านล่าง
- ตอนนี้เรามี รายชื่อ โดยไม่มี เซลล์ว่าง แต่ถ้าเราคลิกที่ data validation list เรายังคงเห็น เซลล์ว่าง ใน drop down list
- และเพื่อลบ เซลล์ว่าง เหล่านี้ ไปที่ Data Validation จาก แท็บ Data
- เปลี่ยนเซลล์สุดท้ายของ ช่วง (range) เป็น C11 เนื่องจาก รายการที่กรองแล้ว มี ช่วง C5 ถึง C11 ใน Source
- คลิก ตกลง (OK) ตอนนี้คุณจะไม่มี เซลล์ว่าง ใน รายการแบบเลื่อนลง แล้ว
ดังนั้น คุณสามารถทำ รายการตรวจสอบข้อมูล โดยไม่มี เซลล์ว่าง ได้
อ่านเพิ่มเติม: ค่าเริ่มต้นในรายการตรวจสอบข้อมูลกับ Excel VBA (Macro และ UserForm)
5. ใช้ฟังก์ชันรวมเพื่อลบเซลล์ว่างออกจากรายการตรวจสอบข้อมูล
เรายังสามารถใช้สูตรซ้อนกันร่วมกับฟังก์ชัน ADDRESS, INDIRECT, COUNTBLANK, IF และ SMALL มาสนทนากันเกี่ยวกับขั้นตอน เราจะใช้ชุดข้อมูลจาก ส่วน 2 คุณยังสามารถไปที่ ส่วน 1 เพื่อดูวิธีสร้าง รายการแบบเลื่อนลง/รายการตรวจสอบข้อมูล
ขั้นตอน:
- ขั้นแรก พิมพ์สูตรต่อไปนี้ในเซลล์ C5
=IF(ROW()-ROW($B$5:$B$14)+1>ROWS($B$5:$B$14)-COUNTBLANK($B$5:$B$14),"", INDIRECT(ADDRESS(SMALL((IF($B$5:$B$14<>"",ROW($B$5:$B$14),ROW()+ROWS($B$5:$B$14))),ROW()-ROW($C$5:$C$14)+1),COLUMN($B$5:$B$14),4)))
ที่นี่ ผมจะอธิบายอย่างง่ายๆ ว่าสูตรนี้ทำงานอย่างไร มันจะตรวจสอบ ช่วง B5:B14 และตรวจสอบ เซลล์ว่าง ด้วยความช่วยเหลือของฟังก์ชัน COUNTBLANK จากนั้นมันก็ตรวจสอบว่เซลล์ไหนไม่ใช่ เซลล์ว่าง ทั่ว B5:B14 และดังนั้นจึงคืน เซลล์ที่ไม่ว่าง
- กด CTRL + SHIFT + ENTER (เพราะเป็น สูตรอาร์เรย์) และคุณจะเห็นผลลัพธ์ในเซลล์ C5 ดังด้านล่าง
- ตอนนี้ ใช้ Fill Handle เพื่อ AutoFill เซลล์ด้านล่าง
- แต่ถ้าคุณไปที่ Drop Down List คุณจะยังเห็นว่ามันมี เซลล์ว่าง จาก คอลัมน์ C
- และเพื่อลบ เซลล์ว่าง เหล่านี้ ไปที่ Data Validation จาก แท็บ Data
- เปลี่ยนเซลล์สุดท้ายของ ช่วง (range) เป็น C11 เนื่องจาก รายการที่กรองแล้ว มี ช่วง C5 ถึง C11 ใน Source
- คลิก ตกลง (OK) ตอนนี้คุณจะไม่มี เซลล์ว่าง ใน รายการแบบเลื่อนลง แล้ว
นี่เป็นอีกวิธีหนึ่งที่คุณสามารถทำ รายการแบบเลื่อนลง ให้ libero จาก เซลล์ว่าง ได้
อ่านเพิ่มเติม: วิธีใช้คำสั่ง IF ในสูตร Data Validation ใน Excel (6 วิธี)
ส่วนฝึกปฏิบัติ
ในส่วนนี้ ผมจะให้ชุดข้อมูลเพื่อให้คุณฝึกปฏิบัติวิธีเหล่านี้ด้วยตัวเอง
สรุป
โดยสรุป ผมพยายามอธิบายวิธีบางอย่างในการลบเซลล์ว่างจาก รายการตรวจสอบข้อมูล ใน Excel วิธีเหล่านี้ซับซ้อนนิดหน่อย แต่ผมพยายามอธิบายให้เข้าใจง่ายที่สุดเท่าที่จะเป็นไปได้ ผมขอให้ท่านแสดงความคิดเห็นเกี่ยวกับบทความนี้ในส่วนความคิดเห็น และถ้าคุณมีไอเดียหรือคำถามของตัวเอง ก็ใส่ไว้ในกล่องความคิดเห็นด้วยนะครับ
บทความที่เกี่ยวข้อง
- ใช้ Data Validation แบบกำหนดเองสำหรับเกณฑ์หลายอย่างใน Excel (4 ตัวอย่าง)
- วิธีใช้ Data Validation ใน Excel พร้อมสี (4 วิธี)
- วิธีใช้รายการตรวจสอบข้อมูลจากแผ่นงานอื่น (6 วิธี)
- Excel VBA สร้างรายการตรวจสอบข้อมูลจากอาร์เรย์
- วิธีใช้ Named Range สำหรับ Data Validation List กับ VBA ใน Excel
