Excel ก็เหมือนกับซอฟต์แวร์อื่น ๆ ก็คือ มีข้อจำกัดบางเรื่องที่อาจจะไม่ได้พบในการทำงานบ่อย ๆ อยู่บ้าง และ DefExcel.com ได้พบความประหลาดใจกับงานที่ทำเมื่อไม่นานมานี้กับเรื่อง “ข้อจำกัดตัวเลข 15 หลัก”
หลังจากนำเข้าจากไฟล์ csv ซึ่งได้มาจากระบบฐานข้อมูลของหน่วยงาน คอลัมน์ X นี้คือ Condo_Seq หรือรหัสประจำโครงการจัดเก็บเป็นตัวเลขเก็บในรูป TEXT (โปรดสังเกตว่าข้อความในคอลัมน์นี้ ชิดซ้าย และ มีสามเหลี่ยมสีเขียวเล็ก ๆ แจ้งเตือนที่มุมบนขวา บอกชัดเจนว่า นี่คือ ตัวเลขที่เก็บในรูปแบบข้อความ -number stored as text)
DefExcel.com ก็ทำงานตามขั้นตอนปกติ นั่นคือตรวจเช็คว่า Condo_Seq มีซ้ำหรือเปล่า ก็ใช้เครื่องมือ Conditional Formatting > Highlight Cells Rules > Duplicate Values กับคอลัมน์ Condo_Seq ถ้ามีซ้ำจะมีไฮไลต์ซ้ำกันหรือเปล่า ก็ปรากฏว่ามีไฮไลต์สีแดงส้มนี้ ขึ้นมาที่ 2 เซลล์นี้
แต่ ดูแล้วมันคนละรหัสกัน
- 10000000000000711
- 10000000000000715
มองแล้ว ไม่ควรที่จะซ้ำ ลองเคลีร์ Rules และตรวจสอบใหม่ ก็ยังเหมือนเดิม ตรวจสอบทั้งคอลัมน์ เผื่อว่าจะมีการซ่อนอะไรไว้หรือไม่ก็ไม่ปรากฏว่ามีแอบอะไรไว้?
(ต้องบอกก่อนว่า DefExce.com ใช้ Microsoft 365 แต่คิดว่า excel เวอร์ชันอื่นก็ไม่ต่างกัน)
ปัญหาเกิดจากอะไร?
ถ้าเผลอไปกดสามเหลี่ยมสีเขียวเล็ก ๆ นั้น แล้ว convert to number เมื่อไหร่ Excel จะแสดงเป็นตัวเลขทางวิทยาศาสตร์โดยอัตโนมัติเป็น 1E+15
ทำไมจึงเป็นเช่นนั้น?
ข้อจำกัดตัวเลข 15 หลัก
ก่อนอื่น เราทุกคนรู้ว่า Excel ก็เหมือนซอฟต์แวร์อื่น นั่นคือ มีข้อจำกัด ซึ่งไมโครซอฟต์ได้แสดงข้อจำกัดของ Excel ไว้ที Excel specifications and limits
หรืออ่านภาษาไทยที่ลิงก์นี้ https://support.microsoft.com/th-th/office/ข้อกำหนดและขีดจำกัดของ-excel-1672b34d-7043-467e-8e27-269d656771c3
ในหัวข้อ Calculation specifications and limits ระบุว่า Number precision ที่ 15 digits หมายความว่า excel จะยอมรับตัวเลข 15 หลักเท่านั้น หากใส่ตัวเลข 16 หลักขึ้นไปExcel จะเปลี่ยนตัวเลขที่เกินมาเป็นศูนย์
อันนี้เป็นไปตามข้อกำหนด IEEE 754 สำหรับวิธีการจัดเก็บและคำนวณเลขทศนิยม ดังนั้น Excel จึงเก็บตัวเลขที่มีนัยสำคัญไว้เพียง 15 หลัก และเปลี่ยนหลักหลังจากตำแหน่งที่สิบห้าเป็นศูนย์
ตัวเลข 15 หลักเป็นข้อจำกัดของ excel ถ้าหากว่าท่านพิมพ์ตัวเลข 16 หลักเข้าไป เช่น 10000000000000711 หรือ 10000000000000715
ทั้งสองตัวเลข จะเก็บค่าเพียงแค่ 10000000000000710 ทั้งสองตัว
ด้วยเหตุนี้ จากตัวอย่างข้างต้นที่พบเจอจึงกลายเป็นว่า 10000000000000711 หรือ 10000000000000715 กลายเป็นมีค่าซ้ำกัน ทั้งที่ความจริงไม่ซ้ำกัน
เราใส่เลข 16 หลักอย่างไร?
การพิมพ์เลข 16 หลัก ถ้าเราพิมพ์ตามตรงปกติ อย่างเช่นตัวอย่างนี้ จะได้เป็น 1E+15 เหมือนกันทั้งคู่
สำหรับท่านที่ไม่คุ้นเคยกับการแสดงข้อมูลในรูปแบบ 1E+15 นี้คืออะไร อธิบายตามหลักก็คือ ตัวเลข 1 × 10^15 (หนึ่งคูณสิบยกกำลังสิบห้า)
แต่ถ้าให้อธิบายง่าย ๆ ให้นึกถึงการประมาณค่า นึกอย่างง่าย เลขตัวแรกคืออะไร ให้เอาตัวเลขนั้นตั้ง เช่น 1E ก็เอาเลข 1 เป็นตัวตั้ง หลังจากนั้นดูว่าเครื่องหมายบวกหรือลบตามด้วยเลขอะไร นั่นคือจำนวนหลักของ 0 เครื่องหมายบวกคือเพิ่มหลักเลข 0 ลบคือถอยไปหลักทศนิยมเลข 0
เช่น 1E+0 ก็จะเป็น 1 นั่นคือ 1 มีเลข 0 ตาม 0 หลัก (หรือ หนึ่งคูณสิบยกกำลังศูนย์)
หรือ 4E+2 ก็คือ 400 นั่นคือ 4 มีเลข 0 ตาม 2 หลัก (หรือ สี่คูณสิบยกกำลังสอง)
ปัญหาและการแก้ปัญหา
ตัวอย่างคลาสสิกหนึ่งก็คือ การบันทึกเลขบัตรเครดิตใน Excel ซึ่งมี 16 หลัก ทางเว็บไซต์ของไมโครซอฟต์มีคำแนะนำว่า ให้ปรับ format ให้เป็นข้อความ หรือใส่ quotation mark (‘) ก็จะแก้ปัญหานี้ได้
https://learn.microsoft.com/en-us/office/troubleshoot/excel/last-digits-changed-to-zeros
แต่ก็อย่างที่เห็น ผลลัพธ์คือได้อย่างที่เห็นจริง ๆ เพียงแต่ว่า ถ้าใช้การไฮไลต์เพื่อหาค่าซ้ำก็จะเป็นออกมาเป็นแบบนี้ ไม่ว่าจะ format ให้เป็นข้อความ หรือจะใส่ quotation mark (‘) ก็ไม่แก้ปัญหานี้
ในความเข้าใจแรก ก็นึกว่าถ้าหากปรับรูปแบบเป็น ข้อความ (text) แล้ว น่าจะไม่พบข้อจำกัดนี้ แต่สุดท้ายแล้ว มันคือ number stored as text หรือ ตัวเลขที่เก็บในรูปแบบข้อความ – และนั่นอาจจะแปลได้ว่า ตัวเลขก็คือตัวเลข ไม่ว่าจะจัดเก็บในรูปแบบ text แล้วก็ตาม ไม่ทำให้เปลี่ยนข้อจำกัดในการเป็นตัวเลขแต่อย่างใด
ข้อสังเกต
ถึงแม้ว่าสิ่งที่เกิดขึ้นนี้ เมื่อถึงขั้นตอนทำงานก็ไม่ได้สร้างความเสียหายผิดพลาดอะไร เพราะเมื่อนำไปใช้ lookup ก็ยังจับข้อมูลที่ถูกต้องมาได้
แต่จะดีกว่าหรือไม่? ถ้าเราหลีกเลี่ยงปัญหาที่ไม่จำเป็นเหล่านี้ เช่น การใช้รหัส ให้เริ่มต้นด้วยตัวอักษร หรือ มีอักขระคั่นระหว่างตัวเลข เพื่อที่จะบังคับให้สิ่งที่กรอกลงไปนั้นเป็น “ข้อความ” เพราะอย่างไรก็ตาม เราไม่ได้ใช้สิ่งนี้ในการคำนวณอะไรอยู่แล้ว?
