วิธีป้องกันไม่ให้เกิด error จากสูตรคำนวณ

ตามปกติเมื่อสูตรหาคำตอบไม่ได้หรือคำนวณไม่ได้แล้วเกิด error ที่มีเครื่องหมายกากบาท # ก็ต้องแก้ไขให้เปลี่ยน error ไปเป็นค่าอื่น เช่น พอสูตร VLookup หาค่าที่ต้องการไม่พบจะแสดง #N/A ซึ่งย่อมาจากคำว่า Not Available ก็จะแก้สูตรใหม่เป็น No More

=IFError( VLookup(xxxxxx), "No More" )
หรือ
=IF( ISNA( VLookup(xxxxxx), "No More", VLookup(xxxxxx) )

xxxxxx คือส่วนที่อยู่ในวงเล็บ ขอแสดงย่อเพื่อจะได้เข้าใจได้ง่ายขึ้น

กรณีที่สร้างสูตรจับตัวเลขมาหารกัน เช่น =B5/B10 พอเซลล์ B10 กลายเป็นเลข 0 ก็จะแสดง #DIV/0! ซึ่งย่อมาจาก Divided by Zero ก็ต้องแก้สูตรใหม่ให้แสดงเป็นเลข 0 แทน

=IFError( B5/B10, 0 )
หรือ
=IF(ISError( B5/B10 ), 0, B5/B10 )

☝️ ทำไมต้องแก้ error ด้วย

1. เพื่อทำให้ผู้ใช้ตารางที่ไม่เก่ง Excel ไม่เห็น error โดยแสดงเป็นค่าอื่นที่เห็นแล้วไม่สงสัย
2. เพื่อนำผลที่คำนวณได้ไปใช้คำนวณต่อ เพราะถ้าติด error ไปก็จะ error ตาม

✋ ถ้าไม่จำเป็น ไม่ได้เข้าข่าย 2 สาเหตุนี้ อย่าไปแก้ error เลยครับ เพราะเท่ากับปิดสัญญาณเตือนว่ามีสิ่งผิดพลาดเกิดขึ้นในการคำนวณแล้วจะได้ระมัดระวังกัน

⏰ เมื่อไหร่ที่ควรแก้ error เหล่านี้

จำไว้ว่า error นี้มีประโยชน์ในการใช้เตือน ดังนั้นอย่ารีบไปแก้ error แต่ต้องสร้างสูตรให้ดีที่สุดก่อน จากนั้นจึงทดสอบค่าที่กรอกลงไปว่าสามารถทำให้เกิด error ที่เซลล์ไหน แล้วจึงค่อยไปปรับสูตรให้แก้ error

ถ้ารีบไปแก้ error ตั้งแต่ตอนสร้างสูตรนั้นเลย ก็จะไม่มีทางทราบจุดอ่อนในงานนั้นๆ

☝️ การป้องกันไว้ตั้งแต่ต้น คือทางที่ดีกว่าการแก้ error

จะเห็นได้ว่าวิธีแก้ error ต้องอาศัยการสร้างสูตร IF ซ้อนเข้าไปช่วย ทำให้แฟ้มคำนวณช้าลงและมีขนาดแฟ้มใหญ่ขึ้น ซึ่งมักพบว่าสาเหตุของ error เหล่านี้มาจากค่าที่กรอกลงไปนั่นเอง ซึ่ง Excel ได้สร้างคำสั่ง Data Validation ไว้ช่วยในการตรวจสอบค่าที่กรอกลงไปว่าตรงตามเงื่อนไขที่ต้องการตั้งแต่แรกหรือไม่ โดยกำหนดเงื่อนไขแบบ Custom ตามภาพ

+++++++++++++++++++++++++++++++++

ปล

หลายคนมักชอบแก้ error ให้แสดงเป็นช่องว่างแทน โดยใช้สูตรแบบนี้

=IFError( VLookup(xxxxxx), "" )
=IF( ISNA( VLookup(xxxxxx), "", VLookup(xxxxxx) )
=IFError( B5/B10, "" )
=IF(ISError( B5/B10 ), "", B5/B10 )

เครื่องหมาย "" นี้เรียกว่า Null Text หมายถึง Text ที่ไม่มีตัวตน แม้จะช่วยให้มองไม่เห็นค่าในเซลล์ แต่ในเซลล์นั้นยังคงถือว่ามีตัวอักษรตัวหนึ่งอยู่ จะส่งผลทำให้เมื่อนำเซลล์ที่เห็นว่าว่างนั้นไปบวกลบต่อก็จะเกิด error ตามไปอีก แล้วก็ต้องอาศัยสูตรแก้ error ต่อไปเรื่อยๆแบบนี้เต็มไปหมดแทบทุกเซลล์ที่นำค่าไปใช้คำนวณต่อ ทำให้แฟ้มคำนวณช้าลงและมีขนาดใหญ่โดยไมจำเป็น

=IF( Cell="", "", Cell )

👉 หลักการแก้ไขที่ถูกต้อง ขอให้ดูว่าสูตรเดิมนั้นให้คำตอบเป็นตัวเลขหรือตัวอักษร เมื่อแก้ error ด้วยสูตรแล้วผลยังต้องมีสถานะเช่นเดิม

ถ้าผลที่ต้องการเดิมนั้นเป็นตัวอักษร จะใช้ "" ก็ได้เพราะปกติไม่มีใครนำตัวอักษรไปบวกลบต่อ

ถ้าผลที่ต้องการเดิมนั้นเป็นตัวเลข ให้ใช้เลข 0 แทน "" เพราะเลข 0 ยังคงเป็นตัวเลขที่นำไปบวกลบต่อได้

เลข 0 เป็นทางเลือกที่ปลอดภัยกว่า "" แน่นอน จากนั้นหากต้องการทำให้มองเห็นเป็นเซลล์ว่างก็ให้ใช้ Custom Format 0;-0; หรือถ้าอยากจะเปลี่ยนเลข 0 ให้เห็นเป็นข้อความ No More ก็ใช้ Format 0;-0;"No More"