co-create
บทความนี้คือส่วนหนึ่งของโครงการ ร่วมสร้าง “หนังสือคู่มือ Excel ที่เจ๋งที่สุด” ใครที่มี comment เพื่อแนะนำ ปรับปรุงหนังสือได้ คุณจะได้เครดิตในฐานะผู้ร่วมเขียน ลงในหนังสือที่จะพิมพ์จริงๆ ด้วย! อ่านรายละเอียด และดูสารบัญหนังสือ คลิ๊กที่นี่


 

พอเราเริ่มเขียนสูตรไปแล้ว ผมรับรองเลยว่าคุณจะต้องเจอ Error ในรูปแบบต่างๆ โดยเฉพาะคนที่เริ่มเขียนสูตรใหม่ๆ ด้วยวิธีการพิมพ์ใน Formula Bar ยิ่งอาจเจอ Error สูงมาก ซึ่งคนที่ไม่เคยเจอ Error เลยใช่ว่าจะเก่งเสมอไปนะครับ มันสามารถตีความได้ว่ายังเขียนสูตรไม่เยอะหรือไม่ยากพอต่างหาก

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

เอาล่ะ แล้วมันจะ Error ในรูปแบบไหนได้บ้าง แล้วจะแก้ไขได้ยังไง มาดูกันครับ

รูปแบบของ Error ใน Excel

รูปแบบของ Error ความหมายและแนวทางแก้ไข
#VALUE! มี 2 กรณีหลักๆ คือ1.เราใส่ข้อมูลผิดประเภทลงไป เช่น ใส่ Text ลงไปใน Argument ที่จะต้องเป็น Number  เช่น =LEFT(“inwexcel”,“abc”) เป็นต้น2.ใส่ข้อมูลเป็น Range ลงไปใน Argument ที่ควรจะใส่ Cell เดียว เช่น =LEN(A1:C1) เป็นต้น
ซึ่งถ้าต้องการจะทำแบบนี้ จะต้องใช้สูตรแบบที่ Advance กว่าปกติที่เรียกว่า Array Formula แทน ซึ่งเราจะมาเรียนรู้กันบทท้ายๆ เลยครับ
#NAME? เกิดขึ้นเพราะเราระบุชื่อ Function หรือ Defined Name ที่ไม่มีอยู่จริง
#NUM! ตัวเลขที่ใช้มีปัญหา เช่น มีค่าน้อยหรือมากเกินไป
#DIV/0 เกิดจากการหารด้วยช่องที่มีค่าเป็น 0 หรือเป็น Blank
#REF! ใส่ Cell Reference ที่ไม่มีตัวตน มักเกิดจากการไปลบ Cell/Row/Column หลังจากใส่สูตรไปแล้ว
#N/A หากข้อมูลไม่เจอ มักเกิดกับ Function พวก Lookup ข้อมูลต่างๆ
#NULL! เกิดจาการใช้ Reference Operator ที่เป็นแบบ Intersect (หาส่วนที่ซ้อนทับกัน) โดยใช้เครื่องหมาย ช่องว่าง แต่ปรากฏว่าไม่มี Range ที่ Intersect กันเลย บางที Error นี้อาจเกิดจากการไม่ได้ตั้งใจพิมพ์เครื่องหมาย space ลงไปก็ได้
######## จริงๆ แล้วอันนี้ไม่ใช่ Error ครับ เพียงแต่ข้อมูลมันยาวเกินกว่าที่จะแสดงให้เห็นใน 1 ช่องได้ เราจะต้องยืดความกว้างคอลัมน์ให้กว้างขึ้น หรือเปลี่ยนรูปแบบ Number Format ให้ตัวเลขมันสั้นลง (เช่นใส่ comma ต่อท้าย ให้กดลงทีละหลักพัน) ถึงจะมองเห็นครับ

งูกินหาง

บางทีการที่สูตรมัน Error ก็อาจเกิดจากการที่เราใส่สูตรแบบ “งูกินหาง” นั่นคือมีการอ้างอิงสูตรกันไปเรื่อยๆ จนครบเป็น Loop หรือที่เรียกว่า Circular Reference นั่นเอง ยกตัวอย่างเช่น

circular-reference

A1 =B1+5, B1=C1+10, C1=A1-2

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

อย่างไรก็ตาม เราสามารถให้ Excel ยอมให้เกิดการงูกินหางได้โดยไปปรับใน Setting เพื่อให้รองรับการคำนวณซ้ำทีละรอบ หรือทีเรียกว่า Iteration แต่วิธีนี้ Advance มากผมจะไม่ขอพูดถึงนะครับ

การตรวจสอบความผิดพลาดของสูตร (Formula Auditing)

เมื่อเจอความผิดพลาดแล้วยังแก้ไขเบื้องต้นไม่ได้ คราวนี้เราต้องตรวจหาและทำความเข้าใจความผิดพลาดและแก้ไขมันอย่างละเอียดแล้วล่ะ  ซึ่งเรามีวิธีในการตรวจสอบสูตรได้ 2 วิธีหลักๆ คือ

  1. ใช้คีย์ลัด F9 ในการคำนวณสูตร
  2. ใช้เครื่องมือในกลุ่ม Formula Auditing 

