☰

在 Excel 中查找最常见的单词（子字符串）

tag-icon • tag-icon microsoft-excel worksheet-function
在 Excel 中查找最常见的单词（子字符串）

好的，我有一列，其中包含各州分支机构使用的数据库的名称。如下所示：

Filemaker
Access, Google Docs
Access, PT Avenue
Salesforce CRM
Microsoft SQL Server
iMIS
PT Avenue, Excel
Access, PT Avenue
Private Database
Google Docs, Constant Contact
Google Docs, Filemaker
Excel
Google Docs
Excel, MemberPlanet

我想生成最常用的数据库。诀窍在于其中一些州使用多个数据库，这些数据库列在同一个单元格中。我尝试使用这个公式：

=INDEX(B5:F5,MODE(MATCH(B5:F5,B5:F5,0)))

使用该公式将得到答案“Access, PT Avenue”，而我想要的真正答案是“Google Docs”。假设多个数据库总是用逗号分隔，有没有办法只返回出现次数最多的单个数据库（这称为子字符串，对吗？）？

答案1

这里有一个类似于 Máté Juhász 在评论中建议的解决方案。它只有几个步骤，但很简单，不需要复杂的公式；Excel 会完成所有繁重的工作。

  • 拆分数据。由于条目是用逗号分隔的，因此只需突出显示数据并使用基于逗号的文本到列即可。这给了我下面的 A 列和 B 列。请注意，文本到列会覆盖源值，因此如果您需要保留它们，只需使用数据的复制/粘贴版本即可。

在此处输入图片描述

  • 每个逗号后面都有一个空格，需要删除该空格，以免名称因前导空格而不同。在 C3 中，我使用了：

    =TRIM(B3)

    并将其复制到该列中。我在 A1 和 C1 中添加了“数据库”作为列标签，它们是下一步的源数据。

  • 我在 A 和 C 范围上分别使用了数据透视表，并将它们一个接一个地粘贴在 A 列中。只需快速拖放，然后进行一些首选项设置：

在此处输入图片描述

  • 我将 D 列中的结果与复制和粘贴特殊值相结合（简单的复制/粘贴就可以，但这样会消除边框）。这样会生成一个包含每列计数的列表。

  • 我使用它来生成一个新的数据透视表，以便用 SUM 合并列表。这就是单个列表中的所有数据库计数。

  • 如果列表很短，您可以目测查看结果（数据透视表按字母顺序对名称进行排序）。如果列表很长，并且有可能出现最大值并列的情况，您可以复制并粘贴结果，然后对其进行排序。我使用以下公式提取了数据透视表下显示的结果（如果出现并列情况，则仅提供第一个最大值数据库）：

    =INDEX(G21:G31,MATCH(MAX(H21:H31),H21:H31,0))

    这将在 SUM 列中找到 MAX 值，使用 MATCH 来识别行，并使用该结果从左列中选择数据库名称。

答案2

在此处输入图片描述

怎么运行的：

我假设您有数据A2:A15。

  • 选择数据并执行使用asTEXT to COLUMN进行拆分。COMMAdelimiter
  • 将此公式写入B2并填写。

=COUNTIF($A$2:$A2,A2)

  • 输入此反向查找公式得到结果。

=INDEX($A$2:$A$15,MATCH(3,$B$2:$B$15,0))

注意：

  • 从逻辑上讲，文本访问和 Google Docs 具有相同的出现频率，但由于 Access 优先，因此公式选择它。

根据需要调整公式中的单元格引用。

相关内容