Sub a() Dim arr, arrt Dim i%, j%, K% Dim Dic As New Dictionary Dim Did As New Dictionary Dim Die As New Dictionary With Sheets("数据源") arr = .Range("a2:c" & .[a65536].End(xlUp).Row) End With For i = 1 To UBound(arr) Dic(arr(i, 1)) = "" Did(arr(i, 2)) = "" Die(arr(i, 1) & arr(i, 2)) = arr(i, 3) Next With Sheets("生成表") .Cells.ClearContents .[a1] = "姓名" .[b1].Resize(1, Did.Count).NumberFormatLocal = "yyyy-m-d h:mm;@" .[b1].Resize(1, Did.Count) = Did.Keys .[a2].Resize(Dic.Count, 1) = WorksheetFunction.Transpose(Dic.Keys) arrt = .[a1].Resize(Dic.Count + 1, Did.Count + 1) For K = 2 To UBound(arrt) For j = 2 To UBound(WorksheetFunction.Transpose(arrt)) arrt(K, j) = Die(arrt(K, 1) & arrt(1, j)) Next Next .[a1].Resize(Dic.Count + 1, Did.Count + 1) = arrt .Cells.EntireColumn.AutoFit End With Set Dic = Nothing Set Did = Nothing Set Die = Nothing End Sub
Sub a() Dim arr, arrt Dim i%, j%, K% Dim Dic As New Dictionary Dim Did As New Dictionary Dim Die As New Dictionary With Sheets("数据源") arr = .Range("a2:c" & .[a65536].End(xlUp).Row) End With For i = 1 To UBound(arr) Dic(arr(i, 1)) = "" Did(arr(i, 2)) = "" Die(arr(i, 1) & arr(i, 2)) = arr(i, 3) Next With Sheets("生成表") .Cells.ClearContents .[a1] = "姓名" .[b1].Resize(1, Did.Count).NumberFormatLocal = "yyyy-m-d h:mm;@" .[b1].Resize(1, Did.Count) = Did.Keys .[a2].Resize(Dic.Count, 1) = WorksheetFunction.Transpose(Dic.Keys) arrt = .[a1].Resize(Dic.Count + 1, Did.Count + 1) For K = 2 To UBound(arrt) For j = 2 To UBound(WorksheetFunction.Transpose(arrt)) arrt(K, j) = Die(arrt(K, 1) & arrt(1, j)) Next Next .[a1].Resize(Dic.Count + 1, Did.Count + 1) = arrt .Cells.EntireColumn.AutoFit End With Set Dic = Nothing Set Did = Nothing Set Die = Nothing End Sub