xlookup vs vlookup Feature image

XLOOKUP คืออะไร? ดีกว่า VLOOKUP แค่ไหน อธิบายแบบเข้าใจง่าย

แบ่งปันบทความนี้ให้คนที่คุณหวังดี :

XLOOKUP คืออะไร? สำหรับเพื่อน ๆ ที่มีความจำเป็นต้องใช้โปรแกรมประเภท Spreadsheet เช่น Excel, Google Sheets เพื่อวิเคราะห์ข้อมูล จะพบว่านอกจากสูตรคำนวณเบื้องต้นแล้ว สูตรที่เป็นเพื่อนสนิทตลอดกาลก็คงหนีไม่พ้น VLOOKUP ที่เอาไว้ดึงข้อมูลจากตารางอื่น หรือชีทอื่น ๆ เพื่อ join ข้อมูลกันนั่นเอง

ในบทความนี้ตอนแรกกะแค่ว่าจะเป็นเทคนิคการใช้งาน สูตรต่าง ๆ แต่หลังจากที่ไปหาข้อมูลเพิ่มเติมก็พบว่ามัน Nerd ไปได้มากกว่านั้น แล้วอย่างวาวาซาบิ (wawasabii.com) จะเอาแค่ข้อมูลธรรมดา ๆ มาได้อย่างไรเพื่อให้ผู้อ่านทุกคนได้เข้าใจมากขึ้นในตัวของทั้งสองสูตรนี้ เราย่อยมาให้แล้วครับ

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

VLOOKUP สูตรคู่สร้างคู่สมกับ Spreadsheet

ใช้คำนี้ก็ถือว่าอาจจะไม่เก่าไปมากนัก คิดว่าหลายคนยังทันนิตยสารคู่สร้างคู่สมกันอยู่และต้องยอมรับว่าสูตร lookup นี้เป็นที่นิยมจริง ๆ เราจะใช้เมื่อต้องการค้นหาสิ่งต่าง ๆ ในตารางหรือช่วงทีละแถว เช่น ค้นหาราคาของสินค้าตามเลขรหัสสินค้า หรือค้นหาชื่อพนักงานตามรหัสพนักงาน

ในความเป็นจริงผมมองว่ามันสะดวกในระดับหนึ่ง มันให้ความรู้สึกเหมือนสามารถใช้ Key เพื่อค้นหาเอา Value ออกมาได้ สามารถดึงค่าจากหลาย ๆ ตารางมาสรุปในตารางเดียวได้ เอาเป็นว่าถ้าใครเคยต้องทำงานร่วมกับชีทอื่น ต้องเชื่อมหลายตาราง หรือว่าต้องใช้พวก Customer ID, Product ID, Employee ID ผมมั่นใจว่าจะต้องมีประสบการณ์การใช้งานสูตรนี้อย่างแน่นอนครับ

แน่นอนว่า บางครั้งมันทำให้รู้สึกยุ่งยากมาก555 เพราะคอลัมน์อ้างอิงต้องเป็นคอลัมน์แรกเท่านั้น ต้องมาใส่ FALSE ทุกครั้งในการเลือกใช้ Match mode

XLOOKUP คืออะไร? ทำไมถึงควรเปลี่ยนเป็น XLOOKUP

ก่อนอื่นต้องหมายเหตุตรงนี้ไว้ก่อนว่า สำหรับเจ้าสิ่งนี้ไม่พร้อมใช้งานใน Excel 2016 และ Excel 2019 แต่ถ้าใช้เวอร์ชันที่ใหม่กว่าหรือใช้เป็น 365 ก็ไม่มีปัญหาครับ

ถ้าหากเพื่อน ๆ เข้าไปในเว็บสูตรและฟังชันก์ของ Microsoft ก็จะพบว่าสูตรนี้มันใหม่กว่า และยังป้ายยาอีกว่าทำให้ใช้งานง่ายกว่าและสะดวกกว่ารุ่นก่อน คือขนาดทีมฝั่ง Microsoft ที่พัฒนาปรับปรุงสูตรยังบอกเอง แล้วจะไม่เปลี่ยนได้ยังไง ต่อจากนี้จะขอเรียกว่า สูตร lookup รุ่นเก่า กับ lookup รุ่นใหม่ เพื่อให้แยกชัดเจนและอ่านได้เข้าใจมากยิ่งขึ้นครับ

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

การใช้งาน XLOOKUP

