MS Excel 2010 中的字数统计

Question

是否有一个内置函数来计算ms-excel-2010？

更具体地说，选定范围内的单词数量是多少？

目前，我将范围复制到微软Word得到单词使用次数。

注意：我对字符数等不感兴趣，只对单词数感兴趣。

Answer 1

包含一些文字C1，在D1进入：

=LEN(C1)-LEN(SUBSTITUTE(TRIM(C1)," ",""))+1

这可以轻松修改以处理多个单元。

编辑＃1：

对所有列执行此操作C使用：

=SUMPRODUCT(--(LEN(C:C)-LEN(SUBSTITUTE(TRIM(C:C)," ",""))+1)*(C:C<>""))

Answer 2

经过在论坛上一番挖掘，我发现下面的代码与我想要的非常接近：

Sub CountWords()

    Dim MyRange As Range
    Dim CellCount As Long
    Dim TotalWords As Long
    Dim NumWords As Integer
    Dim Raw As String

'ACTIVE SELECTION
    Set MyRange = ActiveSheet.Range(ActiveWindow.Selection.Address)
    TotalWords = 0

    For CellCount = 1 To MyRange.Cells.Count

    If Not MyRange.Cells(CellCount).HasFormula Then

    Raw = MyRange.Cells(CellCount).Value
    Raw = Trim(Raw)
    If Len(Raw) > 0 Then

    NumWords = 1

    Else

    NumWords = 0

    End If

    While InStr(Raw, " ") > 0
    Raw = Mid(Raw, InStr(Raw, " "))
    Raw = Trim(Raw)
    NumWords = NumWords + 1

    Wend

    TotalWords = TotalWords + NumWords

    End If

    Next CellCount

    MsgBox "There are " & TotalWords & " words in the selection."

End Sub

Answer 3

既然您已经找到了解决方案，不妨也试试这个 UDF。不过尚未经过广泛测试。它将确保多余的空格（例如，在单词之间意外输入了多个空格）不计入字数，并且公式单元格结果中的值也会被考虑在内。

Public Function GetWordCount(r As Range) As Long

   Dim strTest As String
   Dim strArray() As String
   Dim intCount As Long
   Dim WordCount As Long
   WordCount = 0

   For Each cell In r

    strTest = cell.Value
    strArray = Split(strTest, " ")

    For intCount = LBound(strArray) To UBound(strArray)
       strArray(intCount) = Trim(strArray(intCount))
    Next

    For intCount = LBound(strArray) To UBound(strArray)
       If strArray(intCount) = "" Then
       Else
         WordCount = WordCount + 1
       End If
    Next

    Next cell
   GetWordCount = WordCount

End Function