EXCEL內(nèi)置函數(shù)在水文分析計(jì)算中的應(yīng)用

張國(guó)輝

（山西省晉中市水文水資源勘測(cè)局 山西晉中030600）

Excel提供了豐富的內(nèi)置函數(shù)，可以簡(jiǎn)便、快捷地進(jìn)行各種數(shù)據(jù)運(yùn)算、處理、分析。如果能利用Excel提供的內(nèi)置函數(shù)，結(jié)合一些VBA基本語言和邏輯關(guān)系，編寫內(nèi)嵌式程序，將其應(yīng)用于實(shí)際工作，從而可以大大提高工作效率，使數(shù)據(jù)處理自動(dòng)化、簡(jiǎn)單化、直觀化。同樣可以充分利用它進(jìn)行大量的水文數(shù)據(jù)的分析、計(jì)算處理，使我們的工作達(dá)到事半功倍的效果。為此，本文就以現(xiàn)在使用最廣的Excel2003為基礎(chǔ)，與大家共同分享和探討它在我們?nèi)粘Ｋ墓ぷ髦械囊恍?yīng)用。

1 Excel內(nèi)置函數(shù)的調(diào)用和認(rèn)識(shí)

無論是 Excel2000、Excel2003、還是 Excel2007，只要能夠打開和使用這些辦公自動(dòng)化軟件的操作平臺(tái)，均無特殊要求。

1.1 內(nèi)置函數(shù)的調(diào)用

Excel有些內(nèi)置函數(shù)默認(rèn)是不可以使用的。為此，就需要打開并能夠調(diào)用它們。任意打開一個(gè)Excel工作表，單擊：工具→加載宏，出現(xiàn)加載宏對(duì)話框，依次打勾選擇，并確定之后，內(nèi)置函數(shù)便可進(jìn)行調(diào)用了。

內(nèi)置函數(shù)一經(jīng)調(diào)用，只要不再執(zhí)行關(guān)閉操作，則在本機(jī)其他工作表中也可同樣使用。圖1為內(nèi)置函數(shù)的調(diào)用。

圖1 內(nèi)置函數(shù)的調(diào)用

1.2 內(nèi)置函數(shù)的位置

Excel內(nèi)置函數(shù)的位置，可以在命令按鈕中點(diǎn)擊：“插入→函數(shù)”，在選擇類別中選擇“全部”，即可找到并應(yīng)用Excel全部的內(nèi)置函數(shù)。圖2為內(nèi)置函數(shù)的位置。

圖2 內(nèi)置函數(shù)的位置

1.3 幾個(gè)重要函數(shù)

Excel內(nèi)置函數(shù)中有幾個(gè)函數(shù)，是水文分析和計(jì)算中經(jīng)常用到的重要函數(shù)，特別加以說明。

①IF函數(shù)：基本語句為IF(logical_test,[value_if_true],[value_if_false])。其中l(wèi)ogical_test,為判定條件。如果指定條件的計(jì)算結(jié)果為TRUE，IF函數(shù)將返回某個(gè)值；如果該條件的計(jì)算結(jié)果為FALSE，則返回另一個(gè)值。

在Excel2003中最多可以使用 64個(gè)IF函數(shù)作為value_if_true和value_if_false參數(shù)進(jìn)行嵌套以構(gòu)造更詳盡的測(cè)試，這對(duì)于我們一般進(jìn)行的水文計(jì)算已經(jīng)足夠了。

②MATCH函數(shù)：基本語句為MATCH(lookup_value,lookup_array,match_type)。其中Lookup_value為需要在數(shù)據(jù)表中查找的數(shù)值。Lookup_array可能包含所要查找的數(shù)值的連續(xù)單元格區(qū)域，Lookup_array應(yīng)為數(shù)組或數(shù)組引用。Match_type為數(shù)字 -1、0或 1。Match-type指明 Microsoft Excel如何在lookup_array中查找 lookup_value。

如果 match_type為 0，函數(shù) MATCH查找等于lookup_value的第一個(gè)數(shù)值。Lookup_array可以按任何順序排列。這也是水文分析與計(jì)算中常用的參數(shù)，其它參數(shù)在此不做詳述。

