EExxcceell在陡河水库渗流量资料整编中的应用

□王桂青

渗流量资料是研究大坝是否安全的重要数据，是水库工程管理的重要基础资料之一。陡河水库渗流量资料整编包括梯形堰和三角堰观测数据的整编分析。按照《土石坝安全监测技术规范》（SL551-2012）的要求，陡河水库渗流量数据每10d观测1次，并且随观测随计算并录入电脑。由于渗流水量暴露于地表，观测数据受降水等自然因素影响很大，观测时间遇节假日或降雨可提前或错后1～2d。陡河水库的观测资料每年整编1次，渗流量资料整编内容包括观测数据整理制表、日渗流量的计算、渗流总量统计和图形绘制。Ex⁃cel具有强大的数据统计和绘图功能，可用于渗流量资料整编中。陡河水库资料整编用的是Excel2010版本。

1.陡河水库渗流量资料特点

1.1 日渗流量的假定

实测数据为1d的实时渗流量，由于流量在1d内变化不大，假定1d内任一时间都保持这一流量不变，没有实测流量的日数用两次相邻的实测流量数据内差。

1.2 观测时间不规律

由于自然因素的影响，渗流量资料不是严格的10d1次，这给数据内差带来不便。

1.3 实测数据缺失

由于受结冰影响，无法测量结冰时的堰上水头，造成年报表数据缺失。

1.4 图形绘制复杂

图形绘制中各序列单位不一致，很难在一张图上绘制出渗流量过程线。

2.年报表绘制与数据统计

2.1 年报表绘制

原始观测数据按测次连续记录，日期和观测数据各为一列，并一一对应。统计表是以月份为列，日期为行制成的行列交叉统计表。如果人工录入费时费力，还容易出错。用Match和Index组合函数可以查找到对应的数据。年报表整编步骤如下：第一步制作一个与年报表格式一致的日期表格，并在行列交叉处填写对应日期，数据包括这一年的全部日期，以备Match函数查找，日期整编录入第一行数据，其余数据由Excel自动填充生成。第二步在统计表的第一行第一列录入函数，函数由In⁃dex和Match组成。Match函数找到日期表格中与统计表对应位置的日期在原始观测数据日期列中的行位置，Index函数在原始观测数据列中对Match函数返回的行位置进行查找，返回日期表格第一行第一列对应的观测值。在Match函数中match_type参数为0，查找等于所查日期在原始观测数据中的位置，如果Match函数查找这一日期不成功，Iserror函数返回空值。函数对原始观测数据日期列单元格地址使用绝对引用。第三步，选中第二步单元格的数据，向下自动填充第一列，再选中第一列，向右自动填充其余11列。年报表自动生成。

2.2 日渗流量计算

2.2.1 内差公式

全年的渗流总量是由每1d的渗流总量汇总得到的，原始观测数据10d1次，没有实测数据的日数采用在两次观测值之间内差得到该区间每1d的渗流量数据，第1个观测日之前和最后1个观测日之后的数据，假定等于离他最近的1次观测数据。

