也来练练手:
- Sub aa()
- Dim i%, j%, arr, arr1(), brr(), d As Object
- Set d = CreateObject("scripting.dictionary")
- arr = Sheets("原始数据").Range("A2:G" & Sheets("原始数据").Range("A" & Rows.Count).End(3).Row)
- For i = 1 To UBound(arr)
- If Not d.exists(arr(i, 2)) Then
- d(arr(i, 2)) = Array(IIf(arr(i, 5) <> "", arr(i, 1), 0), IIf(arr(i, 5) <> "", 1, 0), IIf(arr(i, 6) <> "", arr(i, 1), 0), IIf(arr(i, 6) <> "", 1, 0), IIf(arr(i, 7) <> "", arr(i, 1), 0), IIf(arr(i, 7) <> "", 1, 0), arr(i, 1), 1)
- Else
- d(arr(i, 2)) = Array(d(arr(i, 2))(0) + IIf(arr(i, 5) <> "", arr(i, 1), 0), d(arr(i, 2))(1) + IIf(arr(i, 5) <> "", 1, 0), d(arr(i, 2))(2) + IIf(arr(i, 6) <> "", arr(i, 1), 0), d(arr(i, 2))(3) + IIf(arr(i, 6) <> "", 1, 0), d(arr(i, 2))(4) + IIf(arr(i, 7) <> "", arr(i, 1), 0), d(arr(i, 2))(5) + IIf(arr(i, 7) <> "", 1, 0), d(arr(i, 2))(6) + arr(i, 1), d(arr(i, 2))(7) + 1)
- End If
- Next
- Sheets("汇总表").[A2].Resize(d.Count) = Application.Transpose(d.keys)
- arr1 = d.items
- ReDim brr(0 To UBound(arr1), 1 To 4)
- For i = 0 To UBound(arr1)
- For j = 1 To 4
- If arr1(i)(2 * j - 1) <> 0 Then brr(i, j) = arr1(i)(2 * j - 2) / arr1(i)(2 * j - 1)
- Next
- Next
- Sheets("汇总表").[B2].Resize(d.Count, 4) = brr
- End Sub
复制代码 |