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

倪俊堯

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

結(jié)果如表4。

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

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

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

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

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

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

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

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

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

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

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

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

進(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ǔ)句②

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

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

2.3 匯總結(jié)果評(píng)價(jià)

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

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

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

2.5 與過(guò)去的特定值進(jìn)行對(duì)比并進(jìn)行評(píng)價(jià)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3.1 批量顯示水質(zhì)類(lèi)別評(píng)價(jià)結(jié)果指定顏色

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

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

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

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

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

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

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

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

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

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

參考文獻(xiàn)：

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

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

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

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