白爱民

(云南省环境监测中心站,云南昆明650034)

EXCEL与底泥数据的分析

白爱民

(云南省环境监测中心站,云南昆明650034)

以《环境质量报告书》中对底泥监测数据的处理与分析为例,展示了EXCEL的几项强大功能和在实际工作中的使用方法与步骤。

EXCEL应用;环境监测;环境质量报告书;应用

计算机技术在环境监测中的作用日趋重要,尤其体现在对大量数据的处理和分析上[1]。Microsoft Excel是最优秀的电子表格软件之一,它在数据综合管理和分析方面具有功能强大、技术先进、使用方便等特点[2]。EXCEL是目前数据处理工作者使用最频繁的工具之一,其强大的内置函数几乎无所不能[3]。使用好EXCEL提供的各项功能可有效提高数据处理效率,促进环境信息化建设的发展。

《环境质量报告书》(下称《报告书》)是年度环境质量总体状况和环境监测综合成果的重要体现,是环境管理的重要依据之一。环境质量报告书的编制需要汇总处理大量的环境监测数据,EXCEL强大的功能几乎可以完全满足编制环境质量报告书的数据处理和分析需求。

1 函数应用基础[4]

Excel函数是预先定义,可执行计算、分析等处理数据任务的特殊公式。以常用的求和函数SUM为例,它的语法是:“SUM(number1,number2,)”。其中“SUM”称为函数名称,一个函数只有唯一的一个名称,它决定了函数的功能和用途。函数名称后紧跟左括号,接着是用逗号分隔的称为参数的内容,最后用一个右括号表示函数结束。

