Excel公式计算Logoff记录与其上方最近Logon记录的时间间隔

这个需求完全可以通过Excel原生函数实现，不需要VBA或者额外插件。
首先默认表结构为：

• A列：记录操作时间
• B列：记录操作类型（取值为PC LogOn/PC LogOff）
• 第1行为表头，有效数据从第2行开始，第三列（C列）为时长计算列

全版本通用公式（适配所有Excel版本） #

在C2单元格输入以下公式，按回车后下拉填充整列即可：

=IF(B2<>"PC LogOff","",A2-LOOKUP(1,0/($B$1:B1="PC LogOn"),$A$1:A1))

公式逻辑说明：

• 外层IF做第一层判断：如果当前行操作类型不是PC LogOff，直接返回空值，满足“仅登出记录显示时长”的要求
• LOOKUP部分负责定位当前行之前、距离当前行最近的一条PC LogOn记录的时间：通过0除以匹配结果生成含错误值的数组，LOOKUP会自动忽略错误值，匹配到区域内最后一个符合条件的登录时间
• 用当前行的登出时间减去匹配到的最近登录时间，得到的差值就是本次登录的在线时长。你可以根据需要把C列单元格格式设置为[h]:mm（显示小时:分钟格式），或者其他自定义时长格式。

如果需要直接得到以“小时”为单位的数值时长，可以把公式修改为：

=IF(B2<>"PC LogOff","",(A2-LOOKUP(1,0/($B$1:B1="PC LogOn"),$A$1:A1))*24)

输入后把C列单元格格式改为「常规」即可。

新版本Excel简化公式（适配Excel 365/2021及以上版本） #

如果你用的是支持XLOOKUP的新版本，可以用逻辑更直观的写法：

=IF(B2<>"PC LogOff","",A2-XLOOKUP("PC LogOn",$B$1:B1,$A$1:A1,,0,-1))

公式最后一个参数-1的作用是指定搜索方向为从下往上，直接命中当前行之前最近的一条登录记录，写法更易读。

注意事项 #

• 公式里的$B$1:B1、$A$1:A1为混合引用，锁定了区域起始行，下拉填充时会自动扩展到当前行的上一行，不会误算当前行及之后的记录，不要随意修改$符号的位置
• 如果你的数据不是从第2行开始，只需要对应调整公式里的起始行号即可，比如数据从第3行开始，就把公式里的$B$1:B1、$A$1:A1改成$B$2:B2、$A$2:A2

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