GB/T8170数值修约规则在Excel中的实现

GB/T8170数值修约规则在Excel中的实现

赵静仪  林德浩

    利用Excel来进行各种数值的统计分析可以极大的减轻人工计算的工作量和减少人工计算的误差，但是依据Excel本身的计算规则，所有的数值修约都是按照“四舍五入”来进行的。而在实际工作中，许多数据的运算处理都必须依照GB/T8170-2008《数值修约规则与极限数值的表示和判定》中的数值修约规则进行，在Excel中却没有相对应的函数，这样计算出来的结果就和按GB/T8170-2008的规则修约的结果产生了偏差。
   

一、GB/T8170-2008对数值修约的进舍规则

    GB/T8170-2008的数值修约进舍规则并不是单纯的“四舍五入”，而是根据不同情况进行:
    规则一:如果拟舍弃数字的最左一位数字小于5时，则舍去，即保留的各位数字不变。例1:将10.349修约到一位小数，得10.3。
    规则二:如果拟舍弃数字的最左一位数字大于5，则进一，即保留的末位数字加1。例2:将1260修约到百数位，得1300(或13×102)。
    规则三:如果拟舍弃数字的最左一位数字为5，且其后有非0数字时进一，即保留的末位数字加1。例3:将10.501修约到个数位，得11。
    规则四:如果拟舍弃数字的最左一位数字为5，而右面无数字或皆为0时，若所保留的末位数字为奇数(1，3，5，7，9)则进一，为偶数(2，4，6，8，0)则舍弃。
    例4:修约间隔为0.1(或10-1)(见表1)
   

<CTSM>    表1</CTSM>
    规则五:负数修约时，先将它的绝对值按规则一至四进行修约，然后在所得值前面加上负号。
    例5:修约到十数位(见表2)
   

<CTSM>    表2</CTSM>
    规则六:GB/T8170-2008的修约规则中，还有0.5单位修约与0.2单位修约的情况。
    0.5单位修约:将拟修约数值乘以2，然后按指定数位依以上规则一至四进行修约，所得数值再除以2。
    0.2单位修约:将拟修约数值乘以5，然后按指定数位依以上规则一至四进行修约，所得数值再除以5。
   

二、Excel对数值修约的进舍规则

    Excel中的取整函数对数值的修约只有“四舍五入”的规则，当拟舍弃数字的最左一位数字为5，而右面无数字或皆为0时，计算结果就会与GB/T8170-2008修约结果不同。
    例4:修约间隔为0.1(或10-1)(见表3)
   

<CTSM>    表3</CTSM>
   

三、利用自定义函数在Excel中实现GB/T8170-2008修约规则

    Excel的自定义函数是用VBA来编程的，而Visual Basic语言里面的ROUND()函数刚好符合上述的修约规则一至规则五，不过它只能修约小于等于单位1的修约间隔，而且缺少0.5单位和0.2单位的修约间隔，需要做一些补充。下面我们就用VBA来实现新的符合GB/T8170-2008的函数ROUNDGB()。
    第一步:选择菜单“工具→宏→Visual Basic编辑器”命令(或按下键盘Alt+F11组合键)，打开Visual Basic窗口，我们将在这里自定义函数。
    第二步:进入Visual Basic窗口后，选择菜单“插入→模块”命令，于是得到“模块1”，在其中输入如下自定义函数的代码:
    Function ROUNDGB(number1 As Double，Optional digits1 As Integer，Optional flag1 As Double)As Double
    ′本程序实现ROUNDGB()进行GB/T8170-2008数值规则的修约。
    ′参数说明:
    number1拟进行修约的数值(必选参数)；
    ′digits1修约的小数位，(可选参数，默认为0，即取整)。大于0，为保留多少位小数；小于0，为保留多少位整数。如digits1=-3表示number1要修约到千数位。
    ′flag1修约间隔(可选参数，默认为1)；此参数有0.2，0.5可选，当0.2时表示修约间隔为0.2，当0.5时表示修约间隔为0.5。
    Select Case flag1
    Case 0.5
    number1=number1*2′0.5单位的修约
    Case 0.2
    number1=number1*5′0.2单位的修约
    End Select
    If digits1>=0 Then
    roundgb=Round(number1，digits1)′调用VB的Round函数
    Else
    digits2=-digits1
    roundgb=Round(number1*10^digits1)*10^digits2′当digits1为负数时的处理。
    End If
    Select Case flag1
    Case 0.5
    roundgb=roundgb/2′0.5单位的修约
    Case 0.2
    roundgb=roundgb/5′0.2单位的修约
    End Select
    End Function
    第三步:函数自定义完成后，选择菜单“文件→关闭并返回到Excel”命令，返回到Excel工作表窗口。这样我们就可以在Excel里面调用ROUNDGB()函数了。
    表4是Excel取整函数ROUND()和自定义函数ROUNDGB()取值的比较。
   

