在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)确說(sδ‌$'huō)明(míng)“試驗結果在運算(suàn)過程中，可( ↕kě)比規定的(de)有(yǒu)效數(shù)字多(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)件(•<jià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ōnα←g)用(yòng)軟件(jiàn)應用(yòng)₽π∏于醫(yī)藥工(gōng)業(yè)中時(shí)，其自("≤zì)身(shēn)的(de)四舍五入修約規則與中國(guó)藥典中所規定的(§₩♣de)四舍六入五成雙修約規則有(yǒu)所出入，不(bù÷♠$)能(néng)完全符合中國(guó)藥典的(de)要(yào)求。

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

我們知(zhī)道(dào)在Excel中可(kě₽∑←)以運用(yòng)ROUND函數(sh¥©‌↔ù)按指定的(de)位數(shù)對(duì)數(sh∑↕≠ù)值進行(xíng)四舍五入，ROUNDDOWσ×N函數(shù)按指定的(de)位數(sh★ ✔≥ù)向下(xià)舍入數(shù)字，ROUNDUP函數(shσ★ù)按指定的(de)位數(shù)向上(shàng)舍入數(shù)字。

因此可(kě)以通(tōng)過将其與IF等函數(s✘'hù)進行(xíng)嵌套實現(xiàn)四舍六入五成雙的₩π×β(de)函數(shù)算(suàn)法，一(yī)個(gè)較≈α為(wèi)簡潔的(de)公式是(shì‍"‍)：=IF(MOD(ABS(X*POWER(λ♥←γ10, Y)),2)=0.5,ROUNDDOW'×↕≥N(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)整數(‌&shù)位。

下(xià)面進行(xíng)解釋，POWER（10，Y）函£≠☆≠數(shù)表示進行(xíng)10的(de)Y次方乘幂運算(suàβγ&<n)，ABS函數(shù)返回給定數(sh'€ù)值（乘幂運算(suàn)結果）的(de)絕對(duì)值，MOD函數(sλΩλ∑hù)返回兩數(shù)（上(shàng)述絕對(duì)值與'≠§2）相(xiàng)除的(de)餘數(shù)，如(φ>¶rú)果餘數(shù)是(shì)0.5（說(shuō)明(mín↕∑♦→g)被修約數(shù)值的(de)尾數(shù)等于五，且其前面的(×↕de)數(shù)是(shì)偶數(shù)£↕₽₽）則返回ROUNDDOWN(X,Y)，即将待修約數(☆¶shù)值X按Y保留位數(shù)向下(xià)舍入；如(δ♠rú)果餘數(shù)不(bù)是(shì)0.₽±€₽5則返回ROUND(X,Y)，即将待修約數(s↕φ£'hù)值X按Y保留位數(shù)進行(xíng)四舍五¥$®入。

以一(yī)組實際數(shù)據為(wèi)例，需要(y÷✘ào)計(jì)算(suàn)樣品兩次pH值測定'β&結果的(de)平均值，保留兩位小(xiǎo)數(shù)✔↔≤ ，直接運用(yòng)AVERAGE函數(shù)即可(kě)，Ex  cel默認的(de)四舍五入修約規則得(de)到(d<≤ &ào)的(de)結果如(rú)下(xià)圖所示：

而套用(yòng)上(shàng)述四舍六入五成雙函"π✔數(shù)公式，以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ì)算(suàn)公式♣↑大(dà)為(wèi)增長(cháng)了(l"→ε‌e)，這(zhè)還(hái)隻是(shì)一(≠≥×yī)個(gè)求平均值的(de)簡單計(jì)算(suàn)，≠ ←₹如(rú)果是(shì)其它更為(wèi)÷✔複雜(zá)的(de)計(jì)算(suàn)，上(shàng)述公式中的(d♠ •e)X還(hái)需要(yào)進行(xíng)更多(duō)的 © €(de)嵌套。那(nà)麽除函數(shù)方法外♥✔>(wài)，還(hái)有(yǒu)沒有(yǒu)其它更加簡潔的(de☆↔¥∑)方法實現(xiàn)四舍六入五成雙修約呢(n∏φe)？

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

Function TranValue(rng As Double,™₹ number As Integer) As Dou₹ ble

TranValue = Round(rng, numb→​€er)

End Function

函數(shù)編寫完，就(jiù)可(kě)以在Exceγ↑¥l工(gōng)作(zuò)表中引用(yòng)TranValue（X✔β↕,Y）該函數(shù)。其中，X為(wèi)待修約的γπ∏♠(de)原始數(shù)值；Y為(wèi)保留位數(s ×hù)。

輸入一(yī)組數(shù)據，以E3單元格為© →€(wèi)例，X為(wèi)AVERAGE（C3δ​↔↕:D3）,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è)C$≥×↓Dec轉換函數(shù)，将雙精度浮點型數(s•¶←♦hù)據轉換為(wèi)十進制(zhì)型，修改後的(de)VB'↓A代碼如(rú)下(xià)圖所示：

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

小(xiǎo)編此番講解希望對(duì)制(zhì)藥同仁們有ε☆÷ (yǒu)所啓示與幫助，畢竟數(shù)≥→≠λ據修約不(bù)當會(huì)影(yǐng)響到(★♣πdào)産品質量數(shù)據與極限數(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)彌補Exc∑‍el在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)便利。