基于Excel 的水文資料“四舍六入”計(jì)算

王 昕

（陜西省西安水文水資源勘測(cè)局，陜西 西安 710100）

0 引言

在水文生產(chǎn)活動(dòng)中會(huì)產(chǎn)生批量的水文要素?cái)?shù)值，對(duì)于位數(shù)很多的數(shù)值，當(dāng)有效位數(shù)和保留小數(shù)確定后，數(shù)值后面的冗余數(shù)字應(yīng)該舍去，僅保留有效數(shù)字最末尾一位。“四舍六入”對(duì)水文資料精度起到重要的保證作用。但在水文測(cè)驗(yàn)和資料整編工作中，用計(jì)算器計(jì)算難免出現(xiàn)誤判致數(shù)值不正確，采用Excel 直接計(jì)算，因“四舍五入”不合規(guī)范要求，只能作為檢核對(duì)照，不能作為正式資料。為此，利用Excel 函數(shù)編寫組合公式制作模板解決“四舍六入”問題，完成水文資料自動(dòng)計(jì)算。

1 “四舍六入”計(jì)算方法

1.1 修約規(guī)則

引用主要規(guī)則[1]如下。

規(guī)則1：擬舍棄數(shù)字的最左一位數(shù)字小于5，則舍去，保留其余各位數(shù)字不變。

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

規(guī)則3：擬舍棄數(shù)字的最左一位數(shù)字是5，且其后有非0 數(shù)字時(shí)進(jìn)1。

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

1.2 “四舍六入”的科學(xué)性

“四舍五入”是一種常規(guī)的數(shù)字修約規(guī)則，在各行業(yè)中應(yīng)用非常普遍。但是，缺點(diǎn)是逢5 就進(jìn)1，在1～9 這列數(shù)字中5 是中位數(shù)，從水文資料整編規(guī)范“符號(hào)檢驗(yàn)”的觀點(diǎn)看，正負(fù)號(hào)沒有分布在標(biāo)準(zhǔn)線兩側(cè)。從數(shù)理統(tǒng)計(jì)學(xué)原理看，屬于單邊分布使修約后的數(shù)學(xué)期望出現(xiàn)系統(tǒng)偏高。

“四舍六入”運(yùn)用偶數(shù)法則，即擬舍數(shù)字位逢5 看前位，為偶數(shù)末位數(shù)字則舍去，為奇數(shù)則進(jìn)1。在批數(shù)據(jù)處理過程中，遇到5 時(shí)，有的數(shù)進(jìn)，有的數(shù)舍。使得由5 引起的進(jìn)舍誤差大抵呈均衡分布，能有效抵消進(jìn)舍誤差，避免“四舍五入”逢5 就進(jìn)的不對(duì)稱分布誤差。因此，“四舍六入”較“四舍五入”修約更科學(xué),使處理得來的數(shù)值更精確、更合理。

1.3 組合公式設(shè)計(jì)思路

“四舍六入”是對(duì)“四舍五入”的一種改進(jìn)，仍包含著“四舍五入”的精髓。從“四舍六入”主要規(guī)則1～規(guī)則4 看出，數(shù)字“5”是靈魂。僅當(dāng)滿足如下條件才舍，即：①擬舍數(shù)字最左位數(shù)字小于5；②第n 位數(shù)字為偶數(shù)；③第n+1 位數(shù)字為5；④第n+1 位數(shù)字為5 的后面皆為0。其它條件仍用“四舍五入”。關(guān)鍵是要做出上述判斷并用公式表示。

設(shè)修約數(shù)值為X,有效位數(shù)為m（m 等于零時(shí)不限定有效位數(shù)），其數(shù)字為0。保留小數(shù)位數(shù)n，其數(shù)字為p；n+1 為擬取舍數(shù)位，數(shù)字為q；n+2 為擬取舍位的后鄰數(shù)位，數(shù)字為r。

設(shè)計(jì)思路見圖1。

圖1 組合公式流程結(jié)構(gòu)圖

1.4 “四舍六入”Tran.round 公式

規(guī)范[2]規(guī)定，作為整編成果的數(shù)據(jù)，采用各種方法推求或插補(bǔ)的各項(xiàng)要素?cái)?shù)據(jù)，均按指定取用精度取位。指定位數(shù)后一位數(shù)字，采用“四舍六入”，并限定有效位數(shù)m 和保留小數(shù)位數(shù)n,見表1。表1 中n 為修約間隔10-n（n 為整數(shù)，n>0，為小數(shù)位，n=0，為個(gè)位，n<0，為十位、百位等）。

表1 水文要素取用精度摘錄表

1.4.1 表達(dá)式

