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

Excel 2016 สำหรับสมาชิก Office 365 บน Windows และ Mac รองรับฟังก์ชัน XLOOKUP ใหม่ ซึ่งได้รับการขนานนามว่าเป็นการแทนที่ที่ง่ายกว่าและหลากหลายกว่าสำหรับฟังก์ชันการค้นหาแนวตั้งที่ได้รับความนิยมอย่างมาก (แต่มักไม่ร้ายแรง) VLOOKUP (ไม่รู้ว่า X ใน XLOOKUP คืออะไร ย่อมาจาก กว้างขวางบางที?)

สำหรับผู้ที่ยังไม่คุ้นเคยกับ VLOOKUP (ถือว่าเป็นฟังก์ชันที่สามที่ใช้มากที่สุดหลังจาก SUM และ AVERAGE) ฟังก์ชันนี้จะค้นหาในแนวตั้งตามแถวในคอลัมน์ซ้ายสุดของตารางค้นหาที่กำหนดจากบนลงล่างจนกว่าจะพบค่าใน คอลัมน์ค้นหาที่กำหนดโดยหมายเลขออฟเซ็ตที่ตรงกับหรือเกินกว่าที่คุณมองหา แม้ว่าจะมีประโยชน์อย่างมากสำหรับการค้นหารายการเฉพาะในรายการแบบยาวหรือคอลัมน์ของตารางข้อมูลในเวิร์กชีตของคุณ ฟังก์ชัน VLOOKUP มีข้อจำกัดหลายประการที่ฟังก์ชันการค้นหาใหม่นี้ไม่ได้แชร์ เช่น XLOOKUP:

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

ฟังก์ชัน XLOOKUP มีอาร์กิวเมนต์ที่เป็นไปได้ห้าอาร์กิวเมนต์ โดยสามอาร์กิวเมนต์แรกจำเป็น และอาร์กิวเมนต์ 2 รายการสุดท้ายเป็นทางเลือก โดยใช้ไวยากรณ์ต่อไปนี้:

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

อาร์กิวเมนต์lookup_value ที่จำเป็นจะกำหนดค่าหรือรายการที่คุณกำลังค้นหา อาร์กิวเมนต์look_up array ที่จำเป็นจะกำหนดช่วงของเซลล์ที่จะค้นหาค่าการค้นหานี้ และอาร์กิวเมนต์return_array จะกำหนดช่วงของเซลล์ที่มีค่าที่คุณต้องการให้ส่งคืนเมื่อ Excel พบค่าที่ตรงกันทั้งหมด

* พึงระลึกไว้เสมอว่าเมื่อกำหนดอาร์กิวเมนต์ lookup_array และ return_array ในฟังก์ชัน XLOOKUP ของคุณ ทั้งสองช่วงต้องมีความยาวเท่ากัน มิฉะนั้น Excel จะคืนค่า #VALUE! ข้อผิดพลาดในสูตรของคุณ ทั้งหมดนี้เป็นเหตุผลที่มากขึ้นสำหรับคุณที่จะใช้ชื่อช่วงหรือชื่อคอลัมน์ของตารางข้อมูลที่กำหนดเมื่อกำหนดขัดแย้งเหล่านี้มากกว่าที่จะชี้ให้พวกเขาออกหรือพิมพ์ในลำดับที่มือถือของตน

อาร์กิวเมนต์match_mode ที่เป็นตัวเลือกสามารถมีค่าใดค่าหนึ่งจากสี่ค่าต่อไปนี้:

• 0สำหรับการจับคู่แบบตรงทั้งหมด (ค่าเริ่มต้น เช่นเดียวกับเมื่อไม่มีการกำหนดอาร์กิวเมนต์match_mode )
• -1สำหรับการจับคู่แบบตรงทั้งหมดหรือค่าที่น้อยกว่าถัดไป
• 1สำหรับการจับคู่แบบตรงทั้งหมดหรือค่าที่มากกว่าถัดไป
• 2สำหรับการจับคู่บางส่วนโดยใช้อักขระตัวแทนที่เชื่อมต่อกับการอ้างอิงเซลล์ในอาร์กิวเมนต์lookup_value

อาร์กิวเมนต์search_mode ที่เป็นตัวเลือกสามารถมีค่าใดค่าหนึ่งจากสี่ค่าต่อไปนี้:

• 1เพื่อค้นหาก่อน-สุดท้าย นั่นคือ จากบนลงล่าง (ค่าเริ่มต้น เช่นเดียวกับเมื่อไม่มีการกำหนดอาร์กิวเมนต์search_mode )
• -1ค้นหาแบบตัวต่อตัว คือ จากล่างขึ้นบน
• 2สำหรับการค้นหาไบนารีในลำดับจากน้อยไปมาก
• -2สำหรับการค้นหาไบนารีในลำดับจากมากไปน้อย

