７２山西水利科技２０１３年２月文章编号：１００６－８１３９（２０１３）０１－０７２－０３ＥＸＣＥＬ内置函数在水文分析计算中的应用张国辉（山西省晋中市水文水资源勘测局山西晋中０３０６００）摘要：Ｅｘｃｅｌ是美国Ｍｉｃｒｏｓｏｆｃ（微软）公司办公自动化软件。伍ｃｅ的重要组成部分之一，是Ｗｉｎｄｏｗｓ操作平台上非常优秀的电子表格软件，它具有强大的制作表格、处理数据、分析数据、创建图表等功能。通过对Ｅｘｃｅｌｌ的认真学习与实践，利用其内置函数，编写内嵌式程序，将其应用于实际工作，使数据处理自动化，大大提高工作效率。在水文分析、计算工作中我们同样可以充分利用它进行大量的数据处理。关键词：Ｅｘｃｄ；内置函数；内嵌式程序；水文分析计算；应用中圈分类号：Ｐ３３３文献标识码：ＢＴｈｅＡｐｐｌｉｃａｔｉｏｎｏｆＥＸＣＥＬＩｎｎｅｒＦｕｎｃｔｉｏｎＵｓｅｄｉｎＨｙｄｒｏｌｏｇｉｃａｌＡｎａｌｙｓｉｓａｎｄＣａｌｃｕｌａｔｉｏｎＺｈａｎｇＧｕｏ－ｈｕｉＡｂｓｔｒａｃｔ：ＥｘｃｅｌｉｓｏｎｅｏｆｔｈｅｉｍｐｏｒｔａｎｔｃｏｍｐｏｎｅｎｔｓｏｆＭｉｃｒｏｓｏｆｔｏｆｆｉｃｅｓｏｆｔｗａｒｅ．ＩｔｉｓａｖｅｒｙｇｏｏｄｓｐｒｅａｄｓｈｅｅｔｓｏｆｔｗａｒｅｏｐｅｒａｔｅｄｏｎｔｈｅＷｉｎｄｏｗｓｐｌａｔｆｏｒｍ，ａｎｄｈａｓｔｈｅｐｏｗｅｒｆｕｌｆｕｎｃｔｉｏｎｓｓｕｃｈ鹊ｆｏｒｍｐｒｏｄｕｃｔｉｏｎ，ｄａｔａｐｒｏｃｅｓｓｉｎｇａｎｄａｎａｌｙｚｉｎｇ，ａｎｄｃｈａｒｔｃｒｅａｔｉｎｇ．ＢａｓｅｄｏｎｔｈｅｓｅｒｉｏｕｓｌｅａｒｎｉｎｇａｎｄｐｒａｃｔｉｃｅｏｆＥｘｃｅｌ，ｉｔｓｉｎｎｅｒｆｕｎｃｔｉｏｎｈａｓｂｅｅｎｕｓｅｄｉｎｗｒｉｔｉｎｇｅｍｂｅｄｄｅｄｐｒｏｃｅｓｓ，ａｎｄａｐｐｌｉｅｄｔｏｔｈｅａｃｔｕａｌｗｏｒｋｔｏｍａｋｅｔｈｅｄａｔａｐｒｏｃｅｓｓｉｎｇａｕｔｏｍａｔｉｏｎａｎｄｇｒｅａｔｌｙｉｍｐｒｏｖｅｔｈｅｗｏｒｋｅｆｆｉｃｉｅｎｃｙ．Ｉｎｔｈｅｈｙｄｒｏｌｏｇｉｃａｌａｎａｌｙｓｉｓａｎｄｃａｌｃｕｌａｔｉｏｎ，ｉｔＣａｎｂｅａｌｓｏｔａｋｅｎａｄｖａｎｔａｇｅｏｆｆｏｒａｌｏｔｏｆｄａｔａｐｒｏｃｅｓｓｉｎｇ．ｋｅｙｗｏｒｄｓ：ＥＸＣＥＬ；ｉｎｎｅｒｆｕｎｃｔｉｏｎ；ｅｍｂｅｄｄｅｄｐｒｏｃｅｓｓ；ｈｙｄｒｏｌｏｇｉｃａｌａｎａｌｙｓｉｓａｎｄｃａｌｃｕｌａｔｉｏｎ；ａｐｐｌｉｃａｔｉｏｎＥｘｃｅｌ提供了丰富的内置函数，可以简便、快捷地进行各种内置函数一经调用，只要不再执行关闭操作，则在本机其数据运算、处理、分析。如果能利用Ｅｘｃｅｌ提供的内置函数，结合他工作表中也可同样使用。图１为内置函数的调用。一些ＶＢＡ基本语言和逻辑关系，编写内嵌式程序，将其应用于实际工作，从而可以大大提高工作效率，使数据处理自动化、简单化、直观化。同样可以充分利用它进行大量的水文数据的分析、计算处理，使我们的工作达到事半功倍的效果。为此，本文就以现在使用最广的Ｅｘｃｅｌ２００３为基础，与大家共同分享和探讨它在我们日常水文工作中的一些应用。１Ｅｘｃｅｌ内置函数的调用和认识无论是Ｅｘｃｅｌ２０００、Ｅｘｃｅｌ２００３、还是Ｅｘｃｅｌ２００７，只要能够打一一一一开和使用这些办公自动化软件的操作平台，均无特殊要求。１．１内置函数的调用Ｅｘｃｅｌ有些内置函数默认是不可以使用的。为此，就需要打开并能够调用它们。任意打开一个Ｅｘｃｅｌ工作表，单击：工具一提供ＩｎｔｅｍｅｔＡｓｓｉｓｔａｎｔＶＢＡ加载宏，出现加载宏对话框，依次打勾选择，并确定之后，内置函数便可进行调用了。图１内置函数的调用收稿日期：２０１２—１１—２０作者筒介：张国辉（１９６８一），男，工程师，１９９０年毕业于河海大学，学士学位，长期从事水文分析计算、水资源评价、水文镄ｌ验整编等方面的工作。万方数据第１期（总第１８７期）１．２内置函数的位置张国辉：ＥＸＣＥＬ内置函数在水文分析计算中的应用部分。其中Ｎｕｍｅｒａｔｏｒ为被除数。Ｄｅｎｏｍｉｎａｔｏｒ为除数。７３Ｅｘｃｅｌ内置函数的位置，可以在命令按钮中点击：“插入一函数”，在选择类别中选择“全部”，即可找到并应用Ｅｘｃｅｌ全部的内置函数。图２为内置函数的位置。⑦其他一些简单常用的基本函数：ＳＵＭ０、ＭＩＮ０、ＭＡｘｏ、ＩＳＢＬＡＮＫ０等在此就不再赘述。２Ｅｘｃｅｌ内置函数在水文分析与计算中的应用麓囊叠披（ｓ）：｝请－入＿二霉佃蟾的说明采括进当姆徽什么．然唐簟击。转蓟。［—受萄雨５］硪造拜嶷明（ｃ）：全都选辑蕾数ｑ１）：Ａｃｃｒ！ｎｔ２．１水文数据修约基本法则“四舍六入＂的程序设计思路根据现在实行的中华人民共和国行业标准＜水文资料整编规范）ＳＬ２４７－－１９９９ｔ”，要求所有水文资料整编成果取用精度位数的后一位数字。采用“四舍六入”方法取舍。所以，“四舍六人”数据修约是水文资料整编中的一个重要环节，它贯穿于所有水文数据计算过程。具体就是：取用精度位数后一位数字小于五者则舍，大于五者则入，等于五时若其后有非零尾数仍入，无非零尾数则视取用的末位数字的奇偶取舍，末位数字为奇则入，末位数字为偶则舍，其流程框图如图３哆所示。≯圈■——■——●——■—■■—■■■—————■—■—＿＾．’＾ＣＣＲｌ盯Ａｃｃｒ！ｔｔｔｌｔ一＆ＣＣＲＩＩＩＴＩＩＪＡＣＯ￥！＃ＣＯ翱＾ＪＳＳ（ｎｕｔｌ＝ｅｘ）遁百培定披值的蟪对位．孵不带符号的藏值”‘圈２内量函数的位置１．３几个重要函数Ｅｘｃｅｌ内置函数中有几个函数，是水文分析和计算中经常用到的重要函数，特别加以说明。①珂函数：基本语句为ＩＦｏｏｇｉｃａＬＪｅｓｔ’［ｖａｌｕｅ＿ｉｆ＿ｔｒｕｅ］，［ｖａｌｕｅ＿ｉｆ＿ｆａｌｓｅ］）。其中ｌｏｇｉｃａｌ＿ｔｅｓｔ，为判定条件。如果指定条件ｌ接收新数据Ｉ。１乒一ｌ按级别分类ｌ——罩乒一ｌ放大至取用精度位数并取整ＩＬ——１５＝——一的计算结果为ＴＲＵＥ，ＩＦ函数将返回某个值；如果该条件的计算结果为ＦＡＬＳＥ。则返回另一个值。在Ｅｘｃｅｌ２００３中最多可以使用６４个ＩＦ函数作为ｖａｌｕｅ＿ｉｆ＿ｔｒｕｅ和ｖａｌｕｅ＿ｉｆ＿ｆａｌｓｅ参数进行嵌套以构造更详尽的测试，这对于我们一般进行的水文计算已经足够了。②ＭＡＴＣＨ函数：基本语句为ＭＡＴＣＨ（１００ｋｕｐ＿ｖａｌｕｅ，ｌｏｏｋｕｐ＿ａｒｒａｙ，ｍａｔｃｈ＿ｔｙｐｅ）。其中Ｌｏｏｋｕｐｊａｌｕｅ为需要在数据表中Ｉ是否同时满足：１．末位为偶数；２．其后需进位的数字为５；３．需进位的数字５后无精度要求内的非零尾数。Ｌ旦叫—］‘●Ｉ堡：婴鱼蓦人”法碍二墼塑‘’·＝：一——：：－’’．．。Ｌ—【．一Ｊ—【．，ＴＲＵＥ丈乡卜＼广１譬翟埔尝主Ｉ—“四台六入”后的新数据查找的数值。Ｌｏｏｋｕｐ＿ａｒｒａｙ可能包含所要查找的数值的连续单元格区域，Ｌｏｏｋｕｐａｒｒａｙ应为数组或数组引用。Ｍａｔｃｈ＿ｔｙｐｅ为数字一１、０或１。Ｍａｔｃｈ—ｔｙｐｅ指明ＭｉｃｒｏｓｏｆｔＥｘｃｅｌ如何在ｌｏｏｋｕｐ＿ａｒｒａｙ中查找ｌｏｏｋｕｐ＿ｖａｌｕｅ。≮７Ｃ返回至调用此数据处】图３“四舍六入”修约法程序流程框圈如果ｍａｔｃｈ＿ｔｙｐｅ为０，函数ＭＡＴＣＨ查找等于ｌｏｏｋｕｐ＿ｖａｌｕｅ的第一个数值。ｌ＿Ｄｏｋｕｐ＿ａｒｒａｙ可以按任何顺序排列。这也是水文分析与计算中常用的参数，其它参数在此不做详述。２．２应用实例——水位、水温测验统计校核计算表鉴于水文分析与计算问题的多样性。以及ＥＸＣＥＬ应用的广泛性，仅以＜水位、水温测验统计校核计算表＞为例，来说明其具体应用步骤和方法。２．２．１设计ＥＸＣＥＬ表格形式（要）ＩＳＥＶＥＮ函数：基本语句为ＩＳＥＶＥＮ（ｎｕｍｂｅｒ）。Ｎｕｍｂｅｒ待测试的数值，如果参数ｎｕｍｂｅｒ为偶数，返回ＴＲＵＥ，否则返回ＦＡＬＳＥ。如果参数值不是整数，则截尾取整。（蓟ＲＯＵＮＤ函数：基本语句为ＲＯＵＮＤ（ｎｕｍｂｅｒ，ｎｕｍ＿ｄｉｇｉｔｓ）。其中Ｎｕｍｂｅｒ为需要进行四舍五入的数字。Ｎｕｍ＿ｄｉｇｉｔｓ指定的位数，按此位数进行四舍五入。返回某个数字按指定位数取整后的数字。根据实际应用，设计ＥＸＣＥＬＬ表格＜水位、水温测验月统计校核计算表＞如图４所示。２．２＿２在ＥＸＣＥＬ统计表格内嵌入公式在逐日水位表及项目统计栏内，根据各自的内容嵌入计算公式，并应注意小数位数按规范取舍圈。２．２．２．１在逐日水位表内嵌入公式按照水文传统方式录入的水位数据，．在整数位数据相同时，后面的数据省略了整数部分，从而无法进行计算和统计。为此，要按如下步骤进行必要的转换。①将Ｉ）６一Ｄ３６单元格格式，设置为文本方式，即可在相应⑤ＭＯＤ函数：基本语句为ＭＯＤ（ｎｕｍｂｅｒ，ｄｉｖｉｓｏｒ）。返回两数相除的余数。结果的正负号与除数相同。其中Ｎｕｍｂｅｒ为被除数。Ｄｉｖｉｓｏｒ为除数。⑥ＱＵＯＴＩＥＮＴ函数：基本语句为ＱＵＯＴＩＥＮＴ（ｎｕｍｅｒａｔｏｒ，ｄｅｎｏｍｉｎａｔｏｒ）。返回商的整数部分，该函数可用于舍掉商的小数万方数据７４张国辉：ＥＸＣＥＬ内置函数在水文分析计算中的应用２０１３年２月将设计好的表式，配合“Ｃｔｒｌ”键，选中除代表３６６日需输入内容的其他部分。再依次按：工具—保护—保护工作表—设置密码，将工作表进行保护。Ｉ—ｊ…上一一一一一一４一一—ｏ一…一■～一塞瑶Ｅ二二ｊ二ｊ二二士二￡二±ｊ＝二士搿！Ｉ！：！ｌ竹ｆ＿１１２．２．４保存设计好的ＥＸＣＥＬ统计表格可以有两种保存方法：一是将设计好的表格存为ＥＸＣＥＬＬ模板，另一是命名并保存为ＥＸＣＥＬＬ工作表。在这里均不再一一详细叙述。２．２．５示例用义棠站２０１１年部分月份的数据进行计算，结果如图６所示。鹜蚕霹ｊｌ坚蛾Ｉ’”㈣辫犯’１ｔ磷黉嚣：慧鐾ｌ秘毋鹰．ｊｔ鹬鬃婺錾；辇委ｌｌ『翳鹱露鋈畦鞫谴疆霾霉螽醴耱撼黼ｉ｝４ｔ＾ｋ＿－ｔ匿溪黼鬈露黼｝；簧鞘隧蠹箧裂磷｜霎羹辞爨蘑豳鬟ｂ臻ｔ专鬟蒌錾非ｉ￡孽ｉ羹菱裂潦羹薹鼯嚣黎黼拦露嗣瀚鞋蝣鹱蒸湖藩震；《§囊裂ｊ蕊雾萋薹昼摇簿酲臻黛陵翱隧鬈Ｉ｝嚣冀弱謦它簪下，ｌ；薹蠹避ｙ弩霎薹蠹酮孽嵫雾羁裂躺霍舔袖瞄黼蓬～；、ｌｌ＇ｌ雾雾冀图４水位、水温测验月统计校核计算表局部放大图单元格内按水文习惯录入水位数据。２０１１年义靠站水位、水出、西古省眇水ｆ盍％潦≯≮ｔ局表②将Ｄ６～Ｄ３６单元格内的数据，转换为可利用ＥＸＣＥＬ表统计、计算的格式：Ｃ６＝ＶＡＬＵＥ（Ｄ６）Ｃ７＝ＩＦ（ＬＥＮ（０７）＝２，ＱＵＯＴＩＥＮＴ（Ｃ６，１）＋ＶＡＬＵＥ（Ｄ７ｙ１００，ＶＡＬＵＥ（Ｄ７））Ｃ８一Ｃ３６为ｃ７向下复制而得。③为保持工作界面的清洁，将Ｃ６～Ｃ３６单元格的显示内容设置为不可见（即显示字体颜色设为白色）。２．２．２．２在项目统计栏内嵌入公式在向各旬、月项目统计栏内嵌入公式时应注意几个问题：①１、３、５、７、８、１０、１２月份，可用１月份的公式进行复制；４、６，９、１１月份的公式，可用４月份的公式进行复制；２月份的公式为单独系列。图６计算实例②各月上、中旬公式可进行复制。③水位月统计之最高、最低及相应日期，为日平均值所对应的值及日期，而非瞬时值。鉴于篇幅所限，不将公式在此列出，但如有兴趣的同志可共同探讨此问题。２．２．３将设计好的ＥＸＣＥＬ统计表格设置保护３结语在日常水文分析和计算工作中，表格化的工作内容占据很大一部分。充分发挥和利用ＥＸＣＥＬ表的强大功能，使水文职工从繁复、冗杂的计算工作中鳃脱出来，提高工作效率，是时代和社会发展的趋势和要求。同时，用ＥＸＣＥＬ工作表，处理日常水文分析和计算，具有直观性、随即性的特点，也就同时满足了水文“四随”的工作要求。将水文、数学、英语、编程、计算机等与实践相结合，不断地解决工作中遇到的问题，也是水文事业进步的内在推动力。笔者已将日常水文分析和计算工作中的含沙量计算、蒸发量日表的统计计算等设计了相应的ＥＸＣＥＬ工作表，愿与有兴趣者互相交流。由于自己水平所限，不妥之处，敬为了保护好已输入的公式，以免被误操作；保持设计好的ＥＸＣＥＬＬ工作表的直观性；同时月统计各项目，是由ＥＸＣＥＬＬ表自动计算所得，不需要人工输入。所以要设置单元格保护，见图５。蕤拼写检查（ｓ）¨·Ｆ７请指正。参考文献［１］中华人民共和国长江水利委员会水文局．ＳＬ２４７—１９９９水文资料整编规范Ｉｓ］．北京：中国水利水电出版社，２０００：８２．［２］张国辉．水文资料整编中数字修约方法的程序编制［Ｊ］．山西水利科技，２００５（２）：８９．［３］中华人民共和国水利部水文局．ＳＬ４６０－２００９水文年鉴汇编刊印规范Ｉｓ］．北京：中国水利水电出版社，２０１０：１９．信息检索（Ｒ）…ｈｈ＋Ｃｌｌｃｋ错误检查（Ｋ）…■共享工作簿（Ｂ）…保护工作表（Ｐ）允许用户编辑区域（Ａ保护工作簿（ｗ）…共享工作区（Ｄ）…一～～一一．万方数据保护并共享工作簿（Ｓ）水温【℃）１日平均水位（ｍ图５设置工作表的保护EXCEL内置函数在水文分析计算中的应用
作者：作者单位：刊名：英文刊名：年，卷(期)：
张国辉， ZHANG Guo-hui
山西省晋中市水文水资源勘测局 山西晋中030600山西水利科技
Shanxi Hydrotechnics2013(1)
1.中华人民共和国长江水利委员会水文局 水文资料整编规范 2000
2.张国辉 水文资料整编中数字修约方法的程序编制[期刊论文]-山西水利科技 2005(02)3.中华人民共和国水利部水文局 水文年鉴汇编刊印规范 2010
引用本文格式：张国辉.ZHANG Guo-hui EXCEL内置函数在水文分析计算中的应用[期刊论文]-山西水利科技 2013(1)
