EXCEL在空气质量指数计算及环境空气质量分析中的应用

摘要 文章根据《环境空气质量标准》（GB3095-2012）中所列各污染物标准限值、《环境空气质量指数（AQI）技术规定（试行）》（HJ633-2012）中AQI的计算方法及《环境空气质量评价技术规范（试行）》（HJ663-2013）中规定的环境空气评价项目与评价方法，结合福州市环境空气监测数据，介绍如何利用excel 2003软件自动批量计算空气质量指数（AQI）、自动分析某时段的环境空气质量状况、自动绘制空气质量分级比例饼状图、自动生成主要污染物评价结果表等，为环境空气质量分析工作提供便利。

关键词 EXCEL；环境空气质量；AQI；自动计算

中图分类号X3 文献标识码A 文章编号 1674-6708（2014）121-0227-03

2013年1月1日起，京津冀、长三角、珠三角等重点区域以及直辖市和省会城市等共74个城市按照环境空气新标准《环境空气质量标准》（GB3095-2012）要求进行监测与评价。新标准增加了污染物监测项目，严格了部分污染物浓度限值。空气日报中，由包含六项污染物的空气质量指数（AQI）替换了原来包含三项污染物的空气污染指数（API），评价方法更加复杂，靠人工计算工作量非常大。一些软件虽有自动统计功能，但也存在局限性，例如本单位的软件尚不能统计AQI，上级环保部门数据库虽然功能较齐全，但只能进行整年或者整月的统计，而且必须是上报后的数据才能统计出结果，时效性欠佳。EXCEL 2003是一款简单易学且普及的软件，使用门槛低，无人员权限限制。前人曾探讨过应用EXCEL来计算评价单个AQI，但其在污染物浓度取值超出范围及存在两个以上首要污染物时存在漏洞，而且尚无对任意日期范围内自动统计及自动生成图表方面的研究。

本文介绍的EXCEL 2003软件的应用结果，只要在相应单元格中输入各项空气污染物浓度日均值，excel可自动批量计算每日空气质量指数，并显示空气质量级别及首要污染物、超标污染物；输入需要统计的起止日期，EXCEL便能自动统计给定日期范围内的有效天数，AQI最大值、最小值、均值及各级别空气质量的天数等信息，并自动生成空气质量各级别天数比例的饼状图；同时，输入统计时段，可自动生成一张包含各污染物最大日均值、平均值、特定百分位数、单项污染指数、最大日超标倍数、超标率等项目的评价表，方便且直观。

1原理

1.1空气质量指数（AQI）的计算

污染物项目P的空气质量分指数按式（1）计算：

环境空气质量指数及空气质量分指数的计算结果应全部进位取整数，不保留小数。空气质量指数的范围为0500，指数越大，级别越高，说明污染越严重。

1.2首要污染物及超标污染物的确定方法

AQI大于50时，IAQI最大的污染物为首要污染物，若IAQI最大的污染物为两项或两项以上时，并列为首要污染物。IAQI大于100的污染物为超标污染物。

1.3基本评价项目、评价标准及评价方法

基本评价项目包括二氧化硫（SO2）、二氧化氮（NO2）、一氧化碳（CO）、臭氧（O3）、可吸入颗粒物（PM10）、细颗粒物（PM2.5）共6项。各项目评价执行《环境空气质量标准》（GB3095-2012）中的二级标准。

污染物浓度评价结果符合GB3095-2012和HJ663-2013的规定，即为达标。其中，污染物年评价达标是指该污染物年平均浓度（CO和O3除外）和特定百分位数浓度（SO2、NO2日均值的第98百分位数，CO、PM10、PM2.5日均值的第95百分位数，O3的日最大8小时滑动平均值的第90百分位数）同时达标。

2 应用EXCEL的函数公式编制相关统计表

EXCEL工作簿包含“日报AQI”、“环境空气质量统计”和“主要污染物评价结果”三张EXCEL表格。我们设置白色背景的单元格为输入区域，深绿色背景的单元格为字段区域，浅绿色背景的表格为函数自动统计结果的区域。

2.1“日报AQ1”表格的制作

表格的第一、二行用来显示字段名，本表包含18列，A列至G列为输入区域，分别用于输入日期及六项基本空气污染物的日均值、H列至R列为自动计算输出区域，分别显示六项污染物的空气质量分指数IAQIn、AQI、空气质量级别、空气质量类别、首要污染物和超标污染物。见图1。

根据空气质量分指数IAQIn的计算方法，应用IF嵌套函数进行分段线性计算，同时应用ROUNDUP函数实现计算结果的进位取整，即可计算出相应污染物的质量分指数。以SO2为例说明空气质量分指数的计算方法，在H3单位格内输入公式：=ROUNDUP（IF（B3

这样，只要在B3单元格中输入一个SO2日均值浓度，H3单元格即自动显示SO2的质量分指数。NO2、PM10、PM2.5、CO、O3的空气质量分指数同理可得。

N3单元格利用MAX函数确定空气质量指数AQI，同时利用IF、AND函数排除分指数均为0时的异常情况，公式为：=IF（AND（H3=0，I3=0，J3=0，K3=0，L3=0，M3=0），””，MAX（H3，I3，J3，K3，L3，M3））。

O3单元格利用IF嵌套函数实现对空气质量级别的描述。公式为：=IF（N3=""，""，IF（N3

P3单元格利用IF嵌套函数实现对空气质量类别的描述。公式为：=IF（O3=""，""，IF（O3="一级"，"优"，IF（O3="二级"，"良"，IF（O3="三级"，"轻度污染"，IF（O3="四级"，"中度污染"，IF（O3="五级"，"重度污染"，"严重污染"））））））。

