求Excel中计算CLV的符合筛选条件的单单元格平均客户寿命公式

场景说明 #

需针对D列值为"62"的客户，计算每个客户首次与末次采购的天数差，再求这些差值的平均值，且用单个单元格公式实现。

方案1：Excel 365/2021（支持动态数组） #

直接输入以下公式到目标单元格（无需按组合键）：

=AVERAGE(BYROW(UNIQUE(FILTER(B:C,D:D="62")),LAMBDA(r,DATEDIF(XLOOKUP(r[1],B:B,C:C,,,1),XLOOKUP(r[1],B:B,C:C,,, -1),"d"))))

公式拆解 #

• FILTER(B:C,D:D="62")：筛选出所有D列为"62"的客户名与采购日期数据
• UNIQUE(...)：提取唯一客户列表，避免重复计算同一客户
• BYROW(..., LAMBDA(r, ...))：遍历每个唯一客户，执行天数差计算
  • XLOOKUP(r[1],B:B,C:C,,,1)：匹配该客户的首次采购日期（升序取第一个匹配项）
  • XLOOKUP(r[1],B:B,C:C,,, -1)：匹配该客户的末次采购日期（降序取第一个匹配项）
  • DATEDIF(...)：计算两个日期的天数差
• AVERAGE(...)：对所有客户的天数差取平均值

方案2：旧版Excel（无动态数组支持） #

输入以下公式后，按Ctrl+Shift+Enter触发数组计算：

=AVERAGE(IF(MATCH(B:B&C:C,B:B&C:C,0)=ROW(B:B),DATEDIF(MIN(IF(B:B=B:B,C:C)),MAX(IF(B:B=B:B,C:C)),"d"),"")*(D:D="62"))

公式拆解 #

• MATCH(B:B&C:C,B:B&C:C,0)=ROW(B:B)：标记每个客户的第一条记录，确保每个客户只计算一次
• MIN(IF(B:B=B:B,C:C))：获取当前客户的首次采购日期
• MAX(IF(B:B=B:B,C:C))：获取当前客户的末次采购日期
• DATEDIF(...)：计算天数差
• *(D:D="62")：仅保留D列为"62"的客户数据
• AVERAGE(...)：忽略空值，计算有效天数差的平均值

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