EExxcceell在陡河水庫(kù)滲流量資料整編中的應(yīng)用

□王桂青

滲流量資料是研究大壩是否安全的重要數(shù)據(jù)，是水庫(kù)工程管理的重要基礎(chǔ)資料之一。陡河水庫(kù)滲流量資料整編包括梯形堰和三角堰觀測(cè)數(shù)據(jù)的整編分析。按照《土石壩安全監(jiān)測(cè)技術(shù)規(guī)范》（SL551-2012）的要求，陡河水庫(kù)滲流量數(shù)據(jù)每10d觀測(cè)1次，并且隨觀測(cè)隨計(jì)算并錄入電腦。由于滲流水量暴露于地表，觀測(cè)數(shù)據(jù)受降水等自然因素影響很大，觀測(cè)時(shí)間遇節(jié)假日或降雨可提前或錯(cuò)后1～2d。陡河水庫(kù)的觀測(cè)資料每年整編1次，滲流量資料整編內(nèi)容包括觀測(cè)數(shù)據(jù)整理制表、日滲流量的計(jì)算、滲流總量統(tǒng)計(jì)和圖形繪制。Ex?cel具有強(qiáng)大的數(shù)據(jù)統(tǒng)計(jì)和繪圖功能，可用于滲流量資料整編中。陡河水庫(kù)資料整編用的是Excel2010版本。

1.陡河水庫(kù)滲流量資料特點(diǎn)

1.1 日滲流量的假定

實(shí)測(cè)數(shù)據(jù)為1d的實(shí)時(shí)滲流量，由于流量在1d內(nèi)變化不大，假定1d內(nèi)任一時(shí)間都保持這一流量不變，沒(méi)有實(shí)測(cè)流量的日數(shù)用兩次相鄰的實(shí)測(cè)流量數(shù)據(jù)內(nèi)差。

1.2 觀測(cè)時(shí)間不規(guī)律

由于自然因素的影響，滲流量資料不是嚴(yán)格的10d1次，這給數(shù)據(jù)內(nèi)差帶來(lái)不便。

1.3 實(shí)測(cè)數(shù)據(jù)缺失

由于受結(jié)冰影響，無(wú)法測(cè)量結(jié)冰時(shí)的堰上水頭，造成年報(bào)表數(shù)據(jù)缺失。

1.4 圖形繪制復(fù)雜

圖形繪制中各序列單位不一致，很難在一張圖上繪制出滲流量過(guò)程線。

2.年報(bào)表繪制與數(shù)據(jù)統(tǒng)計(jì)

2.1 年報(bào)表繪制

原始觀測(cè)數(shù)據(jù)按測(cè)次連續(xù)記錄，日期和觀測(cè)數(shù)據(jù)各為一列，并一一對(duì)應(yīng)。統(tǒng)計(jì)表是以月份為列，日期為行制成的行列交叉統(tǒng)計(jì)表。如果人工錄入費(fèi)時(shí)費(fèi)力，還容易出錯(cuò)。用Match和Index組合函數(shù)可以查找到對(duì)應(yīng)的數(shù)據(jù)。年報(bào)表整編步驟如下：第一步制作一個(gè)與年報(bào)表格式一致的日期表格，并在行列交叉處填寫(xiě)對(duì)應(yīng)日期，數(shù)據(jù)包括這一年的全部日期，以備Match函數(shù)查找，日期整編錄入第一行數(shù)據(jù)，其余數(shù)據(jù)由Excel自動(dòng)填充生成。第二步在統(tǒng)計(jì)表的第一行第一列錄入函數(shù)，函數(shù)由In?dex和Match組成。Match函數(shù)找到日期表格中與統(tǒng)計(jì)表對(duì)應(yīng)位置的日期在原始觀測(cè)數(shù)據(jù)日期列中的行位置，Index函數(shù)在原始觀測(cè)數(shù)據(jù)列中對(duì)Match函數(shù)返回的行位置進(jìn)行查找，返回日期表格第一行第一列對(duì)應(yīng)的觀測(cè)值。在Match函數(shù)中match_type參數(shù)為0，查找等于所查日期在原始觀測(cè)數(shù)據(jù)中的位置，如果Match函數(shù)查找這一日期不成功，Iserror函數(shù)返回空值。函數(shù)對(duì)原始觀測(cè)數(shù)據(jù)日期列單元格地址使用絕對(duì)引用。第三步，選中第二步單元格的數(shù)據(jù)，向下自動(dòng)填充第一列，再選中第一列，向右自動(dòng)填充其余11列。年報(bào)表自動(dòng)生成。

