使用Excel與Word軟件生成水質(zhì)評價結(jié)果

倪俊堯

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

關(guān)鍵詞：Excel;Word;插入函數(shù);水質(zhì)評價;生成文本結(jié)果

中圖分類號：X830? ? ? ? 文獻(xiàn)標(biāo)志碼：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)文明建設(shè)在華夏大地如火如荼地開展，水質(zhì)監(jiān)測則是其中極為重要的一塊內(nèi)容。通過應(yīng)用Excel 2007與Word 2007軟件，可快速準(zhǔn)確地將匯總后的大批量水樣結(jié)果制作為相關(guān)評價結(jié)果，直接代入每次的計算數(shù)據(jù)，生成報告所需的圖表或文本，避免了不必要的二次手動的輸入過程，提高評價結(jié)果的制作效率。

1 運用Excel軟件生成水質(zhì)概況內(nèi)容

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

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

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

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

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

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

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

需要進(jìn)行以下幾步操作：

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

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

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

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

=IF（B2=0，""，A2&"類水?dāng)嗝嬲?&C2&"%;"）

結(jié)果如表4。

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

最后，復(fù)制總計行的文本結(jié)果，對結(jié)尾的標(biāo)點符號進(jìn)行檢查，若為分號則手動修改為句號。

2 運用Excel軟件IF語句進(jìn)行單項指標(biāo)計算并匯總獲得最終評價結(jié)果

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

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

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

2.1 唯一標(biāo)準(zhǔn)判定類別的輸入方法

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

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

2.2 分類進(jìn)行標(biāo)準(zhǔn)判定的輸入方法

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

方法一：對河流湖泊指定列進(jìn)行整張表的排序，將河流和湖泊分類后，進(jìn)行批量計算。然后，運用分配對應(yīng)的IF函數(shù)進(jìn)行計算。假設(shè)對K列3行的總磷的數(shù)據(jù)進(jìn)行數(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ù)語句①

進(jìn)行函數(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ù)語句②

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

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

2.3 匯總結(jié)果評價

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

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

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

2.5 與過去的特定值進(jìn)行對比并進(jìn)行評價

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

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

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

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

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

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

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

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

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

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

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

最后，復(fù)制左邊六列入報告。

3 指定顏色顯示水質(zhì)類別結(jié)果

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

3.1 批量顯示水質(zhì)類別評價結(jié)果指定顏色

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

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

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

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

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

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

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

最后，用“&”符號進(jìn)行文本連接，復(fù)制入最終表格后按照列表顏色進(jìn)行調(diào)整，得到各項目之間有空格的顯示結(jié)果。

4 結(jié)束語

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

參考文獻(xiàn)：

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

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

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

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