□王桂青
收稿日期:2020-12-07
作者简介：王桂青，女，汉族，唐山市陡河水库事务中心，高级工程师。
摘要渗流量资料是陡河水库工程管理的重要资料，要求观测数据整编快速而准确，图形绘制符合规范且便于研究。通过灵活运用Excel 的函数功能，实现对数据的汇总统计，运用Excel 的强大绘图功能，绘制的图形有效用于工程运行状况分析。
关键词渗流量；过程线；观测数据
渗流量资料是研究大坝是否安全的重要数据，是水库工程管理的重要基础资料之一。陡河水库渗流量资料整编包括梯形堰和三角堰观测数据的整编分析。按照《土石坝安全监测技术规范》（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值为#VAL
UE 或0，Match 函数是对单元格区域的引用，生成了内存数组，Index 函数使用了以数组形式返回的值，输入函数后要按Ctrl+Shift+Enter 。Match 查到的第一个小于或等于0的最大值，位置为B22，然后由Index 函数返回其对应的值。
Da 列数据以C11为例说明其函数的意义。3.9=LOOKUP(1,0/B2:B11,B2:
47
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区间的非零值。
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.3Di列函数的意义
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是工程技术人员广泛应用的
办公软件，通过合理设置函数的参数，
快速而准确地完成了陡河水库渗流量
资料整编的表格绘制，日渗流量计算和
图形绘制。又不需要编程和程序维护，
便于在工程中推广应用。□
表12019年渗流量内差资料
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
A
日期
2019/1/1
2019/1/2
2019/1/3
2019/1/4
2019/1/5
2019/1/6
2019/1/7
2019/1/8
2019/1/9
2019/1/10
2019/1/11
2019/1/12
2019/1/13
2019/1/14
2019/1/15
2019/1/16
2019/1/17
2019/1/18
2019/1/19
2019/1/20
2019/1/21
2019/1/22
2019/1/23
B
实测
3.90
4.13
C
Db
4.13
4.13
4.13
4.13
4.13
4.13
4.13
4.13
4.13
4.13
4.13
4.36
4.36
D
Da
3.90
3.90
3.90
3.90
3.90
3.90
3.90
3.90
3.90
3.90
3.90
4.13
4.13
4.13
E
Di
3.90
3.90
3.90
3.90
excel中index函数的使用方法3.90
3.90
3.90
3.90
3.90
3.90
3.92
3.94
3.96
3.98
4.00
4.02
4.04
4.06
4.08
4.11
4.13
4.15
4.18
48

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。