如何用Excel公式判断指定时间是否匹配对应ID的多时间范围

你当前的公式仅能固定匹配单个Attempt的时间区间，要实现根据Test ID自动匹配对应所有Attempt的时间范围，可以用以下两种方案：

方案1：简洁高效的COUNTIFS公式（推荐，支持Excel 365/2021及以上） #

在TESTS工作表的D2单元格输入公式，下拉填充即可：

=IF(COUNTIFS(ATTEMPTS!$A:$A, $A2, ATTEMPTS!$B:$B, "<="&$C2, ATTEMPTS!$C:$C, ">="&$C2)>0, "In Range", "Out of Range")

公式逻辑： #

• COUNTIFS同时满足三个条件：
  1. ATTEMPTS表A列的ID与当前Test的ID（$A2）一致
  2. ATTEMPTS表B列的开始时间 ≤ 当前Test的时间（$C2）
  3. ATTEMPTS表C列的结束时间 ≥ 当前Test的时间（$C2）
• 只要统计到符合条件的记录数>0，说明该Test时间落在对应ID的任意一个Attempt范围内，返回"In Range"，否则返回"Out of Range"

方案2：适配旧版Excel的数组公式 #

如果使用无动态数组功能的旧版Excel，输入以下公式后按Ctrl+Shift+Enter确认（数组公式输入快捷键），再下拉填充：

=IF(SUM(($A2=ATTEMPTS!$A:$A)*($C2>=ATTEMPTS!$B:$B)*($C2<=ATTEMPTS!$C:$C))>0, "In Range", "Out of Range")

公式逻辑： #

• 通过数组运算逐一检查ATTEMPTS表的每条记录：
  1. $A2=ATTEMPTS!$A:$A：判断ID是否匹配，匹配返回1，否则0
  2. $C2>=ATTEMPTS!$B:$B：判断Test时间是否≥Attempt开始时间，是返回1，否则0
  3. $C2<=ATTEMPTS!$C:$C：判断Test时间是否≤Attempt结束时间，是返回1，否则0
• 三个条件相乘后，符合所有条件的记录会得到1，求和后若结果>0，说明存在匹配的时间范围

优化提示： #

为提升公式运行效率，尽量避免整列引用（如$A:$A），可替换为实际的数据行范围，比如ATTEMPTS表数据到第100行，就写成ATTEMPTS!$A$2:$A$100。

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