สูตรของมันจะมีหลาย Arguments ที่ต้องรับค่า 3 อันที่ต้องใส่เหมือนกัน แต่ปกติแล้วสูตรรุ่นเก่าแม้ว่าจะมีบังคับแค่ 3 อันแต่การใช้งานจริง เรามักจะใส่ค่าที่ 4 ไปด้วย คือ FALSE เนื่องจาก Match mode ที่เราใช้ในการใช้งานจริงโดยร้อยละ 99 คือต้องเป็นแบบ Exact match (ต้องเหมือนเป๊ะถึงจะดึงค่ามาให้)

สาเหตุนี้แหละที่ทำให้การใช้งานมันค่อนข้างทำให้ผมเองรู้สึกรำคาญอยู่บ่อยครั้งก่อนที่จะมารู้จักสูตรรุ่นใหม่นี้ เพราะหากการใช้งานโดยทั่วไปแล้วสูตรจะเป็น Match mode ที่เป็น Exact match ตั้งแต่ค่าเริ่มต้น ทำให้เราสามารถลากสูตรหรือพิมพ์สูตรเฉพาะ 3 arguments ก็ใช้งานได้ทันที!

อีกข้อจำกัดหนึ่งของ lookup รุ่นเก่าคือ คอลัมน์ที่เราต้องการ lookup มันจะไม่สามารถดูคอลัมน์ทางซ้าย (left lookup) ซึ่งคิดว่ามันเป็นข้อจำกัดที่น่าจะใหญ่ที่สุดแล้วสำหรับความแตกต่างระหว่างรุ่นใหม่กับเก่า เพราะหากตารางที่มีความซับซ้อนมาก ๆ บางครั้งคอลัมน์อ้างอิงเราก็จำเป็นต้องใช้ข้อมูลส่วนอื่นที่ต้องดึงข้อมูลมา ทำให้เราจะต้องเปลี่ยน table array อีก

เพื่อให้เข้าใจแบบเห็นภาพมากขึ้น ภาพด้านบนนี้คือการใช้สูตรรุ่นเก่า สังเกตได้ว่าจะต้องใส่คำว่า FALSE ลงไปด้วย (จริง ๆ สามารถใส่ 0 ลงไปได้ถ้าไม่อยากพิมพ์ยาว) และการใช้ col_index_num ก็ต้องนับจากซ้ายสุดที่เป็นการอ้างอิงของ Array ตารางนี้

ขณะที่เมื่อใช้สูตรรุ่นใหม่จะพบว่า เราให้ตัวสูตรมันวิ่งไปค้นหาได้โดยไม่ต้องมานั่งนับว่าคอลัมน์ที่เท่าไหร่ ช่วยประหยัดเวลาได้พอสมควรแถมไม่ต้องมานั่งพิมพ์ FALSE เพราะตัว default ของมันเป็น Exact match อยู่แล้ว

กรณีที่บอกว่า lookup แบบเก่าจะไม่สามารถดูคอลัมน์ทางซ้ายก็ตัวอย่างตามภาพนี้เลยครับ ถ้าข้อมูลที่มีความซับซ้อนมาก ต้องอ้างอิงหลาย ๆ คอลัมน์ก็จะเกิดปัญหาแบบนี้และปัญหานี้จะหมดไปถ้าเราใช้ lookup สูตรใหม่นั่นเอง

พอใช้ lookup แบบใหม่ก็จะพบว่าไม่ว่าอยู่คอลัมน์ฝั่งซ้ายหรือขวา ขอแค่เลือกให้ถูกต้องก็สามารถดึงค่าออกมาได้ตามใจชอบแล้วเอาไปคำนวณกับ Quantity ได้เลย เย่~

ความเจ๋งอีกอย่างคือ สามารถใส่ค่าในส่วน if_not_found ได้ด้วยกรณีที่หาค่าไม่เจอ เพราะปกติเราอาจจะต้องใช้ IFERROR ครอบถ้าเราใช้ lookup แบบเก่า

การประยุกต์ใช้ XLOOKUP ในหลากหลายแบบ

Multiple criteria: concatenation

จากการใช้งานอันแสนยืดหยุ่นนี้ทำให้ lookup แบบใหม่สามารถใช้แบบหลาย Criteria ได้สบาย ๆ เช่น การใช้ concatenation เพื่อเชื่อมหลายเงื่อนไขเข้าด้วยกัน ในตัวอย่างนี้เรามีผลไม้ที่ครอบคลุมในพื้นที่ต่าง ๆ โดยมี Supplier ที่รับผิดชอบและรูปแบบการส่งสินค้า จากนั้นเราจะเอา Criteria ต่าง ๆ มาค้นหาเพื่อดูว่า Supplier คนไหนรับผิดชอบอยู่ เป็นต้น

