计算Excel字符串中的单词的频率 [英] Counting the Frequencies of Words in Excel Strings

问题描述

假设我有一列任意长度，每个单元格包含一串文本。有没有办法确定列中最常见的单词（预先不知道要检查的单词），然后将这些单词及其频率排列在两列表中？ VBA是否最适合这项任务？

例如，单元格可能包含字符串这是一个字符串，并且该字符串中的字符数为> 0。 （错误有意）

解决方案

选择列 A 的一部分，并运行此小宏将被放置在cols中。 B & C ：

  Sub Ftable（）
 Dim BigString As String，I As Long，J As Long，K As Long 
 BigString =
  

'添加代码以总结全部和全部
'添加代码从之前的单词。！等分离，以便单词
也算在总数中，例如：all不应该被报告为1all，而是将all加到all字的总计数
'你发布这个新的代码？

  For each r In Selection 
 BigString = BigString&&价值
下一个r 
 BigString = Trim（BigString）
 ary = Split（BigString，）
 Dim cl As Collection 
 Set cl = New Collection 
为每个a In ary 
 On Error Resume Next 
 cl.Add a，CStr（a）
下一个
 
对于I = 1到cl.Count 
v = cl（I）
单元格（I，B）Value = v 
 J = 0 
对于每个a In ary 
如果a = v那么J = J + 1 
下一个
单元格（I，C）= J 
下一个I 
 
 End Sub 
  pre> 
Suppose I have a column of arbitrary length where each cell contains a string of text.  Is there a way to determine what words appear most frequently in the column (not knowing in advance which words to check) and subsequently order these words along with their frequencies in a two column table?  Would VBA be best for this task?   

As an example, a cell might contain the string "This is a string, and the # of characters inthis string is>0." (errors intentional)
解决方案
Select a portion of column A and run this small macro ( the table will be placed in cols. B & C  :
Sub Ftable()
    Dim BigString As String, I As Long, J As Long, K As Long
    BigString = ""
' Add code to sum both "All" and "all"
' Add code to separate "." "!" etc. from the word preceeding them so that word
' is also counted in the total. For example: "all." should not be reported as 1 ' "all." but "all" be added to the total count of "all" words.
' Would you publish this new code?
    For Each r In Selection 
          BigString = BigString & " " & r.Value
    Next r
    BigString = Trim(BigString)
    ary = Split(BigString, " ")
    Dim cl As Collection
    Set cl = New Collection
    For Each a In ary
        On Error Resume Next
        cl.Add a, CStr(a)
    Next a

    For I = 1 To cl.Count
        v = cl(I)
        Cells(I, "B").Value = v
        J = 0
        For Each a In ary
            If a = v Then J = J + 1
        Next a
        Cells(I, "C") = J
    Next I

End Sub


                        
这篇关于计算Excel字符串中的单词的频率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！