EXCEL函数在水文数据处理中的应用

中国西部科技　２０１０年Ｏ１月（下旬）第０９卷第０３期总第２００期　ＥＸＣＥＬ函数在水文数据处理中的应用　徐荣　龚建新　谢玉琴　（新疆乌鲁木齐水文水资源勘测局，新疆乌鲁木齐８５００００）　摘要：水文数据是国家重要的基础信息资源，为很好地适应水文信息化和现代化发展形势，及时准确提供水文信息服　务，有必要掌握ＥＸＣＥＬ函数的一些计算功能。本文介绍了ＥＸＣＥＬ中几个常用特殊函数在水文数据处理中的使用方法，旨在　抛砖引玉，能使广大水文专业技术人员从中得到启发，掌握更多的函数应用到水文信息化建设上来。　关键词：ＥＸＣＥＬ函数；水文数据；应用　Ｔｈｅ　Ａｐｐｌｉｃａｔｉｏｎ　ｏｆ　ＥＸＣＥＬ　Ｆｕｎｃｔｉｏｎ　ｉｎ　ｔｈｅ　Ｈｙｄｒｏｌｏｇｙ　Ｄａｔａ　Ｄｉｓｐｏｓａｌ　ＸＵ　Ｒｏｎｇ，ＧＯＮＧ｜ｉａｎ．ｘｉｎ，ＸＩＥ　Ｙｕ—ｑｉｎ　（ｘｉｎｊｉａｎｇ　Ｕｒｕｍｑｉ　Ｈｙｄｒｏｌｏｇｉｃａｌ　ａｎｄ　Ｗａｔｅｒ　Ｒｅｓｏｕｒｃｅｓ　ｂｕｒｅａｕ，Ｘｉｎｊｉａｎｇ　８３００００，Ｃｈｉｎａ）　Ａｂｓｔｒａｃｔ：Ｔｈｅ　ｈｙｄｒｏｌｏｇｙ　ｄａｔａ　ｉＳ　ａ　ｎａｔｉｏｎａｌ　ｉｍｐｏｒｔａｎｔ　ｇｒｏｕｎｄｗｏｒｋ　ｉｎｆｏｒｍａｔｉｏｎ　ｒｅｓｏｕｒｃｅｓ．Ｆｏｒ　ｎｉｃｅｌｙ　ａｄａｐｔ　ｔｏ　ｔｈｅ　ｓｉｔｕａｔｉｏｎ　ｏｆ　ｔｈｅ　ｈｙｄｒｏｌｏｇｙ　ｉｎｆｏｒｍａｔｉｏｎ　ａｎｄ　ｍｏｄｅｒｎｉｚｅ　ｔｏ　ｄｅｖｅｌｏｐ，ａｎｄ　ｏｎ　ｔｉｍｅ　ａｃｃｕｒａｔｌｙ　ｐｒｏｖｉｄｅ　ｔｈｅ　ｈｙｄｒｏｌｏｇｙ　ｉｎｆｏｒｍａｔｉｏｎ　ｓｅｒｖｅ，Ｉｔ　ｉＳ　ｖｅｒｙ　ｎｅｃｅｓｓａｒｙ　ｔｏ　ｃｏｎｔｒｏｌ　ｔｈｅ　ＥＸＣＥＬ　ｆｕｎｃｔｉｏｎ　ｏｆ　ｓｏｍｅ　ｃｏｍｐｕｔａｔｉｏｎｓ．Ｔｈｉｓ　ｔｅｘｔ　ｉｎｔｒｏｄｕｃｅｄ　ｔｈｅ　ａｐｐｌｉｃａｔｉｏｎ　ｏｆ　ｓｏｍｅ　ｓｐｅｃｉａｌ　ＥＸＣＥＬ　ｆｕｎｃｔｉｏｎ　ｉｎ　ｔｈｅ　ｈｙｄｒｏｌｏｇｙ　ｄａｔａｓ　ｄｉｓｐｏｓａ１．Ｉｔ　ｉＳ　ｉｎ　ｏｒｄｅｒ　ｔｏ　ｍａｋｅ　ｔｈｅ　ｔｅｃｈｎｉｃａｌ　ｐｅｒｓｏｎ　ｇｅｔ　ｉｎｓｐｉｒｅ　ｆｒｏｍ　ＥＸＣＥＬ　ｆｕｎｃｔｉｏｎ　ａｎｄ　ｃｏｎｔｒｏｌ　ｍｏｒｅ　ｆｕｎｃｔｉｏｎｓ　ｔｏ　ａｐｐｌｙ　ｔｏ　ｔｈｅ　ｈｙｄｒｏｌｏｇｙ　ｉｎｆｏｒｍａｔｉｏｎ　ｄｅｖｅｌｏｐｍｅｎｔｓ．　Ｋｅｙ　ｗｏｒｄｓ：ＥＸＣＥＬ　ｆｕｎｃｔｉｏｎ；Ｈｙｄｒｏｌｏｇｉｃａｌ　ｄａｔａｓ；Ａｐｐｌｉｃａｔｉｏｎ　ｌ　引言　说明：　（１）参数可以是数字，或者是包含数字的名　工程水文计算中常涉及到一些特殊函数，如时段洪量　称、数组或引用。　（２）如果数组或引用参数包含文本、逻　计算、ｃｖ值计算、频率分析中的中ｐ计算等，Ｍｉｃｒｏｓｏｆｔ集　辑值或空白单元格，则这些值将被忽略；但包含零值的单　成软件Ｏｆｆｉｃｅ的组件之一Ｅｘｃｅｌ电子表格软件中的　元格将计算在内。　ＳＵＭＰＲＯＤＵＣＴ、ＧＡＭＭＡＤＩＳＴ和Ｎ０ＲＭＳＩＮｖ等函数可以很好地解　偏差平方和Ｃｖ值的计算公式分别为：　决上述水文计算中所遇到的问题。以下以英雄桥水文站数　据为例进行应用介绍。　ＤＥＶＳＱ＝　（朋一　ｌ　２　函数　２．１　ＳＵＭＰＲＯＤＵＣＴ函数　又＝　喜ｘ．　定义：在给定的几组数组中，将数组间对应的元素相　乘，并返回乘积之和。　——（式４）　语法：ＳＵＭＰＲＯＤＵＣＴ（ａｒｒａｙｌ，ａｒｒａｙ２，ａｒｒａｙ３，…）。　式中：　均值：ｘｉ系列值；　ｎ系列个数；Ｃｖ变差系数。　Ａｒｒａｙｌ，ａｒｒａｙ２，ａｒｒａｙ３，．．．为２～３Ｏ个数组，其相应元　２．３　ＧＡ　ＭＡＤＩＳＴ函数　素需要进行相乘并求和。　定义：返回伽玛累积分布函数的反函数。如果Ｐ＝　说明：数组参数必须具有相同的维数，否则，函数　ＧＡＭＭＡＤＩＳＴ（Ｘ…．），则ＧＡ￣ＩＡＩＮＶ（Ｐ…．）＝Ｘ。使用此函　ＳＵＭＰＲＯＤＵＣＴ将返回错误值＃ＶＡＬＵＥ！。函数ＳＵＭＰＲＯＤＵＣＴ将　数可研究可能出现偏态分布的变量。ＧＡＭＭＡＤＩＳＴ函数可计算　非数值型的数组元素作为０处理。　出不同频率对应的①ｐ值。　其计算公式为：　语法：ＧＡＭＭＡＩＮＶ（ｐｒｏｂａｂｉｌｉｔｙ，ａｌｐｈａ，ｂｅｔａ），参数均　ＳＵＭＰＲＯＤＵＣＴ＝　ＸｉＸｊ——（式１）　为数值型。　式中：ｘｉ系列值１；ｘｊ系列值２；ｎ系列个数。　式中：Ｐｒｏｂａｂｉｌｉｔｙ为伽玛分布的概率值，Ｏ≤　２．２　ＤＥＶＳＱ函数　ｐｒｏｂａｂｉｌｉｔｙ≤１：　定义：返回数据点与各自样本平均值偏差的平方和。　Ａｌｐｈａ为分布参数，ａｌｐｈａ＞０；　语法：ＤＥＶＳＱ（ｎｕｍｂｅｒ１，ｎｕｍｂｅｒ２…．）　Ｂｅｔａ为分布参数，ｂｅｔａ＞０。如果ｂｅｔａ＝１，函数　式中：Ｎｕｍｂｅｒｌ，ｎｕｍｂｅｒ２，…为１～３０个需要计算偏　ＧＡＭＭＡＩＮＶ返回标准伽玛分布。　差平方和的参数，也可以不使用这种用逗号分隔参数的形　说明：如果已给定概率值，则ＧＡＭＭＡＩＮＶ使用　式，而用单个数组或对数组的引用。　ＧＡＭＭＡＤＩＳＴ（Ｘ，ａｌｐｈａ，ｂｅｔａ，ＴＲＵＥ）＝ｐｒｏｂａｂｉ　１　ｉ　ｔｙ求解　收稿日期：２００９—１２—０９修回日期：２００９—１２—２５　作者简介：徐荣（１　９６４一），女，汉族，陕西灞桥籍，高级工，主要从事水文测验、资料整编和工程水文分析研究。　２８　广＿］　垄堕　数值Ｘ。因此，ＧＡＭＭＡＩＮＶ的精度取决于ＧＡＭＭＡＤＩＳＴ的精　度。ＧＡＭＭＡＩＮＶ使用迭代搜索技术。如果搜索在１００次迭代之　２／Ｃ￥３得到不同频率（重现期）　ｐ／ｆ，在Ｄ４单元格中输入　公式＝１＋￥Ｃ￥２木Ｃ４即得到相应的Ｋｐ值。　２：Ｃ　弱　＾　薯　Ｃ　Ｄ　葛　Ｇ　珏　后没有收敛，则函数返回错误值＃Ｎ／Ａ。　Ｐ、Ｋｐ计算公式分别为：　中Ｄ：Ｃ＿ＡＧＡＭＭＡＩＮ　（１一尸４１）一—２ｌ　序警　掣份　军径流量　蒹　嘞　蓑列　系列　偏差平　蚜值　方和　平方　狠　Ｃｖ　，　，２　Ｃｓ——（式５）　２　３　４　ｉ　２　３　糊　２ｏｏＴ　２∞８　２．０ｇ５　３　０ｌ１　１　９１４　式中：中ｐ为离均系数；Ｐ为频率，　（％）；Ｃｓ为偏态系　数。　给定某一Ｐ和Ｃｓ，用式５就可求得相应的中ｐ值。　Ｋｐ＝ｌ＋Ｃｖ＊中Ｐ——（式６）　式中：Ｋｐ为模比系数；ｃｖ为变差系数。　给定某一Ｃｖ，用式６就可求得相应的Ｋｐ值。　３　在工程水文计算中的应用举倒　３．１时段洪量计算示例　水文数据处理时常用到时段洪量的计算，　ＳＵＭＰＲＯＤＵＣＴ函数方便地解决了这个问题，表ｌ为英雄桥水文　站２００５年７月１５日瞬时流量过程，在单元格Ｄ１６栏输入公式　＝ＳＵＭＰＲＯＤＵＣＴ（Ｄ２：Ｄ１５，Ｅ２：Ｅｌ５）／４８×８．６４，表示Ｏ、Ｅ两个数　组的所有元素对应相乘，然后把乘积相加，即（３６．２×３＋　３７．４×４＋…＋８５．３×８＋７９．４×４）／４８×８．６４＝９４０．９×　１０４ｍｓ，就以瞬时流量过程计算得到一目洪量。　Ｄ１６　＝Ｓ　Ｐ鼢Ｄ诫　０眨：Ｅｌ　５ＪＤ２：Ｄｔｓ）／￣Ｘ８．６４　＾　Ｂ　Ｃ　Ｄ　嚣　Ｆ　１　月　日　豺分　流量‘　）　时闻麓　２　１５　０　３６　２　３　３　３　３７　４　《　哇　４　４１　６　３　Ｓ　８　ｌ２５　薯　６　８　ｌ８５　２，Ｓ　７　８：３０　１９７　１．了　８　９：４２　１９７　１．Ｓ　９　１０　ｌ１７　０　Ｃａ　１０　１０：３６　２４９　１　８　ｌｌ　ｌ１：３６　１４６　２　ｌ　１２　１２：４２　１５０　４　４　３　１６　１０７　７．３　１４　２０　８５　３　８　１５　１６　０　伯．４　４　１６　洪最　ｅ唾０．ｅ　表１英雄桥水文站时段洪量计算表　３．２　Ｃｖ值计算示例　在工程水文分析中须进行系列代表性分析，为检验设　计站年径流量系列的代表性，可计算参　站长系列和不同　时段短系列的统计参数，计算方法为从系列终点（即　２００８年）向前依次计算不同时段年径流量系列的平均值和　Ｃｖ值。在表２Ｆ６单元格公式栏中输入公式：　＝ＤＥＶＳＱ（￥Ｃ￥２：Ｃ６，Ｅ６）计算，表示Ｃ２到Ｃ６每个系列与Ｅ６均值　差的平方和，在Ｇ６单元格输入公式：＝ＳＱＲＴ（Ｆ６／（Ａ６—１））求　偏差平方和的平方根，在Ｈ６单元格输入公式：＝Ｇ６／Ｅ６计算　出不同时段年径流量系列的Ｃｖ值。　３．３中Ｄ值计算示例　凡进行过历史洪水调查的几乎都做过重现期分析，但　历史洪水重现期不全是有规律的整数，在　ｐ值查算表上又　不易查到，手工计算和插补既繁琐又不准确。ＧＡＭＭＡＩＮＶ函　数能够解决无规律重现期的中ｐ值计算问题。在表３Ｃ４单元　格中输入公式＝Ｃ￥３／２．ＧＡＭＭＡＩＮＶ（１－ａ４／ｌｏｏ，４／Ｃ￥３　２，ｉ）一　Ｓ　４　２ｏ０５　２　３７５　Ｂ　Ｓ　０　４１９　２ＯＯ４　２　２３７　１１　８３２　２　３２６　Ｏ　Ｔ　７　ｌ　０．１８　０　３７５　Ｂ　２∞３　２　３６ｌ　ｌ３　９９３　２　３３２　Ｏ　７０３６　Ｏ　ｌ６　８　７　０　３５８　２００２　２　６０９　ｌＢ．６０２　２　３７２　０．７６９３　ｌ　０　１５　售　０．４０Ｏ　８　２∞ｌ　１　７３８　１８．３４０　２　２ｅ３　１　ｌ２ＯＴ　１　０　ｌ７　９　０．４０Ｓ　０　２０００　２　Ｔ８４　２１．１　２　３４５　１　３１８３　ｅ　Ｏ　ｌ７　ｌ　１０　０　３９尊　ｌ　ｌ９９９　２，　ｏ８　２３　８１２　２　３８ｌ　ｌ　４３７Ｏ　６　０　ｌ　５　４孽　０　３６４　Ｏ　ｌ鸲ｏ　２　０４１　Ｈ８《譬７　２　４１８　８　３６７７　２　Ｏ　ｌＳ　Ｓ　５０　０　３６２　ｌ　ｉ鲁５９　２．７１５　１２１　２１２　２　４２４　６　４５３９　ｇ　０　ｌ５　Ｓ　６１　０．３６３　２　Ｉ瞄　２．８０１　１２４　０１３　２《３２　Ｂ．５ｇ３ｌ　１　０　１５　表２英雄桥水文站年径流量ＣＶ值计算表　Ｍ　Ｆｘ　＝Ｃ￥３／２．￣（１－Ａ￣ｔｏｏ，４，ｃ￥３‘２，１）　２／ｃ￥３　＾　Ｂ　ｅ　Ｄ　ｌ　既）　熏觋期　尊　１踅　轴位　（年’　２　Ｃｙ　１．０　３　Ｃｓ　５　０　４　０　１　ｌ０００　孽．２２０　ｌ０　２２０　５　Ｏ２　５００　７　７７１　８．７７１　，６　０．３３３　３０ｏ　６　Ｔ３０　７．了３Ｏ　７　０５　２００　Ｓ　９１６　６．９１６　．８　ｌ　１００　４　５７３　５　５７３　ｇ　２　５０　３　３００　４　３∞　ｌ　３０　２　４２４　３Ｏ　３　３３　．４２４　ｌ　２Ｏ　ｉ　７７３　２　７７３　ｌ　Ｓ　１　ｌ０　０　７９５　ｌ　Ｔ鼋５　２　１０　ｌ　５　Ｏ　０Ｓ８　１　Ｏ５８　３　２尊　表３不同频率（重现期）中ｐ值、ＫＤ值计算表　４　结语　本文给出的函数通过大量试算，考虑到了各种不同类　型的数据，没有发现明显的问题。当然，水平有限，而且　只是笔者的一点经验之谈，旨在抛砖引玉，但愿能让广大　水文专业技术人员从中得到启发，掌握更多的函数应用到　水文信息化建设上来，欢迎大家提出宝贵意见。　参考文献：　［１］河海大学，武汉大学．工程水文学［Ｍ］．中国水利水电出版　社，２０００：１１７～ｌ１９．　［２］南京大学，中山大学．普通水文学［Ｍ］．人民教育出版　社，１９７８：７９～１６６．　［３］Ｏｆｆｉｃｅ２０００中文版入门与提高［Ｍ］．北京：清华大学出版　社．２００６：１１３￣１４６．　［４］长江流域办水文处．水文计算图表表［Ｍ］．１９８０：１～３Ｏ．　２９