Excel-计算包含特定字符串的可见单元格的数量

Excel-计算包含特定字符串的可见单元格的数量

我有一张工作表,其中我首先对某一列应用过滤器以显示“仅唯一记录”。现在我有一列包含例如以下值:

Applepie
Applesauce
Crabapple
Banana
Mango

我需要的是包含单词“Apple”的所有可见单元格的数量。请注意,这应该排除隐藏的(过滤掉的)单元格。
现在我找到了一个公式:

`=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet1!A:A,ROW(Sheet1!A:A)-MIN(ROW(Sheet1!A:A)),,1))*(Sheet1!A:A="<Text to search>"))`

但是这个公式的问题在于搜索的是整个文本,而不是部分文本。如何修改这个公式以返回单元格数量包含“苹果”即对于这个例子,它应该返回 3 作为结果。

答案1

COUNTIF使用通配符可以单独计算文本中某处包含“Apple”的所有行......但它不限于可见行,并且不能COUNTIFSUBTOTAL公式结合使用。

SEARCH使用函数像这样调整公式

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet1!A:A,ROW(Sheet1!A:A)-MIN(ROW(Sheet1!A:A)),,1)),ISNUMBER(SEARCH("Apple",Sheet1!A:A))+0)

注意:该版本可能会很慢,引用整个列 - 如果可以的话最好限制范围

替代方法

如果您想避免使用那么长的公式,那么您可以使用辅助列来指示每行是否被过滤,例如在 Sheet1 Z2 中使用此公式向下复制

=SUBTOTAL(3,A2)

现在您可以使用COUNTIFS函数来计算包含“Apple”的可见行

=COUNTIFS(A:A,"*Apple*",Z:Z,1)

答案2

测试:=COUNTIF(A2:A10;"Apple*")改变range

相关内容