Excel公式开发：标记未在120天内完成合格复检的违规设备

需求说明 #

现有含30000条测试记录的Excel表格，字段定义：

• A列：设备名称
• B列：测试日期（需为标准日期格式）
• C列：测试状态（PA=合格，FL=不合格）

需在D列实现公式，标记违规设备：当设备出现FL（不合格）记录后，120天内未产生晚于该不合格日期的PA（合格）记录，则判定为违规。

公式方案 #

方案1：适用于Excel 365/2021（支持动态数组） #

在D2单元格输入以下公式，下拉填充至所有行：

=LET(
    CurrentDev, A2,
    FailDate, B2,
    DevAllRecords, FILTER($A$2:$C$30001, $A$2:$A$30001=CurrentDev),
    PassAfterFail, FILTER(DevAllRecords, (DevAllRecords[测试日期]>FailDate)*(DevAllRecords[测试状态]="PA")),
    ValidPassCount, COUNTIFS(PassAfterFail[测试日期], ">="&FailDate, PassAfterFail[测试日期], "<="&FailDate+120),
    IF(C2="FL", IF(ValidPassCount=0, "违规", "合规"), "")
)

公式解释：

1. LET函数定义变量，简化后续逻辑
2. 筛选当前设备的所有测试记录
3. 进一步筛选该设备中，在当前不合格日期之后的合格记录
4. 统计这些合格记录中，落在不合格日期后120天内的数量
5. 如果当前行是不合格记录且有效合格记录数为0，标记为违规；否则标记合规；合格记录行留空

方案2：适用于旧版Excel（不支持动态数组） #

选中D2:D30001单元格区域，输入以下公式后按Ctrl+Shift+Enter完成数组公式输入：

=IF(C2="FL", IF(SUMPRODUCT(($A$2:$A$30001=A2)*($C$2:$C$30001="PA")*($B$2:$B$30001>B2)*($B$2:$B$30001<=B2+120))=0, "违规", "合规"), "")

公式解释：

1. SUMPRODUCT统计符合所有条件的记录数：
   • 与当前行设备名称一致
   • 测试状态为PA
   • 测试日期晚于当前不合格日期
   • 测试日期在当前不合格日期后120天内
2. 若统计数为0且当前行是不合格记录，标记违规；否则标记合规；合格记录行留空

注意事项 #

• 确保B列测试日期为标准日期格式，否则日期计算会出错
• 公式中的数据范围（如$A$2:$C$30001）需根据实际数据行数调整
• 旧版Excel使用数组公式时，必须通过Ctrl+Shift+Enter确认，直接回车无效

内容的提问来源于stack exchange，提问作者KRD