excel - 试图根据excel中的用户ID计算每个事件之间的天数

Question

尝试计算每个用户的游戏事件相对于其第一个事件的天数。即（t1-f），（t2-f）等对于每个用户ID

输入

     date        id     event   changepoint of user id
    1/25/2019   10002   Play    f
    1/25/2019   10002   Play    t
    1/1/2019    10004   Play    f
    1/30/2019   10012   Play    f
    1/30/2019   10012   Play    t
    2/13/2019   10012   Play    t
    1/19/2019   10013   Play    f
    1/19/2019   10013   Play    t
    1/19/2019   10013   Play    t
    1/21/2019   10013   Play    t
    2/3/2019    10019   Play    f
    2/3/2019    10019   Play    t

使用我使用的公式，我只是得到当前和前一天之间的天数差异。

预期产出

date         id     event   cp  days
1/25/2019   10002   Play    f   0
1/25/2019   10002   Play    t   0  (t-f)
1/1/2019    10004   Play    f   0
1/30/2019   10012   Play    f   0
1/30/2019   10012   Play    t   0  (t-f)
2/13/2019   10012   Play    t   14 (t-f)
1/19/2019   10013   Play    f   0
1/19/2019   10013   Play    t   0  (t-f)
1/19/2019   10013   Play    t   0  (t-f)
1/21/2019   10013   Play    t   2  (t-f)
2/3/2019    10019   Play    f   0  (t-f)
2/3/2019    10019   Play    t   0  (t-f)

标签： excel

Answer 1

我在这里做一些假设：

• 数据始终按日期和 ID 排序，
• 即 id 更改后 id 不再出现（例如 10002 在 ID 10019 后不再出现），
• f总是在前面t，最后t是需要区别的地方。

---

=IF(B2<>B3,IF(MIN(COUNTIFS(B:B,B2,D:D,"t"),COUNTIFS(B:B,B2,D:D,"f"))=0,0,MAXIFS($A:$A,$D:$D,"t",$B:$B,$B2)-MINIFS($A:$A,$D:$D,"f",$B:$B,$B2)),0)

MIN(COUNTIFS(B:B,B2,D:D,"t"),COUNTIFS(B:B,B2,D:D,"f"))=0这部分确保数据中至少有 1t和 1f以进行计算。如果只有f,MAXIFS将返回 0 并返回发生MINIFS的日期f，这将导致一个很大的负值。

MAXIFS($A:$A,$D:$D,"t",$B:$B,$B2)-MINIFS($A:$A,$D:$D,"f",$B:$B,$B2)给出天数的差异。

IF(B2<>B3, ... , 0)检查下一个ID是否不同，如果相同，则输入0（否则ID的每一行都会出现天数的差异）

---

如果您的 excel 不支持MINIFSand MAXIFS，您将不得不使用更长的数组公式（使用Ctrl++来正确使用它。如果 Excel 在公式周围加上大括号，您就知道您做得正确）Shift：Enter

=IF(B2<>B3,IF(MIN(COUNTIFS(B:B,B2,D:D,"t"),COUNTIFS(B:B,B2,D:D,"f"))=0,0,MAX(IF(($D:$D="t")*($B:$B=$B2),$A:$A))-MIN(IF(($D:$D="f")*($B:$B=$B2),$A:$A))),0)

---

在评论之后，我意识到我误解了这个问题。这是一个新公式，应该更符合您的预期：

=IF(D2="t",IF(COUNTIFS(B:B,B2,D:D,"t")=0,0,A2-SUMIFS($A:$A,$D:$D,"f",$B:$B,$B2)),0)