在Excel中,查找最常用的单词( 子字符串)

分享于 

4分钟阅读

电脑

  繁體

问题:

好的,我有一个列,其中包含各种州的数据库。 就像这样:


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)))



当真正答案是"Google Docs"时,使用该公式将产生答案"接入,PT大街"。 假设多个数据库总是用逗号分隔,是否有一种方法只返回单个最频繁出现的数据库( 这称为子字符串,对)?


回答 1:

未测试的想法:

创建具有下列公式的第二列,并将它的应用到整个列:


=LEFT(A1,IFERROR(FIND(",",A1)-1,999))



想法是提取子字符串,或者整个字符串如果没有逗号,假定 999 长于最长的子字符串。


回答 2:

下面是类似于 Máté Juhász在评论中建议的解决方案。 这是一些步骤,但它们很简单并且不需要复杂的公式;Excel所做的所有重要提升。

  • 分割数据。因为项是逗号分隔的,只需突出显示数据并将文本用于列。 给我列了A 和B 列。 请注意,文本将覆盖源值,因这里如果需要保留它们,只需使用一个复制/粘贴版本。

enter image description here

  • 每个逗号之后需要删除一个空格,因为这些名称由于前导空间不同而不同。 在C3中,我使用了:

     
    =TRIM(B3)
    
    
    
     

    然后把它复制下来。 我在A1和C1中添加了"数据库"作为列标签,这是下一步的源数据。

  • 我在每个A 和C 范围中使用了一个 pivot 表,并将它的粘贴在另一个列A。 它是一些快速拖放,很少有偏好设置:

enter image description here

  • 我将结果列在列D 和复制和粘贴特殊值( 一个简单的拷贝/粘贴将有效,但这将)。 生成一个列表,其中包含每个列的计数。

  • 用于生成一个新的pivot 表,用SUM来合并列表。 这是单个列表中的所有数据库计数。

  • 如果列表是短的,你可以眼球看看结果( pivot 表按字母顺序对名称进行排序)。 如果有长列表和可以能存在最大的链接,可以复制并过去结果,然后对它的进行排序。 我将 pivot 表显示的结果与下面的公式( 它只提供了第一个最大数据库,如果有一个) 进行了比较:

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

    在SUM列中查找最大值,使用匹配标识行,并使用该结果从左列中选择数据库名称。



COM  COMM  WORD  FIND  Excel  subst  
相关文章