|
- Sub 条件求和()
- Dim r%, i%
- Dim arr, brr
- Dim d As Object
- Application.ScreenUpdating = False '工作表里面数据发生变化后False禁止实时刷新,True为默认值表示实时更新数据。
- Application.DisplayAlerts = False '屏蔽程序执行过程中出现的一些弹出框警告,设置为true就会显示弹出警告。
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- Set d3 = CreateObject("scripting.dictionary")
-
- With Worksheets("sheet1")
- rq1 = .Range("d2")
- rq2 = .Range("d3")
- End With
-
- With Worksheets("sheet4")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- c = .Cells(1, .Columns.Count).End(xlToLeft).Column
- arr = .Range("a2").Resize(r - 1, c)
- For i = 1 To UBound(arr)
- If arr(i, 3) = "A001" Then
- ReDim brr(1 To 17)
- brr(1) = arr(i, 7)
- xm = CStr(arr(i, 7)) '将括号内的数据转换为文本型,也就是string类型
- d(xm) = brr
- End If
- Next
- End With
-
- With Worksheets("sheet2")
- r = .Cells(.Rows.Count, 3).End(xlUp).Row
- c = .Cells(1, .Columns.Count).End(xlToLeft).Column
- arr = .Range("a2").Resize(r - 1, c)
- For i = 1 To UBound(arr)
- xm = CStr(arr(i, 3))
- If d.exists(xm) Then
- brr = d(xm)
- n = 0
- Select Case Mid(arr(i, 10), 2, 2) '取第10列第2个字符开始,取2个字符
- Case "QQ"
- n = 1
- Case "JJ"
- n = 2
- Case "XX"
- n = 3
- Case "TT"
- n = 4
- End Select
- If n <> 0 Then
- If Len(arr(i, 17)) < 5 Then '???Len():得到字符串的长度???
- brr(n + 1) = brr(n + 1) + 1
- If arr(i, 16) >= rq1 And arr(i, 16) <= rq2 Then
- brr(n + 9) = (brr(n + 9) + 1) - .Cells(i, 15)
- End If
- Else
- brr(n + 5) = brr(n + 5) + 1
- End If
- End If
- d(xm) = brr
- End If
- Next
- End With
-
- With Worksheets("sheet3")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- c = .Cells(2, .Columns.Count).End(xlToLeft).Column
- arr = .Range("a3").Resize(r - 2, c)
- For i = 1 To UBound(arr)
- xm = CStr(arr(i, 7))
- If d.exists(xm) Then
- brr = d(xm)
- n = 0
- Select Case Mid(arr(i, 6), 2, 2)
- Case "QQ"
- n = 1
- Case "JJ"
- n = 2
- Case "XX"
- n = 3
- Case "TT"
- n = 4
- End Select
- If n <> 0 Then
- If Len(arr(i, 8)) < 5 Then
- If arr(i, 5) >= rq1 And arr(i, 5) <= rq2 Then
- brr(n + 13) = brr(n + 13) + 1
- End If
- End If
- End If
- d(xm) = brr
- End If
- Next
- End With
-
- t = d.items
- For x = 0 To d.Count - 1
- For i = 0 To 3
- t(x)(i + 10) = t(x)(i + 10) - t(x)(i + 14)
- Next
- Next
-
- With Worksheets("sheet1")
- .Range("a7:r" & .Rows.Count).Clear
- If d.Count > 0 Then
- With .Range("b7").Resize(d.Count, UBound(brr))
- ' .Value = Application.Transpose(Application.Transpose(d.items))
- .Value = Application.Transpose(Application.Transpose(t))
- .Borders.LineStyle = xlContinuous '区域单元格设置线
- End With
- End If
- .Select
- End With
-
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|