ฟังก์ชัน Excel อาจมีประสิทธิภาพมากหากคุณคุ้นเคยกับการรวมสูตรต่างๆสิ่งที่เคยดูเหมือนเป็นไปไม่ได้ จู่ๆ ก็กลายเป็นเหมือนการเล่นของเด็ก
ตัวอย่างหนึ่งคือการค้นหาค่าที่ใกล้เคียงที่สุดในชุดข้อมูล Excel
มีฟังก์ชันการค้นหาที่มีประโยชน์หลายอย่างใน Excel (เช่น VLOOKUP และ INDEX MATCH) ที่สามารถค้นหาฟังก์ชันที่ใกล้เคียงที่สุด值(ดังแสดงในตัวอย่างด้านล่าง)
แต่ส่วนที่ดีที่สุดคือ คุณสามารถรวมฟังก์ชันการค้นหาเหล่านี้กับฟังก์ชัน Excel อื่นๆ เพื่อทำสิ่งต่างๆ ได้มากขึ้น (รวมถึงการค้นหาค่าที่ใกล้เคียงที่สุดกับค่าการค้นหาในรายการที่ไม่ได้เรียงลำดับ)
ในบทช่วยสอนนี้ ฉันจะแสดงวิธีใช้สูตรการค้นหาเพื่อค้นหาค่าที่ใกล้เคียงที่สุดกับค่าการค้นหาใน Excel
มีหลายสถานการณ์ที่คุณต้องค้นหาการจับคู่ที่ใกล้เคียงที่สุด (หรือค่าการจับคู่ที่ใกล้เคียงที่สุด)
นี่คือตัวอย่างที่ฉันจะกล่าวถึงในบทความนี้:
- ค้นหาอัตราค่าคอมมิชชั่นตามยอดขาย
- ค้นหาผู้สมัครที่ดีที่สุด (ตามประสบการณ์ที่ใกล้เคียงที่สุด)
- ค้นหาวันจัดงานครั้งต่อไป
เริ่มกันเลย!
เนื้อหา
ค้นหาอัตราค่าคอมมิชชั่น (มองหามูลค่าการขายที่ใกล้เคียงที่สุด)
สมมติว่าคุณมีชุดข้อมูลแบบเดียวกับด้านล่างซึ่งคุณต้องการค้นหาอัตราค่าคอมมิชชันสำหรับพนักงานขายทั้งหมด
ค่าคอมมิชชั่นจะกระจายตามมูลค่าการขายคำนวณโดยใช้ตารางด้านขวา
ตัวอย่างเช่น หากยอดขายรวมของพนักงานขายเท่ากับ 5000 ค่าคอมมิชชันคือ 0% และหากยอดขายรวมของเขา/เธอเท่ากับ 15000 ค่าคอมมิชชันจะเท่ากับ 5%
ในการรับอัตราค่าคอมมิชชัน คุณต้องค้นหาช่วงการขายที่ใกล้ที่สุดซึ่งต่ำกว่ามูลค่าการขายตัวอย่างเช่น สำหรับการขาย 15000 รายการ ค่าคอมมิชชันคือ 10,000 (หรือ 5%) และสำหรับการขาย 25000 รายการ อัตราค่าคอมมิชชันคือ 20,000 (หรือ 7%)
หากต้องการค้นหามูลค่าการขายที่ใกล้เคียงที่สุดและรับอัตราค่าคอมมิชชัน คุณสามารถใช้การจับคู่โดยประมาณใน VLOOKUP
สูตรต่อไปนี้สามารถทำได้:
= VLOOKUP (B2, $ E $ 2: $ F $ 6,2,1)
โปรดทราบว่าในสูตรนี้ พารามิเตอร์สุดท้ายคือ 1 ซึ่งบอกให้สูตรใช้การค้นหาโดยประมาณซึ่งหมายความว่าสูตรจะผ่านมูลค่าการขายในคอลัมน์ E และค้นหาค่าที่ต่ำกว่าค่าการค้นหา
สูตร VLOOKUP จะให้อัตราค่าคอมมิชชั่นสำหรับมูลค่านั้น
注意: ในการดำเนินการนี้ คุณต้องจัดเรียงข้อมูลตามลำดับจากน้อยไปมาก
ค้นหาผู้สมัครที่ดีที่สุด (ตามประสบการณ์ที่ใกล้เคียงที่สุด)
ในตัวอย่างข้างต้น ข้อมูลจะต้องเรียงลำดับจากน้อยไปมากแต่อาจมีกรณีที่ข้อมูลไม่ถูกจัดเรียง
ลองมาดูตัวอย่างและดูว่าเราจะใช้การผสมสูตรเพื่อค้นหารายการที่ตรงกันที่สุดใน Excel ได้อย่างไร
ด้านล่างนี้คือชุดข้อมูลตัวอย่างที่ฉันต้องการค้นหาชื่อพนักงานที่มีประสบการณ์การทำงานใกล้เคียงกับค่าที่ต้องการมากที่สุดมูลค่าที่คาดหวังในกรณีนี้คือ 2.5 ปี
โปรดทราบว่าข้อมูลจะไม่ถูกจัดเรียงนอกจากนี้ ประสบการณ์ที่ใกล้เคียงที่สุดอาจน้อยกว่าหรือมากกว่าประสบการณ์ที่กำหนดตัวอย่างเช่น 2 ปีกับ 3 ปีใกล้เคียงกัน (ส่วนต่าง 0.5 ปี)
นี่คือสูตรที่ให้ผลลัพธ์แก่เรา:
=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))
เคล็ดลับในสูตรนี้คือการเปลี่ยนอาร์เรย์การค้นหาและค่าการค้นหาเพื่อค้นหาความแตกต่างเชิงประจักษ์ที่เล็กที่สุดระหว่างค่าที่ต้องการและค่าจริง
ก่อนอื่นมาทำความเข้าใจว่าคุณจะทำอย่างไรด้วยตนเอง (แล้วฉันจะอธิบายว่าสูตรนี้ทำงานอย่างไร)
เมื่อคุณดำเนินการด้วยตนเอง คุณจะผ่านแต่ละเซลล์ในคอลัมน์ B และค้นหาความแตกต่างระหว่างประสบการณ์ที่คุณต้องการและประสบการณ์ที่มีเมื่อคุณมีความแตกต่างทั้งหมดแล้ว คุณจะพบสิ่งเล็กที่สุดและได้ชื่อของบุคคลนั้น
นั่นคือสิ่งที่เราทำกับสูตรนี้
ให้ฉันอธิบาย
ค่าการค้นหาในสูตร MATCH คือ MIN(ABS(D2-B2:B15))
ส่วนนี้ให้ความแตกต่างขั้นต่ำระหว่างประสบการณ์ที่กำหนด (เช่น 2.5 ปี) และประสบการณ์อื่นๆ ทั้งหมดในกรณีนี้จะส่งกลับ 0.3
โปรดทราบว่าฉันใช้ ABS เพื่อให้แน่ใจว่าฉันกำลังค้นหาสิ่งที่ใกล้เคียงที่สุด (อาจมากหรือน้อยกว่าประสบการณ์ที่ได้รับ)
ตอนนี้ ค่าต่ำสุดนี้กลายเป็นค่าการค้นหาของเรา
อาร์เรย์การค้นหาในฟังก์ชัน MATCH คือ ABS(D2-$B$2:$B$15)
สิ่งนี้ทำให้เรามีอาร์เรย์ของตัวเลขที่จะลบ 2.5 (ประสบการณ์ที่ต้องการ)
所以現在我們有一個查找值 (0.3) 和一個查找數組 ({6.8;0.8;19.5;21.8;14.5;11.2;0.3;9.2;2;9.8;14.8;0.4;23.8;2.9})
ฟังก์ชัน MATCH จะค้นหาตำแหน่ง 0.3 ในอาร์เรย์นี้ ซึ่งเป็นตำแหน่งของชื่อบุคคลที่มีประสบการณ์ใกล้เคียงที่สุด
ฟังก์ชัน INDEX จะใช้หมายเลขงานเพื่อส่งคืนชื่อบุคคล
หมายเหตุ: หากมีผู้สมัครหลายคนที่มีประสบการณ์ขั้นต่ำเหมือนกัน สูตรข้างต้นจะให้ชื่อพนักงานที่ตรงกับคนแรก
ค้นหาวันจัดงานครั้งต่อไป
นี่เป็นอีกตัวอย่างหนึ่งที่คุณสามารถใช้สูตรการค้นหาเพื่อค้นหาวันที่ถัดไปของเหตุการณ์ตามวันที่ปัจจุบัน
ด้านล่างนี้เป็นชุดข้อมูลที่ฉันมีชื่อเหตุการณ์และวันที่ของกิจกรรม
สิ่งที่ฉันต้องการคือชื่องานถัดไปและวันที่จัดงานของงานที่กำลังจะมาถึงนี้
นี่คือสูตรที่ให้ชื่อของเหตุการณ์ที่จะเกิดขึ้น:
=INDEX($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)
สูตรต่อไปนี้จะให้วันที่ของเหตุการณ์ที่จะเกิดขึ้น:
=INDEX($B$2:$B$11,MATCH(E1,$B$2:$B$11,1)+1)
ให้ฉันอธิบายว่าสูตรนี้ทำงานอย่างไร
ในการรับวันที่ของเหตุการณ์ ฟังก์ชัน MATCH จะค้นหาวันที่ปัจจุบันในคอลัมน์ Bในกรณีนี้ เราไม่ได้กำลังมองหาการจับคู่แบบตรงทั้งหมด แต่ต้องการการจับคู่โดยประมาณดังนั้น พารามิเตอร์สุดท้ายของฟังก์ชัน MATCH คือ 1 (จะพบค่าที่มากที่สุดที่น้อยกว่าหรือเท่ากับค่าการค้นหา)
ดังนั้นฟังก์ชัน MATCH จะส่งคืนตำแหน่งของเซลล์ที่มีวันที่น้อยกว่าหรือเท่ากับวันที่ปัจจุบันดังนั้น ในกรณีนี้ เหตุการณ์ถัดไปจะอยู่ในเซลล์ถัดไป (เนื่องจากรายการถูกเรียงลำดับจากน้อยไปมาก)
ดังนั้น ในการรับวันที่ของกิจกรรมที่จะเกิดขึ้น เพียงเพิ่มหนึ่งตำแหน่งลงในตำแหน่งเซลล์ที่ส่งคืนโดยฟังก์ชัน MATCH และจะให้ตำแหน่งเซลล์ของวันที่จัดกิจกรรมถัดไป
ค่านี้ถูกกำหนดโดยฟังก์ชัน INDEX
ในการรับชื่อเหตุการณ์ ให้ใช้สูตรเดียวกันและเปลี่ยนช่วงในฟังก์ชัน INDEX จากคอลัมน์ B เป็นคอลัมน์ A
ตัวอย่างนี้เข้ามาในความคิดของฉันเมื่อเพื่อนขอเขาแสดงรายการวันเกิดของเพื่อน/ญาติทั้งหมดของเขาในคอลัมน์ และต้องการทราบว่าวันเกิดถัดไปกำลังจะมาถึง (และชื่อของบุคคลนั้น)
ตัวอย่างสามตัวอย่างข้างต้นแสดงวิธีการใช้สูตรการค้นหาเพื่อค้นหาค่าที่ใกล้เคียงที่สุดใน Excel
