我浏览了所有统计Excel统计字符出现次

2022/12/17 来源：不详

我们都知道，在Excel表格中统计字符出现次数简单到小学生都会做。不就是COUNTIF()函数和SUBSTITUTE()函数嘛。

如果百度Excel表格字符出现次数统计，会搜出无数个教程，都会教这两个函数如何应用到字符出现次数的计数上。

然而，我发现，这些教程都是对这两个函数泛泛而谈，举的例子又是那些只适合他们教程的例子。在Excel表格中统计字符出现次数的通用方法方面，它们存在严重的误导Excel新手的问题。

我是不是在危言耸听呢？

我们看下面的例子。

01网上教程对COUNTIF()函数的错误用法

我们首先界定一下，在Excel表格中，一个值是什么意思。搞不懂这个的意思，就会落入网上那些教程不知不觉中埋下的坑中。

在Excel表格中，一个值就是独占一个单元格的。

所以在下表中，右边的表格里每一个字符串都是一个值，左边表格同样如此。但是，同样是这个字符串，在右边表格中，它是一个值，在左边表格中它仅仅是一个字符串而不是一个值。也就是说，除了在单元格中，是出现在列值中（注意我加粗了二字），从至，它都只出现在列中而未出现在列值中。

估计有点绕，或者语法上不严谨，大家仔细看下图，应该明白我说的意思。

也就是说，在左边表格中，前五行都只能找到包含这个字符串，而的值则等于。

网上的教程在讲授用COUNTIF()函数对字符串出现次数计数时，要么没有区分字符串是出现在列中呢还是等于列中的某个值。

所以，当我们用对出现次数计数时，得到的是1，而不是预期的4。

为了把包含要统计的字符串的列也统计到，网上又出现了使用通配符的COUNTIF()进行计数的教程。

照这样的教程，可以将公式设置为，结果如下：

很遗憾，我们得到了数字3，而不是期望的4。

为什么是4？因为在单元格出现了两次，很显然这个通配符版本的COUNTIF公式只读取了第一个而把后面再出现的全部忽略了。

所以，看到没，通配符版本的COUNTIF公式要统计字符串出现在列中的次数时，对列有严格要求——就是要寻找的字符串在该列的每一行只允许出现一次，否则统计结果就不准。

回顾下您看过的关于用COUNTIF()函数进行字符串计数时，是不是要么没提到字符串出现在列和列值的区别，要么没提到要查找的字符串在目标表格的每一行只允许出现一次？

那么，如何用Excel公式统计到字符串出现的正确次数呢？这需要用到SUBSTITUTE()函数。遗憾的是，网上教程对这个函数的用法也是有问题的。

02网上教程对SUBSTITUTE()函数的误用

在Excel表格中，对字符串出现次数进行统计的另一个方法是，用SUBSTITUTE()结合LEN()函数和SUMPRODUCT()函数来实现。

事实上，这个方法是解决这类计数问题的更通用方法，相比较而言，COUNTIF()函数计数限制太多，一不小心就会出错而不知道。所以推荐使用的方法。

网上教程怎么介绍这个方法的呢？

多是介绍如何用这个方法来统计单字符的出现次数。比如如果要统计这个字符出现的次数，可以用，这样我们得到12，正确。

但是我们要统计的是多字符串出现的次数，上面方法行不通。不信可以试试。

我找到一个介绍用这个方法来统计多字符出现次数的教程。

然而按照这种方法统计的结果是错的。

正确的方法是什么？或者说，更通用的公式是什么？是下面这个：

=SUMPRODUCT((LEN(A2:A4)-LEN(SUBSTITUTE(A2:A4,表2[

关键词],))))/LEN(表2[

关键词])

为什么要在单字符公式的基础上除以字符串的长度呢？这是因为，当用SUBSTITUTE()函数替换多字符串时，我们一般都是选择替换为空。这样一来，整个字符串其实被算成了一个字符，计算出的结果就会是实际出现次数的LEN(关键词)倍。

如果觉得绕，那就再解释下。上面引文中的公式其实是更一般的公式。当我们计算一个字符的出现次数时，其实是把后面的省略了，因为一个字符的长度就是1，除数是1的话，得到的商永远就是被除数本身，所以我们直接省略了这个1。

网上的教程几乎都没讲到这一点，比如这个教程，作者也没讲为啥要除以2而不是别的。

03总结

所以，我们在Excel中对字符串出现次数计数时，更一般的办法是用下面这个公式：

=SUMPRODUCT((LEN(绝对引用标识出的要查找的范围)-LEN(SUBSTITUTE(绝对引用标识出的要查找的范围,关键词,))))/LEN(关键词)

而尽量少用COUNTIF()函数，或使用SUBSTITUTE()结合LEN()函数和SUMPRODUCT()函数来对单字符进行计数。