楊艷艷,薛慶云,朱 江

(杭州市水文水資源監(jiān)測(cè)總站,浙江 杭州 310016)

1 Excel取代計(jì)算器作為原始數(shù)據(jù)處理工具

1.1 傳統(tǒng)方法的弊端

從事一線(xiàn)水質(zhì)分析人員較為頭痛的就是在樣品越來(lái)越多的情況下,計(jì)算各類(lèi)原始數(shù)據(jù)的繁瑣。傳統(tǒng)方法是運(yùn)用計(jì)算器來(lái)計(jì)算結(jié)果,有固定的原始記錄表格,如滴定法要按照計(jì)算公式出結(jié)果,分光光度法需要在回歸曲線(xiàn)的基礎(chǔ)上出結(jié)果,所有數(shù)據(jù)手工逐個(gè)計(jì)算、輸入和輸出,而且碰到小于檢出限的時(shí)候還要進(jìn)行判斷后計(jì)為“＜DL”,當(dāng)每次需要計(jì)算的項(xiàng)目和樣品都很多的時(shí)候,出錯(cuò)的概率就會(huì)增加,而且校核和審核人員負(fù)擔(dān)也重。

1.2 Excel實(shí)現(xiàn)原始數(shù)據(jù)的分析處理

運(yùn)用Excel表來(lái)實(shí)現(xiàn)以上數(shù)據(jù)處理,只需要將程序按照每個(gè)項(xiàng)目的原始記錄格式一次性的編制好表格,確定“輸入列”和 “輸出列”,并用顏色標(biāo)示。這里分別例舉了高錳酸鹽指數(shù)和總磷來(lái)分別代表容量法和分光光度法進(jìn)行數(shù)據(jù)處理分析,其余項(xiàng)目雷同。

1.2.1 高錳酸鹽指數(shù)數(shù)據(jù)處理

高錳酸鹽指數(shù)的原始記錄計(jì)算,依據(jù)酸性高錳酸鉀法(GB 11892—89《水質(zhì) 高錳酸鹽指數(shù)的測(cè)定》)[1]的分析計(jì)算公式,通過(guò)鍵入 “=”編輯公式和算術(shù)運(yùn)算符完成 (見(jiàn)表1)。

表1 高錳酸鹽指數(shù)原始記錄運(yùn)算表[2]

如表1所示,輸入項(xiàng)即分取水樣體積(F4∶F9)、K值(I4)以及由滴定分析出的終點(diǎn) (C4∶C9,C66∶C69)和始點(diǎn)(D4∶D9,D66∶D69)用量;輸出項(xiàng)即需要在原始記錄表里填寫(xiě)的中間成果 (E4∶E9,E66∶E69,F66∶F69,G66,H66,I66)和最終成果(J4∶J9),根據(jù)計(jì)算方法分別由加減乘除公式組成;最終結(jié)果J4可以計(jì)算為 “=(((10+E4)*I4-10)-((10+H4)*I4-10)*G4)*80/F4”[1], 回車(chē)得到需要的含量。

需注意的是,公式編輯好需經(jīng)過(guò)一系列驗(yàn)證,而后每次運(yùn)算就可直接輸入輸入項(xiàng)數(shù)據(jù),索取輸出項(xiàng)的運(yùn)算結(jié)果,樣品多可以在中間行多插入幾行,再將公式整體下拉即可,但是下拉公式如果引用某固定單元格時(shí),需要采用絕對(duì)引用,即在行和列前加“＄”符號(hào)。

1.2.2 總磷為代表的分光光度計(jì)算程序

總磷的原始記錄計(jì)算依據(jù)鉬酸銨分光光度法(GB 11893—89)[3]進(jìn)行,對(duì)標(biāo)準(zhǔn)系列建立“X濃度—Y吸光度”回歸曲線(xiàn),通過(guò)散點(diǎn)圖、直線(xiàn)相關(guān)分析、直線(xiàn)回歸分析完成曲線(xiàn)的相關(guān)性分析和含量的計(jì)算(見(jiàn)表2)。

表2 總磷原始記錄運(yùn)算表[2]

