Calc VLOOKUP เทียบกับ XLOOKUP: เลือกสูตรค้นหาที่เหมาะสม

หากเวิร์กบุ๊กของคุณจะเปิดใน LibreOffice 24.8 หรือเวอร์ชันที่ใหม่กว่า ให้ใช้ XLOOKUP สำหรับการค้นหาข้อมูลใหม่ส่วนใหญ่ โดยค่าเริ่มต้นจะเป็นการค้นหาแบบตรงกันทุกประการ และช่วยให้คุณเลือกช่วงการค้นหาและช่วงผลลัพธ์แยกกันได้ ให้ใช้ VLOOKUP เมื่อคุณต้องการความเข้ากันได้กับ Calc เวอร์ชันเก่า หรือเมื่อค่าที่ค้นหาอยู่ในคอลัมน์แรกของตารางแบบกระชับ หากคุณยังคงใช้ VLOOKUP ให้ใส่ค่าอาร์กิวเมนต์ที่สี่เป็น 0 หรือ FALSE สำหรับการค้นหาแบบตรงกันทุกประการ

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

ความแตกต่างในทางปฏิบัติคืออะไร?

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

ตัวอย่างเช่น สมมติว่าเซลล์ A2:A5 มีรหัสสินค้า และเซลล์ B2:B5 มีราคา เซลล์ F2 มีรหัสที่ต้องการค้นหา ในโปรแกรม Calc สูตร VLOOKUP ที่ค้นหาแบบตรงกันทุกประการคือ:

=VLOOKUP(F2;A2:B5;2;0)

สูตร XLOOKUP ที่เทียบเท่าคือ:

=XLOOKUP(F2;A2:A5;B2:B5;"Not found")

ทั้งสองฟังก์ชันจะส่งคืนราคาที่ตรงกัน โดยปกติแล้ว Calc จะแสดงเครื่องหมายเซมิโคลอนระหว่างอาร์กิวเมนต์ของฟังก์ชันตามการตั้งค่าภาษา หากสูตรในโปรแกรมของคุณใช้เครื่องหมายจุลภาค ให้ใช้ตัวคั่นที่ Calc คาดหวัง ตัวอย่างใช้ผลลัพธ์จากเซลล์เดียวและช่วงการค้นหาแบบมิติเดียว

คุณควรเลือกใช้ XLOOKUP เมื่อใด?

เลือกใช้ XLOOKUP สำหรับเวิร์กบุ๊กใหม่เมื่อคุณทราบว่าผู้รับใช้ LibreOffice เวอร์ชัน 24.8 หรือใหม่กว่า ฟังก์ชันนี้มีให้ใช้งานใน LibreOffice 24.8 แล้ว ตามข้อมูลจากหน้าช่วยเหลืออย่างเป็นทางการของ XLOOKUPโหมดการจับคู่เริ่มต้นคือแบบตรงเป๊ะ ดังนั้นหากไม่มีคีย์ที่ตรงกันเป๊ะ ระบบจะไม่แสดงค่าใกล้เคียงโดยไม่แจ้งให้ทราบล่วงหน้า

รูปแบบฟังก์ชันคือ `\n` =XLOOKUP(search_criterion;search_array;result_array;[if_not_found];[match_mode];[search_mode])โดยอาร์กิวเมนต์สามตัวแรกระบุคีย์ ช่วงหนึ่งมิติที่จะค้นหา และช่วงที่จะส่งคืน อาร์กิวเมนต์ตัวที่สี่ เช่น `\n` "Not found"จะแทนที่ผลลัพธ์ `#N/A` ทั่วไปเมื่อไม่พบการจับคู่

ส่งคืนค่าจากคอลัมน์ทางด้านซ้าย

ฟังก์ชัน VLOOKUP ไม่สามารถส่งคืนค่าทางด้านซ้ายของคอลัมน์แรกในตารางได้ แต่ฟังก์ชัน XLOOKUP สามารถทำได้ เนื่องจากช่วงการค้นหาและช่วงผลลัพธ์เป็นอิสระต่อกัน ตัวอย่างเช่น หากรหัสประจำตัวอยู่ใน C2:C100 และชื่อแผนกอยู่ใน A2:A100 ให้ใช้:

=XLOOKUP(F2;C2:C100;A2:A100;"ID not found")

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

ส่งคืนรายการที่ตรงกันล่าสุด

เมื่อมีคีย์ซ้ำกันและคุณต้องการผลลัพธ์สุดท้ายที่ตรงกัน XLOOKUP รองรับการค้นหาแบบย้อนกลับด้วยโหมดการค้นหา สำหรับช่วงแนวตั้ง อาร์กิวเมนต์สุดท้าย-1จะค้นหาจากสุดท้ายไปยังแรก:

=XLOOKUP(F2;A2:A100;B2:B100;"Not found";0;-1)

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

เมื่อไหร่ที่ VLOOKUP ยังคงเป็นตัวเลือกที่ดีกว่า?

ควรใช้ VLOOKUP เมื่อไฟล์จำเป็นต้องเปิดใน LibreOffice เวอร์ชันเก่าที่ไม่รองรับ XLOOKUP หรือเมื่อแอปพลิเคชันปลายทางอาจไม่รู้จัก XLOOKUP เอกสารอย่างเป็นทางการของ Calc อธิบายถึงอาร์กิวเมนต์ตารางและดัชนีของ VLOOKUP และอาร์กิวเมนต์ช่วงที่เรียงลำดับ (ตัวเลือกเสริม) โปรดดูเอกสารอ้างอิงฟังก์ชันสเปรดชีตอย่างเป็นทางการ

สำหรับการค้นหาที่ตรงกันทุกประการ รูปแบบจะเป็น=VLOOKUP(key;table;column_index;0). ดัชนีจะนับคอลัมน์จากขอบด้านซ้ายของตารางที่เลือก: 1 หมายถึงคอลัมน์แรก 2 หมายถึงคอลัมน์ที่สอง คีย์ค้นหาต้องอยู่ในคอลัมน์แรกนั้น และฟิลด์ที่ส่งคืนต้องอยู่ทางด้านขวาของคอลัมน์แรกนั้น

อาร์กิวเมนต์ที่สี่มีความสำคัญ หากคุณละเว้นหรือตั้งค่าเป็น TRUE ฟังก์ชัน VLOOKUP จะใช้การจับคู่โดยประมาณและคาดหวังว่าคอลัมน์แรกจะเรียงลำดับจากน้อยไปมาก พฤติกรรมนี้อาจมีประโยชน์สำหรับช่วงข้อมูล เช่น ช่วงภาษี ที่แต่ละแถวแสดงขอบเขตล่าง แต่มีความเสี่ยงสำหรับรหัส ชื่อ และรายการที่ไม่เรียงลำดับ สำหรับกรณีเหล่านั้น ให้ระบุ 0 หรือ FALSE

ลักษณะการจับคู่และข้อผิดพลาดแตกต่างกันอย่างไร?

• การค้นหาแบบตรงกันทุกประการ:ฟังก์ชัน XLOOKUP ใช้การค้นหาแบบตรงกันทุกประการเป็นค่าเริ่มต้น ส่วนฟังก์ชัน VLOOKUP จำเป็นต้องตั้งค่าอาร์กิวเมนต์ที่สี่เป็น 0 หรือ FALSE เพื่อรับประกันการค้นหาแบบตรงกันทุกประการ
• หมายเหตุ:ฟังก์ชัน XLOOKUP สามารถส่งคืนข้อความหรือค่าที่เลือกไว้ในอาร์กิวเมนต์ที่สี่ได้ ส่วนฟังก์ชัน VLOOKUP โดยทั่วไปจะส่งคืนค่า #N/A หากต้องการข้อความที่กำหนดเอง ให้ใช้ IFNA ครอบไว้
• การจับคู่โดยประมาณ:ทั้งสองแบบรองรับการจับคู่ตามช่วง แต่ข้อมูลและโหมดที่เลือกต้องตรงกับกฎที่ต้องการ จัดเรียงข้อมูลตามความจำเป็นและตรวจสอบกรณีขอบเขต
• ทิศทางการค้นหา: ฟังก์ชัน XLOOKUP สามารถค้นหาจากด้านล่างโดยใช้พารามิเตอร์ search-mode ในขณะที่ฟังก์ชัน VLOOKUP จะคืนค่าแถวแรกที่ตรงกันในการใช้งานแบบปกติจากบนลงล่าง

