Excel长格式下按条件计算AE与前置AB问卷的时间差

需求说明 #

现有长格式Excel数据，每条记录包含day（日期）、form（问卷类型）、time（填写时间），同一日期下存在AB、AT、AE等多种问卷记录。需为每条AE问卷计算其与最近的前置AB问卷的时间差，忽略两者之间的其他问卷（如AT），最终效果如下：

解决方案 #

方法1：Excel 365/2021 用XLOOKUP（推荐） #

假设数据在A:C列，difference列在D列，在D2单元格输入以下公式后下拉填充：

=IF(B2="AE", TEXT(C2 - XLOOKUP(ROW(A2), IF($B$2:$B$10="AB", ROW($A$2:$A$10)), $C$2:$C$10, , -1), "h,mm"), "")

• 核心逻辑：用XLOOKUP从当前行向上查找最近的AB问卷对应的时间，计算AE与该时间的差值，仅在AE行显示结果
• 若数据行数超过10行，把公式中的$B$2:$B$10和$A$2:$A$10替换为实际数据范围

方法2：兼容旧版Excel 用LOOKUP #

如果没有XLOOKUP功能，可使用以下公式（D2单元格输入后下拉）：

=IF(B2="AE", TEXT(C2 - LOOKUP(2,1/($B$2:B2="AB"),$C$2:C2), "h,mm"), "")

• 核心逻辑：通过LOOKUP匹配当前行及以上最近的AB问卷时间，计算时间差后格式化显示

关键注意点 #

• 确保time列是Excel可识别的时间格式（不是文本格式），否则无法进行时间差计算
• 若同一日期有多条AB问卷，公式会自动匹配AE之前最后出现的那一条AB记录
• 公式中的"h,mm"是时间差的格式，可根据需求调整（比如改为"h:mm"）

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