Excel按条件计算状态时长：跨行匹配Agent状态的公式实现

需求场景 #

当前行（如D2）为Agent John的「On Call」状态，起始时间8AM，该Agent的下一条状态记录在第5行（11AM，「Break」状态），中间隔了其他Agent的记录，需要跨行计算状态时长，预期输出3HRS。

解决方案 #

方案1：使用XLOOKUP（Excel 365/2021及以上版本） #

在D2单元格输入以下公式，下拉填充至整列：

=IFERROR(HOUR(XLOOKUP(B2,B3:$B$100,C3:$C$100,"",0,1)-C2)&"HRS","")

公式解释

• XLOOKUP(B2,B3:$B$100,C3:$C$100,"",0,1)：精准查找当前Agent（B2）在当前行之后的第一条匹配记录，返回对应的结束时间（C列）。参数0表示精确匹配，1表示向下查找后续行。
• 用找到的结束时间减去当前行的起始时间（C2），得到时间差。
• HOUR()提取时间差的小时数，拼接"HRS"得到目标格式。
• IFERROR()处理该Agent无后续记录的情况，返回空值（可根据需求修改为其他内容）。

方案2：使用INDEX+MATCH（兼容旧版Excel） #

如果你的Excel版本不支持XLOOKUP，可使用以下公式：

=IFERROR(HOUR(INDEX(C:C,MATCH(B2,B3:$B$100,0)+ROW())-C2)&"HRS","")

公式解释

• MATCH(B2,B3:$B$100,0)：找到当前Agent在后续行中的第一个匹配位置。
• ROW()获取当前行号，加上匹配位置得到结束时间所在的行号，再用INDEX(C:C,...)提取对应时间。
• 后续逻辑同方案1，计算小时差并格式化。

示例验证 #

针对你的示例：

• B2=John，C2=8AM
• B5=John，C5=11AM
• 公式会找到C5的11AM，计算11AM-8AM=3小时，最终输出3HRS，符合预期。

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