Multiple criteria: Logical Operators

Logical Operators สามารถทำได้เช่นเดียวกัน การตั้ง Criteria เช่น ต้องการกล้วย ที่ไม่ได้อยู่ในพื้นที่ West และมีส่วนลดมากกว่า 6% เราก็จะได้ผลลัพธ์ตามที่เรากำหนด Criteria ไว้

การใส่ lookup_value เป็น 1 จะดึงค่าที่ตรงกับเงื่อนไขใด ๆ ก็ตาม เป็นการค้นหาแบบครอบคลุมทุกเงื่อนไข โดยไม่ต้องระบุเงื่อนไขทีละข้อ

ตัวอย่างรูปด้านบนคือ การใช้เงื่อนไข AND เพราะใช้เครื่องหมาย (*) แต่หากต้องการใช้ OR สามารถใช้เครื่องหมาย (+) แทนได้ ข้อดีของการใช้งานในรูปแบบนี้เราสามารถใช้ผสมทั้ง AND และ OR ได้ด้วย แต่ยังมีข้อจำกัดอยู่คือถ้าได้ผลลัพธ์ที่มากกว่าหนึ่งคำตอบอาจจะทำให้ไม่แสดงผลได้หรือแสดงผลลัพธ์แค่ค่าแรกที่เจอเท่านั้น

สรุปตารางเปรียบเทียบ ใครดีกว่ากันแน่?

ถ้าให้สรุปคุณสมบัติทั้งหมดแบบหมัดชนหมัด จะได้ตารางสรุปดังต่อไปนี้ครับ

คุณสมบัติVLOOKUPXLOOKUP
ใช้ได้ในเวอร์ชันไหนทุกเวอร์ชันExcel 2021 หรือใหม่กว่า, Excel 365
ค้นหาข้อมูลแบบ Exact matchทำได้ทำได้
ค้นหาข้อมูลแบบ Approximate match1 ตัวเลือก (น้อยกว่า)2 ตัวเลือก (น้อยกว่า, มากกว่า)
ค่าเริ่มต้นในการค้นหาApproximate matchExact match
Wildcard searchทำได้ทำได้
Binary Searchทำไม่ได้ทำได้
ต้องเรียงลำดับคอลัมน์ค้นหาสำหรับค้นหาแบบ Approximateสำหรับการค้นหาแบบ Binary Search
จำกัดความยาวของค่าค้นหา255 ตัวอักษรไม่มีข้อจำกัด
ค้นหาจากด้านซ้ายทำไม่ได้ทำได้
ค้นหาข้อมูลแนวนอน (ตามแถว)ทำไม่ได้ทำได้
ค้นหาข้อมูล reverse orderทำไม่ได้ทำได้
รองรับการค้นหาหลายเงื่อนไขซับซ้อน ต้องใช้ helper columnง่าย ไม่ต้องใช้ helper column
ดึงข้อมูลจากหลายคอลัมน์ทำไม่ได้ทำได้
มีระบบแจ้งเตือนข้อผิดพลาดต้องใช้ IFERRORทำได้
การเพิ่ม/ลบคอลัมน์ส่งผลต่อสูตรส่งผลมีทั้งส่งผลและไม่ส่งผล

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

อีกคุณสมบัติของสูตร lookup ใหม่ที่น่าสนใจคือระบบ Binary Search ที่ถ้าใช้ร่วมกันแล้วจะทำให้ประสิทธิภาพของสูตรนี้สูงขึ้นไปอีกครับ เพราะจะเร็ว แรง ทะลุ Excel! แต่จะเร็วขนาดไหน จะแรงขนาดไหน โปรดติดตามตอนต่อไป สามารถกดรับสมัคร Newsletter ได้แล้วนะครับด้านล่างเพื่อเป็นกำลังใจให้คนเขียนด้วย ;-;

สำหรับบทความนี้เนื้อหาพอหอมปากหอมคอแล้ว เดี๋ยวจะแน่นจนเกินไป555 ขอขอบคุณทุกคนที่อ่านจนมาถึงตรงนี้นะครับ ขอให้ทุกคนมีความสุขและสนุกกับการทำงานครับ

fatty corgi
Photo by fatty corgi on Unsplash

แต่ถ้าใครติดลมอยากลุยต่อ พาร์ทสองมาแล้วสามารถลุยได้เลยนะครับ

บทความพาร์ทสองมาแล้ว Binary Search ลุย!
บทความพาร์ทสองมาแล้ว Binary Search ลุย!

Source:



แบ่งปันบทความนี้ให้คนที่คุณหวังดี :

Similar Posts