流量四舍六入计算在电子表格中运用.docx

1、流量四舍六入计算在电子表格中运用“四舍六入”实测流量的EXCEL快速算法实测流量原始计算，是水文测站的基础工作。计算按照流量原始测算专用的表格，逐项逐步计算填写，工作量大，计算繁杂、费时费力又易出错。计算机计算、数字处理功能，如函数运算、取整、“四舍五入”等可为流量计算提供平台。但在水文行业上，为提高资料精度，规范对数字提出了“四舍六入，逢五奇进偶舍”的处理要求，同时还限制了相应的有效数字位数，以及对不同数值范围内的数据又有不同的规定。“四舍六入，逢五奇进偶舍”，在手工资料计算中不存在多大的问题，只是多了一项观察，就是当取用精度位数后一位数字是5、位数为0时需要观察前位的奇偶性。但当使用计算机

2、计算资料时，问题就反映出来，即计算机软件在数学处理中没有明确这一模式，不能直接处理这样要求的数据，特别在流量原始计算上，无论是实测流量成果，计算表中统计数据、各步计算过程中间数据等，均应采用“四舍六入”处理规则，其麻烦是显而易见的。虽然目前任何一个软件开发工具都没有提供“四舍六入，逢五奇进偶舍”的函数，但计算机的先进性、科学性为开发人员提供了空间。笔者工作之余，巧用计算机系统函数，较成功实现了流量计算“四舍六入”数据处理，并在EXCEL中编制了“四舍六入，逢五奇进偶舍”实测流量原始自动计算表格，可直接应用于处理流量原始计算过程的全部数据上，并可在有关基础测验数据输入表格后即刻生成输出与水文测站

3、实测流量手工计算表要求一致的成果表格，具有快速性、简单性、适用性和易操作性，可减轻水文测站大量繁杂的流量校算。1 “四舍六入”数据处理思路1.1现行有关水文要素取用精度表1 现行有关水文要素取用精度一览表水位水面宽水深流量面积流速记至0.01m。保留1位小数。大于或等于5.0m，记至0.1m，小于5.0m，记至0.01m取三位有效数字，小数不过三位。取三位有效数字，小数不过二位。大于或等于1.0m/s，取三位有效数字，小于1.0m/s，取二位有效数字，小数不过三位。按照水文资料整编规范，对水文要素值X的处理有两个约束条件，即最多保留小数位数m和有效数字位数n，在尾数的取舍上遵从“四舍六入，逢五

4、奇进偶舍”，按此可从三方面来进行数据处理设计。一：分析流量计算相关水文要素取用精度（表1）可以发现，水位精度要求小数保留位数为2，水深的精度要求以5为界保留1位与2位小数；流速以1.0m/s为界要求保留2位或3位有效数字，由于流速常常小于10m/s，因此当其不小于0.1m/s时，应保留2位小数，而当其小于0.1m/s时，应保留3位小数，就是说，流速以0.1m/s 为临界值，保留2位或3位小数就能满足精度要求；流量精度要求的有效数字与小数位数都为3，由于流量Q整数位数常常大于1，求其整数位数m，以Q除以10的m次方，将其转换为小于1的数q（q=Q/10m），对q进行三位有效数字（保留3位小数）处

5、理，将处理结果乘以10的m次方即完成了流量数据处理；至于面积，由于有效数字与小数位数不同，当大于1时，采用流量的处理方法，当小于1时保留两位小数就能满足精度要求。因此，实测流量计算中有关水文要素，虽然受有效数字及保留小数位数两方面约束，但在数字处理中，可以仅通过直接或间接考虑小数保留位数实现。二：分析“四舍六入”，可以看出，它是“四舍五入”的一个特例，只在一种特殊情况下“舍”，即当数值X应该保留部分的末位为偶数，末位后面只有1位且为“5”，数据处理的主要工作就是判断该种情况的发生。其它情况按照数学上的“四舍五入”法则即可满足要求。判断过程见图1。三：数据处理公式的编写分析图一，待处理数字X应该

