EXCEL實現自動數值修約

湖南理工職業技術學院 湖南湘潭 411100

摘要：隨著辦公自動化軟件的普及，運用Microsoft Excel處理試驗數據，能極大地提高我們的工作效率，可是，Excel沒有數值修約的內置函數。作者運用Excel現有的一些內置函數構造了四舍六入單進雙舍的數值修約式子。

關鍵詞：EXCEL；四舍六入；數值修約

1 概述

在試驗檢測工作中對數據處理采取四舍六入單進雙舍的數值修約方法，與常見的四舍五入法有一定差別。Excel中的ROUND函數對數值修約采取的是四舍五入法。所以常常會出現直接用ROUND函數對數值修約的結果與人工數值修約的結果不一致的現象。隨著試驗檢測工作的發展，為了提高工作效率必須更多地借助于計算機進行數據處理。要使Excel能夠更好地為數據處理服務就必須解決好ROUND函數不能夠四舍六入單進雙舍的數值修約問題。

但是值得注意的是：在使用Excel進行數據處理時需要注意的是：在Excel中太大和太小的數據都無法正常顯示，Excel 2000可以表示的數值在-1×10^307與1×10^307之間，若超出了上述范圍就會出現#NUM！錯誤。Excel 2003中，單元格中可鍵入的最大數值：9.99999999999999E+307，最大正數：1.79769313486231E+308，最小負數：-2.2251E-308，最小正數：2.229E-308 最大負數：-2.2250738585073E-308，若超出了上述范圍就會出現#NUM！錯誤。

國內的各種期刊曾多次介紹如何利用Excel自帶公式來實現自動四舍六入單進雙舍的數值修約方法。但筆者認為都比較復雜，不便于使用。下面筆者介紹一種非常簡單但又科學有效地利用Excel公式來實現四舍六入單進雙舍的數值修約四舍六人五化偶的方法。

2 數值修約規則

通過省略原數值的最后若干位數字，調整所保留的末位數字，使最后得到的值最接近原數值的過程稱為“數值修約”。[1]，它所遵循的規則稱為“數字修約規”。

根據《數值修約規則與極限數值的表示和判定（GB/T8170-2008）》中的規定，數據取用精度位數后一位數字，采用“四舍六入”的方法進行取舍。即取用精度位數后一位數字小于五者則舍，大于五者則入，等于五時若其后有非零尾數仍入，無非零尾數則視取用的末位數字的奇偶取舍，為奇則入，為偶則舍。

具體規則如下：

1.擬舍棄的數字的最左一位數字小于5，則舍去，保留其余各位數不變。例：將12.149 8修約到個位數，得12；將12.149 8修約到一位小數，得12.1

2.擬舍棄的數字的最左一位數字大于5，則進一，即保留數字的末位數字加1。

例：將1268修約到“百”數位，得13×103（特定場合可寫為1300）。

3.擬舍棄的數字的最左一位數字是5時，且其后有非0數字時進一，即保留數字的末位數字加1。

例：將10.5002修約到個數位，11。

4.擬舍棄的數字的左一位數字為5，且其后無數字或皆為0時，若所保留的末位數字為奇數（1，3，5，7，9）則進一，即保留數字的末位數字加1；若保留的末位數字為偶數（0，2，4，6，8）時，則舍去。

例1：修約間隔為0.1（或10-1）

擬修約數值 修約值

1.050 10×10-1（特定場合可寫成為1.0）

0.35 4×10-1（特定場合可寫成為0.4）

例2：修約間隔為1000（或103）

擬修約數值 修約值

2500 2×103（特定場合可寫成為2000）

3500 4×103（特定場合可寫成為4000）

5.擬修約數字應在確定修約或指定修約數位后一次修約獲得結果，不得多次連續修約。[1]

3 為何要進行數值修約

數值修約（俗稱“四舍六入單進雙舍”，“四舍六入逢五奇進偶舍”，“四舍六入五看齊”，“四舍六入，五看奇偶” 或“四舍六入取單雙”等）從理論上講，“四舍六入”將前位數字的奇偶作為參考，使數位為“5”尾數為“0”的進位幾率變為50%，使進位更合理。從數理統計的角度來看，數值修約的規則比“四舍五入”的規則優越。因此數值修約是一種比較精確比較科學的計數保留法。

從統計學的角度分析，“四舍六入單進雙舍”比“四舍五入”要更科學，在進行大量的運算時，它使舍入后結果的誤差平均值趨向于零，而不是像四舍五入那樣逢五就進或者進得多了，那么會使得結果偏大，導致出現誤差積累進而產生系統誤差，數值修約使得量測的結果沒有受舍入影響太大。

例如：1.15+1.25+1.35+1.45=5.2，若按常規的四舍五入取一位小數計算： 1.2+1.3+1.4+1.5=5.4

按數值修約計算，1.2+1.2+1.4+1.4=5.2，舍入后的結果更能反映實際結果。

4 EXCEL編寫自動數值修約式子

4.1數值修約式子的設計思路

從數值修約的規則來看，數值修約與常見的四舍五入的差別主要在保留數后邊（右邊）的一個數字等于5時處理情況不同。自然地，我想到了用改良四舍五入的辦法來編寫數值修約的式子。

以下部分是數值修約與四舍五入的不同之處：

擬舍棄的數字中，保留數后邊（右邊）的一個數字等于5時，5后面的數字全部為0時，數值修約分兩種情況：若保留的末位數字為奇數時，則進一；若保留的末位數字為偶數時，則舍去。

因此，我將擬舍棄的數字四舍五入后，判斷是否保留數后邊（右邊）的一個數字等于5且5后面的數字全部為0，若是則進行舍運算來補償之前的入運算。

數值修約流程圖

4.2數值修約式子結合實例應用及說明

4.2.1.保留兩位小數的自動數值修約

假如在EXCEL中，要進行數值修約的單元格為“A1\"，需要保留兩位小數，則計算公式為：

=ROUND（A1，2）-（MOD（A1*10^3，20）=5）*10^（-2）（保留小數點后二位）

ROUND（A1，2）的解釋：

ROUND（A1，2）是對A1中的數取小數點后二位，且當小數點后第三位遵守四舍五入的規律。這樣的效果就是五入的時候，可能在小數點后第二位奇或偶的時候進了一位，而小數點后第二位為偶數的時候進了一位是不正確的，有必要減去這個0.01。

1.0149 經round（A1，2）處理后為1.01；經數值修約后為1.01。

1.0249 經round（A1，2）處理后為1.02；經數值修約后為1.02。

1.0150 經round（A1，2）處理后為1.02；經數值修約后為1.02。

1.0250 經round（A1，2）處理后為1.03；經數值修約后為1.02。

1.0151 經round（A1，2）處理后為1.02；經數值修約后為1.02。

1.0252 經round（A1，2）處理后為1.03；經數值修約后為1.03。

1.0348 經round（A1，2）處理后為1.03；經數值修約后為1.03。

1.0349 經round（A1，2）處理后為1.03；經數值修約后為1.03。

1.0350 經round（A1，2）處理后為1.04；經數值修約后為1.04。