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

朱正勛

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

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

朱正勛

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

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

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

1 前言

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

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

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

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

3 計算方法

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

式中：

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

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

綜合以上四式，可得到：

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

4 酸雨統(tǒng)計

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

4.1 創(chuàng)建名稱

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

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

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

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

點擊圖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的事實，結果返回TRUE，IF函數(shù)返回2005年1月份且所測pH值不為0的降水pH值數(shù)據(jù)。用MIN函數(shù)返回這些數(shù)據(jù)中最小值，MAX函數(shù)返回這些數(shù)據(jù)中最大值，用“＆“～”＆”插入字符“～”將之聯(lián)接。數(shù)組公式輸入完畢后在C4單元格中會出現(xiàn)“4.50～6.18”的計算結果，表示2005年1月份的降雨pH范圍是4.50到6.18。

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

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

基于對本文3節(jié)中式5的了解，可以創(chuàng)建降水平均pH公式了。點擊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ù)組公式輸入完成。這時D4單元格中會出現(xiàn)“4.82”的計算結果。相應地在D5至D15輸入各月的數(shù)組公式，只是將“(月份5=1)”中的1換成相應月份的數(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)計與降水平均pH相似，區(qū)別之處是酸雨只統(tǒng)計降水中pH值小于5.6的降水平均pH值，因此需要在降水平均pH公式的基礎上加上約束條件“(pH5＜5.6)”。在E4單元格中輸入“=IF(SUM((pH5＜ ＞0)*(pH5＜5.6)*(月份5=1)*降雨量5)=0，“本月沒有酸雨”，-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()，當某月所有降水pH值均大于或等于5.6，則Excel不將其統(tǒng)計在內，IF函數(shù)在單元格中顯示“本月沒有酸雨”，當該月降水中有pH值小于5.6時，則Excel僅統(tǒng)計其中pH值小于5.6的降水的平均pH值。

在E5至E15中依此分別輸入相應月份的數(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ù)組公式。方法是，點擊單元格F4，在編輯欄中輸入“=SUM(IF((月份5=1)*(pH5＜＞0)*(pH5＜5.6)，1))/SUM(IF((月份 5=1)*(pH5＜ ＞0)，1))”，然后按 Ctrl+Shift+Enter鍵完成1月的數(shù)組公式的輸入，其它月份照前述方法輸入。公式中用到兩個函數(shù)IF()和SUM()，以這兩個函數(shù)的組合來實現(xiàn)多條件下的計數(shù)。分子中IF函數(shù)計算值的含義是當同時滿足①月份為1月②pH值不為0③pH值小于5.6三個條件時即返回值1，然后由SUM函數(shù)對其求和，得到1月份中出現(xiàn)酸雨的次數(shù)。分母中IF函數(shù)計算值的含義是當同時滿足①月份為1月②pH值不為0兩個條件時即返回值1，然后由SUM函數(shù)對其求和，得到1月份中所有降水的次數(shù)。

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

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

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

4.7 顯示結果

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

圖3 統(tǒng)計結果

5 繪圖

圖4 各月降水平均pH趨勢圖

圖5 各月降雨量圖

6 結束語

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

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

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

［3］(美)John Walkenbach.Excel2002 公式與函數(shù)應用寶典［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)測方法研究及統(tǒng)計分析。

［責任編輯：羅幼平］