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

數值修約規則是指在進行具體的(de)數字運算(suàn)前或計算(suàn)出結果後，通(tōng)過省略原數值的(de)最後若幹位數字，調整保留的(de)末位數字，使最後所得(de)到的(de)值最接近原數值的(de)過程。指導數字修約的(de)具體規則被稱爲數值修約規則。

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

目前廣泛使用(yòng)的(de)數值修約規則主要有四舍五入和(hé)四舍六入五成雙。

四舍五入往往是人(rén)們習(xí)慣采用(yòng)的(de)一種數值修約規則，其具體使用(yòng)方法是：在需要保留數字的(de)位次後一位，逢四及以下(xià)就舍，逢五及以上就進。Excel這(zhè)款廣泛使用(yòng)的(de)軟件在處理(lǐ)數據時(shí)默認采用(yòng)的(de)就是四舍五入修約規則。

當然四舍五入修約規則，逢五就進，必定會造成結果的(de)系統性偏高(gāo)，誤差偏大(dà)。爲了(le)避免這(zhè)樣的(de)狀況出現，盡量減小因修約而産生的(de)誤差，在醫藥工業等科技領域中，測定和(hé)計算(suàn)各種數值時(shí)就需要用(yòng)到更爲科學的(de)修約規則。

《中國藥典》2015年版四部凡例中明(míng)确說明(míng)“試驗結果在運算(suàn)過程中，可(kě)比規定的(de)有效數字多(duō)保留一位，而後根據有效數字的(de)修約規則進舍至規定有效位。計算(suàn)所得(de)的(de)最後數值或測定讀數值均可(kě)按修約規則進舍至規定的(de)有效位，取此數值與标準中規定的(de)限度數值比較，以判斷是否符合規定的(de)限度。”這(zhè)裏的(de)修約規則遵循中國國家标準文件GB/T 8170—2008《數值修約規則與極限數值的(de)表示和(hé)判定》，即四舍六入五成雙。

四舍六入五成雙修約規則與四舍五入修約規則有些許差異，當被修約的(de)數字小于或等于四時(shí)，就直接舍去；當被修約的(de)數字大(dà)于或等于六時(shí)，則向前進一位；當被修約的(de)數字等于五時(shí)，需要看五前面的(de)數字，如果是偶數就将五舍去，如果是奇數則進一位，即修約後末尾數都爲偶數，而當五的(de)後面還(hái)有不爲零的(de)任何數時(shí)，則無論五的(de)前面是奇數還(hái)是偶數，均應進位。由此可(kě)見，四舍六入五成雙與四舍五入隻是在被修約的(de)數字等于五，且其前面是偶數時(shí)修約方式不一緻，在其它情況下(xià)修約方式均完全一緻。

所以當Excel這(zhè)款通(tōng)用(yòng)軟件應用(yòng)于醫藥工業中時(shí)，其自身的(de)四舍五入修約規則與中國藥典中所規定的(de)四舍六入五成雙修約規則有所出入，不能完全符合中國藥典的(de)要求。

那麽制藥企業如何讓Excel在日常使用(yòng)中實現四舍六入五成雙修約規則呢(ne)？

我們知道在Excel中可(kě)以運用(yòng)ROUND函數按指定的(de)位數對(duì)數值進行四舍五入，ROUNDDOWN函數按指定的(de)位數向下(xià)舍入數字，ROUNDUP函數按指定的(de)位數向上舍入數字。

因此可(kě)以通(tōng)過将其與IF等函數進行嵌套實現四舍六入五成雙的(de)函數算(suàn)法，一個(gè)較爲簡潔的(de)公式是：=IF(MOD(ABS(X*POWER(10, Y)),2)=0.5,ROUNDDOWN(X,Y),ROUND(X,Y))。其中，X爲待修約的(de)原始數值；Y爲保留位數，可(kě)以爲正值、零和(hé)負值，如+1表示進位到0.1，-2表示進位到100位，0表示進位到整數位。

