Sub WordsFrequency()
'*********************************************
'作者:冷水泡茶,微信公众号:Excel活学活用
'*********************************************
Dim WordApp As Object
Dim WordDoc As Object
Dim wdFile As String
Dim wb As Workbook
Dim ws As Worksheet
Dim savePath As String
Dim arr()
Dim dic As Object
Dim Words() As String
Dim Word As Variant
Dim Text As String
Dim totalWords As Long
Dim time As Single
time = Timer
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set dic = CreateObject("Scripting.Dictionary")
wdFile = FileSelected
If wdFile = "" Then
MsgBox "请正确选择Word文件!"
Exit Sub
End If
savePath = PathSelected
If savePath = "" Then
If Not wContinue("未选择保存路径,将保存在当前文件夹下!") Then Exit Sub
savePath = ThisWorkbook.Path
End If
'打开Word文档
Set WordApp = CreateObject("Word.Application")
WordApp.Visible = False ' 隐藏Word应用程序
Set WordDoc = WordApp.Documents.Open(wdFile)
Set wb = Workbooks.Add
Set ws = wb.Sheets(1)
'从Word文档中提取文本
Text = WordDoc.Range.Text
Text = Replace(Text, "-", "")
arr = Array(".", ",", "!", "?", ";", ":", "'", "\", "(", ")", "[", "]", _
"{", "}", "/", "\\", "|", "_", "*", "&", "%", "$", "#", "@", "+", _
"=", "<", ">", "~", "`", """", "—", "”", "“", "", Chr(10), Chr(13))
For i = LBound(arr) To UBound(arr)
Text = Replace(Text, arr(i), " ")
Next
'将文本分割成单词
Words = Split(Text, " ")
'统计词频
For Each Word In Words
Word = Trim(Word) ' 去掉单词前后的空格
If Len(Word) > 0 Then ' 确保单词非空
totalWords = totalWords + 1
dic(Word) = dic(Word) + 1
End If
Next
'在Excel中写入词频数据
ws.Cells(1, 1) = "Words"
ws.Cells(1, 2) = "Frequency"
ws.Range("A2").Resize(dic.Count, 1) = Application.WorksheetFunction.Transpose(dic.keys)
ws.Range("B2").Resize(dic.Count, 1) = Application.WorksheetFunction.Transpose(dic.items)
'保存Excel文件
wb.SaveAs savePath & "\" & "word_freq" & Format(Now, "YYYYMMDDhhmmss") & ".xlsx" ' 替换成你想保存的Excel文件路径
'关闭并释放对象
wb.Close
WordDoc.Close
Set ws = Nothing
Set wb = Nothing
Set WordDoc = Nothing
Set WordApp = Nothing
MsgBox "词频统计完成" & Chr(10) & "总字数:" & totalWords & Chr(10) & "单词数:" & dic.Count & Chr(10) _
& "共耗时:" & Timer - time
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub