EXCEL與底泥數(shù)據(jù)的分析

白愛民

(云南省環(huán)境監(jiān)測中心站,云南昆明650034)

EXCEL與底泥數(shù)據(jù)的分析

白愛民

(云南省環(huán)境監(jiān)測中心站,云南昆明650034)

以《環(huán)境質(zhì)量報(bào)告書》中對底泥監(jiān)測數(shù)據(jù)的處理與分析為例,展示了EXCEL的幾項(xiàng)強(qiáng)大功能和在實(shí)際工作中的使用方法與步驟。

EXCEL應(yīng)用;環(huán)境監(jiān)測;環(huán)境質(zhì)量報(bào)告書;應(yīng)用

計(jì)算機(jī)技術(shù)在環(huán)境監(jiān)測中的作用日趨重要,尤其體現(xiàn)在對大量數(shù)據(jù)的處理和分析上[1]。Microsoft Excel是最優(yōu)秀的電子表格軟件之一,它在數(shù)據(jù)綜合管理和分析方面具有功能強(qiáng)大、技術(shù)先進(jìn)、使用方便等特點(diǎn)[2]。EXCEL是目前數(shù)據(jù)處理工作者使用最頻繁的工具之一,其強(qiáng)大的內(nèi)置函數(shù)幾乎無所不能[3]。使用好EXCEL提供的各項(xiàng)功能可有效提高數(shù)據(jù)處理效率,促進(jìn)環(huán)境信息化建設(shè)的發(fā)展。

《環(huán)境質(zhì)量報(bào)告書》(下稱《報(bào)告書》)是年度環(huán)境質(zhì)量總體狀況和環(huán)境監(jiān)測綜合成果的重要體現(xiàn),是環(huán)境管理的重要依據(jù)之一。環(huán)境質(zhì)量報(bào)告書的編制需要匯總處理大量的環(huán)境監(jiān)測數(shù)據(jù),EXCEL強(qiáng)大的功能幾乎可以完全滿足編制環(huán)境質(zhì)量報(bào)告書的數(shù)據(jù)處理和分析需求。

1 函數(shù)應(yīng)用基礎(chǔ)[4]

Excel函數(shù)是預(yù)先定義,可執(zhí)行計(jì)算、分析等處理數(shù)據(jù)任務(wù)的特殊公式。以常用的求和函數(shù)SUM為例,它的語法是:“SUM(number1,number2,)”。其中“SUM”稱為函數(shù)名稱,一個(gè)函數(shù)只有唯一的一個(gè)名稱,它決定了函數(shù)的功能和用途。函數(shù)名稱后緊跟左括號,接著是用逗號分隔的稱為參數(shù)的內(nèi)容,最后用一個(gè)右括號表示函數(shù)結(jié)束。

參數(shù)是函數(shù)中最復(fù)雜的組成部分,它規(guī)定了函數(shù)的運(yùn)算對象、順序或結(jié)構(gòu)等。假如一個(gè)函數(shù)可以使用多個(gè)參數(shù),那么參數(shù)與參數(shù)之間使用半角逗號進(jìn)行分隔。參數(shù)可以是常量(數(shù)字和文本)、邏輯值(例如TRUE或FALSE)、數(shù)組、錯(cuò)誤值(例如#N/A)或單元格引用(例如E1:H1),甚至可以是另一個(gè)或幾個(gè)函數(shù)等。參數(shù)的類型和位置必須滿足函數(shù)語法的要求,否則將返回錯(cuò)誤信息。

函數(shù)與公式既有區(qū)別又互相聯(lián)系。如果說前者是Excel預(yù)先定義好的特殊公式,后者就是由用戶自行設(shè)計(jì)對工作表進(jìn)行計(jì)算和處理的公式。以公式“=SUM(E1:H1)3A1+26”為例,它要以等號“=”開始,其內(nèi)部可以包括函數(shù)、引用、運(yùn)算符和常量。上式中的 “SUM(E1:H1)”是函數(shù),“A1”則是對單元格A1的引用(使用其中存儲(chǔ)的數(shù)據(jù)),“26”則是常量,“3”和 “+”則是算術(shù)運(yùn)算符(另外還有比較運(yùn)算符、文本運(yùn)算符和引用運(yùn)算符)。

如果函數(shù)要以公式的形式出現(xiàn),它必須有兩個(gè)組成部分,一個(gè)是函數(shù)名稱前面的等號,另一個(gè)則是函數(shù)本身。

2 環(huán)境質(zhì)量報(bào)告書的作用與內(nèi)容

《報(bào)告書》是年度環(huán)境質(zhì)量總體狀況和環(huán)境監(jiān)測綜合成果的重要體現(xiàn),是環(huán)境管理的重要依據(jù)之一?！秷?bào)告書》須根據(jù)國家環(huán)保局頒發(fā)的《全國環(huán)境監(jiān)測報(bào)告制度》規(guī)定與要求,按照國家環(huán)境監(jiān)測總站《關(guān)于進(jìn)一步加強(qiáng)環(huán)境質(zhì)量綜合分析工作的通知》精神完成編制工作?！秷?bào)告書》的編制需要匯總處理大量的環(huán)境監(jiān)測數(shù)據(jù),并與上一年的相應(yīng)數(shù)據(jù)進(jìn)行對比,對環(huán)境質(zhì)量狀況進(jìn)行系統(tǒng)的分析,指出主要環(huán)境問題和環(huán)境質(zhì)量發(fā)展態(tài)勢,并根據(jù)當(dāng)?shù)貙?shí)際情況提出改善環(huán)境質(zhì)量的相應(yīng)對策和建議。

