เพราะเหตุใดนำ 5.5 หักด้วย 5.4 แล้วไม่ได้เท่ากับ 0.1

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

• 10.9-10.8 แล้วไม่เท่ากับ 0.1
• 5.5-5.4 แล้วไม่เท่ากับ 0.1
• 4.3-4.2 แล้วไม่เท่ากับ 0.1
• ฯลฯ

          ทดสอบได้อย่างไรว่าไม่เท่ากัน เพราะได้ทดลองคีย์ลงในเซลล์แล้วก็เห็นได้คำตอบเป็น 0.1 ซึ่งถูกต้องแล้ว ทดสอบง่าย ๆ ครับ ลองคีย์ตามนี้

1. A1 กรอก 5.5
2. A2 กรอก 5.4
3. A3 คีย์ =A1-A2
4. A4 คีย์ =A3=0.1 ==> คำตอบจะต้องได้ False

          เป็นการให้โปรแกรมตรวจสอบว่า 5.5 หักด้วย 5.4 แล้วเท่าักับ 0.1 หรือไม่ ถ้าเท่าจะได้ค่า True ถ้าไม่เท่าจะได้ค่า False ดังนั้นที่เห็นด้วยตาว่า 0.1 คงจะไม่ใช่เสียแล้ว

อ้อ...ใครได้ค่า True บอกด้วยครับ จะได้ซื้อเครื่องนั้นมาใช้

อ้าว...แล้วอย่างนี้จะเชื่อได้อย่างไรว่า Excel คำนวณถูกต้อง

          เรื่องความแม่นยำในการคำนวณไม่ต้องสงสัย Excel หรอกครับ เพราะ Excel เก่งกว่าที่คิดไว้มาก สำหรับกรณีที่ยกมานี้ สาเหตุที่ผลการคำนวณออกมาแล้วไม่เป็นไปตามที่เราหวังจะได้เห็น เกิดจากการแปลงเลขฐาน 10 ที่เราคีย์ลงไปให้เป็นเลขฐาน 2 (0 กับ 1) แล้วแปลงกลับเป็นเลขฐาน 10 เพื่อการแสดงผล แม้จะไม่ตรงเผง แต่จะผิดพลาดในหลักมาก ๆ เช่น หลักที่ 15 เป็นต้น จึงไม่กระทบกับการคำนวณที่ไม่ได้ต้องการความละเอียดขนาดนั้น

          จากตัวอย่าง 5.5-5.4 จะได้ 0.0999999999999996 (คีย์ตัวเลขในเซลล์ใด ๆ  =5.5-5.4 แล้วลากเมาส์ไปคลุม 5.5-5.4 จากนั้นกดแป้น F9 จะได้ตัวเลข 0.0999999999999996) ซึ่งไม่เท่ากับ 0.1

          จากผลต่างดังกล่าว ก็มีคนสงสัยต่อไปว่า หากต้องการทำให้ได้ 0.1 ตามที่ควรจะเป็นจะต้องทำอย่างไร เรื่องนี้ไม่ยากครับ

1. ใช้ฟังก์ชั่น Round เข้ามาช่วย ลองคีย์ที่เซลล์ใด ๆ ใหม่เป็น
   =Round(5.5-5.4,2)=0.1
   สูตร Round เป็นสูตรการปัดเศษให้เหลือทศนิยม 2 ตำแหน่ง คราวนี้คำตอบต้องเป็น True อย่างแน่นอน
2. กำหนด Precision as displayed โดยเข้าเมนู Tools > Options > Calculation > Precision as displayed

การกำหนดเช่นนี้จะทำให้ค่าในเซลล์เหลือจุดทศนิยมเท่าที่เรากำหนด Format หรือนั่นก็คือเท่าที่มองเห็นด้วยตาแต่จะมีผลกระทบทั้งไฟล์ ดังนี้

• หากค่านั้น ๆ เป็นค่าคงที่ จะถูกตัดทศนิยมให้เหลือเท่าที่มองเห็นในเซลล์อย่างถาวร
• หากค่านั้น ๆ ได้จากสูตร จะสามารถปรับทศนิยมเพิ่มลดได้ตามต้องการซึ่งเหมือน Round ให้โดยอัตโนมัติ

