朱正勛

(武穴市環(huán)境監(jiān)測(cè)站，湖北武穴435400)

Excel在酸雨統(tǒng)計(jì)分析中的應(yīng)用

朱正勛

(武穴市環(huán)境監(jiān)測(cè)站，湖北武穴435400)

將microsoft公司office系列軟件Excel強(qiáng)大的計(jì)算、公式編輯、數(shù)據(jù)統(tǒng)計(jì)和繪圖功能，應(yīng)用在酸雨統(tǒng)計(jì)分析中，通過(guò)計(jì)算機(jī)自動(dòng)運(yùn)算和圖表處理，方法簡(jiǎn)捷、快速、高效，便于從數(shù)據(jù)中分析出所需要的信息。

Excel;酸雨;數(shù)據(jù)統(tǒng)計(jì);函數(shù);數(shù)組公式

1 前言

酸雨是指pH值小于5.6的降水(雨或雪等)。酸雨測(cè)量一般要求逢雨必測(cè)，會(huì)有大量的數(shù)據(jù)產(chǎn)生，經(jīng)常需要從中統(tǒng)計(jì)分析一些重要信息，如月或年降水pH值范圍、月或年均降水pH值、月或年酸雨頻率等，手工統(tǒng)計(jì)計(jì)算煩瑣，費(fèi)時(shí)費(fèi)力，效率不高。microsoft公司 office系列軟件 Excel是一種“表格”式數(shù)據(jù)綜合管理與分析系統(tǒng)，用戶在電子表格中輸入數(shù)據(jù)，通過(guò)公式編輯，由電子表格程序自動(dòng)對(duì)數(shù)據(jù)進(jìn)行處理，即可完成計(jì)算、統(tǒng)計(jì)分析和繪圖等功能。在實(shí)際工作中我們嘗試借助Excel軟件來(lái)進(jìn)行酸雨數(shù)據(jù)的統(tǒng)計(jì)處理，獲得了較好的效果。

2 獲得數(shù)據(jù)

酸雨監(jiān)測(cè)［1］的項(xiàng)目是pH和降雨量，每次降雨必須采樣測(cè)試。連續(xù)降水超過(guò)24h時(shí)，每24h采集1次降水樣品進(jìn)行監(jiān)測(cè)分析。目前武穴市酸雨是用降水降塵自動(dòng)采樣器自動(dòng)采集，降雨量由標(biāo)準(zhǔn)雨量?jī)x自動(dòng)記錄，與降水采樣同步、平行地進(jìn)行操作。每次采集后的降水樣品在3℃ -5℃條件下用無(wú)色聚乙烯塑料瓶保存，再進(jìn)入實(shí)驗(yàn)室分析，得到這次降水的pH值。降水量、降水時(shí)間數(shù)據(jù)可由降水降塵自動(dòng)采樣器自動(dòng)記錄打印出來(lái)。每次采樣時(shí)間、降水量及實(shí)驗(yàn)室分析出的pH值的數(shù)據(jù)獲得之后，將數(shù)據(jù)輸入Excel工作表單元格中，格式如圖1，匯總出年或月數(shù)據(jù)作為源數(shù)據(jù)以備后續(xù)統(tǒng)計(jì)分析用。

圖1 酸雨監(jiān)測(cè)結(jié)果匯總

3 計(jì)算方法

降水pH值平均值的計(jì)算采用(H+)濃度-雨量加權(quán)平均計(jì)算［2］，氫離子濃度單位為mol/L。

式中：

［H+］i——第i次降水的氫離子濃度mol/L;

Vi——第i次降水的降水量mm。

綜合以上四式，可得到：

酸雨頻率是指降水中出現(xiàn)pH值小于5.6的次數(shù)占總的降水次數(shù)的百分比。

4 酸雨統(tǒng)計(jì)

一年或月的酸雨測(cè)量數(shù)據(jù)匯總完畢后需要統(tǒng)計(jì)，在統(tǒng)計(jì)中一般要統(tǒng)計(jì)月或年降水pH值范圍、月或年均降水pH值、月或年酸雨頻率。下面以武穴市2005年酸雨統(tǒng)計(jì)為例說(shuō)明統(tǒng)計(jì)方法。統(tǒng)計(jì)計(jì)算之前需要在Excel工作簿新建一工作表，其格式如圖2。具體步驟為：