《報(bào)告書》的基本內(nèi)容為:環(huán)境監(jiān)測工作、主要污染物排放、地表水環(huán)境質(zhì)量、城市空氣環(huán)境質(zhì)量、城市聲環(huán)境質(zhì)量等。

3 使用EXCEL的實(shí)例

本文以地表水環(huán)境質(zhì)量中的底泥數(shù)據(jù)處理為例,說明如何使用EXCEL來完成數(shù)據(jù)處理和分析任務(wù),滿足編寫《報(bào)告書》的需求。

處理環(huán)境底泥數(shù)據(jù)的基本需求及步驟:

(1)按要求處理基礎(chǔ)數(shù)據(jù);

(2)按監(jiān)測點(diǎn)和監(jiān)測元素分別將當(dāng)年與上年的數(shù)據(jù)對應(yīng)地匯總在一張新表中;

(3)按監(jiān)測點(diǎn)對各監(jiān)測元素分別與上一年度監(jiān)測值進(jìn)行比較,標(biāo)明上升或下降,并計(jì)算出上升或下降幅度;

(4)按各監(jiān)測元素,找出各監(jiān)測元素最大值、最大上升幅度、最小值、最大下降幅度、總上升或總下降數(shù)據(jù)個(gè)數(shù)及相應(yīng)百分比;

(5)按各監(jiān)測元素,找出各監(jiān)測元素值較上年上升測點(diǎn)數(shù)和下降測點(diǎn)數(shù)和相應(yīng)比值、最大值、最大上升幅度、最小值、最大下降幅度對應(yīng)的監(jiān)測點(diǎn)。

為完成數(shù)據(jù)分析,按上述需求和步驟,先做第(1)和第(2)項(xiàng)工作。

要將不同年度的數(shù)據(jù)按測點(diǎn)和監(jiān)測元素分別進(jìn)行比較,就必須將數(shù)據(jù)按測點(diǎn)和監(jiān)測元素分別排序。在底泥分析時(shí),測點(diǎn)和監(jiān)測元素的位置有一定的要求和規(guī)律性,將測點(diǎn)數(shù)據(jù)和監(jiān)測元素?cái)?shù)據(jù)分別定義成兩個(gè)新序列,在數(shù)據(jù)處理時(shí),利用EXCEL的“自定義序列”排序功能,按行或列分別排序可迅速按需求統(tǒng)一測點(diǎn)和監(jiān)測元素相對位置。統(tǒng)一各年度的表格格式后,再匯總成數(shù)據(jù)分析表就很容易了,完成后的基礎(chǔ)數(shù)據(jù)分析表見表1。

