ในบทความนี้ ผมจะพูดถึงวิธีกรองข้อมูล excel โดยใช้ การตรวจสอบข้อมูล รายการแบบหล่นลง โดยปกติใน Microsoft Excel เราใช้ ตัวกรอง ตัวเลือกในการดึงข้อมูลเฉพาะ อย่างไรก็ตาม คุณสามารถใช้รายการดรอปดาวน์เพื่อกรองข้อมูลได้ ในการทำงาน ขั้นแรก ฉันจะสร้างรายการดรอปดาวน์โดยใช้ การตรวจสอบข้อมูล ในเอ็กเซล ภายหลังจากการเลือกรายการแบบเลื่อนลง ฉันจะกรองแถวที่เกี่ยวข้องออก
สามารถดาวน์โหลดแบบฝึกหัดที่เราได้ใช้ในการจัดทำบทความนี้
2 ตัวอย่างการใช้รายการแบบหล่นลงการตรวจสอบความถูกต้องของข้อมูล Excel พร้อมตัวกรอง
มาพิจารณากัน เรามีชุดข้อมูลที่มีข้อมูลการขายผลไม้หลายชนิดตามพื้นที่ ในบทความนี้ ฉันจะสร้างการตรวจสอบความถูกต้องของข้อมูล รายการดรอปดาวน์ของพื้นที่ที่กล่าวถึงในชุดข้อมูล จากนั้นฉันจะใช้รายการแบบเลื่อนลงเพื่อวาดข้อมูลการขายผลไม้
1. กรองค่าจากรายการดรอปดาวน์การตรวจสอบความถูกต้องของข้อมูลโดยใช้คอลัมน์ตัวช่วย
ในวิธีนี้ฉันจะเพิ่ม 3 คอลัมน์ตัวช่วยในชุดข้อมูลหลัก ต่อมา ฉันจะวาดข้อมูลตามการเลือกแบบเลื่อนลง ก่อนป้อนสูตรตัวช่วย ฉันจะสร้างรายการดรอปดาวน์ที่มีพื้นที่ . ทำตามขั้นตอนด้านล่างเพื่อทำงาน
ขั้นตอน:
- ก่อนสร้างรายการแบบเลื่อนลง ให้ระบุ พื้นที่ . ที่ไม่ซ้ำกันทั้งหมด ดังต่อไปนี้
- จากนั้น คลิกเซลล์ที่คุณต้องการค้นหารายการแบบเลื่อนลง (ที่นี่ เซลล์ H5 )
- จาก Excel Ribbon , ไปที่ข้อมูล> เครื่องมือข้อมูล> การตรวจสอบข้อมูล> การตรวจสอบข้อมูล .
- ด้วยเหตุนี้ การตรวจสอบความถูกต้องของข้อมูล กล่องโต้ตอบจะปรากฏขึ้น จากนั้นไปที่ การตั้งค่า แท็บ เลือก รายการ จาก อนุญาต ส่วนและระบุ ที่มา . หลังจากนั้นกด ตกลง .
- เมื่อกด ตกลง , ที่นี่เราได้รับรายการแบบเลื่อนลง
- ตอนนี้ มาที่คอลัมน์ผู้ช่วย พิมพ์สูตรด้านล่างในคอลัมน์ตัวช่วยแรก (ใน เซลล์ D5 ) โดยใช้ ฟังก์ชัน ROWS . กด Enter และใช้ Fill Handle (+ ) เครื่องมือคัดลอกสูตรไปทั่วทั้งคอลัมน์
=ROWS($A5:A$5)
- ดังนั้น เราจะได้ผลลัพธ์ด้านล่าง
- ต่อไป ใช้ฟังก์ชัน IF . ต่อไปนี้ สูตรสำหรับคอลัมน์ตัวช่วยที่ 2 (ตัวช่วย 2 )
=IF(C5=$H$5,D5,"")
- และสำหรับคอลัมน์ตัวช่วยที่ 3 (ตัวช่วย 3 ) ใช้สูตรด้านล่าง
=IFERROR(SMALL($E$5:$E$14,D5),"")
ที่นี่ ฟังก์ชัน SMALL ส่งคืนค่าที่น้อยที่สุดเป็นลำดับที่ k ในช่วง E5:E14 . ต่อมา ฟังก์ชัน IFERROR คืนค่าว่างหากผลลัพธ์ของ เล็ก สูตรมีข้อผิดพลาด
- ตอนนี้ สมมติว่าบัลติมอร์ ฉันต้องการกรองข้อมูลการขายผลไม้ที่เกี่ยวข้องทั้งหมด เพื่อให้ได้ผลลัพธ์ที่คาดหวัง พิมพ์สูตรด้านล่างใน เซลล์ J5 แล้วกด Enter .
=IFERROR(INDEX($A$5:$C$14,$F5,COLUMNS($J$5:J5)),"")
ที่นี่ ฟังก์ชัน INDEX ดึงข้อมูลตามหมายเลขว้าว จากนั้นฟังก์ชัน COLUMNS ส่งกลับหมายเลขคอลัมน์ในช่วง $J$5:J5 . สุดท้าย IFERROR ฟังก์ชันจะคืนค่าว่างหากผลลัพธ์เป็นข้อผิดพลาด
- เมื่อคุณป้อนสูตรข้างต้น ผลลัพธ์ต่อไปนี้จะเป็นผลลัพธ์ ลาก เติมแฮนเดิล ทางด้านขวาเพื่อรับข้อมูลทั้งหมดเป็นแถว
- จากนั้นลาก Fill Handle ด้านล่างและรับข้อมูลการขายผลไม้ขั้นสุดท้ายสำหรับ บัลติมอร์ พื้นที่
- ตอนนี้ ถ้าคุณเลือก ฟีนิกซ์ พื้นที่จากรายการแบบเลื่อนลง แถวที่สอดคล้องกับ ฟีนิกซ์ จะถูกกรองดังนี้
อ่านเพิ่มเติม: วิธีสร้างรายการแบบหล่นลงของ Excel สำหรับการตรวจสอบความถูกต้องของข้อมูล (8 วิธี)
การอ่านที่คล้ายกัน
- รายการแบบหล่นลงการตรวจสอบความถูกต้องของข้อมูลด้วย VBA ใน Excel (7 แอปพลิเคชัน)
- ค่าเริ่มต้นในรายการตรวจสอบข้อมูลด้วย Excel VBA (มาโครและฟอร์มผู้ใช้)
- วิธีลบช่องว่างออกจากรายการตรวจสอบข้อมูลใน Excel (5 วิธี)
- [แก้ไข] การตรวจสอบข้อมูลไม่ทำงานสำหรับการคัดลอกวางใน Excel (พร้อมโซลูชัน)
- วิธีใช้สูตร VLOOKUP แบบกำหนดเองในการตรวจสอบข้อมูล Excel
2. ฟังก์ชัน Excel FILTER เพื่อดึงข้อมูลตามรายการแบบหล่นลงการตรวจสอบความถูกต้องของข้อมูล
หากคุณกำลังทำงานใน Excel 365 คุณสามารถกรองข้อมูลโดยใช้ ฟังก์ชัน FILTER . ก่อนเริ่มกระบวนการ ฉันได้แปลงช่วงข้อมูลเป็นตาราง excel โดยกด Ctrl + T เนื่องจากถ้าคุณเพิ่มระเบียนใหม่ลงในตาราง รายการแบบเลื่อนลงจะได้รับการอัปเดตตามข้อมูลที่เพิ่มใหม่
- เพื่อความสะดวกในการใช้งาน ฉันจะตั้งชื่อให้กับตารางที่สร้างขึ้นใหม่ (เช่น Table4 )
ตอนนี้ ให้ทำตามขั้นตอนด้านล่างเพื่อทำงานหลัก
ขั้นตอน:
- ขั้นแรก เราจะสร้างรายการพื้นที่ที่ไม่ซ้ำกันโดยใช้ ฟังก์ชัน UNIQUE . โดยพิมพ์สูตรต่อไปนี้ใน เซลล์ F5 แล้วกด Enter .
=SORT(UNIQUE(Table4[Area]))
ที่นี่ ฉันได้ใช้ ฟังก์ชัน SORT พร้อมกับ UNIQUE ฟังก์ชั่นเพื่อจัดเรียง พื้นที่ . ด้านบน ข้อมูล
- เมื่อป้อนสูตรนี่คือผลลัพธ์ที่เราได้รับ สูตรด้านบนส่งคืนข้อมูลที่ไม่ซ้ำที่จัดเรียงเป็นอาร์เรย์ (ระบุเป็นสีน้ำเงิน)
- สร้างรายการแบบเลื่อนลงในเซลล์ H5 . ปฏิบัติตามเส้นทางด้านล่างเพื่อนำการตรวจสอบข้อมูล กล่องโต้ตอบ:ข้อมูล> เครื่องมือข้อมูล> การตรวจสอบข้อมูล> การตรวจสอบข้อมูล . จากกล่องโต้ตอบนั้น ให้เลือก รายการ จาก อนุญาต ส่วนและพิมพ์สูตรด้านล่างใน ที่มา สนาม. จากนั้นกด ตกลง .
=F5#
ที่นี่ # สัญลักษณ์แสดงว่าเรากำลังพิจารณาอาร์เรย์ทั้งหมดของ เซลล์ F5 เป็นแหล่งที่มาของรายการแบบเลื่อนลง
- เมื่อคุณกด ตกลง , รายการดรอปดาวน์ด้านล่างจะถูกสร้างขึ้น
- มาพิจารณากัน ฉันต้องการวาดข้อมูลการขายผลไม้สำหรับลองบีช พื้นที่. เพื่อให้ได้ผลลัพธ์ที่ต้องการ พิมพ์สูตรด้านล่างใน เซลล์ F11 แล้วกด Enter .
=FILTER(Table4,Table4[Area]=H5,"No Data Found")
- สุดท้าย เมื่อเข้าสู่ FILTER สูตรเราจะได้ข้อมูลการขายทั้งหมดสำหรับลองบีช พื้นที่. คุณสามารถเปลี่ยนพื้นที่จากรายการแบบเลื่อนลงและกรองแถวที่เกี่ยวข้องตามพื้นที่ที่เลือกได้
อ่านเพิ่มเติม: การตรวจสอบความถูกต้องของข้อมูล Excel ตามค่าของเซลล์อื่น
บทสรุป
ในบทความข้างต้น ฉันได้พยายามพูดถึงสองวิธีในการกรองข้อมูลโดยใช้ การตรวจสอบข้อมูล รายการดรอปดาวน์ใน excel อย่างละเอียด หวังว่าวิธีการและคำอธิบายเหล่านี้จะเพียงพอที่จะแก้ปัญหาของคุณได้ โปรดแจ้งให้เราทราบหากคุณมีข้อสงสัยใดๆ
บทความที่เกี่ยวข้อง
- การตรวจสอบความถูกต้องของข้อมูล Excel เฉพาะตัวเลขและตัวอักษร (โดยใช้สูตรที่กำหนดเอง)
- ใช้การตรวจสอบความถูกต้องของข้อมูลที่กำหนดเองสำหรับหลายเกณฑ์ใน Excel (4 ตัวอย่าง)
- รายการแบบหล่นลงการตรวจสอบความถูกต้องของข้อมูลเติมอัตโนมัติใน Excel (2 วิธี)
- วิธีการสร้างรายการตรวจสอบข้อมูลจากตารางใน Excel (3 วิธี)
- สร้างรายการแบบเลื่อนลงสำหรับการตรวจสอบความถูกต้องของข้อมูลด้วยการเลือกหลายรายการใน Excel
- วิธีการใช้การตรวจสอบความถูกต้องของข้อมูลหลายรายการในเซลล์เดียวใน Excel (3 ตัวอย่าง)
