วิธีใช้ฟังก์ชัน XLOOKUP ใน Microsoft Excel

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

XLOOKUP คืออะไร?

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

ในขณะนี้ XLOOKUP มีให้สำหรับผู้ใช้ในโปรแกรม Insiders เท่านั้น ทุกคนสามารถเข้าร่วมโปรแกรม Insiders เพื่อเข้าถึงฟีเจอร์ใหม่ล่าสุดของ Excel ได้ทันทีที่พร้อมใช้งาน Microsoft จะเริ่มเปิดตัวสำหรับผู้ใช้ Office 365 ทุกคนในไม่ช้า

วิธีใช้ฟังก์ชัน XLOOKUP

มาดูตัวอย่างการใช้งาน XLOOKUP กันเลย ใช้ข้อมูลตัวอย่างด้านล่าง เราต้องการส่งคืนแผนกจากคอลัมน์ F สำหรับแต่ละ ID ในคอลัมน์ A

นี่คือตัวอย่างการค้นหาแบบตรงทั้งหมดแบบคลาสสิก ฟังก์ชัน XLOOKUP ต้องการข้อมูลเพียงสามส่วน

รูปภาพด้านล่างแสดง XLOOKUP ที่มีอาร์กิวเมนต์ 6 รายการ แต่จำเป็นต้องมีเพียงสามอาร์กิวเมนต์แรกสำหรับการจับคู่แบบตรงทั้งหมด มาเน้นที่สิ่งเหล่านี้กัน:

• Lookup_value: สิ่งที่คุณกำลังมองหา
• Lookup_array: จะดูได้ที่ไหน
• Return_array: ช่วงที่มีค่าที่จะส่งคืน

สูตรต่อไปนี้จะใช้ได้กับตัวอย่างนี้: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

ตอนนี้ มาสำรวจข้อดีสองสามข้อที่ XLOOKUP มีเหนือ VLOOKUP ที่นี่

ไม่มีหมายเลขดัชนีคอลัมน์เพิ่มเติม

อาร์กิวเมนต์ที่สามที่น่าอับอายของ VLOOKUP คือการระบุหมายเลขคอลัมน์ของข้อมูลที่จะส่งคืนจากอาร์เรย์ของตาราง นี่ไม่ใช่ปัญหาอีกต่อไปเพราะ XLOOKUP ให้คุณเลือกช่วงที่จะส่งคืนได้ (คอลัมน์ F ในตัวอย่างนี้)

และอย่าลืมว่า XLOOKUP สามารถดูข้อมูลทางด้านซ้ายของเซลล์ที่เลือกได้ ซึ่งต่างจาก VLOOKUP เพิ่มเติมเกี่ยวกับเรื่องนี้ด้านล่าง

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

การจับคู่แบบตรงทั้งหมดคือค่าเริ่มต้น

มันมักจะสับสนเสมอเมื่อเรียนรู้ VLOOKUP ว่าทำไมคุณต้องระบุการจับคู่แบบตรงทั้งหมดจึงเป็นที่ต้องการ

โชคดีที่ XLOOKUP มีค่าเริ่มต้นเป็นการจับคู่แบบตรงทั้งหมด ซึ่งเป็นสาเหตุที่พบบ่อยกว่ามากในการใช้สูตรการค้นหา) ซึ่งจะช่วยลดความจำเป็นในการตอบอาร์กิวเมนต์ที่ 5 นั้น และรับรองข้อผิดพลาดน้อยลงโดยผู้ใช้ที่ไม่เคยใช้สูตรนี้

กล่าวโดยย่อ XLOOKUP ถามคำถามน้อยกว่า VLOOKUP ใช้งานง่ายกว่า และทนทานกว่าด้วย

XLOOKUP สามารถมองไปทางซ้ายได้

ความสามารถในการเลือกช่วงการค้นหาทำให้ XLOOKUP ใช้งานได้หลากหลายกว่า VLOOKUP ด้วย XLOOKUP ลำดับของคอลัมน์ตารางไม่สำคัญ

VLOOKUP ถูกจำกัดโดยการค้นหาคอลัมน์ซ้ายสุดของตารางแล้วกลับจากจำนวนคอลัมน์ที่ระบุไปทางขวา

