Font s :
Background :

สูตรปัดตัวเลข

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

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

สูตรปัดตัวเลข แบ่งออกเป็น 3 กลุ่มใหญ่ๆ คือ

  1. สูตรปัดตัวเลขขึ้น ได้แก่

    RoundUp ปัดตัวเลขขึ้น ให้เหลือจำนวนหลักตัวเลขตามต้องการ
    Ceiling ปัดตัวเลขขึ้น สู่ตัวเลขเท่ากับ จำนวนเท่าคูณค่าทวีคูณ
  2. สูตรปัดตัวเลขลง ได้แก่

    RoundDown ปัดตัวเลขลง ให้เหลือจำนวนหลักตัวเลขตามต้องการ
    Floor ปัดตัวเลขลง สู่ตัวเลขเท่ากับ จำนวนเท่าคูณค่าทวีคูณ
    Int ปัดตัวเลขลง เป็นเลขจำนวนเต็มที่น้อยลง
    Trunc ตัดตัวเลข ให้เหลือจำนวนหลักตัวเลขตามต้องการ
  3. สูตรปัดตัวเลข ขึ้นก็ได้ ลงก็ได้ ได้แก่

    Round ใช้เลข 5 เป็นเกณฑ์ ในการปัดตัวเลขขึ้นหรือลง
    Fixed ทำหน้าที่เหมือน Round แต่เปลี่ยนผลลัพธ์เป็น text
    Round พิเศษ ใช้ค่าทวีคูณเป็นเกณฑ์ ปัดตัวเลขใกล้เคียง เข้าสู่ค่าทวีคูณ
    MRound ใช้ค่าทวีคูณเป็นเกณฑ์ ปัดตัวเลขใกล้เคียง เข้าสู่ค่าทวีคูณ

 

สูตรปัดตัวเลขขึ้น

=ROUNDUP(ตัวเลข,จำนวนหลัก)

=ROUNDUP(1.23,1)
1.3
 
=ROUNDUP(1.23,0)
2
 
=ROUNDUP(12.3,-1)
20

=CEILING(ตัวเลข,ค่าทวีคูณ)

=CEILING(1.23,.25)
1.25
 
=CEILING(1.23,.5)
1.5
 
=CEILING(1.23,1)
2
 
=CEILING(12.3,2)
14

ค่าทวีคูณ หมายถึง ตัวเลขซึ่งจะเพิ่มหรือลด เป็นจำนวนเท่าตัวของค่าเดิม เช่น ค่าทวีคูณ = .5 มีค่า 0 > 0.5 > 1.0 > 1.5 > 2 >>> 

สูตรปัดตัวเลขลง

=ROUNDDOWN(ตัวเลข,จำนวนหลัก)

=ROUNDDOWN(1.23,1)
1.2
 
=ROUNDDOWN(1.23,0)
1
 
=ROUNDDOWN(12.3,-1)
10

=FLOOR(ตัวเลข,ค่าทวีคูณ)

=FLOOR(1.23,.25)
1
 
=FLOOR(1.23,1)
1
 
=FLOOR(12.3,2)
12

=INT(ตัวเลข)

=INT(1.23)
1
 
=INT(-1.23)
-2

=TRUNC(ตัวเลข,จำนวนหลัก)

=TRUNC(1.23)
1
 
=TRUNC(-1.23)
-1
 
=TRUNC(1.23,1)
1.2
 
=TRUNC(12.3,0)
12
 
=TRUNC(12.3,-1)
10

ข้อสังเกต สูตร Trunc จะตัดตัวเลขทิ้งเลย ส่วนสูตร Int จะตัดตัวเลขทิ้งให้เป็นค่าที่น้อยลง
ทำให้ Int(-1.23) กลายเป็น -2

สูตรปัดตัวเลข ขึ้นก็ได้ ลงก็ได้

=ROUND(ตัวเลข,จำนวนหลัก)

=ROUND(1.23,1)
1.2
 
=ROUND(1.25,1)
1.3
 
=ROUND(14.2,-1)
10
 
=ROUND(15.2,-1)
20

สูตร Round จะปัดลง เมื่อตัวเลขหลักถัดไปน้อยกว่า 5
สูตร Round จะปัดขึ้น เมื่อตัวเลขหลักถัดไปมากกว่าหรือเท่ากับ 5

=FIXED(ตัวเลข,จำนวนหลัก,ต้องการ comma หรือไม่)

=FIXED(1.23,1)
"1.2"
 
=FIXED(1.25,1)
"1.3"

=ROUND(ตัวเลข/ค่าทวีคูณ,0)*ค่าทวีคูณ

=ROUND(10.125/.05,0)*.05
10.15
 
=ROUND(10.125/.10,0)*.10
10.10
 
=ROUND(10.124/.25,0)*.25
10.00
 
=ROUND(10.125/.25,0)*.25
10.25

สูตร Round ดัดแปลงนี้มีประโยชน์มาก จะปัดลง เมื่อตัวเลขมีค่า ไม่ถึงครึ่งของค่าทวีคูณ จะปัดขึ้น เมื่อตัวเลขมีค่า มากกว่าหรือเท่ากับครึ่งหนึ่งของค่าทวีคูณ สูตรซึ่งทำหน้าที่เทียบเท่าสูตรนี้ แต่ต้องใช้ Analysis ToolPak Add-Inคือ

=MROUND(ตัวเลข,ค่าทวีคูณ)


เมื่อปัดตัวเลขขึ้นลงได้ตามต้องการแล้ว ยังมีสูตรสำคัญอีกสูตรหนึ่งใช้หา เศษที่เหลือจากการหาร
เมื่อกำหนดตัวหาร = 1

=MOD(ตัวตั้ง,ตัวหาร)

=MOD(10.125,1)
0.125

หากข้อกำหนดของการปัดตัวเลขซับซ้อนกว่านี้ เช่น กำหนดช่วงการปัดขึ้นหรือปัดลง ขึ้นกับค่าตัวเลขว่ามากหรือน้อยเป็นช่วงๆไม่เท่ากัน ให้ใช้สูตร Mod ร่วมกับสูตร IF ซ้อน IF เพื่อตรวจสอบค่าตัวเลข แล้วปัดตามแต่จะกำหนด เช่น หากตัวเลขอยู่ในเซลล์ B4

=IF(MOD(B4,1)<0.125,ROUNDDOWN(B4,0),
  IF(AND(MOD(B4,1)>=0.125,MOD(B4,1)<0.375),TRUNC(B4)+0.15,
  IF(AND(MOD(B4,1)>=0.375,MOD(B4,1)<0.625),TRUNC(B4)+0.3,
  IF(AND(MOD(B4,1)>=0.625,MOD(B4,1)<0.875),TRUNC(B4)+0.75,
  ROUNDUP(B4,0.5)))))

Categories

About this Entry

This page contains a single entry by สมเกียรติ ฟุ้งเกียรติ published on April 1, 2008 1:10 PM.

วิธีป้องกันสูตร was the previous entry in this blog.

วิธีใช้วันที่และเวลา is the next entry in this blog.

Find recent content on the main index.

Font s :
Background :