Excel函数用于解决水文流量计算中尾数取舍问题的简单方法

李 伟，李 弘，彭学成

（四川省成都水文水资源勘测局，四川 成都 611130）

0 引言

流量计算是水文站水文测验资料收集中非常重要的一项基本工作。传统方法进行流量计算需用计算器一步一步计算平均水深、部分断面面积、平均流速、部分流量，然后汇总统计出实测流量。中间还需要对各步计算结果进行有效位数和尾数人工取舍。故1份实测流量的整个计算过程是非常复杂和耗时的。在电脑普及化程度非常高的今天，各个水文站均配置了电脑，故笔者考虑采用Excel软件对实测流量进行计算。Excel作为一个常用的计算机办公软件，其计算的功能非常强大，可以为我们进行实测流量计算提供简便的方法。但在应用时我们发现了一个问题，就是Excel软件的尾数取舍是常规的四舍五入，而并非水文规范要求的尾数取舍方式。为了方便实测流量计算，笔者对Excel软件进行水文流量计算中的尾数取舍问题进行研究和试验，找到一种简单便捷的方法。

1 流量计算的尾数取舍方式

规范文件《数值修约规则与极限数值的表示和判定》（GB/T8170-2008）[1]和《水文资料整编规范》（SL247-2012）对流量计算的尾数取舍方式进行了规范。尾数取舍方式[2]如下：“取用精度位数后一位数字，采用‘四舍六入’方法取舍，即取用精度位数后一位数字小于五者则舍，大于五者则入，等于五时若其后有非零尾数仍入，无非零尾数则视取用的末位数字的奇偶取舍，为奇则入，为偶则舍。”

2 使用Excel函数实现流量计算的尾数取舍

2.1 采用 Excel函数

ROUND函数和MOD函数，关于这2个函数的介绍和用法可以参看本文参考文献[3]或者Excel软件的帮助文档。

2.2 实现流量计算的尾数取舍

由前节所述水文流量“四舍六入”取舍方法可知，这种取舍方法与Excel软件中ROUND函数采用的常规的“四舍五入”取舍方法是有差异的。差异如表1所示。

由上图分析得知2种取舍方法只有在一种情况下才存在取舍差异。即取用精度位数后一位数字等于5，且其后无非零尾数，且取用的末位数字为偶数时，水文流量“四舍六入”法为“舍”Excel“四舍五入”法为“入”。由此分析，保留两位小数的水文流量“四舍六入”的取舍方法可采用以下函数式实现。

式中，n为保留小数位数，与ROUND函数参数定义相同，比如1为保留1位小数，0位为保留个位数，-1为保留到十位数等。

本文以保留2位小数的取舍示例如图2。

图2 2种取舍方法保留2位小数的取舍示例

函数式解释：

第一部分为ROUND（A2，n）。即将A2单元格数据按保留n位小数进行常规Excel“四舍五入”法取舍。如图2例中各行原始数据常规Excel“四舍五入”为第三列结果。

第二部分为（MOD（A2*10^n，2）=0.5）/10^n MOD（A2*10^n，2）表示先求A2单元格数据乘以10的n次方再除以2的余数。如图2例中各行原始数据求余数结果分别为1.41、0.62、1.50001、0.5、1.5。（MOD（A2*10^n，2）=0.5）表示判断上一步求余数结果是不是等于0.5，如果等于0.5返回1，否则返回0。如图2例中各行原始数据仅1.145返回值为1，其余为 0。（MOD（A2*10^n，2）=0.5）/10^n表示将上一步返回结果除以10的n次方。如图2例中各行原始数据仅1.145返回值为0.01，其余为0。所以第二部分表示仅当取用精度位数后一位数字等于5，且其后无非零尾数，且取用的末位数字为偶数时，第二部分返回结果为0.01，其余为0。

表1 2种取舍方法的差异分析

最后将第一部分结果减去第二部分结果得到最终结果。如图2例中各行原始数据仅1.145返回值为第一部分结果减去0.01，其余为第一部分结果。表示仅当取用精度位数后一位数字等于5，且其后无非零尾数，且取用的末位数字为偶数时，最终结果会将第一部分结果减去0.01，即将第一部分结果中五入的0.01舍去，其余情况采用第一部分结果。从而得到了规范要求的水文流量“四舍六入”取舍方法。

3 使用Excel函数实现流量计算中各分步计算

3.1 采用 Excel函数

IF函数，关于这2个函数的介绍和用法可以参看本文参考文献[3]或者Excel软件的帮助文档。

3.2 水深计算

