Excel中查找指定ID对应的最近前置检查日期方法

需求梳理 #

• 在第一个工作表中定位特定ID（xxxxx）与指定日期（x/xx/xxxx）的对应行
• 在第二个工作表中找出同一ID的所有匹配行
• 从第二个工作表的匹配日期里，筛选出恰好早于指定日期的最近日期

公式实现方案 #

假设工作表1名为Sheet1，工作表2名为Sheet2，特定ID为A123，指定日期为2024/5/20（可根据实际场景替换）

步骤1：验证目标行是否存在 #

用COUNTIFS函数确认第一个工作表中是否存在匹配的ID和日期：

=COUNTIFS(Sheet1!A:A, "A123", Sheet1!B:B, DATE(2024,5,20))

返回值大于0则说明目标行存在。

步骤2：提取最近的早日期（Excel 365/2021 版本） #

利用动态数组函数直接获取结果：

=MAX(FILTER(Sheet2!B:B, (Sheet2!A:A="A123")*(Sheet2!B:B<DATE(2024,5,20))))

• FILTER筛选出工作表2中ID匹配且日期早于指定值的所有日期
• MAX从筛选结果中取最大值，即为符合要求的最近日期

步骤3：兼容旧版Excel的方案 #

无动态数组支持的版本，使用数组公式（输入后按Ctrl+Shift+Enter确认）：

=MAX(IF((Sheet2!A:A="A123")*(Sheet2!B:B<DATE(2024,5,20)), Sheet2!B:B, ""))

关键注意事项 #

• 确保两个工作表的日期列均设置为日期格式，避免文本格式导致匹配失效
• 可将公式中的固定值替换为单元格引用（如Sheet1!D2），实现动态获取ID和日期的需求

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