4.1 創(chuàng)建名稱

首先要在降水?dāng)?shù)據(jù)工作表中為包含降雨量、月份、pH值的單元格范圍命名，這樣做的好處是，可以讓公式中單元格引用“文本化”易懂，增強(qiáng)公式的可讀性，容易使用。以創(chuàng)建降雨量名稱為例來(lái)說(shuō)明創(chuàng)建的方法。首先選中包括2005年全年降雨量數(shù)據(jù)的單元格范圍，然后在菜單中選擇“插入”→“名稱”→“定義”(或按 Ctrl+F3)。Excel顯示“定義名稱”對(duì)話框，在標(biāo)有“在當(dāng)前工作簿中的名稱”文本框中鍵入“降雨量5”，表示2005年降雨量數(shù)據(jù)。然后單擊“添加”按鈕，繼續(xù)向工作表中添加“pH5”和“月份5”的名稱，當(dāng)然必須確認(rèn)相應(yīng)“引用位置”的范圍是正確的，這樣創(chuàng)建名稱完成。

4.2 創(chuàng)建降水pH范圍公式

降水pH范圍是指該月降雨中所測(cè)得的最低pH值到最高pH值之間的范圍，可以用函數(shù)MIN()和MAX()來(lái)實(shí)現(xiàn)。在公式編輯中會(huì)用到數(shù)組公式［3］的概念，數(shù)組公式是Excel提供的高級(jí)功能，可以實(shí)現(xiàn)一些復(fù)雜的計(jì)算。所謂數(shù)組是一些元素的簡(jiǎn)單集合，元素可以共同參與運(yùn)算或個(gè)別參與運(yùn)算。以一月份降水pH范圍為例說(shuō)明數(shù)組公式編輯的實(shí)現(xiàn)方法。

圖2 酸雨統(tǒng)計(jì)格式

點(diǎn)擊圖2所示工作表C4單元格，在編輯欄中輸入“=MIN(IF((月份5=1)*(pH5＜＞0)，pH5))＆“～”＆MAX(IF((月份5=1)*(pH5＜ ＞0)，pH5))”，然后按 Ctrl+Shift+Enter鍵，編輯欄出現(xiàn)“{=MIN(IF((月份5=1)*(pH5＜ ＞0)，pH5))＆“～”＆MAX(IF((月份5=1)*(pH5＜ ＞0)，pH5))}”后數(shù)組公式編輯完成。公式中“(月份5=1)”表示2005年1月份，“IF((月份5=1)*(pH5 ＜ ＞0)，pH5)”函數(shù)中“(月份 5=1)*(pH5＜＞0)”是基于布爾值 TRUE*TRUE=TRUE的事實(shí)，結(jié)果返回TRUE，IF函數(shù)返回2005年1月份且所測(cè)pH值不為0的降水pH值數(shù)據(jù)。用MIN函數(shù)返回這些數(shù)據(jù)中最小值，MAX函數(shù)返回這些數(shù)據(jù)中最大值，用“＆“～”＆”插入字符“～”將之聯(lián)接。數(shù)組公式輸入完畢后在C4單元格中會(huì)出現(xiàn)“4.50～6.18”的計(jì)算結(jié)果，表示2005年1月份的降雨pH范圍是4.50到6.18。

同樣在C5至C15輸入相應(yīng)的數(shù)組公式，只是將“(月份5=1)”中的1換成相應(yīng)月份的數(shù)字即可得到各月的降雨pH范圍。在C16中輸入“=MIN(pH5)＆“～”＆MAX(pH5)”，然后按 Ctrl+Shift+Enter鍵可得到全年的降雨pH范圍。

4.3 創(chuàng)建降水平均pH公式

