พอเราเริ่มเขียนสูตรไปแล้ว ผมรับรองเลยว่าคุณจะต้องเจอ 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 นั่นเอง ยกตัวอย่างเช่น
A1 =B1+5, B1=C1+10, C1=A1-2
แบบนี้ Excel ก็จะงงว่า ตกลงแล้วจะให้เริ่มยังไงนะ? โดยถ้าเกิดเหตุการณ์แบบนี้ Excel จะขึ้นข้อความมาเตือนเราก่อน และจะมีลูกศรสีน้ำเงินชี้ให้เห็นว่ามันเป็นงูกินหางยังไง ซึ่งเราก็ควรจะแก้ไขให้ไม่เกิดอาการงูกินหางซะ
อย่างไรก็ตาม เราสามารถให้ Excel ยอมให้เกิดการงูกินหางได้โดยไปปรับใน Setting เพื่อให้รองรับการคำนวณซ้ำทีละรอบ หรือทีเรียกว่า Iteration แต่วิธีนี้ Advance มากผมจะไม่ขอพูดถึงนะครับ
การตรวจสอบความผิดพลาดของสูตร (Formula Auditing)
เมื่อเจอความผิดพลาดแล้วยังแก้ไขเบื้องต้นไม่ได้ คราวนี้เราต้องตรวจหาและทำความเข้าใจความผิดพลาดและแก้ไขมันอย่างละเอียดแล้วล่ะ ซึ่งเรามีวิธีในการตรวจสอบสูตรได้ 2 วิธีหลักๆ คือ
- ใช้คีย์ลัด F9 ในการคำนวณสูตร
- ใช้เครื่องมือในกลุ่ม Formula Auditing
ใช้คีย์ลัด F9 ในการแปลงสูตรให้เป็นผลลัพธ์การคำนวณ (Value)
เมื่อเราเขียนสูตรแล้วเกิดสงสัยอยากจะรู้ว่าบางส่วนของสูตรที่เราเขียนลงไปคำนวณออกมาได้ค่าเท่าไหร่ เราสามารถเลือกแถบดำบริเวณสูตรที่เราต้องการจะตรวจสอบ แล้ว กดปุ่ม F9 เพื่อ Convert สูตรที่กำลังลากแถบดำไว้ ให้กลายเป็น Value ได้เลย
Step1 : ลากแถบดำเลือกสิ่งที่ต้องการ โดยมีเทคนิคการเลือกแถบดำได้ 2 ลักษณะ
- กดลากแถบดำตามปกติ หรือ
- กดที่ แต่ละ Arguments ใน Formula Tool Tips ที่ขึ้นมาเวลาคลิ๊กเลือกที่ฟังชั่น ซึ่งวิธีนี้สะดวกมากไม่ต้องมานั่งลากแถบดำเอง แถมเหมาะกับสูตรที่มีความซับซ้อนสูงด้วย
Step2 : กด F9 เพื่อทำการแปลงบริเวณสูตรที่เลือก ให้กลายเป็น Value ที่คำนวณเสร็จแล้ว
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
Trace Precedents Step ที่ 2 (กดปุ่ม Trace Precedents 2 ที)
จะเห็นว่ามันจะวิ่งการ Trace ย้อนกลับต่อไปอีก (กดซ้ำได้จนกว่าจะถึงต้นทางของสูตรเลย)
เครื่องมืออื่นๆ
| เครื่องมือ | วัตถุประสงค์การใช้งาน | เทคนิคเสริม |
| Show Formulas | แสดงสูตรที่อยู่ในช่องให้เห็นในหน้าจอเลย (แทนที่จะเห็นเป็นผลลัพธ์ที่คำนวณแล้วตามปกติ) | |
| Error Checking | เอาไว้ตรวจสอบ Error รวมถึงการเขียนสูตรวนกันเป็นงูกินหาง (Circular Reference) | |
| Evaluate Formula | เอาไว้ตรวจสอบสูตรทีละ Step ว่า Excel คำนวณได้ผลออกมาได้อย่างไร | เหมาะกับการตรวจสอบสูตรที่ซับซ้อนมากๆ |
| Watch Window | เอาไว้ตรวจสอบข้อมูลใน Cell ที่เราสนใจ เช่น เห็นสูตร ผลลัพธ์ ชื่อที่ตั้งไว้ ชื่อ Sheet และ Workbook เป็นต้น |
|