内差公式为:Di=(Db-Da)÷(Dateb-Datea)×((Datei-Datea)+Da

式中：

Di—需要内差的数据；

Db—后一观测数据；

Da—前一观测数据；

Dateb—后一观测数据对应的日期；

Datea—前一观测数据对应的日期；

Datei—需要内差数据对应的日期。

2.2.2 内差值计算

以2019年为例,渗流量内差资料如表1，表1中A列日期是自动填充的。B列为原始观测数据，采用与制作年报表相同的数据生成方法，在第一行输入函数，再往下自动填充。为了便于求内差值，将每个日期对应的Db、Da值分别放于 C、D列，Db、Da值用 Excel函数的查找功能生成第一个数据，再利用Excel的自动填充功能生成其余数据。E列为每日渗流量，先用公式计算出E12，再往下自动填充至E356。E2～E11等于E11，E356以后的数据等于E356。表1仅截取2019年部分数据。

Db列数据以C12为例说明其函数的意义。4.13=INDEX(B12:B361,MATCH(,0/B12:B361,))，公 式 中 Match函数是查找B列从B12开始第一个非零值位置，Match函数中第一个逗号之前的lookup_value参数省略为0，第二个逗号之后的match_type参数省略为1，默认按升序排列。0/B12:B361值为#VALUE或0，Match函数是对单元格区域的引用，生成了内存数组，Index函数使用了以数组形式返回的值，输入函数后要按Ctrl+Shift+Enter。Match查找到的第一个小于或等于0的最大值，位置为B22，然后由Index函数返回其对应的值。

Da列数据以C11为例说明其函数的意义。3.9=LOOKUP(1,0/B2:B11,B2:B11)，Lookup 函数中参数 lookup_vector为 0/B2:B11，值为 0或#VALUE，#VAL⁃UE是错误值，会被忽略，0/B1:B11默认按升序排序，Lookup找不到lookup_val⁃ue值1，则它与lookup_vector中小于或等于1的最大值匹配，这个最大值为0，查找到B11，LOOKUP函数的作用是找到B2:B11区间的非零值。

表1 2019年渗流量内差资料

Db列Match函数与Da列Lookup函数的参数都有lookup_value，其作用都是为函数找到小于或等于lookup_value的最大数值，但在Match函数中取0值，在Lookup函数中取1值。原因是两个函数查找的目的不同，Match函数查找的是序列中第一个非零值，Lookup函数查找的是序列中最后一个非零值。两个函数序列中都有0值，所以Match函数找到第一个0值就是最大值，做为查找结果，而Lookup函数找不到1值，就一直找到序列末尾，把最后一个0值做为最大值。Db列和Da列函数区间的选取也不同，考虑到观测时间不固定，从第一个查找值开始往后间隔尽量大，以免观测时间间隔过长，查找出现错误，在C12单元格Match函数从B12开始至B361，但是区间过大就包括多个观测数据，lookup_value参数选取0就可以找到B列区间从B12开始第一个非零值。Lookup函数区间的选定，是从B11向前尽量大，可以在表格前端插入空行以扩大序列间隔，以确保序列中含有实测值。lookup_value参数选取1，可以查找到B列区间从最前端开始到B11的最后一个非零值。

2.2.3 Di列函数的意义

Di列数据以E12为例说明其函数的意义。3.92＝=(A12-LOOKUP(1,0/B2:B11,A2:A11))÷(INDEX(A12:A67,MATCH(,0/B12:B67,))-LOOKUP(1,0/B2:B11,A2:A11))×(C12-D12)+D12，式中LOOKUP(1,0/B2:B11,A2:A11)，用 来 查找实测列指定区间最后一个不为零的值对应的A列日期，其函数意义与查找Da列数据类似。INDEX(A12:A67,MATCH(C12,B12:B67,)，用来查找实测列指定区间第一个不为零的值对应的A列日期，其函数意义与查找Db列数据类似。查找到内差所需日期后，用公式求出日渗流量。

2.3 数据统计

有了每天的渗流量数值，就可以利用Sum函数在每月月末统计出当月渗流总量，然后将各月渗流总量相加汇总得出全年渗流总量。在年报表各月末用Max和Min函数统计出各月渗流最大值和最小值，进一步统计出全年最大和最小值。

3.图形绘制

渗流量过程线图是以日期为分类轴，数值轴包括库水位、降水量和渗流量3个系列，这3个系列单位各不一致。采取将两个图形横向并列放在一起的方法绘制出规范要求的过程线。两个图形都是以日期为分类轴，上面图形主坐标轴是库水位，次坐标轴是降水量，下面图形用一个主坐标轴绘制渗流量。

4.结语

Excel是工程技术人员广泛应用的办公软件，通过合理设置函数的参数，快速而准确地完成了陡河水库渗流量资料整编的表格绘制，日渗流量计算和图形绘制。又不需要编程和程序维护，便于在工程中推广应用。