Excel คำนวณไม่ตรง ทำอย่างไรดี?
มีมิตรสหายท่านหนึ่งปรึกษาว่าใช้ฟอร์ม excel ที่แจกจ่ายกันมานานแล้วทำงานตามปรกติ ปรากฏว่าพอหัวหน้ามาคำนวณตรวจทานแล้วผลลัพธ์ไม่ตรงกัน เป็นเพราะอะไร?
ความจริงก็พอเดาออกตั้งแต่ได้ยินคำถามแล้ว แต่เพื่อให้แน่ใจเลยให้ให้มิตรสหายท่านนั้นส่งไฟล์ excel มาให้ดู และเมื่อได้ดูก็เป็นอย่างที่คิดเอาไว้
จากภาพ ช่องที่มีปัญหาคือ U6 ซึ่งเป็นผลจาก K6 X S6 หรือ 60,000 X 1.13 ผลลัพธ์ควรจะได้ 67,800 แต่ที่ excel แสดงให้เห็นคือ 67,659.57
งานนี้ excel ไม่ได้คำนวณผิดแต่อย่างใด
ปัญหา Excel คำนวณไม่ตรง
ความจริง Excel คำนวณตรงแล้ว แต่แสดงผลไม่ตรง เพราะการตั้งค่า Format Number
ในครั้งนี้ปัญหาอยู่ที่ S6 ซึ่งได้ค่ามาจาก R8/R6 หรือ 2,120/1,880 ผลลัพธ์ควรจะได้ 1.127659574 แต่ตั้งฟอร์แมตให้แสดงผลทศนิยมเพียงแค่ 2 ตำแหน่ง excel เลยปัดเศษให้เป็น 1.13 และถึงแม้ว่าจะแสดงผลลัพธ์ให้เห็น 1.13 แต่ excel ก็ยังเก็บค่า 1.127659574 เอาไว้เหมือนเดิม
เมื่อเอา 60,000 X 1.127659574 ผลลัพธ์จะได้เป็น 67,659.57447 excel คำนวณถูกต้องแล้ว
จะแก้ปัญหาอย่างไรดี?
วิธีการแก้ปัญหาที่ได้ให้มิตรสหายท่านนั้นเลือกมีสองทางคือ
- ยกเลิกการตั้งค่าทศนิยมสองตำแหน่ง ก็จะได้ผลลัพธ์ตามจริงคือ 67,659.57447
- ใส่ฟังก์ชัน Round ครอบไว้อีกทีหนึ่ง เพื่อบอก excel ว่า เก็บทศนิยมเพียงแค่สองตำแหน่งนะ คราวนี้ผลลัพธ์ก็จะเป็น 67,800 แล้ว
ความจริงเรื่องนี้เป็นเรื่องพื้นฐานที่คิดว่าทุกคนคงรู้กันแล้ว แต่บางทีก็หลงลืมกันไปบ้าง เลยเอามาเขียนบันทึกเอาไว้ว่าค่าที่ excel เก็บไว้จริง กับค่าที่ format เอาไว้บางทีมันก็หลอกตาเราได้เหมือนกัน
โดยส่วนตัว เวลาเขียนสูตรอะไรแบบนี้จะใช้ฟังก์ชัน round ในการช่วยปัดเศษทศนิยม ซึ่งก็มีบางคนที่ไม่เห็นด้วย โดยให้เหตุผลว่า เราต้องการผลลัพธ์สุดท้ายที่ใกล้เคียงความเป็นจริงที่สุด ซึ่งอันนี้ก็ต้องถามกลับไปว่า แล้วคำว่าใกล้เคียงความเป็นจริงมากที่สุดมันคือยังไง? เราจะคำนวณการปล่อยจรวดที่ต้องแม่นเป๊ะทุกจุดทศนิยม 10 ตำแหน่งหรืออย่างไร?
ปกติการปัดเศษทางบัญชีที่คุ้นเคยกันก็จะใช้ทศนิยมสองตำแหน่ง (อันนี้คิดง่าย ๆ เลย เงินสตางค์ของเรา 100 สตางค์ เท่ากับ 1 บาท เราเลยมีเศษทศนิยมเพียง 2 ตำแหน่ง ดังนั้นถ้าหากคำนวณได้ว่า 10.2519 (หรือหลักมากกว่านี้) ก็ต้องปัดเศษให้เหลือทศนิยม 2 ตำแหน่ง คือจะเป็ฯ 10.25 หรือ 10.26 ไปเลย ตามแต่ที่ตกลงกันว่าจะปัดเศษอย่างไร
ถ้าคิดแบบนี้ ขั้นตอนการคำนวณภายในก่อนถึงผลลัพธ์สุดท้ายก็ไม่จำเป็นต้องเก็บทศนิยมมาก ๆ เอาไว้ ก็ใช้ round roundup rounddown หรือ floor หรือ ceiling หรืออะไรก็ได้ตามหลักการปัดเศษที่ตกลงกันไว้ก่อน จะได้ตัดขั้นตอนการคำนวณให้ตรงกับตาเห็น และจะไม่เกิดเหตุว่าตรวจทานภายหลังแล้วมันคำนวณออกมาไม่ตรงแบบนี้
อ้างอิงจากเว็บไซต์ไมโครซอฟต์