在Excel中實現(xiàn)四舍六入五成雙λ€​修約的(de)幾種方法及問(wèn)題

數(shù)值修約規則是(shì)指在進行(xíng)具體(tǐ ±)的(de)數(shù)字運算(suàn)前或計(jì)算(suàn)‌♣出結果後，通(tōng)過省略原數(shù)值≈→γ的(de)最後若幹位數(shù)字，調整保₩★™留的(de)末位數(shù)字，使最後所得(de)到(dào≈&♦)的(de)值最接近(jìn)原數(shù)值的(de)過程。指<✔導數(shù)字修約的(de)具體(tǐ)規則被稱為(wèi€&>)數(shù)值修約規則。

進行(xíng)數(shù)值修約時(shí)應首先确定“修≈σ‍約間(jiān)隔”（修約值的(de)最小(xiǎo)↑•數(shù)值單位）和(hé)“進舍規則”。一(σ↑yī)經确定，修約值即為(wèi)“修約間€∏(jiān)隔”的(de)整數(shù)倍。然後指定表達方式，即選擇根據“修約間(jiā§→↑n)隔”保留到(dào)指定位數(shù)¶γ$。最後我們需要(yào)注意的(de)是(shì)應該一(yī)步到(Ω↑×dào)位修約，而不(bù)允許連續修約。←<

目前廣泛使用(yòng)的(de)數(shù)值修約規則主要(δ ♠yào)有(yǒu)四舍五入和(hé)四舍六入五成雙Ωλ↑☆。

