การค้นหา Excel ขั้นสูงขั้นสูง:เหนือกว่า VLOOKUP ด้วย XLOOKUP &INDEX-MATCH-MATCH

Excel มีเทคนิคการค้นหาที่ครอบคลุมสำหรับการจัดการข้อมูลและการวิเคราะห์ข้อมูล โดยทั่วไปแล้ว VLOOKUP จะใช้สำหรับการดึงข้อมูล แต่ก็มีข้อจำกัด เช่น การต้องใช้คอลัมน์ค้นหาทางด้านซ้าย และการจัดการข้อผิดพลาดที่ไม่ยืดหยุ่น เพื่อเอาชนะสิ่งเหล่านี้ ฟังก์ชันขั้นสูง เช่น XLOOKUP และ INDEX-MATCH-MATCH มอบความยืดหยุ่น การควบคุม และประสิทธิภาพที่มากขึ้น เรามาแสดงเทคนิคการค้นหาขั้นสูงนอกเหนือจาก VLOOKUP โดยใช้ XLOOKUP และ INDEX-MATCH-MATCH เพื่อใช้ประโยชน์อย่างมีประสิทธิภาพ

ลองพิจารณาชุดข้อมูลการขายเพื่อใช้เทคนิคการค้นหาขั้นสูงโดยใช้ตัวอย่างที่ใช้งานได้จริง

เทคนิคการค้นหาขั้นสูง XLOOKUP

XLOOKUP เป็นฟังก์ชันอเนกประสงค์ใน Excel สำหรับการค้นหาคอลัมน์เดียวหรือหลายคอลัมน์ สามารถค้นหาในทิศทางใดก็ได้ (ซ้ายไปขวา ขวาไปซ้าย แนวตั้ง แนวนอน) ให้ข้อความแสดงข้อผิดพลาดที่กำหนดเอง และไม่จำเป็นต้องมีการจัดเรียงข้อมูลในการค้นหา ฟังก์ชัน XlOOKUP พร้อมใช้งานสำหรับผู้ใช้ Excel 2021 และ MS Office 365 เท่านั้น ฟังก์ชัน XLOOKUP จะอัปเดตการเปลี่ยนแปลงข้อมูลของค่าการค้นหาโดยอัตโนมัติ

ไวยากรณ์:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• lookup_value: ค่าที่จะค้นหา
• lookup_array: ช่วงหรืออาร์เรย์ที่จะค้นหาภายใน
• return_array: ช่วงหรืออาร์เรย์ที่จะใช้ส่งคืนผลลัพธ์
• [ถ้า_not_found]; [ไม่บังคับ]: ค่าที่จะส่งคืนหากไม่พบรายการที่ตรงกัน
• [match_mode]; [ไม่บังคับ]: ระบุประเภทของการจับคู่ คุณสามารถเลือกการจับคู่แบบตรงทั้งหมด ไวด์การ์ด หรือการจับคู่โดยประมาณได้
  • 0   – ตรงทั้งหมด (ค่าเริ่มต้น)
  • 1   – แน่นอนหรือใหญ่กว่าถัดไป
  • -1 – แน่นอนหรือเล็กกว่าถัดไป
  • 2   – การจับคู่ไวด์การ์ด
• [ค้นหา_โหมด]; [ไม่บังคับ]: ตัดสินใจทิศทางการค้นหา เช่น first-to-last หรือ Last-to-first
  • 1 – ค้นหาตั้งแต่แรกไปสุดท้าย
  • -1 – ค้นหาจากสุดท้ายไปแรก
  • 2 – การค้นหาแบบไบนารี (จากน้อยไปมาก)
  • -2 – การค้นหาแบบไบนารี (เรียงลำดับจากมากไปน้อย)

1. การใช้ XLOOKUP สำหรับเกณฑ์เดี่ยว

เรามาค้นหายอดขายที่ใกล้เคียงที่สุดที่ $100 จากชุดข้อมูลการขายของเราเพื่อดูว่าลูกค้าที่ใช้จ่ายเกือบ $100 ในคำสั่งซื้อใดๆ กัน แทรกสูตรต่อไปนี้

สูตร:

=XLOOKUP(100, G2:G71, A2:G71, “ไม่พบ”, 1)

สูตรนี้ค้นหา 100 ในช่วง G2:G71 และส่งกลับแถวที่สอดคล้องกันจาก A2:G71 หากไม่พบค่าที่ใกล้เคียงที่สุดของ 100 ก็จะแสดงข้อความ “ไม่พบ”

เอาต์พุต:

1007     4/1/2024            แดเนียล มาร์ติเนซ             ตะวันออก      39.99    3             119.97

2. การใช้ XLOOKUP ที่มีหลายเกณฑ์

คุณสามารถค้นหาที่ซับซ้อนมากขึ้นได้โดยการเชื่อมโยงหลายเกณฑ์เข้าด้วยกันโดยใช้ XLOOKUP มาสำรวจสูตรกันดีกว่า

สูตร:

=XLOOKUP(“เมลิสซา โลเปซ” &“ตะวันตก”, C2:C71 &D2:D71, A2:G71)

สูตรนี้จะเชื่อมชื่อและภูมิภาคของลูกค้าเข้าด้วยกัน และค้นหาค่ารวมภายในอาร์เรย์การค้นหาที่ต่อกัน จะดึงข้อมูลที่เกี่ยวข้องจากช่วงที่เลือก

เอาต์พุต:

1012     6/1/2024             เมลิสซา โลเปซ  เวสต์     79.99    2             159.98

INDEX-MATCH-MATCH สำหรับเทคนิคการค้นหาขั้นสูง

INDEX-MATCH-MATCH จะใช้เมื่อคุณต้องการค้นหาค่าตามเกณฑ์ทั้งแถวและคอลัมน์ เหมาะสำหรับตารางข้อมูล 2D

ไวยากรณ์:

=INDEX(อาร์เรย์, MATCH(row_lookup_value, row_lookup_array, 0), =MATCH(column_lookup_value, column_lookup_array, 0))

• อาร์เรย์: ช่วงของเซลล์มีค่าที่คุณต้องการดึงข้อมูล
• MATCH(row_lookup_value, row_lookup_array, 0): ส่งกลับหมายเลขแถว
  • row_lookup_value: ค่าที่จะค้นหาในแถว
  • row_lookup_array: ช่วงแถวที่จะค้นหาภายใน
  • 0 – ตรงทั้งหมด

• MATCH(column_lookup_value, column_lookup_array, 0): ส่งกลับหมายเลขคอลัมน์
  • คอลัมน์_lookup_value: ค่าที่จะค้นหาในคอลัมน์
  • คอลัมน์_lookup_array: ช่วงคอลัมน์ที่จะค้นหาภายใน
  • 0 – ตรงทั้งหมด

1. การค้นหาแบบ 2 มิติสำหรับแถวและคอลัมน์

ลองดูยอดขายสำหรับลูกค้าที่มียอดขายจำนวนหนึ่งโดยใช้สูตร INDEX-MATCH-MATCH แทรกสูตรต่อไปนี้

=INDEX(A2:G71, MATCH(“John Smith”, C2:C71, 0), MATCH(“ยอดขาย”, A1:G1, 0))

สูตรนี้ค้นหา John Smith ในช่วง C2:C71 และ "ยอดขาย" ใน A1:G1 จากนั้นจะกลับสู่ค่าที่ตัดกันใน A2:G71

เอาต์พุต:

99.98

2. สูตร INDEX-MATCH-MATCH ขั้นสูงสำหรับการค้นหา 3 มิติ

