Excel 搜索子字符串并显示计数

Question

这是来自的一个扩展问题这个。

假设现在我有一张包含字符串的下表：

    A
1   CHI111
2   SEA222
3   SEA333
4   LA444
...

我还计划建立一个表格来统计关键字CHI、SEA和LA：

    M    N
1   CHI  1
2   SEA  2
3   LA   1

我该如何编写 N1 ~ N3 的公式来填充计数？

编辑：添加一个小变体

现在该表有两个具有相同条目数的关键字：

    A
1   CHI111
2   SEA222
3   SEA333
4   LA444
5   LA555
...

如何在单元格中显示两个用分隔符（逗号、斜线和 &）分隔的关键字。如下所示：

    M    N   O
1   CHI  1   SEA, LA
2   SEA  2
3   LA   2

此外，如果三个关键词的计数相同，则需要显示为“全部”

    M    N   O
1   CHI  2   ALL
2   SEA  2
3   LA   2

感谢帮助！！

Answer 1

要获取 N1 中的计数，请输入：

=COUNTIF($A:$A,"*" & M1 & "*")

然后你可以用这个公式来找到最多的值：

=INDEX(M:M,MATCH(MAX(N:N),N:N,0))

---

在一个公式中，前缀仍在 M1:M3 中，使用以下数组公式：

=INDEX($M$1:$M$3,MATCH(MAX(COUNTIF($A$1:$A$4,"*"&$M$1:$M$3&"*")),COUNTIF($A$1:$A$4,"*"&$M$1:$M$3&"*"),0))

它是一个数组，需要用 Ctrl-Shift-Enter 进行确认。如果操作正确，Excel 将会{}覆盖公式。

使用数组公式时，我们只想引用包含数据的范围，而不是使用完整的列引用。