2.2 日滲流量計(jì)算

2.2.1 內(nèi)差公式

全年的滲流總量是由每1d的滲流總量匯總得到的，原始觀測(cè)數(shù)據(jù)10d1次，沒(méi)有實(shí)測(cè)數(shù)據(jù)的日數(shù)采用在兩次觀測(cè)值之間內(nèi)差得到該區(qū)間每1d的滲流量數(shù)據(jù)，第1個(gè)觀測(cè)日之前和最后1個(gè)觀測(cè)日之后的數(shù)據(jù)，假定等于離他最近的1次觀測(cè)數(shù)據(jù)。

內(nèi)差公式為:Di=(Db-Da)÷(Dateb-Datea)×((Datei-Datea)+Da

式中：

Di—需要內(nèi)差的數(shù)據(jù)；

Db—后一觀測(cè)數(shù)據(jù)；

Da—前一觀測(cè)數(shù)據(jù)；

Dateb—后一觀測(cè)數(shù)據(jù)對(duì)應(yīng)的日期；

Datea—前一觀測(cè)數(shù)據(jù)對(duì)應(yīng)的日期；

Datei—需要內(nèi)差數(shù)據(jù)對(duì)應(yīng)的日期。

2.2.2 內(nèi)差值計(jì)算

以2019年為例,滲流量?jī)?nèi)差資料如表1，表1中A列日期是自動(dòng)填充的。B列為原始觀測(cè)數(shù)據(jù)，采用與制作年報(bào)表相同的數(shù)據(jù)生成方法，在第一行輸入函數(shù)，再往下自動(dòng)填充。為了便于求內(nèi)差值，將每個(gè)日期對(duì)應(yīng)的Db、Da值分別放于 C、D列，Db、Da值用 Excel函數(shù)的查找功能生成第一個(gè)數(shù)據(jù)，再利用Excel的自動(dòng)填充功能生成其余數(shù)據(jù)。E列為每日滲流量，先用公式計(jì)算出E12，再往下自動(dòng)填充至E356。E2～E11等于E11，E356以后的數(shù)據(jù)等于E356。表1僅截取2019年部分?jǐn)?shù)據(jù)。

Db列數(shù)據(jù)以C12為例說(shuō)明其函數(shù)的意義。4.13=INDEX(B12:B361,MATCH(,0/B12:B361,))，公 式 中 Match函數(shù)是查找B列從B12開(kāi)始第一個(gè)非零值位置，Match函數(shù)中第一個(gè)逗號(hào)之前的lookup_value參數(shù)省略為0，第二個(gè)逗號(hào)之后的match_type參數(shù)省略為1，默認(rèn)按升序排列。0/B12:B361值為#VALUE或0，Match函數(shù)是對(duì)單元格區(qū)域的引用，生成了內(nèi)存數(shù)組，Index函數(shù)使用了以數(shù)組形式返回的值，輸入函數(shù)后要按Ctrl+Shift+Enter。Match查找到的第一個(gè)小于或等于0的最大值，位置為B22，然后由Index函數(shù)返回其對(duì)應(yīng)的值。

Da列數(shù)據(jù)以C11為例說(shuō)明其函數(shù)的意義。3.9=LOOKUP(1,0/B2:B11,B2:B11)，Lookup 函數(shù)中參數(shù) lookup_vector為 0/B2:B11，值為 0或#VALUE，#VAL?UE是錯(cuò)誤值，會(huì)被忽略，0/B1:B11默認(rèn)按升序排序，Lookup找不到lookup_val?ue值1，則它與lookup_vector中小于或等于1的最大值匹配，這個(gè)最大值為0，查找到B11，LOOKUP函數(shù)的作用是找到B2:B11區(qū)間的非零值。

