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.bkd208.com/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而已

好貼，必須要頂頂