规范文件《水文资料整编规范》（SL247-2012）对水深的取用精度方式进行了规范[2]。“不小于100 m，记至1 m；小于100 m，不小于5 m，记至0.1 m；小于5 m，记至0.01 m”。所以对水深计算结果采用以下函数式实现精度取舍。

3.3 断面面积计算

规范文件《水文资料整编规范》（SL247-2012）对断面面积的取用精度方式进行了规范。“取三位有效数字，小数不过二位”。可以表示为“断面面积数值不小于100，记至1；小于100，不小于10，记至0.1；小于10，记至0.01”（由于笔者所在水文站断面面积一般均小于1000 m2，故不考虑断面面积不小于1000 m2的情况）。所以对断面面积计算结果采用以下函数式实现精度取舍。

3.4 流速计算

规范文件《水文资料整编规范》（SL247-2012）对流速的取用精度方式进行了规范。“不小于1 m/s，取三位有效数字；小于1 m/s，取二位有效数字，小数不过三位”。可以表示为“流速数值不小于10，记至0.1；小于10，不小于0.1，记至0.01；小于 0.1，记至0.001”（由于笔者所在水文站流速一般均小于100 m/s，故不考虑流速不小于100 m/s的情况）。所以对流速计算结果采用以下函数式实现精度取舍。

3.5 流量计算

规范文件《水文资料整编规范》（SL247-2012）对流量的取用精度方式进行了规范。“取三位有效数字，小数不过三位”。可以表示为“流量数值不小于10000，记至100；小于10000，不小于1000，记至10；小于1000，不小于100，记至1；小于100，不小于1，记至0.1；小于10，不小于1，记至0.01；小于1，记至0.001”（由于笔者所在水文站流速一般均小于100000 m3/s，故不考虑流速不小于100000 m3/s的情况）。所以对流量计算结果采用以下函数式实现精度取舍。

流量计算中各分步计算示例如图3。

流量计算中各分步计算函数式（2）（3）（4）（5）看似很长和复杂，其实均为IF函数的嵌套使用，中间全部是插入的函数式（1），所以各个函数式都很简单易用。将各个函数式用于实测流量计算表中，通过对同类计算函数式的复制粘贴就可以对流量中各行列数据进行精度取舍，最终计算出符合规范要求的实测流量值。

图3 流量计算中各分步计算示例

4 使用Excel实现流量计算可能出现问题的探讨

由于Excel中浮点运算可能会给出不准确的结果[4]，本文所采用的计算函数式进行精度取舍后偶尔还是会出现偏差。原因是Excel中的数值均是以二进制存储，而浮点数（即小数）以二进制存储时，大多数为无限循环数，而且Excel中只能存储15位精度之内的浮点数，所以部分小数计算结果就存在尾数取舍误差。比如计算结果是0.5，可能在有些情况下就存储为0.500000000000028，而1.5有些情况下却存储为1.49999999999999。在这种情况出现时本文函数式（1）的计算结果就会出现偏差。这种情况出现时，函数式（1）中的MOD（A2*10^n，2）就会出现本该等于0.5的却由于Excel中浮点运算误差而不等于0.5，故笔者考虑将MOD（A2*10^n，2）改为 ROUND（MOD（A2*10^n，2），10），这样对MOD（A2*10^n，2）的结果进行小数10位的取舍，消除像0.500000000000028这种Excel中浮点运算误差。但这样做也会导致本来计算结果真的为1.49999999999999的数据出现精度取舍偏差。但笔者以为后面这种偏差出现的几率比Excel中浮点运算误差引起偏差的几率更低（关于哪种情况的几率更低笔者还未能进行证明，希望各位读者朋友能够帮忙证明，或者找到更好的方法），故笔者采用这种改良过的函数式（1）。即如下函数式（6），用以替代函数式（2）（3）（4）（5）中包含的函数式（1）。

=ROUND（A2，n）-（ROUND（MOD（A2*10^n，2），10）=0.5）/10^n （6）

5 结论

本文仅仅选用ROUND函数和MOD函数2个Excel函数就实现了符合规范要求的水文流量“四舍六入”取舍方法，方法简单易学。加上IF函数仅仅3个Excel函数就解决了符合规范要求的水文实测流量计算中水深、断面面积、流速、流量各个分步计算的取用精度取舍问题。从而大大地简化了实测流量的计算过程，节约了实测流量计算时间。方法简单易学，便于推广，从而能提高水文流量测验的工作效率。本文的取用精度取舍方法已经在邛崃水文站的实测流量计算中进行大量运用，计算结果准确可靠，得到了同事们的认可。

当然，水平有限，本文旨在抛砖引玉，希望广大水文技术人员能从中得到启发，编写出更多方便实用的水文电算化程序，从而提高各种水文技术工作的工作效率。