做环境数据分析的朋友应该都有这个感受每天拿到六项污染物浓度后最烦的就是从限值表里逐段匹配、手算AQI、再判断首要污染物。一个两个数据还好一旦碰到整月数据或者多个站点手动算不仅慢还特别容易在插值那一步出错。这事用Excel函数完全可以自动化——只要把参数表搭好后面每天只需要粘贴浓度数据AQI、空气质量等级、首要污染物就能自动出来。这篇文章我就把我日常在用的这套公式模板完整的拆给你看从计算逻辑、函数选型到每一步怎么搭全部摊开讲。这套模板适合环境监测、环评报告、学校课题里做数据分析的人也适合只是想快速算出某天AQI的普通用户。你不需要懂VBA不需要写代码只要会用Excel的基础操作照着抄就能用。1. 先把AQI的计算逻辑弄明白模板才不会搭错很多人在Excel里搭不好AQI计算模板问题往往不是函数不会用而是基础逻辑没理顺。在写公式之前我们需要把AQI的计算规则从头到尾捋一遍否则公式写出来都是对着错误流程做自动化越自动越离谱。1.1 六项污染物和两套浓度单位AQI空气质量指数不是直接测出来的而是由六项污染物的浓度换算而来的这六项分别是PM2.5细颗粒物24小时平均PM10可吸入颗粒物24小时平均SO2二氧化硫24小时平均NO2二氧化氮24小时平均CO一氧化碳24小时平均O3臭氧这里要区分8小时滑动平均和1小时平均这里面有个非常容易踩坑的点单位不统一。PM2.5、PM10、SO2、NO2、O3的浓度单位在国标里都用微克每立方米μg/m³但CO用的是毫克每立方米mg/m³。1 mg/m³等于1000 μg/m³。这直接决定了你参数表里的限值怎么填也决定了你在Excel里录入监测数据时要不要做换算。我见过不止一个同事把CO的原始数据单位是mg/m³录成了2000多然后发现CO的IAQI直接爆表怎么查都查不出原因——其实就是把2000 mg/m³当成2000 μg/m³去匹配了。另外O3比较特殊它又分O3-8h8小时滑动平均和O3-1h1小时平均。国标规定计算AQI时O3这一项要用这两个值分别计算IAQI然后取较大值而不是简单拿其中一个去算。这一点我也会在后面单独讲。1.2 分段线性插值AQI计算的核心AQI计算的底层逻辑可以理解成一张“浓度-指数对照表”。但浓度和IAQI之间不是简单的整数对应关系而是一段一段的线性关系。举个例子PM2.5的24小时平均浓度在0到35 μg/m³之间时对应的IAQI是0到50浓度在35到75之间时对应的IAQI是50到100。如果PM2.5浓度是50那IAQI不能简单查表查出来而是要用插值公式算IAQI 低档IAQI (高档IAQI - 低档IAQI) / (高档浓度 - 低档浓度) × (实测浓度 - 低档浓度)这个公式的本质就是中学数学里的两点式直线方程。你不需要真的去理解斜率截距只需要知道只要确定了两个区间端点就能把中间任何浓度对应的指数算出来。手动算的时候这个插值就是最容易出错的一步。因为要先在限值表里找到当前浓度落在哪个区间然后抄四个数再做一次乘除法稍不留神就会按错计算器。Excel里的MATCH函数可以自动找到浓度落在哪个区间INDEX函数可以把区间端点的值自动取出来这两个函数搭配起来就是替你做“查表取数”的动作。1.3 AQI和首要污染物的判定逻辑单独一个污染物的指数叫IAQIIndividual Air Quality Index六项污染物算出来六个IAQI最终的AQI取的是其中的最大值。这个逻辑很重要因为它意味着AQI不是六项污染物的平均值也不是加权值而是“木桶效应”——哪项污染最重当天的空气质量指数就由它决定。PM2.5再好只要O3爆表AQI照样高。首要污染物的判定逻辑稍微复杂一点当AQI大于50时AQI最大值对应的污染物就是首要污染物当AQI小于等于50时通常视为空气质量优不设首要污染物显示“无”如果好几项污染物同时达到同一个最大IAQI那首要污染物是并列的应该显示多项。这些逻辑如果不提前想清楚公式写到一半很容易卡壳。尤其“AQI小于等于50时显示无”这个细节很多人会忽略。2. 选函数和搭表我把整套模板的思路先给你2.1 用到的函数每个都是干嘛的搭建这套模板核心函数其实只有几个但每一个都不可替代MATCH函数负责定位。比如PM2.5浓度是50MATCH(50, {0;35;75;115;150;250;350;500}, 1)返回2意思是50落在第2档也就是35到75这个区间。第三个参数写1表示“查找小于等于查找值的最大值”这是做区间分段匹配的关键。INDEX函数负责取数。定位到第2档之后需要用INDEX从参数表里把第2档的浓度下限、第3档的浓度上限、对应的IAQI值取出来。INDEX是坐标取数的函数配合MATCH的定位结果就能实现动态查表。TREND或FORECAST函数负责插值。严格来说你可以手动写出插值公式但用TREND函数会更简洁。TREND函数本质是做线性回归预测但传入两个点时就是精确的线性插值正好符合分段插值需求。老版本Excel里没有TREND也没关系用FORECAST或者直接写插值公式都行。MAX函数负责取最大值。六项IAQI算出来后AQI就是它们的最大值。IF函数负责做判断。首要污染物只有在AQI大于50时才显示以及数据缺测时是否返回空值都需要用IF控制。TEXTJOIN函数负责处理并列情况。新版Excel里如果几项污染物同时是首要污染物用TEXTJOIN可以把它们连成“PM2.5、O3”这样的文本。老版本用户没这个函数也能用我后面会讲替代方案。2.2 参数表单独放一个Sheet的原因搭这个模板我强烈建议单独建一个Sheet放参数表千万不要把限值数据直接写进公式里。第一个原因是可维护性。国标的浓度限值不是一成不变的如果哪天当地发布了更严格的评价标准你只需要改参数表里的数字所有公式会自动跟着变。如果限值写在公式里改一处就得改好几处还容易漏。第二个原因是可读性。公式里套常数别人拿到你的表根本看不懂这些数字哪来的。参数表单独放一页公式里引用的是“参数表!$B$2:$B$9”这种区域一看就知道逻辑。第三个原因是方便检查。AQI算出来不对的时候90%的情况是参数表录入错了。单独放一页排查起来一目了然。2.3 数据表字段和辅助列的规划数据表我习惯叫“计算表”的字段规划直接决定了后面公式是否好写。我的建议是这样排A列放日期B到H列放六项污染物的原始浓度其中O3要占两列O3-8h和O3-1h。从I列开始放“档位辅助列”从P列开始放六项IAQI最后放AQI和首要污染物。辅助列是什么就是把MATCH函数那个定位结果单独算出来存在一列里这样IAQI公式里就不用反复嵌套MATCH既方便检查也能大幅缩短公式长度。很多人为了省事直接在一个单元格里写完一长串嵌套公式结果LENGTH长到自己也看不懂一旦报错不知道怎么改。我建议宁可多建几列辅助列让每一步都看得见、能检查这才是模板稳定好维护的关键。3. 完整搭建流程跟着步骤抄作业3.1 第一步录入浓度限值参数表新建一个Sheet命名为“参数表”。在A1:H9区域录入以下数据。IAQI节点PM2.5 (24h)PM10 (24h)SO2 (24h)NO2 (24h)CO (24h) mg/m³O3-8hO3-1h0000000050355050402100160100751501508041602001501152504751801421530020015035080028024265400300250420160056536800800400350500210075048800800500500600262094060800800提示以上浓度节点依据HJ 633-2012整理。O3-8h和O3-1h在国标里最高浓度节点只到800对应IAQI为300实际数据里O3超过800的情况极少模板里后面几档重复填800是为了保证公式结构统一不影响正常计算。如果你们当地有更严格的限值标准直接改参数表数字即可。这个表就是整套模板的数据地基。注意A列是IAQI节点B到H列是各污染物的浓度节点两列对应同一行的值就是插值公式里的端点坐标。3.2 第二步搭好当日数据录入区回到Sheet重命名或新建一个“计算表”。A1:H1填写表头A1日期 B1PM2.5 (μg/m³) C1PM10 (μg/m³) D1SO2 (μg/m³) E1NO2 (μg/m³) F1CO (mg/m³) G1O3-8h (μg/m³) H1O3-1h (μg/m³)从第2行开始每一行是一天的数据。比如A2填2025-04-01B2到H2填当天的六项浓度值。这里有一个细节F列CO的单位录入的时候一定要跟参数表保持一致都是mg/m³。如果你手头的CO数据是μg/m³在录入时先除以1000。这不是Excel公式能自动识别的必须人工保证。3.3 第三步六项IAQI计算公式一次配齐先建六列档位辅助列。I列对应PM2.5J列对应PM10K列对应SO2L列对应NO2M列对应CON列和O列对应O3-8h和O3-1h。在I2输入以下公式IF(B2, , MIN(MATCH(B2, 参数表!$B$2:$B$9, 1), COUNTA(参数表!$B$2:$B$9)-1))这个公式的含义是用MATCH找到PM2.5浓度落在第几档然后用MIN限制档位不超过参数表倒数第二档防止浓度超过上限时MATCH返回最后一个档位、导致后续INDEX取数越界。把I2的公式横向拖到O列时要记得改引用列J列的MATCH区域是参数表!$C$2:$C$9K列是参数表!$D$2:$D$9L列是参数表!$E$2:$E$9M列是参数表!$F$2:$F$9N列是参数表!$G$2:$G$9O列是参数表!$H$2:$H$9然后从P列开始写六项IAQI。先看P2也就是PM2.5对应的IAQIIF(B2, , ROUND(TREND( INDEX(参数表!$A$2:$A$9, I2):INDEX(参数表!$A$2:$A$9, I21), INDEX(参数表!$B$2:$B$9, I2):INDEX(参数表!$B$2:$B$9, I21), B2 ), 0))这个公式我拆开解释一下TREND函数的第一个参数是Y值区域也就是两个端点的IAQI值第二个参数是X值区域也就是两个端点的浓度值第三个参数是当前的实测浓度。TREND在这两个端点之间做线性插值返回的就是当前浓度对应的IAQI。ROUND把它四舍五入成整数因为国标规定IAQI取整数。外层套了一个IF作用是当B2为空缺测时P2也返回空而不是算出一个假数值。Q2到W2的公式逻辑一模一样只需要替换对应的浓度单元格和参数表列区域Q2PM10浓度是C2参数表用C列R2SO2浓度是D2参数表用D列S2NO2浓度是E2参数表用E列T2CO浓度是F2参数表用F列V2O3-8h浓度是G2参数表用G列W2O3-1h浓度是H2参数表用H列这里有一个容易犯的错PM2.5的档位辅助列I2和P2是同一行的但如果某一列数据是从第2行开始而参数表是从第1行开始绝对引用的区域一定要带$符号否则往下拖的时候区域会整体偏移这是最常见的#VALUE!错误来源。U2是O3的最终IAQI用公式IF(AND(G2, H2), , MAX(V2, W2))这里用V2和W2分别代表O3-8h和O3-1h的IAQI。如果两个浓度都有取大值如果其中一个为空MAX函数会自动忽略空值用另一个算如果两个都为空就返回空。3.4 第四步AQI值和首要污染物自动出结果X2是AQI公式很简单IF(COUNT(P2:U2)6, 数据不足, MAX(P2:U2))这里用COUNT统计P2到U2六个IAQI有几个是数值。如果少于6个说明当天六项污染物有缺测按国标要求AQI缺报显示“数据不足”。Y2是首要污染物先给一个兼容性最好的版本IF(X2, , IF(X250, 无, INDEX({PM2.5,PM10,SO2,NO2,CO,O3}, MATCH(X2, P2:U2, 0))))这个公式里MATCH(X2, P2:U2, 0)会在P2到U2区域里找到AQI所在的位置然后用INDEX从污染物名称数组里取对应名称。不过这个公式有一个局限如果出现并列首要污染物它只会返回第一个。要处理并列可以用TEXTJOIN。Office 365和Excel 2019及以上版本支持IF(X2, , IF(X250, 无, TEXTJOIN(、, TRUE, IF(P2:U2X2, {PM2.5,PM10,SO2,NO2,CO,O3}, ))))注意这是一个数组公式。在Excel 365里直接回车即可在老版本Excel里需要按CtrlShiftEnter输入公式两侧会出现花括号{}。3.5 第五步用条件格式让结果更直观数据算出来只是第一步看得舒服是第二步。我给AQI列加一个条件格式让结果一眼就能看出污染等级。选中X列数据区域在“开始”选项卡里选择“条件格式”-“新建规则”-“使用公式确定要设置格式的单元格”规则写X250设置一个绿色填充然后用同样的方式依次添加AND(X250, X2100)黄色填充良AND(X2100, X2150)橙色填充轻度污染AND(X2150, X2200)红色填充中度污染AND(X2200, X2300)紫色填充重度污染X2300深红色填充严重污染这样每天打开表一个色块就能看出来当天到底什么水平比盯着数字舒服多了。4. 常见问题和避坑经验这套模板我实际用了很长时间踩过不少坑。下面这些问题基本都是新手最容易遇到的。4.1 CO单位混用导致指数翻倍这是最常见的问题没有之一。之前有个同事把CO数据直接从监测平台导出来数值是1800多直接粘到F列结果CO的IAQI算出来是500整月数据重算才发现是单位没换算。CO在监测平台上经常以mg/m³展示但部分平台导出时用的是μg/m³。录入前必须看清楚如果是μg/m³先除以1000再填进去。4.2 O3的1小时和8小时到底该取哪个国标的规定是分别用O3-1h和O3-8h计算IAQI取较大值作为O3这一项的最终IAQI。实际运用中O3-8h在白天的多数时候会高于O3-1h但在臭氧污染快速上升的时段O3-1h的IAQI会反超。只取其中任何一个都可能算错AQI。4.3 浓度超过分档上限怎么办比如PM2.5浓度500多已经超过参数表最后一个节点。这时候MATCH会返回最后一档的位置而插值公式里I21会引用区域外的一格直接报错。我在档位公式里用了MIN(..., COUNTA(...)-1)来防止这个情况但结果只能是500。如果真的到了“爆表”程度建议在备注里单独说明不要纠结于精确值——这种极端浓度下的AQI意义已经不大。4.4 数据缺测时如何处理按国标当天六项污染物中缺了任何一项AQI都应缺报。但很多人会在这一项填个0或者瞎填个值导致算出来的AQI比真实值低很多。我在公式里都加了IF(B2, , ...)判断只要浓度为空IAQI就为空而AQI公式用COUNT统计不足6项就返回“数据不足”。这样既遵循标准又能在表里明确看出是哪天数据有问题。4.5 首要污染物并列时怎么显示普通版本的INDEXMATCH公式只能显示第一个。如果你们当地经常出现PM2.5和O3同时是首要污染物的情况建议直接用TEXTJOIN版本。实测效果是当两项IAQI同为120时Y2单元格会显示“PM2.5、O3”而不是只显示一个。4.6 公式不显示结果先查这几处以下是我排查公式问题的几个顺序现象可能原因解决办法公式返回#N/AMATCH查找区域引用错误或浓度为空检查参数表区域是否锁定、浓度单元格是否有空值公式返回#VALUE!单元格格式是文本或者公式区域串行把公式单元格格式改成常规重新输入公式公式显示但不出结果Excel计算模式被设为手动按F9重新计算或在选项里改为自动计算明明改了参数表但结果不变公式里写死了参数没有引用参数表检查公式是否包含数字常量改成引用参数表单元格复制模板后出现#REF!Sheet名称被修改引用丢失确认参数表Sheet名称是“参数表”或在公式里改回正确名称还有一个大家经常忽略的问题如果工作表被保护辅助列被锁定粘贴新数据时可能直接报错。我的做法是辅助列和公式区域全部锁定只有B到H这几个浓度录入单元格设为不锁定然后给工作表加保护。这样既能防止误删公式又不影响每天录入数据。5. 模板还能怎么扩展这套模板搭完之后其实就是一套“单日AQI计算器”。但实际工作中我们往往不只是看当天还要做月度评价、超标统计这些东西也可以顺手用Excel函数扩出来。5.1 月度统计和等级占比AQI等级判断可以用IFS函数Excel 2016及以上支持IFS(X250, 优, X2100, 良, X2150, 轻度污染, X2200, 中度污染, X2300, 重度污染, TRUE, 严重污染)老版本Excel没有IFS就用VLOOKUP做区间匹配在参数表里加一个等级对照表用VLOOKUP(X2, 等级表, 2, 1)也能实现同样的效果。然后你用COUNTIFS统计某个月“良”的天数COUNTIFS(A:A, 2025-04-01, A:A, 2025-04-30, Z:Z, 良)Z列是等级判断列。5.2 超标提醒与日报生成如果想把超标的日期标出来可以用COUNTIF统计首要污染物里“PM2.5”出现的次数COUNTIF(Y:Y, *PM2.5*)注意用通配符*因为并列情况下单元格里可能是“PM2.5、O3”。也可以做一个简单的日报表用INDEXMATCH自动从数据表里抓取指定日期的AQI和首要污染物生成一句“4月1日AQI为75首要污染物为O3”这种文字。这些扩展其实都不需要额外装插件只要理解了这套模板的结构往旁边加列就行。这套模板我个人用的时间最长的一次是处理连续半年的监测数据三千多行跑下来计算速度没有任何问题。模板的优点是自动化之后基本不会再出计算错误但前提是前面的逻辑要理清尤其是O3的取数逻辑和CO的单位换算这两处一旦错了后面所有结果都跟着错。最后再分享一个小技巧不要把公式只拖一层就完事。当你把第一行所有公式写好、验证无误之后直接选中第2行整行下拉填充前先做一个“数值化验证”——手动把某一天的数据和官网公布的AQI对比一下如果对得上再放心往下拉。这套模板我一开始没有做这个验证结果批量处理完才发现O3那边取值方式写错了整个月的数据返工重算教训很深刻。
