ขณะทำงานกับ การตรวจสอบข้อมูล (Data Validation) ใน Excel คุณอาจต้องการลบรายการที่ใช้แล้วออกจาก รายการแบบเลื่อนลง (Drop-down List) เพื่อ ป้องกันการมอบหมายรายการเดียวกันซ้ำ ตัวอย่างเช่น คุณอาจต้องมอบหมายพนักงานหลายคนไปทำกะทำงานต่างกัน และไม่ต้องการให้พนักงานคนใดถูกมอบหมายซ้ำ อีกกรณีคือ การมอบหมายผู้เล่นไปตำแหน่งต่างๆ ในเกมคะแนน และต้องมอบหมายผู้เล่นแต่ละคนให้ตำแหน่งเฉพาะ ในกรณีเหล่านี้ หากคุณมีรายการแบบเลื่อนลงสำหรับมอบหมายพนักงานหรือผู้เล่น คุณอาจต้องการลบชื่อพนักงานหรือผู้เล่นออกจากรายการทันทีที่เขา/เธอถูกมอบหมายแล้ว ในบทช่วยสอนนี้ จะแสดงวิธีการลบรายการที่ใช้แล้วออกจากรายการแบบเลื่อนลงใน Excel
ดาวน์โหลดสมุดฝึกปฏิบัติเพื่อฝึกงานขณะอ่านบทความนี้
2 วิธีง่ายๆ ในการลบรายการที่ใช้แล้วออกจากรายการแบบเลื่อนลงใน Excel
สมมติสถานการณ์ที่เรามีเวิร์กชีต Excel ที่มี ชื่อพนักงาน ขององค์กร คุณต้องมอบหมายพนักงานแต่ละคนไปทำกะทำงานที่ต่างกัน และไม่ต้องการมอบหมายพนักงานคนใดซ้ำ ดังนั้น คุณจึงต้องการรายการแบบเลื่อนลงที่มีชื่อพนักงาน ซึ่งจะลบพนักงานออกโดยอัตโนมัติเมื่อเขา/เธอถูกมอบหมายงานแล้ว ผมจะใช้เวิร์กชีตนี้เพื่อแสดง 2 วิธีง่ายๆ ในการ ลบรายการที่ใช้แล้วออกจากรายการแบบเลื่อนลง ภาพด้านล่างแสดงเวิร์กชีตที่เราจะทำงาน ซึ่งมีรายการแบบเลื่อนลงที่ลบรายการที่ใช้แล้วออกไปแล้ว
วิธีที่ 1: ใช้คอลัมน์ช่วย (Helper Columns) เพื่อลบรายการที่ใช้แล้วออกจากรายการแบบเลื่อนลงใน Excel
วิธีง่ายๆ ในการลบรายการที่ใช้แล้วคือการใช้ คอลัมน์ช่วย 2 คอลัมน์ ให้ดูวิธีการทำกัน
ขั้นตอนที่ 1:
- ก่อนอื่น ให้เขียนสูตรต่อไปนี้ในเซลล์ C5 ภายใต้หัวข้อ Row Number
=IF(COUNTIF($F$5:$F$14,B5)>=1,"",ROW())
การแยกวิเคราะห์สูตร:
- ฟังก์ชัน IF จะทดสอบเงื่อนไข COUNTIF($F$5:$F$14, B5)>=1
- ฟังก์ชัน COUNTIF จะตรวจสอบว่าเซลล์ B5 ปรากฏใน ช่วงอ้างอิงแบบสัมบูรณ์ $F$5:$F$14 มากกว่าหนึ่งครั้งหรือไม่
- ถ้าเซลล์ B5 ปรากฏ หนึ่งครั้ง หรือ มากกว่า ในช่วง $F$5:$F$14 ฟังก์ชัน IF จะคืนค่า สตริงว่าง ("")
- มิฉะนั้น ฟังก์ชัน IF จะคืนค่า หมายเลขแถว ของเซลล์ B5 โดยใช้ ROW
- จากนั้น กด ENTER จะได้ หมายเลขแถว ของเซลล์ B5 อยู่ที่เซลล์ C5
- ตอนนี้ ให้ลาก fill-handle ของเซลล์ C5 ลงไป เพื่อ ใช้สูตร กับ เซลล์ที่เหลือ ในคอลัมน์ Row Number
- สุดท้าย เราจะได้ หมายเลขแถว ทั้งหมดของเซลล์ในคอลัมน์ Employee
ขั้นตอนที่ 2:
- ต่อไป ให้เขียนสูตรต่อไปนี้ในเซลล์ D5 ภายใต้หัวข้อ Employee Name
=IF(ROW(B5)-ROW(B$5)+1>COUNT(C$5:C$14),"",INDEX(B:B,SMALL(C$5:C$14,1+ROW(B5)-ROW(B$5))))
การแยกวิเคราะห์สูตร:
- ฟังก์ชัน IF ทดสอบเงื่อนไข ROW(B5)-ROW(B$5)+1>COUNT(C$5:C$14)
- ฟังก์ชัน COUNT นับจำนวนเซลล์ในช่วงอ้างอิงแบบสัมบูรณ์ C$5:C$14
- ฟังก์ชัน SMALL หา ค่าที่น้อยที่สุดลำดับ k ในช่วง C$5:C$14 โดย k ถูกกำหนดโดย 1+ROW(B5)-ROW(B$5)
- ฟังก์ชัน INDEX นำค่าที่น้อยที่สุดลำดับ k จากช่วง C$5:C$14 ที่ฟังก์ชัน SMALL หาได้ มาเป็นอาร์กิวเมนต์ row_num เพื่อคืนค่า อ้างอิงเซลล์
- จากนั้น กด ENTER จะได้ ชื่อพนักงาน ของเซลล์ B5 อยู่ที่เซลล์ D5
- ลาก fill-handle ของเซลล์ D5 ลงไป เพื่อใช้สูตรกับเซลล์ที่เหลือในคอลัมน์ Employee Name
- สุดท้าย เราจะได้ ชื่อพนักงาน ทั้งหมดในคอลัมน์ Employee
ขั้นตอนที่ 3:
- ต่อไป ให้คลิก Define Name ภายใต้แท็บ Formulas
- หน้าต่าง Edit Name จะปรากฏ ให้ใส่ Employee ในกล่อง Name
- จากนั้น ใส่สูตรด้านล่างในกล่อง Refers to
=Helper!$B$4:$D$14=OFFSET(Helper!$D$5,0,0, COUNTA(Helper!$D$5:$D$14)-COUNTBLANK(Helper!$D$5:$D$14),1)การแยกวิเคราะห์สูตร:
- Helper คือชื่อ เวิร์กชีต ที่เรากำลังทำงานอยู่
- ฟังก์ชัน COUNTA นับ ค่าของเซลล์ ทั้งหมดในช่วง $D$5:$D$14
- ฟังก์ชัน COUNTBLANK นับ จำนวน เซลล์ว่างในช่วง $D$5:$D$14
- หลังจากนั้น กด OK
ขั้นตอนที่ 4:
- ต่อไป ให้เลือกเซลล์ทั้งหมดในคอลัมน์ Drop-Down เพื่อสร้าง รายการแบบเลื่อนลง
- คลิก Data Validation ภายใต้แท็บ Data
- เลือก Data Validation จากเมนูแบบเลื่อนลง
- หน้าต่าง Data Validation จะปรากฏ ให้เลือก List จากเมนู Allow
- ใส่ =Employee ในกล่อง Source
- กด OK
- ตอนนี้จะเห็น รายการแบบเลื่อนลง ในทุกเซลล์ของคอลัมน์ Drop-Down
- ลองเลือกชื่อ Gus Fring จากรายการในเซลล์ F5
- ถ้าคลิกที่ รายการแบบเลื่อนลงตัวที่สอง จะเห็นว่า Gus Fring ไม่อยู่ในรายการแล้ว เพราะถูกใช้ไปแล้ว จึงถูกลบออกจากรายการถัดไป
- ถ้าเลือกชื่อจากรายการแบบเลื่อนลงอื่นๆ อีก รายการที่เลือกไปแล้วจะถูกลบออกจากรายการถัดไปเรื่อยๆ
อ่านเพิ่มเติม: วิธีสร้างรายการแบบเลื่อนหลายคอลัมน์ใน Excel (3 วิธี)
บทความที่คล้ายกัน:
- วิธีสร้าง Multi Select Listbox ใน Excel
- รายการแบบเลื่อนลงของ Excel ขึ้นอยู่กับการเลือก
- วิธีเชื่อมโยงค่าเซลล์กับรายการแบบเลื่อนลงใน Excel (5 วิธี)
- รายการแบบเลื่อนลงแบบมีเงื่อนไขใน Excel (สร้าง, เรียงลำดับ และใช้งาน)
- วิธีสร้างรายการแบบเลื่อนลงแบบไดนามิกที่ขึ้นอยู่กับกันใน Excel
วิธีที่ 2: ลบรายการที่ใช้แล้วออกจากรายการแบบเลื่อนลงใน Excel โดยรวมฟังก์ชัน FILTER และ COUNTIF
ถ้าคุณมีสิทธิ์ใช้ Microsoft Office 365 วิธีที่ง่ายที่สุดคือใช้ฟังก์ชัน FILTER ที่เป็นของเฉพาะ Excel 365 เพื่อลบรายการที่ใช้แล้วออกจากรายการแบบเลื่อนลง เราต้องทำตามขั้นตอนด้านล่าง
ขั้นตอนที่ 1:
- ก่อนอื่น ให้เขียนสูตรต่อไปนี้ในเซลล์ C5 ภายใต้หัวข้อ Row Number
=FILTER(B5:B14, COUNTIF(E5:E14,B5:B14)=0)
การแยกวิเคราะห์สูตร:
- ฟังก์ชัน FILTER ให้เรา กรอง ช่วง B5:B14 ตาม เกณฑ์ COUNTIF(E5:E14, B5:B14)=0
- ฟังก์ชัน COUNTIF ตรวจสอบว่าช่วง B5:B14 ปรากฏในช่วง E5:E14 หรือ ไม่
- จากนั้น กด ENTER จะได้ ชื่อพนักงาน ทั้งหมดในคอลัมน์ Employee
ขั้นตอนที่ 2:
- เลือกเซลล์ทั้งหมดในคอลัมน์ Drop-Down เพื่อสร้าง รายการแบบเลื่อนลง
- คลิก Data Validation ภายใต้แท็บ Data
- เลือก Data Validation จากเมนูแบบเลื่อนลง
- หน้าต่าง Data Validation จะปรากฏ ให้เลือก List จากเมนู Allow
- ใส่ $C$5:$C$14 ในกล่อง Source หรือใช้ =$C$5# ก็ได้
- กด OK
- ตอนนี้จะเห็น รายการแบบเลื่อนลง ในทุกเซลล์ของคอลัมน์ Drop-Down
- เลือกชื่อ Stuart Bloom จากรายการในเซลล์ F5
- ถ้าคลิกที่ รายการแบบเลื่อนลงตัวที่สอง จะเห็นว่า Stuart Bloom ไม่อยู่ในรายการแล้ว เพราะถูกใช้ไปแล้ว
- ถ้าเลือกชื่อจากรายการอื่นๆ อีก รายการที่เลือกไปจะถูกลบออกจากรายการถัดไปเรื่อยๆ
อ่านเพิ่มเติม: การสร้างตัวกรองแบบเลื่อนลงเพื่อดึงข้อมูลตามการเลือกใน Excel
บันทึกสำคัญ
🎯 ฟังก์ชัน FILTER เป็นฟังก์ชันเฉพาะที่มีอยู่เฉพาะใน Excel 365 เท่านั้น จึงจะไม่ทำงานถ้าคุณไม่มี Excel 365 บนคอมพิวเตอร์
🎯 และ อ่านบทความนี้ เพื่อเรียนรู้วิธีสร้างรายการแบบเลื่อนลงด้วยค่าที่ไม่ซ้ำกันใน Excel
สรุป
ในบทความนี้ เราได้เรียนรู้ วิธีลบรายการที่ใช้แล้วออกจากรายการแบบเลื่อนลงใน Excel หวังว่า ตอนนี้คุณสามารถ ลบรายการที่ใช้แล้วออกจากรายการแบบเลื่อนลงใน Excel ได้ง่ายแล้ว อย่างไรก็ตาม หากมีข้อสงสัยหรือข้อเสนอแนะเกี่ยวกับบทความนี้ กรุณาแสดงความคิดเห็นด้านล่างได้เลย ขอให้มีวันที่ดี!!!
บทความที่เกี่ยวข้อง
- สร้างรายการแบบเลื่อนลงที่ค้นหาได้ใน Excel (2 วิธี)
- วิธีสร้างรายการแบบเลื่อนลงของ Excel พร้อมสี (2 วิธี)
- รายการแบบเลื่อนลงของ Excel ไม่ทำงาน (8 ปัญหาและวิธีแก้)
- วิธีสร้างรายการจากช่วงใน Excel (3 วิธี)
- อัปเดตรายการแบบเลื่อนลงใน Excel อัตโนมัติ (3 วิธี)
