我有一张工作表,其中我首先对某一列应用过滤器以显示“仅唯一记录”。现在我有一列包含例如以下值:
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”的所有行......但它不限于可见行,并且不能COUNTIF
与SUBTOTAL
公式结合使用。
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