基于两个条件或正则表达式计算出现次数

Question

如何根据以下方法获取特定范围内的出现次数：

1. 一个正则表达式

2. 2个或更多条件，比如包含“yes”和/或“no”的单元格

目前我所拥有的：

COUNTIF(B5:O5; "*yes*")

我尝试使用 COUNTIF(B5:O5; {"*yes*", "*no*"}) 或者 COUNTIF(B5:O5; "(*yes*)|(*no*)")，但它们都没有起作用。

或者，我该如何使用正则表达式来统计包含特定域名（如yahoo.com，hotmail.com和gmail.com）的单元格？例如：

(\W|^)[\w.+\-]{0,25}@(yahoo|hotmail|gmail)\.com(\W|$)

- NGix

Answer 1

你问题的最朴素解决办法（在Excel和Google Docs中测试过）是简单地将多个countif公式的结果相加：

=COUNTIF(B5:O5, "*yes*") + COUNTIF(B5:O5, "*no*")

这个表达式将计算所有包含"yes"或"no"的单元格的总数。它将对包含"yesno"或"noyes"的单元格进行双重计数，因为它同时匹配了这两个表达式。您可以尝试使用

=COUNTIF(B5:O5, "*yes*") + COUNTIF(B5:O5, "*no*") - COUNTIF(B5:O5, "*no*yes*") - COUNTIF(B5:O5, "*yes*no*")

但是如果出现像noyesno这样的字符串，你仍然会遇到麻烦。

然而，在谷歌文档中有一个非常巧妙的技巧，可能只是你正在寻找的解决方案的暗示：

=COUNTA(QUERY(A1:A9, "select A where A matches '(.*yes.*)|(.*no.*)'"))

QUERY 函数类似于一个小型数据库。在这种情况下，它查看范围内的表格 A1:A9，并仅选择与列 A 中对应元素匹配的元素（按照 "preg regex" 的意义）后面跟随表达式的元素 - 在这种情况下，“任何后面跟着yes然后是任何内容，或者任何后面跟着no然后是任何内容”。在我制作的一个简单示例中，这只计算了一个 yesnoyes - 这正是您所要求的（我想是这样的...）

当前，您的范围 B5:O5 是几列宽，只有一行高；这使得使用 QUERY 技巧变得困难。以下方法相对不那么优雅（但无论范围的形状如何都可以工作）：

=countif(arrayformula(isnumber(find("yes",A1:A9))+isnumber(find("no",A1:A9))),">0")

isnumber 函数的总和相当于逐个元素应用 OR - 不幸的是，正常的 OR 函数似乎不能在数组的单个元素上工作。与以前一样，这会找到包含 "yes" 或 "no" 的单元格，并计算其中包含这些字符串之一的单元格数量。

- Floris

---

你能在将其发送到QUERY之前对TRANSPOSE(B5:O5)进行转置吗？或者QUERY在匿名矩阵的列标识符上存在问题吗？ - Michael

@Michael 你试过了吗？我现在无法测试，但我对答案非常感兴趣。 - Floris

我无法让它工作。不是用 QUERY(TRANSPOSE(B5:O5), "select A")，也不是用 …, "select col1")，甚至不是用 QUERY({"foo"; TRANSPOSE(B5:O5)}, "select foo", 1)。它总是抱怨缺少列标识符。select * 可以工作，但你不能在 where 子句中使用 *。 - Michael

我再次尝试并使其正常工作。请参见下面的链接：显然，Col1是“魔法”标识符，并且它区分大小写。请注意，它在文档示例标识符中的大小写不正确。 - Michael

Answer 2

这是受到 Floris's answer的启发而来。特别是看一下评论。如果你将要比较的项目行进行转置，则对于水平数据，QUERY也可以正常工作：

=COUNTA(QUERY(TRANSPOSE(B5:O5), "select * where Col1 matches '.*(yes|no).*'"))

据我所知，Col1是“特殊的”并且区分大小写！

- Michael

Answer 3

最近我想在Google表格中实现这个功能。我想出了一个不同的解决方案，看起来也非常易读。

=COUNTIF(ARRAYFORMULA(REGEXMATCH(O36:R36,"(yes|no)")),TRUE)

基本上它所做的是，在使用ArrayFormula运行REGEXMATCH的范围内返回TRUE或FALSE。然后使用COUNTIF来计算TRUE的出现次数。

- MWaheed

Answer 4

NGix 发现的最简单方法是创建一个自定义函数来实现这些目的。他们添加了2个完美地适用于他们的函数，并希望它也能帮到别人：

/**
 * Count if cell value matches any condition ( supports regexp also )
 */

function countCustomMatchOr(data){
  var count = 0;
  for(var i=0; i < data.length; i++){
    for(var j=0; j<data[i].length; j++){
        var cell = data[i][j];
      for(var k=1;k<arguments.length;k++){
        if(typeof arguments[k] == "number"){
          if(arguments[k] == cell) count++;
        } else if(cell.toString().match(arguments[k])) count++;
      }
    }
  }
  return count;
}

而且

/**
 * Counts value in data range if matches regular expression
 */

function countCustomRegExp(data, reg, flag){
  var rows = data.length, count = 0, re = flag?new RegExp(reg, flag):new RegExp(reg);
  for( var i = 0; i < rows; i++){
    for( var j = 0; j < data[i].length; j++){
      if( data[i][j] != "" && data[i][j].toString().match(re) ){
        count++;
      }
    }
  }
  return count;
}

为了使用它们，只需应用countCustomRegExp(A2:G3;"yes.*")或countCustomMatchOr(A2:G3;"yes";"no")

- Michael

Answer 5

我曾经遇到过同样的问题，如果你不想使用 VBA，可以尝试在公式中添加 SUM：

=SUM(COUNTIF(B5:O5; {"*yes*", "*no*"}))

- Leandro

---

这在Google Sheets中不起作用。请注意，该问题已标记为“google-docs”。 - Kris Khaira