วิธีใช้ฟังก์ชัน 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 ที่มีข้อโต้แย้งหกข้อ แต่จำเป็นต้องมีเพียงสามข้อแรกเท่านั้นสำหรับการจับคู่แบบตรงทั้งหมด มาเน้นที่พวกเขา:
- 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 ตั้งค่าเริ่มต้นเป็นการจับคู่แบบตรงทั้งหมดซึ่งเป็นเหตุผลที่พบบ่อยมากในการใช้สูตรการค้นหา) ซึ่งจะช่วยลดความจำเป็นในการตอบข้อโต้แย้งที่ห้าและทำให้ผู้ใช้ที่เพิ่งเริ่มใช้สูตรมีข้อผิดพลาดน้อยลง
ดังนั้นในระยะสั้น 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 ที่พิมพ์ผิด
สูตรต่อไปนี้จะแสดงข้อความ "ID ไม่ถูกต้อง" แทนข้อความแสดงข้อผิดพลาด: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")
การใช้ XLOOKUP สำหรับการค้นหาช่วง
แม้ว่าจะไม่ธรรมดาเท่ากับการจับคู่แบบตรงทั้งหมด แต่การใช้สูตรการค้นหาที่มีประสิทธิภาพมากคือการมองหาค่าในช่วง ใช้ตัวอย่างต่อไปนี้ เราต้องการคืนส่วนลดขึ้นอยู่กับจำนวนเงินที่ใช้ไป
คราวนี้เราไม่ได้มองหาค่าเฉพาะ เราจำเป็นต้องทราบว่าค่าในคอลัมน์ B อยู่ในช่วงใดในคอลัมน์ E ซึ่งจะเป็นตัวกำหนดส่วนลดที่ได้รับ
XLOOKUP มีอาร์กิวเมนต์ที่ 5 ซึ่งเป็นทางเลือก (โปรดจำไว้ว่าค่าเริ่มต้นคือการจับคู่แบบตรงทั้งหมด) ที่ชื่อโหมดการจับคู่
คุณจะเห็นได้ว่า 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
ตารางการค้นหาอยู่ในลำดับวันที่และมีการตรวจสอบสต็อกหลายรายการต่อผลิตภัณฑ์ เราต้องการคืนระดับสต็อกจากครั้งสุดท้ายที่ตรวจสอบ (การเกิดครั้งสุดท้ายของรหัสผลิตภัณฑ์)
อาร์กิวเมนต์ที่หกของฟังก์ชัน XLOOKUP มีสี่ตัวเลือก เราสนใจที่จะใช้ตัวเลือก“ ค้นหาจากผู้ใช้ไปก่อน”
สูตรที่สมบูรณ์แสดงที่นี่: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)
ในสูตรนี้อาร์กิวเมนต์ที่สี่และห้าถูกละเว้น เป็นทางเลือกและเราต้องการค่าเริ่มต้นของการจับคู่แบบตรงทั้งหมด
ปัดเศษขึ้น
ฟังก์ชัน XLOOKUP เป็นฟังก์ชันที่รอคอยอย่างใจจดใจจ่อสำหรับฟังก์ชัน VLOOKUP และ HLOOKUP
บทความนี้ใช้ตัวอย่างต่างๆเพื่อแสดงข้อดีของ XLOOKUP หนึ่งในนั้นคือ XLOOKUP สามารถใช้กับแผ่นงานสมุดงานและกับตารางได้ ตัวอย่างถูกเก็บไว้อย่างง่ายในบทความเพื่อช่วยให้เราเข้าใจ
เนื่องจากอาร์เรย์แบบไดนามิกถูกนำมาใช้ใน Excel เร็ว ๆ นี้จึงสามารถส่งคืนช่วงของค่าได้เช่นกัน นี่เป็นสิ่งที่ควรค่าแก่การสำรวจเพิ่มเติม
วันของ VLOOKUP จะถูกนับ XLOOKUP อยู่ที่นี่และเร็ว ๆ นี้จะเป็นสูตรการค้นหาโดยพฤตินัย
