时间戳

首页 > 解决方案 > 谷歌表格 - Countif 公式计数单元格特定文本多个标准

google-sheets - 谷歌表格 - Countif 公式计数单元格特定文本多个标准

问题描述

我目前有一个谷歌表，其中我根据参考表“Ref”计算 L 列中的值，然后根据状态列 W

IF(AND(COUNTIF(Ref!$A$1:$A$100,L2),W2 <>"Cancelled",W2 <>"Postponed",W2 <> "Dropped"),"Y", "N")

但是，现在 L 列中的值已经发生了变化。每个单元格中没有定义的值，它们将是每个单元格中的一串值，排列太大而无法进入我的参考表。

如何仍然计算 L 列中的值以正确返回“Y”或“N”

我努力了

=IF(AND(COUNTIF(L2,{"*LEE*","*LON*","*LAM*"}),W2 <>"Cancelled",W2 <>"Postponed",W2 <> "Dropped"),"Y", "N")

=IF(AND(COUNTIF(Ref!$A$1:$A$100,"*"&L2&"*"),W2 <>"Cancelled",W2 <>"Postponed",W2 <> "Dropped"),"Y", "N")

=IF(AND(COUNTIF(L2,{"*LEE*","*LON*","*LAM*"}),W2 <>"Cancelled",W2 <>"Postponed",W2 <> "Dropped"),"Y", "N")

我需要计算的值将包含以下短语之一“LEE”、“LON”、“LAM”。

输入到 L 列的新值将如下所示

<Area1: Y - LEE>,<Area2: Y - MIL>
<Area3: WF>,<Area4: Y - MUN>
<Area1: YY - MUN>,<Area2: YY - LON>,<Area3: YY-LAM>

所以我需要能够在单元格中搜索 3 个短语中的 1 个。

非常感谢任何帮助

谢谢

标签： google-sheetsexcel-formulagoogle-sheets-formula

解决方案

arrayformula()我有一个可以在单个单元格中使用的解决方案。

如果我理解正确，您似乎正在检查 Col L 以查看单元格是否包含 LEE LON 或 LAM。您还要检查它们是否出现在Ref!$A$1:$A$100.

在包含 col F 和 Col W（状态）的工作表上，在第 1 行试试这个（我用过AB1）：

=arrayformula({"Check";if(if(L2:L<>"",regexreplace(regexreplace(regexreplace(flatten(query(transpose({if(regexmatch(split(regexreplace(L2:L,"[^A-Z]",char(9999)),char(9999)),"LAM|LEE|LON"),split(regexreplace(L2:L,"[^A-Z]",char(9999)),char(9999)),"|")}),"",9^9)),"\ ",""),"(\|)+","\|"),"^\||\|$",),)<>"",if(REGEXMATCH(join(" ",Ref!A2:A),if(L2:L<>"",regexreplace(regexreplace(regexreplace(flatten(query(transpose({if(regexmatch(split(regexreplace(L2:L,"[^A-Z]",char(9999)),char(9999)),"LAM|LEE|LON"),split(regexreplace(L2:L,"[^A-Z]",char(9999)),char(9999)),"|")}),"",9^9)),"\ ",""),"(\|)+","\|"),"^\||\|$",),)),if(regexmatch(W2:W,"Cancelled|Postponed|Dropped"),"N","Y"),),)})

在此处输入图像描述

为了展示工作，我有三个帮助列AD，AE和AF：

在此处输入图像描述

AD1从 col L 获取 LAM LEE 或 LON：

=arrayformula({"LAM|LEE|LON entry";if(L2:L<>"",regexreplace(regexreplace(regexreplace(flatten(query(transpose({if(regexmatch(split(regexreplace(L2:L,"[^A-Z]",char(9999)),char(9999)),"LAM|LEE|LON"),split(regexreplace(L2:L,"[^A-Z]",char(9999)),char(9999)),"|")}),"",9^9)),"\ ",""),"(\|)+","\|"),"^\||\|$",),)})

AE1检查 col AD​​ 中的值以查看它们是否出现在Ref!A2:A：

=arrayformula({"LAM|LEE|LON match";if(AD2:AD<>"",REGEXMATCH(join(" ",Ref!A2:A),AD2:AD),)})

AF1检查 col AE 中的值是否为真，然后检查 col W 中的状态：

=arrayformula({"Check";if(AE2:AE=true,if(regexmatch(W2:W,"Cancelled|Postponed|Dropped"),"N","Y"),)})

3 个辅助列组合在AB1.

推荐阅读