根据2个条件或Regexp计算出现次数 [英] Count number of occurrences based on 2 conditions or Regexp
问题描述
如何基于
-
正则表达式
A regular expression
2个以上条件；假设包含是和/或否的单元格
2+ conditions; let's say cells that contain "yes" and / or "no"
我目前拥有的内容：
COUNTIF(B5:O5; "*yes*")
我尝试使用 COUNTIF（B5：O5; { * yes *， * no *}）或 COUNTIF（B5：O5;（* yes *）|（* no *）），但它们都不起作用。
I tried to use COUNTIF(B5:O5; {"*yes*", "*no*"}) or COUNTIF(B5:O5; "(*yes*)|(*no*)"), but neither of them worked.
或者，如何使用正则表达式计算包含某些域名的单元格— yahoo.com，hotmail.com和gmail.com&mdash ;？例如：
Or, how do I count cells that contain some domain names—yahoo.com, hotmail.com, and gmail.com—using regexp? e.g.:
(\W|^)[\w.+\-]{0,25}@(yahoo|hotmail|gmail)\.com(\W|$)
推荐答案
最可行的解决方案（在Excel和Google Docs中进行了测试）是简单地添加几个 countif 公式的结果：
The most pedestrian solution to your problem (tested in Excel and Google Docs) is to simply add the result of several countif formulas:
=COUNTIF(B5:O5, "*yes*") + COUNTIF(B5:O5, "*no*")
此表达式将计算带有是或否的单元格总数。因为它将匹配两个表达式，所以它将对 yesno或 noyes的单元格进行重复计数。您可以尝试使用
This expression will count the total of cells with "yes" or "no". It will double count a cell with "yesno" or "noyes" since it matches both expressions. You could try to take out the doubles with
=COUNTIF(B5:O5, "*yes*") + COUNTIF(B5:O5, "*no*") - COUNTIF(B5:O5, "*no*yes*") - COUNTIF(B5:O5, "*yes*no*")
但这仍然会让您遇到诸如 noyesno 之类的字符串的麻烦。
But that will still get you in trouble with a string like noyesno.
不过，Google文档中有一个相当巧妙的技巧，可能只是您正在寻找的解决方案的提示：
However there is a rather clever trick in Google Docs that may just be a hint of the solution you are looking for:
=COUNTA(QUERY(A1:A9, "select A where A matches '(.*yes.*)|(.*no.*)'"))
QUERY 函数就像一个小型数据库。在这种情况下，它将查看 A1：A9 范围内的表，并仅选择A列中与A列中的相应元素相匹配的元素 （在 preg regex 的意义上）后面的表达式-在这种情况下，任何后跟是后跟任何东西，或后跟否后跟任何东西。在我做的一个简单示例中，这只计算一次 yesnoyes -正是您所要的（我认为...）
The QUERY function is like a mini database thing. In this case it looks at the table in range A1:A9, and selects only elements in column A where the corresponding element in column A matches (in the preg regex sense of the word) the expression that follows - in this case, "anything followed by yes followed by anything, or anything followed by no followed by anything". In a simple example I made, this counts a yesnoyes only once - making it exactly what you were asking for (I think...)
现在您的范围 B5：O5 的宽度为几列，只有一行高；因此很难使用 QUERY 技巧。不太优雅的东西（但是不管范围的形状如何都可以）是这样的：
Right now your range B5:O5 is several columns wide, and only one row high; that makes it hard to use the QUERY trick. Something rather less elegant (but that works regardless of the shape of the range) is this:
=countif(arrayformula(isnumber(find("yes",A1:A9))+isnumber(find("no",A1:A9))),">0")
isnumber 函数之和在元素上 OR -不幸的是，常规的 OR 函数似乎不适用于数组的各个元素。和以前一样，它查找包含是或否的单元格，并计算其中包含这些字符串之一的单元格。
The sum of the isnumber functions acts as an element-wise OR - unfortunately, the regular OR function doesn't seem to work on individual elements of an array. As before, this finds cells that contain either "yes" or "no", and counts the ones that have either of these strings contained within.
这篇关于根据2个条件或Regexp计算出现次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
