使用google sheets查询来确定Id在每个月内处于离线状态的天数

Question

我有一个谷歌工作表，可以跟踪Id何时离线，以及何时返回。此工作表中的每一行都有对象的Id、脱机日期、返回日期以及是否计数的布尔值。

在Sheet2上，我想跟踪对象在过去一年中每个月离线的天数。

示例数据

Id      StartDate   EndDate    Bool
999999  1/5/2020    1/10/2020  TRUE
999999  12/25/2020  1/4/2020   TRUE
888888  10/1/2019   1/20/2020  TRUE
888888  1/21/2020   1/30/2020  TRUE

Sheet2

Id      2/1/2020  1/1/2020  12/1/2019  11/1/2019  10/1/2019  9/1/2019
999999     15        15        15         15         15         15
888888     111       111       111        111        111        111

期望输出

Id      2/1/2020  1/1/2020  12/1/2019  11/1/2019  10/1/2019  9/1/2019
999999     0         9          7          0          0         0
888888     0         29         31         30         31        0

Google Sheet Example

为了计算产品离线的天数，我制作了以下代码

=SUM(QUERY(Sheet1!$A:$D,CONCATENATE("select datediff(C,B) where D=TRUE and A=",$A2)))

然而，它不检查日期是否在月份内，我正在考虑在datediff函数中使用if语句，它的工作原理如下，

select datediff(ifs(StartDate<1stOfTheMonth,1stOfTheMonth,StartDate>1stOfTheMonth and StartDate<EndOfMonth,StartDate,StartDate>EndOfMonth,EndOfMonth),ifs(EndDate>EndOfMonth,EndOfMonth,EndDate<EndOfMonth and EndDate>StartOfMonth,EndDate,EndDate<1stOfTheMonth,1stOfTheMonth)) where ...

如果开始日期在月份开始之前，则从月初开始计算天数；如果开始日期在月内，则从开始日期开始计算天数；如果开始日期在月开始日期之后，则返回0天，因为它从月底到月末计数。

如果结束日期在月末之后，则计数到月末；如果结束日期在该月内，则计数到结束日期；如果结束日期在该月之前，则返回0天，因为它从月初到月初计数。

我希望它能工作，但我不知道如何在查询函数中实现它。

Answer 1

在样例工作表上创建一个新的选项卡，将此公式放在A1中，然后将第一行的格式设置为Format>Number>More Formats>Custom Number格式：

嗯，yyyy

=ARRAYFORMULA(TRANSPOSE(QUERY(VLOOKUP(SEQUENCE(COUNTA(Sheet1!B2:B)*(MAX(Sheet1!C2:C-Sheet1!B2:B)+1),1,0)/(MAX(Sheet1!C2:C-Sheet1!B2:B)+1)+2,{ROW(Sheet1!B2:B),Sheet1!A2:A,IF((Sheet1!B2:B+SEQUENCE(1,MAX(Sheet1!C2:C-Sheet1!B2:B)+1,0)<=Sheet1!C2:C)*Sheet1!D2:D,IFERROR(EOMONTH(Sheet1!B2:B+SEQUENCE(1,MAX(Sheet1!C2:C-Sheet1!B2:B)+1,0),-1)+1),)},MOD(SEQUENCE(COUNTA(Sheet1!B2:B)*(MAX(Sheet1!C2:C-Sheet1!B2:B)+1),1,0),MAX(Sheet1!C2:C-Sheet1!B2:B)+1)*{0,1}+{2,3}),"select Col2,Count(Col2) where Col2 is not null group by Col2 pivot Col1 order by Col2 desc label Col2'ID'")))