你有没有算过 AQI不是去网站查现成结果而是拿着六项污染物的浓度自己拿计算器一点点做分段插值。我上周帮一个做环境监测的朋友整理月度报表时发现她还在用最原始的方式处理打开手机计算器对着限值表一项项按几十行数据能算一整个下午而且稍不留神就会按错一个数字整张表的结论全废。后来我帮她搭了一套 Excel 公式模板只需要把当天的 PM2.5、PM10、SO2、NO2、CO、O3 浓度贴进去AQI、空气质量等级、首要污染物全部自动算好。整个过程从“一下午”压缩到“三分钟”。这篇文章就把这套模板的完整思路、核心公式、建表步骤、常见坑全部拆开讲清楚如果你也在做环保数据报表、环评分析或者单纯想在 excel 里练一练查找引用和插值计算可以直接照着复制公式。1. 先搞懂 AQI 的计算逻辑别上来就写公式很多人卡在 Excel 公式上其实真正卡住他们的是业务逻辑没弄清楚。AQI 不是“六项污染物浓度的平均值”它的计算规则很明确照着标准一步步来就行。1.1 六项污染物对应六张“限值换算表”AQI 的全称是空气质量指数英文 Air Quality Index。它由六项污染物浓度共同决定PM2.5、PM10、SO2、NO2、CO、O3。每一项污染物都有一个“空气质量分指数”简称 IAQIIndividual Air Quality Index。AQI 就是这六个 IAQI 里最大的那个。这六项污染物各自对应一张浓度限值表。这张表不是随便拍的依据是 HJ 633-2012《环境空气质量指数AQI技术规定》。以 PM2.5 的 24 小时平均浓度为例浓度 35μg/m³ 对应 IAQI 50浓度 75μg/m³ 对应 IAQI 100浓度 115μg/m³ 对应 IAQI 150以此类推。其他污染物也都有一组对应的“浓度限值-IAQI”对照关系。如果你已经在做环境数据相关的工作这张表应该不陌生。如果没有后面我会把完整的限值表放出来你直接抄到 Excel 里就行不用自己到处翻标准。1.2 真正卡住人的是“分段线性插值”为什么不能直接用 VLOOKUP 查表因为污染物浓度很少刚好落在限值表的那几个点上。比如 PM2.5 浓度是 20μg/m³查表发现它落在 0~35μg/m³ 这个区间里这个区间对应 IAQI 是 0~50。20 不在表里怎么换成 IAQI标准规定用“分段线性插值”来计算IAQI 低位IAQI 高位IAQI - 低位IAQI×浓度 - 低位浓度/高位浓度 - 低位浓度打个比方这就像给一条平滑斜坡搭桥已知左端点的坐标是低位浓度低位IAQI右端点是高位浓度高位IAQI现在给你一个中间的浓度值你要在这条直线上找到它对应的 IAQI。比例的算法就是小学学过的“按比例分配”浓度在区间里走了多少比例IAQI 也按同样比例走。1.3 首要污染物不是“浓度最大的那项”很多人会把首要污染物理解成“六项里浓度最高的污染物”这是错的。判断依据是 IAQI不是浓度。正确规则是算出六项污染物的 IAQI 后取最大值得到 AQI当 AQI 大于 50 时IAQI 最大的污染物就是首要污染物。如果两项或多项污染物的 IAQI 并列最大它们并列成为首要污染物。如果 AQI 小于或等于 50空气质量好不报首要污染物。这个逻辑在 Excel 里非常好实现先用 MAX 找到最大的 IAQI再用 MATCH 定位这个最大值在第几个位置最后用 INDEX 从污染物名称数组里取名字。步骤虽然简单但很多人第一次写公式时还是会把“浓度最大值”和“IAQI 最大值”搞混结果算出来的首要污染物全是错的。2. 模板设计的核心思路与函数选型理解了业务逻辑下面进入正题怎么用 Excel 函数把这一整套算出来。2.1 为什么选 Excel而不是编程脚本有人可能会说这种计算写个 Python 脚本不更快吗确实Python 处理几百行数据也就几秒钟。但实际业务场景里数据往往来自不同格式的监测平台甚至有些同事还在发手抄表格。Excel 最大的优势是拿到数据就能改、能看、能转手而且每一步公式都能追溯领导问你“这个 AQI 是怎么出来的”你点开单元格就能现场展示。还有一个现实原因环境监测行业里很多操作老师并不写代码。Excel 是他们最熟悉的工具。函数公式虽然也需要学但比让他们装 Python 环境、pip 安装库要友好一百倍。这套模板做出来基本任何人拿到都能直接用。2.2 用 MATCH INDEX 组合而不是 VLOOKUP 近似匹配先用 VLOOKUP 行不行表面上看VLOOKUP 的近似匹配可以找到“小于等于当前值的最大限值”正好对应插值需要的低位浓度。但接下来还有个问题你还需要“高位限值”才能做插值。VLOOKUP 只能返回同一行的数据要拿“下一行”的值通常得再用 OFFSET表格一变动就容易错。MATCH INDEX 是更稳的组合。MATCH 负责找到浓度落在哪一行返回一个位置数字 P表示“当前浓度大于等于这一行的限值但小于下一行的限值”。INDEX 再根据这个位置 P去限值表里精确取出低位限值、高位限值、低位 IAQI、高位 IAQI。整个逻辑一目了然调试也方便。我把两个方案做了个对比方式查找低位查找高位插值计算整体可读性VLOOKUP 近似匹配直接支持需要 OFFSET 处理公式很长一般MATCH INDEX直接支持直接用 P1 取下一行公式结构清晰好辅助列拆分分步实现分步实现每步都能看最好理解2.3 用 LET 函数给公式“瘦身”光说“公式清晰”还不够实际写出来你会发现一个 IAQI 计算要反复引用 MATCH、INDEX 很多次直接拼一条公式能有几百个字符别人根本没法维护。新版本的 Excel 和 WPS 都支持 LET 函数。LET 的语法很简单LET(变量名1, 值1, 变量名2, 值2, ..., 最终计算表达式)。它的作用就是把中间结果先定义成变量后面直接用变量名引用。比如 MATCH(B2, 限值表!$C$2:$C$9, 1) 这个查找动作理论上只需要做一次。用 LET 定义成 p后面所有 INDEX 都引用 p公式长度至少缩短一半逻辑也更清楚。如果你的 Excel 版本太老不支持 LET我再给你一个辅助列拆分方案效果一样。3. 完整公式模板搭建一步一步抄作业下面进入实操环节。我会从建表开始把整套模板逐步搭出来。建议你照着文章一步步在 Excel 里操作不要只看不练。3.1 第一步建立限值表新建一个工作表命名为“IAQI表”。从 A1 开始按下面的结构录入限值数据序号IAQIPM2.5_24hPM10_24hSO2_24hNO2_24hCO_24h(mg/m³)O3_8hO3_1h10000000025035505040210016031007515015080416020041501152504751801421530052001503508002802426540063002504201600565368008007400350500210075048800100085005006002620940608001200几个细节必须提醒CO 这一列的单位是 mg/m³不是 μg/m³。其他五项都是 μg/m³。如果你手头的数据全部是 μg/m³CO 浓度记得先除以 1000否则结果会完全失真。O3_8h 在 IAQI 400 和 500 档位原本没有对应限值我这里填了 800是为了让公式在常规计算时不会报错。如果 O3 8 小时浓度真的超过 800标准要求改用 O3 1 小时浓度计算 IAQI所以我在限值表里额外保留了一列 O3_1h两列取较大值即可。录入完成后选中 A1:I9按 CtrlT 把它变成表格或者直接保持普通区域也行。重点是记住区域引用范围是 A1:I9后面公式要用。3.2 第二步设计监测数据表再新建一个工作表命名为“日报表”。A1 到 H1 分别输入日期、PM2.5、PM10、SO2、NO2、CO、O3_8h、O3_1h。注意格式规范日期列用日期格式浓度列用数字格式。CO 列可以加一个单位提示建议在标题写成“CO(mg/m³)”避免以后忘记换算。从第 2 行开始每天一行把你已有的监测浓度数据贴进去。如果还没有数据可以先用一组测试数据验证公式比如日期PM2.5PM10SO2NO2COO3_8hO3_1h2025-01-01609518421.21101303.3 第三步用辅助列拆解 IAQI 计算这一步先做“看得懂”的版本。在日报表右侧比如从 K1 开始放 PM2.5 的辅助计算列。K2 输入位置查找公式MATCH(B2,IAQI表!$C$2:$C$9,1)这个公式的意思是在 PM2.5 限值列 C2:C9 中查找 B2 这个浓度值处于哪个位置升序匹配返回一个行号。比如浓度 60MATCH 返回 2因为 60 大于等于 35 且小于 75落在第二段区间。L2 输入低位 IAQIINDEX(IAQI表!$B$2:$B$9,$K2)M2 输入高位 IAQIINDEX(IAQI表!$B$2:$B$9,$K21)N2 输入低位浓度INDEX(IAQI表!$C$2:$C$9,$K2)O2 输入高位浓度INDEX(IAQI表!$C$2:$C$9,$K21)最后 P2 输入插值公式ROUND($L2($M2-$L2)*(B2-$N2)/($O2-$N2),0)ROUND 保留 0 位小数是因为标准规定 IAQI 结果保留整数。这样拆开写每一步都能看到数据很适合初学阶段理解插值逻辑。公式正确后选中 K2:P2向下填充到所有数据行。3.4 第四步一条 LET 公式直接算 IAQI辅助列版本虽然好理解但列数太多实际工作时不太美观。如果你的 Excel 版本支持 LET可以直接在 I 列写完整的 IAQI 计算。PM2.5 的 IAQI 公式如下放在 I2IF(ISNUMBER(B2),IF(B20,0,IF(B2MAX(IAQI表!$C$2:$C$9),超上限,ROUND(LET(p,MATCH(B2,IAQI表!$C$2:$C$9,1),lo,INDEX(IAQI表!$B$2:$B$9,p),hi,INDEX(IAQI表!$B$2:$B$9,p1),blo,INDEX(IAQI表!$C$2:$C$9,p),bhi,INDEX(IAQI表!$C$2:$C$9,p1),lo(hi-lo)*(B2-blo)/(bhi-blo)),0))),)这个公式看着长拆解后就是标准的插值流程最外层 ISNUMBER 判断单元格不是数字就返回空。第二层判断浓度小于等于 0IAQI 直接是 0。第三层判断浓度超过限值表最大值返回“超上限”避免 INDEX 越界报错。LET 内部p 是 MATCH 位置lo/hi 是低位和高位 IAQIblo/bhi 是低位和高位浓度最后按插值公式计算并 ROUND。拿到 PM2.5 的公式后其他污染物只需要改两个地方查询单元格 B2 改成对应列限值列 $C$2:$C$9 改成对应污染物的限值列。对应关系如下污染物浓度单元格限值表列PM2.5B2IAQI表!$C$2:$C$9PM10C2IAQI表!$D$2:$D$9SO2D2IAQI表!$E$2:$E$9NO2E2IAQI表!$F$2:$F$9COF2IAQI表!$G$2:$G$9O3_8hG2IAQI表!$H$2:$H$9注意 CO 这一列的浓度单元格 F2 直接引用就行前提是你录入数据时已经换算成 mg/m³。O3 的 IAQI 特殊一点标准里要求 8 小时和 1 小时浓度共同参与评价。正常空气质量数据下用 O3_8h 单独计算即可一旦 O3_8h 浓度很高就要和 O3_1h 的 IAQI 取较大值。我建议直接在 N 列放一个 MAX 公式引用两列的 IAQI 结果。如果你的数据没有 O3_1h就把这个公式简化为只引用 G2 对应的 IAQI 列。3.5 第五步算 AQI、等级、首要污染物六项污染物的 IAQI 算出来后剩下的就简单了。AQI 在 O2 输入MAX(I2:N2)等级在 P2 输入IF(O2,,IF(O250,优,IF(O2100,良,IF(O2150,轻度污染,IF(O2200,中度污染,IF(O2300,重度污染,严重污染))))))为什么不用 LOOKUP因为很多人对 LOOKUP 的边界条件不熟悉一旦临界值理解错AQI 恰好等于 100 时可能误判成“轻度污染”。嵌套 IF 虽然啰嗦但边界清晰50 以内就是优51 到 100 就是良永远不会出边界问题。首要污染物在 Q2 输入IF(O2,,IF(O250,无,INDEX({PM2.5;PM10;SO2;NO2;CO;O3},MATCH(MAX(I2:N2),I2:N2,0))))这里的逻辑是先判断 AQI 是否小于等于 50是则返回“无”否则用 MATCH 定位 IAQI 最大值的位置再用 INDEX 从常量数组中取出对应名称。如果你希望显示中文名可以把数组改成 {PM2.5;PM10;二氧化硫;二氧化氮;一氧化碳;臭氧}。需要加粗提醒这个基础版公式遇到“并列首要污染物”时只返回数组中第一个。后面我会讲进阶写法。4. 一组真实数据完整跑一遍公式搭好之后必须用真实数据验证一遍。我拿一组实际监测数据演示整个流程。4.1 数据准备假设某城市某天 24 小时平均浓度如下项目数值单位PM2.572μg/m³PM10118μg/m³SO222μg/m³NO258μg/m³CO1.5mg/m³O3_8h168μg/m³填入日报表后开始计算。4.2 计算过程手把手对账先看 PM2.5。浓度 72落在限值表的哪个区间VLOOKUP 或 MATCH 会找到 35 和 75 之间。低位限值 35 对应 IAQI 50高位限值 75 对应 IAQI 100。按插值公式计算IAQI 50 100 - 50×72 - 35/75 - 35 50 50 × 37 / 40 50 46.25 96.25四舍五入为 96。再看 PM10。浓度 118落在 50 和 150 之间也就是 IAQI 50 到 100 的区间。计算IAQI 50 100 - 50×118 - 50/150 - 50 50 50 × 68 / 100 50 34 84。以此类推最终六项 IAQI 算出来分别是污染物浓度IAQIPM2.57296PM1011884SO22222NO25872.5取整 73CO1.537.5取整 38O3_8h168103.8取整 104AQI 取最大值是 O3 对应的 104。等级是“轻度污染”。由于 AQI 大于 50首要污染物是 IAQI 最大的 O3。这里有个值得注意的实际场景当天 PM2.5 浓度达到 72看起来“灰蒙蒙”但按标准计算臭氧才是首要污染物。这很常见夏季午后臭氧污染经常“闷声发大财”不做专业分析根本意识不到。4.3 边界情况测试模板建好后强烈建议你用三组边界数据测试。第一组所有浓度都填 0。此时所有 IAQI 都是 0AQI 是 0等级“优”首要污染物“无”。如果公式返回的不是这个结果说明初始判断逻辑有误。第二组某项浓度恰好等于限值例如 PM2.5 填 35。此时 IAQI 应该正好等于 50。如果公式返回 49 或 51说明 MATCH 的升序匹配边界没处理好需要检查限值表是否按升序排列以及低位浓度是否被正确引用。第三组构造一个并列首要污染物的场景。比如 PM2.5 浓度和 PM10 浓度都让 IAQI 等于 100这时候基础版公式只会显示第一个也就是 PM2.5。如果想同时显示多项用下面的进阶公式IF(O2,,IF(O250,无,TEXTJOIN(、,TRUE,IF(I2:N2MAX(I2:N2),{PM2.5;PM10;SO2;NO2;CO;O3},))))这是一个数组公式。老版本 Excel 需要同时按下 Ctrl Shift Enter 确认新版本直接回车即可。TEXTJOIN 会把所有符合条件的污染物名称用顿号连接起来。5. 常见问题与排查技巧实录公式模板搭好不等于万事大吉。我帮朋友调试的过程中遇到过不少问题挑几个典型的列出来给你做个速查。5.1 公式报错速查表错误提示产生原因解决思路#N/A浓度文本格式导致 MATCH 不识别用 VALUE 转换或分列清洗数据#N/A浓度超过限值表中最大档位增加 IF 判断返回“超上限”#REF!MATCH 返回最后一行再取 P1 越界检查超上限判断是否写在公式里#VALUE!单元格里有不可见字符或文本数字用 TRIM 清除空格用分列转数值结果全是文本“超上限”CO 单位没换算μg/m³ 当成 mg/m³CO 浓度除以 1000AQI 正常但等级显示错误O2 引用范围选了其他列检查 P2 等式的 O2 单元格引用最容易被忽视的是 CO 单位。有个朋友第一次用模板CO 填了 1500公式立刻返回“超上限”他一脸懵。后来发现原始数据是 1500μg/m³换算成 mg/m³ 应该是 1.5。这类单位陷阱在环境数据处理里非常常见务必在表头写清楚单位并在录入前统一换算。5.2 数据有效性可以提前拦截错误除了事后排查更推荐在源头拦截。选中日报表的浓度区域 B2:H1000点击“数据”选项卡里的“数据验证”选择“小数”最小值填 0最大值填你业务中可能出现的天花板浓度比如 2000。这样如果有人手误输入负数或者中文字符Excel 会直接弹窗拒绝。更重要的是提前把可能出现的“缺测”情况约定好空单元格代表缺测不要填“无数据”“-9999”这类文本。空单元格在公式里会被 ISNUMBER 拦截并返回空不影响其他污染物计算。5.3 O3 的 8 小时滑动平均值怎么生成很多人只拿到逐小时臭氧浓度数据没有现成的 8 小时滑动平均值。这时候先用原始小时数据生成滑动平均再套用上面的 IAQI 公式。在 Excel 里8 小时滑动平均可以用 OFFSET 实现。假设 A 列是时间B 列是逐小时浓度从 C8 开始往下AVERAGE(B2:B9)这是最简单的静态写法对应 24 小时内第一个 8 小时窗口。往下复制时需要把窗口整体滑动直接拖动 AVERAGE 公式不会自动改范围所以更推荐用 OFFSET 写法AVERAGE(OFFSET($B$1,ROW()-1,0,8,1))这个公式以 B1 为基准点根据当前行号动态往下偏移 8 行再求平均。注意每个滑动窗口至少要有 6 个小时的有效数据不足 6 小时的窗口建议标记为无效这是行业里的常规操作。5.4 模板扩展一键可视化与月度汇总模板跑通后顺手再加两个加分项。AQI 颜色分级选中 AQI 列在“开始”选项卡里选“条件格式”用“色阶”或者自定义规则把“优、良、轻度污染、中度污染、重度污染、严重污染”分别显示为绿、黄、橙、红、紫、褐红。这样扫一眼就知道哪天空气质量爆表不用逐行看数字。月度汇总在日报表旁边新建一个透视表把日期拖到行区域AQI 拖到值区域改一下值字段设置就能快速算出每月 AQI 平均值、最大值、超标天数。如果还想追查哪种污染物最常成为首要污染物把首要污染物字段拖到行区域统计次数即可。这些扩展都不需要额外写公式纯鼠标操作。真正麻烦的 IAQI 插值计算前面的公式已经帮你搞定了。6. 最后再补充一点我的实操心得这套模板我前前后后用了两年多踩过的坑基本都写进上面章节了。如果只让我留一条经验那就是千万别跳过辅助列版本直接就去写 LET 长公式。辅助列虽然多占几行但它能让你一步一步验证中间结果哪里错了马上能看出来。等彻底跑通一遍再改成精简版效率反而最高。还有一个使用习惯值得分享把限值表单独放一个工作表后给它命名为“IAQI表”不要放在日报表旁边。以后每个月新建一份新的日报表公式里引用的限值表位置完全不变你只需要把当月监测数据黏贴进去十分钟搞定整月报表。哪怕换一个人来接手也不用重新教一遍计算逻辑打开 Excel 就能看懂、能用。