使用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