ความผิดพลาดเป็นเรื่องธรรมชาติ

ความผิดพลาดเป็นเรื่องธรรมชาติ 1
Logic Function สิ่งสำคัญที่คุณต้องเชี่ยวชาญให้ได้
Function ที่ใช้งานบ่อยๆ ในชีวิตจริง

พอเราเริ่มเขียนสูตรไปแล้ว ผมรับรองเลยว่าคุณจะต้องเจอ 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 ArrowsClear ลูกศรที่จะขึ้นมาหลังจากกดปุ่ม 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 ไว้ได้เลย

 

........

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

อย่าลืมกดติดตามเทพเอ็กเซลได้ที่ Facebook.com/ThepExcel
และสามารถติดตามคลิปวีดีโอเจ๋งๆ ได้ที่ YouTube Channel ของ Thep Excel ครับ


Logic Function สิ่งสำคัญที่คุณต้องเชี่ยวชาญให้ได้
Function ที่ใช้งานบ่อยๆ ในชีวิตจริง
  •  
  •  
  •  
  •  
  •  

Posted on: April 3, 2015
Tags: , , , ,

1 thought on “ความผิดพลาดเป็นเรื่องธรรมชาติ

Leave a Reply

Your email address will not be published. Required fields are marked *