- Sub Macro1()
- Dim arr, brr, crr, d, i&, j%, s&, s2&
- Set d = CreateObject("scripting.dictionary")
- arr = Sheet3.[f2:g84]
- brr = Sheet2.[a3:t3]
- w = Array(0, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120, 130, 140)
- w2 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15)
- ReDim crr(1 To 20000, 1 To UBound(brr, 2))
- For i = 1 To UBound(arr)
- If Not d.exists(arr(i, 1)) Then
- s = s + 2
- d(arr(i, 1)) = s
- crr(s - 1, 1) = arr(i, 1)
- crr(s - 1, 2) = "人数"
- crr(s, 2) = "累加"
- crr(s - 1, 18) = arr(i, 2)
- crr(s - 1, 19) = arr(i, 2)
- End If
- s2 = d(arr(i, 1))
- n = 18 - Application.Lookup(arr(i, 2), w, w2)
- crr(s2 - 1, n) = crr(s2 - 1, n) + 1
- If arr(i, 2) > crr(s2 - 1, 18) Then crr(s2 - 1, 18) = arr(i, 2)
- If arr(i, 2) < crr(s2 - 1, 19) Then crr(s2 - 1, 19) = arr(i, 2)
- crr(s2 - 1, 20) = crr(s2 - 1, 20) + 1
- If n = 3 Then crr(s2, n) = crr(s2 - 1, n)
- Next
- For i = 1 To s - 1 Step 2
- For j = 4 To 17
- crr(i + 1, j) = crr(i + 1, j - 1) + crr(i, j)
- Next
- Next
- Sheet2.Range("a4").Resize(s, UBound(crr, 2)) = crr
- End Sub
复制代码 |