<CTSM>    表4</CTSM>
   

四、结束语

    Excel本身“四舍五入”的数值修约规则，已经可以满足大多数日常工作，但在有明确要求使用GB/T8170修约规则的计算、测量、统计中，现有的函数则无法满足要求。如果单纯使用Excel公式的逻辑判断加数值小数计算来实现本功能，由于Excel本身的局限性，数值计算的准确度不够，就很容易因为相应误差而导致出错。本文使用VBA编程的方式来实现GB/T8170-2008数值修约规则，相对其他方法来说比较容易实现和方便使用，就算是不懂得VB语言的人员，自定义函数一经输入以后，每次使用时调用ROUNDGB()函数就可以像调用其他Excel函数一样方便。
    作者单位【广东省汕头市质量计量监督检测所】

好贴，必须要顶顶

不错的函数，比我以前用过的要简洁得多。

谢谢楼主分享啊！！！！

谢谢分享。

厉害厉害！

电脑不太好，学习了

学习了，谢谢

不可能那么简单，很多特殊情况都没有考虑。0.10000001，0.999999，修约一下。会出现错误。如果不考虑特殊情况，何必用上ＶＢ

A2输入待修约数字，B2输入保留小数点位数，C2输入公式

IF(B2=0,IF(OR(AND(ISEVEN(INT(A2)),A2-INT(A2)=0.5),A2-INT(A2)<0.5),INT(A2),INT(A2)+1),IF(LEN(IF(OR(AND(ISEVEN(INT(A2*POWER(10,B2))),A2*POWER(10,B2)-INT(A2*POWER(10,B2))=0.5),A2*POWER(10,B2)-INT(A2*POWER(10,B2))<0.5),INT(A2*POWER(10,B2))/POWER(10,B2),(INT(A2*POWER(10,B2))+1)/POWER(10,B2)))=LEN(INT(A2)),IF(OR(AND(ISEVEN(INT(A2*POWER(10,B2))),A2*POWER(10,B2)-INT(A2*POWER(10,B2))=0.5),A2*POWER(10,B2)-INT(A2*POWER(10,B2))<0.5),INT(A2*POWER(10,B2))/POWER(10,B2),(INT(A2*POWER(10,B2))+1)/POWER(10,B2))&"."&REPT("0",B2),IF(LEN(INT(A2*POWER(10,B2)))+1>LEN((IF(OR(AND(ISEVEN(INT(A2*POWER(10,B2))),A2*POWER(10,B2)-INT(A2*POWER(10,B2))=0.5),A2*POWER(10,B2)-INT(A2*POWER(10,B2))<0.5),INT(A2*POWER(10,B2))/POWER(10,B2),(INT(A2*POWER(10,B2))+1)/POWER(10,B2)))),(IF(OR(AND(ISEVEN(INT(A2*POWER(10,B2))),A2*POWER(10,B2)-INT(A2*POWER(10,B2))=0.5),A2*POWER(10,B2)-INT(A2*POWER(10,B2))<0.5),INT(A2*POWER(10,B2))/POWER(10,B2),(INT(A2*POWER(10,B2))+1)/POWER(10,B2)))&REPT("0",LEN(INT(A2*POWER(10,B2)))-1-LEN((IF(OR(AND(ISEVEN(INT(A2*POWER(10,B2))),A2*POWER(10,B2)-INT(A2*POWER(10,B2))=0.5),A2*POWER(10,B2)-INT(A2*POWER(10,B2))<0.5),INT(A2*POWER(10,B2))/POWER(10,B2),(INT(A2*POWER(10,B2))+1)/POWER(10,B2))))),IF(OR(AND(ISEVEN(INT(A2*POWER(10,B2))),A2*POWER(10,B2)-INT(A2*POWER(10,B2))=0.5),A2*POWER(10,B2)-INT(A2*POWER(10,B2))<0.5),INT(A2*POWER(10,B2))/POWER(10,B2),(INT(A2*POWER(10,B2))+1)/POWER(10,B2)))))

