Excel的vba实用合集

求特定类商品的购买量的平均值

去年的时候帮女朋友计算的，计算在两张表中，特定商品的购买量的平均值

Sub test()
'
' test 宏
'
Dim catgs(13) As String
Dim ca As Variant
catgs(0) = "饼干糕点"
catgs(1) = "冲调饮品"
catgs(2) = "方便速食"
catgs(3) = "坚果炒货"
catgs(4) = "酒类"
catgs(5) = "米面粮油"
catgs(6) = "蜜饯果蔬干"
catgs(7) = "茗茶茶包"
catgs(8) = "肉类零食"
catgs(9) = "乳品饮料"
catgs(10) = "生鲜特色"
catgs(11) = "食材调味"
catgs(12) = "小食糖巧"
catgs(13) = "滋补保健"


Dim index, j, rowCount As Integer
Dim r As Integer
Dim sum, counter As Double
index = 1
j = 1
rowCount = Sheets("Sheet0").UsedRange.Rows.Count
For Each ca In catgs
    Sheets("Sheet1").Cells(index, 1) = ca
    sum = 0
    counter = 0
    For r = 1 To rowCount
        If ca = Sheets("Sheet0").Cells(r, 6) Then
            sum = sum + Sheets("Sheet0").Cells(r, 10)
            counter = counter + 1
        End If
    Next
    Sheets("Sheet1").Cells(index, 2) = sum / counter
    index = index + 1
Next

'
End Sub