基于重复ID计算客户到访间隔天数的Excel公式求助

传统Excel版本（非365/2021） #

在C2单元格输入以下数组公式，按Ctrl+Shift+Enter确认后下拉填充：

=IF(COUNTIF($A$2:A2,A2)=1,"首次到访",DATEDIF(INDEX($B$2:$B$100,MATCH(1,($A$2:$A$100=A2)*($B$2:$B$100<B2),0)),B2,"D"))

• 逻辑说明：
  • COUNTIF($A$2:A2,A2)=1 判断当前行是否为该客户的首次到访，是则返回"首次到访"
  • INDEX+MATCH 精准定位同ID下日期早于当前行的最近到访日期
  • DATEDIF(...,B2,"D") 计算两个日期的间隔天数

Excel 365/2021（支持动态数组） #

使用以下动态数组公式，输入到C2后会自动溢出填充所有结果：

=LET(
  sortedData, SORTBY(A:B, A:A, 1, B:B, 1),
  customerIDs, INDEX(sortedData,,1),
  visitDates, INDEX(sortedData,,2),
  dateDiff, SCAN(0, visitDates, LAMBDA(prev, curr, 
    IF(INDEX(customerIDs, ROW(curr)-ROW(visitDates)+1)=INDEX(customerIDs, ROW(curr)-ROW(visitDates)), curr-prev, "首次到访")
  )),
  HSTACK(sortedData, dateDiff)
)

• 逻辑说明：
  • SORTBY 先按客户ID、到访日期对数据排序
  • SCAN 遍历排序后的日期，对比当前行与上一行的客户ID，相同则计算日期差，否则标记"首次到访"
  • HSTACK 将原数据与计算结果合并输出

注意事项 #

• 确保B列单元格格式为日期格式，避免文本格式导致计算错误
• 传统公式中的$B$2:$B$100需根据实际数据范围调整
• 动态数组公式无需手动下拉，Excel会自动填充所有行

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