技术问询：如何用Excel公式自动计算不同行程间拖车的停留时长

核心逻辑 #

针对每个拖车，按时间顺序匹配前一个行程的最后一个COMPLETE时间戳和后一个行程的第一个ASSGN时间戳，计算两者的间隔小时数（确保两个时间戳属于不同Trip ID）。

方案1：Excel公式实现（适合中小规模数据） #

1. 先排序数据：按「拖车ID」升序、「时间戳」升序排列所有行，保证时间顺序正确。
2. 添加计算列：在ASSGN状态的行中，直接计算停留小时数，公式如下：

   =IF([@[状态码]]="ASSGN",IFERROR(([@[时间戳]]-XLOOKUP(1,([@[拖车ID]]=拖车ID)*(状态码="COMPLETE")*(Trip ID<>[@[Trip ID]]),时间戳,,0,-1))*24,""),"")
   

   公式说明：
   • XLOOKUP 从当前行向上查找同拖车、状态为COMPLETE、Trip ID不同的最近时间戳
   • *24 将日期差转换为小时数
   • 非ASSGN行或找不到匹配COMPLETE的行返回空值

方案2：Power Query实现（适合大规模数据） #

Power Query能批量处理分组排序和条件匹配，步骤如下：

1. 把数据导入Power Query（数据选项卡 → 自表格/区域）
2. 按「拖车ID」分组，在分组操作中添加自定义逻辑：

   let
       排序数据 = Table.Sort([分组数据],{{"时间戳", Order.Ascending}}),
       添加索引 = Table.AddIndexColumn(排序数据, "行索引", 0, 1),
       计算停留时间 = Table.AddColumn(添加索引, "停留小时数", (当前行) => 
           if 当前行[状态码] = "ASSGN" then
               let
                   筛选前序COMPLETE = Table.SelectRows(排序数据, 
                       each [行索引] < 当前行[行索引] 
                       and [状态码] = "COMPLETE" 
                       and [Trip ID] <> 当前行[Trip ID] 
                       and [拖车ID] = 当前行[拖车ID]
                   ),
                   最近COMPLETE = if Table.RowCount(筛选前序COMPLETE) > 0 then List.Last(筛选前序COMPLETE[时间戳]) else null
               in
                   if 最近COMPLETE <> null then Duration.TotalHours(当前行[时间戳] - 最近COMPLETE) else null
           else
               null
       )
   in
       计算停留时间
   

3. 展开分组数据，移除多余列，加载回Excel即可。

方案3：Python Pandas实现（适合自动化脚本） #

如果需要定期批量处理，用Python更高效：

import pandas as pd

# 读取数据（替换为你的文件路径）
df = pd.read_excel("拖车行程数据.xlsx")
# 按拖车ID和时间戳排序
df = df.sort_values(["拖车ID", "时间戳"], ascending=[True, True])

# 定义分组计算函数
def compute_wait_time(group):
    # 提取每个Trip的最后一个COMPLETE时间
    trip_completes = group[group["状态码"] == "COMPLETE"].groupby("Trip ID")["时间戳"].last().reset_index()
    # 初始化停留时间列
    group["停留小时数"] = None
    
    for idx, row in group.iterrows():
        if row["状态码"] == "ASSGN":
            # 筛选符合条件的前序COMPLETE
            valid = trip_completes[(trip_completes["Trip ID"] != row["Trip ID"]) & (trip_completes["时间戳"] < row["时间戳"])]
            if not valid.empty:
                group.at[idx, "停留小时数"] = (row["时间戳"] - valid["时间戳"].iloc[-1]).total_seconds() / 3600
    return group

# 分组计算并整理结果
df = df.groupby("拖车ID").apply(compute_wait_time).reset_index(drop=True)
# 保存结果
df.to_excel("拖车停留时间计算结果.xlsx", index=False)

注意事项 #

• 确保「时间戳」列是可识别的日期时间格式，否则无法计算差值
• 若一个Trip存在多个COMPLETE状态，默认取该Trip的最后一个COMPLETE作为行程结束时间，可根据需求调整逻辑
• 第一个行程的ASSGN状态找不到前序COMPLETE，结果会显示空值/NaN，可根据业务需求填充默认值

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