#N/A ใน Excel

#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 ดังนั้นการเตรียมข้อมูลจึงเป็นเรื่องสำคัญ

#N/A

ลองดูตัวอย่าง เราอยากหาว่า 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

#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 เท่านี้ก็เรียบร้อย สูตรจะขึ้นว่ามีปีกกา { และ } ประกบหน้าและหลังสูตร

เท่านี้ก็เรียบร้อย

ความคิดเห็นของคุณ :)

%d bloggers like this: