如何在有限制的日期范围的子集中计算网络工作日？

Question

演示工作簿在这里，现在有公式：https://docs.google.com/spreadsheets/d/1uj5P17iIesV6tEIwt00jUqJQdipxubk18CfV0uZshgU/edit?usp=sharing

这个问题非常类似，但与：Expand/explode a Google Sheets table with ticket open and close stats to show all tickets open each month不同。

我有一个职位空缺列表(输入选项卡)，我需要跟踪每月的指标。我的指标需要计算为<=10工作日开放的职位空缺数和每月开放超过10个工作日的职位空缺数，而忽略假日。

每个月的总数都需要计算上述月份和前一个月的净工作日。然而，在这个月结束后的任何一天，它都无法计数。任何未关闭的开盘都要从开盘日算到今天()。

例如，一个职务将于2020年12月31日开始，并于2021年1月14日关闭。即使这份工作在1月份只开放10个工作日，但由于前几个月有1个额外工作日，它被计算为>10个工作日。

2021年1月1/29和3/1/2021年结束的工作将被计算为<=10日，因为只有1个工作日；2月超过10个，因为总共有21个工作日；以及3月，因为有22个工作日，所以算在10天。

在演示工作簿的Metrics选项卡上，我需要一个公式来使B8:D9与B2:D3中的答案匹配，使用输入选项卡中的数据。

我在B8中计算12月份开放的<=10日中的就业情况的公式是：

