Font s :
Background :

กับดัก Error

ในทันทีที่เซลล์แสดง Error ซึ่งนำหน้าด้วยเครื่องหมาย # ขึ้นมา เช่น #DIV/0! #N/A #NAME? #NULL! #NUM! #REF! #VALUE! งานที่ใช้ Excel ตั้งใจสร้างขึ้นอย่างดี อาจกลายเป็นไร้ค่า เพราะดูแล้วเกิดสงสัยว่า สูตรคำนวณถูกต้องอย่างที่ต้องการหรือไม่ ทั้งๆที่ Error ที่แสดงขึ้นมาเหล่านี้ ไม่ได้หมายความว่า สูตรที่สร้างขึ้นนั้นผิดพลาดเสมอไปก็ตาม แต่ผู้ใช้ Excel ซึ่งไม่รู้จักคำเตือน Error ดี เมื่อเห็น Error แสดงขึ้น ก็มักตกอกตกใจ และพาลหาเรื่องกับผู้ที่สร้างงานขึ้นมาทีเดียวว่า งานที่คุณสร้างขึ้นมานั้นผิด

Error ไม่ได้แสดงขึ้นเพื่อเตือนว่ามีสิ่งผิดพลาดเสมอไป แต่อาจแสดงขึ้นตามปกติ เมื่อสูตรที่สร้างขึ้นไม่สามารถคำนวณ หรือคำนวณแล้วพบสิ่งที่น่าสังเกต ต้องเตือนให้ผู้ใช้ทราบไว้ก่อน

#DIV/0! เตือนเมื่อสูตรไม่สามารถใช้เลข 0 มาเป็นตัวหาร เช่น
=5/0

#N/A เตือนเมื่อสูตร Lookup ค้นหาค่าที่ต้องการไม่พบ เช่น
=Match(MyData, MyRange, 0) แต่ไม่มี MyData

#NAME? เตือนเมื่อสูตรใช้ชื่อเซลล์ แต่ไม่พบชื่อที่ตั้งไว้ เช่น
=One+Two แต่ไม่มี Range Name ชื่อ One กับ Two

#NULL! เตือนเมื่อสูตรอ้างถึงตำแหน่งเซลล์ที่ไม่มีตัวตน เช่น
=(F:F H:H) เมื่อ Column F ไม่ได้ตัดกับ Column H

#NUM! เมื่อสูตรใช้ตัวเลขที่เป็นไปไม่ได้ในการคำนวณ เช่น
=Sqrt(-1)

#REF! เมื่อตำแหน่งเซลล์ที่อ้างในสูตร เซลล์ที่อ้างถูกลบทิ้ง

#VALUE! เมื่อสูตรไม่สามารถคำนวณหาค่าเป็นตัวเลขได้ เช่น
=5/"Test"

หากเซลล์ใดเซลล์หนึ่งที่ใช้ในสูตร เกิด Error ขึ้นมา ถ้าผูกสูตรต่อกันไปทั้งตาราง จะส่งผลให้เซลล์อื่นซึ่งนำค่าจากเซลล์ Error ไปคำนวณต่อ เกิด Error ตามไปด้วย ดังนั้นแทนที่จะปล่อยให้การเกิด Error ของเซลล์ใดเซลล์หนึ่ง แล้วส่งผลเสียต่องานทั้งหมด จึงควรหาทางป้องกันไม่ให้เกิด Error ขึ้นตั้งแต่ต้น ซึ่งเรียกว่า วิธีสร้างกับดัก Error (Error Trapping) โดยใช้สูตร IF ตรวจสอบเงื่อนไขซึ่งอาจทำให้เกิด Error ก่อน เช่น

เดิมสร้างสูตรหาร =A1/A2 แต่ถ้า A2=0 จะทำให้สูตรหารนี้แสดง Error #DIV/0! ซึ่งต้องป้องกันไม่ให้แสดง Error โดยใช้สูตรต่อไปนี้แทนสูตร =A1/A2

=IF(A2=0, 0, A1/A2)

ถ้า A2=0 จะทำให้สูตร If คืนค่าเป็น 0 แทนไปก่อน แต่ถ้า A2 ไม่เท่ากับ 0 จะคำนวณได้ผลลัพธ์ตามสูตรเดิม =A1/A2