如表2所示,按照總磷的原始記錄設(shè)計(jì)了分光光度法的分析計(jì)算表格,輸入項(xiàng)為 (D11:K12,F19:G24,K21:K24),輸出項(xiàng)為(D13:K13,A15:D15,H19:J24)。對(duì)行11、13求回歸曲線(xiàn),先進(jìn)行回歸圖分析,選取表格中的散點(diǎn)圖,選取這些正態(tài)分布的點(diǎn)系列添加直線(xiàn)回歸趨勢(shì)線(xiàn),并顯示截距和相關(guān)系數(shù)的平方值;若參與計(jì)算,則可直接通過(guò)函數(shù)實(shí)現(xiàn),其中相關(guān)系數(shù)r的結(jié)果A15處可通過(guò)函數(shù)得到,即 “=SQRT(RSQ(D11:K11,D13:K13))”,取絕對(duì)值;斜率 a的結(jié)果 C15處通過(guò)函數(shù) “=SLOPE(D13:K13,D11:K11)”得到,截距b的結(jié)果D15通過(guò)函數(shù)“=INTERCEPT(D13:K13,D11:K11)” 得到。針對(duì)J列含量結(jié)果,根據(jù) “C=(A-b)/a”、算術(shù)運(yùn)算公式,并結(jié)合檢出限,得出J21處含量為 “=IF((H21-＄D＄15)/＄C＄15*25/K21*2＜＄K＄7, “＜DL”,(H21-＄D＄15)/＄C＄15*25/K21*2)”[3]。

該分光光度法計(jì)算可同時(shí)運(yùn)用插入散點(diǎn)圖并添加趨勢(shì)線(xiàn)、函數(shù)RSQ(返回根據(jù)known_y′s和 known_x′s中數(shù)據(jù)點(diǎn)計(jì)算得出的 Pearson乘積矩相關(guān)系數(shù)的平方)、SQRT(絕對(duì)值)、SLOPE(返回根據(jù) known_y′s和 known_x′s中的數(shù)據(jù)點(diǎn)擬合的線(xiàn)性回歸直線(xiàn)的斜率)、INTERCEPT(利用現(xiàn)有的x值與y值計(jì)算直線(xiàn)與y軸的截距)[4];當(dāng)含量結(jié)果有最低限制要求即檢出限限制時(shí),需要結(jié)合IF語(yǔ)句,并把符合條件的直接輸出為“＜DL”,否則直接返回計(jì)算結(jié)果的數(shù)值;另外公式也涉及到＄絕對(duì)引用。

2 運(yùn)用Excel自動(dòng)評(píng)價(jià)

水質(zhì)各項(xiàng)數(shù)據(jù)在原始記錄計(jì)算的基礎(chǔ)上,需要手工錄入表格形成月報(bào)和年報(bào),并對(duì)其進(jìn)行單項(xiàng)和綜合評(píng)價(jià),在大量的站點(diǎn)和項(xiàng)目面前,手工評(píng)價(jià)過(guò)程的效率和質(zhì)量都很難保證,以下介紹運(yùn)用Excel表格函數(shù)對(duì)地表水水質(zhì)監(jiān)測(cè)結(jié)果進(jìn)行自動(dòng)單項(xiàng)類(lèi)別評(píng)價(jià)、綜合評(píng)價(jià)及湖庫(kù)富營(yíng)養(yǎng)化評(píng)價(jià)分析研究。

2.1 單項(xiàng)水質(zhì)類(lèi)別評(píng)價(jià)

對(duì)于該項(xiàng)評(píng)價(jià),主要是運(yùn)用了“IF疊加語(yǔ)句”,評(píng)價(jià)依據(jù)為GB 3838—2002《地表水環(huán)境質(zhì)量標(biāo)準(zhǔn)》,并可以通過(guò)2種形式實(shí)現(xiàn)。2種單項(xiàng)評(píng)價(jià)的原理是一樣的,不同的是,當(dāng)IF判別的時(shí)候,將判別標(biāo)準(zhǔn)直接輸入還是自動(dòng)引用。前提是以固定格式將數(shù)據(jù)原值列入表 “評(píng)價(jià)數(shù)值匯總”中(見(jiàn)表3)。

表3 監(jiān)測(cè)結(jié)果原值表

首先是直接輸入判別標(biāo)準(zhǔn),對(duì)應(yīng) “IF公式自動(dòng)評(píng)價(jià)”表,以 E6位置CODMn為例 (見(jiàn)表4),當(dāng) (IF)CODmn數(shù)值處為空缺時(shí),評(píng)價(jià)位置也是空;當(dāng)出現(xiàn)小于檢出限(＜DL)的時(shí)候,默認(rèn)為最好水質(zhì)I類(lèi);當(dāng)結(jié)果≤2的時(shí)候,水質(zhì)為I類(lèi),當(dāng)……,以此類(lèi)推,超過(guò)V類(lèi)的情況視為劣V類(lèi)。此類(lèi)評(píng)價(jià)簡(jiǎn)單易懂,一目了然,但是要每個(gè)項(xiàng)目列編一程序,繁瑣。

