google-sheets - Google 表格 - 使用过滤器作为序列的输入来查找唯一日期

Question

长期聆听者，第一次来电者....

对于几个人，我有多个开始和结束日期。所以像：

Name   Start   End

Sam    5/1     5/5

Bob    5/3     5/7

Sam    5/3     5/10

Ralph  5/7     5/12

Bob    5/7     5/12

Ralph  5/15    5/17

等等。每个人多达 13 个开始和结束日期条目。

我需要计算这些条目中的唯一日期。对于上面的 Sam，它将是 10: 5/1 - 5/10。（实际上，我只需要9- 我跳过 5/10。）对于 Bob 来说是5/3 - 5/12这样10，而 Ralph 是9。

我可以FILTER用来获取日期列表：

=FILTER(Sheet2!D:E,Sheet2!P:P,"=Sam")

有了开始（下面的列 A）和结束（下面的列 B）日期列表，我可以一次一行并生成一个唯一的日期数组（5/1、5/2、5 /3, 5/4, 5/5....5/10) 并计算唯一条目。Sequence 根据开始和结束日期生成数组中的每个日期；UNIQUE清理重复项；COUNT给我唯一日期的数量：

=COUNT(ArrayFormula(UNIQUE({SEQUENCE(B1-A1,1,A1);IF(A2>0,SEQUENCE(B2-A2,1,A2),"");IF(A3>0,SEQUENCE(B3-A3,1,A3),"");IF(A4>0,SEQUENCE(B4-A4,1,A4),"")})))

（为简洁起见，公式缩短 - 将是 13SEQUENCE项。）

我不知道如何将其组合成一个公式，并将结果放在一个带有每个人姓名的表格旁边，这样我就可以计算出唯一日期。结果将是：

Sam   10

Bob   10

Ralph 9

关于下一步尝试的任何建议？也许SEQUENCE是分析重叠日期的错误方法？

（虽然脚本会用数组中的 FOR EACH 来解决这个问题，但更喜欢公式。）

标签： google-sheetsgoogle-sheets-formula

Answer 1

我认为你是对的，序列是要走的路。

首先，如果没有差距，那是微不足道的，只是最大结束日期 - 最小开始日期：

maxifs(C$2:C,A$2:A,E2)-minifs(B$2:B,A$2:A,E2)

但是在存在间隔（如 Ralph）并且您不想计算它们的情况下，您可以使用 Sequence 生成跨越开始日期和结束日期的日期数组，然后使用 Countifs 检查每个日期是否落在在数据中的至少一对日期中：

=ArrayFormula(
countif(
   countifs(A$2:A,E2,B$2:B,"<="&sequence(maxifs(C$2:C,A$2:A,E2)-minifs(B$2:B,A$2:A,E2),1,minifs(B$2:B,A$2:A,E2)),
                     C$2:C,">="&SEQUENCE(maxifs(C$2:C,A$2:A,E2)-minifs(B$2:B,A$2:A,E2),1,minifs(B$2:B,A$2:A,E2))),
">0")
)

编辑

上述公式与评论中提到的不一致，因为从 max 到 min 的范围不包含在内（省略范围中的最后一个日期），而单独的子范围（如 Ralph 的）包含在内。

非包含公式（省略最后日期）：

=ArrayFormula(
countif(
   countifs(A$2:A,E2,B$2:B,"<"&sequence(maxifs(C$2:C,A$2:A,E2)-minifs(B$2:B,A$2:A,E2)+1,1,minifs(B$2:B,A$2:A,E2)),
C$2:C,">="&SEQUENCE(maxifs(C$2:C,A$2:A,E2)-minifs(B$2:B,A$2:A,E2)+1,1,minifs(B$2:B,A$2:A,E2))),
">0")) 

包含公式（包括最后日期）

=ArrayFormula(
countif(
   countifs(A$2:A,E2,B$2:B,"<="&sequence(maxifs(C$2:C,A$2:A,E2)-minifs(B$2:B,A$2:A,E2)+1,1,minifs(B$2:B,A$2:A,E2)),
C$2:C,">="&SEQUENCE(maxifs(C$2:C,A$2:A,E2)-minifs(B$2:B,A$2:A,E2)+1,1,minifs(B$2:B,A$2:A,E2))),
">0"))