鄺仕升，陳火林

（江門市環(huán)境監(jiān)測中心站，廣東 江門 529000）

1 引言

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

本文以地表水的數(shù)據(jù)統(tǒng)計和類別判斷為例，探討如何使用Excel提高環(huán)境質(zhì)量數(shù)據(jù)統(tǒng)計效率。

2 地表水統(tǒng)計概況

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

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

3 使用Excel提高工作效率

3.1 斷面信息及監(jiān)測結(jié)果

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

3.2 統(tǒng)計結(jié)果

表2為監(jiān)測斷面均值及類別判斷結(jié)果。截取自“地表水”工作薄內(nèi)的“均值類別判斷”工作表。

各單元格公式如下：

各斷面原始數(shù)據(jù)：

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

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

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

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

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

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

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

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

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

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

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

A24：利用數(shù)組公式{=INDEX(數(shù)據(jù)區(qū)!A$1:A$100,SMALL(IF(數(shù)據(jù)區(qū)!$A$1:$A$100="斷面 A",ROW(數(shù)據(jù)區(qū)!$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列公式如此類推。

保留小數(shù)位數(shù)隨監(jiān)測項目而定。

類別判斷：

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 結(jié)論

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

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

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

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

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

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

表1

表2