根據(jù)數(shù)學(xué)原理，寫出各項(xiàng)表達(dá)式如下。

X 第n 位數(shù):INT(X*10^n)-10*INT(X*10^(n-1))；

偶數(shù)用MOD 函數(shù)表示：MOD (INT(X*10^n)-10*INT(X*10^(n-1)),2)=0；

規(guī)則1～規(guī)則4：

Situ1：IF(INT(X*10^(n+1))-10*INT(X*10^n)<5,TRUNC(X,n))；

Situ2：IF(INT(X*10^(n+1))-10*INT(X*10^n)>5,ROUND(X,n))；

Situ3:IF(AND(INT(X*10^(n+1))-10*INT(X*10^n)=5,INT(X*10^(n+2))-10*INT(X*10^(n+1)<>0)),round(X,n))；

Situ4:IF(AND(MOD(INT(X*10^n)-10*INT(X*10^(n-1)),2)<>0,INT(X*10^(n+1))-10*INT(X*10^n)=5,INT(X*10^m)-10*INT(X*10^(m-1)=0),ROUND(X,n))；

Situ5:IF(AND(MOD(INT(X*10^n)-10*INT(X*10^(n-1)),2)=0,INT(X*10^(n+1))-10*INT(X*10^n)=5,INT(X*10^m)-10*INT(X*10^(m-1))=0),TRUNC(X,n))。

1.4.2 先驗(yàn)測(cè)試

為了驗(yàn)證situ1 ～situ5 表達(dá)式，隨機(jī)取一組數(shù)據(jù)：0.20501，0.20424，0.21535，0.6894，0.6814 在EXCEL 中進(jìn)行先驗(yàn)測(cè)試，見表2。測(cè)試結(jié)果顯示準(zhǔn)確，說明公式正確無誤。

表2 先驗(yàn)測(cè)試（m=5,n=2）

1.4.3 Tran.round 公式

綜合上述情形，Situ2 與Situ1、Situ5 與Situ4 互為邏輯真和假關(guān)系，經(jīng)過對(duì)Situ1、Situ5 的優(yōu)化并定義為Tran.round 公式[3]：=IF(AND(MOD(INT(X*10^n)-10*INT(X*10^(n-1)),2)=0,INT(X*10^(n+1))-10*INT(X*10^n)=5,INT(X*10^m)-10*INT(X*10^(m-1))=0),TRUNC(X,m,n),ROUND(X,m,n))。

式中：TRUNC (X,m,n)，將數(shù)值X 截為整數(shù)或保留n 位數(shù)的小數(shù)。

ROUND(X,m,n)，將數(shù)值X 進(jìn)行四舍五入，保留n 位數(shù)小數(shù)，其中，n≤m。

本文利用Excel 函數(shù)組合成Tran.round 公式，還可用Excel VBA 編寫自定義函數(shù)來實(shí)現(xiàn)，這里不再贅述。

2 應(yīng)用實(shí)例

2.1 流量計(jì)算模板

采用中小河流輞川站2019 年12 月3 日實(shí)測(cè)流量資料，LS10 型流速儀公式：V=0.1027R/S+0.0373。操作過程:在測(cè)深測(cè)速記載及流量計(jì)算表中用Tran.round 公式，替換各要素的X，m，n 值。僅摘錄部分要素公式結(jié)果，其余略去。

1）水深：小于100 m,不小于5 m,計(jì)至0.1 m,小于5 m,計(jì)至0.01 m。這里X=AVERAGE(H9:H11),n=2。

=IF(AND(INT(AVERAGE(H9:H11)*10^(2+1))-10*I NT(AVERAGE (H9:H11)*10^2)=5,MOD(INT(AVERAGE (H9:H11)*10^2)-10*INT(AVERAGE(H9:H11)*10^(2-1)),2)=0,INT(AVERAGE(H9:H11)*10^(2+1))-10*AVERAGE(H9:H11)*10^(2)=0),TRUNC(AVERAGE (H9:H11),2),ROUND (AVERAGE (H9:H11),2))=0.24 m。

2）測(cè)點(diǎn)流速:不小于l m/s,取三位有效數(shù)字,小于1m/s,取二位有效數(shù)字, 小數(shù)不過三位。這里X=0.1027*R11/Q11+0.0373,m=3,n=2。

=IF(AND(MOD(INT((0.1027*R11/Q11+0.0373)*10^2)-10*INT((0.1027*R11/Q11+0.0373)*10^(2-1)),2)=0,INT((0.1027*R11/Q11+0.0373)*10^(2+1))-10*INT((0.1027*R11/Q11+0.0373)*10^2)=5,INT((0.1027*R11/Q11+0.0373)*10^(3))-10*(0.1027*R11/Q11+0.0373)*10^(3-1)=0),TRUNC((0.1027*R11/Q11+0.0373),2),ROUND((0.1027*R11/Q11+0.0373),2))=0.48 m/s

部分面積: 取三位有效數(shù)字, 小數(shù)不過兩位。這里X=X10*Z10,m=3,n=2。

=IF(AND(INT(X10*Z10*10^(2+1))-10*INT(X10*Z10*10^2)=5,MOD(INT(X10*Z10*10^2)-10*INT(X10*Z10*10^(2-1)),2)=0,INT(X10*Z10*10^(3))-10*X10*Z10*10^(2)=0),TRUNC(X10*Z10,2),ROUND(X10*Z10,2))=0.12 m2

3)斷面面積：這里X=sum(AC10:AC20),m=3,n=2。

