6,210
社区成员
发帖
与我相关
我的任务
分享
Private Sub Worksheet_Activate()
Dim i As Long, k As Long
Dim s As String, n As Long
Dim c
k = 2: Rows("2:65530").ClearContents
With Sheet1
.Rows("2:65530").Sort Key1:=.Range("A2"), Order1:=xlAscending, Key2:=.Range("B2"), Order2:=xlAscending '按姓名、性别排序
s = .[C2]
'如果当前行和上一行姓名性别一样,就组合s,否则求总得分和abc的个数
For i = 3 To .[A65530].End(xlUp).Row + 1
If .Range("A" & i) = .Range("A" & i - 1) And .Range("B" & i) = .Range("B" & i - 1) Then
s = s & ";" & .Range("C" & i) '组合成绩字符串
Else
Cells(k, 1) = .Range("A" & i - 1) '姓名
Cells(k, 2) = .Range("B" & i - 1) '性别
s = LCase(s)
For Each c In Split(s, ";")
n = Asc(c) - Asc("a") + 1 '对应:a->1 b->2 c->3
Cells(k, n + 3) = Cells(k, n + 3) + 1 ' a b c 的个数
Cells(k, 3) = Cells(k, 3) + Sheet2.Cells(n + 1, 2) '求和
Next c
k = k + 1: s = .Range("C" & i)
End If
Next i
End With
End Sub
结果:
姓名 性别 总得分 a个数 b个数 c个数
李 男 8 1 2
张 男 8 1 2 1
张 女 4 2 1