หากต้องการ กำหนดค่าการตอบกลับสำหรับคีย์ที่หายไปแบบกำหนดเองด้วย VLOOKUP ให้ใช้ `<pre>` =IFNA(VLOOKUP(F2;A2:B5;2;0);"Not found")วิธีนี้จะทำให้การตั้งค่าการจับคู่ที่ตรงกันทุกประการยังคงมองเห็นได้ และจัดการเฉพาะข้อผิดพลาด "ไม่พร้อมใช้งาน" เท่านั้น หลีกเลี่ยงการห่อสูตรด้วยตัวจัดการข้อผิดพลาดแบบกว้างๆ เว้นแต่คุณต้องการซ่อนปัญหาที่ไม่เกี่ยวข้องอื่นๆ เช่น การอ้างอิงช่วงที่ไม่ถูกต้อง

คุณควรตรวจสอบอะไรบ้างก่อนที่จะแชร์เวิร์กบุ๊ก?

คู่มือช่วยเหลือของ LibreOffice ระบุว่าฟังก์ชัน XLOOKUP มีให้ใช้งานตั้งแต่เวอร์ชัน 24.8 นอกจากนี้ยังระบุว่า XLOOKUP ไม่ได้เป็นส่วนหนึ่งของมาตรฐาน OpenFormula ของ OpenDocument เวอร์ชัน 1.3 และระบุเนมสเปซฟังก์ชันของ Microsoft เพื่อความเข้ากันได้ นั่นหมายความว่ารูปแบบไฟล์เพียงอย่างเดียวไม่ได้รับประกันว่าแอปพลิเคชันสเปรดชีตเก่าหรือแอปพลิเคชันของบุคคลที่สามทุกตัวจะสามารถคำนวณฟังก์ชันนี้ได้ สำหรับสภาพแวดล้อมซอฟต์แวร์ที่หลากหลาย ให้ทดสอบเวิร์กบุ๊กในแอปพลิเคชันเป้าหมายที่เก่าที่สุด หรือใช้สูตรที่ได้รับการยอมรับ เช่น VLOOKUP แบบตรงกันทุกประการ ในกรณีที่รูปแบบของสูตรนั้นเหมาะสม

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

คุณจะตรวจสอบผลลัพธ์ได้อย่างไร?

1. ทดสอบคีย์ที่มีอยู่แล้วและเปรียบเทียบค่าที่ได้กับแถวต้นทาง
2. ทดสอบคีย์ที่ไม่มีอยู่จริง ตรวจสอบว่า XLOOKUP แสดงคีย์สำรองที่คุณเลือก หรือ VLOOKUP ส่งคืนข้อความ #N/A หรือ IFNA ตามที่คาดไว้
3. สำหรับการค้นหาแบบตรงเป๊ะด้วย VLOOKUP ให้ตรวจสอบว่าค่าสุดท้ายเป็น 0 หรือ FALSE หากคุณตั้งใจใช้การค้นหาแบบประมาณ ให้ตรวจสอบลำดับการเรียงและค่าที่อยู่เหนือ บน และใต้ขอบเขตที่กำหนด
4. สำหรับการใช้ XLOOKUP ที่มีคอลัมน์ส่งคืนทางด้านซ้ายหรือการค้นหาแบบย้อนกลับ ให้ทดสอบคีย์กับผลลัพธ์ที่ทราบแล้ว และเมื่อมีข้อมูลซ้ำกัน ให้ยืนยันว่าควรส่งคืนข้อมูลใด
5. บันทึกและเปิดเวิร์กบุ๊กอีกครั้งในโปรแกรม Calc หรือโปรแกรมสเปรดชีตเวอร์ชันเก่าที่สุดที่ผู้รับของคุณใช้ ผลลัพธ์ที่ถูกต้องบนคอมพิวเตอร์ของคุณไม่ได้หมายความว่าเวอร์ชันเก่ากว่าจะรองรับฟังก์ชันเดียวกัน

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