ค้นหาหลายเกณฑ์ด้วย INDEX และ MATCH
เมื่อต้องจัดการกับฐานข้อมูลขนาดใหญ่ในสเปรดชีต Excel ที่มีหลายคอลัมน์และคำอธิบายแถว การค้นหาสิ่งที่ตรงตามเกณฑ์หลายเกณฑ์มักเป็นเรื่องยากเสมอ ในกรณีนี้ คุณสามารถใช้สูตรอาร์เรย์กับ the ดัชนี และ MATCH fความไม่แน่นอน
จะทำการค้นหาโดยใช้หลายเกณฑ์ได้อย่างไร?
เพื่อค้นหาไฟล์ ผลิตภัณฑ์ นั่นคือ ขาว และ กลาง- ขนาดราคา $18 ดังที่แสดงในภาพด้านบน คุณสามารถใช้ประโยชน์จากตรรกะบูลีนเพื่อสร้างอาร์เรย์ 1 และ 0 เพื่อแสดงแถวที่ตรงตามเกณฑ์ ฟังก์ชัน MATCH จะค้นหาตำแหน่งของแถวแรกที่ตรงตามเกณฑ์ทั้งหมด หลังจากนั้น INDEX จะค้นหา ID ผลิตภัณฑ์ที่เกี่ยวข้องในแถวเดียวกัน
ไวยากรณ์ทั่วไป
=INDEX(return_range,MATCH(1,(criteria_value1=criteria_range1*criteria_value2=criteria_range2*(…),0))
√ หมายเหตุ: นี่คือสูตรอาร์เรย์ที่คุณต้องป้อนด้วย Ctrl + เปลี่ยน + เข้าสู่.
- กลับ_ช่วง: ช่วงที่คุณต้องการให้สูตรผสมส่งคืนรหัสผลิตภัณฑ์ ในที่นี้หมายถึงช่วงรหัสผลิตภัณฑ์
- เกณฑ์_ค่า: เกณฑ์ที่ใช้เพื่อค้นหาตำแหน่งของรหัสผลิตภัณฑ์ ในที่นี้หมายถึงค่าในเซลล์ H4, H5 และ H6
- เกณฑ์_ช่วง: ช่วงที่สอดคล้องกันโดยที่ เกณฑ์_values มีการระบุไว้ ในที่นี้หมายถึงสี ขนาด และช่วงราคา
- match_type 0: บังคับให้ MATCH หาค่าแรกที่เท่ากับ lookup_value.
เพื่อค้นหาสินค้าที่ใช่ ขาว และ กลาง- ขนาดราคา $18โปรดคัดลอกหรือป้อนสูตรด้านล่างในเซลล์ H8 แล้วกด Ctrl + เปลี่ยน + เข้าสู่ เพื่อให้ได้ผลลัพธ์:
=ดัชนี(B5: B10,แมตช์(1,("สีขาว"=C5: C10) * ("ปานกลาง"=D5: D10) * (18=E5: E10),0))
หรือใช้การอ้างอิงเซลล์เพื่อทำให้สูตรเป็นแบบไดนามิก:
=ดัชนี(B5: B10,แมตช์(1,(H4=C5: C10) * (H5=D5: D10) * (H6=E5: E10),0))
คำอธิบายของสูตร
=INDEX(B5:B10,MATCH(1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))
- (H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10): สูตรเปรียบเทียบสีในเซลล์ H4 กับทุกสีในช่วงนี้ C5: C10; เปรียบเทียบขนาดใน H5 กับทุกขนาดใน D5: D10; เปรียบเทียบราคาใน H6 เทียบกับราคาทั้งหมดใน E5: E10. ผลลัพธ์เริ่มต้นเป็นดังนี้:
{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}*{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE}*{FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}
การคูณจะเปลี่ยน TRUE และ FALSE เป็น 1 และ 0:
{1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}.
หลังจากการคูณเราจะได้อาร์เรย์เดียวดังนี้:
{0;0;0;0;1;0}. - แมตช์(1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10), 0) = แมตช์(1,{0;0;0;0;1;0}, 0): Match_type 0 ถามฟังก์ชัน MATCH เพื่อค้นหาค่าที่ตรงกัน ฟังก์ชันจะคืนค่าตำแหน่งของ 1 ในอาร์เรย์ {0;0;0;0;1;0}ซึ่งเป็น 5.
- ดัชนี(B5: B10,แมตช์(1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10), 0)) = ดัชนี(B5: B10,5): ฟังก์ชัน INDEX ส่งกลับค่า 5ค่า th ในช่วงรหัสผลิตภัณฑ์ B5: B10ซึ่งเป็น 30005.
ฟังก์ชันที่เกี่ยวข้อง
ฟังก์ชัน Excel INDEX ส่งคืนค่าที่แสดงตามตำแหน่งที่กำหนดจากช่วงหรืออาร์เรย์
ฟังก์ชัน Excel MATCH จะค้นหาค่าที่ระบุในช่วงของเซลล์ และส่งกลับตำแหน่งสัมพัทธ์ของค่า
สูตรที่เกี่ยวข้อง
ค้นหาค่าการจับคู่ที่ใกล้เคียงที่สุดโดยมีหลายเกณฑ์
ในบางกรณี คุณอาจต้องค้นหาค่าการจับคู่ที่ใกล้เคียงที่สุดหรือค่าประมาณตามเกณฑ์มากกว่าหนึ่งเกณฑ์ ด้วยการผสมผสานระหว่างฟังก์ชัน INDEX, MATCH และ IF คุณสามารถทำให้เสร็จใน Excel ได้อย่างรวดเร็ว
การจับคู่โดยประมาณกับ INDEX และ MATCH
มีบางครั้งที่เราจำเป็นต้องค้นหาการจับคู่โดยประมาณใน Excel เพื่อประเมินผลการปฏิบัติงานของพนักงาน ให้คะแนนนักเรียน คำนวณค่าไปรษณีย์ตามน้ำหนัก ฯลฯ ในบทช่วยสอนนี้ เราจะพูดถึงวิธีใช้ฟังก์ชัน INDEX และ MATCH เพื่อดึงข้อมูล ผลลัพธ์ที่เราต้องการ
ค้นหาค่าจากแผ่นงานหรือสมุดงานอื่น
ถ้าคุณรู้วิธีใช้ฟังก์ชัน VLOOKUP เพื่อค้นหาค่าในเวิร์กชีต ค่า vlookup จากเวิร์กชีตหรือเวิร์กบุ๊กอื่นจะไม่เป็นปัญหาสำหรับคุณ กวดวิชาจะแสดงวิธีการ vlookup ค่าจากแผ่นงานอื่นใน Excel
เครื่องมือเพิ่มประสิทธิภาพการทำงานในสำนักงานที่ดีที่สุด
Kutools สำหรับ Excel - ช่วยให้คุณโดดเด่นจากฝูงชน
Kutools สำหรับ Excel มีคุณสมบัติมากกว่า 300 รายการ รับรองว่าสิ่งที่คุณต้องการเพียงแค่คลิกเดียว...
แท็บ Office - เปิดใช้งานการอ่านแบบแท็บและการแก้ไขใน Microsoft Office (รวม Excel)
- หนึ่งวินาทีเพื่อสลับไปมาระหว่างเอกสารที่เปิดอยู่มากมาย!
- ลดการคลิกเมาส์หลายร้อยครั้งสำหรับคุณทุกวันบอกลามือเมาส์
- เพิ่มประสิทธิภาพการทำงานของคุณได้ถึง 50% เมื่อดูและแก้ไขเอกสารหลายฉบับ
- นำแท็บที่มีประสิทธิภาพมาสู่ Office (รวมถึง Excel) เช่นเดียวกับ Chrome, Edge และ Firefox
