- Sub 字典奖金汇总()
- Dim d As Object, r%, i%, arr, brr1, brr2, mkey, jd%, srr, r1&, r2&
- tt = Timer
- jd = Sheet2.[B3].Value
- arr = Sheet1.[A1].CurrentRegion
- Set d = CreateObject("scripting.dictionary")
- For i = 2 To UBound(arr)
- If Month(arr(i, 1)) >= jd * 3 - 2 And Month(arr(i, 1)) <= jd * 3 Then
- d(arr(i, 2)) = d(arr(i, 2)) & "++" & i
- d(arr(i, 2) & "++" & arr(i, 3)) = d(arr(i, 2) & "++" & arr(i, 3)) & "++" & i
- End If
- Next
- ReDim brr1(1 To d.Count, 1 To 4): ReDim brr2(1 To d.Count, 1 To 3)
- r1 = 1: r2 = 1
- For Each mkey In d.keys
- If InStr(mkey, "++") > 0 Then
- '处理1
- srr = Split(d(mkey), "++")
- brr1(r1, 1) = Split(mkey, "++")(0)
- brr1(r1, 2) = Split(mkey, "++")(1)
- For i = 1 To UBound(srr)
- brr1(r1, 3) = brr1(r1, 3) + arr(srr(i), 4)
- brr1(r1, 4) = brr1(r1, 4) + jianjin(Val(arr(srr(i), 4)))
- Next
- r1 = r1 + 1
- Else
- '处理2
- brr2(r2, 1) = mkey
- srr = Split(d(mkey), "++")
- For i = 1 To UBound(srr)
- brr2(r2, 2) = brr2(r2, 2) + arr(srr(i), 4)
- brr2(r2, 3) = brr2(r2, 3) + jianjin(Val(arr(srr(i), 4)))
- Next
- r2 = r2 + 1
- End If
- Next
- With Sheet2
- .Range("a5").Resize(10000, 4).Clear
- .Range("a5").Resize(r2, 3) = brr2
- .Range("a" & r2 + 10).Resize(r1, 4) = brr1
- End With
- End Sub
- Function jianjin(ms#) As Double
- If ms >= 100000 Then
- jianjin = 2000
- ElseIf ms >= 50000 Then
- jianjin = 1000
- ElseIf ms >= 10000 Then
- jianjin = 500
- Else
- jianjin = 0
- End If
- End Function
复制代码 |