时间戳

首页 > 解决方案 > 如果列表的所有值都存在于单元格中，则将列表与范围进行比较并返回列标题

regex - 如果列表的所有值都存在于单元格中，则将列表与范围进行比较并返回列标题

问题描述

我正在尝试创建一个表格，该表格将显示乐队中选定的人何时可用。

有多个乐队，有些人被列在多个乐队中。

我已经将所有可用的人都列在适当的单元格中，但是拿那个列表并将其与乐队列表进行比较让我很适应。

这就是我目前所拥有的（我已经尝试了几次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”表示“忙碌”）

如果它们可用，它们的名称将添加到“可用性”中的相应单元格中，然后将其|分隔。

编辑：

一旦所有日历单元格都填充了可用的名称，然后将该名称列表与“乐队”选项卡中的每个乐队列表进行比较。如果名称组合与任何列中的所有名称匹配，则这些列上方的乐队名称（标题）将显示在单元格中（而不是个人名称）。

***将“组”更改为“乐队”***

我希望我已经解释了一切......

这是我的单子。非常感谢您的帮助！

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),)
)

标签： regexgoogle-sheetsgoogle-sheets-formulaarray-formulas

解决方案

我不清楚您问题的组部分，但这将使每个人都在一个单元格中，然后可以通过查找或类似的方式来获取组名称。

如果将每个人日历中的“x”替换为复选框，则可以在概览选项卡中使用以下公式：

 =TEXTJOIN(", ", True, 
  IF(Person1!B2, "", "Person1"), 
  IF(Person2!B2, "", "Person2")
)

查看工作表

解释

工作表将复选标记视为真或假。所以我问单元格是否选中（真），然后什么都不显示，如果它是空白的（假），然后显示人名。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")
)

推荐阅读