表1 2019年滲流量?jī)?nèi)差資料

Db列Match函數(shù)與Da列Lookup函數(shù)的參數(shù)都有l(wèi)ookup_value，其作用都是為函數(shù)找到小于或等于lookup_value的最大數(shù)值，但在Match函數(shù)中取0值，在Lookup函數(shù)中取1值。原因是兩個(gè)函數(shù)查找的目的不同，Match函數(shù)查找的是序列中第一個(gè)非零值，Lookup函數(shù)查找的是序列中最后一個(gè)非零值。兩個(gè)函數(shù)序列中都有0值，所以Match函數(shù)找到第一個(gè)0值就是最大值，做為查找結(jié)果，而Lookup函數(shù)找不到1值，就一直找到序列末尾，把最后一個(gè)0值做為最大值。Db列和Da列函數(shù)區(qū)間的選取也不同，考慮到觀測(cè)時(shí)間不固定，從第一個(gè)查找值開(kāi)始往后間隔盡量大，以免觀測(cè)時(shí)間間隔過(guò)長(zhǎng)，查找出現(xiàn)錯(cuò)誤，在C12單元格Match函數(shù)從B12開(kāi)始至B361，但是區(qū)間過(guò)大就包括多個(gè)觀測(cè)數(shù)據(jù)，lookup_value參數(shù)選取0就可以找到B列區(qū)間從B12開(kāi)始第一個(gè)非零值。Lookup函數(shù)區(qū)間的選定，是從B11向前盡量大，可以在表格前端插入空行以擴(kuò)大序列間隔，以確保序列中含有實(shí)測(cè)值。lookup_value參數(shù)選取1，可以查找到B列區(qū)間從最前端開(kāi)始到B11的最后一個(gè)非零值。

2.2.3 Di列函數(shù)的意義

Di列數(shù)據(jù)以E12為例說(shuō)明其函數(shù)的意義。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)，用 來(lái) 查找實(shí)測(cè)列指定區(qū)間最后一個(gè)不為零的值對(duì)應(yīng)的A列日期，其函數(shù)意義與查找Da列數(shù)據(jù)類(lèi)似。INDEX(A12:A67,MATCH(C12,B12:B67,)，用來(lái)查找實(shí)測(cè)列指定區(qū)間第一個(gè)不為零的值對(duì)應(yīng)的A列日期，其函數(shù)意義與查找Db列數(shù)據(jù)類(lèi)似。查找到內(nèi)差所需日期后，用公式求出日滲流量。

2.3 數(shù)據(jù)統(tǒng)計(jì)

有了每天的滲流量數(shù)值，就可以利用Sum函數(shù)在每月月末統(tǒng)計(jì)出當(dāng)月滲流總量，然后將各月滲流總量相加匯總得出全年滲流總量。在年報(bào)表各月末用Max和Min函數(shù)統(tǒng)計(jì)出各月滲流最大值和最小值，進(jìn)一步統(tǒng)計(jì)出全年最大和最小值。

3.圖形繪制

滲流量過(guò)程線圖是以日期為分類(lèi)軸，數(shù)值軸包括庫(kù)水位、降水量和滲流量3個(gè)系列，這3個(gè)系列單位各不一致。采取將兩個(gè)圖形橫向并列放在一起的方法繪制出規(guī)范要求的過(guò)程線。兩個(gè)圖形都是以日期為分類(lèi)軸，上面圖形主坐標(biāo)軸是庫(kù)水位，次坐標(biāo)軸是降水量，下面圖形用一個(gè)主坐標(biāo)軸繪制滲流量。

4.結(jié)語(yǔ)

Excel是工程技術(shù)人員廣泛應(yīng)用的辦公軟件，通過(guò)合理設(shè)置函數(shù)的參數(shù)，快速而準(zhǔn)確地完成了陡河水庫(kù)滲流量資料整編的表格繪制，日滲流量計(jì)算和圖形繪制。又不需要編程和程序維護(hù)，便于在工程中推廣應(yīng)用。