参数是函数中最复杂的组成部分,它规定了函数的运算对象、顺序或结构等。假如一个函数可以使用多个参数,那么参数与参数之间使用半角逗号进行分隔。参数可以是常量(数字和文本)、逻辑值(例如TRUE或FALSE)、数组、错误值(例如#N/A)或单元格引用(例如E1:H1),甚至可以是另一个或几个函数等。参数的类型和位置必须满足函数语法的要求,否则将返回错误信息。

函数与公式既有区别又互相联系。如果说前者是Excel预先定义好的特殊公式,后者就是由用户自行设计对工作表进行计算和处理的公式。以公式“=SUM(E1:H1)3A1+26”为例,它要以等号“=”开始,其内部可以包括函数、引用、运算符和常量。上式中的 “SUM(E1:H1)”是函数,“A1”则是对单元格A1的引用(使用其中存储的数据),“26”则是常量,“3”和 “+”则是算术运算符(另外还有比较运算符、文本运算符和引用运算符)。

如果函数要以公式的形式出现,它必须有两个组成部分,一个是函数名称前面的等号,另一个则是函数本身。

2 环境质量报告书的作用与内容

《报告书》是年度环境质量总体状况和环境监测综合成果的重要体现,是环境管理的重要依据之一。《报告书》须根据国家环保局颁发的《全国环境监测报告制度》规定与要求,按照国家环境监测总站《关于进一步加强环境质量综合分析工作的通知》精神完成编制工作。《报告书》的编制需要汇总处理大量的环境监测数据,并与上一年的相应数据进行对比,对环境质量状况进行系统的分析,指出主要环境问题和环境质量发展态势,并根据当地实际情况提出改善环境质量的相应对策和建议。

《报告书》的基本内容为:环境监测工作、主要污染物排放、地表水环境质量、城市空气环境质量、城市声环境质量等。

3 使用EXCEL的实例

本文以地表水环境质量中的底泥数据处理为例,说明如何使用EXCEL来完成数据处理和分析任务,满足编写《报告书》的需求。

处理环境底泥数据的基本需求及步骤:

(1)按要求处理基础数据;

(2)按监测点和监测元素分别将当年与上年的数据对应地汇总在一张新表中;

(3)按监测点对各监测元素分别与上一年度监测值进行比较,标明上升或下降,并计算出上升或下降幅度;

(4)按各监测元素,找出各监测元素最大值、最大上升幅度、最小值、最大下降幅度、总上升或总下降数据个数及相应百分比;

(5)按各监测元素,找出各监测元素值较上年上升测点数和下降测点数和相应比值、最大值、最大上升幅度、最小值、最大下降幅度对应的监测点。

为完成数据分析,按上述需求和步骤,先做第(1)和第(2)项工作。

要将不同年度的数据按测点和监测元素分别进行比较,就必须将数据按测点和监测元素分别排序。在底泥分析时,测点和监测元素的位置有一定的要求和规律性,将测点数据和监测元素数据分别定义成两个新序列,在数据处理时,利用EXCEL的“自定义序列”排序功能,按行或列分别排序可迅速按需求统一测点和监测元素相对位置。统一各年度的表格格式后,再汇总成数据分析表就很容易了,完成后的基础数据分析表见表1。

为确保数据的保密性,用测点1、测点2、……来代表实际监测点名称,如滇池草海、观音山、个旧湖……;用元素1、元素2、……来代表各实际监测元素,如汞、砷、铅、……。在例子中只采用了10测点和部分2个元素,以说明如何快速输入函数,完成底泥数据的分析。

表1 图表示例1

为完成第(3)项工作,在D4单元格和E4单元格中分别输入公式:“=IF(B4>C4,"u","d")”和 “=(B4-C4)/C4”,接着选中D4和E4单元格,执行复制命令,再选择表中的所有空白单元格(D5:E13和H4:I13),然后执行粘贴命令,第(3)项工作宣告完成。

其中,为使表格显得简捷,用字母u代表监测值较上年升高,字母d代表监测值较上年下降。

为完成最后两项工作,即基本需求及步骤的第(4)、第(5)步,在上述EXCEL工作表的底泥数据分析表下方,再制作一个底泥数据分析续表:可使数据分析表与上表中元素列对应、方便查询,更重要的是保证通过复制粘贴获得的公式能自动并正确地调整其引用单元格和区域。在B17至B25单元格中输入公式:

“=COUNTIF(D4:D13,"u")”、“=COUN2 TIF(D4:D13,"d")”、“=SUM(B16:B17)”、“=B16/B18”、 “=B17/B18”、 “=MAX(B4:B13)”、 “=MAX(E4:E13)”、 “=M I N(B4:B13)”、“=M I N(E4:E13)”;

在C22和C24单元格中分别输入公式:

“=I NDEX(＄A＄4:＄I＄13,MATCH(B21,B＄4:B＄13,0),1)”、“=I NDEX(＄A

＄4:＄I＄13,MATCH(B23,E＄4:E＄13,0),1)”。其中C23或C25的公式通过分别复制C22和C24来实现。

在B17至B25单元格中的公式仅采用了相对引用,公式的含义也较简单,分别表示条件计数、求和、求最大值和求最小值。

在C22至C25单元格各包括有MATCH和I N2 DEX两个函数,且MATCH嵌套于I NDEX函数中,其公式的含义是:先用MATCH函数找到基础数据区中满足某条件的值的位置,再据此查询出对应的测点名。这几个公式采用了相对引用、绝对引用和混和引用。其区别在于,采用相对引用时,如果公式所在单元格的位置改变,引用也随之改变。采用绝对引用时,如果公式所在单元格的位置改变,绝对引用保持不变。混合引用分为绝对列和相对行,或是绝对行和相对列两种。如果公式所在单元格的位置改变,则相对引用改变,而绝对引用不变。如果多行或多列地复制公式,相对引用自动调整,而绝对引用不作调整。

在进行数据处理和分析时,常常会在EXCEL表中使用大量的函数和公式,充分利用EXCEL的引用功能,可大大减少公式输入工作量,即只须输入一套最基础的公式,再采用复制粘贴功能,就能完成全部公式的输入,从而快速正确地获得所需要的结果。

表2 底泥数据分析续表

要完成数据分析续表的其它部分,只须选中底泥数据分析续表中元素1所属的各项公式,复制到元素2所属的相应单元格即可。即复制B17至B25单元格的内容,粘贴到F17至F25,复制C22至C25单元格的内容,粘贴到G22至G25,则数据分析续表的编制工作完成。

G22的公式是从C22复制过来的,让我们比较一下G22单元格和C22单元格的内容:

C22:“=I NDEX(＄A＄4:＄I＄13,MATCH(B21,B＄4:B＄13,0),1)”

G22:“=I NDEX(＄A＄4:＄I＄13,MATCH(F22,F＄4:F＄13,0),1)”

可看出,公式复制到新的位置后,相对引用中的列和混合引用中的相对列已经自动调整正确,而绝对引用保持不变。因此为确保通过复制粘贴获得的公式能产生正确的计算结果,必须规范化表格的制作。

在上述实例中,只选择了10个监测点和2个监测元素,底泥数据分析续表相对简单。实际上,监测点增多时只须调整各式公式中的数据起始行和结束行的行标号即可,而监测元素增加,只须将监测元素1所包括的已经完成的公式复制到其它监测元素的相应的位置就可实现全部计算。即使监测点和监测元素都增加10倍,增加的工作量可以说是微乎其微。换句话说,在上述分析表中监测点和监测元素越多,就越显示出使用EXCEL函数的优越性。

另外,对于已经建立好的分析表而言,还有一个很大的优势,即如果基础数据发生改变(因上报错误或输入错误等),只须修改数据分析表中相应的基础数据,其新分析结果自动计算完成。

4 结语

《环境质量报告书》的编制需要汇总大量数据,并做大量的数据处理与分析比较,这些工作通常在EXCEL中来完成。EXCEL功能非常强大,在环保系统内几乎都是用它来处理数据和编制报表,但很多人仅仅停留在初级水平,远没有体现出EX2 CEL的优势。用好EXCEL的 “自定义序列”排序功能可迅速统一测点和监测元素相对位置,熟悉内置函数和掌握各种引用方法可轻松完成数据分析工作。总之,在底泥数据分析时熟练掌握EXCEL的这几项功能可大大减少工作量,降低出错率,提高工作效率。由于EXCEL易学易懂,即使是不熟悉软件编程语言且不太熟悉EXCEL的人员也能很快掌握这些功能。

[1]单文坡.EXCEL和OR IGI N在环境监测数据处理中的应用[J].石家庄职业技术学院学报,2008,20(2).

[2]荣钦科技.EXCEL2003在统计学中的应用[M].北京:电子工业出版社,2005.

[3]Excel研究组.Excel2007函数与公式速查手册[M].北京:电子工业出版社,2008.

[4]张迎新.EXCEL2003函数应用完全手册[M].http://www.doc88.com/p-54803971.html,2004.

Sludge Data Analysis by Using EXCEL

BA IAi2min
(Yunnan EnvironmentalMonitoring Center,Kunming Yunnan 650034 China)

The analysis of the sludge data is taken as an example to reveal the s mart function of EXCEL software.The steps of real application of EXCEL are discovered in the article.

EXCEL application;environmentalmonitoring;environmental quality report;application

X11

A

1673-9655(2010)05-0109-04

2010-08-18