ฟังก์ชัน XLOOKUP
ใช้ฟังก์ชัน XLOOKUP เพื่อค้นหาสิ่งต่างๆ ในตารางหรือช่วงตามแถว ตัวอย่างเช่น ค้นหาราคาของชิ้นส่วนรถยนต์ตามหมายเลขชิ้นส่วน หรือค้นหาชื่อพนักงานตามรหัสพนักงาน ด้วย XLOOKUP คุณสามารถดูคําที่ใช้ค้นหาในคอลัมน์หนึ่งและส่งกลับผลลัพธ์จากแถวเดียวกันในอีกคอลัมน์หนึ่งได้ โดยไม่คํานึงถึงด้านที่คอลัมน์ส่งกลับอยู่
หมายเหตุ: XLOOKUP ไม่พร้อมใช้งานใน Excel 2016 และ Excel 2019 อย่างไรก็ตาม คุณอาจพบสถานการณ์ในการใช้เวิร์กบุ๊กใน Excel 2016 หรือ Excel 2019 ที่มีฟังก์ชัน XLOOKUP ซึ่งสร้างขึ้นโดยบุคคลอื่นที่ใช้ Excel เวอร์ชันที่ใหม่กว่า
ไวยากรณ์
ฟังก์ชัน XLOOKUP จะค้นหาช่วงหรืออาร์เรย์ แล้วส่งกลับรายการที่สอดคล้องกับค่าที่ตรงกันแรกที่พบ ถ้าไม่มีค่าที่ตรงกัน XLOOKUP สามารถส่งกลับค่าที่ตรงกันที่ใกล้เคียงที่สุด (โดยประมาณ) ได้
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| อาร์กิวเมนต์ | คำอธิบาย |
|---|---|
| lookup_value ต้องระบุ* | ค่าที่จะค้นหา *ถ้าไม่ใส่ค่าอะไรไว้ XLOOKUP จะส่งกลับเซลล์ว่างที่พบใน lookup_array |
| lookup_array จำเป็น | อาร์เรย์หรือช่วงที่จะค้นหา |
| return_array จำเป็น | อาร์เรย์หรือช่วงที่จะส่งกลับ |
| [if_not_found] ไม่จำเป็น | ไม่พบค่าที่ตรงกันที่ถูกต้อง ให้ส่งกลับข้อความ [if_not_found] ที่คุณใส่ ถ้าไม่พบค่าที่ตรงกันที่ถูกต้อง และ [if_not_found] หายไป #N/A จะถูกส่งกลับ |
| [match_mode] ไม่จำเป็น | ระบุชนิดการจับคู่: 0 – ตรงกันทุกประการ ถ้าไม่พบ ให้ส่งคืน #N/A นี่คือค่าเริ่มต้น -1 – ตรงกันทุกประการ ถ้าไม่พบ ให้ส่งกลับรายการขนาดเล็กถัดไป 1 – ตรงกันทุกประการ ถ้าไม่พบ ให้ส่งกลับรายการที่มีขนาดใหญ่ขึ้นถัดไป 2 – การตรงกันกับอักขระตัวแทนที่มี *, ? และ ~ มีความหมายพิเศษ |
| [search_mode] ไม่จำเป็น | ระบุโหมดการค้นหาที่จะใช้: 1 – ดําเนินการค้นหาโดยเริ่มต้นที่รายการแรก นี่คือค่าเริ่มต้น -1 – ดําเนินการค้นหาย้อนกลับโดยเริ่มต้นที่รายการสุดท้าย 2 – ทําการค้นหาแบบไบนารีโดยอาศัย lookup_array เรียงลําดับจากน้อยไปหามาก ถ้าไม่ได้เรียงลําดับ ผลลัพธ์ที่ไม่ถูกต้องจะถูกส่งกลับ -2 – ทําการค้นหาแบบไบนารีโดยอาศัย lookup_array เรียงลําดับจากมากไปหาน้อย ถ้าไม่ได้เรียงลําดับ ผลลัพธ์ที่ไม่ถูกต้องจะถูกส่งกลับ |
ตัวอย่าง
ตัวอย่างที่ 1 ใช้ XLOOKUP เพื่อค้นหาชื่อประเทศในช่วง แล้วส่งกลับรหัสประเทศของโทรศัพท์ ซึ่งรวมถึงอาร์กิวเมนต์ lookup_value (เซลล์ F2) lookup_array (ช่วง B2:B11) และ return_array (ช่วง D2:D11) ซึ่งไม่มีอาร์กิวเมนต์ match_mode เนื่องจาก XLOOKUP สร้างค่าที่ตรงกันทุกประการตามค่าเริ่มต้น
หมายเหตุ: XLOOKUP ใช้อาร์เรย์การค้นหาและอาร์เรย์ที่ส่งกลับ ในขณะที่ VLOOKUP ใช้อาร์เรย์ตารางเดี่ยวตามด้วยหมายเลขดัชนีคอลัมน์ สูตร VLOOKUP ที่เทียบเท่าในกรณีนี้จะเป็น: =VLOOKUP(F2,B2:D11,3,FALSE)
ตัวอย่างที่ 2 ค้นหาข้อมูลพนักงานตามหมายเลข ID ของพนักงาน XLOOKUP ต่างจาก VLOOKUP คือสามารถส่งกลับอาร์เรย์ที่มีหลายรายการ ดังนั้นสูตรเดียวสามารถส่งกลับทั้งชื่อพนักงานและแผนกจากเซลล์ C5:D14
ตัวอย่างที่ 3 เพิ่มอาร์กิวเมนต์ if_not_found ลงในตัวอย่างก่อนหน้า
ตัวอย่างที่ 4 จะค้นหาในคอลัมน์ C สําหรับรายได้ส่วนบุคคลที่ใส่ในเซลล์ E2 และค้นหาอัตราภาษีที่ตรงกันในคอลัมน์ B ฟังก์ชันนี้จะตั้งค่าอาร์กิวเมนต์ if_not_found ให้ส่งกลับค่า 0 (ศูนย์) ถ้าไม่พบค่าใดเลย อาร์กิวเมนต์ match_mode ถูกตั้งค่าเป็น 1 ซึ่งหมายความว่าฟังก์ชันจะค้นหาค่าที่ตรงกันพอดี และถ้าไม่พบ จะส่งกลับรายการที่มีขนาดใหญ่กว่าถัดไป สุดท้าย อาร์กิวเมนต์ search_mode ถูกตั้งค่าเป็น 1 ซึ่งหมายความว่าฟังก์ชันจะค้นหาจากรายการแรกถึงรายการสุดท้าย
หมายเหตุ: คอลัมน์ lookup_array ของ XARRAY อยู่ทางด้านขวาของคอลัมน์ return_array ในขณะที่ VLOOKUP สามารถดูได้จากซ้ายไปขวาเท่านั้น
ตัวอย่าง 5 ใช้ฟังก์ชัน XLOOKUP ที่ซ้อนกันเพื่อดําเนินการจับคู่ทั้งแนวตั้งและแนวนอน ก่อนอื่นจะค้นหา กําไรขั้นต้น ในคอลัมน์ B แล้วค้นหา ไตรมาส 1 ในแถวบนสุดของตาราง (ช่วง C5:F5) และสุดท้ายจะส่งกลับค่าที่จุดตัดของทั้งสอง ซึ่งจะคล้ายกับการใช้ฟังก์ชัน INDEX และ MATCH ร่วมกัน
เคล็ดลับ: คุณยังสามารถใช้ XLOOKUP เพื่อแทนที่ฟังก์ชัน HLOOKUP ได้
หมายเหตุ: สูตรในเซลล์ D3:F3 คือ: =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3,$C 5:$G 5,$C 6:$G 17))
ตัวอย่าง 6 ใช้ ฟังก์ชัน SUM และฟังก์ชัน XLOOKUP ที่ซ้อนกันสองฟังก์ชัน เพื่อรวมค่าทั้งหมดระหว่างช่วงสองช่วง ในกรณีนี้เราต้องการรวมค่าสําหรับองุ่นกล้วยและลูกแพร์ซึ่งอยู่ระหว่างสองลูก
สูตรในเซลล์ E3 คือ: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))
วิธีการใช้งาน ฟังก์ชัน XLOOKUP จะส่งกลับช่วง ดังนั้นเมื่อคํานวณแล้ว สูตรจะจบลงด้วยลักษณะดังนี้ =SUM($E$7:$E$9) คุณสามารถดูวิธีการทํางานด้วยตัวคุณเองได้โดยการเลือกเซลล์ที่มีสูตร XLOOKUP ที่คล้ายกับสูตรนี้ จากนั้นเลือก สูตร > การตรวจสอบสูตร > ประเมินสูตร แล้วเลือก ประเมิน เพื่อทําตามขั้นตอนในการคํานวณ
หมายเหตุ: ขอบคุณ Microsoft Excel MVP, Bill Jelen ที่เสนอตัวอย่างนี้
อ้างอิง Microsoft
สอบถามเพิ่มเติม
💬Line: @monsterconnect https://lin.ee/cCTeKBE
☎️Tel: 02-026-6664
📩Email: [email protected]
📝 Price List สินค้า https://bit.ly/3mSpuQY
🏢 Linkedin : https://www.linkedin.com/company/monster-connect-co-ltd/
📺 YouTube : https://www.youtube.com/c/MonsterConnectOfficial
📲 TikTok : https://www.tiktok.com/@monsteronlines
🌍 Website : www.monsterconnect.co.th
