วันพุธที่ 7 ตุลาคม พ.ศ. 2558

การแก้ปัญหา #N/A ของสูตร vlookup

สูตร vlookup() มีรูปแบบการใช้ ดังนี้

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_valueคือ ข้อมูลที่ต้องการให้ค้นหา
table_array คือ ตารางข้อมูลที่ไปทำการค้นหา
col_index_num คือ ตำแหน่งคอลัมน์ในตารางข้อมูลที่ต้องการให้นำมาแสดงเมื่อพบข้อมูลที่ค้นหาแล้ว ตำแหน่งคอลัมน์แรก คือ 1
range_lookup คือ ลักษณะการค้น
     ถ้าต้องการให้ค้นคำที่เหมือนกับคำค้นทุกประการ ให้กำหนดเป็น False หรือ 0
     ถ้าให้ค้นแบบใกล้เคียง ให้กำหนดเป็น True หรือ 1 และต้องเรียงข้อมูลในตารางจากน้อยไปหามาก
     ถ้าไม่ระบุ Excel จะใช้ถือว่าเป็น true


สูตร vlookup() ของ Excel เป็นสูตรที่มีประโยชน์ แต่ก็มีข้อที่ต้องระวังหลายอย่างเหมือนกัน มิฉะนั้น จะไม่แสดงค่าตามที่ต้องการ เช่น อาจจะแสดงค่า  #N/A เป็นต้น

ดูตัวอย่างข้างล่างนี้ ท่านทราบหรือไม่ว่า ทำไมสูตร vlookup() จึงไม่แสดงชื่อที่ต้องการ



ถ้าพิจารณาจากสูตรที่ใช้  จะเห็นว่า เป็นการค้นหาแบบ exact match คือ เหมือนคำค้นที่ต้องการ คือ ให้แสดงชื่อคนในตาราง D26:J36 ที่ได้คะแนน 73.00 แต่ผลปรากฎว่า ได้ #N/A แทน

นี่เป็นข้อจำกัดหนึ่งของสูตร vlookup() นั่นคือ การค้นหาข้อมูล จะค้นหาจากทางด้านซ้ายของคำค้นหาเป็นต้นไป ในตัวอย่าง คำค้นหา คือ 73.00 จะเป็นว่า ในตาราง  D26:J36 คำค้นหานี้ อยู่ในคอลัมน์ที่ 6 แต่ให้นำข้อมูลในคอลัมน์ที่ 2 ซึ่งอยู่ด้านขวา ดังนั้น Excel จึงแสดงข้อความ #N/A เพื่อบอกว่า ไม่พบข้อมูล เพราะหาไม่เจอ ไม่เป็นไปตามเงื่อนไขซึ่งจะหาข้อมูลที่อยู่ทางด้านซ้าย

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

วิธีที่ดีกว่า ก็คือ ไม่ใช้สูตร vlookup() เพราะข้อมูลไม่เป็นไปตามกติกาการใช้งานสูตรนี้ สูตรใหม่ที่ใช้ได้ดี คือ ใช้ index() คู่กับ match()  รายละเอียดการใช้สูตรทั้งสองผมเขียนไว้แล้ว คลิกที่นี่
ในกรณีนี้ สูตรที่ใช้คือ
=INDEX(D27:I35,MATCH(D38,I27:I35,0),2)



ยังมีกรณีอื่น ๆ ที่ vlookup() อาจจะไม่แสดงผลตามที่ต้องการ สิ่งที่ต้องตรวจสอบ มีดังนี้
  1. เขียนสูตรถูกต้องหรือไม่ เช่น อาจจะระบุตำแหน่งผิด หรือ พิมพ์ข้อความที่ให้ค้นหาผิด เป็นต้น
  2. ถ้าเป็นการค้นหาแบบ Approximate เช่น การตัดเกรด เป็นต้น ให้ตรวจดูว่า ตารางข้อมูล(หรือเกณฑ์) มีการเรียงจากน้อยไปหามาก และ ผลที่จะให้แสดงอยู่ทางคอลัมน์ด้านขวาของคอลัมน์ที่ใช้ค้นหา หรือไม่ (คอลัมน์ที่ใช้ค้นหา ต้องอยู่ทางด้านซ้าย ส่วนผลที่จะให้แสดงต้องอยู่ทางด้านขวา)
  3. ตัวเลขจัดรูปแบบเป็นตัวอักษร หรือไม่ ถ้ามีการจัดแบบตัวอักษร Excel จะหาไม่พบ เช่น มีการใช้เครื่องหมาย ' เพื่อบังคับว่า ข้อมูลในเซลล์เป็นตัวอักษร เป็นต้น

ที่มา:
http://www.extendoffice.com/documents/excel/2453-excel-vlookup-right-to-left.html
https://www.ablebits.com/office-addins-blog/2014/08/27/excel-vlookup-not-working/