excel - 获取元素在数组中出现的次数

Question

如何计算元素在数组中出现的次数？

例如水果（香蕉、橙子、苹果、橙子、苹果、橙子）

我想要：香蕉(1) 橙子(3) 苹果(2)

我试过这个：

Private Function IsInArray(findMe As Variant, arr As Variant) As Long 
Dim element As Variant 
Dim count As Integer 
count = 0 
   For Each element In arr 
     If element = valToBeFound Then 
       count = count + 1 
       IsInArray = Replace(element & "(" & count & ")", " ", "") 
       Exit Function 

     End If 

   Next element 

End Function

标签： excelvba

Answer 1

尝试这个

Sub test()
    fruitArr = Array("banana", "orange", "apple", "orange", "apple", "orange")
    Set dict = CreateObject("Scripting.Dictionary")
    For Each Fruit In fruitArr
        sKey = Fruit
            If Not dict.Exists(sKey) Then
                dict.Add sKey, 1
            Else
                dict(sKey) = dict(sKey) + 1
            End If
    Next Fruit
    For Each key In dict.Keys
        Debug.Print key, dict(key)
    Next key
End Sub