การจับคู่โดยประมาณสำหรับตารางแบ่งระดับ
ค้นหาระดับที่ถูกต้องในตารางราคาหรือระดับคะแนนด้วย MATCH ที่จัดเรียงแล้ว
การจับคู่โดยประมาณสำหรับตารางแบ่งระดับ เป็นบทเรียน Excel Formulas Academy ฟรีบน CoddyKit นี่คือบทเรียนที่ 4 จากทั้งหมด 4 บทเรียน คุณสามารถอ่านบทเรียนทั้งหมดด้านล่างฟรี — จากนั้นลองปฏิบัติด้วยตัวคุณเองในเบราว์เซอร์พร้อมตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7 บทเรียนนี้เป็นส่วนหนึ่งของเส้นทางการเรียน Excel Formulas Academy และความก้าวหน้าของคุณจะซิงค์ข้ามเว็บและแอป CoddyKit คอร์ส Excel Formulas Academy มีบทเรียนทั้งหมด 4 บทเรียน
ตารางแบ่งระดับคืออะไร
ตารางแบ่งระดับ จัดค่าต่อเนื่องออกเป็นช่วง ตัวอย่างเช่น อัตราภาษี ค่าจัดส่งตามน้ำหนัก ส่วนลดตามปริมาณ และเกรดตัวอักษรตามคะแนน
คุณไม่จำเป็นต้องมีแถวสำหรับค่าที่เป็นไปได้ทุกค่า แต่ให้เก็บเฉพาะ ค่าเริ่มต้นของช่วง แต่ละช่วงเท่านั้น คะแนน 87 ไม่มีรายการที่ตรงกันพอดี แต่จะอยู่ในช่วงที่เริ่มต้นที่ 80
นี่คือจุดที่การค้นหาแบบใกล้เคียงมีประโยชน์ เพราะจะค้นหาช่วงที่ถูกต้องแทนการบังคับให้ต้องตรงกันพอดี
การจับคู่แบบตรงกันพอดีกับแบบใกล้เคียง
จนถึงตอนนี้เราใช้ MATCH(value, range, 0) เพื่อค้นหาแบบตรงกันพอดี อาร์กิวเมนต์ตัวที่สาม 0 หมายถึง "ค้นหาค่านี้ให้ตรงกันพอดี หากไม่พบให้ส่งกลับ #N/A"
สำหรับการแบ่งระดับ เราจะใช้ชนิดการจับคู่เป็น 1 แทน ซึ่งจะค้นหาค่าที่มากที่สุดและน้อยกว่าหรือเท่ากับค่าที่ใช้ค้นหา วิธีนี้ตรงกับพฤติกรรมที่ควรเป็นของการค้นหาช่วงพอดี
มีกฎสำคัญข้อหนึ่งคือ เมื่อใช้ชนิดการจับคู่ 1 รายการค่าเกณฑ์ต้องเรียงจากน้อยไปมาก
=MATCH(87, E2:E6, 1)การตั้งค่าช่วง
ลองนึกภาพตารางให้เกรด คอลัมน์ E เก็บค่าเกณฑ์เริ่มต้น โดยเรียงจากน้อยไปมาก: 0, 60, 70, 80, 90 ส่วนคอลัมน์ F เก็บป้ายกำกับ: F, D, C, B, A
คะแนน 0 ถึง 59 ได้ F, 60 ถึง 69 ได้ D และต่อไปตามลำดับ เราเก็บเฉพาะจุดเริ่มต้นของแต่ละช่วง ไม่ได้เก็บคะแนนทุกค่า
เป้าหมายของเราคือ เมื่อมีคะแนนอยู่ใน G1 ให้ส่งกลับเกรดตัวอักษรของคะแนนนั้น
การค้นหาตำแหน่งของช่วง
ใช้ MATCH แบบใกล้เคียงเพื่อค้นหาว่าคะแนนอยู่ในช่วงใด MATCH(G1, E2:E6, 1) เมื่อคะแนนเป็น 87 จะค้นหาค่าเกณฑ์ที่มากที่สุดและไม่เกิน 87
ค่าเกณฑ์คือ 0, 60, 70, 80, 90 ค่าที่มากที่สุดและไม่เกิน 87 คือ 80 ซึ่งอยู่ในตำแหน่งที่ 4 ดังนั้น MATCH จะส่งกลับค่า 4
ตำแหน่งนี้ชี้ไปยังช่วงที่ถูกต้อง แม้ว่า 87 จะไม่ได้อยู่ในรายการก็ตาม
=MATCH(G1, E2:E6, 1)การส่งกลับป้ายกำกับระดับ
จากนั้นส่งตำแหน่งที่ได้เข้าไปใน INDEX โดยใช้คอลัมน์ป้ายกำกับ F2:F6
INDEX(F2:F6, MATCH(G1, E2:E6, 1)) รับตำแหน่งที่ 4 และส่งกลับป้ายกำกับลำดับที่ 4 ซึ่งคือ "B"
ดังนั้นคะแนน 87 จึงถูกจับคู่เป็นเกรด B อย่างถูกต้อง หากเปลี่ยน G1 เป็น 95 MATCH จะส่งกลับ 5 และได้ "A" หากเปลี่ยนเป็น 55 MATCH จะส่งกลับ 1 และได้ "F"
=INDEX(F2:F6, MATCH(G1, E2:E6, 1))ข้อกำหนดเรื่องการเรียงลำดับ
MATCH แบบใกล้เคียง (ชนิดที่ 1) กำหนดให้เรียงจากน้อยไปมากในช่วงค้นหา โดยฟังก์ชันจะถือว่าข้อมูลเพิ่มขึ้นเรื่อย ๆ และหยุดทันทีที่พบค่าซึ่งเกินค่าที่ใช้ค้นหา
หากค่าเกณฑ์ของคุณเรียงไม่ถูกต้อง MATCH อาจหยุดเร็วเกินไปและส่งกลับตำแหน่งที่ผิดโดยไม่มีข้อผิดพลาดแจ้งเตือน คุณควรเรียงคอลัมน์ค่าเกณฑ์จากน้อยไปมากเสมอก่อนใช้งานการค้นหาแบบแบ่งระดับ
=INDEX(F2:F6, MATCH(G1, E2:E6, 1))การทำแบบเดียวกันด้วย XLOOKUP
XLOOKUP ก็รองรับการจับคู่แบบใกล้เคียงเช่นกัน อาร์กิวเมนต์ตัวที่ห้า ซึ่งเป็นโหมดการจับคู่ รองรับค่า -1 สำหรับ "จับคู่แบบตรงกันพอดี หรือเลือกรายการถัดไปที่น้อยกว่า" จึงเหมาะอย่างยิ่งกับตารางแบ่งระดับ
วิธีนี้จะค้นหาค่าเกณฑ์ที่มากที่สุดและไม่เกิน G1 แล้วส่งกลับป้ายกำกับที่ตรงกัน โดยไม่ต้องใช้ INDEX สำหรับการค้นหาช่วง วิธีนี้มักอ่านเข้าใจง่ายกว่า INDEX-MATCH
=XLOOKUP(G1, E2:E6, F2:F6, "Out of range", -1)ตัวอย่างระดับราคา
มาดูตัวอย่างส่วนลดตามปริมาณกัน ค่าเกณฑ์ใน E (จำนวนที่สั่งซื้อ) คือ 0, 10, 50, 100 ส่วนส่วนลดใน F คือ 0%, 5%, 10%, 15%
- สั่งซื้อ 7 ชิ้น: ค่าเกณฑ์ที่มากที่สุดและไม่เกิน 7 คือ 0 อยู่ในตำแหน่งที่ 1 จึงได้ส่วนลด 0%
- สั่งซื้อ 60 ชิ้น: ค่าที่มากที่สุดและไม่เกิน 60 คือ 50 อยู่ในตำแหน่งที่ 3 จึงได้ส่วนลด 10%
- สั่งซื้อ 200 ชิ้น: ค่าที่มากที่สุดและไม่เกิน 200 คือ 100 อยู่ในตำแหน่งที่ 4 จึงได้ส่วนลด 15%
ใช้สูตรเดียวจัดการกับจำนวนทุกค่าได้
=INDEX(F2:F5, MATCH(G1, E2:E5, 1))การจัดการค่าที่ต่ำกว่าระดับแรก
จะเกิดอะไรขึ้นหากค่าหนึ่งน้อยกว่าค่าเกณฑ์ทั้งหมด เมื่อใช้ MATCH แบบใกล้เคียง จะไม่มีค่าที่น้อยกว่าหรือเท่ากับค่านั้น ดังนั้น MATCH จะส่งกลับ #N/A
เพื่อป้องกันปัญหานี้ ตรวจสอบให้แน่ใจว่าค่าเกณฑ์แรกครอบคลุมค่าต่ำสุด (โดยมักเป็น 0) หรือครอบสูตรด้วย IFERROR เพื่อแสดงข้อความที่เข้าใจง่ายเมื่อค่าอินพุตอยู่นอกช่วง
=IFERROR(INDEX(F2:F6, MATCH(G1, E2:E6, 1)), "Below lowest tier")ข้อผิดพลาดที่พบบ่อย
โปรดระวังปัญหาเหล่านี้ในตารางแบ่งระดับ:
- ค่าเกณฑ์ไม่เรียงลำดับ: สาเหตุอันดับหนึ่งของผลลัพธ์ที่ผิดโดยไม่มีข้อผิดพลาดแจ้งเตือน
- ใช้ชนิดการจับคู่ 0: บังคับให้ต้องจับคู่แบบตรงกันพอดี และส่งกลับ #N/A สำหรับค่าที่อยู่ระหว่างกลาง
- เก็บจุดสิ้นสุดของช่วงแทนจุดเริ่มต้น: MATCH ชนิดที่ 1 ต้องใช้ขอบเขตล่างของแต่ละช่วง ไม่ใช่ขอบเขตบน
- ค่าเกณฑ์เป็นข้อความ: ตัวเลขที่จัดเก็บเป็นข้อความจะทำให้การเปรียบเทียบผิดพลาด ควรจัดเก็บเป็นตัวเลข
ตารางแบ่งระดับแบบสองมิติ
คุณสามารถผสานการจับคู่แบบใกล้เคียงเข้ากับเทคนิคแบบสองทางได้ ลองนึกภาพค่าจัดส่งที่ขึ้นอยู่กับทั้งช่วงน้ำหนัก (แถว) และช่วงพื้นที่ (คอลัมน์)
ใช้ MATCH แบบใกล้เคียง (ชนิดที่ 1) หนึ่งครั้งเพื่อค้นหาแถวน้ำหนัก และใช้อีกครั้งเพื่อค้นหาคอลัมน์พื้นที่ จากนั้นส่งค่าทั้งสองเข้าไปใน INDEX เนื่องจากแกนทั้งสองเป็นค่าเกณฑ์ที่เรียงลำดับไว้ MATCH แต่ละรายการจึงชี้ไปยังช่วงที่ถูกต้อง
วิธีนี้ผสาน INDEX-MATCH-MATCH เข้ากับตรรกะการแบ่งระดับ เพื่อสร้างตารางอัตราที่ซับซ้อนได้
=INDEX(B2:D6, MATCH(G1, A2:A6, 1), MATCH(G2, B1:D1, 1))ตรวจสอบความเข้าใจอย่างรวดเร็ว
ตรวจสอบความเข้าใจเกี่ยวกับการค้นหาช่วงแบบใกล้เคียง
สรุปบทเรียน
สำหรับการค้นหาระดับและช่วง:
- เก็บค่าเกณฑ์ล่างของแต่ละช่วง และเรียงจากน้อยไปมาก
- ใช้
MATCH(value, thresholds, 1)เพื่อค้นหาตำแหน่งของช่วง (ค่าที่มากที่สุดและไม่เกินอินพุต) - ครอบด้วย
INDEX(labels, ...)เพื่อส่งกลับช่วง หรือใช้XLOOKUP(..., -1)เพื่อให้ได้ผลลัพธ์เดียวกัน
ครอบคลุมค่าต่ำสุดด้วยค่าเกณฑ์ 0 หรือใช้ IFERROR สำหรับอินพุตที่อยู่นอกช่วง และอย่าปล่อยให้ค่าเกณฑ์เรียงไม่ถูกต้อง
=INDEX(F2:F6, MATCH(G1, E2:E6, 1))คำถามที่พบบ่อย
บทเรียน “การจับคู่โดยประมาณสำหรับตารางแบ่งระดับ” ฟรีหรือไม่
ใช่ — ข้อความเต็มของ “การจับคู่โดยประมาณสำหรับตารางแบ่งระดับ” ฟรีให้อ่านที่นี่บนเว็บ เพื่อปฏิบัติแบบโต้ตอบ (ตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7) และปลดล็อคส่วนที่เหลือของคอร์ส Excel Formulas Academy ให้อัปเกรดเป็น CoddyKit PRO คอร์ส Excel Formulas Academy มีบทเรียนทั้งหมด 4 บทเรียน
คุณจะเรียนรู้อะไรในบทเรียน “การจับคู่โดยประมาณสำหรับตารางแบ่งระดับ”
ค้นหาระดับที่ถูกต้องในตารางราคาหรือระดับคะแนนด้วย MATCH ที่จัดเรียงแล้ว คุณปฏิบัติ Excel Formulas Academy ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน
คุณต้องมีประสบการณ์ก่อนที่จะเริ่มเรียน Excel Formulas Academy หรือไม่
ไม่จำเป็นต้องมีประสบการณ์มาก่อน Excel Formulas Academy บน CoddyKit ออกแบบมาสำหรับผู้เริ่มต้นไปจนถึงผู้เรียนขั้นสูง คุณสามารถเริ่มต้นที่นี่หรือเริ่มจากตัวแรกและเรียนด้วยความเร็วของคุณเอง นี่คือบทเรียนที่ 4 จากทั้งหมด 4 บทเรียน
บทเรียน “การจับคู่โดยประมาณสำหรับตารางแบ่งระดับ” ใช้เวลานานแค่ไหน
บทเรียน CoddyKit ส่วนใหญ่ใช้เวลาประมาณ 5–10 นาที แต่ละบทเรียนจึงสั้นและเป็นแบบโต้ตอบ คุณสามารถก้าวหน้าอย่างต่อเนื่องและกลับมาเรียนต่อจากตรงที่เพิ่งหยุดบนเว็บและแอปได้เลย
ฉันเขียนและรันโค้ดในบทเรียน Excel Formulas Academy นี้ได้ไหม
ได้ บทเรียน Excel Formulas Academy ทุกบทมีตัวแก้ไขโค้ดในตัว คุณจึงเขียนและรันโค้ดจริงได้เลยในเบราว์เซอร์ และได้รับข้อเสนอแนะจาก AI ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ
บทเรียนทั้งหมดในหลักสูตรนี้
- ค้นหาแบบสองทางด้วย INDEX-MATCH-MATCH
- ค้นหาค่าที่ตรงกันรายการสุดท้าย
- ค้นหาหลายเงื่อนไขด้วย INDEX-MATCH
- การจับคู่โดยประมาณสำหรับตารางแบ่งระดับ