③ISEVEN函數(shù)：基本語句為ISEVEN(number)。Number待測(cè)試的數(shù)值，如果參數(shù) number為偶數(shù)，返回 TRUE，否則返回FALSE。如果參數(shù)值不是整數(shù)，則截尾取整。

④ROUND函數(shù)：基本語句為ROUND(number,num_digits)。其中Number為需要進(jìn)行四舍五入的數(shù)字。Num_digits指定的位數(shù)，按此位數(shù)進(jìn)行四舍五入。返回某個(gè)數(shù)字按指定位數(shù)取整后的數(shù)字。

⑤MOD函數(shù)：基本語句為MOD(number,divisor)。返回兩數(shù)相除的余數(shù)。結(jié)果的正負(fù)號(hào)與除數(shù)相同。其中Number為被除數(shù)。Divisor為除數(shù)。

⑥QUOTIENT函數(shù)：基本語句為QUOTIENT(numerator,denominator)。返回商的整數(shù)部分，該函數(shù)可用于舍掉商的小數(shù)部分。其中Numerator為被除數(shù)。Denominator為除數(shù)。

⑦其他一些簡(jiǎn)單常用的基本函數(shù)：SUM()、MIN()、MAX()、ISBLANK()等在此就不再贅述。

2 Excel內(nèi)置函數(shù)在水文分析與計(jì)算中的應(yīng)用

2.1 水文數(shù)據(jù)修約基本法則“四舍六入”的程序設(shè)計(jì)思路

根據(jù)現(xiàn)在實(shí)行的中華人民共和國(guó)行業(yè)標(biāo)準(zhǔn)《水文資料整編規(guī)范》SL247—1999[1]，要求所有水文資料整編成果取用精度位數(shù)的后一位數(shù)字，采用“四舍六入”方法取舍。所以，“四舍六入”數(shù)據(jù)修約是水文資料整編中的一個(gè)重要環(huán)節(jié)，它貫穿于所有水文數(shù)據(jù)計(jì)算過程。

具體就是：取用精度位數(shù)后一位數(shù)字小于五者則舍，大于五者則入，等于五時(shí)若其后有非零尾數(shù)仍入，無非零尾數(shù)則視取用的末位數(shù)字的奇偶取舍，末位數(shù)字為奇則入，末位數(shù)字為偶則舍，其流程框圖如圖3[2]所示。

圖3 “四舍六入”修約法程序流程框圖

2.2 應(yīng)用實(shí)例——水位、水溫測(cè)驗(yàn)統(tǒng)計(jì)校核計(jì)算表

鑒于水文分析與計(jì)算問題的多樣性，以及EXCEL應(yīng)用的廣泛性，僅以《水位、水溫測(cè)驗(yàn)統(tǒng)計(jì)校核計(jì)算表》為例，來說明其具體應(yīng)用步驟和方法。

2.2.1 設(shè)計(jì)EXCEL表格形式

根據(jù)實(shí)際應(yīng)用，設(shè)計(jì)EXCELL表格《水位、水溫測(cè)驗(yàn)月統(tǒng)計(jì)校核計(jì)算表》如圖4所示。

2.2.2 在EXCEL統(tǒng)計(jì)表格內(nèi)嵌入公式

在逐日水位表及項(xiàng)目統(tǒng)計(jì)欄內(nèi)，根據(jù)各自的內(nèi)容嵌入計(jì)算公式，并應(yīng)注意小數(shù)位數(shù)按規(guī)范取舍[3]。

2.2.2.1 在逐日水位表內(nèi)嵌入公式

按照水文傳統(tǒng)方式錄入的水位數(shù)據(jù)，在整數(shù)位數(shù)據(jù)相同時(shí)，后面的數(shù)據(jù)省略了整數(shù)部分，從而無法進(jìn)行計(jì)算和統(tǒng)計(jì)。為此，要按如下步驟進(jìn)行必要的轉(zhuǎn)換。

①將D6～D36單元格格式，設(shè)置為文本方式，即可在相應(yīng)單元格內(nèi)按水文習(xí)慣錄入水位數(shù)據(jù)。

圖4 水位、水溫測(cè)驗(yàn)月統(tǒng)計(jì)校核計(jì)算表局部放大圖

②將D6～D36單元格內(nèi)的數(shù)據(jù)，轉(zhuǎn)換為可利用EXCEL表統(tǒng)計(jì)、計(jì)算的格式：