表4 IF語(yǔ)句單項(xiàng)自動(dòng)評(píng)價(jià)表

其次是引用自動(dòng)評(píng)價(jià)判別標(biāo)準(zhǔn)的IF判別語(yǔ)句(見(jiàn)表5),先建立1個(gè) “自動(dòng)評(píng)價(jià)判別標(biāo)準(zhǔn)表”,即按照 “評(píng)價(jià)數(shù)值匯總”一欄的表頭格式一一對(duì)應(yīng)。

表5 自動(dòng)評(píng)價(jià)標(biāo)準(zhǔn)判別表[5]

然后在另一張 “根據(jù)自動(dòng)評(píng)價(jià)表自動(dòng)評(píng)價(jià)計(jì)算過(guò)程”表里進(jìn)行IF語(yǔ)句編程,仍以E6位置的CODMn為例 (見(jiàn)表6),IF語(yǔ)句里I～V類(lèi)之間的標(biāo)準(zhǔn)值就直接索引 “自動(dòng)評(píng)價(jià)判別標(biāo)準(zhǔn)表”里的標(biāo)準(zhǔn)值即可,只要編制第1行第1列的單項(xiàng)評(píng)價(jià),即可橫向縱向下拉。

表6 通過(guò)引用標(biāo)準(zhǔn)表的IF語(yǔ)句單項(xiàng)自動(dòng)評(píng)價(jià)表

需注意的是,原值、標(biāo)準(zhǔn)值和評(píng)價(jià)值的sheet表里,表頭所在的行列一定要一一對(duì)應(yīng);要特別注意特殊項(xiàng)目pH值、溶解氧和集中式飲用水源地補(bǔ)充項(xiàng)目的評(píng)價(jià)差異性和特殊性;判斷次序:空白—＜DL—標(biāo)準(zhǔn)類(lèi)別。

2.2 綜合水質(zhì)評(píng)價(jià)

綜合評(píng)價(jià)包括綜合評(píng)價(jià)類(lèi)別、超標(biāo)項(xiàng)目以及超標(biāo)倍數(shù)的計(jì)算。根據(jù)水資源公報(bào)的需求,每年評(píng)價(jià)結(jié)果中的超標(biāo)項(xiàng)目需要按照超標(biāo)倍數(shù)由大到小排列,且需要列出超標(biāo)項(xiàng)目的極值,表現(xiàn)形式為 “超標(biāo)項(xiàng)目 (超標(biāo)倍數(shù))[極值]”。該項(xiàng)內(nèi)容以年均值為例,同時(shí)介紹以上功能函數(shù)。

2.2.1 綜合水質(zhì)類(lèi)別評(píng)價(jià)

綜合評(píng)價(jià)過(guò)程運(yùn)用到函數(shù)IF語(yǔ)句和求極值MAX函數(shù)。將單項(xiàng)評(píng)價(jià)類(lèi)別的羅馬數(shù)字“I～劣V類(lèi)”分別運(yùn)用IF函數(shù)替換成阿拉伯?dāng)?shù)字的1～6(見(jiàn)表7),然后根據(jù)單因子評(píng)價(jià)原理,對(duì)每個(gè)站點(diǎn)全部項(xiàng)目最大值化,得到的數(shù)字再通過(guò)IF語(yǔ)句替換回羅馬數(shù)字,即得到綜合評(píng)價(jià)結(jié)果(見(jiàn)表8)。

表7 單項(xiàng)評(píng)價(jià)結(jié)果替換成數(shù)字程序表

表8 綜合評(píng)價(jià)結(jié)果替換成類(lèi)別程序表

表9 水功能區(qū)目標(biāo)類(lèi)別表

表10 函數(shù)VLOOKUP自動(dòng)查找目標(biāo)水質(zhì)類(lèi)別表

2.2.2.2 函數(shù)MAX建立極值表

將每個(gè)站點(diǎn)的全年極值通過(guò)MAX函數(shù)計(jì)算結(jié)果,列入“極值表”,與 “評(píng)價(jià)數(shù)值匯總”表保持統(tǒng)一站名,統(tǒng)一表頭,統(tǒng)一位置,以保證索引極值時(shí)不出錯(cuò)。

2.2.2.3 判別和計(jì)算超標(biāo)項(xiàng)目和倍數(shù)

