EXCEL内置函数在水文分析计算中的应用

张国辉

（山西省晋中市水文水资源勘测局 山西晋中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.