#N/A ใน excel มักจะหมายถึงว่าสูตรไม่พบสิ่งที่ขอให้ค้นหา ซึ่งถ้าเป็น Match, VLookup, HLookup หรือ XLookup ในตอนนี้จะนำเสนอแนวทางการแก้ปัญหา และการใช้ SUBSTITUTE ช่วยจัดข้อมูลใน Excel
จากที่เคยเขียนเรื่องพื้นฐานของ vlookup ไปแล้วว่าหลักการทำงานของสูตรหรือ function นี้เป็นอย่างไร คราวนี้จะเขียนถึงเรื่องปัญหาที่พบเมื่อนำ vlookup ไปใช้แล้วเกิด #N/A ซึ่งเป็นการแสดงว่าไม่พบค่าที่ต้องการหา ทั้ง ๆ ที่เห็นอยู่ตำตาว่ามีข้อมูลนี้ ซึ่งถ้าไม่ตรวจสอบให้ดีจะนึกว่าข้อมูลนั้นไม่พบข้อมูลที่ต้องการค้นหา ทำให้ได้ผลลัพธ์ผิดพลาดขึ้น ท่านสามารถอ่านรายละเอียดได้จากเว็บของไมโครซอฟต์
ซึ่งที่พบเจอมักจะมีสาเหตุดังนี้
เนื้อหาโดยรวม :)
#N/A เพราะไม่ได้ตรึงค่า Table Array
ปัญหาที่มักจะพบทั้งที่ไม่คิดว่าจะพบก็คือ ลืมตรึงค่า Table Array!!! (ในตัวอย่างครั้งที่แล้วก็ไม่ได้ตรึง) เพราะเมื่อมีการลากสูตรเมื่อไหร่ ตำแหน่งอ้างอิงจะเปลี่ยนไปตามทิศทางที่ลาก แต่ Table Array จะต้องตรึง ด้วยการกด F4 หรือใส่ $ ลองดูเรื่อง การอ้างอิงตำแหน่ง ประกอบด้วย
#N/A ใน Excel เพราะ Data Type ไม่ตรงกัน
อันนี้ก็เป็นปัญหาอีกอย่างที่พบได้บ่อย เวลาไม่ตรวจสอบข้อมูลให้ดีก่อน หรือไม่ทันได้สังเกตว่า data type ไม่ตรงกัน อย่างที่เคยเขียนไปตอนก่อนหน้านี้แล้วว่า ค้นหาตัวเลข (Number) แต่ข้อมูลเก็บเป็นข้อความ (Text) แบบนี้ก็ขึ้นค่า error เพราะเป็น data คนละ type ดังนั้นการเตรียมข้อมูลจึงเป็นเรื่องสำคัญ
ลองดูตัวอย่าง เราอยากหาว่า Code 7 คืออะไร ซึ่งเราก็เห็นอยู่ว่ามี Code 7 อยู่ แต่ถ้าสังเกตให้ดี ตรง 7 ในตารางข้อมูล จะมีสามเหลี่ยมเล็ก ๆ ขึ้นอยู่ เตือนว่ามีอะไรสักอย่าง ซึ่งในที่นี้คือ เลข 7 โดนเก็บไว้ใน format text ไม่ใช่ number ดังนั้นถ้าเราหาค่าของ 7 ที่เป็น Number มันจึงแสดงผลว่าหาไม่เจอ ทั้งที่มี 7 อยู่ แต่ เป็น 7 ใน Format Text
สำหรับการแก้ไข ถ้าข้อมูลเกี่ยวกับตัวเลข แนะนำให้จัดการ Format ให้เรียบร้อยก่อนทำงาน โดยจะเลือกคอลัมน์นั้นแล้วคลิกขวา > Format Cell > Number
ค่าที่ใช้ค้นหามีวรรคหรือ Space
เรื่องนี้เป็นปัญหาที่พบบ่อยที่สุด และสร้างความปวดหัวให้มากที่สุดนั่นคือ ค่าที่ใช้ค้นหากับคำที่ใช้ค้นหาสะกดไม่เหมือนกัน และที่ผิดพลาดมากที่สุดคือการใส่ วรรค หรือ space ในคำที่ใช้ค้นหา
จากตัวอย่าง เราจะหาความกว้างถนนจากชื่อตำแหน่งที่ตั้ง ซอยพหลโยธิน32/1 ใส่ไปทั้งหมด 4 ตัว ดูผ่าน ๆ มันก็ ซอยพหลโยธิน32/1 เหมือนกัน ทำไมออกผลลัพธ์มาให้แค่ตัวเดียว คือที่ Cell A3 ที่เหลือได้ผลลัพธ์ว่าหาไม่เจอ หรือ #n/a
ลองมาดูที่ Cell A4 ซอยพหลโยธิน 32/1 มีวรรคก่อน 32/1
ลองมาดูที่ Cell A5 ซอยพหลโยธิน32/1 มีวรรคก่อน ซอย
ลองมาดูที่ Cell A6 ซอยพหลโยธิน32/1 มีวรรคหลัง 32/1 อันนี้ดูด้วยตาเปล่ายากหน่อย แต่ถ้าเอาเคอเซอร์ไปวางจะเห็นว่ามีวรรคอยู่ต่อด้านหลัง
ลองมาดูที่ Cell A4 ซอยพหลโยธิน ๓๒/๑ เป็นเลขไทย อันนี้หาไม่เจออยู่แล้ว แต่ใส่ไว้ให้เห็น เพราะนึกถึงที่เคยเจอคนใช้ฟอนต์ TH Sarabun IT๙ ปรากฏว่า ตัวเลขตามที่สายตาเห็นคือเลยไทยหมด แต่ปรากฎว่าตัวเลขฝั่งหนึ่งพิมพ์ด้วยเลขไทยจริง อีกฝั่งพิมพ์เป็นเลขอาราบิก แต่ TH Sarabun IT๙ แสดงให้เห็นเป็นเลขไทยหมดเลย มองผ่าน ๆ จะเหมือนกัน แต่โค้ดของแต่ละอักขระจะไม่เหมือนกัน มันเลยหาไม่เจอ ถึงได้บอกย้ำหลายครั้งว่าอย่าใช้ฟอนต์ TH Sarabun IT๙ เลย ลบออกจากเครื่องไปเถอะเพราะบางทีมันสร้างปัญหาง่าย ๆ แต่ไม่ทันคิดหลายครั้งแล้ว
วิธีแก้ไขปัญหาเรื่องวรรคใน Vlookup
ส่วนใหญ่จะใช้วิธีแก้ปัญหาโดยการใช้ฟังก์ชัน TRIM ซึ่ง ฟังก์ชันนี้จะช่วยตัดช่องว่างหน้าและหลังข้อความใส่ประกบเข้าไปด้วย เช่น อย่างคราวที่แล้วใช้สูตร
VLOOKUP(A3,E3:J9,3,FALSE)
ก็จะเป็น
=VLOOKUP(A3, TRIM (E3:J9), 3, FALSE)
แต่วิธีนี้ ไม่ได้ช่วยแก้ปัญหาเรื่องวรรคช่วงตรงกลาง อย่างในตัวอย่าง ซอยพหลโยธิน 32/1 มันจะมีเขียนทั้ง “ซอยพหลโยธิน 32/1” “ซอย พหลโยธิน 32/1” หรือแม้แต่ “ซอย พหลโยธิน32/1” ที่เราต้องการคือ เอาให้มันกลายเป็นไม่มีช่องว่างไปเลย เพื่อตัดปัญหา
วิธีแก้ปัญหาเรื่องคำที่ค้นหามีวรรค ก็ใช้ฟังก์ชัน SUBSTITUTE ง่าย ๆ เลย สูตรก็คือ SUBSTITUTE(ข้อความหรือ Cell ที่ต้องการ,ค่าที่หา,ค่าที่แทน) ซึ่งถ้าเราต้องการเอาช่องว่างออก สูตรก็จะเป็น =SUBSTITUTE(A1,” “,””) คือแทนที่วรรคด้วยการไม่วรรค
เราต้องเอา SUBSTITUTE ไปครอบทั้ง lookup Value และ Table Array เพื่อจัดการข้อมูลให้เป็นแบบเดียวกันคือไม่มีช่องวรรคทั้งหน้าข้อความ หลังข้อความ หรือ ระหว่างข้อความ
Tip: ถ้าเราเตรียมข้อมูลให้ดีพอ เราก็ไม่ต้องใส่ SUBSTITUTE แต่อย่างใด แต่บางทีก็เห็นใจเพราะคนทำข้อมูลก็ร้อยพ่อพันแม่ ไม่รู้ว่าใครจะพิมพ์อะไร ใส่สไตล์ไหน วรรคไม่วรรค หรือเคาะวรรคเพลินด้วยความเคยชิน และข้อมูลที่รวบรวมมาก็เป็นจำนวนมากกว่าแสน Row แบบนี้ก็ต้องหาวิธีช่วย
ขั้นแรก ใส่ SUBSTITUTE ใน Lookup Value
จากสูตร Vlookup เดิม คือ
=VLOOKUP(A3,$D$3:$E$9,2,FALSE)
จะใส่ SUBSTITUTE เข้าไปใน Lookup Value (ในที่นี้คือ A3) จะได้สูตรเป็น
=VLOOKUP(SUBSTITUTE(A3," ",""),$D$3:$E$9,2,FALSE)
ขั้นสอง ใส่ SUBSTITUTE ใน Table Array
VLOOKUP(SUBSTITUTE(A3," ",""),SUBSTITUTE($D$3:$E$9," ",""),2,FALSE)
อ้าวไม่ได้ ผลลัพธ์ที่ได้เป็น #Value ทำไมเป็นแบบนั้น?
เหตุผลง่าย ๆ ก็คือ Table Array เป็นช่วง ไม่ใช่แค่ Cell 1 Cell อย่าง Lookup Value การใส่สูตรครอบช่วงแบบนี้ค่าอาร์กิวเมนต์จะเกินขีดรับรู้ของ Excel เราถึงต้องใช้ Array เป็นตัวกำกับอีกที
การเรียกใช้ Array ในสูตรนี้ง่ายมาก เมื่อเขียนสูตรเสร็จ เพียงแค่กด Ctrl + Sheft + Enter เท่านี้ก็เรียบร้อย สูตรจะขึ้นว่ามีปีกกา { และ } ประกบหน้าและหลังสูตร
เท่านี้ก็เรียบร้อย
สำหรับ error อื่น ๆ ที่พบได้ใน Excel โปรดอ่านที่บล็อกนี้