Thep Excel
ISNAตรวจสอบว่าค่าเป็น Error #N/A หรือไม่
ISNA ตรวจสอบเฉพาะ Error #N/A ที่เกิดจากการค้นหาไม่เจอในฟังก์ชันต่างๆ เช่น VLOOKUP หรือ MATCH มีประโยชน์มากเมื่อต้องแยกแยะระหว่าง “หาไม่เจอ” กับ “สูตรคิดผิด”
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | คำอธิบาย |
|---|---|---|
| value | Any | ค่า เซลล์ หรือผลลัพธ์จากสูตรที่ต้องการตรวจสอบว่าเป็น #N/A หรือไม่ สามารถใส่ได้ทุกชนิด (ตัวเลข ข้อความ ผลลัพธ์สูตร ฯลฯ) |
Additional Notes
ISNA คือฟังก์ชันตรวจจับข้อผิดพลาด (Error Detection Function) ที่ตรวจสอบว่าค่าหรือผลลัพธ์จากสูตรเป็น Error แบบ #N/A (Not Available) หรือไม่ ถ้าเป็น #N/A จะคืนค่า TRUE ถ้าไม่ใช่ (รวมถึง Error ชนิดอื่น) จะคืนค่า FALSE
ที่เจ๋งคือ ISNA จะตรวจจับ #N/A อย่างเฉพาะเจาะจงเท่านั้น ไม่เหมือน ISERROR ที่จะจับ Error ทั้งหมด (#VALUE!, #DIV/0!, #REF! ฯลฯ) ดังนั้นถ้าต้องการรู้ว่า “VLOOKUP หาไม่เจอจริงๆ” หรือ “มีข้อมูลแต่สูตรเขียนผิด” ให้ใช้ ISNA ดีกว่า
ส่วนตัวผมใช้ ISNA เป็นจำนวนมากในงาน Data Cleaning เพราะมันช่วยระบุได้ว่าข้อมูลหรือ Lookup Rules ไหนที่มีปัญหา ผมชอบใช้มันควบคู่กับ COUNTIF เพื่อหานับว่ามี #N/A กี่อันในแต่ละ Column เลยครับ
How it works
จัดการ Error จากการค้นหาข้อมูล
ใช้ตรวจสอบว่าข้อมูลที่ User กรอกมีอยู่ในฐานข้อมูลหรือไม่ โดยไม่สนใจ Error ประเภทอื่นที่เกิดจากสูตรผิดพลาด
การตรวจสอบความสมบูรณ์ของข้อมูล
เช็คว่ามีรายการใดในตารางที่ Lookup แล้วไม่พบข้อมูล เพื่อทำรายงานสรุปรายการที่ตกหล่น
Examples
ตรวจสอบผลลัพธ์ VLOOKUP หาค่าไม่เจอISNA(VLOOKUP("Apple", A1:B10, 2, 0))
ถ้า VLOOKUP หาคำว่า "Apple" ไม่เจอในคอลัมน์แรก จะคืนค่า #N/A ทำให้ ISNA คืนค่า TRUE แต่ถ้าเจอหรือเกิด Error อื่น (เช่น #REF! จากช่วงไม่ถูกต้อง) จะได้ FALSE
ใช้กับ IF เพื่อแสดงข้อความแทนค่า #N/AIF(ISNA(VLOOKUP(A1, Database, 2, 0)), "ไม่พบข้อมูล", VLOOKUP(A1, Database, 2, 0))
แถบเซลล์ A1 ไม่อยู่ในฐานข้อมูล VLOOKUP จะคืน #N/A เมื่อ ISNA ตรวจจับจะให้แสดงข้อความ "ไม่พบข้อมูล" แทน นี่คือวิธีการสร้าง Error Handler โดยไม่ใช้ IFERROR
แยกแยะประเภท Error ต่างๆIF(ISNA(MATCH(E2, List, 0)), "หาไม่เจอ", IF(ISERROR(MATCH(E2, List, 0)), "Error อื่นๆ", "ค้นหาเจอ"))
ใช้ ISNA ตรวจสอบ #N/A ก่อน หลังจากนั้นใช้ ISERROR ตรวจสอบ Error ชนิดอื่น เพื่อแยกแยะให้ชัดเจนว่าปัญหาคืออะไร
นับจำนวน #N/A ในช่วงข้อมูลSUMPRODUCT(–ISNA(A1:A100))
ใช้ SUMPRODUCT กับ ISNA เพื่อนับว่า Cell A1:A100 มี #N/A กี่อัน วิธีนี้สะดวกมากในการตรวจสอบ Data Quality เพื่อดูว่า Lookup ไหนล้มเหลวจำนวนมาก
FAQs
ผม Error #N/A แล้ว ต้องใช้ ISNA ยังไงถึงแก้ได้?+
ISNA ไม่ได้แก้ Error หรือทำให้ #N/A หายไป มันแค่ตรวจจับเท่านั้น ถ้าต้องการแก้ให้ใช้ IFNA หรือ IFERROR แทน เช่น =IFNA(VLOOKUP(…), 0) นี่คือวิธีที่จะเปลี่ยน #N/A เป็นค่าอื่น
ISNA ต่างจาก ISERROR อย่างไร?+
ISNA ตรวจจับเฉพาะ #N/A เท่านั้น ส่วน ISERROR จะตรวจจับ Error ทุกชนิด (#N/A, #VALUE!, #REF!, #DIV/0!, #NAME?, #NUM!, #NULL! ฯลฯ) ถ้าต้องการรู้เฉพาะว่า "ค้นหาไม่เจอ" ใช้ ISNA แต่ถ้าต้องการจับ Error ใดๆ ใช้ ISERROR
ควรใช้ IFNA หรือ ISNA?+
ถ้าต้องการแทนที่ #N/A ด้วยค่าอื่นทันที (เช่น 0 หรือข้อความ) ให้ใช้ IFNA เพราะเขียนสั้นกว่า เช่น =IFNA(VLOOKUP(…), 0) แต่ถ้าต้องการแค่ตรวจสอบเป็น TRUE/FALSE เพื่อนำไปใช้ใน Logic อื่น หรือต้องการนับจำนวน #N/A ให้ใช้ ISNA
#N/A เกิดจากอะไรบ้าง?+
เกิดจากการค้นหาไม่เจอในฟังก์ชัน VLOOKUP, HLOOKUP, MATCH, INDEX, LOOKUP, XLOOKUP เป็นต้น บ่อยๆ คือ typo, space ซ่อนอยู่, หรือข้อมูลค้นหาจริงๆ ไม่อยู่ในตาราง หรือ Reference ไป Sheet อื่นซึ่งปิด workbook ไว้
สามารถใช้ ISNA กับ Array Formula ได้ไหม?+
ได้ครับ ISNA ที่นำไปใช้กับ Range จะคืนค่า Array ของ TRUE/FALSE ตามจำนวนเซลล์ เช่น =ISNA(VLOOKUP(A1:A10, Data, 2, 0)) จะได้ผลลัพธ์ 10 ค่า (ใน Excel 365 จะ Spill ลงมาเอง)
ทำไมผลลัพธ์เป็น #N/A แต่ ISNA(range) ถึง Error?+
เพราะฟังก์ชัน VLOOKUP หรือ MATCH ใช้ได้กับ Single Value ไม่ได้ใช้กับ Array ถ้าต้องการตรวจจับ Error หลายๆ เซลล์พร้อมกัน ลองใช้ IFERROR แทน =IFERROR(VLOOKUP(A:A, Data, 2, 0), "") แบบนี้ดีกว่า
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ ISNA จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
แปลง Error ที่เกิดในเซลล์ (เช่น #DIV/0!, #N/A, #VALUE!) ให้เป็นรหัสตัวเลข 1-14 เพื่อนำไปเช็คเงื่อนไขและจัดการ Error แต่ละชนิดแยกกันได้
ตรวจสอบว่าค่าที่ระบุเป็นค่า Error หรือไม่ ยกเว้น #N/A ใช้คู่กับ IF เพื่อดักจับ Error อย่าง #DIV/0! หรือ #VALUE! ก่อนแสดงผลลัพธ์ให้ผู้ใช้เห็น
ISERROR ตรวจสอบค่าว่าเป็น Error หรือไม่ โดยครอบคลุม Error ทุกประเภทใน Excel ได้แก่ #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, และ #NULL! มักใช้คู่กับ IF เพื่อแสดงข้อความเตือนหรือจัดการกับ Error ก่อนที่จะแสดงผล
ISLOGICAL เช็คว่าเซลล์เป็นค่าตรรกะ (TRUE หรือ FALSE) จริง ๆ หรือเพียงแค่ข้อความที่อ่านดูเหมือน
ตรวจสอบว่า Argument (พารามิเตอร์) ที่กำหนดใน LAMBDA แบบ Optional ถูกละเว้นไม่ได้ใส่ค่ามาหรือไม่ ใช้ร่วมกับ IF เพื่อตั้งค่า Default ให้ Custom Function
ฟังก์ชัน NA() ใช้สำหรับส่งคืนค่าความผิดพลาด #N/A ซึ่งหมายถึง 'ค่าไม่พร้อมใช้งาน' (No Value Available) โดยไม่มีอาร์กิวเมนต์ใดๆ ใช้เพื่อทำเครื่องหมายเซลล์ที่ข้อมูลขาดหายไปหรือเพื่อหลีกเลี่ยงการรวมเซลล์ว่างในการคำนวณ
IFERROR ช่วยดักจับ Error ทุกประเภทที่เกิดขึ้นในสูตร (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!) แล้วเปลี่ยนเป็นค่าที่เราต้องการแทน
.
ที่เจ๋งคือมันช่วยให้รายงานและ Dashboard ดูสะอาด ไม่มี Error แสดงให้ผู้ใช้งานเห็น โดยถ้าสูตรไม่มี Error ก็จะ return ผลลัพธ์ปกติ
.
ส่วนตัวผมคิดว่าฟังก์ชันนี้เป็น "ตัวช่วยมหาเทพ" สำหรับคนทำรายงานเลยครับ 😎
IFNA เป็นฟังก์ชันที่ช่วยจัดการ #N/A errors โดยแทนค่าเป็นข้อความหรือค่าอื่นที่คุณกำหนด เหมาะสำหรับการค้นหาข้อมูลที่อาจไม่พบผลลัพธ์
MATCH คืนเลขลำดับตำแหน่งของค่าที่ค้นหาในช่วงข้อมูลแถวเดียวหรือคอลัมน์เดียว รองรับการค้นหา 3 โหมด คือ Exact Match (0) ที่ไม่ต้องเรียงข้อมูล, Approximate Match แบบ Less Than or Equal (1) ที่ต้องเรียงจากน้อยไปมาก, และ Greater Than or Equal (-1) ที่ต้องเรียงจากมากไปน้อย รองรับ Wildcard (* และ ?) ในโหมด Exact Match และมักใช้คู่กับ INDEX เป็นรูปแบบ INDEX-MATCH ที่ยืดหยุ่นกว่า VLOOKUP
VLOOKUP เป็นฟังก์ชันค้นหาแนวตั้งที่ได้รับความนิยมสูงสุดใน Excel โดยค้นหาค่าที่ต้องการในคอลัมน์ซ้ายสุดของตาราง จากนั้นดึงข้อมูลจากคอลัมน์ที่ระบุในแถวเดียวกัน รองรับทั้งการค้นหาแบบตรงทุกตัวอักษร (Exact Match) และแบบประมาณค่า (Approximate Match) สำหรับข้อมูลที่เรียงลำดับ ข้อจำกัดหลักคือต้องค้นหาจากคอลัมน์ซ้ายสุดเท่านั้น ไม่สามารถดึงข้อมูลจากด้านซ้ายของคอลัมน์ค้นหาได้
ยังไม่มีบทความที่เกี่ยวข้องกับฟังก์ชันนี้
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่