ในตัวอย่างด้านล่าง เราจำเป็นต้องค้นหา ID (คอลัมน์ E) และส่งคืนชื่อบุคคล (คอลัมน์ D)

สูตรต่อไปนี้สามารถทำได้: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

จะทำอย่างไรถ้าไม่พบ

ผู้ใช้ฟังก์ชันการค้นหาคุ้นเคยกับข้อความแสดงข้อผิดพลาด #N/A มาก ซึ่งจะทักทายพวกเขาเมื่อ VLOOKUP หรือฟังก์ชัน MATCH ไม่พบสิ่งที่ต้องการ และมักมีเหตุผลเชิงตรรกะสำหรับเรื่องนี้

ดังนั้น ผู้ใช้จึงค้นหาวิธีซ่อนข้อผิดพลาดนี้อย่างรวดเร็ว เนื่องจากไม่ถูกต้องหรือมีประโยชน์ และแน่นอนว่ามีหลายวิธีที่จะทำเช่นนั้นได้

XLOOKUP มาพร้อมกับอาร์กิวเมนต์ "หากไม่พบ" ในตัวเพื่อจัดการกับข้อผิดพลาดดังกล่าว มาดูการใช้งานจริงกับตัวอย่างที่แล้วกันแต่พิมพ์ ID ผิด

สูตรต่อไปนี้จะแสดงข้อความ “Incorrect ID” แทนที่จะเป็นข้อความแสดงข้อผิดพลาด: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")

การใช้ XLOOKUP สำหรับการค้นหาช่วง

แม้ว่าจะไม่เหมือนกับการจับคู่แบบตรงทั้งหมด แต่การใช้สูตรการค้นหาอย่างมีประสิทธิภาพมากคือการมองหาค่าในช่วง ยกตัวอย่างต่อไปนี้ เราต้องการคืนส่วนลดตามจำนวนเงินที่ใช้ไป

คราวนี้เราไม่ได้มองหาค่าเฉพาะ เราจำเป็นต้องรู้ว่าค่าในคอลัมน์ B อยู่ในช่วงใดในคอลัมน์ E ซึ่งจะเป็นตัวกำหนดส่วนลดที่ได้รับ

XLOOKUP มีอาร์กิวเมนต์ที่ห้าที่เป็นตัวเลือก (โปรดจำไว้ว่าจะมีค่าเริ่มต้นเป็นการจับคู่แบบตรงทั้งหมด) ที่มีชื่อโหมดการจับคู่

คุณจะเห็นว่า XLOOKUP มีความสามารถมากกว่าด้วยการจับคู่โดยประมาณมากกว่า VLOOKUP

มีตัวเลือกในการค้นหาค่าที่ใกล้เคียงที่สุดซึ่งน้อยกว่า (-1) หรือใกล้เคียงที่สุดมากกว่า (1) ค่าที่ค้นหา นอกจากนี้ยังมีตัวเลือกในการใช้อักขระตัวแทน (2) เช่น ? หรือ *. การตั้งค่านี้ไม่ได้เปิดอยู่ตามค่าเริ่มต้นเหมือนใน VLOOKUP

สูตรในตัวอย่างนี้จะส่งกลับค่าที่ใกล้เคียงที่สุดน้อยกว่าค่าที่ค้นหาหากไม่พบการจับคู่ที่ตรงกันทั้งหมด: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)

อย่างไรก็ตาม มีข้อผิดพลาดในเซลล์ C7 ที่ส่งคืนข้อผิดพลาด #N/A (ไม่ได้ใช้อาร์กิวเมนต์ 'if not found') สิ่งนี้น่าจะได้ส่วนลดกลับมาเป็น 0% เนื่องจากการใช้จ่าย 64 ไม่ผ่านเกณฑ์ส่วนลดใดๆ

ข้อดีอีกประการของฟังก์ชัน XLOOKUP คือไม่ต้องการช่วงการค้นหาในลำดับจากน้อยไปมากเหมือนที่ VLOOKUP ทำ

ป้อนแถวใหม่ที่ด้านล่างของตารางค้นหา จากนั้นเปิดสูตร ขยายช่วงที่ใช้โดยคลิกและลากมุม

สูตรแก้ไขข้อผิดพลาดทันที ไม่ใช่ปัญหาที่จะมี "0" ที่ด้านล่างของช่วง