วิธีที่ดีที่สุดในการทำความเข้าใจพลังและความเก่งกาจของฟังก์ชัน XLOOKUP ใหม่คือการดูการทำงานจริงในเวิร์กชีต Excel ในรูปต่อไปนี้ ฉันมีเวิร์กชีตที่มีตารางข้อมูลการขายแบบง่ายๆ ปี 2019 ที่จัดเรียงตามประเทศ ในการใช้ XLOOKUP เพื่อส่งคืนยอดขายรวมจากตารางนี้ในเซลล์ E4 ตามประเทศที่คุณป้อนในเซลล์ D4 ของเวิร์กชีต ให้ทำตามขั้นตอนเหล่านี้:

วางเคอร์เซอร์เซลล์ในเซลล์ E4 ของเวิร์กชีต

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

คลิกเซลล์ D4 ในเวิร์กชีตเพื่อป้อนการอ้างอิงเซลล์ลงในกล่องข้อความอาร์กิวเมนต์ Lookup_value

กด Tab เพื่อเลือกกล่องข้อความอาร์กิวเมนต์ Lookup_array จากนั้นคลิกเซลล์ A4 และกด Shift ค้างไว้ในขณะที่คุณกด Ctrl-ลูกศรลงเพื่อเลือก A4:A8 เป็นช่วงที่จะค้นหา (เนื่องจากช่วง A3:B8 ถูกกำหนดเป็นตารางข้อมูล Excel Table1[Country] ปรากฏในกล่องข้อความแทนช่วง A4:A8)

กด Tab เพื่อเลือกกล่องข้อความอาร์กิวเมนต์ Return_array จากนั้นคลิกเซลล์ B4 และกด Shift ค้างไว้ ขณะที่คุณกด Ctrl-ลูกศรลงเพื่อเลือก B4:B8 เป็นช่วงที่มีค่าที่จะส่งคืนตามผลลัพธ์ของการค้นหา (ที่ปรากฏเป็น Table1[Total Sales] ในกล่องข้อความ)

คลิกตกลงเพื่อป้อนสูตร XLOOKUP ในเซลล์ E4

การสร้างสูตรด้วย XLOOKUP ในเซลล์ E4 ที่ส่งกลับยอดขายตามประเทศที่ป้อนในเซลล์ D4

Excel ป้อนสูตร XLOOKUP ลงในเซลล์ E4 ของเวิร์กชีตและคืนค่า 4900 เป็นผลลัพธ์ เนื่องจากขณะนี้คอสตาริกาถูกป้อนลงในเซลล์การค้นหา D4 และดังที่คุณเห็นในตารางยอดขายปี 2019 นี่คือยอดขายรวมสำหรับประเทศนี้

เนื่องจาก XLOOKUP ทำงานจากขวาไปซ้ายเช่นเดียวกับจากซ้ายไปขวา คุณจึงสามารถใช้ฟังก์ชันนี้เพื่อส่งคืนประเทศจากตารางการขายนี้โดยพิจารณาจากยอดขายเฉพาะ รูปต่อไปนี้แสดงวิธีการที่คุณทำเช่นนี้ คราวนี้ คุณสร้างสูตร XLOOKUP ในเซลล์ D4 และกำหนดค่าที่ป้อนในเซลล์ E4 (ในกรณีนี้ 11,000) เป็นอาร์กิวเมนต์ lookup_value

นอกจากนี้ คุณป้อน -1 เป็นอาร์กิวเมนต์ match_mode เพื่อแทนที่ค่าเริ่มต้นของการจับคู่แบบตรงทั้งหมดของฟังก์ชัน เพื่อให้ Excel ส่งคืนประเทศที่มีการจับคู่แบบตรงทั้งหมดกับมูลค่าการขายที่ป้อนในเซลล์การค้นหา E4 หรือเซลล์ที่มียอดขายรวมที่ต่ำกว่าถัดไป (เม็กซิโกด้วย ในกรณีนี้คือ 10,000 ดอลลาร์ เนื่องจากไม่มีประเทศใดในตารางนี้ที่มียอดขายรวม 11,000 ดอลลาร์) หากไม่มีการกำหนดอาร์กิวเมนต์ match_mode สำหรับสูตรนี้ Excel จะส่งกลับ #NA เป็นผลลัพธ์ เนื่องจากไม่มีการจับคู่แบบตรงทั้งหมดกับ $11,000 ในตารางการขายนี้

การสร้างสูตรด้วย XLOOKUP ในเซลล์ D4 ที่ส่งคืนประเทศตามยอดขายที่ป้อนในเซลล์ E4

