做环境监测和数据分析的同行应该都有体会，每天拿到监测站的小时浓度数据后，最烦的一步就是把PM2.5、PM10、SO2、NO2、CO、O3这六项浓度换算成IAQI，再取最大值出AQI，最后判断今天的首要污染物是谁。以前我都是打印一张AQI分段表放在工位上，拿着计算器手动查、手动插值，一天几十个站点下来，眼睛看花是小事，算错档位、搞错单位才是真要命。后来我花了一个下午在Excel里把这套计算全部函数化，只需要输入六项污染物的浓度，AQI、首要污染物、空气质量等级自动带出。这篇文章就把这套模板完整拆开讲：分段表怎么建、IAQI线性插值公式怎么写、首要污染物并列时怎么处理，全部给可直接复制的公式。
1. 手动算AQI到底慢在哪：查表、插值、并列
1.1 查表：每个人手里都有一张纸
AQI不是简单的浓度相加取平均，污染物浓度和IAQI之间是分段线性关系。比如PM2.5浓度从0到35，IAQI从0到50；浓度到了35到75这一段，同样是单位浓度变化，IAQI的变化斜率就变了。所以必须根据浓度落在哪个档位，选择对应区间的上下限做插值。麻烦就麻烦在这：六项污染物各有7档左右，档位多，浓度区间还不一样。PM2.5的档位是0-35、35-75、75-115这么排，PM10又是0-50、50-150、150-250，NO2又是0-40、40-80、80-180，几乎没有一个污染物和其他污染物共用同一套档位。时间一长，人很容易把档位记混，尤其是到了150、200、300这些临界档位，差一个档，IAQI能差出好几十分。
1.2 插值：手算最容易出错的地方
手动插值的公式其实是小学比例题：先算浓度在整个区间的位置比例，再把这个比例映射到IAQI区间里。标准写法是：
IAQI = (IAQI上限 - IAQI下限) / (浓度上限 - 浓度下限) × (实测浓度 - 浓度下限) + IAQI下限
举个例子，PM2.5实测浓度90，落在75-115这一档，对应IAQI区间是100-150。手算过程是：
(150 - 100) / (115 - 75) × (90 - 75) + 100 = 118.75
四舍五入取整，IAQI就是119。看着简单，可一旦批量处理几十行，麻烦就来了：档位选错、小数点位置看漏、四舍五入时机不对，都会导致和官方平台对不上。更麻烦的是O3还要分8小时和1小时两套限值分别计算再取大值，手动处理极其痛苦。
1.3 首要污染物：并列时最容易漏
按HJ 633-2012的规定，AQI大于50时，IAQI最大的污染物为首要污染物；如果IAQI最大的污染物是两项或两项以上，要并列报告。手动扫描六列数据时，人眼往往只盯住第一个最大值，并列项很容易漏掉。我就吃过这个亏：有一回某站点PM2.5和PM10的IAQI同时都是135，我只报了PM2.5，后来和区里复核平台对不上，查了半小时才发现是并列首要污染物漏报。这类问题在AQI刚好等于50、100这种临界日尤其常见，因为污染物之间浓度接近，IAQI也容易撞车。
2. HJ 633-2012的分段限值：先把这七张表刻进Excel
2.1 单位与统计形式：先统一口径再谈公式
建模板之前，必须先确认自己算的是日报还是小时报。我们这里做的是日报口径，也就是以24小时平均浓度为基础，O3比较特殊，用的是日最大8小时滑动平均，同时还要看1小时最大浓度。每个污染物的参与形式如下表：
| 污染物 | 参与AQI日报计算的浓度 | 单位 | 备注 |
|---|---|---|---|
| PM2.5 | 24小时平均 | μg/m³ | |
| PM10 | 24小时平均 | μg/m³ | |
| SO2 | 24小时平均 | μg/m³ | |
| NO2 | 24小时平均 | μg/m³ | |
| CO | 24小时平均 | mg/m³ | 注意不是μg/m³ |
| O3 | 日最大8小时滑动平均、日最大1小时平均 | μg/m³ | 两个都算，IAQI取大值参与AQI |
单位这块必须特别强调：CO在HJ 633-2012里用的是mg/m³，其他五项都是μg/m³。我第一次搭模板的时候顺手把CO的1.2直接当成了1200 μg/m³填进去，结果AQI瞬间爆表，排查了半天才发现是单位换算的锅。如果你手上拿到的CO原始数据是μg/m³，进公式之前要先除以1000。
2.2 六项污染物IAQI分段限值
下面是日报口径下需要用的分段限值，我直接按"浓度区间(对应IAQI区间)"的格式列出来，方便对照建表。这些数据全部来自HJ 633-2012的附录，不要凭记忆写，最好文档和代码对着看。
- PM2.5（24h，μg/m³）：0~35(0~50)、35~75(50~100)、75~115(100~150)、115~150(150~200)、150~250(200~300)、250~350(300~400)、350~500(400~500)
- PM10（24h，μg/m³）：0~50(0~50)、50~150(50~100)、150~250(100~150)、250~350(150~200)、350~420(200~300)、420~500(300~400)、500~600(400~500)
- SO2（24h，μg/m³）：0~50(0~50)、50~150(50~100)、150~475(100~150)、475~800(150~200)、800~1600(200~300)、1600~2100(300~400)、2100~2620(400~500)
- NO2（24h，μg/m³）：0~40(0~50)、40~80(50~100)、80~180(100~150)、180~280(150~200)、280~565(200~300)、565~750(300~400)、750~940(400~500)
- CO（24h，mg/m³）：0~2(0~50)、2~4(50~100)、4~14(100~150)、14~24(150~200)、24~36(200~300)、36~48(300~400)、48~60(400~500)
- O3-8h（μg/m³）：0~100(0~50)、100~160(50~100)、160~215(100~150)、215~265(150~200)、265~800(200~300)
- O3-1h（μg/m³）：0~160(0~50)、160~200(50~100)、200~300(100~150)、300~400(150~200)、400~800(200~300)、800~1000(300~400)、1000~1200(400~500)
这里有两个地方要特别说明。第一，O3-8h的分段只到265~800这一档，对应IAQI最高只有300，规范没有再往上的8小时档位。所以当O3-8h超过800时，模板要把IAQI上限封顶在300，最终AQI是否继续往上走，要看O3-1h的插值结果。这是很多人做模板时容易忽略的规则。第二，每一档区间的端点都是连续的，上一档浓度上限等于下一档浓度下限，所以在边界值上（比如PM2.5正好等于35），不管按哪一档插值，结果都一样，不存在歧义。
2.3 为什么不能跳过线性插值直接查表
有的朋友图省事，用VLOOKUP的近似匹配直接返回档位对应的IAQI上限，比如PM2.5=90直接返回115这一行对应的150，然后AQI就被高估了。标准做法必须是先定位浓度落点，再在区间内部做线性插值。同一个90的浓度，正确结果是119，直接查上限却是150，差了31，这已经不是四舍五入能弥补的误差了。所以模板的核心不是VLOOKUP，而是MATCH+INDEX嵌套，后面第4章详细拆。
3. 参数表与命名区域：公式能跑通的前提在这里
3.1 参数表结构：四列法，而不是两列法
我见过有人把分段表做成两列："浓度、IAQI"，然后靠VLOOKUP近似匹配，这个方案最大的问题就是没办法做区间内插值。正确做法是每个污染物单独建一张四列的小表，分别是：IAQI上限、浓度上限、IAQI下限、浓度下限。以PM2.5为例，参数表长这样：
| IAQI上限 | 浓度上限 | IAQI下限 | 浓度下限 |
|---|---|---|---|
| 50 | 35 | 0 | 0 |
| 100 | 75 | 50 | 35 |
| 150 | 115 | 100 | 75 |
| 200 | 150 | 150 | 115 |
| 300 | 250 | 200 | 150 |
| 400 | 350 | 300 | 250 |
| 500 | 500 | 400 | 350 |
注意几个细节：浓度下限从0开始，保证浓度等于0的时候MATCH能找到对应的第一档；最后一行的浓度上限是规范里的500，不是随便填个9999。很多人为了防止浓度超限时报错，会在最后加一行"500~9999"，这个做法本身没错，但会让线性插值的斜率出问题。正确做法是在公式里用MIN函数把浓度封顶在规范上限（PM2.5就是500），这样浓度600时，MIN(600,500)=500，MATCH仍然定位到最后一行，插值结果正好是500。如果表格里加了9999那一行又没有MIN封顶，插值会把600放在500~9999区间里，算出来只有400多，反而错了。
3.2 命名区域：让公式从"天书"变成可读
公式里如果直接写参数表!$B$2:$B$8这种区域，一长串括号套下来，自己第二天都看不懂。强烈建议用名称管理器把每个污染物的四列数据定义成名字。以PM2.5为例，打开"公式"选项卡，进入"名称管理器"，新建以下四个名称：
| 名称 | 引用位置 |
|---|---|
| PM25_C_HI | =参数表!$B$2:$B$8 |
| PM25_C_LO | =参数表!$D$2:$D$8 |
| PM25_I_HI | =参数表!$A$2:$A$8 |
| PM25_I_LO | =参数表!$C$2:$C$8 |
命名逻辑统一是"污染物_指标_类型"，C表示浓度Concentration，I表示IAQI，LO是下限，HI是上限。PM10、SO2、NO2、CO、O3-8H、O3-1H这六组按同样方式建，只是前缀换成PM10_、SO2_、NO2_、CO_、O3_8H_、O3_1H_。名称建好之后，公式里就不再出现裸区域引用，写起来清爽，复制到其他单元格也不会因为相对引用错位而串行。
3.3 输入区规范：缺测、负值、文本统统拦截
主计算表我建议这样布局：A列日期，B到H列依次是六项污染物浓度（B:PM2.5、C:PM10、D:SO2、E:NO2、F:CO、G:O3-8h、H:O3-1h），I到O列是对应IAQI，P列是O3参与值，Q列是AQI，R列是空气质量等级，S列是首要污染物。浓度输入区有两个必须做的事：一是在"数据验证"里限制只能输入大于等于0的数值，防止手滑填负数；二是缺测当天不要填0，直接留空或者填文本"NA"，因为0浓度会计算出IAQI=0，会把AQI整体拉低，和缺测不参与评价是两回事。
4. IAQI核心公式：MATCH+INDEX的线性插值为什么可靠
4.1 MATCH+INDEX定位区间，就像翻字典
MATCH的作用是在一列升序数组里查找某个值的位置，第三个参数用1表示返回小于等于查找值的最大值位置。INDEX的作用则是根据行号返回区域里对应行的内容。两个函数套在一起，逻辑就是：先用MATCH找到浓度落在参数表的第几行，再用INDEX去取那一行的浓度上限、浓度下限、IAQI上限、IAQI下限，最后做线性插值。
这个组合比VLOOKUP可靠在哪里？VLOOKUP近似匹配只能拿到"落点所在区间"的某个端点，拿不到区间的两个端点，自然做不了插值；MATCH+INDEX则可以自由地在区域里提取任何列、任何行的值，四列参数随便取。
4.2 以PM2.5为例的完整公式
假设PM2.5浓度在计算表的B2单元格，参数表已经定义了PM25_C_HI、PM25_C_LO、PM25_I_HI、PM25_I_LO这四个名称。I2单元格写入：
=IF(B2="","", ROUND( (INDEX(PM25_I_HI,MATCH(MIN(B2,500),PM25_C_LO,1)) -INDEX(PM25_I_LO,MATCH(MIN(B2,500),PM25_C_LO,1))) /(INDEX(PM25_C_HI,MATCH(MIN(B2,500),PM25_C_LO,1)) -INDEX(PM25_C_LO,MATCH(MIN(B2,500),PM25_C_LO,1))) *(MIN(B2,500)-INDEX(PM25_C_LO,MATCH(MIN(B2,500),PM25_C_LO,1))) +INDEX(PM25_I_LO,MATCH(MIN(B2,500),PM25_C_LO,1)) ,0))我来拆开讲。MATCH(MIN(B2,500),PM25_C_LO,1)这一长串，先取浓度和500的较小值，然后在浓度下限列里定位所在行。为什么要包一层MIN？就是为了前面说的封顶逻辑。如果没有MIN，当B2=600时，MATCH照样能定位到最后一行，但因为最后一行浓度上限就是500，插值公式里的浓度上限减去浓度下限会出现除数等于零的情况，直接报#DIV/0!；就算你给最后一行加了个9999的上限，插值斜率也会失真。所以MIN封顶是这套公式里绝对不能省的一步。
拿到行号后，四个INDEX分别提取IAQI上限、IAQI下限、浓度上限、浓度下限，然后套标准插值公式。最后再用ROUND(...,0)把结果取整。IAQI必须先取整再参与后面的MAX比较，这是规范要求，也直接影响AQI最终值和首要污染物的判定。
4.3 用LET函数让公式短一半
如果你用的是Excel 2021或Office 365，推荐用LET函数把MATCH结果定义为变量，这样公式可读性会好很多，也不容易抄错：
=IF(B2="","", LET(n,MATCH(MIN(B2,500),PM25_C_LO,1), ROUND( (INDEX(PM25_I_HI,n)-INDEX(PM25_I_LO,n)) /(INDEX(PM25_C_HI,n)-INDEX(PM25_C_LO,n)) *(MIN(B2,500)-INDEX(PM25_C_LO,n)) +INDEX(PM25_I_LO,n) ,0)))这里n就是区间行号，后面的公式只写一次INDEX，公式长度直接砍半。LET函数的逻辑很直白：先定义n，再在后面的表达式里反复引用。老版本Excel不支持LET，就老老实实用第4.2节的完整写法。
4.4 其他五列公式怎么改
PM10、SO2、NO2、CO、O3-8H、O3-1H的公式结构和PM2.5完全一样，只需要改两个地方：一是名称前缀，二是MIN函数里的封顶上限。封顶值分别对应：PM10用600，SO2用2620，NO2用940，CO用60，O3-8H用800，O3-1H用1200。我以CO为例写一个J2? 不，按前面布局，CO在M列：M2公式是
=IF(F2="","", LET(n,MATCH(MIN(F2,60),CO_C_LO,1), ROUND( (INDEX(CO_I_HI,n)-INDEX(CO_I_LO,n)) /(INDEX(CO_C_HI,n)-INDEX(CO_C_LO,n)) *(MIN(F2,60)-INDEX(CO_C_LO,n)) +INDEX(CO_I_LO,n) ,0)))这里CO的浓度统一按mg/m³处理，如果输入的是μg/m³，记得先除以1000。O3-8H和O3-1H分别放在N2和O2，公式中MIN封顶值一个是800，一个是1200。千万别把O3-8H的封顶也写成1200，否则超过800的部分会按8小时限值硬算，结果就不符合规范了。
5. AQI汇总与首要污染物识别：MAX、IF、TEXTJOIN的分工
5.1 O3参与值的特殊处理
六项污染物中，只有O3需要先算两个IAQI。按照规范，O3参与AQI计算的IAQI值取日最大8小时滑动平均和日最大1小时平均两者计算结果中的较大值。所以在P2单元格写道：
=IF(COUNT(N2,O2)=0,"",MAX(N2,O2))有的人不理解为什么日报还要看1小时平均，这是因为O3浓度在午后会出现明显峰值，8小时滑动平均会把这个峰值磨平，导致污染过程被低估。当8小时浓度超过265以上时，1小时浓度往往会先冲到更高档位，如果只看8小时，AQI可能被压在200~300区间，但实际污染水平已经更高了。
5.2 AQI：一行MAX搞定
Q2单元格写：
=IF(COUNT(I2:O2)=0,"",MAX(I2:M2,P2))I2:M2是PM2.5、PM10、SO2、NO2、CO这五项的IAQI，P2是O3参与值。这里COUNT函数的作用是，如果当天所有IAQI都是空文本，说明整行都没有有效浓度数据，返回空值而不是0。MAX会自动忽略文本，所以哪怕有个别IAQI是空，也不会报错。
5.3 空气质量等级自动判定
有了AQI之后，等级判断用LOOKUP常量数组是最省事的写法，R2单元格：
=IF(Q2="","",LOOKUP(Q2,{0;51;101;151;201;301},{"优";"良";"轻度污染";"中度污染";"重度污染";"严重污染"}))LOOKUP会在第一个数组中找小于等于Q2的最大值，然后返回第二个数组中对应位置的等级。AQI=50返回"优"，AQI=51返回"良"，AQI=100返回"良"，AQI=101返回"轻度污染"，边界完全和规范一致。不喜欢常量数组的朋友也可以用IFS嵌套，但公式会长不少，没必要。
5.4 首要污染物：TEXTJOIN把并列项一网打尽
S2单元格是这套模板里最见功夫的地方。先判断AQI是否大于50，再逐个比较每个IAQI是否等于AQI，最后用TEXTJOIN把所有命中的污染物名称用顿号连起来：
=IF(Q2="","", IFERROR( TEXTJOIN("、",TRUE, IF(AND(Q2>50,I2=Q2),"PM2.5",""), IF(AND(Q2>50,J2=Q2),"PM10",""), IF(AND(Q2>50,K2=Q2),"SO2",""), IF(AND(Q2>50,L2=Q2),"NO2",""), IF(AND(Q2>50,M2=Q2),"CO",""), IF(AND(Q2>50,N2=Q2),"O3-8h",""), IF(AND(Q2>50,O2=Q2),"O3-1h","") ),"-"))TEXTJOIN的第一个参数是分隔符"、"，第二个参数TRUE表示忽略空值。如果AQI小于等于50，所有IF都返回空字符串，TEXTJOIN结果是空，IFERROR显示为"-"，也就是规范里"不报首要污染物"的口径。如果PM2.5和PM10的IAQI同时等于AQI，结果就是"PM2.5、PM10"。
这里要特别说明O3的处理。我把O3-8H和O3-1H作为两个独立候选者放进去，是因为两者都有可能成为IAQI最大的污染物。实际审核时，有些省份要求O3-1H只在O3-8H超标时才启用，有的平台则直接按"O3-8H优先"报告。模板层面我选择把两个都列出来，输出后自己人工确认一下，总比漏报强。
5.5 旧版Excel的降级方案
TEXTJOIN需要Excel 2019或Office 365。如果你的电脑还是Excel 2016甚至更老，可以用下面的土办法：用IF判断后拼出带空格的文本，最后用SUBSTITUTE把空格替换成顿号。
=IF(Q2="","", SUBSTITUTE(TRIM( IF(AND(Q2>50,I2=Q2),"PM2.5 ","")& IF(AND(Q2>50,J2=Q2),"PM10 ","")& IF(AND(Q2>50,K2=Q2),"SO2 ","")& IF(AND(Q2>50,L2=Q2),"NO2 ","")& IF(AND(Q2>50,M2=Q2),"CO ","")& IF(AND(Q2>50,N2=Q2),"O3-8h ","")& IF(AND(Q2>50,O2=Q2),"O3-1h ","") )," ","、"))这个方案有个小毛病：如果污染物名称里本身有空格（我们这个场景里没有），替换会出错。用在AQI模板上完全够。
6. 模板实测：边界值、爆表值和#N/A、#DIV/0!排查
6.1 用两组数据实测验证
模板搭完不能直接上生产，先手工验几组数据。第一组取较常规的浓度：PM2.5=75、PM10=130、SO2=42、NO2=88、CO=1.5、O3-8h=110、O3-1h=95。
手动算一遍：PM2.5=75正好是35~75区间的端点，按哪边插值都是IAQI=100；PM10=130落在50~150区间，IAQI=50+(100-50)/(150-50)×(130-50)=90；SO2=42在前两档的0~50区间，IAQI=42；NO2=88落在80~180区间，IAQI=50+(100-50)/(180-80)×(88-80)=54；CO=1.5在0~2区间，IAQI=37.5取整38；O3-8h=110在100~160区间，IAQI=58；O3-1h=95在0~160区间，IAQI=30。O3参与值取MAX(58,30)=58。AQI=MAX(100,90,42,54,38,58)=100，首要污染物是PM2.5，等级"良"。
模板输出应该和这个手算结果完全一致。重点看PM2.5=75这种边界值，公式输出是100而不是偏差1或2，说明MATCH的区间定位和插值斜率是对的。
第二组验证O3主导的场景：PM2.5=20、PM10=40、SO2=10、NO2=20、CO=0.5、O3-8h=180、O3-1h=230。O3-8h=180落在160~215区间，IAQI=100+(150-100)/(215-160)×(180-160)≈118；O3-1h=230落在200~300区间，IAQI=100+(150-100)/(300-200)×(230-200)=115。O3参与值取MAX(118,115)=118，AQI=118，首要污染物是O3-8h。如果把O3-1h改成260，则O3-1h的IAQI=130，O3参与值变成130，首要污染物就切换到O3-1h。这组数据能验证O3参与值取MAX的逻辑是否正确。
6.2 三个最常见的错值排查
模板跑起来之后，最常遇到的错值是#N/A、#DIV/0!和#VALUE!。
出现#N/A，绝大多数是MATCH找不到位置。原因基本只有三种：浓度填成了负数；浓度单元格是文本格式导致比较失败；命名区域引用错了工作表。先用鼠标点一下公式里的MATCH部分，按F9看计算过程，能看到返回的是什么值。
出现#DIV/0!，几乎都是参数表里有两个相邻行的浓度上限和浓度下限相等。比如PM2.5参数表最后一行浓度上限如果不小心填了500，而它上一行的浓度上限也是500，MATCH定位到某些行时，分母"上限减下限"就变成0。检查方式是把参数表按"浓度下限"升序排一遍，肉眼扫一下有没有上下行端点重复。
出现#VALUE!，一般是浓度单元格里有空格或者中文。建议把输入区整列选中，用"数据验证"限制为数值，同时公式里加IF(B2="","",...)这种空值判断。
6.3 和官方平台对不上时，按这三个方向核
模板算出来的结果和空气质量发布平台不一致，先别急着怀疑公式。第一个要核对的是浓度口径：平台上的日报AQI用的是24小时平均浓度，不是某个小时的瞬时浓度；O3用的是日最大8小时滑动平均，也不是固定取8点到16点那么简单。第二个要核对的是IAQI取整时机：规范要求先对每个IAQI四舍五入取整，再对取整后的结果取MAX，如果先用未取整的插值结果取MAX再round，最后可能差1。第三个要核对的是缺测处理：某一天SO2有效数据不足20小时，这份数据就不该参与AQI计算，直接整列留空，而不是填0拉低结果。这三个方向排查完，绝大部分对不上的问题都能解决。
最后再分享一个扩展技巧
模板稳定运行之后，建议顺手加两个锦上添花的东西。一个是条件格式：选中AQI列，设置"大于200"填充淡红色、"大于300"填充深红色，这样每天打开表格扫一眼就知道今天有没有污染过程。另一个是做一张透视表，把日期、AQI、首要污染物拉进去，按月份统计优良天数和首要污染物频次，周报月报就能直接从模板出数。这套函数模板我自己用了两年多，日常填报基本没出过错；如果你站点多、数据量大，后续再考虑用VBA或Python批量处理也不迟。