โดยส่วนตัวแล้ว ฉันยังคงจัดเรียงตารางตามคอลัมน์การค้นหา การมีเลข "0" ที่ด้านล่างจะทำให้ฉันแทบบ้า แต่ความจริงที่ว่าสูตรไม่แตกนั้นยอดเยี่ยม

XLOOKUP แทนที่ฟังก์ชัน HLOOKUP ด้วย

ดังที่ได้กล่าวมาแล้ว ฟังก์ชัน XLOOKUP ก็อยู่ที่นี่เพื่อแทนที่ HLOOKUP หนึ่งฟังก์ชันเพื่อแทนที่สอง ยอดเยี่ยม!

ฟังก์ชัน HLOOKUP คือการค้นหาแนวนอน ใช้สำหรับค้นหาตามแถว

ไม่เป็นที่รู้จักในฐานะพี่น้อง VLOOKUP แต่มีประโยชน์สำหรับตัวอย่างด้านล่างซึ่งส่วนหัวอยู่ในคอลัมน์ A และข้อมูลจะอยู่ในแถวที่ 4 และ 5

XLOOKUP สามารถดูได้ทั้งสองทิศทาง – ลงคอลัมน์และตามแถว เราไม่ต้องการสองหน้าที่ต่างกันอีกต่อไป

ในตัวอย่างนี้ สูตรนี้ใช้เพื่อส่งกลับมูลค่าการขายที่เกี่ยวข้องกับชื่อในเซลล์ A2 โดยจะดูตามแถวที่ 4 เพื่อค้นหาชื่อ และส่งคืนค่าจากแถวที่ 5: =XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUP สามารถมองจากล่างขึ้นบนได้

โดยปกติ คุณจะต้องค้นหารายการเพื่อค้นหาค่าที่เกิดขึ้นครั้งแรก (มักจะเท่านั้น) XLOOKUP มีอาร์กิวเมนต์ที่หกชื่อโหมดการค้นหา ซึ่งช่วยให้เราสามารถสลับการค้นหาเพื่อเริ่มต้นที่ด้านล่างและค้นหารายการเพื่อค้นหาค่าที่เกิดขึ้นครั้งสุดท้ายแทน

ในตัวอย่างด้านล่าง เราต้องการหาระดับสต็อกของแต่ละผลิตภัณฑ์ในคอลัมน์ A

ตารางค้นหาอยู่ในลำดับวันที่ และมีการตรวจสอบสต็อคหลายรายการต่อผลิตภัณฑ์ เราต้องการคืนระดับสต็อคจากครั้งล่าสุดที่มีการตรวจสอบ (การเกิด ID ผลิตภัณฑ์ครั้งล่าสุด)

อาร์กิวเมนต์ที่หกของฟังก์ชัน XLOOKUP มีสี่ตัวเลือก เราสนใจที่จะใช้ตัวเลือก

สูตรที่สมบูรณ์แสดงไว้ที่นี่: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)

ในสูตรนี้ อาร์กิวเมนต์ที่สี่และห้าจะถูกละเว้น เป็นทางเลือก และเราต้องการค่าเริ่มต้นของการจับคู่แบบตรงทั้งหมด

ปัดเศษขึ้น

ฟังก์ชัน XLOOKUP เป็นตัวตายตัวแทนของทั้งฟังก์ชัน VLOOKUP และ HLOOKUP ที่รอคอยอย่างใจจดใจจ่อ

บทความนี้ใช้ตัวอย่างที่หลากหลายเพื่อแสดงข้อดีของ XLOOKUP หนึ่งในนั้นคือ XLOOKUP สามารถใช้ในแผ่นงาน สมุดงาน และกับตารางได้ ตัวอย่างมีความเรียบง่ายในบทความเพื่อช่วยให้เราเข้าใจ

เนื่องจากอาร์เรย์ไดนามิกจะถูกนำมาใช้ใน Excel ในไม่ช้า จึงสามารถส่งคืนช่วงของค่าได้ นี่เป็นสิ่งที่ควรค่าแก่การสำรวจเพิ่มเติมอย่างแน่นอน

วันของ VLOOKUP ถูกนับ XLOOKUP อยู่ที่นี่และอีกไม่นานจะเป็นสูตรการค้นหาโดยพฤตินัย