文章背景:在进行数据处理时,有时需要对数据进行取整,以满足数据分析的要求。下面对Excel自带的一些取整函数进行介绍。
(1) INT(number)
(2) TRUNC(number, [num_digits])
(3) ROUND(number, num_digits)
(4) ROUNDUP(number, num_digits)
(5) ROUNDDOWN(number, num_digits)
(6) MROUND(number, multiple)
(7) CEILING(number, significance)
(8) FLOOR(number, significance)
Rounds a number down to the nearest integer.(向下取整)
Formula Result
=INT(3.14159) 3
=INT(-3.14159) -4
Truncates a number to an integer by removing the fractional part of the number. (数据截断)
Formula Result
=TRUNC(5.3) 5
=TRUNC(3.146,2) 3.14
The ROUND function rounds a number to a specified number of digits. (四舍五入)
Formula Result
=ROUND(2.15, 1) 2.2
=ROUND(2.149, 1) 2.1
=ROUND(-1.475, 2) -1.48
=ROUND(21.5, -1) 20
=ROUND(626.3,-3) 1000
=ROUND(1.98,-1) 0
=ROUND(-50.55,-2) -100
Rounds a number up, away from 0 (zero).
Formula Result
=ROUNDUP(3.2,0) 4
=ROUNDUP(76.9,0) 77
=ROUNDUP(3.14159, 3) 3.142
=ROUNDUP(-3.14159, 1) -3.2
=ROUNDUP(31415.92654, -2) 31500
Rounds a number down, toward zero.
Formula Result
=ROUNDDOWN(3.2, 0) 3
=ROUNDDOWN(76.9,0) 76
=ROUNDDOWN(3.14159, 3) 3.141
=ROUNDDOWN(-3.14159, 1) -3.1
=ROUNDDOWN(31415.92654, -2) 31400
MROUND returns a number rounded to the desired multiple.
Formula Result
=MROUND(10, 3) 9
=MROUND(-10, -3) -9
=MROUND(1.3, 0.2) 1.4
=MROUND(5, -2) #NUM!
Returns number rounded up, away from zero, to the nearest multiple of significance.
Formula Result
=CEILING(2.5, 1) 3
=CEILING(-2.5, -2) -4
=CEILING(-2.5, 2) -2
=CEILING(1.5, 0.1) 1.5
=CEILING(0.234, 0.01) 0.24
Rounds number down, toward zero, to the nearest multiple of significance.
Formula Result
=FLOOR(3.7,2) 2
=FLOOR(-2.5,-2) -2
=FLOOR(-2.5,2) -4
=FLOOR(2.5,-2) #NUM!
=FLOOR(1.58,0.1) 1.5
=FLOOR(0.234,0.01) 0.23
参考资料:
[1] Coursera课程(everyday-excel-part-1)
[2] Microsoft Support技术文档
相关阅读:
[1] 【Excel技巧】- 取整函数(四舍五入、向上取整,向下取整(https://www.zhihu.com/column/p/27298037)