基于對(duì)本文3節(jié)中式5的了解，可以創(chuàng)建降水平均pH公式了。點(diǎn)擊D4單元格，在編輯欄中輸入“=-LOG10(SUM((pH5＜＞0)*(月份5=1)*降雨量5*10^(-(pH5＜＞0)*(月份5=1)*pH5))/SUM((pH5＜＞0)*(月份5=1)*降雨量5))”，然后按Ctrl+Shift+Enter鍵，編輯欄中出現(xiàn)“{=-LOG10(SUM((pH5＜＞0)*(月份5=1)*降雨量5*10^(-(pH5＜＞0)*(月份5=1)*pH5))/SUM((pH5＜＞0)*(月份5=1)*降雨量5))}”后數(shù)組公式輸入完成。這時(shí)D4單元格中會(huì)出現(xiàn)“4.82”的計(jì)算結(jié)果。相應(yīng)地在D5至D15輸入各月的數(shù)組公式，只是將“(月份5=1)”中的1換成相應(yīng)月份的數(shù)字即可得到各月的降水平均pH。在D16中輸入“=-LOG10(SUM((pH5＜＞0)*降雨量5*10^(-(pH5＜＞0)*pH5))/SUM((pH5＜＞0)*降雨量5))”，然后按Ctrl+Shift+Enter鍵可得到全年的降水平均pH值。

4.4 創(chuàng)建酸雨平均pH公式

酸雨平均pH的統(tǒng)計(jì)與降水平均pH相似，區(qū)別之處是酸雨只統(tǒng)計(jì)降水中pH值小于5.6的降水平均pH值，因此需要在降水平均pH公式的基礎(chǔ)上加上約束條件“(pH5＜5.6)”。在E4單元格中輸入“=IF(SUM((pH5＜ ＞0)*(pH5＜5.6)*(月份5=1)*降雨量5)=0，“本月沒(méi)有酸雨”，-LOG10(SUM((pH5＜ ＞0)*(pH5＜5.6)*(月份5=1)*降雨量5*10^(-(pH5＜＞0)*(pH5＜5.6)*(月份 5=1)*pH5))/SUM((pH5 ＜ ＞0)*(pH5 ＜5.6)*(月份5=1)* 降雨量 5)))”，然后按Ctrl+Shift+Enter鍵得1月份酸雨平均pH值。此處用到邏輯函數(shù)IF()，當(dāng)某月所有降水pH值均大于或等于5.6，則Excel不將其統(tǒng)計(jì)在內(nèi)，IF函數(shù)在單元格中顯示“本月沒(méi)有酸雨”，當(dāng)該月降水中有pH值小于5.6時(shí)，則Excel僅統(tǒng)計(jì)其中pH值小于5.6的降水的平均pH值。

在E5至E15中依此分別輸入相應(yīng)月份的數(shù)組公式得到各月的酸雨平均pH值。在E16單元格中輸入“{=-LOG10(SUM((pH5＜＞0)*(pH5＜5.6)* 降雨量5*10^(-(pH5＜ ＞0)*(pH5＜5.6)*pH5))/SUM((pH5＜ ＞0)*(pH5＜5.6)*降雨量5))}”得到全年酸雨平均pH值。

4.5 創(chuàng)建酸雨頻率公式

依據(jù)3節(jié)中式6酸雨頻率的定義，輸入酸雨頻率的數(shù)組公式。方法是，點(diǎn)擊單元格F4，在編輯欄中輸入“=SUM(IF((月份5=1)*(pH5＜＞0)*(pH5＜5.6)，1))/SUM(IF((月份 5=1)*(pH5＜ ＞0)，1))”，然后按 Ctrl+Shift+Enter鍵完成1月的數(shù)組公式的輸入，其它月份照前述方法輸入。公式中用到兩個(gè)函數(shù)IF()和SUM()，以這兩個(gè)函數(shù)的組合來(lái)實(shí)現(xiàn)多條件下的計(jì)數(shù)。分子中IF函數(shù)計(jì)算值的含義是當(dāng)同時(shí)滿足①月份為1月②pH值不為0③pH值小于5.6三個(gè)條件時(shí)即返回值1，然后由SUM函數(shù)對(duì)其求和，得到1月份中出現(xiàn)酸雨的次數(shù)。分母中IF函數(shù)計(jì)算值的含義是當(dāng)同時(shí)滿足①月份為1月②pH值不為0兩個(gè)條件時(shí)即返回值1，然后由SUM函數(shù)對(duì)其求和，得到1月份中所有降水的次數(shù)。

