这样问题应该用透视表,拖几下的事,唉,代码写的我自己都累- Sub test()
- Dim i%, j%, arrData, arrResult, n%, k%, arrT, x%, y%
- Dim dicx As Object, dicy As Object, d
- Set dicx = CreateObject("scripting.dictionary")
- Set dicy = CreateObject("scripting.dictionary")
- arrData = Range("a1").CurrentRegion.Value
- ReDim arrResult(1 To UBound(arrData), 1 To 10)
- ReDim arrT(1 To UBound(arrData), 1 To 2)
- arrResult(1, 1) = "Date"
- arrT(1, 1) = "Deal Number of customers": arrT(1, 2) = "Deal Amount"
- k = 1: n = 1
- For i = 2 To UBound(arrData)
- If Not dicy.exists(arrData(i, 4)) Then
- k = k + 1
- dicy(arrData(i, 4)) = k
- arrResult(1, k) = arrData(i, 4)
- End If
- y = dicy(arrData(i, 4))
- d = Format(arrData(i, 1), "m/yyyy")
- If Not dicx.exists(d) Then
- n = n + 1
- dicx(d) = n
- arrResult(n, 1) = d
- End If
- x = dicx(d)
- arrResult(x, y) = arrResult(x, y) + 1
- arrT(x, 2) = arrData(i, 3) + arrT(x, 2)
- arrT(x, 1) = 1 + arrT(x, 1)
- Next i
- Range("n1").Resize(Rows.Count, k + 2).ClearContents
- Range("n1").Resize(n, k) = arrResult
- Range("n1").Offset(, k).Resize(n, 2) = arrT
- End Sub
复制代码 |