การพิสูจน์ว่าหลังจากกำหนด Precision as displayed แล้ว 5.5-5.4 เท่ากับ 0.1 ให้ลองดังนี้ครับ

1. A1 กรอก 5.5
2. A2 กรอก 5.4
3. A3 คีย์ =A1-A2
4. กำหนด Format ให้เป็นทศนิยม 2 ตำแหน่ง
5. A4 คีย์ =A3=0.1 ==> คำตอบจะต้องได้ True

คีย์ 1 แล้วให้แสดงเป็น 1000

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

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

วิธีการกำหนดทำดังนี้ครับ

1. เข้าเมนู Tools เลือก Options
2. เลือก Tab Edit แล้วทำเครื่องหมายที่ Fixed decimal
3. ที่ Places คีย์ -3
4. OK

การกำหนดเช่นนี้จะคูณด้วย 1000 ให้เลย และหากไม่ต้องการให้คูณด้วย 1000 เราคีย์จุดตามหลังเข้าไปด้วย เช่น

• คีย์ 1 จะกลายเป็น 1000
• คีย์ 1. จะกลายเป็น 1

ทดลองเล่นโดยคีย์ตัวเลขอื่น ๆ ตามต้องการดูครับ

การรวมค่าจากการปัดเศษด้วยสูตรเดียว

ปกติหากเราต้องการหาค่ารวมจากการปัดเศษ จะต้องเขียนสูตร Round ก่อนแล้วค่อย Sum ค่าที่ได้ทั้งหมดออกมาอีกที

การใช้วิธีลัดเพื่อรวมค่าที่ปัดเศษทีเดียวทั้งหมดโดยไม่ต้องเขียนสูตร Round ไว้ก่อนจะทำให้ประหยัดเวลา ประหยัดเซลล์ได้มาก ยกตัวอย่างเช่น A2:A6 ประกอบด้วย

A2: 2.354
A3: 3.653
A4: 3.765
A5: 9.228
A6: 4.773

หากปัดให้เหลือทศนิยม 2 หลักทั้งหมดก่อนแล้วค่อยรวม ที่เซลล์ใด ๆ สามารถใช้สูตร Array ดังนี้

=Sum(Round(A2:A6,2))

Ctrl+Shift+Enter

หรือหากไม่ต้องการใช้สูตร Array แบบกำหนดเอง ก็สามารถใช้สูตร Array ธรรมชาติแบบด้านล่างเพื่อไม่ต้องกด Ctrl+Shift+Enter

=Sumproduct(Round(A2:A6,2))

Enter

การปัดเศษตามจำนวนที่ต้องการ

การปัดเศษแบบปกติใน Excel สามารถใช้ฟังก์ชั่นเกี่ยวกับการปัดเศษ ซึ่งได้แก่

Round
Roundup
Rounddown
Ceiling
Floor
Int
Trunc

แต่หากต้องการปัดตามจำนวนที่ต้้องการ ยกตัวอย่างเช่นปัดคราวละ 5 หมายถึงว่าหากหลักหน่วยมากกว่าหรือเท่ากับ 2.5 ให้ปัดเป็น 5 ถ้าน้อยกว่านั้นให้ปัดเป็น 0 สามารถประยุกต์ใช้ฟังก์ชั่น Round ได้ดังนี้ครับ

สมมุติค่าที่ต้องการปัดอยู่ที่ A1 ที่เซลล์ B1 คีย์

=Round(A1/5,0)*5

สูตรด้านบนนี้ต่างจากสูตร

=Ceiling(A1,5)

เนื่องจาก Ceiling จะปัดขึ้นเป็นจำนวนเท่าของ 5 เสมอ

และต่างจาก

=Floor(A1,5)

เนื่องจาก Floor จะปัดลงเป็นจำนวนเท่าของ 5 เสมอ

สำหรับฟังก์ชั่นการปัดคราวละตามที่กำหนดสามารถใช้ Mround ได้อีกฟังก์ชั่นหนึ่ง เช่น

=Mround(A1,5)

ผลลัพธ์ที่ได้จะเท่ากับ Round(A1/5,0)*5 เพียงแต่การใช้ Mround ต้องติดตั้ง Analysis Toolpak สำหรับ Excel 2003 สามารถทำการติดตั้งโดย

เข้าเมนู Tools เลือก Add-ins > ทำเครื่องหมายที่ Analysis Toolpak > OK