對(duì)年酸雨頻率的計(jì)算，則要簡(jiǎn)單一些，僅需要滿足pH值小于5.6一個(gè)條件即可，這樣可用到單條件計(jì)數(shù)函數(shù)COUNTIF，具體方法是：點(diǎn)擊單元格F16，在編輯欄中輸入“{=COUNTIF(pH5，“＜ 5.6”)/COUNT(pH5)}”完成編輯。公式中函數(shù)COUNTIF對(duì)pH小于5.6的降水次數(shù)計(jì)數(shù)，函數(shù)COUNT對(duì)所有降水次數(shù)計(jì)數(shù)，從而算得酸雨出現(xiàn)次數(shù)的百分比。

4.6 創(chuàng)建降雨量計(jì)算公式

如上方法，在 G4單元格中輸入“{=SUMIF(月份5，1，降雨量5)}”。這里用到函數(shù) SUMIF()，其含義為：當(dāng)月份是1月時(shí)，對(duì)降雨量求和，計(jì)算出1月份的降雨量。同樣在G5至G15單元格中分別輸入相應(yīng)的公式，不過(guò)要將“1”改為相應(yīng)的月份數(shù)，從而計(jì)算出各月降雨量，最后在G16中輸入“{=SUM(降雨量5)}”，可得到全年的降雨量。

4.7 顯示結(jié)果

所有公式輸入完畢后，工作表會(huì)自動(dòng)計(jì)算出結(jié)果，如圖3所示。在“酸雨平均pH值”列中，對(duì)應(yīng)五月、十月、十一月出現(xiàn)“本月沒(méi)有酸雨”，表示該月所有降雨pH均大于5.6，單元格顯示“該月沒(méi)有酸雨”以提示該月沒(méi)有酸雨出現(xiàn)。

圖3 統(tǒng)計(jì)結(jié)果

5 繪圖

圖4 各月降水平均pH趨勢(shì)圖

圖5 各月降雨量圖

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

Excel還具有強(qiáng)大的圖表繪制功能，可將統(tǒng)計(jì)的數(shù)據(jù)以圖表形式形象地表達(dá)出來(lái)，如圖4是各月降水平均pH趨勢(shì)圖。方法是：“插入”→“圖表”→“標(biāo)準(zhǔn)類型”中選“XY散點(diǎn)圖”→“數(shù)據(jù)區(qū)域”中選好相應(yīng)數(shù)據(jù)區(qū)域→輸入相應(yīng)標(biāo)題→更改系列名為“降水平均pH值”和酸雨平均pH值，得到圖4。繪制各月降雨量圖的菜單操作方法是：“插入”→“圖表”→“標(biāo)準(zhǔn)類型”中選“柱形圖”→“數(shù)據(jù)區(qū)域”，選好相應(yīng)數(shù)據(jù)區(qū)域→輸入相應(yīng)標(biāo)題→更改系列名為“降雨量”，得到圖5。

經(jīng)過(guò)嘗試，將Excel和數(shù)據(jù)統(tǒng)計(jì)有機(jī)的結(jié)合起來(lái)，給出了用Excel進(jìn)行酸雨統(tǒng)計(jì)分析和圖表制作的一般方法，利用計(jì)算機(jī)進(jìn)行自動(dòng)化的運(yùn)算和圖表處理，方法簡(jiǎn)捷、快速高效，可比較方便地從數(shù)據(jù)中分析出所需要的信息。

［1］［2］國(guó)家環(huán)境保護(hù)總局.空氣和廢氣監(jiān)測(cè)分析方法(第四版)［M］.北京：中國(guó)環(huán)境科學(xué)出版社，2003：298 ～307.

［3］(美)John Walkenbach.Excel2002 公式與函數(shù)應(yīng)用寶典［M］.北京：電子工業(yè)出版社，2002：261～295.

The Application of Excel in Acid Rain Statistical Analysis

ZHU Zheng-xun
(Wuxue Environmental Monitoring Station，Wuxue435400 Hubei)

Making use of the powerful calculation，formula editing，statistics and graphics function about the office software Excel of the Microsoft company in the acid rain statistical analysis，by the auto calculation and the graphic processing of the computers which is simple，rapid and efficient in order to analyze the demanded information from the statistics.

Excel;Acid rain;Statistics;Function;Array formula

TP317.3

A

1672-1047(2011)06-0096-04

10.3969/j.issn.1672-1047.2011.06.25

2011-11-08

朱正勛，男，湖北武穴人，工程師。研究方向：環(huán)境監(jiān)測(cè)方法研究及統(tǒng)計(jì)分析。

［責(zé)任編輯：羅幼平］