☰

Excel 中的 Countif 数字匹配模式

tag-icon • tag-icon microsoft-excel office365
Excel 中的 Countif 数字匹配模式

如何计算一列中以指定 3 个字符的前缀开头且总长度为 6 个字符的单元格的数量？

例如对于列

123000
123001
123002
546000
1230101

和前缀123，我想返回数字 3，对于包含123000、123001、的单元格123002（但不是1230101因为它太长）。

我试过了=COUNTIF(A:A, "123???")，但只是返回 0。

在此处输入图片描述

我猜它返回 0 是因为 excel 将单元格的内容视为数字，但查找模式是字符串，因此它永远不会匹配。然后我尝试=COUNTIF(TEXT(A:A, "0"), "123???")修复此问题，但这只会导致错误，我甚至不知道如何找到错误原因。

答案1

这很有趣！试试这个：

=COUNTIFS(A:A, ">122999", A:A, "<123999")

我认为您的原始公式不起作用，因为 A 列中的值是数字而不是文本，所以通配符搜索不起作用。

编辑：忘记 A：1 评论 - 我以为你要使用辅助列并将它们加起来。

答案2

因为这些是数字，所以您应该使用数字标准，而不是文本标准。因此，在这种特定情况下，您可以使用：

=COUNTIFS(A:A, ">=123000", A:A, "<=123999")

或者更一般地，如果您的前缀123在一个单元格中，假设B1您可以使用：

=COUNTIFS(A:A, ">=" & $B$1*1000, A:A, "<=" & $B$1*1000+999)

另一种可能的方法是将数字视为文本并使用SUM函数：

=SUM((LEN(A1:A100)=6)*(LEFT(A1:A100,3)="123"))

但在这种情况下，我建议避免引用完整的列。

答案3

有多种方法可以做到这一点。我将尝试概述其中几种方法。

你的第一个公式无法正常工作，因为数据类型条件范围是数字，而标准是文本。

如果数据类型为条件范围格式为文本。请参阅屏幕截图：

在此处输入图片描述

=COUNTIF(A9:A13,B9&"???")

但是上述公式有一个缺点，正如您所看到的，范围A9:A13贪婪地被格式化为数字，当单击那些红色复选标记时，第一个选项显示Number Stored As Text，第二个选项是Convert To Number，如果有人错误地将它们转换为数字，那么您的公式将返回0，因此应该避免。

这第二个公式你的函数不起作用，因为COUNTIF()函数处理Excel Range因此任何公式在里面使用COUNTIFS() 标准范围返回一个array。本质上，一些函数，包括所有IFs函数系列，如SUMIF()、等COUNTIFS()，AVERAGEIFS()都需要ranges参数criteria range。

这也解释了为什么这样的函数不能用于从关闭的工作簿中提取数据，因为从关闭的工作簿中提取的数据是作为array.

但是，您可以尝试以下步骤，这可能会使其相对容易使用和适应。

在此处输入图片描述

• *单元格中使用的公式C1

=SUM(--IFNA(LEN(TEXTAFTER(A1:A5,B1))=3,))

• 或者，单元格中使用的公式D1

=SUM(N(--LEFT(RIGHT(A1:A5,6),3)=B1))

• 或者，单元格中使用的公式E1

=SUM(N(--REPLACE(A1:A5,4,3,)=B1))

笔记： *这是MS365由于TEXTAFTER()功能需要的。根据版本不同，用户在退出编辑模式时Excel可能需要或不需要按CTRL+ SHIFT+ 。用户不需要上述按键。ENTERMS365

最后，正如前面提到的，您不应该使用整个范围，因为它会减慢工作功能，而是Excel将您的源范围/列表转换为Structured Referencesaka Tables，这使得它更容易在公式中使用，并且当数据大小发生变化时它会自动调整大小，例如，如果您删除一些行或添加行，公式将自动适应它。

相关内容