Error ใน Excel

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

สำหรับ excel เวอร์ชันหลัง ๆ นี้มีการปรับปรุงตรวจสอบการเขียนสูตรหรือการใส่ฟังก์ชันอำนวยความสะดวกให้ผู้ใช้งานมากแล้ว และจะมีหน้าต่างคำเตือนสำหรับความผิดพลาดต่าง ๆ

อย่างเช่น

เราเขียนสูตรว่า B2*1.25

(คือเอาค่าที่อยู่ในช่อง B2 มาคูณด้วย 1.25)

Error ใน Excel

สมมติว่าเราเขียนผิด แทนที่จะเป็น 1.25 กลายเป็น 1,25 (ใช้จุลภาคแทน มหัพภาค หรือ จุด) เมื่อเรากด enter จะมีหน้าต่างเล็ก ๆ ขึ้นมาบอกว่า เราพิมพ์ผิดพลาดนะ และ excel จะแก้ให้ จะยอมรับหรือไม่ ถ้าเรากด yes ยอมรับไป excel ก็จะแก้ไขให้เราเอง

Error ที่ไม่ใช่ Error

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

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

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

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

error ใน excel ที่เจอบ่อยมีอะไรบ้าง?

#DIV/0!

ถ้าหากว่าขึ้นคำนี้มา ให้คิดไว้ก่อนเลยว่า เกิดการหารด้วย 0 หรือ อ้างอิงการหารด้วยเซลล์ว่าง

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

=D3/(D3-D4-D5)

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

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

Error ใน Excel #DIV0/!
error #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 (โปรดอ่านเรื่องการอ้างอิงตำแหน่งเซลล์)  หรือ การใช้เครื่องหมายจุลภาค (comma ,) ซึ่งจะเป็นเครื่องหมายกำหนดไวยากรณ์ของสูตรในฟังก์ชันต่าง ๆ หรือ อาจจะเกิดจากค่าที่ได้ มีจำนวนหลักเกินขีดจำกัดของ 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! ให้ดูต่างหน้าทันที

จะตรวจสอบและแก้ Error อย่างไร?

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

Error Checking

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

Error Checking เมื่อพบปัญหา Error ใน 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)

ใช้ IfError กลบเกลื่อนปัญหา Error ใน Excel

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

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

%d bloggers like this: