GB/T8170數(shù)值修約規(guī)則在Excel中的實現(xiàn)

GB/T8170數(shù)值修約規(guī)則在Excel中的實現(xiàn)

趙靜儀  林德浩

    利用Excel來進行各種數(shù)值的統(tǒng)計分析可以極大的減輕人工計算的工作量和減少人工計算的誤差，但是依據(jù)Excel本身的計算規(guī)則，所有的數(shù)值修約都是按照“四舍五入”來進行的。而在實際工作中，許多數(shù)據(jù)的運算處理都必須依照GB/T8170-2008《數(shù)值修約規(guī)則與極限數(shù)值的表示和判定》中的數(shù)值修約規(guī)則進行，在Excel中卻沒有相對應(yīng)的函數(shù)，這樣計算出來的結(jié)果就和按GB/T8170-2008的規(guī)則修約的結(jié)果產(chǎn)生了偏差。
   

一、GB/T8170-2008對數(shù)值修約的進舍規(guī)則

    GB/T8170-2008的數(shù)值修約進舍規(guī)則并不是單純的“四舍五入”，而是根據(jù)不同情況進行:
    規(guī)則一:如果擬舍棄數(shù)字的最左一位數(shù)字小于5時，則舍去，即保留的各位數(shù)字不變。例1:將10.349修約到一位小數(shù)，得10.3。
    規(guī)則二:如果擬舍棄數(shù)字的最左一位數(shù)字大于5，則進一，即保留的末位數(shù)字加1。例2:將1260修約到百數(shù)位，得1300(或13×102)。
    規(guī)則三:如果擬舍棄數(shù)字的最左一位數(shù)字為5，且其后有非0數(shù)字時進一，即保留的末位數(shù)字加1。例3:將10.501修約到個數(shù)位，得11。
    規(guī)則四:如果擬舍棄數(shù)字的最左一位數(shù)字為5，而右面無數(shù)字或皆為0時，若所保留的末位數(shù)字為奇數(shù)(1，3，5，7，9)則進一，為偶數(shù)(2，4，6，8，0)則舍棄。
    例4:修約間隔為0.1(或10-1)(見表1)
   

<CTSM>    表1</CTSM>
    規(guī)則五:負數(shù)修約時，先將它的絕對值按規(guī)則一至四進行修約，然后在所得值前面加上負號。
    例5:修約到十數(shù)位(見表2)
   

<CTSM>    表2</CTSM>
    規(guī)則六:GB/T8170-2008的修約規(guī)則中，還有0.5單位修約與0.2單位修約的情況。
    0.5單位修約:將擬修約數(shù)值乘以2，然后按指定數(shù)位依以上規(guī)則一至四進行修約，所得數(shù)值再除以2。
    0.2單位修約:將擬修約數(shù)值乘以5，然后按指定數(shù)位依以上規(guī)則一至四進行修約，所得數(shù)值再除以5。
   

二、Excel對數(shù)值修約的進舍規(guī)則

    Excel中的取整函數(shù)對數(shù)值的修約只有“四舍五入”的規(guī)則，當擬舍棄數(shù)字的最左一位數(shù)字為5，而右面無數(shù)字或皆為0時，計算結(jié)果就會與GB/T8170-2008修約結(jié)果不同。
    例4:修約間隔為0.1(或10-1)(見表3)
   

<CTSM>    表3</CTSM>
   

三、利用自定義函數(shù)在Excel中實現(xiàn)GB/T8170-2008修約規(guī)則

    Excel的自定義函數(shù)是用VBA來編程的，而Visual Basic語言里面的ROUND()函數(shù)剛好符合上述的修約規(guī)則一至規(guī)則五，不過它只能修約小于等于單位1的修約間隔，而且缺少0.5單位和0.2單位的修約間隔，需要做一些補充。下面我們就用VBA來實現(xiàn)新的符合GB/T8170-2008的函數(shù)ROUNDGB()。
    第一步:選擇菜單“工具→宏→Visual Basic編輯器”命令(或按下鍵盤Alt+F11組合鍵)，打開Visual Basic窗口，我們將在這里自定義函數(shù)。
    第二步:進入Visual Basic窗口后，選擇菜單“插入→模塊”命令，于是得到“模塊1”，在其中輸入如下自定義函數(shù)的代碼:
    Function ROUNDGB(number1 As Double，Optional digits1 As Integer，Optional flag1 As Double)As Double
    ′本程序?qū)崿F(xiàn)ROUNDGB()進行GB/T8170-2008數(shù)值規(guī)則的修約。
    ′參數(shù)說明:
    number1擬進行修約的數(shù)值(必選參數(shù))；
    ′digits1修約的小數(shù)位，(可選參數(shù)，默認為0，即取整)。大于0，為保留多少位小數(shù)；小于0，為保留多少位整數(shù)。如digits1=-3表示number1要修約到千數(shù)位。
    ′flag1修約間隔(可選參數(shù)，默認為1)；此參數(shù)有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)′調(diào)用VB的Round函數(shù)
    Else
    digits2=-digits1
    roundgb=Round(number1*10^digits1)*10^digits2′當digits1為負數(shù)時的處理。
    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
    第三步:函數(shù)自定義完成后，選擇菜單“文件→關(guān)閉并返回到Excel”命令，返回到Excel工作表窗口。這樣我們就可以在Excel里面調(diào)用ROUNDGB()函數(shù)了。
    表4是Excel取整函數(shù)ROUND()和自定義函數(shù)ROUNDGB()取值的比較。
   

<CTSM>    表4</CTSM>
   

四、結(jié)束語

    Excel本身“四舍五入”的數(shù)值修約規(guī)則，已經(jīng)可以滿足大多數(shù)日常工作，但在有明確要求使用GB/T8170修約規(guī)則的計算、測量、統(tǒng)計中，現(xiàn)有的函數(shù)則無法滿足要求。如果單純使用Excel公式的邏輯判斷加數(shù)值小數(shù)計算來實現(xiàn)本功能，由于Excel本身的局限性，數(shù)值計算的準確度不夠，就很容易因為相應(yīng)誤差而導致出錯。本文使用VBA編程的方式來實現(xiàn)GB/T8170-2008數(shù)值修約規(guī)則，相對其他方法來說比較容易實現(xiàn)和方便使用，就算是不懂得VB語言的人員，自定義函數(shù)一經(jīng)輸入以后，每次使用時調(diào)用ROUNDGB()函數(shù)就可以像調(diào)用其他Excel函數(shù)一樣方便。
    作者單位【廣東省汕頭市質(zhì)量計量監(jiān)督檢測所】

好貼，必須要頂頂

不錯的函數(shù)，比我以前用過的要簡潔得多。

謝謝樓主分享啊！！！！

謝謝分享。

厲害厲害！

電腦不太好，學習了

學習了，謝謝

不可能那么簡單，很多特殊情況都沒有考慮。0.10000001，0.999999，修約一下。會出現(xiàn)錯誤。如果不考慮特殊情況，何必用上ＶＢ

A2輸入待修約數(shù)字，B2輸入保留小數(shù)點位數(shù)，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輸入待修約數(shù)值，B2輸入保留小數(shù)點位數(shù)，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函數(shù)進行強制轉(zhuǎn)換即可解決浮點小數(shù)的問題。參見
http://www.bkd208.com/forum.php?mod=viewthread&tid=223427

sunrry 發(fā)表于 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的情況。即末位為偶數(shù)且舍去部分剛好為整5，或小于5的情況即可。實現(xiàn)各種特殊情況的才難點

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

我們力圖通過內(nèi)建函數(shù)解決，這樣一般的人都可以通過電子表格實現(xiàn)使用

wangyiyong7703 發(fā)表于 2023-9-18 19:48
我們力圖通過內(nèi)建函數(shù)解決，這樣一般的人都可以通過電子表格實現(xiàn)使用

函數(shù)解決不了浮點小數(shù)的問題，總會有特例，雖然極小概率碰到

sunrry 發(fā)表于 2023-9-22 14:58
函數(shù)解決不了浮點小數(shù)的問題，總會有特例，雖然極小概率碰到

你說一個浮點問題，看這個公式能否解決。也希望你多提問題，公式進一步改進，多幾個if而已

好貼，必須要頂頂