為確保數(shù)據(jù)的保密性,用測點(diǎn)1、測點(diǎn)2、……來代表實(shí)際監(jiān)測點(diǎn)名稱,如滇池草海、觀音山、個(gè)舊湖……;用元素1、元素2、……來代表各實(shí)際監(jiān)測元素,如汞、砷、鉛、……。在例子中只采用了10測點(diǎn)和部分2個(gè)元素,以說明如何快速輸入函數(shù),完成底泥數(shù)據(jù)的分析。

表1 圖表示例1

為完成第(3)項(xiàng)工作,在D4單元格和E4單元格中分別輸入公式:“=IF(B4>C4,"u","d")”和 “=(B4-C4)/C4”,接著選中D4和E4單元格,執(zhí)行復(fù)制命令,再選擇表中的所有空白單元格(D5:E13和H4:I13),然后執(zhí)行粘貼命令,第(3)項(xiàng)工作宣告完成。

其中,為使表格顯得簡捷,用字母u代表監(jiān)測值較上年升高,字母d代表監(jiān)測值較上年下降。

為完成最后兩項(xiàng)工作,即基本需求及步驟的第(4)、第(5)步,在上述EXCEL工作表的底泥數(shù)據(jù)分析表下方,再制作一個(gè)底泥數(shù)據(jù)分析續(xù)表:可使數(shù)據(jù)分析表與上表中元素列對應(yīng)、方便查詢,更重要的是保證通過復(fù)制粘貼獲得的公式能自動(dòng)并正確地調(diào)整其引用單元格和區(qū)域。在B17至B25單元格中輸入公式:

“=COUNTIF(D4:D13,"u")”、“=COUN2 TIF(D4:D13,"d")”、“=SUM(B16:B17)”、“=B16/B18”、 “=B17/B18”、 “=MAX(B4:B13)”、 “=MAX(E4:E13)”、 “=M I N(B4:B13)”、“=M I N(E4:E13)”;

在C22和C24單元格中分別輸入公式:

“=I NDEX(＄A＄4:＄I＄13,MATCH(B21,B＄4:B＄13,0),1)”、“=I NDEX(＄A

＄4:＄I＄13,MATCH(B23,E＄4:E＄13,0),1)”。其中C23或C25的公式通過分別復(fù)制C22和C24來實(shí)現(xiàn)。

在B17至B25單元格中的公式僅采用了相對引用,公式的含義也較簡單,分別表示條件計(jì)數(shù)、求和、求最大值和求最小值。

在C22至C25單元格各包括有MATCH和I N2 DEX兩個(gè)函數(shù),且MATCH嵌套于I NDEX函數(shù)中,其公式的含義是:先用MATCH函數(shù)找到基礎(chǔ)數(shù)據(jù)區(qū)中滿足某條件的值的位置,再據(jù)此查詢出對應(yīng)的測點(diǎn)名。這幾個(gè)公式采用了相對引用、絕對引用和混和引用。其區(qū)別在于,采用相對引用時(shí),如果公式所在單元格的位置改變,引用也隨之改變。采用絕對引用時(shí),如果公式所在單元格的位置改變,絕對引用保持不變?；旌弦梅譃榻^對列和相對行,或是絕對行和相對列兩種。如果公式所在單元格的位置改變,則相對引用改變,而絕對引用不變。如果多行或多列地復(fù)制公式,相對引用自動(dòng)調(diào)整,而絕對引用不作調(diào)整。

在進(jìn)行數(shù)據(jù)處理和分析時(shí),常常會(huì)在EXCEL表中使用大量的函數(shù)和公式,充分利用EXCEL的引用功能,可大大減少公式輸入工作量,即只須輸入一套最基礎(chǔ)的公式,再采用復(fù)制粘貼功能,就能完成全部公式的輸入,從而快速正確地獲得所需要的結(jié)果。

表2 底泥數(shù)據(jù)分析續(xù)表

要完成數(shù)據(jù)分析續(xù)表的其它部分,只須選中底泥數(shù)據(jù)分析續(xù)表中元素1所屬的各項(xiàng)公式,復(fù)制到元素2所屬的相應(yīng)單元格即可。即復(fù)制B17至B25單元格的內(nèi)容,粘貼到F17至F25,復(fù)制C22至C25單元格的內(nèi)容,粘貼到G22至G25,則數(shù)據(jù)分析續(xù)表的編制工作完成。

G22的公式是從C22復(fù)制過來的,讓我們比較一下G22單元格和C22單元格的內(nèi)容:

