计算Excel字符串中的单词的频率

本文介绍了计算Excel字符串中的单词的频率的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 假设我有一列任意长度，每个单元格包含一串文本。有没有办法确定列中最常见的单词（预先不知道要检查的单词），然后将这些单词及其频率排列在两列表中？ 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