倪俊堯

摘? 要：鑒于手工計算的復雜性和應對大批量水樣結果匯總后需要批量計算獲得準確水質評價結果，通過應用Excel 2007與Word 2007軟件，在輸入水質檢測各項目數(shù)據(jù)后，生成所需的水質評價結果，以此來提高水質評價的工作效率。

關鍵詞：Excel;Word;插入函數(shù);水質評價;生成文本結果

中圖分類號：X830? ? ? ? 文獻標志碼：A? ? ? ? ?文章編號：2095-2945（2020）18-0054-04

Abstract： In view of the complexity of manual calculation and the need for batch calculation to obtain accurate water quality evaluation results after summarizing the results of a large number of water samples， the required water quality evaluation results are generated after inputting the data of various water quality testing projects by using Excel 2007 software and Word 2007 software， so as to improve the efficiency of water quality evaluation.

Keywords： Excel; Word; insert function; water quality evaluation; generating text results

引言

生態(tài)文明建設在華夏大地如火如荼地開展，水質監(jiān)測則是其中極為重要的一塊內容。通過應用Excel 2007與Word 2007軟件，可快速準確地將匯總后的大批量水樣結果制作為相關評價結果，直接代入每次的計算數(shù)據(jù)，生成報告所需的圖表或文本，避免了不必要的二次手動的輸入過程，提高評價結果的制作效率。

1 運用Excel軟件生成水質概況內容

水質概況內容一般要對特定時間內檢測斷面總數(shù)、河道總數(shù)和水質類別等進行統(tǒng)計，并按照水質類別進行斷面百分比的計算，最終獲得一段固定的文本內容。

1.1 評價不重復河道總數(shù)與監(jiān)測斷面總數(shù)的統(tǒng)計方法

若評價河道中所有監(jiān)測斷面的河道無重復，則評價河道總數(shù)與監(jiān)測斷面總數(shù)相等。然而，在大量采樣過程中，不可避免地遇到河道有重復的情況。因此，借助Excel軟件進行統(tǒng)計，能夠大大加快評價報告的編制效率與準確率。

選中河道列，在菜單中選擇“數(shù)據(jù)-篩選-高級”命令，進入高級篩選對話框，選擇“將篩選結果復制到其他位置”，勾選“選擇不重復的記錄”，并選擇需要復制到的位置，獲得不重復的河道名稱，再通過COUNTA函數(shù)框選此列，來統(tǒng)計不重復河道總數(shù)與監(jiān)測斷面的總數(shù)。

當我們獲得監(jiān)測斷面數(shù)據(jù)統(tǒng)計表（表1）后，按照上述篩選方法，可得不重復河道名稱篩選結果，見表2。

1.2 生成固定句式的統(tǒng)計結果輸出文字

每月水質概況中需要對水質類別進行區(qū)分，并按照斷面?zhèn)€數(shù)與總體的比值進行計算后整理為文字結果。若對每個水質類別的結果進行生成文字，則可以使報告生成的效率提高。如表3，需將該表在Excel軟件中生成為一整段統(tǒng)計文字。

需要進行以下幾步操作：

將占比數(shù)字修約。本文中保留小數(shù)點后一位。若小數(shù)點后一位均不為0，則ROUND函數(shù)與TEXT函數(shù)可以達到一致的效果;否則，需要使用TEXT函數(shù)進行修約，方能獲得保留位數(shù)一致的效果。因此TEXT函數(shù)在處理需要統(tǒng)一小數(shù)位數(shù)的統(tǒng)計文本時更方便。例如：對A列4行的數(shù)據(jù)進行保留小數(shù)點后一位，則輸入=TEXT（A4，"0.0"）即可。

將每一行的統(tǒng)計數(shù)字生成一段文字。運用“&”符號進行各個數(shù)據(jù)與所需生成的文本的連接。需注意的是，所有函數(shù)中的符號僅可使用英文狀態(tài)下的符號，文本內容需要用雙引號進行包絡。

一般統(tǒng)計報告中，所有為0的數(shù)據(jù)需要剔除，因此，可以通過加入IF函數(shù)來進行數(shù)據(jù)是否為正值的判斷。若為0，則顯示空文本，否則顯示輸出文本。

例如，對表1中第2行Ⅱ類水斷面?zhèn)€數(shù)進行0值判斷是否生成文本（水質類別位于A列），若是，則顯示“Ⅱ類水斷面占0.0%;”的句式，則輸入語句如下：

=IF（B2=0，""，A2&"類水斷面占"&C2&"%;"）

結果如表4。

連接文字除了使用“&”符號外，還可以用 CONCATENATE函數(shù)[1]將所有文字全部合并。需要注意的是，此函數(shù)必須將輸出文本逐一選中，不可進行范圍框選，每個對象中間用英文逗號隔開。

最后，復制總計行的文本結果，對結尾的標點符號進行檢查，若為分號則手動修改為句號。

