'你这是条件改变并不是代码有弊端,原附件每个月的人数都是一样的,而你并没有说明
'假设姓名没有重复,不然仅获取最后一次出现的(覆盖),也就是数据会丢失,,,
Option Explicit
Sub test()
Dim arr, i, m, n, cnt, dic, p
Set dic = CreateObject("scripting.dictionary")
arr = Sheets("绩效汇总 ").[a1].CurrentRegion.Offset(1)
ReDim brr(UBound(arr, 1), 1 To 30 + 2) As String '最多支持30个月
brr(0, 1) = "部门": brr(0, 2) = "姓名": n = 3
For i = 1 To UBound(arr, 1) - 1
If Len(arr(i, 2)) Then p = arr(i, 2)
If Not dic.exists(arr(i, 4)) Then m = m + 1: dic(arr(i, 4)) = m
brr(dic(arr(i, 4)), 1) = p
brr(dic(arr(i, 4)), 2) = arr(i, 4)
brr(dic(arr(i, 4)), n) = arr(i, 6)
If arr(i, 1) <> arr(i + 1, 1) Then brr(0, n) = arr(i, 1): n = n + 1
Next
Call bsort(brr, 1, m, 1, n, 1)
With Sheets("绩效按月统计").[a1]
.Resize(Rows.Count, n + 1).ClearContents
.Resize(m + 1, n) = brr
End With
End Sub
Function bsort(arr, first, last, left, right, key)
Dim i, j, k, t
For i = first To last - 1
For j = first To last + first - 1 - i
If arr(j, key) > arr(j + 1, key) Then
For k = left To right
t = arr(j, k): arr(j, k) = arr(j + 1, k): arr(j + 1, k) = t
Next
End If
Next j, i
End Function |