Excel匹配故障日期前最近服务日期及后续首个故障日期问题

核心需求梳理 #

针对你的5000条故障记录（列F-G），需要为每条故障匹配同一设备在故障日期之前的最近服务日期，最终输出格式为「设备编号 --- 服务日期 --- 服务后首个故障日期」，同时解决原公式的两个问题：仅匹配服务日期后的故障、排除无故障设备的错误匹配。

1. Excel 365/2021 动态数组公式（推荐） #

直接在故障记录旁的单元格输入以下公式，下拉填充即可：

=F2&" --- "&XLOOKUP(1, ($A$1:$A$36000=F2)*($B$1:$B$36000<=G2), $B$1:$B$36000, "", 0, -1)&" --- "&G2

公式说明：

• ($A$1:$A$36000=F2)：匹配当前故障对应的设备编号
• ($B$1:$B$36000<=G2)：过滤出故障日期之前的服务日期
• XLOOKUP 参数-1：从后往前查找，返回符合条件的最近服务日期
• 无匹配服务日期时返回空字符串，避免错误匹配其他数据

2. 旧版Excel兼容公式（非动态数组） #

如果使用旧版Excel，用INDEX+MATCH数组公式（需按Ctrl+Shift+Enter确认输入）：

=F2&" --- "&IFERROR(INDEX($B$1:$B$36000, MAX(IF(($A$1:$A$36000=F2)*($B$1:$B$36000<=G2), ROW($B$1:$B$36000), 0))), "")&" --- "&G2

公式说明：

• MAX(IF(...))：筛选出符合设备和日期条件的服务记录行号，取最大值即最近的服务日期行
• IFERROR：无匹配时返回空，避免#NUM!错误

针对两个问题的针对性修正 #

• 问题1（匹配服务后首个故障）：如果你的需求是为每条服务记录匹配之后的首个故障（而非故障匹配之前的服务），可使用以下公式：

  =A2&" --- "&B2&" --- "&XLOOKUP(1, ($F$1:$F$5000=A2)*($G$1:$G$5000>=B2), $G$1:$G$5000, "", 0, 1)
  

  参数1表示从前往后查找，返回服务日期后的首个故障日期，避免匹配服务前的旧故障。
• 问题2（无故障设备错误匹配）：上述公式通过XLOOKUP的空返回值或IFERROR，确保无故障设备（无对应G列数据）不会返回错误的默认值，而是显示空字符串。

注意事项 #

• 确保设备编号格式统一（无前后空格、同为文本/数字格式）
• 日期格式一致，避免因格式差异导致日期比较错误
• 尽量使用实际数据范围（如$A$1:$A$36000）而非整列引用，提升计算效率

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