问计算每个单词出现在excel单元格范围内的次数。

EN

Function without_special_chars(text As String) As String
Dim i As Integer
Const special_chars As String = "-.,:;#+ß'*?=)(/&%$§!~\}][{"
For i = 1 To Len(special_chars)
text = Replace(text, Mid(special_chars, i, 1), "")
Next i
without_special_chars = text
End Function

Function IsInArray(stringToBeFound, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

Sub query()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Tabelle1")
Dim wordlist()
Dim nList()
Dim word As String
For i = 1 To ws.Cells(1048576, 11).End(xlUp).Row
    text = without_special_chars(ws.Cells(i, 11).Value)
    cell_words = Split(text, " ")
    For j = 0 To UBound(cell_words)
        the_word = cell_words(j)
        If j = 0 And i = 1 Then
            ReDim wordlist(0)
            ReDim nList(0)
            wordlist(UBound(wordlist)) = the_word
            nList(UBound(nList)) = 1
        Else
            If IsInArray(the_word, wordlist) = True Then
                For n = 0 To UBound(wordlist)
                    If wordlist(n) = the_word Then
                        nList(n) = nList(n) + 1
                        Exit For
                    End If
                Next
            Else
                ReDim Preserve wordlist(UBound(wordlist) + 1)
                ReDim Preserve nList(UBound(nList) + 1)
                wordlist(UBound(wordlist)) = the_word
                nList(UBound(wordlist)) = 1
            End If
        End If
    Next
Next
For m = 0 To UBound(wordlist)
    ws.Cells(m + 1, 1).Value = wordlist(m)
    ws.Cells(m + 1, 2).Value = nList(m)
Next
End Sub