Q3单元格显示首要污染物。AQI为空值或小等于50时，不显示首要污染物。当有两种或两种以上首要污染物时，则能将所有首要污染物同时显示。公式为：=IF（N3=""，""，IF（N3

R3单元格显示超标污染物。AQI为空值或小等于100时，不显示超标污染物。公式为：=IF（N3=""，""，IF（N3100，"二氧化硫"，""））&（IF（I3>100，"二氧化氮"，""））&（IF（J3>100，"可吸入颗粒物"，""））&（IF（L3>100，"一氧化碳"，""））&（IF（M3>100，"臭氧日最大8小时值"，""））&（IF（K3>100，"细颗粒物"，""））））。

将A3至R3的公式自动向下填充（假定向下填充至第10000行）。

对手动输入的A列至G列进行数据有效性设置，可防止输入不合适的数据而扰乱后期的统计结果。

2.2 “环境空气质量统计”表格制作

如图2，在深绿色背景的单元格内输入需要统计的项目字段，预留B1、D1单元格，用来手动输入统计起止日期。B2至B17单元格及D3至D8单元格为自动计算输出区域，即显示给定日期范围内相应的统计数据。下面分别介绍：

B2单元格显示给定日期范围内AQI不为空值的天数，利用数组公式可实现：=SUMPRODUCT（（日报AQI！$A$3：$A$10000>=B1）*（日报AQI！$A$3：$A$10000

B3-B8单元格分别显示给定日期范围内不同质量类别的天数。以“优的天数”为例，B3的公式为：=SUMPRODUCT（（日报AQI！$A$3：$A$10000>=B1）*（日报AQI！$A$3：$A$10000

D3-D8单元格分别显示给定日期范围内不同质量类别的天数比例，以“优的天数比例”为例，D3的公式为：=B3/B2。

B9单元格显示给定日期范围内的AQI均值，保留整数。公式为：=ROUNDUP（AVERAGE（IF（（日报AQI！$A$3：$A$10000>=B1）*（日报AQI！$A$3：$A$10000

B10和B11单元格分别显示给定日期范围内AQI的最小值和最大值，以最小值为例，公式为：=MIN（IF（（日报AQI！$A$3：$A$10000>=B1）*（日报AQI！$A$3：$A$10000

B12-B17单元格分别显示给定日期范围内各首要污染物的天数。以“首要污染物为可吸入颗粒物的天数”为例，公式为：=SUM（N（（日报AQI！A$3：A$10000>=B1）*（日报AQI！A$3：A$10000

在C9：D17区域范围内，插入饼状图，源数据选取“=环境空气质量统计！C3：D8”，根据《环境空气质量指数（AQI）技术规定（试行）》（HJ633-2012）规定选择表征颜色，即生成给定日期范围内的不同空气质量级别天数比例的扇形图，简洁美观。

为了避免输入错误的日期格式，可在菜单栏“数据”-“有效性”中选择“允许日期”，进行相应的设置即可。

2.3 “主要污染物评价结果”表格制作

“主要污染物评价结果”表格主要统计指定日期范围内的各空气污染指标的大值日均值、平均浓度、特定百分位数、单项指数、日最大超标倍数、超标率等。首先，设计好表格格式，输入污染物指标名称及评价项目，标记上深绿色背景，然后在需要利用EXCEL公式自动计算的单元格范围标记上浅绿色背景，如图3。

下面以二氧化硫为例说明各评价项目的计算公式。

B4单元格计算最大日均值，公式为：=MAX（IF（（日报AQI！A3：A10000>=E2）*（日报AQI！A3：A10000

B5单元格计算平均浓度，公式为：=ROUND（AVERAGE（IF（（日报AQI！A3：A10000>=E2）*（日报AQI！A3：A10000

B6单元格计算特定百分位数，公式为：=ROUND（PERCENTILE（IF（（AQI计算！$A$3：$A$10000>=$E$2）*（AQI计算！$A$3：$A$10000

B7单元格计算单项指数，公式为：=ROUND（MAX（B5/0.06，B6/0.15），2）。

B8单元格计算最大日超标倍数，公式为：=IF（B4

B9单元格计算超标率，公式为：=ROUND（SUMPRODUCT（（AQI计算！$A$3：$A$10000>=E2）*（AQI计算！$A$3：$A$100000.15））/SUMPRODUCT（（AQI计算！$A$3：$A$10000>=E2）*（AQI计算！$A$3：$A$10000

3 数据验证

3.1 “日报AQI”批量计算结果的验证

将我市2013年1月1日-2013年12月31日监测的六项污染物日均值浓度复制到工作表“日报AQI”中，EXCEL自动计算得出分指数、空气质量指数、首要污染物、超标污染物等结果，与福建省环境监测数据管理信息系统中的统计结果完全一致。

3.2 “环境空气质量统计”表及“主要污染物评价结果”表的计算结果验证

在“环境空气质量统计”及“主要污染物评价结果”表格的空白单元格分别输入起始日期“2013-1-1”和终止日期“2013-12-31”，excel自动统计的结果与福建省环境监测数据管理信息系统中的统计结果一致。

4 结论

用EXCEL编制公式来自动计算空气污染指数及自动评价，只要电脑有EXCEL 2003以上版本就可以使用，不需要网络连接，没有权限限制，可以实时计算，成本忽略不计，而且随着评价方法的改变，更改公式也很容易，是环境分析人员日常统计的好帮手。

参考文献

[1]GB 3095-2012环境空气质量标准[S].

[2]HJ 633-2012环境空气质量指数（AQI）技术规定（试行）[S].

[3]HJ 663-2013环境空气质量评价技术规范（试行）[S].

[4]雏志资讯，龙建祥，张铁军.Excel函数与公式综合应用技巧[M].北京：人民邮电出版社，2011.