在Excel中實現四舍六入五成雙修約的幾種方法及問題

數值修約規則是指在進行具體的數字運算前或計算出結果后,通過省略原數值的最后若干位數字,調整保留的末位數字,使最后所得到的值最接近原數值的過程。指導數字修約的具體規則被稱為數值修約規則。

進行數值修約時應首先確定“修約間隔”(修約值的最小數值單位)和“進舍規則”。一經確定,修約值即為“修約間隔”的整數倍。然后指定表達方式,即選擇根據“修約間隔”保留到指定位數。最后我們需要注意的是應該一步到位修約,而不允許連續修約。

目前廣泛使用的數值修約規則主要有四舍五入和四舍六入五成雙。

四舍五入往往是人們習慣采用的一種數值修約規則,其具體使用方法是:在需要保留數字的位次后一位,逢四及以下就舍,逢五及以上就進。Excel這款廣泛使用的軟件在處理數據時默認采用的就是四舍五入修約規則。

當然四舍五入修約規則,逢五就進,必定會造成結果的系統性偏高,誤差偏大。為了避免這樣的狀況出現,盡量減小因修約而產生的誤差,在醫藥工業等科技領域中,測定和計算各種數值時就需要用到更為科學的修約規則。

《中國藥典》2015年版四部凡例中明確說明“試驗結果在運算過程中,可比規定的有效數字多保留一位,而后根據有效數字的修約規則進舍至規定有效位。計算所得的最后數值或測定讀數值均可按修約規則進舍至規定的有效位,取此數值與標準中規定的限度數值比較,以判斷是否符合規定的限度。”這里的修約規則遵循中國國家標準文件GB/T 8170—2008《數值修約規則與極限數值的表示和判定》,即四舍六入五成雙。

四舍六入五成雙修約規則與四舍五入修約規則有些許差異,當被修約的數字小于或等于四時,就直接舍去;當被修約的數字大于或等于六時,則向前進一位;當被修約的數字等于五時,需要看五前面的數字,如果是偶數就將五舍去,如果是奇數則進一位,即修約后末尾數都為偶數,而當五的后面還有不為零的任何數時,則無論五的前面是奇數還是偶數,均應進位。由此可見,四舍六入五成雙與四舍五入只是在被修約的數字等于五,且其前面是偶數時修約方式不一致,在其它情況下修約方式均完全一致。

所以當Excel這款通用軟件應用于醫藥工業中時,其自身的四舍五入修約規則與中國藥典中所規定的四舍六入五成雙修約規則有所出入,不能完全符合中國藥典的要求。

那么制藥企業如何讓Excel在日常使用中實現四舍六入五成雙修約規則呢?

我們知道在Excel中可以運用ROUND函數按指定的位數對數值進行四舍五入,ROUNDDOWN函數按指定的位數向下舍入數字,ROUNDUP函數按指定的位數向上舍入數字。

因此可以通過將其與IF等函數進行嵌套實現四舍六入五成雙的函數算法,一個較為簡潔的公式是:=IF(MOD(ABS(X*POWER(10, Y)),2)=0.5,ROUNDDOWN(X,Y),ROUND(X,Y))。其中,X為待修約的原始數值;Y為保留位數,可以為正值、零和負值,如+1表示進位到0.1,-2表示進位到100位,0表示進位到整數位。

下面進行解釋,POWER(10,Y)函數表示進行10的Y次方乘冪運算,ABS函數返回給定數值(乘冪運算結果)的絕對值,MOD函數返回兩數(上述絕對值與2)相除的余數,如果余數是0.5(說明被修約數值的尾數等于五,且其前面的數是偶數)則返回ROUNDDOWN(X,Y),即將待修約數值X按Y保留位數向下舍入;如果余數不是0.5則返回ROUND(X,Y),即將待修約數值X按Y保留位數進行四舍五入。

以一組實際數據為例,需要計算樣品兩次pH值測定結果的平均值,保留兩位小數,直接運用AVERAGE函數即可,Excel默認的四舍五入修約規則得到的結果如下圖所示:

在Excel中實現四舍六入五成雙修約的幾種方法及問題

而套用上述四舍六入五成雙函數公式,以E3單元格為例,將X替換為AVERAGE(C3:D3),將Y替換為2,其它單元格以此類推,得到四舍六入五成雙修約結果如下圖所示:

在Excel中實現四舍六入五成雙修約的幾種方法及問題

我們發現兩張圖中E4單元格計算結果不一樣,因為5.56和5.57的平均值5.565取兩位小數如按四舍五入修約得5.57,而依四舍六入五成雙修約得5.56。

當然我們也發現計算公式大為增長了,這還只是一個求平均值的簡單計算,如果是其它更為復雜的計算,上述公式中的X還需要進行更多的嵌套。那么除函數方法外,還有沒有其它更加簡潔的方法實現四舍六入五成雙修約呢?

答案是肯定有的,一種常見的利用VBA編寫自定義函數的方式:

Function TranValue(rng As Double, number As Integer) As Double

TranValue = Round(rng, number)

End Function

函數編寫完,就可以在Excel工作表中引用TranValue(X,Y)該函數。其中,X為待修約的原始數值;Y為保留位數。

輸入一組數據,以E3單元格為例,X為AVERAGE(C3:D3),Y為2,其它單元格以此類推,得到四舍六入五成雙修約結果如下圖所示:

在Excel中實現四舍六入五成雙修約的幾種方法及問題

仔細觀察,E3單元格計算結果0.545按四舍六入五成雙修約應為0.54,為什么是0.55呢?但E5單元格計算結果0.565修約為0.56又是正確的,這又是什么原因呢?

不難看出,Excel中這種常見的運用宏命令實現四舍六入五成雙修約的方法是存在缺陷的。因為計算機是基于二進制的,我們需要在代碼中增加一個CDec轉換函數,將雙精度浮點型數據轉換為十進制型,修改后的VBA代碼如下圖所示:

在Excel中實現四舍六入五成雙修約的幾種方法及問題

這樣在輸入同樣的數據后,四舍六入五成雙修約結果才完全正確,如下圖所示:

在Excel中實現四舍六入五成雙修約的幾種方法及問題

小編此番講解希望對制藥同仁們有所啟示與幫助,畢竟數據修約不當會影響到產品質量數據與極限數值的判定。

利用Excel能為提高工作效率帶來立竿見影的效果,投入成本低,適應性強。實驗室的信息化與自動化可以從Excel開始。eInfotree軟件能彌補Excel在GxP領域合規性上的差距,實現Excel的用戶訪問控制,審計追蹤,電子簽名,完全符合CFR21 Part11的要求,讓制藥企業無顧慮地享用電子表格帶來的便利。

广东11选5开奖直播