A2输入待修约数值，B2输入保留小数点位数，C2输入公式。对9.9999多的情况不适用，还待改进

IF(B2=0,IF(OR(AND(ISEVEN(INT(A2)),A2-INT(A2)=0.5),A2-INT(A2)<0.5),INT(A2),INT(A2)+1),IF(LEN(IF(OR(AND(ISEVEN(INT(A2*POWER(10,B2))),A2*POWER(10,B2)-INT(A2*POWER(10,B2))=0.5),A2*POWER(10,B2)-INT(A2*POWER(10,B2))<0.5),INT(A2*POWER(10,B2))/POWER(10,B2),(INT(A2*POWER(10,B2))+1)/POWER(10,B2)))=LEN(INT(A2)),IF(OR(AND(ISEVEN(INT(A2*POWER(10,B2))),A2*POWER(10,B2)-INT(A2*POWER(10,B2))=0.5),A2*POWER(10,B2)-INT(A2*POWER(10,B2))<0.5),INT(A2*POWER(10,B2))/POWER(10,B2),(INT(A2*POWER(10,B2))+1)/POWER(10,B2))&"."&REPT("0",B2),IF(LEN(INT(A2*POWER(10,B2)))+1>LEN((IF(OR(AND(ISEVEN(INT(A2*POWER(10,B2))),A2*POWER(10,B2)-INT(A2*POWER(10,B2))=0.5),A2*POWER(10,B2)-INT(A2*POWER(10,B2))<0.5),INT(A2*POWER(10,B2))/POWER(10,B2),(INT(A2*POWER(10,B2))+1)/POWER(10,B2)))),(IF(OR(AND(ISEVEN(INT(A2*POWER(10,B2))),A2*POWER(10,B2)-INT(A2*POWER(10,B2))=0.5),A2*POWER(10,B2)-INT(A2*POWER(10,B2))<0.5),INT(A2*POWER(10,B2))/POWER(10,B2),(INT(A2*POWER(10,B2))+1)/POWER(10,B2)))&REPT("0",LEN(INT(A2*POWER(10,B2)))-1-LEN((IF(OR(AND(ISEVEN(INT(A2*POWER(10,B2))),A2*POWER(10,B2)-INT(A2*POWER(10,B2))=0.5),A2*POWER(10,B2)-INT(A2*POWER(10,B2))<0.5),INT(A2*POWER(10,B2))/POWER(10,B2),(INT(A2*POWER(10,B2))+1)/POWER(10,B2))))),IF(OR(AND(ISEVEN(INT(A2*POWER(10,B2))),A2*POWER(10,B2)-INT(A2*POWER(10,B2))=0.5),A2*POWER(10,B2)-INT(A2*POWER(10,B2))<0.5),INT(A2*POWER(10,B2))/POWER(10,B2),(INT(A2*POWER(10,B2))+1)/POWER(10,B2)))))

Function Round2(rng As Double, number As Integer) As Double
Round2 = Round(CDec(rng), number)
End Function


可以试试这个，最简洁的方法，使用CDec函数进行强制转换即可解决浮点小数的问题。参见
http://www.gfjl.org/forum.php?mod=viewthread&tid=223427

sunrry 发表于 2023-9-18 10:49
Function Round2(rng As Double, number As Integer) As Double
Round2 = Round(CDec(rng), number)
End Fu ...

还用上VB ，如果用上VB，特殊情况大都可以解决。四舍六入的问题的逻辑语言很简单，分为舍去的情况，剩下的就是进1的情况。即末位为偶数且舍去部分刚好为整5，或小于5的情况即可。实现各种特殊情况的才难点

sunrry 发表于 2023-9-18 10:49
Function Round2(rng As Double, number As Integer) As Double
Round2 = Round(CDec(rng), number)
End Fu ...

我们力图通过内建函数解决，这样一般的人都可以通过电子表格实现使用

wangyiyong7703 发表于 2023-9-18 19:48
我们力图通过内建函数解决，这样一般的人都可以通过电子表格实现使用

函数解决不了浮点小数的问题，总会有特例，虽然极小概率碰到

sunrry 发表于 2023-9-22 14:58
函数解决不了浮点小数的问题，总会有特例，虽然极小概率碰到

你说一个浮点问题，看这个公式能否解决。也希望你多提问题，公式进一步改进，多几个if而已

好贴，必须要顶顶