利用IF語(yǔ)句對(duì)每個(gè)站點(diǎn)的每個(gè)項(xiàng)目進(jìn)行逐個(gè)計(jì)算超標(biāo)倍數(shù),如表11里J4所示,索引“評(píng)價(jià)數(shù)值匯總”里的原值和“自動(dòng)評(píng)價(jià)判別標(biāo)準(zhǔn)表”的標(biāo)準(zhǔn),如果單項(xiàng)原值評(píng)價(jià)類(lèi)別大于目標(biāo)水質(zhì)類(lèi)別,則超標(biāo),計(jì)算超標(biāo)倍數(shù),否則均為空。

表11 單項(xiàng)超標(biāo)倍數(shù)判別與計(jì)算表

表12 運(yùn)用數(shù)組公式格式化超標(biāo)項(xiàng)目表

其中函數(shù) “LARGE(＄C2:＄AD2,AJ＄1),” 意為找出C2～AD2中第AJ1大的結(jié)果,因AJ1為1,即為第1大的結(jié)果;而函數(shù) “SUM(IF(＄C2:＄AD2=LARGE(＄C2:＄AD2,AJ＄1),COLUMN(＄C2:＄AD2)))” 意為如果C2～AD2之間有等于C2～AD2序列里第1大結(jié)果的,返回為該列列號(hào),否則就返回FALSE,計(jì)為0,得到這樣一個(gè)已找到的列號(hào)和0組成數(shù)組,然后用SUM對(duì)該數(shù)組求和,結(jié)果就是C2～AD2里超標(biāo)倍數(shù)最大的數(shù)值所在的列號(hào)(氨氮列)。同理,上表中SUM所求的結(jié)果就是C2～AD2里超標(biāo)倍數(shù)第2大的數(shù)值所在的列號(hào)。而函數(shù) “INDEX(＄A＄1:＄AD＄1,1,SUM(IF(＄C2:＄AD2=LARGE(＄C2:＄AD2,AJ＄1),COLUMN(＄C2: ＄AD2))))” 即 為“INDEX(＄A＄1:＄AD＄1,1,氨氮列)”,意為返回成第1行氨氮列的結(jié)果,即為 “氨氮”2個(gè)字。而函數(shù)TEXT(LARGE(＄C2:＄AD2,AJ＄1),” #0.00”)意為返回 C2～AD2里超標(biāo)倍數(shù)最大的數(shù)值,且該數(shù)值保留2位小數(shù)位數(shù);函數(shù) “INDEX(單站極值!＄A＄1:＄AH＄806,ROW(),SUM(IF(＄C2:＄AD2=LAR GE(＄C2:＄AD2,AJ＄1),COLU MN(＄C2:＄AD2))))”,同理意為返回單站極值表里的本行 (2)氨氮列的數(shù)值,即為氨氮的極值;最后AJ2的公式可以概括為,當(dāng)AJ1(1)＜=AI2(3)時(shí),該位置顯示 “本行最大結(jié)果列(氨氮列)的第1行結(jié)果”&“(”&“本行氨氮列保留2位的數(shù)值”&“)[”&“單站極值表里的本行、氨氮列的數(shù)值”&“]”,顯示出來(lái)就是“氨氮(0.58) [0.39]”。其中 AI2超標(biāo)個(gè)數(shù)運(yùn)用了函數(shù) “=COUNTIF(C2:AD2,” ＞0”)”, 意為 C2～ AD2里＞0結(jié)果的個(gè)數(shù),即為超標(biāo)個(gè)數(shù)。

但是需要明確的是,以上復(fù)雜函數(shù)運(yùn)用了SUM+IF數(shù)組公式,編輯好以后的公式需要“ctrl+shift+enter”結(jié)束,而不是常規(guī)的 “enter”。

只要編輯好AJ1,直接向下向右將公式下拉即可。

最后在超標(biāo)項(xiàng)目如AH2欄將AJ～BH系列用 “&組合”,即得到需要的超標(biāo)結(jié)果——超標(biāo)項(xiàng)目 (超標(biāo)倍數(shù))[極值]按照超標(biāo)倍數(shù)的由大到小排列 (見(jiàn)表13)。

表13 &組合超標(biāo)項(xiàng)目表

3 運(yùn)用Excel進(jìn)行水質(zhì)數(shù)值統(tǒng)計(jì)

水質(zhì)數(shù)據(jù)統(tǒng)計(jì)過(guò)程中主要用到“數(shù)據(jù)透視表”。使用數(shù)據(jù)透視表可以匯總、分析、瀏覽和提供匯總數(shù)據(jù)。使用數(shù)據(jù)透視表可顯示匯總數(shù)據(jù),并且可以方便地查看比較、模式和趨勢(shì)。

該項(xiàng)功能廣泛地應(yīng)用于水質(zhì)資料會(huì)審、水資源質(zhì)量通報(bào)、水資源公報(bào)的前期數(shù)據(jù)處理中。同時(shí)可以計(jì)算均值、極值、計(jì)數(shù)等,這里例舉水資源公報(bào)中不同水資源分區(qū)不同評(píng)價(jià)類(lèi)別的河流長(zhǎng)度統(tǒng)計(jì)。首先將監(jiān)測(cè)斷面的基本信息列出 (見(jiàn)表14)。

表14 水資源公報(bào)監(jiān)測(cè)站點(diǎn)基本信息表[6]

現(xiàn)需按水資源4級(jí)分區(qū)統(tǒng)計(jì)不同水質(zhì)類(lèi)別的河流長(zhǎng)度,就是對(duì)4級(jí)分區(qū)里各個(gè)類(lèi)別的長(zhǎng)度分別求和 (見(jiàn)表15)。綜合評(píng)價(jià)全年和所屬4級(jí)分區(qū)中的下拉框可以篩選顯示;當(dāng)統(tǒng)計(jì)口徑需要變更,可以通過(guò)報(bào)表字段進(jìn)行打鉤篩選;當(dāng)匯總方式變更,可以通過(guò)值字段設(shè)置重新選擇(見(jiàn)表16)。

表15 按河長(zhǎng)、類(lèi)別透視結(jié)果表

表16 數(shù)據(jù)透視值字段設(shè)置表

按照以上的例子,當(dāng)需要統(tǒng)計(jì)年報(bào)表中多個(gè)站點(diǎn)的全年均值、汛期均值、非汛期均值以及特征值 (最大值及出現(xiàn)時(shí)間、最小值及出現(xiàn)時(shí)間等)時(shí),只要將全部數(shù)據(jù)放在同一張sheet表中,統(tǒng)一站點(diǎn)名稱(chēng),將分析時(shí)間按照4—9月為汛期、1—3月和10—12月為非汛期的規(guī)則增加一列時(shí)期屬性,就可以進(jìn)行同等透視了。

4 結(jié) 語(yǔ)

Excel具有強(qiáng)大的試算表、統(tǒng)計(jì)圖表、資料分析等功能,將其運(yùn)用在水資源水質(zhì)監(jiān)測(cè)評(píng)價(jià)中,實(shí)現(xiàn)了工作的高效率、高準(zhǔn)確性。本文根據(jù)實(shí)際工作的需要,運(yùn)用算術(shù)運(yùn)算、回歸曲線(xiàn)、散點(diǎn)圖、直線(xiàn)相關(guān)分析、IF語(yǔ)句等實(shí)現(xiàn)了原始數(shù)據(jù)的分析處理;并運(yùn)用 IF疊加語(yǔ)句、MAX語(yǔ)句、VLOOKUP查找語(yǔ)句、SUM+IF數(shù)組公式、&組合語(yǔ)句等實(shí)現(xiàn)了水質(zhì)自動(dòng)評(píng)價(jià);運(yùn)用數(shù)據(jù)透視表實(shí)現(xiàn)了數(shù)值的統(tǒng)計(jì)。這些語(yǔ)句真實(shí)地應(yīng)用在水質(zhì)監(jiān)測(cè)評(píng)價(jià)工作的各個(gè)環(huán)節(jié),具有極強(qiáng)的指導(dǎo)性。

[1]尚邦懿.GB 11892—89水質(zhì) 高錳酸鹽指數(shù)的測(cè)定 [S].北京:中國(guó)標(biāo)準(zhǔn)出版社,1991.

[2]董華.浙江省水資源監(jiān)測(cè)中心程序文件[Z].杭州:浙江省水資源監(jiān)測(cè)中心,2009.

[3]袁玉璐,姚元.GB 11893—89水質(zhì) 總磷的測(cè)定 [S].北京:中國(guó)標(biāo)準(zhǔn)出版社,1991.

[4]宇傳華,顏杰.Excel與數(shù)據(jù)分析 [M].北京:電子工業(yè)出版社,2002.

[5]國(guó)家環(huán)境保護(hù)總局.GB 3838—2002地表水環(huán)境質(zhì)量標(biāo)準(zhǔn) [S].北京:中國(guó)環(huán)境科學(xué)出版社,2002.

[6]周定炎.杭州市水資源公報(bào)2010年度[R].杭州:杭州市林業(yè)水利局,2011.