使用Excel提高環境質量數據統計效率

鄺仕升，陳火林

（江門市環境監測中心站，廣東 江門 529000）

1 引言

隨著社會經濟的迅速發展，人們的生活水平得到了很大提高，但隨之而來的環境污染問題也日益突出，國家和社會對環境保護的重視程度不斷加強，導致環境監測壓力不斷增大，環境質量監測數據量也隨之劇增。因此，提高環境質量數據統計效率，也就成為目前環境管理工作者迫在眉睫的問題。

本文以地表水的數據統計和類別判斷為例，探討如何使用Excel提高環境質量數據統計效率。

2 地表水統計概況

江門市每月地表水常規監測斷面共計四十余個，每個斷面的監測垂線點位、頻次各有差異，監測項目均為水溫、pH值、溶解氧、高錳酸鹽指數、化學需氧量、五日生化需氧量、氮氨、總磷、銅、鋅、氟化物、硒、砷、汞、鎘、六價鉻、鉛、氰化物、揮發酚、石油類、陰離子表面活性劑、硫化物和糞大腸菌群等24項。每月監測數據收集完成后，對各斷面各監測項目的均值進行統計，對各斷面的水質達標情況進行類別判斷，目的是更好的為環境主管部門服務。

由于監測斷面和監測項目繁多，使得數據統計和類別判斷的工作量非常大。若采用常規的排序或篩選方法，逐個斷面輪流統計并判別，效率低下；若采用肉眼觀察和借助均值計算的方法，則判別工作完全無法進行，特別是對于斷面名稱、時間、點位等字段混亂排列甚至出現間斷的空白行的情況，此方法對各斷面各項目逐一統計，逐一判斷類別，耗時費力，由于人為因素，導致判別結果極易出錯。

3 使用Excel提高工作效率

3.1 斷面信息及監測結果

表1截取了“地表水”工作薄內“數據區”工作表中斷面A、B、C的部分原始監測數據，包含：高錳酸鹽指數、化學需氧量、氨氮和糞大腸菌群4個項目，以此為例。

3.2 統計結果

表2為監測斷面均值及類別判斷結果。截取自“地表水”工作薄內的“均值類別判斷”工作表。

各單元格公式如下：

各斷面原始數據：

A13：利用數組公式{=INDEX(數據區!A$1:A$100,SMALL(IF(數據區!$A$1:$A$100="斷面 A",ROW(數據區!$A$1:$A$100),""),ROWS($1:1)))}

A14：利用數組公式{=INDEX(數據區!A$1:A$100,SMALL(IF(數據區!$A$1:$A$100="斷面 A",ROW(數據區!$A$1:$A$100),""),ROWS($1:2)))}

A15：利用數組公式{=INDEX(數據區!A$1:A$100,SMALL(IF(數據區!$A$1:$A$100="斷面 A",ROW(數據區!$A$1:$A$100),""),ROWS($1:1)))}

A16：利用數組公式{=INDEX(數據區!A$1:A$100,SMALL(IF(數據區!$A$1:$A$100="斷面 A",ROW(數據區!$A$1:$A$100),""),ROWS($1:2)))}

A17：利用數組公式{=INDEX(數據區!A$1:A$100,SMALL(IF(數據區!$A$1:$A$100="斷面 A",ROW(數據區!$A$1:$A$100),""),ROWS($1:3)))}

A18：利用數組公式{=INDEX(數據區!A$1:A$100,SMALL(IF(數據區!$A$1:$A$100="斷面 A",ROW(數據區!$A$1:$A$100),""),ROWS($1:4)))}

A19：利用數組公式{=INDEX(數據區!A$1:A$100,SMALL(IF(數據區!$A$1:$A$100="斷面 A",ROW(數據區!$A$1:$A$100),""),ROWS($1:1)))}

A20：利用數組公式{=INDEX(數據區!A$1:A$100,SMALL(IF(數據區!$A$1:$A$100="斷面 A",ROW(數據區!$A$1:$A$100),""),ROWS($1:2)))}

A21：利用數組公式{=INDEX(數據區!A$1:A$100,SMALL(IF(數據區!$A$1:$A$100="斷面 A",ROW(數據區!$A$1:$A$100),""),ROWS($1:3)))}

A22：利用數組公式{=INDEX(數據區!A$1:A$100,SMALL(IF(數據區!$A$1:$A$100="斷面 A",ROW(數據區!$A$1:$A$100),""),ROWS($1:4)))}

A23：利用數組公式{=INDEX(數據區!A$1:A$100,SMALL(IF(數據區!$A$1:$A$100="斷面 A",ROW(數據區!$A$1:$A$100),""),ROWS($1:5)))}

A24：利用數組公式{=INDEX(數據區!A$1:A$100,SMALL(IF(數據區!$A$1:$A$100="斷面 A",ROW(數據區!$A$1:$A$100),""),ROWS($1:6)))}

E13~H24列公式如此類推。

斷面均值：

E10：=ROUND(AVERAGE(E13:E14),1)

E11：=ROUND(AVERAGE(E15:E18),1)

E12：=ROUND(AVERAGE(E19:E24),1)

F10~H12列公式如此類推。

保留小數位數隨監測項目而定。

類別判斷：

E7：=IF(E10<=E$1,1,IF(E10<=E$2,2,IF(E10<=E$3,3,IF(E10<=E$4,4,IF(E10<=E$5,5,6)))))

E8：=IF(E11<=E$1,1,IF(E11<=E$2,2,IF(E11<=E$3,3,IF(E11<=E$4,4,IF(E11<=E$5,5,6)))))

E9：=IF(E12<=E$1,1,IF(E12<=E$2,2,IF(E12<=E$3,3,IF(E12<=E$4,4,IF(E12<=E$5,5,6)))))

F7~H9列公式如此類推。

D7：=MAX(E7:H7)

D8：=MAX(E8:H8)

D9：=MAX(E9:H9)

C7：=IF(D7<=B7,"達標","超標")

C8：=IF(D8<=B8,"達標","超標")

C9：=IF(D9<=B9,"達標","超標")

條件格式設定：E7～H9：E7～H7 <=$B$7、E8～H8 <=$B$8、E9～H9 <=$B$9，字體顯白色，否則顯粗體紅色；C7～D9：D7<=$B$7、D8<=$B$8、D9<=$B$9，字體顯黑色，否則顯粗體紅色；這樣超標的斷面、項目和類別就一目了然。

4 結論

使用以上方法，極大的提高了數據統計和類別判斷的效率，整個過程被壓縮至一個步驟：錄入或導入原始數據，完全無需任何形式的排列或篩選操作，方法中的數組公式將各斷面各項目間斷或不間斷的原始數據提取后形成連續且各自獨立的數組，再分別被均值函數統計計算，最后通過條件函數進行自動判斷，得出各個斷面的水質類別結果，并利用條件格式標出超標項目所在列，具有直觀、快捷等優點。此方法還可推廣運用至環境質量空氣、噪聲等領域的數據統計和判別，為服務環境主管部門提供了一個快捷、有力、實用的小工具，節省了大量寶貴的時間。

1 起點文化.Excel 2007函數與公式自學寶典［M］.北京：電子工業出版社，2009

2 起點文化.Excel 2010函數與公式速查手冊［M］.北京：電子工業出版社，2011

3 起點文化.Excel 2007數據管理與分析處理［M］.北京：電子工業出版社，2009

4 九州數源.Excel 2007函數、圖表與數據分析［M］.北京：清華大學出版社，2009

5 Excel Home.Excel應用大全［M］.北京：人民郵電出版社，2008

表1

表2