Blog

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

ข้อผิดพลาดใน Excel ที่เจอบ่อยมีอะไรบ้าง?

#####

เชื่อว่าหลายท่านเคยเจอ ##### กันมาบ้าง

ตรงนี้อาจจะไม่ใช่ความผิดพลาด เราแค่ใส่ค่าอะไรสักอย่างลงไปยาวเกินกว่าความกว้างเซลล์จะรองรับมันก็เลยขึ้น ##### ให้ดู

ทางแก้ง่าย ๆ ก็คือ ขยายความกว้างคอลัมน์ให้กว้างขึ้น

แต่ในอีกกรณีหนึ่ง อาจจะเป็นการใส่ค่าวันที่ผิดพลาด โปรดอ่านที่บล็อกนี้ การใส่ข้อมูลวันที่ผิดพลาดอันนำมาสู่ #####

#DIV/0!

คำนี้ เป็นคำเตือนที่บอกชัดเจนในตัวว่า คุณกำลัง หารด้วย 0 (ศูนย์) หรือ divide by zero

หรือถ้ามีการอ้างอิงตำแหน่งเซลล์ ตรงเซลล์ที่อ้างอิงที่จะใช้หารนั้น มีค่าเท่ากับศูนย์ หรือเป็นเซลล์ว่าง

ตัวอย่างเช่น ถ้า D3 D4 และ D5 มีค่าเท่ากับ 20 15 5 ตามลำดับ ถ้าเราใส่สูตร

=D3/(D3-D4-D5)

สิ่งที่เกิดขึ้นก็คือ จะได้เท่ากับ 20/0

ตัวอย่างนี้จะแสดงค่า #DIV/0! ขึ้นมาทันที

และถ้าเราคลิกที่บริเวณสามเหลี่ยมสี่เขียวบริเวณมุมขวาด้านบน จะเห็นป็อปอัปตัวเลือกให้เราเลือกเพื่อดำเนินการแก้ไขหรือตรวจสอบ อย่างเช่น Show Calculation Steps

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

#N/A!

เห็นคำนี้ขึ้นมา คือการอ้างอิงที่ผิดพลาดเกิดขึ้น นั่นคือ หาไม่เจอ! อย่างเช่นที่เคยเขียนไปแล้ว ย้อนกลับไปอ่านหน่อยนะจ๊ะ

#NAME?

ถ้าคุณเขียนฟังก์ชันไม่ถูกต้องตามข้อกำหนดการเขียนในฟังก์ชันนั้น ก็จะขึ้นคำว่า #NAME? อย่างเช่น ท่านจะใส่ฟังก์ชัน Sumif แต่เขียนสูตรแบบนี้

=SUMI(B2:B25,”>5″)

คือ สะกด SUMIF ผิด ตัว F หายไปตัวหนึ่งก็จะขึ้น #NAME?

#NUM!

 ใช้ตัวเลขไม่ถูกต้อง อย่างเช่น การพิมพ์เครื่องหมายดอลลาร์ $ ซึ่งมีความหมายใน excel (โปรดอ่านเรื่องการอ้างอิงตำแหน่งเซลล์) เช่น แทนที่จะพิมพ์ 100 มีคนพิมพ์ว่า 100$ โดยตั้งใจจะให้หมายถึง 100 ดอลลาร์สหรัฐฯ แต่เนื่องจาก $ เป็นอักขระพิเศษที่มีผลต่อการดำเนินการของ Excel จะส่ง error #NUM! ให้เห็น

หรือ การใช้เครื่องหมายจุลภาค (comma ,) ซึ่งจะเป็นเครื่องหมายที่ใช้ในไวยากรณ์กำหนดรูปแบบของฟังก์ชันต่าง ๆ แต่ถ้าเราเผลอใส่ตัวเลขพร้อมเครื่องหมายจุลภาค เช่น 1,000 excel จะมองตรงนี้เป็น 2 ส่วนคือ 1 กับ 000 ซึ่งถ้าตามสูตรการใช้ฟังชันนั้นไม่มีการใช้จุลภาคตรงตามนั้นก็จะเกิด error #NUM! หรือไม่เช่นนั้น ถ้าฟังก์ชันนั้นมีจุลภาคตามหลังได้ ก็จะแสดงผลลัพธ์ผิดพลาดไปจากที่ตั้งใจ (เช่น SUM(200, 1,000) จะได้ผลลัพธ์ไม่เท่ากับ SUM(200, 1000)

หรือ อาจจะเกิดจากค่าที่ได้ มีจำนวนหลักเกินขีดจำกัดของ Excel

#REF!      

การอ้างอิงผิดพลาด เช่น เราเคยอ้างอิงคอลัมน์ C ไว้ แล้วเราเผลอลบคอลัมน์ C นั้นทิ้ง แบบนี้เป็นต้น หรือ เราอาจจะใช้การอ้างอิงที่ผิดพลาดมาจากการใช้ indirect หรืออื่น ๆ

#NULL!

ข้อผิดพลาดนี้จะแสดงขึ้นเมื่อใช้ตัวดำเนินการบางอย่างไม่ถูกต้องในสูตร อย่างเช่น เราจะหาผลรวมระหว่า A1 ถึง A20 กับ B1 ถึง B20 แต่ท่านเขียนสูตรแบบนี้

=SUM(A1:A20 B1:B20)

จะได้ผลลัพธ์เป็น #NULL!

เพราะการใช้ช่องว่างคั่นกลางระหว่างช่วง A1:A20 กับ B1:B20 นั้นคือการกำหนดตัวดำเนินการ intersect แต่ ช่วงในคอลัมน์ A ไม่มีความเกี่ยวข้องกับ คอลัมน์ B แต่อย่างใด

ความจริงสูตรนี้ ถ้าต้องการผลรวม ของทั้งสองช่วง ต้องใช้ จุลภาพ คั่นระหว่างช่วง A1:A20 กับ B1:B20

=SUM(A1:A20, B1:B20)

#VALUE!

ใช้ข้อมูลผิดประเภท เช่น ถ้าเป็น Text แต่เอามาใช้เป็นตัวคำนวณทางคณิตศาสตร์ บวกลบคูณหารกัน แบบนี้ไม่ได้ จะขึ้นเป็น #VALUE! ให้ดูต่างหน้าทันที

จะตรวจสอบและแก้ข้อผิดพลาดใน Excel ได้อย่างไร?

เวลา เราเจอ ข้อผิดพลาดใน Excel เราคงต้องตรวจสอบก่อนว่า การแสดงผลค่า Error เป็นอะไร ตามที่เขียนไปแล้วด้านบน แล้วค่อยจัดการทีละปัญหา แต่ Excel ก็มีตัวช่วย

Error Checking

ถ้าหากว่ามีค่าผิดพลาดเกิดขึ้น เราสามารถหาตัวช่วยง่าย ๆ ด้วยการไปที่ แท็บ Formula แล้วดู Error Checking ไอคอนจะเป็นเครื่องหมายอัศเจรีย์ (!) ในสามเหลี่ยมสีเหลือง กดไปจะได้คำอธิบาย และจะมีตัวช่วยคือ Show Evaluation Step นั่นคือจะไล่ขั้นตอนการคำนวณของ Excel ให้เราดูทีละขั้น

แบบนี้ก็จะช่วยได้มาก

Evaluate Formula

สมมติ มีสูตรยาว ๆ แบบนี้

=SMALL(IF(COUNTIF($A$1:$A$496,ROW($1:$500))=0,ROW($1:$500),””),ROW(A1))

อันนี้เป็นสูตรที่เขียนเมื่อตอนพูดถึงการค้นหาเลขที่หายไป บางคนพอเห็นฟังก์ชันซ้อนกันหลายอันจะงงเล็กน้อย (คนเขียนก็งง) ลองคลิกที่เซลล์ที่เขียนสูตรไว้ ก็จะเห็นขึ้นตัวช่วย บอกว่านี่คือฟังก์ชันอะไร และบริเวณที่เคอร์เซอร์อยู่คือส่วนของไวยากรณ์ไหน