ใช้คีย์ลัด F9 ในการแปลงสูตรให้เป็นผลลัพธ์การคำนวณ (Value)

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

Step1 : ลากแถบดำเลือกสิ่งที่ต้องการ  โดยมีเทคนิคการเลือกแถบดำได้ 2 ลักษณะ

  • กดลากแถบดำตามปกติ หรือ
  • กดที่ แต่ละ Arguments ใน Formula Tool Tips ที่ขึ้นมาเวลาคลิ๊กเลือกที่ฟังชั่น ซึ่งวิธีนี้สะดวกมากไม่ต้องมานั่งลากแถบดำเอง แถมเหมาะกับสูตรที่มีความซับซ้อนสูงด้วย
    formula-evaluation-1-add

Step2 : กด F9 เพื่อทำการแปลงบริเวณสูตรที่เลือก ให้กลายเป็น Value ที่คำนวณเสร็จแล้ว
formula-evaluation-2add

Step3 : ตรวจดู/ทำความเข้าใจผลลัพธ์ ว่าเป็นไปตามที่ต้องการหรือไม่

Step4 : ทำให้ Value กลับมาเป็นสูตรตามปกติ โดยมีเทคนิคการย้อนกลับได้ 2 ลักษณะ

  • ย้อนกลับการแปลงค่า Step ล่าสุด : กด Undo (Ctrl+Z)
  • Cancel การแปลงค่าทั้งหมด : กด Esc

 

ใช้เครื่องมือในกลุ่ม Formula Auditing

เครื่องมือนี้เหมาะกับผู้ที่มีหน้าที่ตรวจสอบงานของคนอื่นที่ทำมาใน Excel เป็นอย่างมาก เรียกได้ว่าเป็น “เครื่องมือช่วยแกะสูตร” ชั้นดีเลยล่ะ เพราะในชีวิตจริงนั้น คนที่ส่งไฟล์งานมาให้เรา มักจะไม่ค่อยได้อธิบายว่าสูตรที่เขียนมาทำงานยังไง เรามักจะต้องมานั่งไล่สูตรเองเสมอๆ ซึ่งหากต้องมานั่งไล่เองจะเสียเวลามาก Excel จึงทำ Tool นี้มาช่วยครับ

เครื่องมือในกลุ่ม Formula Auditing อยู่ใน

[Formulas] à Formula Auditing มีให้เลือกใช้ดังนี้

เครื่องมือ วัตถุประสงค์การใช้งาน เทคนิคเสริม
Trace Precedents หาว่าสูตรที่ช่องที่ตรวจสอบใช้เป็น Input มาจากช่องไหน ·   กด Trace ได้หลายครั้ง มันจะวิ่งที่ละ Step·   สามารถกด Double Click ที่เส้นเชื่อมเพื่อวิ่งไปยังช่องต้นทางได้เลย (หรือกด Ctrl+[ )
Trace Dependents หาว่าสูตรที่ช่องที่ตรวจสอบส่งเป็น Output นั้นไปที่ช่องไหน ·   กด Trace ได้หลายครั้ง มันจะวิ่งที่ละ Step·   สามารถกด Double Click ที่เส้นเชื่อมเพื่อวิ่งไปยังช่องปลายทางได้(หรือกด Ctrl+] )
Remove Arrows Clear ลูกศรที่จะขึ้นมาหลังจากกดปุ่ม Trace ทั้งสองอัน

Trace Precedents Step ที่ 1

trace1

 

Trace Precedents Step ที่ 2 (กดปุ่ม Trace Precedents 2 ที)

trace2

จะเห็นว่ามันจะวิ่งการ Trace ย้อนกลับต่อไปอีก (กดซ้ำได้จนกว่าจะถึงต้นทางของสูตรเลย)

เครื่องมืออื่นๆ

เครื่องมือ วัตถุประสงค์การใช้งาน เทคนิคเสริม
Show Formulas แสดงสูตรที่อยู่ในช่องให้เห็นในหน้าจอเลย (แทนที่จะเห็นเป็นผลลัพธ์ที่คำนวณแล้วตามปกติ)
Error Checking เอาไว้ตรวจสอบ Error รวมถึงการเขียนสูตรวนกันเป็นงูกินหาง (Circular Reference)
Evaluate Formula เอาไว้ตรวจสอบสูตรทีละ Step ว่า Excel คำนวณได้ผลออกมาได้อย่างไร เหมาะกับการตรวจสอบสูตรที่ซับซ้อนมากๆ
Watch Window เอาไว้ตรวจสอบข้อมูลใน Cell ที่เราสนใจ เช่น เห็นสูตร ผลลัพธ์ ชื่อที่ตั้งไว้ ชื่อ Sheet และ Workbook เป็นต้น
  • เราสามารถเลือก Cell ที่สนใจจะดู แล้วกด Add Watch… ได้เลย
  • เราสามารถกด Double Click ใน Watch Window เพื่อพุ่งไปยัง Cell ที่ Add ไว้ได้เลย