excel - Countif 动态标准范围

Question

我有电子表格，其中包含以下值
csrini/无锆 csrini PAC PAC PAC PAC PAC PAC PAC PILLAR PILLAR

我想排除某些文本，例如(PAC PDU 和 Pillar)。
我想用动态COUNTIF公式，这样当我想添加排除项时，我可以不费吹灰之力地做到这一点。

是否可以在标准中添加范围？目前我正在使用下面的公式。

 =COUNTIFS(B6:M6,"<>*PAC*",B6:M6,"<>*PDU*",B6:M6,"<>*PILLAR*",B6:M6,"<>*Free*") 

谢谢

Answer 1

要使其动态工作，您需要使用数组公式。

首先定义您的单词以在某处排除列。我将使用 A:A在这个例子中。我也会使用 B:B作为搜索范围。

不区分大小写的搜索公式为:

=COUNTA($B:$B)-SUMPRODUCT((IF(ISERROR(SEARCH(TRANSPOSE(INDIRECT("A1:A"&COUNTA($A:$A))),$B:$B)),0,1)))

对于区分大小写的搜索更改 SEARCH至 FIND

=COUNTA($B:$B)-SUMPRODUCT((IF(ISERROR(FIND(TRANSPOSE(INDIRECT("A1:A"&COUNTA($A:$A))),$B:$B)),0,1)))

使用 crtl 输入数组公式+ shift + enter
此公式首先使用 COUNTA 获取不为空的 B 列的全部计数。然后从中减去 A:A 中的条件计数在 B:B 中找到.它使用 SEARCH 进行检查或 FIND对于 B:B 列中的每个标准值然后 SUMPRODUCT总结所有的比赛。

需要注意的一个重要部分是 TRANSPOSE ，这只是因为我将我的标准保存在列列表中。如果您将标准保留在一行而不是一列中，则删除转置， FIND函数只需要它是一个水平数组。

每当您在 A 列中添加数据行时， INDIRECT公式会更新范围，因此您不必手动更新它。