Excel中用于计算字符串中子串出现次数的公式

Question

我试图在Excel的字符串数据列中计算子字符串出现的次数。请参见下面的示例。

字符串数据列（推文）如下所示：

   A
1  An example string with @username in it
2  RT @AwesomeUser says @username is awesome

“substrings”列（Twitter屏幕名称）的外观如下：

   B
1  username
2  AwesomeUser

我想使用一个公式来计算从B1、B2等单元格中提取的子字符串在A列字符串中出现的次数。例如：搜索B1的公式将返回“2”，搜索B2将返回“1”。

我不能这样做：

=COUNTIF(A:A, "username")

因为COUNTIF只查找字符串，而不是子字符串。这个公式总是会返回"0"。

这里有一个我认为可以做到的公式：

=SUMPRODUCT((LEN(A:A)-(LEN(SUBSTITUTE(A:A,"username",""))))/LEN("username"))

很遗憾，列B中有16000个条目，而列A中有数万个条目，因此即使在高性能PC上计算字符也不起作用（而且函数返回的结果可疑）。我考虑使用：

=COUNTIF(A:A, "*username*")

但是COUNTIF需要带有星号运算符的字符串；由于数据量很大，我需要使用单元格引用。

我的问题是：有人知道我如何使用公式吗？如果使用COUNTIF，我如何在语句的条件部分中获得单元格引用（或者使用函数替换COUNTIF语句的条件部分中引用的单元格内的字符串）？

我知道我可以解析数据，但我想知道如何在Excel中完成它。

- Andrew

---

请注意，您引用的SUMPRODUCT公式与Chris的COUNTIF函数略有不同。 COUNTIF最多只会计算每个单元格一次，因此每个单元格要么得分1，要么得分零。 SUMPRODUCT公式将计算“所有”实例或“用户名”，即使它在任何单个单元格中出现多次-如果您想使用该公式，应限制范围而不是使用整个列。 - barry houdini

Answer 1

你已经快完成了，使用

=COUNTIF(A:A, "*"&B1&"*")

警告：
此公式计算的是在 A:A 中包含字符串“one”一次或多次的单元格数量，并不计算该字符串的总实例数。

- chris neilsen

---

Chris：那似乎是答案。感谢您的帮助！昨晚我尝试了连接，但语法可能出了问题。 - Andrew

Chris，这个不行。请尝试以下操作：在B1中输入“1”，现在A2：“s1df1”，A3：“dsad”，A4：“sd`1”，A5：“sdf1111”。 - Alex Gordon

@Aртём Царионов尝试了你的数据，返回了3个计数。你期望得到什么？ - chris neilsen

使用LibreOffice： =COUNTIF(A:B, ".*"&C&".*") :} - Kamiccolo

参数分隔符在您的Excel中可能不同。如果出现错误，您可以尝试使用分号。例如=COUNTIF(A:A; "*"&B1&"*")。 - aligokayduman

Answer 2

一种非常简单的Excel方案，用于计算y字符串（"ABC 123 ABC 23dfABC"）中x字符串（"ABC"）出现的次数：

1. 确定y字符串的长度。Ly = len(y)
2. 通过将x字符串替换为x1字符串（仅将任何受支持字符的1个字符附加到x字符串即可。例如，"ABC"=> "ABC_"），并计算新字符串y1-string的长度。Ly1 = len(substitute(y,x,x1))。
3. 在y字符串中出现的x字符串的数量=Ly1-Ly

出现次数：len(substitute(y,x,x1)) - len(y)

理解所使用的术语： x-string: "ABC" x1-string: "ABC_" y-string: "ABC 123 ABC 23dfABC" y1-string: ""ABC_ 123 ABC_ 23dfABC_"

- vinod bherwal

---

OP要求在整个单元格列中计算出现次数。这个答案计算了单个单元格（或单个字符串）中的出现次数......这实际上正是我所寻找的。谢谢!!! :-D. - Zephan Schroeder

谢谢你，Vinod。非常优雅的解决了我的问题。确实是在字符串中计算子字符串出现次数的方法。 - hpb

Answer 3

使用TEXTJOIN函数查找实际出现次数

使用TEXTJOIN函数（自2019年或MS 365版本以来可用）可以获取一个或多个列（例如列A:A或甚至是列A:B）中任何出现次数的实际数字。

因此，它避免了COUNTIF函数的警告

"仅计算（在A:A中）包含该字符串一次或多次的单元格数量。 它不计算字符串的总实例数。"

三个方法步骤

下面显示的公式（假设有一个命名的搜索术语MySearch）仅执行三个逻辑步骤以获取发现的实际数量：

• 1 通过 TEXTJOIN 连接给定列中的所有已填充单元格，
• 2 用长度减少 1 的术语替换字符串 1 中的搜索项，
• 3 从字符串 1 的长度中减去字符串 2 的长度。

=LEN(TEXTJOIN("|",TRUE,A:B))-LEN(SUBSTITUTE(TEXTJOIN("|",TRUE,A:B),MySearch,LEFT(MySearch,LEN(MySearch)-1)))

微软帮助 TEXTJOIN 函数

- T.M.