=if(EOMonth(7，0)<7，0()，(countA(filter(输入！$A$2：$A，输入！$B$2：$B=“open”，$C$2：$C<=EOMonth！$C$2：$C<=EOMonth(7，0)，networkday(输入！$C$2：$C，EOMonth(7，0)<=10)，(countA(filter(输入！$A$2：$A，输入！$B$2：$B=“开放”，输入！$C$2：$C<=EOMonth(7，0)，网络工作日(输入！$C$2：$C，今天()<=10)

+if(EOMonth(7，0美元)<今天()，(countA(过滤器(输入！$A$2：$A，输入！$B$2：$B=“关闭”，输入！$D$2：$D<=EOMonth(7，0)，输入！$D$2：$D>=B$7，网络工作日(输入！$C$2：$C，EOMonth(B7，0)<=10)，(过滤器(输入！$A$2：$A，输入“关闭”，输入！$D$2：$D<=EOMonth(7，0)输入，输入！$D$2：$D>=B$7，网络工作日(输入！$C$2：$C，输入！$D$2：$D)

我在B9中使用的计算12月份就业人数的公式是：

=if(EOMonth(B7，0)<7，0()，(countA(filter(输入！$A$2：$A，输入！$B$2：$B=“open”，$C$2：$C<=EOMonth！$C$2：$C<=EOMonth(7，0)，网络工作日(输入！$C$2：$C，EOMonth(7，0))>10))，(countA(filter(输入！$A$2：$A，输入！$B$2：$B=“打开”，输入！$C$2：$C<=EOMonth(7，0)，网络工作日(输入！$C$2：$C，今天()>10)

+if(EOMonth(7，0美元)<今天()，(countA(过滤器(输入！$A$2：$A，输入！$B$2：$B=“关闭”，输入！$D$2：$D<=EOMonth(7，0美元)，输入！$D$2：$D>=B$7，网络工作日(输入！$C$2：$C，EOMonth(B7，0)>10))，(countA(过滤器(输入！$A$2：$A，输入！$B$2：$B=“关闭”，输入！$D$2：$D<=EOMonth(7，0)输入，输入！$D$2：$D>=B$7，网络工作日(输入！$C$2：$C，输入)>10)

正如你所看到的，计数是非常遥远的，我不能把逻辑直接放在我的脑子里，或者在纸上，好到足以把它弄清楚。

Answer 1

校验和-每个期间的净工作日：

={"Checksum"; INDEX(NETWORKDAYS(C2:C11, IF(D2:D11="", TODAY(), D2:D11)))}

​

​

每月净工作日：

=INDEX(IFNA(VLOOKUP(ROW(A1:A11)&"", QUERY({TEXT(SPLIT(QUERY(FLATTEN(IF(DAYS(IF(D2:D11="",TODAY(), D2:D11), C2:C11)>=
 SEQUENCE(1, MAX(DAYS(IF(D2:D11="", TODAY(), D2:D11), C2:C11)+1), ), ROW(C2:C11)&"×"&C2:C11+SEQUENCE(1, MAX(DAYS(IF(D2:D11="", TODAY(), D2:D11), C2:C11)+1), ), NA())), 
 "where Col1 matches '"&TEXTJOIN("|", 1, ROW(C2:C11)&"×"&C2:C11, ".*"&VALUE(UNIQUE(EOMONTH(SEQUENCE(TODAY()-MIN(C2:C11), 1, MIN(C2:C11)), -1)+1)))&"'"), "×"), {"#", "yyy-mm"}), NETWORKDAYS(
 INDEX(SPLIT(QUERY(FLATTEN(IF(DAYS(IF(D2:D11="",TODAY(), D2:D11), C2:C11)>=
 SEQUENCE(1, MAX(DAYS(IF(D2:D11="", TODAY(), D2:D11), C2:C11)+1), ), ROW(C2:C11)&"×"&C2:C11+SEQUENCE(1, MAX(DAYS(IF(D2:D11="", TODAY(), D2:D11), C2:C11)+1), ), NA())), 
 "where Col1 matches '"&TEXTJOIN("|", 1, ROW(C2:C11)&"×"&C2:C11, ".*"&VALUE(UNIQUE(EOMONTH(SEQUENCE(TODAY()-MIN(C2:C11), 1, MIN(C2:C11)), -1)+1)))&"'"), "×"),,2), 
 INDEX(SPLIT(QUERY(FLATTEN(IF(DAYS(IF(D2:D11="",TODAY(), D2:D11), C2:C11)>=
 SEQUENCE(1, MAX(DAYS(IF(D2:D11="", TODAY(), D2:D11), C2:C11)+1), ), ROW(C2:C11)&"×"&C2:C11+SEQUENCE(1, MAX(DAYS(IF(D2:D11="", TODAY(), D2:D11), C2:C11)+1), ), NA())), 
 "where Col1 matches '"&TEXTJOIN("|", 1, ROW(D2:D11)&"×"&IF(D2:D11="", TODAY(), D2:D11), ".*"&VALUE(UNIQUE(FLATTEN(EOMONTH(SEQUENCE(TODAY()-MIN(C2:C11), 1, MIN(C2:C11)), )))))&"'"), "×"),,2))}, 
 "select Col1,sum(Col3) group by Col1 pivot Col2 label Col1'1'"), SEQUENCE(1, COUNTUNIQUE(EOMONTH(SEQUENCE(TODAY()-MIN(C2:C11), 1, MIN(C2:C11)), )))+1, 0)))

​

​

你的月度指标应该是：

=INDEX(REGEXREPLACE(QUERY(QUERY(SPLIT(FLATTEN(TEXT(TRANSPOSE(UNIQUE(EOMONTH(SEQUENCE(TODAY()-MIN(C2:C11), 1, MIN(C2:C11)), ))), "yyymm\¤mmm-yyy")&"×"&
 REGEXREPLACE(REGEXREPLACE(QUERY(QUERY({TEXT(SPLIT(QUERY(FLATTEN(IF(DAYS(IF(D2:D11="",TODAY(), D2:D11), C2:C11)>=
 SEQUENCE(1, MAX(DAYS(IF(D2:D11="", TODAY(), D2:D11), C2:C11)+1), ), ROW(C2:C11)&"×"&C2:C11+SEQUENCE(1, MAX(DAYS(IF(D2:D11="", TODAY(), D2:D11), C2:C11)+1), ), NA())), 
 "where Col1 matches '"&TEXTJOIN("|", 1, ROW(C2:C11)&"×"&C2:C11, ".*"&VALUE(UNIQUE(EOMONTH(SEQUENCE(TODAY()-MIN(C2:C11), 1, MIN(C2:C11)), -1)+1)))&"'"), "×"), {"#", "yyy-mm"}), NETWORKDAYS(
 INDEX(SPLIT(QUERY(FLATTEN(IF(DAYS(IF(D2:D11="",TODAY(), D2:D11), C2:C11)>=
 SEQUENCE(1, MAX(DAYS(IF(D2:D11="", TODAY(), D2:D11), C2:C11)+1), ), ROW(C2:C11)&"×"&C2:C11+SEQUENCE(1, MAX(DAYS(IF(D2:D11="", TODAY(), D2:D11), C2:C11)+1), ), NA())), 
 "where Col1 matches '"&TEXTJOIN("|", 1, ROW(C2:C11)&"×"&C2:C11, ".*"&VALUE(UNIQUE(EOMONTH(SEQUENCE(TODAY()-MIN(C2:C11), 1, MIN(C2:C11)), -1)+1)))&"'"), "×"),,2), 
 INDEX(SPLIT(QUERY(FLATTEN(IF(DAYS(IF(D2:D11="",TODAY(), D2:D11), C2:C11)>=
 SEQUENCE(1, MAX(DAYS(IF(D2:D11="", TODAY(), D2:D11), C2:C11)+1), ), ROW(C2:C11)&"×"&C2:C11+SEQUENCE(1, MAX(DAYS(IF(D2:D11="", TODAY(), D2:D11), C2:C11)+1), ), NA())), 
 "where Col1 matches '"&TEXTJOIN("|", 1, ROW(D2:D11)&"×"&IF(D2:D11="", TODAY(), D2:D11), ".*"&VALUE(UNIQUE(EOMONTH(SEQUENCE(TODAY()-MIN(C2:C11), 1, MIN(C2:C11)), ))))&"'"), "×"),,2))}, 
 "select sum(Col3) group by Col1 pivot Col2"), "offset 1", 0)&"", "(\b([0-9]|10)\b)", "$1×# Jobs open <= 10 business days")&"", "(.*\d$)", "$1×# Jobs open > 10 business days")), "×"), 
 "where Col2 is not null"), "select Col3,count(Col2) group by Col3 pivot Col1")&"", "(.*¤)", ))

​

​