[EXCEL] อ้าวทำไมตัวเลข Excel มันเป็นแบบนี้หละ

Blog ตอนนี้เขียนอธิบายกึ่งบ่นด้วยครับ เนื่องจากอยู่ในช่วง UAT ระบบครับ แล้ว User แจ้ง Defect มา 13 ข้อ ซึ่ง 10 ข้อ มันไม่ได้เกิดจากระบบคำนวณผิดครับ แต่เป็นที่ตัว Excel มาข้อมูลที่ User ได้ Copy เพื่อมาผูกสูตรตรวจครับ

ทำไมตัวเลข Excel มันผิดไปหละ

• เกิดจาก Excel มันข้อมูลที่ได้ เป็นตัวเลขเยอะๆ ตัว Excel มันปรับการแสดงผลให้เป็นแบบ Exponential Notation หรือ Scientific Notation แทนครับ

แก้ไขอย่างไร ?

• Default แล้ว Excel มันกำหนด Format เป็นแบบ General ครับ (ใช้ได้กับทุกเรื่องประมาณนั้น)

• Format แบบ General ตัว Excel มันมีข้อจำกัด ถ้ามันเกิด 12 ตำแหน่ง Microsoft Excel มันจัดแสดงผลให้เป็นแบบ Exponential Notation หรือ Scientific Notation แทนครับ แต่ข้อมูลจริงๆ ยังอยู่ครบครับ

• ถ้ามันเป็นตัวเลขจริงๆ ให้กำหนด Format ให้เป็น Number แทนครับ

• ตรงนี้ไม่จำเป็นต้องกำหนด Decimal Place ให้เป็น 0 ก็ได้นะครับ ที่ผมกำหนดเพิ่ม เพราะจะได้เอา่ไปเทียบกับ Column Real Value ได้ครับ

Scientific Notation คืออะไร ?

• Scientific Notation เป็นเทคนิคทางคณิตศาสตร์รูปแบบนึงที่ให้ลดการแสดงผลของข้อมูล โดยเขียนตัวเลขให้อยู่ในพจน์ (Term) ของเลขยกกำลังฐานสิบ
  m x 10ⁿ

m คือ สัมประสิทธ์ (significand หรือ mantissa)
n คือ ตัวชี้กำลัง (exponent)

• Exponential Notation (E notation) เป็นศัพท์ที่เจาะจงจากมันมาจากเครื่องคิดเลข (ปุ่ม E) / คอมพิวเตอร์ โดยมีรูปแบบ
  mEn
• ยกตัวอย่าง
  • ยอดรวม 1.23456E-15 บาท มีค่าเท่ากับ 1.23456 × 10^-15 บาท
  • ยอดรวม 1.23456E+15 บาท มีค่าเท่ากับ 1.23456 × 10¹⁵ บาท
• โดยทาง Microsoft มองว่าอยากให้ข้อมูลแสดงผลได้ครบถ้วนใน Cell เลยกำหนดเป็นแบบนี้ครับ

จากปัญหาของ User แก้ไขได้อย่างไร ?

• ตรวจสอบข้อมูลก่อนครับ ว่าจริงๆแล้ว เป็นตัวเลขที่ต้องนำไปใช้คำนวณ หรือป่าว
• อย่างเคสของผมตัวเลขชุดนี้ไม่ได้เอาไปใช้คำนวณต่อครับ เป็นเลขที่อ้างอิงตอนส่งรายการให้ทาง ธปท เท่านั้นจึงได้แนะให้ User กำหนด Format ให้เป็น ZipCode แทนครับ เนื่องจากเป็นข้อมูลผสมระหว่าง Number / String

• ผลลัพธ์ที่ได้ครับ ซูมให้ชัดๆ ว่าข้อมูลไม่ได้หายไปไหนครับ

จบอย่างไร ?

• แม้ว่าจะแก้ไป 3 ข้อแล้ว ที่ระบบผิดจริง เหลือ 10 ข้อที่มาจาก Excel user รับฟังเฉยๆ แต่สุดท้ายหลังจากชี้แจ้งไป User ยังขอให้ Build โปรแกรมใหม่ ขยับเลขเวอร์ชันด้วย อ้างว่าบริษัทปรับแก้ระบบครับ #อิหยังหวะ อันนี้หลังจากผมไปสืบๆมาพบว่า User ท่านนั้นกำหนด KPI ว่าต้องเจอ Defect ในระบบเยอะๆ เพื่อประเมินว่าตัวเองมีความสามารถสูงครับ ผมก็งงกับตรรกะแบบนี้เหมือนกันครับ
• อ๋อ จริงๆแล้วเรื่องนี้ User ควรแจ้ง Microsoft Thailand ให้ส่งเรื่องไปมากกว่าครับ เพราะ มีคนอยากได้อยู่เยอะเหมือนกัน (Ref: Vote to fix maddening Excel auto-convert-to-scientific-notation behavior : excel (reddit.com))
  ** ต้องมารอลุ้นกับครับ ว่า User จะแจ้ง Microsoft Thailand หรือป่าว ?
• อย่างไรก็ตาม ก่อนที่จะโทษระบบ พยายามตรวจสอบก่อนครับ ว่ามันมาจากอะไร อย่างเคสนี้เอาจริงๆ ผมมองเป็นว่า Human Error มากกว่าครับ เพราะเนื่อไฟล์จริงๆ ข้อมูลครบถ้วนครับ

Reference

• Display numbers in scientific (exponential) notation (microsoft.com)
• When I paste a long number, Excel turns it into scientific notation and changes numbers. - Microsoft Tech Community