EXCEL内置函数在水文分析计算中的应用
2013-04-23张国辉
张国辉
(山西省晋中市水文水资源勘测局 山西晋中030600)
Excel提供了丰富的内置函数,可以简便、快捷地进行各种数据运算、处理、分析。如果能利用Excel提供的内置函数,结合一些VBA基本语言和逻辑关系,编写内嵌式程序,将其应用于实际工作,从而可以大大提高工作效率,使数据处理自动化、简单化、直观化。同样可以充分利用它进行大量的水文数据的分析、计算处理,使我们的工作达到事半功倍的效果。为此,本文就以现在使用最广的Excel2003为基础,与大家共同分享和探讨它在我们日常水文工作中的一些应用。
1 Excel内置函数的调用和认识
无论是 Excel2000、Excel2003、还是 Excel2007,只要能够打开和使用这些办公自动化软件的操作平台,均无特殊要求。
1.1 内置函数的调用
Excel有些内置函数默认是不可以使用的。为此,就需要打开并能够调用它们。任意打开一个Excel工作表,单击:工具→加载宏,出现加载宏对话框,依次打勾选择,并确定之后,内置函数便可进行调用了。
内置函数一经调用,只要不再执行关闭操作,则在本机其他工作表中也可同样使用。图1为内置函数的调用。
图1 内置函数的调用
1.2 内置函数的位置
Excel内置函数的位置,可以在命令按钮中点击:“插入→函数”,在选择类别中选择“全部”,即可找到并应用Excel全部的内置函数。图2为内置函数的位置。
图2 内置函数的位置
1.3 几个重要函数
Excel内置函数中有几个函数,是水文分析和计算中经常用到的重要函数,特别加以说明。
①IF函数:基本语句为IF(logical_test,[value_if_true],[value_if_false])。其中logical_test,为判定条件。如果指定条件的计算结果为TRUE,IF函数将返回某个值;如果该条件的计算结果为FALSE,则返回另一个值。
在Excel2003中最多可以使用 64个IF函数作为value_if_true和value_if_false参数进行嵌套以构造更详尽的测试,这对于我们一般进行的水文计算已经足够了。
②MATCH函数:基本语句为MATCH(lookup_value,lookup_array,match_type)。其中Lookup_value为需要在数据表中查找的数值。Lookup_array可能包含所要查找的数值的连续单元格区域,Lookup_array应为数组或数组引用。Match_type为数字 -1、0或 1。Match-type指明 Microsoft Excel如何在lookup_array中查找 lookup_value。
如果 match_type为 0,函数 MATCH查找等于lookup_value的第一个数值。Lookup_array可以按任何顺序排列。这也是水文分析与计算中常用的参数,其它参数在此不做详述。
③ISEVEN函数:基本语句为ISEVEN(number)。Number待测试的数值,如果参数 number为偶数,返回 TRUE,否则返回FALSE。如果参数值不是整数,则截尾取整。
④ROUND函数:基本语句为ROUND(number,num_digits)。其中Number为需要进行四舍五入的数字。Num_digits指定的位数,按此位数进行四舍五入。返回某个数字按指定位数取整后的数字。
⑤MOD函数:基本语句为MOD(number,divisor)。返回两数相除的余数。结果的正负号与除数相同。其中Number为被除数。Divisor为除数。
⑥QUOTIENT函数:基本语句为QUOTIENT(numerator,denominator)。返回商的整数部分,该函数可用于舍掉商的小数部分。其中Numerator为被除数。Denominator为除数。
⑦其他一些简单常用的基本函数:SUM()、MIN()、MAX()、ISBLANK()等在此就不再赘述。
2 Excel内置函数在水文分析与计算中的应用
2.1 水文数据修约基本法则“四舍六入”的程序设计思路
根据现在实行的中华人民共和国行业标准《水文资料整编规范》SL247—1999[1],要求所有水文资料整编成果取用精度位数的后一位数字,采用“四舍六入”方法取舍。所以,“四舍六入”数据修约是水文资料整编中的一个重要环节,它贯穿于所有水文数据计算过程。
具体就是:取用精度位数后一位数字小于五者则舍,大于五者则入,等于五时若其后有非零尾数仍入,无非零尾数则视取用的末位数字的奇偶取舍,末位数字为奇则入,末位数字为偶则舍,其流程框图如图3[2]所示。
图3 “四舍六入”修约法程序流程框图
2.2 应用实例——水位、水温测验统计校核计算表
鉴于水文分析与计算问题的多样性,以及EXCEL应用的广泛性,仅以《水位、水温测验统计校核计算表》为例,来说明其具体应用步骤和方法。
2.2.1 设计EXCEL表格形式
根据实际应用,设计EXCELL表格《水位、水温测验月统计校核计算表》如图4所示。
2.2.2 在EXCEL统计表格内嵌入公式
在逐日水位表及项目统计栏内,根据各自的内容嵌入计算公式,并应注意小数位数按规范取舍[3]。
2.2.2.1 在逐日水位表内嵌入公式
按照水文传统方式录入的水位数据,在整数位数据相同时,后面的数据省略了整数部分,从而无法进行计算和统计。为此,要按如下步骤进行必要的转换。
①将D6~D36单元格格式,设置为文本方式,即可在相应单元格内按水文习惯录入水位数据。
图4 水位、水温测验月统计校核计算表局部放大图
②将D6~D36单元格内的数据,转换为可利用EXCEL表统计、计算的格式:
C6=VALUE(D6)
C7=IF(LEN(D7)=2,QUOTIENT(C6,1)+VALUE(D7)/100,VALUE(D7))
C8~C36为C7向下复制而得。
③为保持工作界面的清洁,将C6~C36单元格的显示内容设置为不可见(即显示字体颜色设为白色)。
2.2.2.2 在项目统计栏内嵌入公式
在向各旬、月项目统计栏内嵌入公式时应注意几个问题:
①1、3、5、7、8、10、12 月份,可用 1 月份的公式进行复制;4、6、9、11月份的公式,可用4月份的公式进行复制;2月份的公式为单独系列。
②各月上、中旬公式可进行复制。
③水位月统计之最高、最低及相应日期,为日平均值所对应的值及日期,而非瞬时值。
鉴于篇幅所限,不将公式在此列出,但如有兴趣的同志可共同探讨此问题。
2.2.3 将设计好的EXCEL统计表格设置保护
为了保护好已输入的公式,以免被误操作;保持设计好的EXCELL工作表的直观性;同时月统计各项目,是由EXCELL表自动计算所得,不需要人工输入。所以要设置单元格保护,见图5。
图5 设置工作表的保护
将设计好的表式,配合“Ctrl”键,选中除代表366日需输入内容的其他部分。再依次按:工具→保护→保护工作表→设置密码,将工作表进行保护。
2.2.4 保存设计好的EXCEL统计表格
可以有两种保存方法:一是将设计好的表格存为EXCELL模板,另一是命名并保存为EXCELL工作表。在这里均不再一一详细叙述。
2.2.5 示例
用义棠站2011年部分月份的数据进行计算,结果如图6所示。
图6 计算实例
3 结语
在日常水文分析和计算工作中,表格化的工作内容占据很大一部分。充分发挥和利用EXCEL表的强大功能,使水文职工从繁复、冗杂的计算工作中解脱出来,提高工作效率,是时代和社会发展的趋势和要求。同时,用EXCEL工作表,处理日常水文分析和计算,具有直观性、随即性的特点,也就同时满足了水文“四随”的工作要求。将水文、数学、英语、编程、计算机等与实践相结合,不断地解决工作中遇到的问题,也是水文事业进步的内在推动力。笔者已将日常水文分析和计算工作中的含沙量计算、蒸发量日表的统计计算等设计了相应的EXCEL工作表,愿与有兴趣者互相交流。由于自己水平所限,不妥之处,敬请指正。
[1]中华人民共和国长江水利委员会水文局.SL 247- 1999 水文资料整编规范[S].北京:中国水利水电出版社,2000:82.
[2]张国辉.水文资料整编中数字修约方法的程序编制[J].山西水利科技,2005(2):89.
[3]中华人民共和国水利部水文局.SL 460- 2009 水文年鉴汇编刊印规范[S].北京:中国水利水电出版社,2010:19.