如何用excel做数值修约?

数值修约与四舍五入是有区别的,在介绍数值修约之前,先介绍excel内置的函数公式如何实现数值的舍入计算的。

一、在excel中做数值的四舍五入

在excel中有很多个函数可以做数值的舍入计算,它们分别是:

ROUND、ROUNDUP、ROUNDDOWN 、MROUND

其中ROUND、ROUNDUP、ROUNDDOWN 3个函数的语法都是相似的,如下所示:

ROUND(number, num_digits)

number参数是要四舍五入的数字;

num_digits是要进行四舍五入运算的保位数;

如果 num_digits 大于 0(零),则将数字四舍五入到指定的小数位数;

如果 num_digits 等于 0,则将数字四舍五入到最接近的整数;

如果 num_digits 小于 0,则将数字四舍五入到小数点左边的相应位数。

唯一的不同就是ROUNDUP函数将数值向远离0的大小舍入,ROUNDDOWN函数将数值向靠近0的大小舍入。

如下图所示:

而MROUND函数是将数值修约到最接近的某个数的倍数,它根据要舍入的数值更靠近哪个倍数值,就选择舍入为那个倍数值。

比如数值是41.24,修约到最接近的10的倍数可以使用公式

=MROUND(A2,10)

结果是40。

MROUND函数不同的是,CEILING函数可以实现按照固定的倍数向上舍入,而FLOOR函数则可以实现按照固定的倍数向下舍入。

比如数值是41.24,用以下公式

=CEILING(41.24,10)

将得到结果50,向上舍入到最接近10的倍数,而用以下公式

=FLOOR(41.24,10)

将得到结果40,向下舍入到最接近10的倍数。

二、在excel中做数值修约

上文介绍了excel中内置的诸多函数如何实现数值的舍入计算的,接下来介绍数值修约。

国家标准 GB/T 8170-2008 《数值修约规则与极限数值的表示和判定》中规定

数值修约是指通过省略原数值的最后若干位数字,调整所保留的末位数字,使最后所得到的值最接近原数值的过程。

数值修约的规则如下:

1.拟舍去数字的最左一位数字小于5,则舍去,保留其余各位数字不变。

2.拟舍去数字的最左一位数字大于5,则进一,即保留数字的末位数字加1。

3.拟舍去数字的最左一位数字是5,且其后有非0数字时进一,即保留数字的末位数字加1。

4.3.拟舍去数字的最左一位数字是5,且其后无数字或皆为0时,若所保留的末位数字为奇数则进一,即保留数字的末位数字加1,若所保留的末位数字为偶数(0、2、4、6、8),则舍去。

5.负数修约时,先将其绝对值按照1-4的规则修约,然后在前面加上负号。

以上规则就是著名四舍六入五留双 数值修约规则。

分析上述规则,可以发现,其中第1-3条规则与目前的四舍五入是一致的。只有第4条规则,需要当舍弃数字的最左一位数字是5,且其后无数字或均为0时,看保留的末位数字是奇数还是偶数,如果是奇数,跟四舍五入的规则是一样的,只有当要保留的末位数字为偶数时,需要舍去。

如果数值在A列,则可以在B列输入如下的公式:

=IF(MOD(ABS(A2*POWER(10,$C$1)),2)=0.5,ROUNDDOWN(A2,$C$1),ROUND(A2,$C$1))

然后填充填充柄,即可实现四舍六入五留双的数值修约。

其中C1单元格输入的是要保留的有效数字,其中2表示保留小数点后2位有效数字,0表示保留到个位,-2表示保留到百位等。

公式解析:

MOD(ABS(B2*POWER(10,$C$1)),2)=0.5 用于判断拟舍弃数字的最左一位数字是5,且其后无数字或均为0时,所保留的末位数字是否为偶数(0、2、4、6、8),如果是,则用ROUNDDOWN(B2,$C$1)函数向下舍去,如果不是,其它情况同四舍五入规则,就用ROUND(B2,$C$1)函数即可。

如下图所示:

 

       

仅有1条评论 发表评论

  1. Aaron Zhang /

    因为浮点运算误差会导致 IF 判定错误,公式追加TRUNC进行截取后再判定:

    =IF(TRUNC(MOD(ABS(A2*POWER(10,$C$1)),2),$C$1)=0.5,ROUNDDOWN(A2,$C$1),ROUND(A2,$C$1))

  2. 匿名 /

    0.565因为5后面有0以上的数字吧

  3. 匿名 /

    保留2位

  4. 马闻瑫 /

    您是一下为什么0.565用公式修约之后是0.57,正常应该是0.56啊。

发表评论