เปรียบเทียบฟังก์ชัน VLOOKUP กับ XLOOKUP ใน LibreOffice Calc: คู่มือสำหรับผู้เริ่มต้นใช้งาน

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

คู่มือนี้เริ่มต้นด้วยแบบจำลองทางความคิดพื้นฐาน จากนั้นจะอธิบายสูตรการใช้งานจริงที่คุณสามารถคัดลอกลงในสเปรดชีตขนาดเล็กได้ นอกจากนี้ยังอธิบายว่าฟังก์ชัน INDEX/MATCH ยังคงเหมาะสมกับการใช้งานในสถานการณ์ใดบ้าง อะไรที่อาจผิดพลาดได้ และฟังก์ชันใดเหมาะสมกับเวิร์กบุ๊กใหม่

สิ่งที่คุณควรรู้ก่อนใช้ฟังก์ชันค้นหาคืออะไร?

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

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

รายละเอียดหนึ่งที่มักทำให้ผู้ที่คุ้นเคยกับ Microsoft Excel ประหลาดใจคือ ตัวอย่างสูตรใน LibreOffice มักใช้เครื่องหมายเซมิโคลอนคั่นระหว่างตัวแปร ตัวอย่างเช่น=XLOOKUP(F2;A2:A6;D2:D6)Calc อาจปรับตัวคั่นให้เข้ากับภาษาท้องถิ่นของคุณ ดังนั้นให้ใช้ตัวคั่นที่โปรแกรมของคุณยอมรับ

VLOOKUP เทียบกับ XLOOKUP โดยสังเขป

เตรียมโต๊ะฝึกหัดขนาดเล็ก

สร้างคอลัมน์ A ถึง D โดยใช้หัวข้อProduct ID , Product Name , CategoryและPrice ตามลำดับ เพิ่ม ID สองสามตัว เช่น P001 ถึง P005 ใส่ ID ที่คุณต้องการค้นหาลงในเซลล์ F2 และใส่สูตรของคุณลงในเซลล์ G2

สูตรใน Calc เริ่มต้นด้วยเครื่องหมายเท่ากับ คุณสามารถพิมพ์สูตรลงในเซลล์โดยตรงหรือในแถบสูตรก็ได้คู่มือการป้อนสูตรอย่างเป็นทางการของ LibreOfficeอธิบายทั้งสองวิธีนี้ไว้แล้ว

ขั้นตอนที่ 1: ใช้ฟังก์ชัน VLOOKUP เพื่อค้นหาคำที่ตรงกันทุกประการ

สมมติว่า P004 อยู่ใน F2 และคุณต้องการราคาจากคอลัมน์ D ให้ป้อน:

=VLOOKUP(F2;$A$2:$D$6;4;0)

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

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

ขั้นตอนที่ 2: ใช้ XLOOKUP เมื่อคอลัมน์ผลลัพธ์อยู่ทางด้านซ้าย

ทีนี้ลองจัดเรียงตารางใหม่ โดยให้ชื่อผลิตภัณฑ์อยู่ในคอลัมน์ A และรหัสผลิตภัณฑ์อยู่ในคอลัมน์ C เนื่องจากฟังก์ชัน VLOOKUP จะจัดเรียงแบบนี้ได้ไม่สะดวก เพราะฟังก์ชันจะค้นหาเฉพาะคอลัมน์แรกของอาร์เรย์ในตารางและส่งคืนค่าทางด้านขวาเท่านั้น

XLOOKUP จะแยกอาร์เรย์ที่ใช้ค้นหา ออก จากอาร์เรย์ผลลัพธ์ในการค้นหา P003 ในคอลัมน์ C และส่งคืนชื่อผลิตภัณฑ์จากคอลัมน์ A ให้ใช้:

=XLOOKUP(F2;$C$2:$C$6;$A$2:$A$6;"Not found")

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

ขั้นตอนที่ 3: จัดการกับรหัสที่หายไปโดยไม่แสดง #N/A

หากผู้ใช้ป้อน ID ที่ไม่มีอยู่จริง การค้นหาแบบตรงเป๊ะโดยทั่วไปจะให้ผลลัพธ์เป็น#N/Aใน XLOOKUP ให้ใส่ผลลัพธ์ที่เป็นมิตรต่อผู้ใช้ในอาร์กิวเมนต์ที่สี่:

=XLOOKUP(F2;$A$2:$A$6;$D$2:$D$6;"Not found";0)

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

