การแก้ปัญหา #N/A ของสูตร vlookup
สูตร vlookup() มีรูปแบบการใช้ ดังนี้
สูตร 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() รายละเอียดการใช้สูตรทั้งสองผมเขียนไว้แล้ว คลิกที่นี่
ในกรณีนี้ สูตรที่ใช้คือ
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() อาจจะไม่แสดงผลตามที่ต้องการ สิ่งที่ต้องตรวจสอบ มีดังนี้
- เขียนสูตรถูกต้องหรือไม่ เช่น อาจจะระบุตำแหน่งผิด หรือ พิมพ์ข้อความที่ให้ค้นหาผิด เป็นต้น
- ถ้าเป็นการค้นหาแบบ Approximate เช่น การตัดเกรด เป็นต้น ให้ตรวจดูว่า ตารางข้อมูล(หรือเกณฑ์) มีการเรียงจากน้อยไปหามาก และ ผลที่จะให้แสดงอยู่ทางคอลัมน์ด้านขวาของคอลัมน์ที่ใช้ค้นหา หรือไม่ (คอลัมน์ที่ใช้ค้นหา ต้องอยู่ทางด้านซ้าย ส่วนผลที่จะให้แสดงต้องอยู่ทางด้านขวา)
- ตัวเลขจัดรูปแบบเป็นตัวอักษร หรือไม่ ถ้ามีการจัดแบบตัวอักษร 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/
ขอบคุณครับ มีประโยขน์ครับ
ตอบลบ