Excel中的公式计算字符串中子字符串的出现次数 [英] Formula in Excel to count occurrences of substrings within strings

问题描述

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

字符串数据（tweets）的列如下所示：

  A 
 1一个包含@username的示例字符串
 2 RT @AwesomeUser表示@username是awesome 
  

具有子串（Twitter屏幕名称）的列如下所示：

  B 
 1用户名
 2 AwesomeUser 
  

我想使用公式计数B1，B2等子字符串出现在列A中的字符串中的次数。例如：搜索B1的公式将返回2，B2的搜索将返回1。 p>

我不能这样做：

  = COUNTIF（A ：A，username）
  

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

这是一个 formula 我以为可能会这样做：

  = SUMPRODUCT（（LEN（A：A） - （LEN（SUBSTITUTE（A：A，username，））））/ LEN （username））
  

不幸的是，我在B列中有16,000个条目，在A中有数万个因此，即使在高功率PC上，计数字符也不会起作用（同样，函数返回的结果是可疑的）。

我考虑过使用：

  = COUNTIF（A：A，* username *）
  

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

我的问题：有谁知道我可以使用公式吗？如果使用COUNTIF，如何在语句的条件部分中获取单元格引用（或使用函数替代在COUNTIF语句的条件部分中引用的单元格中的字符串）？

我知道我可以解析数据，但我想知道如何在Excel中执行。

解决方案

你几乎在那里，使用

  = COUNTIF（A：A，* & B1&*）
  

（在Excel 2010中测试）

I am trying to count the number of times a sub-string appears within a column of string data in Excel. Please see the below example.

The column of string data (tweets) looks like this:

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

The column with "substrings" (Twitter screen names) looks like this:

   B
1  username
2  AwesomeUser

I want to use a formula to count the number of times that a substring from B1, B2, etc. appears in the strings in column A. For example: a formula searching for B1 would return "2" and a search for B2 would return "1".

I can't do it this way:

=COUNTIF(A:A, "username")

because COUNTIF only looks for strings, not substrings. This formula would always return "0".

Here's a formula I thought might do it:

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

Unfortunately, I have 16,000 entries in column B and tens of thousands in A, so counting characters won't work even on a high power PC (also, the result returned by the function is suspect).

I thought about using:

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

but COUNTIF requires a string with the star operators; I need to use cell references due to the volume of data.

My question: does anyone know how I can use a formula for this? If using COUNTIF, how do I get a cell reference in the conditional part of the statement (or use a function to substitute the string in the cell referenced within the conditional part of a COUNTIF statement)?

I know that I could parse the data, but I would like to know how to do it in Excel.

解决方案

You are nearly there, use

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

(tested in Excel 2010)

这篇关于Excel中的公式计算字符串中子字符串的出现次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！