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

王 昕

（陜西省西安水文水資源勘測局，陜西 西安 710100）

0 引言

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

1 “四舍六入”計算方法

1.1 修約規則

引用主要規則[1]如下。

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

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

規則3：擬舍棄數字的最左一位數字是5，且其后有非0 數字時進1。

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

1.2 “四舍六入”的科學性

“四舍五入”是一種常規的數字修約規則，在各行業中應用非常普遍。但是，缺點是逢5 就進1，在1～9 這列數字中5 是中位數，從水文資料整編規范“符號檢驗”的觀點看，正負號沒有分布在標準線兩側。從數理統計學原理看，屬于單邊分布使修約后的數學期望出現系統偏高。

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

1.3 組合公式設計思路

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

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

設計思路見圖1。

圖1 組合公式流程結構圖

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

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

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

1.4.1 表達式

根據數學原理，寫出各項表達式如下。

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

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

規則1～規則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 先驗測試

為了驗證situ1 ～situ5 表達式，隨機取一組數據：0.20501，0.20424，0.21535，0.6894，0.6814 在EXCEL 中進行先驗測試，見表2。測試結果顯示準確，說明公式正確無誤。

表2 先驗測試（m=5,n=2）

1.4.3 Tran.round 公式

綜合上述情形，Situ2 與Situ1、Situ5 與Situ4 互為邏輯真和假關系，經過對Situ1、Situ5 的優化并定義為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)，將數值X 截為整數或保留n 位數的小數。

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

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

2 應用實例

2.1 流量計算模板

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

1）水深：小于100 m,不小于5 m,計至0.1 m,小于5 m,計至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）測點流速:不小于l m/s,取三位有效數字,小于1m/s,取二位有效數字, 小數不過三位。這里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

部分面積: 取三位有效數字, 小數不過兩位。這里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) 測點流量：取三位有效數字, 小數不過三位。這里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 土壤含水量計算模板

采用2019 年9 月10 日王曲站土壤墑情監測摘錄資料。

土壤含水量：用百分數表示，記至一位小數（記至0.1%)。

表3 王曲站土壤墑情監測烘干法計算表

操作過程：在墑情監測含水量計算表3 中，在相應單元格輸入X，m，n 值。摘錄一部分數據公式展示X，m，n 替換過程，其余略去。

土壤含水量（%）：百分數表示, 記至一位小數。這里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%

測點土壤含水量（%）:這里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%

測點土壤平均含水量（%）: 這里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%

相對濕度（%）：這里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%

從操作實例看出，應用Tran.round 公式，在Excel 中替換X、m、n 便完成“四舍六入”修約，形成計算模板。在模板中只需輸入觀測值或測量值，便可實現自動計算，簡單實用。

注意：①在替換X、m、n 過程中不能對公式中的標點、括弧有任何改變，否則出錯，會帶來檢查或重新復制公式的麻煩。②m、n 按給定位數或視中間運算后位數而定。

3 不準確結果分析

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

4 結語

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