|
楼主 |
发表于 2019-8-2 16:32
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
我应该是在你的基础上,用的一种方法,有些部分可以优化。
Sub test()
Dim sh1, sh2 As Worksheet
Dim arr, brr, crr, d, dic, drr
Dim i, j, s, k, l, m
Set sh1 = Sheets("sheet1")
Set sh2 = Sheets("sheet2")
sh2.Cells.Clear
sh1.Rows("1:1").Copy sh2.[a1]
Set d = CreateObject("scripting.dictionary")
Set dic = CreateObject("scripting.dictionary")
arr = sh1.[a1].CurrentRegion
For i = 2 To UBound(arr, 1)
dic(arr(i, 1)) = ""
For j = 2 To UBound(arr, 2)
s = arr(i, 1) & arr(1, j)
d(s) = arr(i, j) + d(s)
Next
Next
brr = dic.keys
Set dic = Nothing
sh2.Cells(2, 1).Resize(UBound(brr) + 1, 1) = Application.Transpose(brr)
sh2.[dd1].Resize(d.Count, 2) = Application.Transpose(Array(d, keys, d.items))
drr = sh2.[dd1].CurrentRegion '字典存到数组,可以循环读入数组
crr = sh2.[a1].CurrentRegion
For k = 2 To UBound(crr, 1)
For l = 2 To UBound(crr, 2)
For m = 1 To UBound(drr, 1)
If arr(k, 1) & crr(1, l) = drr(m, 1) Then
sh2.Cells(k, l) = drr(m, 2)
End If
Next
Next
Next
sh2.[dd1].CurrentRegion
MsgBox "完成!!!"
End Sub
|
评分
-
1
查看全部评分
-
|