=IF(AND(INT(SUM(AC10:AC21)*10^(2+1))-10*INT(SUM(AC10:AC21)*10^2)=5,MOD(INT(SUM(AC10:AC21)*10^2)-10*INT(SUM(AC10:AC21)*10^(2-1)),2)=0,INT(SUM(AC10:AC21)*10^(3))-10*SUM(AC10:AC21)*10^(2)=0),TRUNC(SUM(AC10:AC21),2),ROUND(SUM(AC10:AC21),2))=1.85 m2

4) 測(cè)點(diǎn)流量：取三位有效數(shù)字, 小數(shù)不過三位。這里X=W10*AC10,m=3,n=3。

=IF(AND(MOD(INT(W10*AC10*10^3)-10*INT(W10*AC10*10^(3-1)),2)=0,INT(W10*AC10*10^(3+1))-10*INT(W10*AC10*10^3)=5,INT(W10*AC10*10^(3+1))-10*W10*AC10*10^(3)=0),TRUNC(W10*AC10,3)，ROUND(W10*AC10，3))=0.041 m3/s

5)斷面流量：這里X=SUM(AD10:AD20))，m=3，n=3。

=IF(AND(INT(SUM(AD10:AE21)*10^(3+1))-10*INT(SUM(AD10:AE21)*10^3)=5,MOD(INT(SUM(AD10:AE21)*10^3)-10*INT(SUM(AD10:AE21)*10^(3-1)),2)=0,INT(SUM(AD10:AE21)*10^(4))-10*SUM (AD10:AE21)*10^(3)=0),TRUNC (SUM(AD10:AE21),3),ROUND(SUM(AD10:AE21),3))=0.685 m3/s

6)平均流速：這里X=D38/D39，m=3，n=2。

=IF(AND(INT(D38/D39*10^(2+1))-10*INT(D38/D39*10^2)=5,MOD(INT(D38/D39*10^2)-10*INT(D38/D39*10^(2-1)),2)=0,INT(D38/D39*10^(2+1))-10*D38/D39*10^(2+0)=0),TRUNC(D38/D39,2),ROUND(D38/D39,2))=0.37 m/s

2.2 土壤含水量計(jì)算模板

采用2019 年9 月10 日王曲站土壤墑情監(jiān)測(cè)摘錄資料。

土壤含水量：用百分?jǐn)?shù)表示，記至一位小數(shù)（記至0.1%)。

表3 王曲站土壤墑情監(jiān)測(cè)烘干法計(jì)算表

操作過程：在墑情監(jiān)測(cè)含水量計(jì)算表3 中，在相應(yīng)單元格輸入X，m，n 值。摘錄一部分?jǐn)?shù)據(jù)公式展示X，m，n 替換過程，其余略去。

土壤含水量（%）：百分?jǐn)?shù)表示, 記至一位小數(shù)。這里X=100*C10/C9*100，n=1。

=IF(AND(INT(100*C10/C9*10^(1+1))-10*INT(100*C10/C9*10^1)=5,MOD(INT(100*C10/C9*10^1)-10*INT(100*C10/C9*10^(1-1)),2)=0,INT(100*C10/C9*10^(1+1))-10*100*C10/C9*10^(1+0)=0),TRUNC(100*C10/C9,1),ROUND(100*C10/C9,1))=22.5%

測(cè)點(diǎn)土壤含水量（%）:這里X=AVERAGE(C11:E11)，n=1。

=IF(AND(INT(AVERAGE(C11:E11)*10^(1+1))-10*INT(AVERAGE(C11:E11)*10^1)=5,MOD(INT(AVER AGE(C11:E11)*10^1)-10*INT(AVERAGE(C11:E11)*10^(1-1)),2)=0,INT(AVERAGE(C11:E11)*10^(1+1))-10*AVERAGE(C11:E11)*10^(1+0)=0),TRUNC(AVERAGE(C11:E11),1),ROUND(AVERAGE(C11:E11),1))=22.5%

