Excel中按唯一Task ID及状态匹配计算两类日期的小时差

默认原始数据列映射：A列为状态字段、B列为Task ID字段、D列为记录日期时间字段，首行为表头，数据从第2行开始。 #

1. 提取符合要求的唯一Task ID #

筛选规则：Task ID总出现次数为2次，且同时包含「Travel Details」「Client Sent For」两个状态。

Excel 365/2021及以上版本 #

在空白输出区域的首个单元格（如F2）输入以下公式，按回车后自动溢出所有符合条件的去重Task ID：

=UNIQUE(FILTER(B:B,(COUNTIF(B:B,B:B)=2)*(COUNTIFS(B:B,B:B,A:A,"Travel Details")=1)*(COUNTIFS(B:B,B:B,A:A,"Client Sent For")=1)))

Excel 2019及更早版本 #

• 新增辅助列E，在E2单元格输入公式后下拉填充整列，标记符合条件的行：

  =AND(COUNTIF(B:B,B2)=2,COUNTIFS(B:B,B2,A:A,"Travel Details")=1,COUNTIFS(B:B,B2,A:A,"Client Sent For")=1)
  

• 选中B列，点击「数据」选项卡下的「高级筛选」，筛选条件设置为E列值为TRUE，勾选「选择不重复的记录」，将筛选结果复制到F列即可。

2. 计算两个状态的小时差 #

计算逻辑：同一Task ID下，「Travel Details」对应时间 减去 「Client Sent For」对应时间，结果以小时为单位。

Excel 365/2021及以上版本 #

在Task ID列右侧（G2单元格）输入以下公式，按回车后自动匹配计算所有ID的时间差：

=MAP(F2:F#,LAMBDA(x,SUMIFS(D:D,B:B,x,A:A,"Travel Details")-SUMIFS(D:D,B:B,x,A:A,"Client Sent For")))*24

公式直接返回小时数值，无需额外调整格式。如果存在时间顺序颠倒的异常数据，可在SUMIFS相减逻辑外层套ABS()函数取绝对值，避免出现负数结果。

Excel 2019及更早版本 #

在G2单元格输入以下数组公式，按Ctrl+Shift+Enter三键确认后下拉填充至所有ID行：

=ABS(INDEX(D:D,MATCH(1,(B:B=F2)*(A:A="Travel Details"),0))-INDEX(D:D,MATCH(1,(B:B=F2)*(A:A,"Client Sent For"),0)))*24

结果说明 #

最终输出两列内容：F列为去重后符合规则的唯一Task ID，G列为对应两条记录的小时差，计算逻辑与示例中Task ID 2552804的计算要求完全一致。

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