ของจริง ของปลอม ของใช้ไม่ได้ใน Excel

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

ของจริงที่ทำให้สูตร IF กลายเป็นของปลอม

เนื้อหาที่จะนำมาอธิบายต่อไปนี้จะชี้เป็นชี้ตายให้กับคนที่ชอบใช้สูตร IF หรือสูตรใดก็ตามที่เกี่ยวข้องกับการตัดสินใจหรือใช้ในการค้นหาข้อมูลว่า แฟ้มงานที่คุณสร้างขึ้นนั้นมีความน่าเชื่อถือหรือไม่ เพื่อพิสูจน์ให้เห็นกับตา ลองดูสูตร IF ต่อไปนี้แล้วคิดว่าจะได้คำตอบออกมาเป็นคำว่า เท่ากัน หรือ ไม่เท่ากัน

=IF( 22.3-22.2=0.1, "เท่ากัน", "ไม่เท่ากัน")

เชื่อหรือไม่!!! Excel จะให้คำตอบออกมาว่า 22.3-22.2 นั้นไม่เท่ากับ 0.1 โดยคืนค่าออกมาเป็นคำว่า ไม่เท่ากัน ซึ่งถือเป็นคำตอบที่ถูกต้อง เพราะ 22.3-22.2 ไม่ได้คำนวณออกมาแล้วให้ผลลัพธ์เท่ากับ 0.1 ตามที่เราเข้าใจ

ขอให้ลองสร้างสูตร =22.3-22.2 ลงไปในเซลล์ แล้วกดปุ่ม F2 ตามด้วยปุ่ม F9 จะพบว่าค่าที่แท้จริงที่ Excel คำนวณได้คือ 0.100000000000001 ซึ่งแน่นอนว่าไม่ใช่ 0.1

บางคนมองค่า 0.100000000000001 แล้วยอมรับตัวเลขที่ได้นี้ เพราะเห็นว่ามันต่างจาก 0.1 ที่ต้องการเพียงเล็กน้อย ถ้าใครคิดเช่นนี้แสดงว่าเข้าใจ Excel ผิด เพราะ Excel จะไม่ยอมรับว่าค่า 0.1 เท่ากับ0.100000000000001 ไม่ว่าค่าที่แตกต่างกันนั้นจะน้อยมากก็ตาม

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

Excel จะใช้รูปแบบ (Format Number) แสดงค่าในเซลล์ตามลักษณะตัวเลขที่พิมพ์ นี่คือสาเหตุประการแรกที่อธิบายว่า ทำไม Excel จึงไม่แสดงค่า 0.100000000000001 ในเซลล์ออกมาให้เห็นครบทุกหลัก เนื่องจากตัวเลข 22.3-22.2 ที่พิมพ์ลงไปมีทศนิยมเพียงหนึ่งหลัก ดังนั้น Excel จึงแสดงผลลัพธ์ให้เห็นตัวเลขที่มีทศนิยมเพียงหนึ่งหลักตามไปด้วย

ค่าที่เพี้ยนไปไม่ได้ถือว่าเกิดจากการคำนวณที่ผิดพลาด แต่เป็นเพราะ Excel ใช้หลักการคำนวณแบบเลขฐานสองแล้วแปลงกลับมาเป็นเลขฐานสิบ ซึ่งค่า 22.3-22.2 คำนวณแบบเลขฐานสอง ได้คำตอบเป็นเลขที่ซ้ำไม่รู้จบ (Repeating Binary Number) จากนั้นเมื่อแปลงกลับมาเป็นเลขฐานสิบให้พวกเราเข้าใจ จึงต้องปรับค่าให้เหลือเพียง 15 หลัก (Excel มีความละเอียดPrecision โดยรับตัวเลขได้สูงสุด 15 หลัก) แล้วส่งผลให้ได้ผลลัพธ์แตกต่างจากที่ควรบ้างเล็กน้อย และมิได้เกิดขึ้นเฉพาะกรณี 22.3-22.2 เท่านั้น เราไม่มีทางคาดการณ์ได้เลยว่าจะเพี้ยนเมื่อใดและเกิดจากการคำนวณใด

ดังนั้นก่อนที่คุณจะใช้สูตรที่เกี่ยวข้องกับการตัดสินใจหรือสูตรในการค้นหาข้อมูล เช่น IF, SumIF, VLookup, หรือ Match จำเป็นต้องปรับค่าให้เท่ากับค่าที่ต้องการ เพื่อให้เป็นไปตามเงื่อนไขที่ต้องการก่อนเสมอ โดยใช้สูตรอื่นเข้ามาช่วยปรับค่า จากนั้นเมื่อได้ค่าตามที่ต้องการจริงๆแล้วคุณจะกำหนดรูปแบบ Format ใดๆให้กับค่านั้นก็ตามใจ (การกำหนดรูปแบบไม่ได้ช่วยปรับค่าที่แท้จริงแต่อย่างใด และห้ามใช้ Set precision as displayed เพราะจะมีผลต่อตัวเลขทั้งแฟ้มโดยไม่มีการเตือนผู้ใช้)

สูตรสำคัญซึ่งใช้ในการปรับค่าตัวเลขให้เป็นไปตามต้องการ (โปรดศึกษารายละเอียดของสูตรได้จาก Excel Help) ได้แก่

1. สูตร =Round(ตัวเลข, จำนวนหลัก) ใช้สำหรับการปัดค่า
   
   =Round(123.45,0) คืนค่า 123
   =Round(123.45,1) คืนค่า 123.5
   =Round(123.45,-1) คืนค่า 120
   =Round(123.45,-2) คืนค่า 100
2. สูตร =Trunc(ตัวเลข, จำนวนหลัก) ใช้สำหรับการตัดค่า
   
   =Trunc(123.45,0) คืนค่า 123
   =Trunc(123.45,1) คืนค่า 123.4 ต่างจาก Round
   =Trunc(123.45,-1) คืนค่า 120
   =Trunc(123.45,-2) คืนค่า 100
3. สูตร =Int(ตัวเลข) ใช้สำหรับปรับเป็นตัวเลขจำนวนเต็ม(ที่น้อยกว่าค่าเดิม)
   
   =Int(1.23) คืนค่า 1
   =Int(-1.23) คืนค่า -2
4. สูตร =Mod(เลขตั้งตั้ง, เลขตัวหาร) ใช้สำหรับหาเศษที่เหลือจากการหาร
   
   =Mod(7,2) คืนค่า 1 เพราะ 7/2 เทียบเท่ากับ 3 1/2
   =Mod(7,4) คืนค่า 3 เพราะ 7/4 เทียบเท่ากับ 1 3/4
   =Mod(123.45,1) คืนค่าเท่ากับ .45 !!!