測(cè)點(diǎn)土壤平均含水量（%）: 這里X=AVERAGE(C12:F12),n=1。

=IF(AND(INT(AVERAGE (C12:F12)*10^(1+1))-10*IN T(AVERAGE(C12:F12)*10^1)=5,MOD(INT(AVERAGE(C12:F12)*10^1)-10*INT(AVERAGE(C12:F12)*10^(1-1)),2)=0,INT(AVERAGE(C12:F12)*10^(1+1))-10*AVERAGE(C12:F12)*10^(1+0)=0),TRUNC(AVERAGE(C12:F12),1),ROUND(AVERAGE(C12:F12),1))=22.8%

垂線平均土壤含水量（%）：這里X=(10*C13+10*F13+20*L13)/40，n=1。

=IF(AND(INT((10*C13+10*F13+20*L13)/40*10^(1+1))-10*INT((10*C13+10*F13+20*L13)/40*10^1)=5,MOD(INT((10*C13+10*F13+20*L13)/40*10^1)-10*INT((10*C13+10*F13+20*L13)/40*10^(1-1)),2)=0,INT((10*C13+10*F13+20*L13)/40*10^(1+1))- 10*(10*C13+10*F13+20*L13)/40*10^(1+0)=0),TRUNC((10*C13+10*F13+20*L13)/40,1),ROUND((10*C13+10*F13+20*L13)/40,1))=22.9%

相對(duì)濕度（%）：這里X=C14/0.234,n=1。

=IF(AND(INT(C14/0.234*10^(1+1))-10*INT(C14/0.234*10^1)=5,MOD(INT(C14/0.234*10^1)-10*INT(C14/0.234*10^(1-1)),2)=0,INT(10*C13+C14/0.234*10^(1+1))-10*C14/0.234*10^(1+0)=0),TRUNC(C14/0.234,1),ROUND(C14/0.234,1))=97.9%

從操作實(shí)例看出，應(yīng)用Tran.round 公式，在Excel 中替換X、m、n 便完成“四舍六入”修約，形成計(jì)算模板。在模板中只需輸入觀測(cè)值或測(cè)量值，便可實(shí)現(xiàn)自動(dòng)計(jì)算，簡(jiǎn)單實(shí)用。

注意：①在替換X、m、n 過程中不能對(duì)公式中的標(biāo)點(diǎn)、括弧有任何改變，否則出錯(cuò)，會(huì)帶來檢查或重新復(fù)制公式的麻煩。②m、n 按給定位數(shù)或視中間運(yùn)算后位數(shù)而定。

3 不準(zhǔn)確結(jié)果分析

從計(jì)算機(jī)及Micorosoft Excel 軟件相關(guān)文獻(xiàn)資料中獲知，浮點(diǎn)運(yùn)算可能會(huì)給出不準(zhǔn)確的結(jié)果[4],用Tran.round 公式進(jìn)行精度取舍有時(shí)會(huì)出現(xiàn)偏差。原因是Excel 中的數(shù)值均以雙精度浮點(diǎn)數(shù)(二進(jìn)制X=分?jǐn)?shù)*2^(指數(shù)- 偏置))形式存儲(chǔ)。而十進(jìn)制要轉(zhuǎn)化為二進(jìn)制就意味著誤差的出現(xiàn)。同時(shí)IEEE 754 規(guī)范限制Excel 只儲(chǔ)存15 位精度之內(nèi)的浮點(diǎn)數(shù),以后數(shù)字當(dāng)0 處理，造成部分?jǐn)?shù)值計(jì)算結(jié)果存在尾數(shù)取舍誤差。兩種誤差雖然存在,但對(duì)使用Tran.round 公式影響較小，可以忽略。況且，水文資料出現(xiàn)15 位數(shù)值概率非常小。另外，注意不能連續(xù)修約，否則影響運(yùn)算結(jié)果。若誤差影響到公式使用，可以針對(duì)具體問題將Tran.round 公式予以補(bǔ)充完善，這里不再贅述。

4 結(jié)語

使用Excel“四舍五入”ROUND(X,n)函數(shù)能滿足大多數(shù)行業(yè)的要求，但水文等行業(yè)使用“四舍六入”修約，本文用Tran.round公式制作“四舍六入”計(jì)算模板，成功地解決了水文資料自動(dòng)計(jì)算，保證計(jì)算或校核無差錯(cuò)，減少計(jì)算量，提高工作效率，值得同行借鑒，同時(shí)對(duì)其他行業(yè)也具參考作用。