ค้นหาคู่ที่ใกล้เคียงที่สุด
ในการค้นหาค่าการค้นหาที่ใกล้เคียงที่สุดในชุดข้อมูลตัวเลขใน Excel คุณสามารถใช้ ดัชนี, MATCH, เอบีเอส และ MIN ทำงานพร้อมกัน
จะหาคู่ที่ใกล้เคียงที่สุดใน Excel ได้อย่างไร?
ที่จะรู้ว่า พนักงานขายทำยอดขายได้ใกล้เคียงเป้าหมายที่ $20,000 ดังที่แสดงไว้ข้างต้น สูตรที่รวมฟังก์ชัน INDEX, MATCH, ABS และ MIN จะช่วยคุณในลักษณะนี้: ฟังก์ชัน ABS จะเปลี่ยนค่าความแตกต่างทั้งหมดระหว่างยอดขายที่ทำโดยพนักงานขายแต่ละคนกับเป้าหมายการขายเป็นค่าบวก จากนั้น MIN จะค้นหา ความแตกต่างที่เล็กที่สุดซึ่งหมายถึงการจับคู่ตู้เสื้อผ้า ตอนนี้เราสามารถใช้ฟังก์ชัน MATCH เพื่อค้นหาตำแหน่งของตู้เสื้อผ้าที่ตรงกันและมี INDEX เพื่อดึงค่าที่ตำแหน่งที่เกี่ยวข้อง
ไวยากรณ์ทั่วไป
=INDEX(return_range,MATCH(MIN(ABS(lookup_array-lookup_value)),ABS(lookup_array-lookup_value),0))
√ หมายเหตุ: นี่คือสูตรอาร์เรย์ที่คุณต้องป้อนด้วย Ctrl + เปลี่ยน + เข้าสู่.
- กลับ_ช่วง: ช่วงที่คุณต้องการให้สูตรการรวมกันส่งคืนพนักงานขาย นี่หมายถึงช่วงชื่อ
- lookup_array: ช่วงของเซลล์ที่มีค่าเพื่อเปรียบเทียบกับ lookup_value. ในที่นี้หมายถึงช่วงการขาย
- lookup_value: ค่าที่จะเปรียบเทียบเพื่อหาค่าที่ใกล้เคียงที่สุด ในที่นี้หมายถึงเป้าหมายการขาย
ที่จะรู้ว่า พนักงานขายทำยอดขายได้ใกล้เคียงเป้าหมายที่ $20,000โปรดคัดลอกหรือป้อนสูตรด้านล่างในเซลล์ F5 แล้วกด Ctrl + เปลี่ยน + เข้าสู่ เพื่อให้ได้ผลลัพธ์:
=ดัชนี(B5: B10,MATCH(ขั้นต่ำ(ABS(C5: C10-20000)), เอบีเอส (C5: C10-20000),0))
หรือใช้การอ้างอิงเซลล์เพื่อทำให้สูตรเป็นแบบไดนามิก:
=ดัชนี(B5: B10,MATCH(ขั้นต่ำ(ABS(C5: C10-F4)), เอบีเอส (C5: C10-F4),0))
คำอธิบายของสูตร
=INDEX(B5:B10,MATCH(MIN(ABS(C5:C10-F4)),ABS(C5:C10-F4),0))
- เอบีเอส(C5:C10-F4): ส่วนนี้ C5:C10-F4 จะได้รับค่าความแตกต่างทั้งหมดระหว่างการขายแต่ละครั้งในช่วง C5: C10 และยอดขายเป้าหมาย $20,000 ในเซลล์ F4 ในอาร์เรย์ดังนี้: {-4322;2451;6931;-1113;6591;-4782} ฟังก์ชัน ABS จะเปลี่ยนจำนวนลบทั้งหมดเป็นบวกดังนี้: {4322;2451;6931;1113;6591;4782}.
- นาที(เอบีเอส(C5:C10-F4)) = นาที({4322;2451;6931;1113;6591;4782}): ฟังก์ชัน MIN จะค้นหาจำนวนที่น้อยที่สุดจากอาร์เรย์ {4322;2451;6931;1113;6591;4782}ซึ่งหมายถึงความแตกต่างที่เล็กที่สุดหรือเราบอกว่าตู้เสื้อผ้าตรงกัน ดังนั้นฟังก์ชันจะกลับมา 1113.
- จับคู่(นาที(เอบีเอส(C5:C10-F4)),เอบีเอส(C5:C10-F4), 0) = จับคู่(1113,{4322;2451;6931;1113;6591;4782}, 0): พื้นที่ match_type0 บังคับให้ฟังก์ชัน MATCH หาตำแหน่งของตัวเลขที่แน่นอน 1113 ในอาร์เรย์ {4322;2451;6931;1113;6591;4782}. ฟังก์ชั่นจะกลับมา 4 เนื่องจากหมายเลขอยู่ที่ 4ตำแหน่งที่
- ดัชนี(B5: B10,จับคู่(นาที(เอบีเอส(C5:C10-F4)),เอบีเอส(C5:C10-F4), 0)) = ดัชนี(B5: B10,4): ฟังก์ชัน INDEX ส่งกลับค่า 4ค่า th ในช่วงชื่อ B5: B10ซึ่งเป็น ก้อน.
ฟังก์ชันที่เกี่ยวข้อง
ฟังก์ชัน Excel INDEX ส่งคืนค่าที่แสดงตามตำแหน่งที่กำหนดจากช่วงหรืออาร์เรย์
ฟังก์ชัน Excel MATCH จะค้นหาค่าที่ระบุในช่วงของเซลล์ และส่งกลับตำแหน่งสัมพัทธ์ของค่า
ฟังก์ชัน ABS ส่งคืนค่าสัมบูรณ์ของตัวเลข ตัวเลขติดลบจะถูกแปลงเป็นค่าบวกด้วยฟังก์ชันนี้ แต่ตัวเลขบวกและเลขศูนย์จะไม่ได้รับผลกระทบ
สูตรที่เกี่ยวข้อง
ค้นหาค่าการจับคู่ที่ใกล้เคียงที่สุดด้วยเกณฑ์หลายข้อ
ในบางกรณี คุณอาจต้องค้นหาค่าการจับคู่ที่ใกล้เคียงที่สุดหรือค่าประมาณตามเกณฑ์มากกว่าหนึ่งเกณฑ์ ด้วยการผสมผสานระหว่างฟังก์ชัน INDEX, MATCH และ IF คุณสามารถทำให้เสร็จใน Excel ได้อย่างรวดเร็ว
การจับคู่โดยประมาณกับ INDEX และ MATCH
มีบางครั้งที่เราจำเป็นต้องค้นหาการจับคู่โดยประมาณใน Excel เพื่อประเมินผลการปฏิบัติงานของพนักงาน ให้คะแนนนักเรียน คำนวณค่าไปรษณีย์ตามน้ำหนัก ฯลฯ ในบทช่วยสอนนี้ เราจะพูดถึงวิธีใช้ฟังก์ชัน INDEX และ MATCH เพื่อดึงข้อมูล ผลลัพธ์ที่เราต้องการ
ค้นหาค่าการจับคู่ที่ใกล้เคียงที่สุดโดยมีหลายเกณฑ์
ในบางกรณี คุณอาจต้องค้นหาค่าการจับคู่ที่ใกล้เคียงที่สุดหรือค่าประมาณตามเกณฑ์มากกว่าหนึ่งเกณฑ์ ด้วยการผสมผสานระหว่างฟังก์ชัน INDEX, MATCH และ IF คุณสามารถทำให้เสร็จใน Excel ได้อย่างรวดเร็ว
เครื่องมือเพิ่มประสิทธิภาพการทำงานในสำนักงานที่ดีที่สุด
Kutools สำหรับ Excel - ช่วยให้คุณโดดเด่นจากฝูงชน
Kutools สำหรับ Excel มีคุณสมบัติมากกว่า 300 รายการ รับรองว่าสิ่งที่คุณต้องการเพียงแค่คลิกเดียว...
แท็บ Office - เปิดใช้งานการอ่านแบบแท็บและการแก้ไขใน Microsoft Office (รวม Excel)
- หนึ่งวินาทีเพื่อสลับไปมาระหว่างเอกสารที่เปิดอยู่มากมาย!
- ลดการคลิกเมาส์หลายร้อยครั้งสำหรับคุณทุกวันบอกลามือเมาส์
- เพิ่มประสิทธิภาพการทำงานของคุณได้ถึง 50% เมื่อดูและแก้ไขเอกสารหลายฉบับ
- นำแท็บที่มีประสิทธิภาพมาสู่ Office (รวมถึง Excel) เช่นเดียวกับ Chrome, Edge และ Firefox
