提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  工具

当前栏目

Excel: 数据取整函数（向上取整，向下取整）

Excel数据 函数 取整 向上 向下
2023-06-13 09:12:58 时间

文章背景：在进行数据处理时，有时需要对数据进行取整，以满足数据分析的要求。下面对Excel自带的一些取整函数进行介绍。

  • INT, TRUNC
  • ROUND, ROUNDUP, ROUNDDOWN
  • MROUND, CEILING, FLOOR

(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)

(1) INT(number)

Rounds a number down to the nearest integer.（向下取整）

Formula                             Result
=INT(3.14159)            3
=INT(-3.14159)           -4

(2) TRUNC(number, [num_digits])

Truncates a number to an integer by removing the fractional part of the number. （数据截断）

  • Num_digits Optional. A number specifying the precision of the truncation. The default value for num_digits is 0 (zero).
Formula                                     Result
=TRUNC(5.3)                         5
=TRUNC(3.146,2)          3.14

(3) ROUND(number, num_digits)

The ROUND function rounds a number to a specified number of digits. （四舍五入）

  • num_digits Required. The number of digits to which you want to round the number argument.
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

(4) ROUNDUP(number, num_digits)

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

(5) ROUNDDOWN(number, num_digits)

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

(6) MROUND(number, multiple)

MROUND returns a number rounded to the desired multiple.

  • Multiple Required. The multiple to which you want to round number.
  • MROUND rounds up, away from zero, if the remainder of dividing number by multiple is greater than or equal to half the value of multiple.
  • The Number and Multiple arguments must have the same sign. If not, a #NUM error is returned.
Formula                                             Result
=MROUND(10, 3)                          9
=MROUND(-10, -3)                -9
=MROUND(1.3, 0.2)                1.4
=MROUND(5, -2)                          #NUM!

(7) CEILING(number, significance)

Returns number rounded up, away from zero, to the nearest multiple of significance.

  • Significance Required. The multiple to which you want to round.
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

(8) FLOOR(number, significance)

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)

相关文章