อย่าซ่อนข้อผิดพลาดทุกอย่างโดยอัตโนมัติ หากรหัสประจำตัวควรมีอยู่เสมอ#N/Aอาจมีประโยชน์เพราะช่วยเปิดเผยข้อมูลต้นฉบับที่ผิดพลาดหรือไม่สมบูรณ์ ใช้ตัวเลือกสำรองที่เป็นมิตรเมื่อคาดว่าจะมีคีย์หายไป ไม่ใช่เมื่อคีย์เหล่านั้นบ่งชี้ถึงปัญหาด้านคุณภาพข้อมูล

ขั้นตอนที่ 4: ค้นหาจากด้านล่างขึ้นไปเพื่อหาข้อมูลที่ตรงกันล่าสุด

บางครั้งคีย์อาจปรากฏมากกว่าหนึ่งครั้ง ตัวอย่างเช่น บันทึกธุรกรรมอาจมีหลายแถวสำหรับลูกค้า C001 ฟังก์ชัน XLOOKUP มีอาร์กิวเมนต์โหมดการค้นหา ค่าของ`search_mode`-1จะค้นหาจากรายการสุดท้ายไปยังรายการแรก

หากต้องการดึงจำนวนเงินจากแถวสุดท้ายที่มีรหัสลูกค้าใน F2 ให้ใช้คำสั่ง:

=XLOOKUP(F2;$A$2:$A$8;$D$2:$D$8;"Not found";0;-1)

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

คุณควรใช้ INDEX และ MATCH เมื่อใด?

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

=INDEX($D$2:$D$6;MATCH(F2;$A$2:$A$6;0))

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

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

ข้อผิดพลาดทั่วไปที่ควรหลีกเลี่ยง

เว้นช่องสุดท้ายของอาร์กิวเมนต์ VLOOKUP ว่างไว้เมื่อต้องการผลลัพธ์ที่ตรงกันทุกประการ

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

การนับดัชนีคอลัมน์ VLOOKUP ผิดพลาด

ดัชนีคอลัมน์ของฟังก์ชัน VLOOKUP จะนับจากคอลัมน์แรกของอาร์เรย์ตาราง ไม่ใช่จากตัวอักษรคอลัมน์ของสเปรดชีต ใน A2:D6 คอลัมน์ D มีดัชนีเป็น 4 หากคุณแทรกคอลัมน์ลงในตารางนั้นในภายหลัง ดัชนีที่กำหนดไว้ล่วงหน้าอาจไม่ชี้ไปยังฟิลด์ที่คุณต้องการอีกต่อไป

การใช้ช่วงที่มีความยาวต่างกันใน XLOOKUP

อาร์เรย์การค้นหาและอาร์เรย์ผลลัพธ์ของคุณควรจัดเรียงให้ตรงกันแบบแถวต่อแถวหรือคอลัมน์ต่อคอลัมน์ ช่วงการค้นหาเช่น A2:A100 ที่จับคู่กับ D2:D90 เป็นข้อผิดพลาดในการออกแบบ เนื่องจากตำแหน่งที่ตรงกันไม่ได้ครอบคลุมระเบียนเดียวกัน

การใช้การค้นหาแบบไบนารีกับข้อมูลที่ไม่ได้เรียงลำดับ

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

สับสนระหว่างสัญลักษณ์ตัวแทน (wildcards) กับนิพจน์ปกติ (regular expressions)

Calc รองรับการจับคู่แบบไวด์การ์ดและแบบนิพจน์ปกติ แต่โหมดการจับคู่ทั้งสองนี้แยกจากกันใน XLOOKUP โหมดไวด์การ์ดใช้ตัวอักษรเช่น*และ?; โหมดนิพจน์ปกติใช้กลไกนิพจน์ปกติของ Calc เลือกโหมดอย่างรอบคอบแทนที่จะคิดว่ารูปแบบจะถูกตีความโดยอัตโนมัติ

ผู้เริ่มต้นควรเลือกใช้ฟังก์ชันค้นหาใด?

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

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

นิสัยที่สำคัญที่สุดไม่ใช่การท่องจำทุกอาร์กิวเมนต์ แต่ให้ระบุสี่สิ่งก่อนเขียนสูตร: ฉันกำลังค้นหาค่าอะไร ฉันกำลังค้นหาที่ไหน ช่วงใดควรให้คำตอบ และจะเกิดอะไรขึ้นถ้าคีย์หายไปเมื่อสิ่งเหล่านี้ชัดเจนแล้ว การเลือกใช้ VLOOKUP, XLOOKUP หรือ INDEX/MATCH ก็จะง่ายขึ้นมาก