vba - Excel 宏 - 数据操作

Question

我是宏的新手，我需要以下帮助。



我有上面的值。我想计算苹果、橙子等的总数。
我希望最终结果在另一张纸上，如下所示
苹果 = 15 香蕉 = 17 芒果 = 15 橙子 = 13 甜瓜 = 7

我知道我需要先拆分它们并将它们存储在一个数组中并循环计算它们。但我不知道该怎么做。请帮忙!
谢谢!

Answer 1

Public Function ConcatResults(rng As Range) As String
Dim rng1 As Range, tmpArray() As String, nameArray() As String, sumArray() As Double, counter As Long
For Each rng1 In rng 'for each cell in your range
    If InStr(rng1.Value2, "=") > 0 Then 'if it contains an equal sign
        tmpArray = Split(rng1.Value2, "=") 'the cell value gets split by the equal sign
        If NameIndex(tmpArray(0), nameArray) > -1 Then 'if the fruit name is found already in the name array
           sumArray(NameIndex(tmpArray(0), nameArray)) = sumArray(NameIndex(tmpArray(0), nameArray)) + CDbl(tmpArray(1)) 'then it adds the number to the existing name's corresponding sum
        Else 'otherwise
            ReDim Preserve nameArray(counter) 'it expands the array of fruit names
            ReDim Preserve sumArray(counter) 'and the corresponding sum array
            nameArray(counter) = tmpArray(0) 'adds the name to the last (open) place in the name array
            sumArray(counter) = CDbl(tmpArray(1)) 'adds the name to the last (open) place in the sum array
            counter = counter + 1 'increments the index for further potential list items
        End If
    End If
Next rng1
'exports data
For i = LBound(nameArray) To UBound(nameArray) 'for the whole set
    ConcatResults = ConcatResults & nameArray(i) & " = " & sumArray(i) & " " 'it concatenates [NAME] = [SUM]
Next i

ConcatResults = Left(ConcatResults, Len(ConcatResults) - 1) 'removes the ending space

End Function
Function NameIndex(str As String, arr() As String) As Long 'this function tells the index of the given string (fruit) in the [name]array
'defaults to -1
NameIndex = -1
On Error GoTo err 'if the array is not yet defined it outputs the default -1
For i = LBound(arr) To UBound(arr) 'for each item in the set
    If arr(i) = str Then NameIndex = i 'if it's the same as the item we're looking for then outputs its index
Next i
err:
End Function

输出为 Apples = 15 Oranges = 13 Mangoes = 15 Banana = 12 Bananas = 5 Melon = 7 , 注意 Bananas = 5 来自描述中的拼写错误。