答案1
这个宏可以解决问题
Public Sub itemaverages()
Dim wks As Worksheet
Set wks = ActiveSheet
firstrow = 2
resultcolumn = 6
titletext = "AVG"
resultrow = firstrow
searching = True
lastitemname = wks.Cells(firstrow, 1)
lastitemfresh = wks.Cells(firstrow, 2)
lastitemgroup = wks.Cells(firstrow, 3)
itemtotal = lastitemfresh
therow = firstrow + 1
While searching
countitem = 1
sameitem = True
While sameitem
itemname = wks.Cells(therow, 1)
itemfresh = wks.Cells(therow, 2)
itemgroup = wks.Cells(therow, 3)
If itemname <> "" Then
If (itemname = lastitemname) And (itemgroup = lastitemgroup) Then
itemtotal = itemtotal + itemfresh
countitem = countitem + 1
therow = therow + 1
lastitemname = itemname
lastitemfresh = itemfresh
lastitemgroup = itemgroup
Else
averagename = UCase(lastitemname) & " " & titletext
averagefresh = itemtotal / countitem
wks.Cells(resultrow, resultcolumn).Value = averagename & " " & averagefresh
wks.Cells(resultrow, resultcolumn + 1).Value = lastitemgroup
sameitem = False
lastitemname = itemname
lastitemfresh = itemfresh
lastitemgroup = itemgroup
itemtotal = itemfresh
resultrow = therow
therow = therow + 1
End If
Else
sameitem = False
searching = False
End If
Wend
Wend
a = MsgBox("Finished", vbInformation)
End Sub
使用 Alt+F11 打开 VBA/Macros,在下方插入一个新模块本工作簿,并将此代码粘贴到右侧。
变量firstrow
可以resultcolumn
根据您的需要进行调整。