เนื่องจากโดยทั่วไป INDEX-MATCH-MATCH นั้นใช้สำหรับการค้นหาแบบสองมิติ คุณจึงสามารถขยายเป็นการค้นหาแบบสามมิติได้โดยใช้สูตรอาร์เรย์ที่มีฟังก์ชัน MATCH หลายฟังก์ชัน เหมาะอย่างยิ่งสำหรับชุดข้อมูลขนาดใหญ่และการจับคู่เกณฑ์ที่ซับซ้อน ซึ่งมีประสิทธิภาพเหนือกว่า VLOOKUP ในด้านความยืดหยุ่นและความสามารถ

สูตร:

=INDEX(G2:G71, MATCH(1, (C2:C71=”จอห์น สมิธ”) * (D2:D71=”ใต้”) * (F2:F71=4), 0))

สูตรนี้จะค้นหายอดขายโดยจับคู่เกณฑ์สามประการ ได้แก่ ชื่อลูกค้าในคอลัมน์ C ภูมิภาคในคอลัมน์ D และปริมาณในคอลัมน์ F เงื่อนไขจะรวมกันโดยใช้การคูณเป็นฟังก์ชัน AND ช่วยให้ MATCH ค้นหาแถวที่ตรงตามเงื่อนไขทั้งหมด และ INDEX จะส่งกลับค่าที่สอดคล้องกันจาก G2:G71

เอาต์พุต:

129.99

ข้อดีของการใช้ XLOOKUP และ INDEX-MATCH

เหตุใดจึงเลือก XLOOKUP มากกว่า VLOOKUP

• การค้นหาแบบสองทิศทาง :XLOOKUP สามารถค้นหาค่าได้ทั้งสองทิศทาง ในขณะที่ VLOOKUP สามารถค้นหาจากซ้ายไปขวาเท่านั้น
• ไม่มีหมายเลขดัชนีคอลัมน์ :ไม่จำเป็นต้องระบุหมายเลขดัชนีคอลัมน์ ดังนั้นการเปลี่ยนแปลงคอลัมน์จะไม่ส่งผลกระทบต่อ
• การจับคู่แบบตรงทั้งหมดเริ่มต้น :ตามค่าเริ่มต้น XLOOKUP จะค้นหาการจับคู่แบบตรงทั้งหมด เพื่อลดข้อผิดพลาดให้เหลือน้อยที่สุด
• การค้นหาแบบไวด์การ์ดใน XLOOKUP :คุณสามารถใช้ไวด์การ์ดเช่น * และ? ด้วย XLOOKUP เมื่อทำการค้นหาข้อความ
• การจัดการข้อผิดพลาด :คุณสามารถพูดถึงสิ่งที่จะส่งคืนหากไม่พบรายการที่ตรงกัน

ประโยชน์ของ INDEX-MATCH เหนือ VLOOKUP

• โครงสร้างตารางที่ยืดหยุ่น :INDEX-MATCH ไม่ได้รับผลกระทบจากการแทรกหรือการลบคอลัมน์
• การค้นหาด้านซ้าย :ต่างจาก VLOOKUP ตรงที่ INDEX-MATCH สามารถมองไปทางซ้ายได้
• ประสิทธิภาพ :มีประสิทธิภาพมากกว่าในชุดข้อมูลขนาดใหญ่ เนื่องจาก MATCH เร็วกว่าในบางกรณีเมื่อเทียบกับการคำนวณ VLOOKUP ใหม่

บทสรุป

เทคนิคการค้นหาขั้นสูง เช่น XLOOKUP และ INDEX-MATCH-MATCH จำเป็นสำหรับการจัดการข้อมูลขั้นสูงใน Excel ฟังก์ชันเหล่านี้จะอัปเดตการเปลี่ยนแปลงข้อมูลโดยอัตโนมัติ ฟังก์ชันเหล่านี้มอบความยืดหยุ่น ความแม่นยำ และประสิทธิภาพที่เหนือกว่า VLOOKUP แบบดั้งเดิม ทำให้มีความสำคัญสำหรับทุกคนที่ต้องการยกระดับทักษะ Excel