C22:“=I NDEX(＄A＄4:＄I＄13,MATCH(B21,B＄4:B＄13,0),1)”

G22:“=I NDEX(＄A＄4:＄I＄13,MATCH(F22,F＄4:F＄13,0),1)”

可看出,公式復(fù)制到新的位置后,相對引用中的列和混合引用中的相對列已經(jīng)自動(dòng)調(diào)整正確,而絕對引用保持不變。因此為確保通過復(fù)制粘貼獲得的公式能產(chǎn)生正確的計(jì)算結(jié)果,必須規(guī)范化表格的制作。

在上述實(shí)例中,只選擇了10個(gè)監(jiān)測點(diǎn)和2個(gè)監(jiān)測元素,底泥數(shù)據(jù)分析續(xù)表相對簡單。實(shí)際上,監(jiān)測點(diǎn)增多時(shí)只須調(diào)整各式公式中的數(shù)據(jù)起始行和結(jié)束行的行標(biāo)號即可,而監(jiān)測元素增加,只須將監(jiān)測元素1所包括的已經(jīng)完成的公式復(fù)制到其它監(jiān)測元素的相應(yīng)的位置就可實(shí)現(xiàn)全部計(jì)算。即使監(jiān)測點(diǎn)和監(jiān)測元素都增加10倍,增加的工作量可以說是微乎其微。換句話說,在上述分析表中監(jiān)測點(diǎn)和監(jiān)測元素越多,就越顯示出使用EXCEL函數(shù)的優(yōu)越性。

另外,對于已經(jīng)建立好的分析表而言,還有一個(gè)很大的優(yōu)勢,即如果基礎(chǔ)數(shù)據(jù)發(fā)生改變(因上報(bào)錯(cuò)誤或輸入錯(cuò)誤等),只須修改數(shù)據(jù)分析表中相應(yīng)的基礎(chǔ)數(shù)據(jù),其新分析結(jié)果自動(dòng)計(jì)算完成。

4 結(jié)語

《環(huán)境質(zhì)量報(bào)告書》的編制需要匯總大量數(shù)據(jù),并做大量的數(shù)據(jù)處理與分析比較,這些工作通常在EXCEL中來完成。EXCEL功能非常強(qiáng)大,在環(huán)保系統(tǒng)內(nèi)幾乎都是用它來處理數(shù)據(jù)和編制報(bào)表,但很多人僅僅停留在初級水平,遠(yuǎn)沒有體現(xiàn)出EX2 CEL的優(yōu)勢。用好EXCEL的 “自定義序列”排序功能可迅速統(tǒng)一測點(diǎn)和監(jiān)測元素相對位置,熟悉內(nèi)置函數(shù)和掌握各種引用方法可輕松完成數(shù)據(jù)分析工作。總之,在底泥數(shù)據(jù)分析時(shí)熟練掌握EXCEL的這幾項(xiàng)功能可大大減少工作量,降低出錯(cuò)率,提高工作效率。由于EXCEL易學(xué)易懂,即使是不熟悉軟件編程語言且不太熟悉EXCEL的人員也能很快掌握這些功能。

[1]單文坡.EXCEL和OR IGI N在環(huán)境監(jiān)測數(shù)據(jù)處理中的應(yīng)用[J].石家莊職業(yè)技術(shù)學(xué)院學(xué)報(bào),2008,20(2).

[2]榮欽科技.EXCEL2003在統(tǒng)計(jì)學(xué)中的應(yīng)用[M].北京:電子工業(yè)出版社,2005.

[3]Excel研究組.Excel2007函數(shù)與公式速查手冊[M].北京:電子工業(yè)出版社,2008.

[4]張迎新.EXCEL2003函數(shù)應(yīng)用完全手冊[M].http://www.doc88.com/p-54803971.html,2004.

Sludge Data Analysis by Using EXCEL

BA IAi2min
(Yunnan EnvironmentalMonitoring Center,Kunming Yunnan 650034 China)

The analysis of the sludge data is taken as an example to reveal the s mart function of EXCEL software.The steps of real application of EXCEL are discovered in the article.

EXCEL application;environmentalmonitoring;environmental quality report;application

X11

A

1673-9655(2010)05-0109-04

2010-08-18