时间戳

首页 > 解决方案 > 如何使用标准计算时间轴上的出现次数

excel - 如何使用标准计算时间轴上的出现次数

问题描述

我有一个包含 3 列的表：费用、client_id、日期。该日期有客户每天半年的费用记录。有些客户每天花费很多，但有些则不然。支出从零到几百不等。我想对每天花费超过 50 美元的活跃客户进行分组/计数，并计算在 0-30 天、30-60 天、60-90 天、90-120 天、120-150 天之间活跃的客户数量， 150+。我的意思是如果客户在 40 天内每天至少花费 50 美元，我会将他添加到 30-60 天列中。客户 ID 每天只出现一次。

费用 client_id 日期
20 1 2000 年 1 月 1 日
60 2 2020 年 1 月 1 日
70 3 2020 年 1 月 2 日

结果应该是这样的

0-30天 30-60 天 60-90 天 90-120 天
9 3 12 20

这些值是活跃客户的数量非常感谢

标签： excelexcel-formulacountexcel-2019

解决方案

可能会有更好的解决方案，帮助列更少，但这是我目前可以提供的。

在此处输入图像描述

准备

我为 10 个客户、一个月和 100 的费用限制生成了一些列A中的随机数据。您必须根据需要调整费用阈值和日期范围。C

辅助列和公式

  • criteria_met检查费用是否高于或等于给定阈值（此处为 10）。中的公式D2是=IF(A2>=10,TRUE,FALSE)。
  • is_consecutive检查客户端在前一天是否有条目。中的公式E2是=IF(COUNTIFS(B$1:B1,B2,C$1:C1,C2-1)>0,TRUE,FALSE)。
  • Continuous_group为客户支出高于或等于阈值的每组连续天数分配一个数字。中的公式F2是=IF(AND(D2=TRUE,E2=TRUE),MAXIFS(F$1:F1,B$1:B1,B2),MAXIFS(F$1:F1,B$1:B1,B2)+1)。每当criteria_met和is_consecutive的组合为FALSE时，组数就会增加一。
  • days_per_group计算每个client_id和Continuous_group的天数。中的公式G2是=COUNTIFS(B$1:B2,B2,F$1:F2,F2)。
  • max_per_group确保每个连续组只考虑最大连续天数。中的公式H2是=IF(G2=MAXIFS(G:G,B:B,B2,F:F,F2),G2,0)。

结果表

  • 标签创建标题。公式J3为=J2&"-"&K2-1&" days"

  • values计算给定阈值之间的数字出现的频率。中的公式J4是=COUNTIFS($H:$H,">="&I$2,$H:$H,"<"&J$2)。

要求

  • 每个客户每天只能有一个条目
  • 源列表 ( A:C) 必须按日期排序

如果您需要更多信息，请告诉我。

推荐阅读