下(xià)面進行解釋，POWER（10，Y）函數表示進行10的(de)Y次方乘幂運算(suàn)，ABS函數返回給定數值（乘幂運算(suàn)結果）的(de)絕對(duì)值，MOD函數返回兩數（上述絕對(duì)值與2）相除的(de)餘數，如果餘數是0.5（說明(míng)被修約數值的(de)尾數等于五，且其前面的(de)數是偶數）則返回ROUNDDOWN(X,Y)，即将待修約數值X按Y保留位數向下(xià)舍入；如果餘數不是0.5則返回ROUND(X,Y)，即将待修約數值X按Y保留位數進行四舍五入。

以一組實際數據爲例，需要計算(suàn)樣品兩次pH值測定結果的(de)平均值，保留兩位小數，直接運用(yòng)AVERAGE函數即可(kě)，Excel默認的(de)四舍五入修約規則得(de)到的(de)結果如下(xià)圖所示：

而套用(yòng)上述四舍六入五成雙函數公式，以E3單元格爲例，将X替換爲AVERAGE（C3:D3）,将Y替換爲2，其它單元格以此類推，得(de)到四舍六入五成雙修約結果如下(xià)圖所示：

我們發現兩張圖中E4單元格計算(suàn)結果不一樣，因爲5.56和(hé)5.57的(de)平均值5.565取兩位小數如按四舍五入修約得(de)5.57，而依四舍六入五成雙修約得(de)5.56。

當然我們也(yě)發現計算(suàn)公式大(dà)爲增長(cháng)了(le)，這(zhè)還(hái)隻是一個(gè)求平均值的(de)簡單計算(suàn)，如果是其它更爲複雜(zá)的(de)計算(suàn)，上述公式中的(de)X還(hái)需要進行更多(duō)的(de)嵌套。那麽除函數方法外，還(hái)有沒有其它更加簡潔的(de)方法實現四舍六入五成雙修約呢(ne)？

答(dá)案是肯定有的(de)，一種常見的(de)利用(yòng)VBA編寫自定義函數的(de)方式：

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

TranValue = Round(rng, number)

End Function

函數編寫完，就可(kě)以在Excel工作表中引用(yòng)TranValue（X,Y）該函數。其中，X爲待修約的(de)原始數值；Y爲保留位數。

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

仔細觀察，E3單元格計算(suàn)結果0.545按四舍六入五成雙修約應爲0.54，爲什(shén)麽是0.55呢(ne)？但E5單元格計算(suàn)結果0.565修約爲0.56又是正确的(de)，這(zhè)又是什(shén)麽原因呢(ne)？

不難看出，Excel中這(zhè)種常見的(de)運用(yòng)宏命令實現四舍六入五成雙修約的(de)方法是存在缺陷的(de)。因爲計算(suàn)機是基于二進制的(de)，我們需要在代碼中增加一個(gè)CDec轉換函數，将雙精度浮點型數據轉換爲十進制型，修改後的(de)VBA代碼如下(xià)圖所示：

這(zhè)樣在輸入同樣的(de)數據後，四舍六入五成雙修約結果才完全正确，如下(xià)圖所示：

小編此番講解希望對(duì)制藥同仁們有所啓示與幫助，畢竟數據修約不當會影(yǐng)響到産品質量數據與極限數值的(de)判定。

利用(yòng)Excel能爲提高(gāo)工作效率帶來(lái)立竿見影(yǐng)的(de)效果，投入成本低，适應性強。實驗室的(de)信息化(huà)與自動化(huà)可(kě)以從Excel開始。eInfotree軟件能彌補Excel在GxP領域合規性上的(de)差距，實現Excel的(de)用(yòng)戶訪問控制，審計追蹤，電子簽名，完全符合CFR21 Part11的(de)要求，讓制藥企業無顧慮地享用(yòng)電子表格帶來(lái)的(de)便利。