2 運用Excel軟件IF語句進行單項指標計算并匯總獲得最終評價結果

對河道參數(shù)進行評價，運用Excel軟件中插入函數(shù)IF語句，將數(shù)據(jù)判斷區(qū)間與評價結果一一對應，對單個項目作出正確評價后，方可進行整體評價。

IF函數(shù)語句成分如下：

水質類別評價結果分為Ⅰ類、Ⅱ類、Ⅲ類、Ⅳ類、Ⅴ類與劣Ⅴ類，我們可以使用自然數(shù)與之對應（劣Ⅴ類對應自然數(shù)6），并進行總體判斷評價，從而提升評價效率。

2.1 唯一標準判定類別的輸入方法

諸如pH值、化學需氧量等項目的水質類別判斷是同一個系列的數(shù)字統(tǒng)一進行判定。按照地表水環(huán)境質量標準，凡是在6-9范圍內，即為Ⅰ～Ⅴ類水，又因為非劣Ⅴ類水體還有其他待定判斷類別項目，取最差指標進行最終結果判定，因此合格水體水質統(tǒng)一設定為Ⅰ類。假設對U列2行的pH值進行數(shù)據(jù)判別，函數(shù)可如下輸入：

需要判別的系列值越多，則需要嵌套的IF函數(shù)則越多。

2.2 分類進行標準判定的輸入方法

根據(jù)GB 3838-2002《地表水環(huán)境質量標準》可知，計算地表水總磷的過程中，需要對河流和湖泊進行分類判斷。可以有以下兩種方法進行快速評價：

方法一：對河流湖泊指定列進行整張表的排序，將河流和湖泊分類后，進行批量計算。然后，運用分配對應的IF函數(shù)進行計算。假設對K列3行的總磷的數(shù)據(jù)進行數(shù)據(jù)判別。河道則需輸入

=IF（K3>0.4，6，IF（K3>0.3，5，IF（K3>0.2，4，IF（K3>0.1，3，IF（K3>0.02，2，1）））））……………………函數(shù)語句①

進行函數(shù)判別;而湖泊則需輸入

=IF（K3>0.2，6，IF（K3>0.1，5，IF（K3>0.05，4，IF（K3>0.025，3，IF（K3>0.01，2，1）））））……………………函數(shù)語句②

此方法需要將第一行的河流和第一行的湖泊中輸入對應的函數(shù)，然后進行批量公式填充，最后使用序號列對整張表格進行排列。

方法二：IF函數(shù)首先對水體類別列進行判斷，判斷條件為="湖泊"。若為湖泊則使用函數(shù)語句②;若不為湖泊，即為河流，則使用方法一中判斷函數(shù)①。

2.3 匯總結果評價

待所有單項指標評價結束后，使用函數(shù)MAX對斷面評價指標取最大值（使用MAX函數(shù)并框選范圍），再將數(shù)字結果轉化為對應的水質類別的文本結果。若出現(xiàn)由于部分河道整治中而沒有采樣數(shù)據(jù)，則需要進行IF函數(shù)對其中一列數(shù)據(jù)進行大于0的判斷后，再進行取值。按照表1的統(tǒng)計數(shù)據(jù)舉例，最終各檢測項目水質類別評價結果與各斷面的水質類別評價報告結果，見表5。

2.4 復制數(shù)據(jù)粘貼入計算表格消除文本格式與無效符號的技巧

另外，若上報數(shù)據(jù)中存在未采水樣斷面數(shù)據(jù)列出現(xiàn)“-”或“/”符號，或者部分數(shù)據(jù)為文本格式，以上這些數(shù)據(jù)無法參與或者導致評價錯誤計算的，則需要復制原數(shù)據(jù)后，鼠標移動到待復制區(qū)域，“右鍵”-“選擇性粘貼”，“粘貼”-“數(shù)值”，“運算”-“加”，則可以將文本轉為數(shù)字，而單獨的符號可以全部清除。

2.5 與過去的特定值進行對比并進行評價

就某一特定值進行去年或上月的對比，前提為兩次所選斷面一致。先對兩次的河道或斷面和數(shù)據(jù)進行復制，由IF函數(shù)進行文本內容是否相同的判定，而后進行（今值-過去值）/過去值×100%的計算，獲得對比的百分比值;若文本內容不一致，則顯示“-”符號。

獲得對比百分比值以后，根據(jù)自身范圍需求，進行對比值的大小判定，并顯示對應文字。例如：設對比絕對值>20%的為顯著變化，>10%的為略有變化，<10%的為基本持平，負值為改善，正值為惡化。對CH3的數(shù)據(jù)進行計算，則有以下公式：

=IF（CH3<-20%，"顯著改善"，IF（CH3<-10%，"略有改善"，IF（CH3<10%，"基本持平"，IF（CH3<20%，"略有惡化"，"顯著惡化"））））

2.6 對轄區(qū)范圍出入境河道進行水質變化情況生成計算

