从每个用户的获取日期开始的自定义周数

Question

有没有办法计算从用户的第一个交易日期开始的自定义周数？"用户 (emailId("和"TransDate"列可能未处于排序状态，如下所示：

例如

+------+-------------+---------------------+
| WkNo |  TransDate  |       emailId       |
+------+-------------+---------------------+
|    1 | 2018-Aug-30 | [e-mail]    |
|    1 | 2018-Aug-30 | [e-mail]    |
|   10 | 2018-Nov-07 | [e-mail]    |
|    1 | 2018-Aug-09 | [e-mail]  |
|    1 | 2018-Aug-09 | [e-mail]  |
|    6 | 2018-Sep-20 | [e-mail]  |
|   15 | 2018-Nov-23 | [e-mail]  |
|   21 | 2018-Dec-31 | [e-mail]  |
|    1 | 2018-Aug-20 | [e-mail] |
|    5 | 2018-Sep-23 | [e-mail] |
|    7 | 2018-10-11  | [e-mail] |
|    7 | 2018-10-11  | [e-mail] |
|    8 | 2018-Oct-14 | [e-mail] |
|    9 | 2018-Oct-19 | [e-mail] |
|    1 | 2018-Jul-01 | [e-mail] |
|    6 | 2018-Aug-10 | [e-mail] |
|   13 | 2018-Oct-03 | [e-mail] |
+------+-------------+---------------------+

我使用 FILTER 函数编写了以下公式，然后将每个用户的过滤日期提供给 DATEDIF 函数。但是，我没有得到如上所示的预期结果。

=ARRAYFORMULA(if(B2:B="","",1 + round(DATEDIF(min(sort(FILTER(B2:B,C2:C=C2:C),1,true)),sort(FILTER(B2:B,C2:C=C2:C),1,true),"D")/7)))

编辑：

公式结果：

1
7
7
7
8
10
10
13
13
14
16
16
16
17
19
22
27

还从上面的公式中删除了排序：

=ARRAYFORMULA(if(B2:B="","",1 + round(DATEDIF(min(sort(FILTER(B2:B,C2:C=C2:C),1,true)),FILTER(B2:B,C2:C=C2:C),"D")/7)))

公式结果：

10
10
19
7
7
13
22
27
8
13
16
16
16
17
1
7
14

两者都似乎有效，但会产生意想不到的结果，因为 MIN 正在评估单个日期2018-Jul-01而不是每个用户的最小日期数组。我哪里出错了？

Answer 1

你试过使用 MINIFS(( 函数吗？

EG 具有以下特征

单元格 B2：B10 中的日期 单元格 C2：C10 中的电子邮件

单元格A2 中的公式将给出单元格 C2 中电子邮件地址的最早日期

=MINIFS($B$2:$B$10, $C2:$C10, "="&C2)

您应该能够在公式中使用它来计算周数

Answer 2

对于那些可能面临类似挑战的人来说，答案如下：

=ARRAYFORMULA(IF(B2:B="","",ROUND((B2:B-VLOOKUP(C2:C,SORT({C2:C,B2:B},2,1),2,0))/7)+1))

这个想法是在 C 列上做一个 Vlookup，按升序传递一个切换和排序的日期范围。然后从B列日期中扣除这些第一个日期，以获得所需的结果，即。几天或几周。

可以删除 +1，因为我使用它只是为了将开始周显示为 1 而不是 0。因此，结果可能略有不同。但是如果没有 +1，结果是准确的，特别是如果你正在做一个队列。

0
0
10
0
0
6
15
21
0
5
7
7
8
9
0
6
13

作为检查，我删除了除法 7 和 +1，然后检查了正确的天数。

0
0
69
0
0
42
106
144
0
34
52
52
55
60
0
40
94

希望这有帮助。