本帖最后由 飞天篮球猪 于 2019-5-21 10:59 编辑
也用字典法合并同类行,供参考
- Sub combine_rows()
- Dim dic, rawdata, sz, jg
- Set dic = CreateObject("Scripting.Dictionary")
- Set rawdata = Sheets("合并").Range("a2").CurrentRegion
- cols = rawdata.Columns.Count
- ReDim jg(1 To rawdata.Rows.Count, 1 To cols + 1)
- sz = rawdata
- For i = 2 To UBound(sz)
- If dic.exists(sz(i, 3)) Then
- r = dic(sz(i, 3)): n = n + 1 '如果字典存在姓名,则返回该姓名所在的行号,并累计1次重复次数
- jg(r, 2) = jg(r, 2) & "," & sz(i, 2)
- jg(r, 5) = jg(r, 5) & "," & sz(i, 5)
- jg(r, 6) = jg(r, 6) + sz(i, 6)
- jg(r, 7) = Round(jg(r, 6) / n, 1)
- Else
- h = h + 1: n = 1 '如果字典中未曾出现姓名,则新建一个行号,并记录1次,该次数用于计算平均分
- dic(sz(i, 3)) = h '把新生成的行号赋值给字典,key是姓名,value是行号
- jg(h, 1) = h: jg(h, 7) = Round(jg(h, 6) / n, 1)
- For j = 2 To cols
- jg(h, j) = sz(i, j)
- Next j
- End If
- Next i
- Range("a30").Resize(1, cols + 1) = [{"序号","编号","姓名","班级","学科","总分","平均分"}]
- Range("a31").Resize(h, cols + 1) = jg
- End Sub
复制代码
|