EXCEL實(shí)現(xiàn)自動(dòng)數(shù)值修約

湖南理工職業(yè)技術(shù)學(xué)院 湖南湘潭 411100

摘要：隨著辦公自動(dòng)化軟件的普及，運(yùn)用Microsoft Excel處理試驗(yàn)數(shù)據(jù)，能極大地提高我們的工作效率，可是，Excel沒有數(shù)值修約的內(nèi)置函數(shù)。作者運(yùn)用Excel現(xiàn)有的一些內(nèi)置函數(shù)構(gòu)造了四舍六入單進(jìn)雙舍的數(shù)值修約式子。

關(guān)鍵詞：EXCEL；四舍六入；數(shù)值修約

1 概述

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

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

國(guó)內(nèi)的各種期刊曾多次介紹如何利用Excel自帶公式來實(shí)現(xiàn)自動(dòng)四舍六入單進(jìn)雙舍的數(shù)值修約方法。但筆者認(rèn)為都比較復(fù)雜，不便于使用。下面筆者介紹一種非常簡(jiǎn)單但又科學(xué)有效地利用Excel公式來實(shí)現(xiàn)四舍六入單進(jìn)雙舍的數(shù)值修約四舍六人五化偶的方法。

2 數(shù)值修約規(guī)則

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

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

具體規(guī)則如下：

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

2.擬舍棄的數(shù)字的最左一位數(shù)字大于5，則進(jìn)一，即保留數(shù)字的末位數(shù)字加1。

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

3.擬舍棄的數(shù)字的最左一位數(shù)字是5時(shí)，且其后有非0數(shù)字時(shí)進(jìn)一，即保留數(shù)字的末位數(shù)字加1。

例：將10.5002修約到個(gè)數(shù)位，11。

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

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

擬修約數(shù)值 修約值

1.050 10×10-1（特定場(chǎng)合可寫成為1.0）

0.35 4×10-1（特定場(chǎng)合可寫成為0.4）

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

擬修約數(shù)值 修約值

2500 2×103（特定場(chǎng)合可寫成為2000）

3500 4×103（特定場(chǎng)合可寫成為4000）

5.擬修約數(shù)字應(yīng)在確定修約或指定修約數(shù)位后一次修約獲得結(jié)果，不得多次連續(xù)修約。[1]

3 為何要進(jìn)行數(shù)值修約

數(shù)值修約（俗稱“四舍六入單進(jìn)雙舍”，“四舍六入逢五奇進(jìn)偶舍”，“四舍六入五看齊”，“四舍六入，五看奇偶” 或“四舍六入取單雙”等）從理論上講，“四舍六入”將前位數(shù)字的奇偶作為參考，使數(shù)位為“5”尾數(shù)為“0”的進(jìn)位幾率變?yōu)?0%，使進(jìn)位更合理。從數(shù)理統(tǒng)計(jì)的角度來看，數(shù)值修約的規(guī)則比“四舍五入”的規(guī)則優(yōu)越。因此數(shù)值修約是一種比較精確比較科學(xué)的計(jì)數(shù)保留法。

從統(tǒng)計(jì)學(xué)的角度分析，“四舍六入單進(jìn)雙舍”比“四舍五入”要更科學(xué)，在進(jìn)行大量的運(yùn)算時(shí)，它使舍入后結(jié)果的誤差平均值趨向于零，而不是像四舍五入那樣逢五就進(jìn)或者進(jìn)得多了，那么會(huì)使得結(jié)果偏大，導(dǎo)致出現(xiàn)誤差積累進(jìn)而產(chǎn)生系統(tǒng)誤差，數(shù)值修約使得量測(cè)的結(jié)果沒有受舍入影響太大。

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

按數(shù)值修約計(jì)算，1.2+1.2+1.4+1.4=5.2，舍入后的結(jié)果更能反映實(shí)際結(jié)果。

4 EXCEL編寫自動(dòng)數(shù)值修約式子

4.1數(shù)值修約式子的設(shè)計(jì)思路

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

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

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

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

數(shù)值修約流程圖

4.2數(shù)值修約式子結(jié)合實(shí)例應(yīng)用及說明

4.2.1.保留兩位小數(shù)的自動(dòng)數(shù)值修約

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

=ROUND（A1，2）-（MOD（A1*10^3，20）=5）*10^（-2）（保留小數(shù)點(diǎn)后二位）

ROUND（A1，2）的解釋：

ROUND（A1，2）是對(duì)A1中的數(shù)取小數(shù)點(diǎn)后二位，且當(dāng)小數(shù)點(diǎn)后第三位遵守四舍五入的規(guī)律。這樣的效果就是五入的時(shí)候，可能在小數(shù)點(diǎn)后第二位奇或偶的時(shí)候進(jìn)了一位，而小數(shù)點(diǎn)后第二位為偶數(shù)的時(shí)候進(jìn)了一位是不正確的，有必要減去這個(gè)0.01。

1.0149 經(jīng)round（A1，2）處理后為1.01；經(jīng)數(shù)值修約后為1.01。

1.0249 經(jīng)round（A1，2）處理后為1.02；經(jīng)數(shù)值修約后為1.02。

1.0150 經(jīng)round（A1，2）處理后為1.02；經(jīng)數(shù)值修約后為1.02。

1.0250 經(jīng)round（A1，2）處理后為1.03；經(jīng)數(shù)值修約后為1.02。

1.0151 經(jīng)round（A1，2）處理后為1.02；經(jīng)數(shù)值修約后為1.02。

1.0252 經(jīng)round（A1，2）處理后為1.03；經(jīng)數(shù)值修約后為1.03。

1.0348 經(jīng)round（A1，2）處理后為1.03；經(jīng)數(shù)值修約后為1.03。

1.0349 經(jīng)round（A1，2）處理后為1.03；經(jīng)數(shù)值修約后為1.03。

1.0350 經(jīng)round（A1，2）處理后為1.04；經(jīng)數(shù)值修約后為1.04。