สูตรแบบสั้นของ XLookup ปลอดภัยกว่า VLookup แต่ ...

VLookup vs XLookup 01 04

"หาค่าเจอเสมอ" เป็นข้อผิดพลาดที่ร้ายที่สุดของการใช้สูตร VLookup แบบสั้นๆ

ไม่ว่าตารางข้อมูลจะเรียงหรือไม่เรียง ไม่ว่าจะใช้รหัส a003 หรือ a003x ซึ่งไม่มีรหัสนี้บันทึกไว้ สูตร =VLOOKUP(F3,MyData,2) จะหาเจอว่า ID ที่กรอกลงไปนั้นมีชื่อ Name กับ Amount เป็นเท่าใดอยู่เสมอ หากไม่ได้ตรวจสอบให้ดีแล้วเอาคำตอบไปใช้เลยก็อาจสอบตกโดยไม่รู้ตัว

สูตร =VLOOKUP(F3,MyData,2) ที่สร้างขึ้นนี้เป็นการสร้างแบบสั้นๆ ซึ่ง Excel กำหนดเงื่อนไขไว้ว่า ข้อมูลที่ใช้ค้นหาซึ่งอยู่ใน column ซ้ายสุดนั้น "ต้องเรียงค่าจากน้อยไปมาก" โดยจะค้นหาค่าที่มากที่สุดที่ยังน้อยกว่าหรือเท่ากับค่าที่ใช้หามาให้

ถ้าเรียงจากน้อยไปมากเอาไว้ เช่นภาพซ้ายบน และใช้ a003 ซึ่งมีบันทึกไว้ด้วย นำไปใช้ค้นหาก็จะได้ผลลัพธ์ถูกต้อง

VLookup vs XLookup 01

ภาพขวาบน ถ้าใช้ a003x แม้ไม่มีค่านี้บันทึกไว้ก็ยังหาค่ามาให้ ซึ่งผิด ที่ถูกต้องก็คือต้องตอบว่า #N/A Not Available

VLookup vs XLookup 03

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

VLookup vs XLookup 02

ภาพขวาล่าง วิธีการที่ถูกต้องของการใช้ VLookup ต้องเปลี่ยนจากสูตรแบบสั้นไปใช้สูตรแบบเต็ม =VLOOKUP(F3,MyData,2,0) หรือ =VLOOKUP(F3,MyData,2,FALSE) โดยเลข 0 หรือ False นี้ เป็นการกำหนดให้สูตรทำหน้าที่ค้นหาค่าแบบ Exact Match

VLookup vs XLookup 04

แบบ Exact Match หมายความว่า ถ้าค้นหาค่าพบก็ให้ใช้รายการที่ค้นพบนั้น แต่ถ้าหาไม่พบก็ให้คืนค่าออกมาว่า Not Available หรือ #N/A ซึ่งเป็นวิธีการที่ปลอดภัยกว่า เพราะเราไม่มีโอกาสที่จะตามตรวจสอบได้เสมอไปหรอกว่ารายการยังเรียงค่าจากน้อยไปมากอยู่หรือไม่

การสร้างสูตรแบบสั้น =VLOOKUP(F3,MyData,2) นั้น Excel จะถือว่าเป็นการละส่วนขวาสุดของสูตรแบบเต็ม =VLOOKUP(F3,MyData,2,1) หรือ =VLOOKUP(F3,MyData,2,TRUE) โดยไม่ต้องใส่เลข 1 หรือ TRUE ก็ได้

ผลจากการออกแบบสูตรเพื่อช่วยให้ผู้ใช้ Excel สร้างสูตร VLookup สั้นๆได้ง่ายๆเช่นนี้ กลับกลายเป็นปัญหาใหญ่ที่ทำให้สูตรไม่เตือนเมื่อควรจะถูกเตือน (Match แบบสั้น ก็ใช้หลักเดียวกับ VLookup แบบสั้น ซึ่งเหมาะกับตารางที่เรียงค่าจากน้อยไปมากเท่านั้น)

พอถึงยุด Excel 365 ได้ปรับเปลี่ยนหลักการใช้สูตรแบบสั้นให้รัดกุมขึ้นว่า ถ้าไม่ได้กำหนดเงื่อนไขให้ครบ สูตร XLookup แบบสั้น จะค้นหาค่าแบบ exact match เสมอ

ด้วยหลักการใหม่นี้ สูตร =XLOOKUP(F3,Id,Name) แบบสั้น จึงสามารถใช้ค้นหาค่าได้ถูกต้องเสมอ ไม่ว่าตารางจะเรียงหรือไม่ และไม่ว่าจะใช้ค่าที่ไม่มีในการค้นหาค่าก็ตามโดยจะคืนค่า Not Available ออกมาให้แทน

VLookup vs XLookup 01 04

ปล

อย่าเพิ่งรีบไปใช้ XLookup แทน VLookup นะครับ เพราะเมื่อใช้ VLookup แบบ exact match เป็นแล้วก็หมดปัญหา

สาเหตุหลักที่จะตัดสินใจเปลี่ยนไปใช้ XLookup แทนนั้น ให้ดูโครงสร้างของตารางว่าในอนาคตจะมีการแทรก column เพิ่มหรือไม่ กับต้องมั่นใจว่าเพื่อนๆทุกคนที่จะใช้แฟ้มที่มี XLookup นั้นใช้กับ Excel 365 หรือรุ่นล่าสุดกว่าเท่านั้น ส่วน VLookup ยังไงก็ตามยังยืดหยุ่นกว่าเพราะสามารถนำไปใช้กับ Excel ได้ทุก version

คลิกที่นี่เพื่ออ่านวิธีการใช้สูตร XLookup จากไมโครซอฟท์

 

 

Related Articles

© Copyright 1999

สงวนลิขสิทธิ์ตามกฎหมาย

ห้ามนำข้อความหรือส่วนหนึ่งส่วนใดของบทความหรือวิดีโอหรือรูปภาพไปใช้เพื่อการค้าขาย หรือเพื่อประโยชน์ส่วนตัว

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

ลิงก์เว็บ Excel Expert Training

เว็บสำหรับ เรียนออนไลน์

เว็บสำหรับ เรียนแบบกลุ่ม-ส่วนตัว

ติดตามข่าวสารได้จาก facebook

ถามปัญหาได้ที่ กลุ่มคนรัก Excel

และไลน์กลุ่ม Excel Expert Group

หรือ Excel Expert Forum

ที่อยู่และการติดต่อ

สมเกียรติ ฟุ้งเกียรติ 7/1 รามคำแหง ซอย 35 หัวหมาก บางกะปิ กทม 10240 โทร 097-140-5555, 02-718-9331

Excel@ExcelExpertTraining.com

SFK MVPLogoH60