我正在尝试创建一个表，该表将显示乐队中的选定人员何时同时可用。
有多个乐队，有些人被列在多个乐队中。
我已经把所有可用的人都列在了适当的单元格中，但把这个列表与乐队列表进行比较会让我感到不舒服。
这就是我目前所拥有的(我已经尝试了REGEXMATCH()的几次迭代，但运气不好(，但它仍然显示了一些不应该的可用波段。
=ARRAYFORMULA(
ArrayFormula(
IF(REGEXMATCH(
{IF(Dalene!B2:CH13="",Dalene!A1&"|",)&
IF(Rachel!B2:CH13="",Rachel!A1&"|",)&
IF(Phillip!B2:CH13="",Phillip!A1&"|",)&
IF(Jay!B2:CH13="",Jay!A1&"|",)&
IF(Jameson!B2:CH13="",Jameson!A1&"|",)&
IF('Scott E'!B2:CH13="",'Scott E'!A1&"|",)&
IF(Chuck!B2:CH13="",Chuck!A1&"|",)&
IF(Jennifer!B2:CH13="",Jennifer!A1,)},
Groups!C2:C),
Groups!C1,)
)&CHAR(10)&
ArrayFormula(
IF(REGEXMATCH(
Groups!D2:D,
{IF(Dalene!B2:CH13="",Dalene!A1&"|",)&
IF(Rachel!B2:CH13="",Rachel!A1&"|",)&
IF(Phillip!B2:CH13="",Phillip!A1&"|",)&
IF(Jay!B2:CH13="",Jay!A1&"|",)&
IF(Jameson!B2:CH13="",Jameson!A1&"|",)&
IF('Scott E'!B2:CH13="",'Scott E'!A1&"|",)&
IF(Chuck!B2:CH13="",Chuck!A1&"|",)&
IF(Jennifer!B2:CH13="",Jennifer!A1,)}),
Groups!D1,)
))
片材的工作原理是每个成员都有自己的标签来标记何时可用(清除框表示"可用"，"x"表示"繁忙"(
如果它们可用，则将它们的名称添加到"；可用性"；然后对其进行CCD_ 2定界。
编辑：
一旦所有的日历单元格都填充了那些可用的名称，则将该名称列表与来自"；带"；选项卡。如果名称组合与任意列中的所有名称匹配，则单元格中会显示这些列上方的乐队名称(标题((而不是个人名称(。
***"已更改"；组"；至"；带"***
我希望我已经解释了一切。。。
这是我的床单。非常感谢您的帮助！
https://docs.google.com/spreadsheets/d/1jE8BPrqAgQEfdCrHiFHtrMGPrD8cW-GHFyhAVYfFSrk/edit?usp=sharing
更新：
这个公式虽然很笨重，但却符合我的要求。我相信肯定会有一个简单得多的方法，但这就是我所能想到的，并且只需要输入一次代码而不需要拖动。
=ArrayFormula(
IF(IF(REGEXMATCH(IF(Dalene!$B$2:$CQ="",Dalene!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$C$2:$C)),1,)+
IF(REGEXMATCH(IF(Rachel!$B$2:$CQ="",Rachel!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$C$2:$C)),1,)+
IF(REGEXMATCH(IF(Phillip!$B$2:$CQ="",Phillip!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$C$2:$C)),1,)+
IF(REGEXMATCH(IF(Jay!$B$2:$CQ="",Jay!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$C$2:$C)),1,)+
IF(REGEXMATCH(IF(Jameson!$B$2:$CQ="",Jameson!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$C$2:$C)),1,)+
IF(REGEXMATCH(IF('Scott E'!$B$2:$CQ="",'Scott E'!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$C$2:$C)),1,)+
IF(REGEXMATCH(IF(Chuck!$B$2:$CQ="",Chuck!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$C$2:$C)),1,)+
IF(REGEXMATCH(IF(Jennifer!$B$2:$CQ="",Jennifer!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$C$2:$C)),1,)
=COUNTA(Bands!$C$2:$C),Bands!$C$1&CHAR(10),)&
IF(IF(REGEXMATCH(IF(Dalene!$B$2:$CQ="",Dalene!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$D$2:$D)),1,)+
IF(REGEXMATCH(IF(Rachel!$B$2:$CQ="",Rachel!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$D$2:$D)),1,)+
IF(REGEXMATCH(IF(Phillip!$B$2:$CQ="",Phillip!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$D$2:$D)),1,)+
IF(REGEXMATCH(IF(Jay!$B$2:$CQ="",Jay!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$D$2:$D)),1,)+
IF(REGEXMATCH(IF(Jameson!$B$2:$CQ="",Jameson!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$D$2:$D)),1,)+
IF(REGEXMATCH(IF('Scott E'!$B$2:$CQ="",'Scott E'!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$D$2:$D)),1,)+
IF(REGEXMATCH(IF(Chuck!$B$2:$CQ="",Chuck!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$D$2:$D)),1,)+
IF(REGEXMATCH(IF(Jennifer!$B$2:$CQ="",Jennifer!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$D$2:$D)),1,)
=COUNTA(Bands!$D$2:$D),Bands!$D$1&CHAR(10),)&
IF(IF(REGEXMATCH(IF(Dalene!$B$2:$CQ="",Dalene!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$E$2:$E)),1,)+
IF(REGEXMATCH(IF(Rachel!$B$2:$CQ="",Rachel!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$E$2:$E)),1,)+
IF(REGEXMATCH(IF(Phillip!$B$2:$CQ="",Phillip!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$E$2:$E)),1,)+
IF(REGEXMATCH(IF(Jay!$B$2:$CQ="",Jay!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$E$2:$E)),1,)+
IF(REGEXMATCH(IF(Jameson!$B$2:$CQ="",Jameson!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$E$2:$E)),1,)+
IF(REGEXMATCH(IF('Scott E'!$B$2:$CQ="",'Scott E'!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$E$2:$E)),1,)+
IF(REGEXMATCH(IF(Chuck!$B$2:$CQ="",Chuck!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$E$2:$E)),1,)+
IF(REGEXMATCH(IF(Jennifer!$B$2:$CQ="",Jennifer!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$E$2:$E)),1,)
=COUNTA(Bands!$E$2:$E),Bands!$E$1&CHAR(10),)&
IF(IF(REGEXMATCH(IF(Dalene!$B$2:$CQ="",Dalene!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$F$2:$F)),1,)+
IF(REGEXMATCH(IF(Rachel!$B$2:$CQ="",Rachel!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$F$2:$F)),1,)+
IF(REGEXMATCH(IF(Phillip!$B$2:$CQ="",Phillip!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$F$2:$F)),1,)+
IF(REGEXMATCH(IF(Jay!$B$2:$CQ="",Jay!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$F$2:$F)),1,)+
IF(REGEXMATCH(IF(Jameson!$B$2:$CQ="",Jameson!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$F$2:$F)),1,)+
IF(REGEXMATCH(IF('Scott E'!$B$2:$CQ="",'Scott E'!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$F$2:$F)),1,)+
IF(REGEXMATCH(IF(Chuck!$B$2:$CQ="",Chuck!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$F$2:$F)),1,)+
IF(REGEXMATCH(IF(Jennifer!$B$2:$CQ="",Jennifer!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$F$2:$F)),1,)
=COUNTA(Bands!$F$2:$F),Bands!$F$1&CHAR(10),)&
IF(IF(REGEXMATCH(IF(Dalene!$B$2:$CQ="",Dalene!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$G$2:$G)),1,)+
IF(REGEXMATCH(IF(Rachel!$B$2:$CQ="",Rachel!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$G$2:$G)),1,)+
IF(REGEXMATCH(IF(Phillip!$B$2:$CQ="",Phillip!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$G$2:$G)),1,)+
IF(REGEXMATCH(IF(Jay!$B$2:$CQ="",Jay!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$G$2:$G)),1,)+
IF(REGEXMATCH(IF(Jameson!$B$2:$CQ="",Jameson!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$G$2:$G)),1,)+
IF(REGEXMATCH(IF('Scott E'!$B$2:$CQ="",'Scott E'!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$G$2:$G)),1,)+
IF(REGEXMATCH(IF(Chuck!$B$2:$CQ="",Chuck!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$G$2:$G)),1,)+
IF(REGEXMATCH(IF(Jennifer!$B$2:$CQ="",Jennifer!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$G$2:$G)),1,)
=COUNTA(Bands!$G$2:$G),Bands!$G$1&CHAR(10),)&
IF(IF(REGEXMATCH(IF(Dalene!$B$2:$CQ="",Dalene!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$H$2:$H)),1,)+
IF(REGEXMATCH(IF(Rachel!$B$2:$CQ="",Rachel!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$H$2:$H)),1,)+
IF(REGEXMATCH(IF(Phillip!$B$2:$CQ="",Phillip!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$H$2:$H)),1,)+
IF(REGEXMATCH(IF(Jay!$B$2:$CQ="",Jay!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$H$2:$H)),1,)+
IF(REGEXMATCH(IF(Jameson!$B$2:$CQ="",Jameson!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$H$2:$H)),1,)+
IF(REGEXMATCH(IF('Scott E'!$B$2:$CQ="",'Scott E'!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$H$2:$H)),1,)+
IF(REGEXMATCH(IF(Chuck!$B$2:$CQ="",Chuck!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$H$2:$H)),1,)+
IF(REGEXMATCH(IF(Jennifer!$B$2:$CQ="",Jennifer!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$H$2:$H)),1,)
=COUNTA(Bands!$H$2:$H),Bands!$H$1&CHAR(10),)&
IF(IF(REGEXMATCH(IF(Dalene!$B$2:$CQ="",Dalene!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$I$2:$I)),1,)+
IF(REGEXMATCH(IF(Rachel!$B$2:$CQ="",Rachel!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$I$2:$I)),1,)+
IF(REGEXMATCH(IF(Phillip!$B$2:$CQ="",Phillip!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$I$2:$I)),1,)+
IF(REGEXMATCH(IF(Jay!$B$2:$CQ="",Jay!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$I$2:$I)),1,)+
IF(REGEXMATCH(IF(Jameson!$B$2:$CQ="",Jameson!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$I$2:$I)),1,)+
IF(REGEXMATCH(IF('Scott E'!$B$2:$CQ="",'Scott E'!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$I$2:$I)),1,)+
IF(REGEXMATCH(IF(Chuck!$B$2:$CQ="",Chuck!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$I$2:$I)),1,)+
IF(REGEXMATCH(IF(Jennifer!$B$2:$CQ="",Jennifer!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$I$2:$I)),1,)
=COUNTA(Bands!$I$2:$I),Bands!$I$1&CHAR(10),)&
IF(IF(REGEXMATCH(IF(Dalene!$B$2:$CQ="",Dalene!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$J$2:$J)),1,)+
IF(REGEXMATCH(IF(Rachel!$B$2:$CQ="",Rachel!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$J$2:$J)),1,)+
IF(REGEXMATCH(IF(Phillip!$B$2:$CQ="",Phillip!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$J$2:$J)),1,)+
IF(REGEXMATCH(IF(Jay!$B$2:$CQ="",Jay!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$J$2:$J)),1,)+
IF(REGEXMATCH(IF(Jameson!$B$2:$CQ="",Jameson!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$J$2:$J)),1,)+
IF(REGEXMATCH(IF('Scott E'!$B$2:$CQ="",'Scott E'!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$J$2:$J)),1,)+
IF(REGEXMATCH(IF(Chuck!$B$2:$CQ="",Chuck!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$J$2:$J)),1,)+
IF(REGEXMATCH(IF(Jennifer!$B$2:$CQ="",Jennifer!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$J$2:$J)),1,)
=COUNTA(Bands!$J$2:$J),Bands!$J$1&CHAR(10),)&
IF(IF(REGEXMATCH(IF(Dalene!$B$2:$CQ="",Dalene!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$K$2:$K)),1,)+
IF(REGEXMATCH(IF(Rachel!$B$2:$CQ="",Rachel!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$K$2:$K)),1,)+
IF(REGEXMATCH(IF(Phillip!$B$2:$CQ="",Phillip!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$K$2:$K)),1,)+
IF(REGEXMATCH(IF(Jay!$B$2:$CQ="",Jay!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$K$2:$K)),1,)+
IF(REGEXMATCH(IF(Jameson!$B$2:$CQ="",Jameson!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$K$2:$K)),1,)+
IF(REGEXMATCH(IF('Scott E'!$B$2:$CQ="",'Scott E'!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$K$2:$K)),1,)+
IF(REGEXMATCH(IF(Chuck!$B$2:$CQ="",Chuck!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$K$2:$K)),1,)+
IF(REGEXMATCH(IF(Jennifer!$B$2:$CQ="",Jennifer!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$K$2:$K)),1,)
=COUNTA(Bands!$K$2:$K),Bands!$K$1&CHAR(10),)&
IF(IF(REGEXMATCH(IF(Dalene!$B$2:$CQ="",Dalene!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$L$2:$L)),1,)+
IF(REGEXMATCH(IF(Rachel!$B$2:$CQ="",Rachel!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$L$2:$L)),1,)+
IF(REGEXMATCH(IF(Phillip!$B$2:$CQ="",Phillip!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$L$2:$L)),1,)+
IF(REGEXMATCH(IF(Jay!$B$2:$CQ="",Jay!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$L$2:$L)),1,)+
IF(REGEXMATCH(IF(Jameson!$B$2:$CQ="",Jameson!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$L$2:$L)),1,)+
IF(REGEXMATCH(IF('Scott E'!$B$2:$CQ="",'Scott E'!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$L$2:$L)),1,)+
IF(REGEXMATCH(IF(Chuck!$B$2:$CQ="",Chuck!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$L$2:$L)),1,)+
IF(REGEXMATCH(IF(Jennifer!$B$2:$CQ="",Jennifer!$A$1&CHAR(10),),TEXTJOIN("|",1,Bands!$L$2:$L)),1,)
=COUNTA(Bands!$L$2:$L),Bands!$L$1&CHAR(10),)
)
我不清楚你问题的组部分，但这会让每个人都在一个单元格中，然后可以通过查找或类似的方法来获得组名。
如果用复选框替换每个人日历中的"x"，则可以在概览选项卡中使用以下公式：
=TEXTJOIN(", ", True,
IF(Person1!B2, "", "Person1"),
IF(Person2!B2, "", "Person2")
)
查看工作表
解释
工作表会将选中标记视为true或false。所以我问单元格是否被选中(true(，然后什么都不显示，如果它是空的(False(，然后显示人名。TEXTJOIN将所有IF语句聚合到一个单元格中。
为每个额外的人添加额外的IF语句，然后将公式添加到每个单元格。您可以将分隔符更改为TEXTJOIN公式中所需的任何分隔符。
更新
以下是如何在顶部使用数组公式并将组名称输入单元格的方法。这不是一个动态的解决方案，你必须维护公式来考虑新的组：
=ArrayFormula(
REGEXREPLACE(
( IF((Person1!B2:B11=False)*(Person2!B2:B11=false), "Group1", "")
&", "& IF((Person2!B2:B11=False)*(Person3!B2:B11=false), "Group2", "")
&", "& IF((Person1!B2:B11=False)*(Person3!B2:B11=false)*(Person3!B2:B11=false), "Group3", "")
),
"^[, ]+|[, ]+$|(, )+","$1")
)