Excel在酸雨统计分析中的应用

朱正勋

(武穴市环境监测站，湖北武穴435400)

Excel在酸雨统计分析中的应用

朱正勋

(武穴市环境监测站，湖北武穴435400)

将microsoft公司office系列软件Excel强大的计算、公式编辑、数据统计和绘图功能，应用在酸雨统计分析中，通过计算机自动运算和图表处理，方法简捷、快速、高效，便于从数据中分析出所需要的信息。

Excel;酸雨;数据统计;函数;数组公式

1 前言

酸雨是指pH值小于5.6的降水(雨或雪等)。酸雨测量一般要求逢雨必测，会有大量的数据产生，经常需要从中统计分析一些重要信息，如月或年降水pH值范围、月或年均降水pH值、月或年酸雨频率等，手工统计计算烦琐，费时费力，效率不高。microsoft公司 office系列软件 Excel是一种“表格”式数据综合管理与分析系统，用户在电子表格中输入数据，通过公式编辑，由电子表格程序自动对数据进行处理，即可完成计算、统计分析和绘图等功能。在实际工作中我们尝试借助Excel软件来进行酸雨数据的统计处理，获得了较好的效果。

2 获得数据

酸雨监测［1］的项目是pH和降雨量，每次降雨必须采样测试。连续降水超过24h时，每24h采集1次降水样品进行监测分析。目前武穴市酸雨是用降水降尘自动采样器自动采集，降雨量由标准雨量仪自动记录，与降水采样同步、平行地进行操作。每次采集后的降水样品在3℃ -5℃条件下用无色聚乙烯塑料瓶保存，再进入实验室分析，得到这次降水的pH值。降水量、降水时间数据可由降水降尘自动采样器自动记录打印出来。每次采样时间、降水量及实验室分析出的pH值的数据获得之后，将数据输入Excel工作表单元格中，格式如图1，汇总出年或月数据作为源数据以备后续统计分析用。

图1 酸雨监测结果汇总

3 计算方法

降水pH值平均值的计算采用(H+)浓度-雨量加权平均计算［2］，氢离子浓度单位为mol/L。

式中：

［H+］i——第i次降水的氢离子浓度mol/L;

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

综合以上四式，可得到：

酸雨频率是指降水中出现pH值小于5.6的次数占总的降水次数的百分比。

4 酸雨统计

一年或月的酸雨测量数据汇总完毕后需要统计，在统计中一般要统计月或年降水pH值范围、月或年均降水pH值、月或年酸雨频率。下面以武穴市2005年酸雨统计为例说明统计方法。统计计算之前需要在Excel工作簿新建一工作表，其格式如图2。具体步骤为：

4.1 创建名称

首先要在降水数据工作表中为包含降雨量、月份、pH值的单元格范围命名，这样做的好处是，可以让公式中单元格引用“文本化”易懂，增强公式的可读性，容易使用。以创建降雨量名称为例来说明创建的方法。首先选中包括2005年全年降雨量数据的单元格范围，然后在菜单中选择“插入”→“名称”→“定义”(或按 Ctrl+F3)。Excel显示“定义名称”对话框，在标有“在当前工作簿中的名称”文本框中键入“降雨量5”，表示2005年降雨量数据。然后单击“添加”按钮，继续向工作表中添加“pH5”和“月份5”的名称，当然必须确认相应“引用位置”的范围是正确的，这样创建名称完成。

4.2 创建降水pH范围公式

降水pH范围是指该月降雨中所测得的最低pH值到最高pH值之间的范围，可以用函数MIN()和MAX()来实现。在公式编辑中会用到数组公式［3］的概念，数组公式是Excel提供的高级功能，可以实现一些复杂的计算。所谓数组是一些元素的简单集合，元素可以共同参与运算或个别参与运算。以一月份降水pH范围为例说明数组公式编辑的实现方法。

图2 酸雨统计格式

点击图2所示工作表C4单元格，在编辑栏中输入“=MIN(IF((月份5=1)*(pH5＜＞0)，pH5))＆“～”＆MAX(IF((月份5=1)*(pH5＜ ＞0)，pH5))”，然后按 Ctrl+Shift+Enter键，编辑栏出现“{=MIN(IF((月份5=1)*(pH5＜ ＞0)，pH5))＆“～”＆MAX(IF((月份5=1)*(pH5＜ ＞0)，pH5))}”后数组公式编辑完成。公式中“(月份5=1)”表示2005年1月份，“IF((月份5=1)*(pH5 ＜ ＞0)，pH5)”函数中“(月份 5=1)*(pH5＜＞0)”是基于布尔值 TRUE*TRUE=TRUE的事实，结果返回TRUE，IF函数返回2005年1月份且所测pH值不为0的降水pH值数据。用MIN函数返回这些数据中最小值，MAX函数返回这些数据中最大值，用“＆“～”＆”插入字符“～”将之联接。数组公式输入完毕后在C4单元格中会出现“4.50～6.18”的计算结果，表示2005年1月份的降雨pH范围是4.50到6.18。

同样在C5至C15输入相应的数组公式，只是将“(月份5=1)”中的1换成相应月份的数字即可得到各月的降雨pH范围。在C16中输入“=MIN(pH5)＆“～”＆MAX(pH5)”，然后按 Ctrl+Shift+Enter键可得到全年的降雨pH范围。

4.3 创建降水平均pH公式

基于对本文3节中式5的了解，可以创建降水平均pH公式了。点击D4单元格，在编辑栏中输入“=-LOG10(SUM((pH5＜＞0)*(月份5=1)*降雨量5*10^(-(pH5＜＞0)*(月份5=1)*pH5))/SUM((pH5＜＞0)*(月份5=1)*降雨量5))”，然后按Ctrl+Shift+Enter键，编辑栏中出现“{=-LOG10(SUM((pH5＜＞0)*(月份5=1)*降雨量5*10^(-(pH5＜＞0)*(月份5=1)*pH5))/SUM((pH5＜＞0)*(月份5=1)*降雨量5))}”后数组公式输入完成。这时D4单元格中会出现“4.82”的计算结果。相应地在D5至D15输入各月的数组公式，只是将“(月份5=1)”中的1换成相应月份的数字即可得到各月的降水平均pH。在D16中输入“=-LOG10(SUM((pH5＜＞0)*降雨量5*10^(-(pH5＜＞0)*pH5))/SUM((pH5＜＞0)*降雨量5))”，然后按Ctrl+Shift+Enter键可得到全年的降水平均pH值。

4.4 创建酸雨平均pH公式

酸雨平均pH的统计与降水平均pH相似，区别之处是酸雨只统计降水中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值。此处用到逻辑函数IF()，当某月所有降水pH值均大于或等于5.6，则Excel不将其统计在内，IF函数在单元格中显示“本月没有酸雨”，当该月降水中有pH值小于5.6时，则Excel仅统计其中pH值小于5.6的降水的平均pH值。

在E5至E15中依此分别输入相应月份的数组公式得到各月的酸雨平均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 创建酸雨频率公式

依据3节中式6酸雨频率的定义，输入酸雨频率的数组公式。方法是，点击单元格F4，在编辑栏中输入“=SUM(IF((月份5=1)*(pH5＜＞0)*(pH5＜5.6)，1))/SUM(IF((月份 5=1)*(pH5＜ ＞0)，1))”，然后按 Ctrl+Shift+Enter键完成1月的数组公式的输入，其它月份照前述方法输入。公式中用到两个函数IF()和SUM()，以这两个函数的组合来实现多条件下的计数。分子中IF函数计算值的含义是当同时满足①月份为1月②pH值不为0③pH值小于5.6三个条件时即返回值1，然后由SUM函数对其求和，得到1月份中出现酸雨的次数。分母中IF函数计算值的含义是当同时满足①月份为1月②pH值不为0两个条件时即返回值1，然后由SUM函数对其求和，得到1月份中所有降水的次数。

对年酸雨频率的计算，则要简单一些，仅需要满足pH值小于5.6一个条件即可，这样可用到单条件计数函数COUNTIF，具体方法是：点击单元格F16，在编辑栏中输入“{=COUNTIF(pH5，“＜ 5.6”)/COUNT(pH5)}”完成编辑。公式中函数COUNTIF对pH小于5.6的降水次数计数，函数COUNT对所有降水次数计数，从而算得酸雨出现次数的百分比。

4.6 创建降雨量计算公式

如上方法，在 G4单元格中输入“{=SUMIF(月份5，1，降雨量5)}”。这里用到函数 SUMIF()，其含义为：当月份是1月时，对降雨量求和，计算出1月份的降雨量。同样在G5至G15单元格中分别输入相应的公式，不过要将“1”改为相应的月份数，从而计算出各月降雨量，最后在G16中输入“{=SUM(降雨量5)}”，可得到全年的降雨量。

4.7 显示结果

所有公式输入完毕后，工作表会自动计算出结果，如图3所示。在“酸雨平均pH值”列中，对应五月、十月、十一月出现“本月没有酸雨”，表示该月所有降雨pH均大于5.6，单元格显示“该月没有酸雨”以提示该月没有酸雨出现。

图3 统计结果

5 绘图

图4 各月降水平均pH趋势图

图5 各月降雨量图

6 结束语

Excel还具有强大的图表绘制功能，可将统计的数据以图表形式形象地表达出来，如图4是各月降水平均pH趋势图。方法是：“插入”→“图表”→“标准类型”中选“XY散点图”→“数据区域”中选好相应数据区域→输入相应标题→更改系列名为“降水平均pH值”和酸雨平均pH值，得到图4。绘制各月降雨量图的菜单操作方法是：“插入”→“图表”→“标准类型”中选“柱形图”→“数据区域”，选好相应数据区域→输入相应标题→更改系列名为“降雨量”，得到图5。

经过尝试，将Excel和数据统计有机的结合起来，给出了用Excel进行酸雨统计分析和图表制作的一般方法，利用计算机进行自动化的运算和图表处理，方法简捷、快速高效，可比较方便地从数据中分析出所需要的信息。

［1］［2］国家环境保护总局.空气和废气监测分析方法(第四版)［M］.北京：中国环境科学出版社，2003：298 ～307.

［3］(美)John Walkenbach.Excel2002 公式与函数应用宝典［M］.北京：电子工业出版社，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

朱正勋，男，湖北武穴人，工程师。研究方向：环境监测方法研究及统计分析。

［责任编辑：罗幼平］