☰

Excel - 如何计算可变长度范围内的字符串中字符的出现次数

tag-icon • tag-icon microsoft-excel worksheet-function
Excel - 如何计算可变长度范围内的字符串中字符的出现次数

背景：我正在分析一组电子邮件中主题出现的频率。每个主题都有一个对应的字符 (az)，每当主题的某个方面出现时，每封电子邮件都会被分配几个这样的字符：

数据的基本结构

在上面的例子中，MAIL2 涉及主题“c”2 次，涉及主题“d”2 次。

问题：我该如何总结这些事件每封电子邮件单独如果每封电子邮件对应的行数和字符数是不可预测的（在 1 到 ~12 之间）？

我正在考虑将结果格式化如下： 在此处输入图片描述

我可以为每行赋予其相应的电子邮件标签。我猜我会让每个灰色单元格计数一个字符（例如 'a' C2）的出现次数，该字符出现在从标记为 MAIL1 的所有行中的单元格连接起来的某种字符串中？但我将感谢任何允许为每封电子邮件提供单独结果的解决方案。

答案1

请注意，此答案中的公式需要在单元格中输入C2，然后根据需要复制/填充。

编辑：原来是不使用辅助细胞来解决问题的方法：

=
SUMPRODUCT(
  ($B2=$A$2:INDEX($A:$A,MATCH("*",$B:$B,-1)))*
  (LEN($B$2:INDEX($B:$B,MATCH("*",$B:$B,-1)))-LEN(SUBSTITUTE($B$2:INDEX($B:$B,MATCH("*",$B:$B,-1)),C$1,"")))
)

说明：该$A$2:INDEX($A:$A,MATCH("*",$B:$B,-1))术语和相应的B列版本用于动态调整源范围。该公式相当于适用于示例电子表格的以下静态版本：

=
SUMPRODUCT(
  ($B2=$A$2:$A$11)*
  (LEN($B$2:$B$11)-LEN(SUBSTITUTE($B$2:$B$11,C$1,"")))
)


最简单的解决方案是以下公式：

=COUNTIFS($A:$A,$B2,$B:$B,"=*"&C$1&"*")

不幸的是，这种方法在单元格中有多个字母的特殊情况下会失效。例如，单元格中的“f”B11只会被计算一次。


我们能通过对多次出现的次数求和，可以以丑陋且有限的方式解决此问题。例如，以下方法最多适用于任何字符出现三次的情况：

=COUNTIFS($A:$A,$B2,$B:$B,"=*"&C$1&"*")
+COUNTIFS($A:$A,$B2,$B:$B,"=*"&C$1&"*"&C$1&"*")
+COUNTIFS($A:$A,$B2,$B:$B,"=*"&C$1&"*"&C$1&"*"&C$1&"*")

或者，我们可以使用辅助单元格。将以下公式复制/填充到范围内的每个单元格中即可C2:H11实现此目的：

=
IF(
  ""<>$A2,
  LEN($B2)-LEN(SUBSTITUTE($B2,C$1,"")),
  SUMIF($A:$A,$B2,C:C)
)

简要说明：

""<>$A2选择单元格是辅助单元格（真）还是灰色结果单元格（假）。

LEN($B2)-LEN(SUBSTITUTE($B2,C$1,""))计算相应单元格中相应字母出现的次数。

SUMIF($A:$A,$B2,C:C)对相应字母的计数求和。

相关内容