- Sub 测试()
- Dim i%, j%, k%, m%, n%, arr, brr, crr, drr
- Dim rng As Range, sht As Worksheet, wbk As Workbook
- Dim dic As Object, key, keys, items
- Set dic = CreateObject("scripting.dictionary")
- arr = Range("A1").CurrentRegion.Value
- brr = Range("A7").CurrentRegion.Value
- For i = 2 To UBound(arr)
- For j = 2 To UBound(arr, 2)
- key = arr(i, 1) & "-" & arr(1, j)
- dic(key) = dic(key) + arr(i, j)
- Next
- Next
- For j = 2 To UBound(brr, 2)
- For i = 2 To UBound(brr)
- key = brr(1, j) & "-" & brr(i, 1)
- dic(key) = dic(key) + brr(i, j)
- Next
- Next
- keys = dic.keys
- items = dic.items
- ReDim crr(1 To 100, 1 To 2)
- For i = LBound(keys) To UBound(keys)
- If items(i) <> 0 Then
- k = k + 1
- crr(k, 1) = keys(i)
- crr(k, 2) = items(i)
- End If
- Next
- Range("M2").Resize(k, 2) = crr
- End Sub
复制代码 |