基于Excel 的水文资料“四舍六入”计算

2020-04-30王昕

陕西水利 2020年2期

王昕

（陕西省西安水文水资源勘测局，陕西西安 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 公式予以补充完善，这里不再赘述。