统计重复次数，这样做超简单，隔壁同事都看呆！

作者：小花

编辑：妮妮

相比于数值运算，Excel 对字符的处理，通常都要复杂的多。

因此需要我们花更多的精力和脑力来学习。

今天，小花给大家详细拆解，如何处理字符串计数问题。

小眼睛要看着老师哦！

 

与传统的单元格计数问题不同，字符串计数问题统计的是满足条件的字符串出现的频数，而非单元格数量。

 

举个例子，我们有一张 GDP 前 20 城市的本科院校清单，需要统计每个城市有多少所大学？我们需要怎么做？

 

▲ 数据太多，图中仅展示一部分

 

传统的单元格计数都是以单元格为基本对象，使用 COUNTIF 或 COUNTIFS 函数来完成统计，但这并不能完成案例中对大学数量的统计。

案例中要统计每个城市大学的数量，就是要统计「大学」这两个字符在单元格字符串「复旦大学，同济大学，上海交通大学，......」中出现的次数，它应该是对字符串进行比较，而非单元格！

这就是字符串计数，它是以单元格内的字符串为基本对象的，是对字符串的逐一比对和计数的一种计数问题。

这种问题该如何使用函数来解决呢？

COUNTIF 显然是无能为力了，我们需要一些文本函数来操刀。 

拆分比对法

如何使用 Excel 公式计算某个字符串，在特定单元格内出现的次数呢？

第一种思路是，把单元格中的字符串拆分成一个个独立的字符串，再与目标字符串进行逐一比对并计数。

举个简单的例子，我们需要计算方括号「【」在物料种类字符串中出现的次数，来确定某个订单涉及的物料种类数量。

 

按先拆分再比对的运算逻辑，我们使用 COUNT MID ROW 的数组公式来完成。

公式如下：

{=COUNT((0/(MID($B2,ROW($1:$100),1)='【')))}

▲ 左右滑动查看

👉 公式说明：

❶ ROW(1:100)：通过数组运算，返回一组 1 到 100 的有序数组；

❷ MID(B2,❶,1）：MID 函数根据 ROW 函数确定的起始位置，分别从第 1 到第 100 个单元格开始，各提取 1 个字符，形成 100 个字符串，实现对每一个字符的拆分来；

❸ MID='【'：比对 MID 截取的字符串组与目标字符串「【」是否相同，相同返回 TRUE，不同返回 FALSE，即生成一组逻辑值数组；

❹ COUNT(0/③)：0/TRUE 为 0，0/FALSE 返回错误值#DIV/0!，COUNT 用于统计数字的个数，忽略错误，正好可以用来统计 0/TRUE 的个数，即是目标字符串「【」出现的次数。

 

👉 此处应该注意：

❶ ROW 函数返回多少个数字，是 1:100，还是 1:50，取决于单元格字符最高字符数，可以根据实际情况修改。

 

❷ MID 函数每次提取的字符串数量，应该等于目标字符串的字符数，此处目标字符串「【」的字符数为 1，所以我们将 MID 的第三个参数设置为 1。

完整的字符串计算公式这样书写：

=COUNT((0/(MID(单元格,ROW(1:最大字符数),LEN(目标字符串))=目标字符串)))

▲ 左右滑动查看

我们将这种方法运用到统计各城市大学数量案例中，就能很轻易地得到各城市的大学数量。

 

公式如下：

{=COUNT((0/(MID($B2,ROW($1:$600),2)='大学')))}

▲ 左右滑动查看

 

❸ 数组公式需要在输入公式完成后、退出单元格编辑前，同时按【Ctrl Shift Enter】，才能正确计算。

替换求差法

所谓替换求差法，就是使用 SUBSTITUTE 替换掉单元格内的目标字符串，再使用 LEN 函数分别计算替换前后单元格字符串的长度。

前后长度之差除以目标字符串的长度，就是目标字符串出现的次数。

 

=LEN(B2)-LEN(SUBSTITUTE(B2,'【',))

▲ 左右滑动查看

👉 公式说明：

❶ LEN(B2)：LEN 函数用于计算单元格文本字符串中的字符个数，此处用来计算 B2 单元格原有的字符串数量；

 

❷ SUBSTITUTE(B2,'【',)：SUBSTITUTE 函数可以，将单元格字符串内的部分字符以新字符替代；

 

我们通过将 B2 单元格中的'【'，替换为空，从而去除了所有的'【'，生成一个不包含目标字符'【'的新字符串；

 

❸ LEN(❷)：计算除去'【'后的新字符串中字符的个数，也是使用 LEN 函数的基本功能；

 

❹ LEN(B2)-❸：计算去除目标字符'【'前后、字符串的字符数，即为单元格包含目标字符'【'的个数。

 

与拆分比对法不同，替换求差法不需要数组运算。

但它需要根据目标字符串的字符数，将字符数之差除以目标字符串的字符数，来得到最终字符串计数结果。

 

此案例中，因为目标字符串的字符数为 1，小花偷了懒，完整公式应该这样书写：

=（LEN(单元格)-LEN(SUBSTITUTE(单元格,目标字符串,))）/LEN(目标字符串)

 ▲ 左右滑动查看

同样的，我们也可以用替换求差法，来解决城市大学数量统计问题。

 

公式如下：

=(LEN(B2)-LEN(SUBSTITUTE(B2,'大学',)))/LEN('大学')

▲ 左右滑动查看

当我们把需要计数的范围扩大到单元格区域时，只需要使用 PHONETIC 函数，将单元格区域连结起来，使用替换求差法，就可以照方抓药，药到病除啦！

我们来做一个有意思的字符串计数案例，统计下图中描写冬天的诗句，使用频率最高的关键字到底是什么？

 

=LEN(PHONETIC($D$2:$D$101))-LEN(SUBSTITUTE(PHONETIC($D$2:$D$101),F2,))

▲ 左右滑动查看

👉 公式说明：

此处，PHONETIC 函数的作用，仅仅是将 D2:D101 这 100 个单元格的内容，连结为一个新的字符串。

小伙伴们在理解上，只需将它视同为一个特殊的单元格即可。

 

好啦~以上就是本文关于字符串统计问题，我们一起来回顾一下吧：

❶ COUNT MID ROW 的数组公式，逐一拆分比对并计数；

❷ LEN SUBSTITUTE 求替换目标字符前后、字符个数之差；

❸ PHONETIC 函数的连结作用，帮助我们求多个单元格的字符串计数问题。

以上内容你学会多少了？快下载配套练习文件，巩固一下学习成果吧！