EXCEL VBA:创建单词列表时计算单词出现次数 [英] EXCEL VBA: Counting word occurence while creating list of words

问题描述

我需要创建一个用于A列中所有单元格的单词列表，并对列表中每个单词的出现次数进行计数.

I need to create a list of words used in all cells in column A, with a count of occurrence for each word on the list.

到目前为止，我已经能够创建单词列表. (通过搜索论坛.) 单词列表在B列中生成，有人可以帮助我提供代码，以便它也生成C列中的出现次数吗?

So far, I've been able to create the list of words. (by searching the forum.) The list of words is generated in column B, can anyone help me with the code so it also generate the count of occurrence in column C?

谢谢！

Sub Sample()

Dim varValues As Variant
Dim strAllValues As String
Dim i As Long
Dim d As Object

'Create empty Dictionary
Set d = CreateObject("Scripting.Dictionary")

'Create String With all possible Values
strAllValues = Join(Application.Transpose(Range("A1", Range("A" & Rows.Count).End(xlUp))), " ")
strAllValues = Replace(strAllValues, ".", "")
strAllValues = Replace(strAllValues, ",", "")
strAllValues = Replace(strAllValues, "!", "")
strAllValues = Replace(strAllValues, "?", "")
strAllValues = Application.WorksheetFunction.Trim(strAllValues)


'Split All Values by space into array
varValues = Split(strAllValues, " ")

'Fill dictionary with all values (this filters out duplicates)
For i = LBound(varValues) To UBound(varValues)
    d(varValues(i)) = 1
Next i

'Write All The values back to your worksheet
Range("B1:B" & d.Count) = Application.Transpose(d.Keys)

End Sub

推荐答案

我将处理唯一列表和计数.

I'll just deal with the unique list and count.

...
'Fill dictionary with all values (this filters out duplicates)
For i = LBound(varValues) To UBound(varValues)
    d.item(varValues(i)) = d.item(varValues(i)) + 1
Next i

'Write All The values back to your worksheet
Range("B1").resize(d.count, 1) = Application.Transpose(d.Keys)
Range("C1").resize(d.count, 1) = Application.Transpose(d.items)

这篇关于EXCEL VBA:创建单词列表时计算单词出现次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！