四舍五入往往是(shì)人(rén)們習(xí±✘₩₽)慣采用(yòng)的(de)一(yī)種數(shù)值修約規則，其具體(t₽ ®↑ǐ)使用(yòng)方法是(shì)：在ε€≈₹需要(yào)保留數(shù)字的(de)位次後一(yī)位，≈‌&​逢四及以下(xià)就(jiù)舍，逢五及以上(shà ‌σng)就(jiù)進。Excel這(zhè)款廣≤π∞→泛使用(yòng)的(de)軟件(jiàn)在處理(lǐδ"↓)數(shù)據時(shí)默認采用(yòng)的→₹≤®(de)就(jiù)是(shì)四舍五入修約規則∞÷。

當然四舍五入修約規則，逢五就(jiù)進，必定會(huì>​)造成結果的(de)系統性偏高(gāo)，誤差偏大(dà)。為(wèi)了•≥♠ (le)避免這(zhè)樣的(de)狀況出現(xiàn)，盡量減 ¥小(xiǎo)因修約而産生(shēng)的(de)誤差，在醫(÷ ¥★yī)藥工(gōng)業(yè)等科(kē)技(jì)領域中，測定和☆→(hé)計(jì)算(suàn)各種數(shù)值時(shí)就(ji→γ  ù)需要(yào)用(yòng)到(dào)£✘±×更為(wèi)科(kē)學的(de)修約規則。

《中國(guó)藥典》2015年(nián)版四部÷  ®凡例中明(míng)确說(shuō)明(míng)“試驗β±>結果在運算(suàn)過程中，可(kě)比規定的(de)有(yǒu)效數(sγ✔hù)字多(duō)保留一(yī)位，而後根據有←'§(yǒu)效數(shù)字的(de)修約規則進舍至規定有 ¥×(yǒu)效位。計(jì)算(suàn)所得(de©✘↔‌)的(de)最後數(shù)值或測定讀(dú)數(shù)值均可(kě)按修'•約規則進舍至規定的(de)有(yǒu)效位，取此數(shù↔≤ ≠)值與标準中規定的(de)限度數(shù)值比較，以判斷是(shì)否符合規定 ∏的(de)限度。”這(zhè)裡(lǐ)的(de)修約規則遵循中國(gu≈ ≥πó)國(guó)家(jiā)标準文(wén)件(j‌¥εiàn)GB/T 8170—2008《數(shù)值修約規則π♠與極限數(shù)值的(de)表示和(hé)判定》，即四ε>™舍六入五成雙。

四舍六入五成雙修約規則與四舍五入修約規則有(yǒu)些(xiē)許差異∑₹，當被修約的(de)數(shù)字小(xiǎo)于或'φ↔×等于四時(shí)，就(jiù)直接舍去(qù)；當被修約δ→$ 的(de)數(shù)字大(dà)于或等于六時(shí)，則向前進一(yī✔<≥)位；當被修約的(de)數(shù)字等于五時(shí)，需要(yào)看₩♥δ✔(kàn)五前面的(de)數(shù)字，如(rú)果是(sh® ♦™ì)偶數(shù)就(jiù)将五舍去(qù)，如(rú)δ♠果是(shì)奇數(shù)則進一(yī)位，即修約後末尾數(shù)都(☆ dōu)為(wèi)偶數(shù)，而當五的(de)後面還(★$∞hái)有(yǒu)不(bù)為(wèi)零的(de)↑ 任何數(shù)時(shí)，則無論五的(de)前面是(sh•®∏ì)奇數(shù)還(hái)是(shì)偶數(shù)，均應進位。由此可(¶₩∏kě)見(jiàn)，四舍六入五成雙與四<∞ 舍五入隻是(shì)在被修約的(de)數(shù)字等于五‌∑∞ ，且其前面是(shì)偶數(shù)時(shí)修約方式不(bù)一(yī)緻♠σ，在其它情況下(xià)修約方式均完全一π©(yī)緻。

所以當Excel這(zhè)款通(tōng)用(yòn♠→←πg)軟件(jiàn)應用(yòng)于醫(yī)藥工(gōng)業(yè✔§)中時(shí)，其自(zì)身(shēn)的(d×→☆↕e)四舍五入修約規則與中國(guó)藥典中所規定的(de)四↔φ舍六入五成雙修約規則有(yǒu)所出入，不(bù)能(néng)完全←'符合中國(guó)藥典的(de)要(yào)求。

那(nà)麽制(zhì)藥企業(yè)如(rú)何讓Exce ‍l在日(rì)常使用(yòng)中實現(xiàn)四舍六入五成↕'♥ 雙修約規則呢(ne)？

我們知(zhī)道(dào)在Excel中可(kě)以運用(y♠±↓òng)ROUND函數(shù)按指定的(de)位數(shù)對(duì₽‍≠)數(shù)值進行(xíng)四舍五入，ROUNDDOWN函<™≈數(shù)按指定的(de)位數(shù)向下​α(xià)舍入數(shù)字，ROUNDUP函數(sΩ↔hù)按指定的(de)位數(shù)向上(∏γ‌shàng)舍入數(shù)字。

因此可(kě)以通(tōng)過将其與IF等函數(shù)進行÷₽→®(xíng)嵌套實現(xiàn)四舍六入五成  雙的(de)函數(shù)算(suàn)法，一(yī)個(gè)較為(wèi•λ≠)簡潔的(de)公式是(shì)：=IF(MOD(ABS(X*POWER(10, Y)),2)=0.§'5,ROUNDDOWN(X,Y),ROUND(X,Y))。其中，X為(wèi)待修約的(de)原始數(shù)值；Y為​✘ β(wèi)保留位數(shù)，可(kě)以為(wèi)正值、零和(h✔"'™é)負值，如(rú)+1表示進位到(dào)0.1，-2表示進位到(dào)ε'§₩100位，0表示進位到(dào)整數(s>☆"hù)位。

下(xià)面進行(xíng)解釋，POWER（10，Y）函數(s' §✘hù)表示進行(xíng)10的(de)Y次方乘幂運算 ♣"(suàn)，ABS函數(shù)返回給定數(shù)值（乘幂運算(suà≤₩n)結果）的(de)絕對(duì)值，MOD函數(sh‍¥™ù)返回兩數(shù)（上(shàng)述絕對(duì)值與2）相(xi ©αàng)除的(de)餘數(shù)，如(rú)果餘數(shù)是(shì)σ™↕0.5（說(shuō)明(míng)被修約數(shù)值的δ÷ (de)尾數(shù)等于五，且其前面的(de)數(≥α♦shù)是(shì)偶數(shù)）則返回ROUNDDOWN(X,★♥↑Y)，即将待修約數(shù)值X按Y保留位數(shù)向下(xià)舍入；↑&↑&如(rú)果餘數(shù)不(bù)是(shì)0.5則返回ROUND♠♥•(X,Y)，即将待修約數(shù)值X按Y保留位數(shù)進行(≠​¥∞xíng)四舍五入。

以一(yī)組實際數(shù)據為(wèi)例，需要(yàoקδ)計(jì)算(suàn)樣品兩次pH值測定結果的(δφde)平均值，保留兩位小(xiǎo)數(shù)，直接運用(y±¥∏òng)AVERAGE函數(shù)即可(kě)，σ♦★Excel默認的(de)四舍五入修約規則得(de)到(dào)的(de)↔α>結果如(rú)下(xià)圖所示：

而套用(yòng)上(shàng)述四舍六入五成雙函數(s↑↔hù)公式，以E3單元格為(wèi)例，将↑§<X替換為(wèi)AVERAGE（C3:D3）,将Y替 γ™換為(wèi)2，其它單元格以此類推，得(de)到(✔•  dào)四舍六入五成雙修約結果如(rú)下(xià)圖所示∏₽≤：

我們發現(xiàn)兩張圖中E4單元格計(jì)算δ♣✔(suàn)結果不(bù)一(yī)樣，因為(wèi)5.56和(hé$​‍)5.57的(de)平均值5.565取兩位小(xiǎo)數ε§(shù)如(rú)按四舍五入修約得(de)5.57，而依四舍六入五€ 成雙修約得(de)5.56。

當然我們也(yě)發現(xiàn)計(jì)算(s✔∏uàn)公式大(dà)為(wèi)增長(cháng)了(le)，這(zhè≈≤φ)還(hái)隻是(shì)一(yī)個(gè)求平± ¶₩均值的(de)簡單計(jì)算(suàn)，如₩'(rú)果是(shì)其它更為(wèi)複雜(zá)的(dλ₽✔>e)計(jì)算(suàn)，上(shàng)述公式中的(₩"÷ de)X還(hái)需要(yào)進行(xíng)↓ ∑更多(duō)的(de)嵌套。那(nà)麽除函數(shùΩ€₹‍)方法外(wài)，還(hái)有(yǒu)沒有(yǒu)其它₹φ ☆更加簡潔的(de)方法實現(xiàn)四舍六入五成雙修∞★​δ約呢(ne)？

答(dá)案是(shì)肯定有(yǒu)的(de)，一(y♦∞₽ī)種常見(jiàn)的(de)利用(yòng)VBA編寫自(zì)定義×₹函數(shù)的(de)方式：

Function TranValue(rng As Doubl∑★e, number As Integer) As Double

TranValue = Round(rng, number)

End Function

函數(shù)編寫完，就(jiù)可(kě)以在Excel工©₩ ±(gōng)作(zuò)表中引用(yòng)TranVal ✘ue（X,Y）該函數(shù)。其中，X★‌為(wèi)待修約的(de)原始數(sh∑λ✘ù)值；Y為(wèi)保留位數(shù)。

輸入一(yī)組數(shù)據，以E3單元格為(wè≤‌σi)例，X為(wèi)AVERAGE（C3:Dπε↔&3）,Y為(wèi)2，其它單元格以此類推，得(de)到(d £ào)四舍六入五成雙修約結果如(rú)下(xià)圖所示：

仔細觀察，E3單元格計(jì)算(suàn)結果0.545按四舍六  ≈¥入五成雙修約應為(wèi)0.54，為(wèi'₹≠σ)什(shén)麽是(shì)0.55呢(ne)？但(dàn♦™♠)E5單元格計(jì)算(suàn)結果0.565修約© π∑為(wèi)0.56又(yòu)是(shì↔₽∞)正确的(de)，這(zhè)又(yòu)是(shì)什(shén)麽原因✔↔☆呢(ne)？

不(bù)難看(kàn)出，Excel中這(zhè)種常見(jiàn)的(de∞↓★♣)運用(yòng)宏命令實現(xiàn)四舍六入五 ×↕成雙修約的(de)方法是(shì)存在缺陷$↔的(de)。因為(wèi)計(jì)算(suàn)機(jī)是(shì)基Ω ‍于二進制(zhì)的(de)，我們需要(yào)在代®§↕σ碼中增加一(yī)個(gè)CDec轉換函數(shù)，将雙精度浮點型數(s>∏≈hù)據轉換為(wèi)十進制(zhì)型，修改後的(de)VBA代碼如&≠®∑(rú)下(xià)圖所示：

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

小(xiǎo)編此番講解希望對(duì)制(≈₩©zhì)藥同仁們有(yǒu)所啓示與幫助，畢竟數(shù)據修約不(bù)★<當會(huì)影(yǐng)響到(dào)産品質量數(s ®εhù)據與極限數(shù)值的(de)判定。

利用(yòng)Excel能(néng)為(wèi)提高(gāo)工(gōng>↑)作(zuò)效率帶來(lái)立竿見(jiàn)影(yǐng♣✔¶)的(de)效果，投入成本低(dī)，适應性強。實∞$$₩驗室的(de)信息化(huà)與自(zì)動化(α‍♦huà)可(kě)以從(cóng)Excel開(kāi)始。eInfotree軟件(jiàn)能(néngφγ<)彌補Excel在GxP領域合規性上(shàng)的(de)差距，₩ π實現(xiàn)Excel的(de)用(yòng)戶訪¶$₹問(wèn)控制(zhì)，審計(jì)追蹤，電(diàn)子(zǐ)簽名，完>♠全符合CFR21 Part11的(de)要(y€§​ào)求，讓制(zhì)藥企業(yè)無顧慮地(dì←→  )享用(yòng)電(diàn)子(zǐ)表格帶來(lái)λ↓λ↓的(de)便利。