如何用LOOKUP函数按条件查找上一个日期/数值并计算差值

需求：匹配车牌（Matrícula），计算当前行的日期时间与该车牌上一条记录的日期时间差值。

现有公式及问题 #

• 合并日期与时间的公式：

  date&time = =+[@Fecha]+[@Hora]
  

• 尝试的差值计算公式：

  Diff = =+IF([@Matrícula]=[@Matrícula];([@[date&time]]-XLOOKUP([@Matrícula];[Matrícula];[date&time];0;0;1));"" )
  

• 问题：XLOOKUP的搜索模式设为1（从上到下匹配第一个结果），导致总是用当前值减去该车牌的第一条记录值，而非上一条记录。

解决方案 #

推荐使用以下两种公式，根据你的Excel版本选择：

方案1：XLOOKUP（Excel 365/2021及以上）

=IFERROR([@[date&time]]-XLOOKUP([@Matrícula];$A$2:A2;$D$2:D2;"";0;-1);"")

• 说明：
  • $A$2:A2和$D$2:D2是动态范围，只包含当前行及之前的记录，确保只查找当前行之前的同车牌记录
  • 搜索模式设为-1，会从后往前匹配，直接找到当前行之前的最后一条同车牌记录
  • IFERROR用于处理第一条记录（无前置记录时返回空）

方案2：INDEX+MATCH（兼容所有Excel版本）

=IFERROR([@[date&time]]-INDEX([date&time],MATCH(1,([Matrícula]=[@Matrícula])*(ROW([Matrícula])<ROW()),0)),"")

• 说明：
  • ([Matrícula]=[@Matrícula])*(ROW([Matrícula])<ROW())：筛选出同车牌且行号小于当前行的记录
  • MATCH(1,...,0)：找到符合条件的最后一条记录的位置
  • 旧版本Excel需按Ctrl+Shift+Enter完成数组公式输入，Excel 365直接回车即可

内容的提问来源于stack exchange，提问作者Alejandro González Espejo