6、保留m位小数，第m位后面的数字舍去需满足三个条件：（1）第m位为偶数，（2）第m1位为“5”， （3） “5”（第m1位）后面数字为0。小数第m位数字的表达式为INT(X*10m)-10*INT X*10(m-1)，式中，INT(number)为取整函数，其中参数number可以为常数、函数或表达式。则满足条件（2）的表达式为：INT(X*10（m+1）)-10*INT X*10(m)5；满足条件（3）的表达式为：INT(X*10（m+1）X*10(m+1)0；偶数的判断采用余数函数MOD(number, Num_digit)，该函数表示被除数number除以除数Num_digit的余数，其中

7、参数number可以为常数、函数或表达式。由数学知识可知，如number为偶数，则能被2整除，即MOD(number，2)0，因此，满足条件（1）的表达式为MOD(INT(X*10m)-10*INT（X*10(m-1) ),2）=0。 几个条件同时满足，由逻辑函数AND(logical1,logical2, .)表示，其中Logical1, logical2, . 表示待检测的若干个条件值，各条件值可能为TRUE，可能为 FALSE。参数必须是逻辑值，或者包含逻辑值的表达式，所有参数的逻辑值为真时返回 TRUE。（1）、（2）、（3）条件都满足的表达式为：AND（INT(X*10（m+1）)-

8、10*INT（ X*10(m)）5，MOD(INT(X*10m)-10*INT（X*10(m-1) ),2）=0，INT(X*10（m+1）X*10(m+1)0）。数据处理采用IF函数。它的语法为IF(logical_test,value_if_true,value_if_false)。其中Logical_test表示计算结果为 TRUE 或 FALSE 的任意值或表达式。该函数表示，如果第一个参数logical_test返回的结果为真的话，则执行第二个参数Value_if_true的结果，否则执行第三个参数Value_if_false的结果。待处理数字X的公式为IF（AND（INT(X*10

9、（m+1）)-10*INT（ X*10(m)）5，MOD(INT(X*10m)-10*INT（X*10(m-1) ),2）=0，INT(X*10（m+1）X*10(m+1)0），TRUNC（X，m），ROUND（X，m）。公式中TRUNC(X，m )表示将X的第m位小数后面的部分截去，结果保留m位小数；ROUND（X，m）表示对X进行“四舍五入”处理，结果保留的m位小数。简言之，该式表示当X同时满足上述三个条件时，保留m位小数，不做舍入处理，否则对X进行“四舍五入”处理，保留m位小数。2、计算工具制作举例2.1、表格及页面按“站流量测算表”样式，在电子表中画出。表中横向格子交错，可先合并单元格

10、。如将水深部分，C8：C9合并，C10：C11合并；垂线平均水深部分，将L8：L10合并，L11：L12合并，就得到交错的目的。其余部分，用常用工具览中“格式刷”一刷而就。该表设置为一页，为输出成果页。计算过程及精度取舍计算放在右页（可用A4纸），计算结果引入输出页。这样设置，便于计算过程的编写和工作表的保护。2.2、水尺零点高程在输出页A5单元格，录入测流水尺编号，在计算过程页AC2单元格写入该组各水尺的零点高程：（以汊河集站流速仪断面为例）=IF(A5=C3,11.82,IF(A5=C4,11.09,IF(A5=C5,10.25,IF(A5=C6,9.16,IF(A5=C7,8.46,IF

11、(A5=C8,7.63,无该水尺)，式中数字为各水尺零点高程，范围从C3C8，如录入错误或越界，则提示“无该水尺”。2.3、测点深度 AF8单元格为非取舍精度测点深度，测点深度大于5.0m的表达式在AG8单元格写入：=IF(AND(MOD(INT(AF8*10),2)=0,INT(AF8*100)-INT(AF8*10)*10=5,AF8*100=INT(AF8*100),TRUNC(AE8,1),ROUND(AF8,1)测点深度小于5.0m的表达式在AH8单元格写入：=IF(AND(MOD(INT(AF8*100),2)=0,INT(AF8*1000)-INT(AF8*100)*10=5,A

12、F8*1000=INT(AF8*1000),TRUNC(AF8,2),ROUND(AF8,2)。AI8单元格为计算结果：=IF(AF8=5,AG8,AH8)。利用鼠标拖动法将它复制粘贴到其他计算区域。2.4、测点流速在AJ8单元格按流速仪公式算出非取舍精度流速。由于随流速的变化，精度要求不同，分步计算：阶数EJ的计算：在AK8单元格：=IF(AJ81,INT(LOG(AJ8)+1),0)当V=1.0m/s：在AM8单元格写入：=IF(AND(INT(AJ8/10(AK8-4)-INT(AJ8/10(AK8-3)*10=5,MOD(INT(AJ8/10(AK8-3),2)=0,INT(AJ8/1

13、0(AK8-4)=AJ8/10(AK8-4),INT(AJ8/10(AK8-3)*10(AK8-3),INT(AJ8+5*10(AK8-4)/10(AK8-3)*10(AK8-3)计算结果：在AN8单元格写入：=IF(AJ8=1.0,AL8,AM8)2.5、垂线间面积计算在AR9单元格算出非取舍精度数。在AS9单元格计算阶数EJ：=IF(AR91,INT(LOG(AR9)+1),0)。取舍精度由上表知：面积A1.0m2时，同流速V1.0m/s计算,将AM8单元格表达式粘贴到AT9,并将AJ8,AK8改为AR9,AS9.面积A1.0 m 2计算:同流速V1.0m/s时计算,将AL8单元格表达式粘

14、贴到AU9,并将AJ8改为AR9.计算结果：在AV9单元格：=IF(AND（AR90.1),AT9,AU9)2.6 流量精度取舍及过程举例在BH9单元格算出非取舍精度流量数,在BI9单元格计算阶数EJ.在BJ9单元格取舍: =IF(1)AND(2)INT(BH9/10(BI9-Y-1)-(3)INT(BH9/10(BI9-Y)*10=5(4),(5)MOD(INT(BH9/10(BI9-Y),2)=0,(6)INT(BH9/10(BI9-Y-1)=BH9/10(BI9-Y-1),(7)INT(BH9/10(BI9-Y)*10(BI9-Y),(8)INT(BH9+5*10(BI9-Y-1)/10

15、(BI9-Y)*10(BH9-Y)式中,Y为取用精度.由查上表知,Y=3。举例说明取舍过程.例1、 某非精度取舍流量为5425m3/s。按要求保留三位有效数字。EJ=INT（LOG（5425）+1）=4，则，BH9=5425，BI9=4，2式为5425，3式为5420，4式5425-5420=5成立，5式542/2余数为0成立，6式5425=5425成立，因此，1式成立，取值TRUE，计算结果为7式5420m3/s。例2、 某非精度取舍流量数为0.942500014m3/s，则EJ=0，BH9=0.9400014，BI9=0。2式为9425，3式为9420，4式9425-9420=5成立，5式

16、942/2余数为0成立，但6式94259425.00014不成立，1式取值为FALSE，计算结果为8式0.943 m3/s。表达式中，当4、5、6式同时成立时，1式才为TRUE，计算结果为7式，只要有一式不成立，1式为FALSE，计算结果为8式。2.8、工作簿文件将计算工具中大断面，水尺零点高程等基础信息写入计算过程页，就成了不同水文站的流量计算工具，存于SHEET1，SHEET2SHEETn工作表中，并以站名为表名，就成了综合计算工具工作簿文件。 3、结束语3.1 计算工具全由函数、函数嵌套等表达式编写，简明易读。逻辑函数IF（L，A1，A2），允许7层嵌套，用于水尺零点高程时，可录入7支水

17、尺，如该组水尺很多，可将较多工作部分录入。3.2 使用时，仅需在输出页录入水尺读数，编号，起迄时间，信号数、信号历时等原始信息，表中以彩色底纹标记。原始信息录完确定后，流量计算瞬间完成。该工具用于校算石角桥水文站流量46测次、汊河集水文站流量50测次、黄栗树水文站流量44测次、滁县水文站流量66测次、沙河集水文站（南灌渠）流量24测次。输出的成果表与手工计算表完全一样。3.3 打开工作簿文件后，先另存个文件名，避免改动原文件，可加密码保护。3.4 表中精度取舍计算过程表达式可直接复制用于电子表格中其它各类水文要素计算，如面雨量、水准、水尺测量计算，资料整编手工平行表如日平均水位、流量、含沙量计

18、算等等。1水位计算开始读数=B5终了读数=C5平均读数=(Y3+Z3)/2读数处理=IF(MOD(TRUNC(Y3*100)-TRUNC(Y3*10)*10),2)=1,ROUND(Y3,2),TRUNC(Y3,2)水尺零高=IF(A5=P8,12.98,IF(A5=P7,13.85,IF(A5=P6,14.80,IF(A5=P5,15.68,IF(A5=P10,11.25,IF(A5=P9-1,12.12,IF(A5=P9,12.14,IF(A5=P10-1,11.23)水位：=AB3+AC32断面参数:3水深计算:垂线水位:=$B$5+$E$5水深=AB10-AA10D5=IF(MOD(I

19、NT(AC10*10),2)=0,INT(AC10*10+0.4)/10,ROUND(AC10,1)D=5,AD10,AE10)4测点深计算:测点深度=D10*E10D5:=IF(AND(MOD(INT(AG10*10),2)=0,INT(AG10*100)-INT(AG10*10)*10=5,AG10*10=INT(AG10*10),TRUNC(AG10,1),ROUND(AG10,1)D=5,AH10,AI10)5测点流速计算总转数:=J10vi: =IF(J100,$D$2*J10/G10+$E$2,0)ejv: =IF(AL101,INT(LOG(AL10)+1),0)V1.0: =I

20、F(AND(INT(AL10/10(AM10-4)-INT(AL10/10(AM10-3)*10=5,MOD(INT(AL10/10(AM10-3),2)=0,INT(AL10/10(AM10-4)=AL10/10(AM10-4),INT(AL10/10(AM10-3)*10(AM10-3),INT(AL10+5*10(AM10-4)/10(AM10-3)*10(AM10-3)v计算后: =IF(AND(AL100.1),AN10,AO10)6平均水深计算:hi :=0.5*(AF8+AF10)7面积计算:间距=Z10-Z8ai =AU9*AV9ej =IF(AW91,INT(LOG(AW9)

21、+1),0)a=1 =IF(AND(INT(AW9/10(AX9-4)-INT(AW9/10(AX9-3)*10=5,MOD(INT(AW9/10(AX9-3),2)=0,INT(AW9/10(AX9-4)=AW9/10(AX9-4),INT(AW9/10(AX9-3)*10(AX9-3),INT(AW9+5*10(AX9-4)/10(AX9-3)*10(AX9-3)a1,AY9,AZ9)8垂线流速计算:vi:=IF(E10=0,K10*0.8,IF(E10=0.6,K10,其它)ejv=IF(BB101,INT(LOG(BB10)+1),0)(0.1,1.0):=IF(AND(MOD(INT

22、(BB10*100),2)=0,INT(BB10*1000)-INT(BB10*100)*10=5,INT(BB10*1000)=BB10*1000),TRUNC(BB10,2),ROUND(BB10,2)1.0:=IF(AND(INT(BB10/10(BC10-4)-INT(BB10/10(BC10-3)*10=5,MOD(INT(BB10/10(BC10-3),2)=0,INT(BB10/10(BC10-4)=BB10/10(BC10-4),INT(BB10/10(BC10-3)*10(BC10-3),INT(BB10+5*10(BC10-4)/10(BC10-3)*10(BC10-3)v

23、计算后:=IF(AND(BB100.1),BD10,BE10)9垂线平均流速计算:垂线平均: =0.7*BF10, =0.5*(BF10+BF12)ejv=IF(BG91,INT(LOG(BG9)+1),0)(0.1,1.0):=IF(AND(MOD(INT(BG9*100),2)=0,INT(BG9*1000)-INT(BG9*100)*10=5,INT(BG9*1000)=BG9*1000),TRUNC(BG9,2),ROUND(BG9,2)1.0:=IF(AND(INT(BG9/10(BH9-4)-INT(BG9/10(BH9-3)*10=5,MOD(INT(BG9/10(BH9-3),

24、2)=0,INT(BG9/10(BH9-4)=BG9/10(BH9-4),INT(BG9/10(BH9-3)*10(BH9-3),INT(BG9+5*10(BH9-4)/10(BH9-3)*10(BH9-3)v计算后:=IF(AND(BG9=0.1,BG91,INT(LOG(BL9)+1),0)q计算后: =IF(AND(INT(BL9/10(BM9-4)-INT(BL9/10(BM9-3)*10=5,MOD(INT(BL9/10(BM9-3),2)=0,INT(BL9/10(BM9-4)=BL9/10(BM9-4),INT(BL9/10(BM9-3)*10(BM9-3),INT(BL9+5*

25、10(BM9-4)/10(BM9-3)*10(BM9-3)11统计计算:最大流速(m/s): =MAX(K8:K40)水面宽(m):水面宽=C42-C8ej:=IF(B491,INT(LOG(B49)+1),0)调试结果:=IF(AND(INT(B49/10(C49-4)-INT(B49/10(C49-3)*10=5,MOD(INT(B49/10(C49-3),2)=0,INT(B49/10(C49-4)=B49/10(C49-4),INT(B49/10(C49-3)*10(C49-3),INT(B49+5*10(C49-4)/10(C49-3)*10(C49-3)最大水深(m): =MAX(

26、D8:D42)断面面积(m2)面积总数: =SUM(O9:O41)平均水深 (m) :=J49/D49流量(m3/s) :=SUM(R8:R46)平均流速(m/s): =Q49/J49保护公式为了防止我们的统计公式不小心被修改，在公式验证无误时，可采用单元格保护功能将其保护。具体作法是：选择要输入的区域如：附表2中的筛选条件区B4：F4格式单元格保护卡去锁定复选框确定，工具保护保护工作表确定，注意如果输入口令一定要记住。由于Excel在缺省情况下，所有单元格均被锁定，所以除筛选条件区外所有单元格均被锁定不可更改数据，从而保护了数据公式的修改。如需修改公式，可先撤消保护工作表，在修改公式数据，最后别忘了保护。