對出入境河流進行水質變化計算，首先對需要對比的指數(shù)（假設位于B2）進行2位小數(shù)修約，可使用ROUND函數(shù)[3]，即=ROUND（B2，2）。然后，進行出入境斷面的變化值計算：

變化量=（出境值-入境值）/入境值×100%

如表6，河道名稱位于A列，需對兩條跨境河道統(tǒng)計數(shù)據(jù)進行變化值變化的計算與變化情況的文字說明。

首先，對河道x計算變化值（位于G列2行）的正負值進行改善/不變/惡化的結果判定，計算結果在G列3行中顯示。

=IF（G2>0，"惡化"，IF（G2=0，"不變"，"改善"））。

然后，編輯水質變化情況的文本輸出顯示，用IF函數(shù)先判斷變化量是否為0，再用CONCATENATE函數(shù)將變化判斷文本與變化百分比取絕對值（函數(shù)ABS）后進行TEXT函數(shù)文本化，則河道x變化情況文本可輸入函數(shù)：

=IF（G2=0，G3，CONCATENATE（G3，TEXT（ABS（G2），"0.0"），"%"））

最后，復制左邊六列入報告。

3 指定顏色顯示水質類別結果

得到水質類別評價數(shù)據(jù)結果后，可能還需要在報告中列出其中超標的具體項目名稱和其對應的水質類別，并用顏色進行標識，來進一步顯示該水體污染程度和項目超標的具體情況。然而，僅通過人眼進行數(shù)據(jù)判別是否超標，還要判斷顯示顏色，出錯概率很大。因此，需要使用函數(shù)或條件格式進行顏色辨別，協(xié)助水質報告編寫者甄別超標項目，體現(xiàn)其高效和準確的作用。

3.1 批量顯示水質類別評價結果指定顏色

水質類別評價后，若需要對現(xiàn)狀水質進行特定顏色標識（綠色為優(yōu)于或等于Ⅲ類，紅色為Ⅳ類，紫色為Ⅴ類，黑色為劣V類），如表5中每個斷面的水質類別結果，可以通過“開始”菜單-條件格式-突出顯示單元格規(guī)則-等于，來設置內容：將Ⅰ類、Ⅱ類和Ⅲ類的設置綠色文本，紅色為Ⅳ類，紫色為Ⅴ類，劣Ⅴ類無需設置，即為默認黑色[4]。

此方法同樣可對表5中各項目的評價結果數(shù)字進行指定顏色的顯示。

3.2 批量顯示超出功能水質類別的項目并指定水質類別顏色

若需要將超出功能水質的污染項目列出，則通過數(shù)字評價表中超出Ⅲ類水要求的單項評價結果進行突出顯示單元格規(guī)則設置。

設置之后，由于Excel復制數(shù)據(jù)過程中自動清除顏色，因此需要先復制入Word軟件，再復制入需要進行超標項目名稱顯示的判斷函數(shù)計算表格內，顏色同數(shù)據(jù)即可一同復制過來。然后對功能水質與當前水質進行判斷，功能水質小于當前水質的結果顯示當前項目名稱，復制函數(shù)格，再對整列進行“選擇性粘貼”-“公式”操作，即可得到有顏色的超出功能水質的帶顏色的項目表。

需要注意的是，最右列檢測項目左邊的所有檢測項目則在判斷函數(shù)顯示文本中加一個空格，這樣可以避免多個項目連成一行文字，方便審閱或修改。例如：對氨氮換算得出的單項水質評價某一結果（O列11行）進行是否超出功能水質類別（功能水質類別對應數(shù)字位于C列11行）的判斷顯示輸入函數(shù)：

=IF（O11>C11，"氨氮"，""）

最后，用“&”符號進行文本連接，復制入最終表格后按照列表顏色進行調整，得到各項目之間有空格的顯示結果。

4 結束語

Excel 2007與Word 2007軟件十分普遍。通過充分運用軟件中的功能與特性，盡可能地減少手動輸入與人工判斷的繁瑣過程，使用預先設定好的函數(shù)，在方法不斷總結與優(yōu)化的過程中，使大批量水質數(shù)據(jù)評價的固定格式評價結果生成變得更加快速與準確，簡化評價人員的數(shù)據(jù)操作，最終提高每次報告的制作效率。

參考文獻：

[1]張山風.用函數(shù)解決數(shù)值自動排名[J].應用經(jīng)驗，2008（127）：52.

[2]馬鋒.Excel在水文數(shù)據(jù)數(shù)字修約中的應用[J].陜西水利，2013（4）：19-21.

[3]謝有亮，麥愛華.EXCEL在環(huán)境監(jiān)測數(shù)據(jù)統(tǒng)計分析中的應用[J].內蒙古環(huán)境科學，2008（3）：34-36.

[4]陳桂鑫.Excel2007按規(guī)律設置行列格式[J].電腦迷，2008（7）：79-80.