统计表中没有 西安, 就当你不打算统计它
- Sub main()
- Dim i As Long
- Dim m As Long, n As Long
- Dim d As Object
- Dim Arr, Res1, Res2
-
- Arr = Sheets("数据").Range("A1").CurrentRegion
- Set d = CreateObject("scripting.dictionary")
- Range("B2:F5").Clear
- Range("B8:F10").Clear
- Res1 = Range("B1:F5")
- Res2 = Range("B7:F10")
- For i = 1 To 4
- d(Res1(1, i)) = i
- Next i
- For i = 2 To UBound(Arr)
- If d.exists(Arr(i, 1)) = True Then
- Select Case Arr(i, 3)
- Case 1, 2, 3:
- m = Arr(i, 3)
- Case Else:
- m = 4
- End Select
- n = d(Arr(i, 1))
- Res1(m + 1, n) = Res1(m + 1, n) + Arr(i, 2)
- Res1(m + 1, 5) = Res1(m + 1, 5) + Arr(i, 2)
- Select Case Arr(i, 4)
- Case Is < 500:
- m = 2
- Case Is < 1000:
- m = 3
- Case Is < 1500:
- m = 4
- End Select
- Res2(m, n) = Res2(m, n) + Arr(i, 2)
- Res2(m, 5) = Res2(m, 5) + Arr(i, 2)
- End If
- Next i
- Range("B1").Resize(5, 5) = Res1
- Range("B7").Resize(4, 5) = Res2
- End Sub
复制代码 |