แต่ถ้าเราจะต้องมาตรวจสอบว่าสูตรนี้ทำงานอย่างไร (กรณีเกิด error หรือ อยากรู้ว่าคนอื่นเขียนไว้คิดมาอย่างไรก็ได้) เวลามีสูตรยาว ๆ แบบนี้ก็ยังงงอยู่ดี ต้องค่อย ๆ ไล่หาทีละฟังก์ชันว่า มาจากไหนไปตรงไหน

ลองไปที่ แท็บ Formula แล้วดู Evaluate Formula ไอคอนจะเป็น Fx ในวงกลม จะแสดงผลจากการคำนวณให้เราดู

Trace Precedents กับ Trace Dependent

เครื่องมือสองอย่างนี้ จะช่วยให้เรารู้ว่า สูตรที่ใช้ในเซลล์นั้น อ้างอิงมาจากไหน Trace Precedents คือข้อมูลต้นทาง กดแต่ละครั้งคือ 1 ขั้นตอน ถ้าสูตรมีการอ้างอิงหลายทอด ให้เรา Double Click ที่เส้นลูกศรนั้นเพื่อไปเซลล์ต้นทางได้เลย (หรือกดคีย์บอร์ดด้วยช็อตคัต Ctrl+[ ก็ได้)

ส่วน Trace Dependent คือบอกว่า จากเซลล์นั้นจะส่งไปทางไหนต่อ ใช้เหมือน Trace Precedents

และถ้าต้องการลบเส้นลูกศรที่แสดงทิศทางออก ให้กด Remove Arrows

IFERROR

หรือเราอาจจะกลบเกลื่อนปัญหาด้วย iferror

ย้ำว่าเป็นการกลบเกลื่อนปัญหา ไม่ได้แก้ไขปัญหา คือ ปัญหาจากการคำนวณไม่ได้ก็ยังคงอยู่เหมือนเดิม เพียงแต่เราทำให้มันไปต่อไป

ปัญหาคือแบบนี้ บางทีเราคงเคยเจอการบันทึกข้อมูลที่ ตรงไหนว่าง ก็จะใส่ – (ขีดกลาง) มาเพื่อให้รู้ว่าตรงนี้ไม่ได้รับค่า คือแทนที่จะปล่อยว่าง ก็จะให้เป็นขีดเพื่อให้รู้ว่า ไม่มีนะ

แต่ถ้าเราเอาตรงนี้มาคำนวณ มันจะขึ้น #VALUE!

สมมติ เราเอา “ขีดกลาง” + 423 (เลี่ยงใช้ – ก็แล้วกัน เพราะมันเหมือนเครื่องหมายลบ)

ก็คือ

ขีดกลาง + 423

ขีดกลางคือ Text หรือ ข้อความ ไม่สามารถเอาไปบวก ลบ คูณ หาร ได้ จะได้ผลลัพธ์ #VALUE! ออกมา

แต่ในการคำนวณเป็นจำนวนมาก แล้วเราจำเป็นต้องเอาผลลัพธ์ไปใช้งานต่อ เช่น บวก ลบ คูณ หาร กับตัวเลขอื่นนี่แหละ ถ้าเอาผลลัพธ์ #VALUE! ไปใช้ ก็จะได้ผลลัพธ์ #VALUE! ทันที!

แต่มันก็มีทางแก้นั่นแหละ นั่นก็คือ ฟังก์ชัน IFERROR

เอาตัวนี้ ไปครอบสูตรที่ต้องการคำนวณ แล้วกำหนดว่า ถ้าผลลัพธ์เป็นค่า error จะให้แสดงผลเป็นอะไร เช่น ถ้า ผิดพลาด ให้แสดงเป็น 0 เพื่อเอาไปคำนวณต่อได้

ก็จะได้สูตร

=IFERROR(ขีดกลาง+ 423, 0)

ค่าของ ขีดกลาง + 423 ก็ยังเป็น #VALUE! แต่จะแสดงผลเป็น 0 เพื่อเอาไปใช้งานหรือคำนวณต่อได้ เป็นการช่วยให้ Error ใน Excel ที่เกิดขึ้นไม่ส่งผลกระทบต่อการคำนวณที่ตามมา ลองเอาไปปรับใช้