C6=VALUE(D6)

C7=IF(LEN(D7)=2,QUOTIENT(C6,1)+VALUE(D7)/100,VALUE(D7))

C8～C36為C7向下復(fù)制而得。

③為保持工作界面的清潔，將C6～C36單元格的顯示內(nèi)容設(shè)置為不可見（即顯示字體顏色設(shè)為白色）。

2.2.2.2 在項(xiàng)目統(tǒng)計(jì)欄內(nèi)嵌入公式

在向各旬、月項(xiàng)目統(tǒng)計(jì)欄內(nèi)嵌入公式時(shí)應(yīng)注意幾個(gè)問題：

①1、3、5、7、8、10、12 月份，可用 1 月份的公式進(jìn)行復(fù)制；4、6、9、11月份的公式，可用4月份的公式進(jìn)行復(fù)制；2月份的公式為單獨(dú)系列。

②各月上、中旬公式可進(jìn)行復(fù)制。

③水位月統(tǒng)計(jì)之最高、最低及相應(yīng)日期，為日平均值所對(duì)應(yīng)的值及日期，而非瞬時(shí)值。

鑒于篇幅所限，不將公式在此列出，但如有興趣的同志可共同探討此問題。

2.2.3 將設(shè)計(jì)好的EXCEL統(tǒng)計(jì)表格設(shè)置保護(hù)

為了保護(hù)好已輸入的公式，以免被誤操作；保持設(shè)計(jì)好的EXCELL工作表的直觀性；同時(shí)月統(tǒng)計(jì)各項(xiàng)目，是由EXCELL表自動(dòng)計(jì)算所得，不需要人工輸入。所以要設(shè)置單元格保護(hù)，見圖5。

圖5 設(shè)置工作表的保護(hù)

將設(shè)計(jì)好的表式，配合“Ctrl”鍵，選中除代表366日需輸入內(nèi)容的其他部分。再依次按：工具→保護(hù)→保護(hù)工作表→設(shè)置密碼，將工作表進(jìn)行保護(hù)。

2.2.4 保存設(shè)計(jì)好的EXCEL統(tǒng)計(jì)表格

可以有兩種保存方法：一是將設(shè)計(jì)好的表格存為EXCELL模板，另一是命名并保存為EXCELL工作表。在這里均不再一一詳細(xì)敘述。

2.2.5 示例

用義棠站2011年部分月份的數(shù)據(jù)進(jìn)行計(jì)算，結(jié)果如圖6所示。

圖6 計(jì)算實(shí)例

3 結(jié)語

在日常水文分析和計(jì)算工作中，表格化的工作內(nèi)容占據(jù)很大一部分。充分發(fā)揮和利用EXCEL表的強(qiáng)大功能，使水文職工從繁復(fù)、冗雜的計(jì)算工作中解脫出來，提高工作效率，是時(shí)代和社會(huì)發(fā)展的趨勢(shì)和要求。同時(shí)，用EXCEL工作表，處理日常水文分析和計(jì)算，具有直觀性、隨即性的特點(diǎn)，也就同時(shí)滿足了水文“四隨”的工作要求。將水文、數(shù)學(xué)、英語、編程、計(jì)算機(jī)等與實(shí)踐相結(jié)合，不斷地解決工作中遇到的問題，也是水文事業(yè)進(jìn)步的內(nèi)在推動(dòng)力。筆者已將日常水文分析和計(jì)算工作中的含沙量計(jì)算、蒸發(fā)量日表的統(tǒng)計(jì)計(jì)算等設(shè)計(jì)了相應(yīng)的EXCEL工作表，愿與有興趣者互相交流。由于自己水平所限，不妥之處，敬請(qǐng)指正。

［1］中華人民共和國(guó)長(zhǎng)江水利委員會(huì)水文局.SL 247- 1999 水文資料整編規(guī)范［S］.北京：中國(guó)水利水電出版社，2000：82.

［2］張國(guó)輝.水文資料整編中數(shù)字修約方法的程序編制［J］.山西水利科技，2005（2）：89.

［3］中華人民共和國(guó)水利部水文局.SL 460- 2009 水文年鑒匯編刊印規(guī)范［S］.北京：中國(guó)水利水電出版社，2010：19.