เนื่องจากฟังก์ชัน XLOOKUP สามารถค้นหาในแนวนอนตามคอลัมน์ได้อย่างสะดวกสบายเท่าๆ กัน ขณะที่ค้นหาในแนวตั้งทีละแถว คุณสามารถใช้ฟังก์ชันนี้เพื่อสร้างสูตรที่ดำเนินการค้นหาแบบสองทาง (แทนที่ความจำเป็นในการสร้างสูตรที่รวมฟังก์ชัน INDEX และ MATCH เข้าด้วยกันเป็น ในอดีตที่ผ่านมา). รูปภาพต่อไปนี้ ซึ่งมีตารางกำหนดการผลิตปี 2019 สำหรับหมายเลขชิ้นส่วน AB-100 ถึง AB-103 สำหรับเดือนเมษายนถึงธันวาคม แสดงให้คุณเห็นถึงวิธีการดำเนินการ

การสร้างสูตรด้วยฟังก์ชัน XLOOKUP ที่ซ้อนกันเพื่อส่งคืนจำนวนหน่วยที่ผลิตสำหรับชิ้นส่วนในเดือนใดโดยเฉพาะ

ในเซลล์ B12 ฉันสร้างสูตรต่อไปนี้:

=XLOOKUP(part_lookup,$A$3:$A$6,XLOOKUP(date_lookup,$B$2:$J$2,$B$3:$J$6))

สูตรนี้เริ่มต้นด้วยการกำหนดฟังก์ชัน XLOOKUP ที่ค้นหาในแนวตั้งตามแถวเพื่อหาค่าที่ตรงกันกับรายการชิ้นส่วนที่สร้างในเซลล์ที่ชื่อ part_lookup (เซลล์ B10 ในกรณีนี้) ในช่วงเซลล์ $A$3:$A$6 ของตารางการผลิต . อย่างไรก็ตาม โปรดทราบว่าอาร์กิวเมนต์ return_array สำหรับฟังก์ชัน LOOKUP ดั้งเดิมนี้เป็นฟังก์ชัน XLOOKUP ตัวที่สอง

ฟังก์ชัน XLOOKUP แบบซ้อนที่สองนี้จะค้นหาช่วงเซลล์ $B$2:$J$2 ในแนวนอนตามคอลัมน์เพื่อให้ตรงกับรายการวันที่ในเซลล์ที่ชื่อ date_lookup (ในกรณีนี้คือเซลล์ B11) อาร์กิวเมนต์ return_array สำหรับฟังก์ชัน XLOOKUP ที่ซ้อนกันเป็นวินาทีนี้คือ $B$3:$J$6 ซึ่งเป็นช่วงเซลล์ของค่าการผลิตทั้งหมดในตาราง

วิธีการทำงานของสูตรนี้คือ Excel จะคำนวณผลลัพธ์ของฟังก์ชัน XLOOKUP ที่สองที่ซ้อนกันอยู่ก่อน โดยทำการค้นหาในแนวนอน ซึ่งในกรณีนี้ จะส่งกลับอาร์เรย์ในช่วงเซลล์ D3: D6 ของคอลัมน์ Jun-19 (ด้วยค่า: 438, 153, 306 และ 779) เป็นผลลัพธ์ ผลลัพธ์นี้จะกลายเป็นอาร์กิวเมนต์ return_array สำหรับฟังก์ชัน XLOOKUP ดั้งเดิมที่ทำการค้นหาในแนวตั้งทีละแถวเพื่อให้ตรงกับรายการหมายเลขชิ้นส่วนที่ทำในเซลล์ B11 (ชื่อ part_lookup) เนื่องจากในตัวอย่างนี้ เซลล์ part_lookup นี้มี AB-102 สูตรจะส่งกลับเฉพาะค่าการผลิตในวันที่ 19 มิ.ย. ที่ 306 จากผลลัพธ์ของฟังก์ชัน XLOOKUP ตัวที่สองถัดไป

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

* แต่น่าเสียดายที่ฟังก์ชั่น XLOOKUP คือไม่เข้ากันได้กับรุ่นก่อนหน้าของ Microsoft Excel ที่สนับสนุนเฉพาะ VLOOKUP และ HLOOKUP ฟังก์ชั่นหรือเข้ากันได้กับรุ่นปัจจุบันที่ยังไม่รวมไว้เป็นหนึ่งในฟังก์ชั่นการค้นหาของพวกเขาเช่น Excel 2019 และ Excel ออนไลน์ ซึ่งหมายความว่าถ้าคุณแชร์เวิร์กบุ๊กที่มีสูตร XLOOKUP กับเพื่อนร่วมงานหรือลูกค้าที่ใช้ Excel เวอร์ชันที่ไม่มีฟังก์ชันการค้นหาใหม่ สูตรเหล่านี้จะส่งคืน #NAME? ค่าความผิดพลาดเมื่อเปิดแผ่นงาน