ไม่ควรใช้สูตร =IF(A2=0, "", A1/A2) เพราะเครื่องหมาย Null Text "" นั้นถือว่าเป็น Text ไม่สามารถนำค่าไปคำนวณต่อ

แต่เนื่องจากสูตรหารนั้น อาจเกิด Error ขึ้นอีกได้เสมอเมื่อตัวหารเป็นตัวอักษร จึงแนะนำให้แก้ไขสูตร If โดยใช้สูตร IsError ช่วย ดังนี้

=If( IsError(A1/A2), 0, A1/A2)

ถ้าสูตร A1/A2 ทำให้เกิด Error จะทำให้สูตร If คืนค่าเป็น 0 แทนไปก่อน แต่ถ้าสูตร A1/A2 ไม่เกิด Error จะคำนวณได้ผลลัพธ์ตามสูตรเดิม =A1/A2

(แต่ก่อนที่จะนำสูตร A1/A2 มาใช้ จะต้องตรวจสอบสูตรก่อนว่าคำนวณได้ถูกต้อง เพราะเมื่อนำสูตร IsError มาช่วยป้องกัน จะทำให้จากนี้ไปจะไม่มีทางพบ Error แสดงขึ้นมาให้เห็นอีกเลย)

สูตรกลุ่ม IS ซึ่งสามารถนำมาใช้กับสูตร If ในการป้องกัน Error เช่น

=IsError(สูตร) ตรวจสอบ Error ทุกประเภท

=IsErr(สูตร) ตรวจสอบ Error เกือบทุกประเภท เว้น #N/A

=IsNA(สูตร) ตรวจสอบ Error #N/A เท่านั้น

=IsText(เซลล์) ตรวจสอบว่าเซลล์มีค่าเป็น Text หรือไม่

=IsNumber(เซลล์) ตรวจสอบว่าเซลล์มีค่าเป็น Number

=IsBlank(เซลล์) ตรวจสอบว่าเซลล์เป็นเซลล์ว่างหรือไม่

ข้อผิดพลาดที่มักพบเห็นป็นประจำในการสร้างสูตรเพื่อดัก Error คือ มักสร้างสูตรซ้ำกันโดยไม่จำเป็น เช่น ถ้าเดิมสูตร VLookup คืนค่าเป็น #N/A แสดงว่าหาค่าที่ต้องการไม่พบ แทนที่จะใช้สูตรต่อไปนี้ซ้ำกันหลายเซลล์

=If( IsError(VLookup(xxxxx)), "No More", VLookup(xxxxx))

(หมายเหตุ : xxxxx ที่อยู่ในวงเล็บของ VLookup ใช้แทนโครงสร้างภายในของสูตร VLookup ซึ่งไม่ใช่สาระสำคัญในบทนี้ เพื่อทำให้เรียนรู้การใช้ If ได้เห็นชัดขึ้น)

เนื่องจากมีสูตร VLookup 2 สูตรในเซลล์เดียว ดังนั้นถ้ามีสูตรซ้ำกันหลายเซลล์ จะทำให้ใช้ VLookup คำนวณซ้ำหลายรอบ จึงขอให้ใช้สูตร VLookup เพียงเซลล์เดียว คำนวณหาค่ารายการนั้นก่อน จากนั้นจึงใช้ผลส่งต่อไปใช้ในเซลล์อื่นต่อ เช่น

A1 มีสูตร =VLookup(xxxxxx)

เซลล์สูตรอื่นๆ ให้อ้างถึง A1 แบบนี้ต่อไปแทนดีกว่า เพื่อทำให้ VLookup เสียเวลาคำนวณซ้ำน้อยที่สุด

=If(IsError(A1), "No More", VLookup(xxxxx))
=If(IsError(A1), "No More", VLookup(yyyyy))
=If(IsError(A1), "No More", VLookup(zzzzz))

 

Categories

About this Entry

This page contains a single entry by สมเกียรติ ฟุ้งเกียรติ published on May 16, 2005 12:20 PM.

If ที่ไม่ต้องใช้สูตร If was the previous entry in this blog.

เครื่องมือที่ใช้สร้างเว็บนี้ is the next entry in this blog.

Find recent content on the main index.

Font s :
Background :