|
![](https://clubstatic.excelhome.net/image/common/ico_lz.png)
楼主 |
发表于 2024-11-19 17:59
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Sub 统计整月()
'Dim Sht As Worksheet
Dim arr, brr, crr, ar, br(1 To 100000, 1 To 20)
Dim dic
Dim d, d1, d2
Set d = CreateObject("scripting.dictionary")
Set d1 = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
Set dic = CreateObject("scripting.dictionary")
m = 2
For aa = 6 To Sheets.Count
irow = Sheets(aa).Range("a65536").End(3).Row
arr = Sheets(aa).Range("a1:n" & irow)
arr(2, 3) = "早"
For i = 3 To UBound(arr)
For j = 3 To UBound(arr, 2)
If Len(arr(i, j)) Then
If Left(arr(i, j), 1) = 1 Then
arr(i, j) = "高一"
ElseIf Left(arr(i, j), 1) = 2 Then
arr(i, j) = "高二"
ElseIf Left(arr(i, j), 1) = 3 Then
arr(i, j) = "高三"
End If
dic(arr(i, j) & "|" & arr(2, j)) = dic(arr(i, j) & "|" & arr(2, j)) + 1
End If
Next
Next
ar = Sheets(aa).Range("a1:n" & irow)
For n = 4 To 11
ar(2, n) = "课"
Next
For x = 3 To UBound(ar)
If Not d1.exists(ar(x, 1) & "|" & ar(x, 2) & "|" & x) Then
d1(ar(x, 1) & "|" & ar(x, 2) & "|" & x) = d1.Count + 1
br(d1(ar(x, 1) & "|" & ar(x, 2) & "|" & x), 1) = ar(x, 1)
br(d1(ar(x, 1) & "|" & ar(x, 2) & "|" & x), 2) = ar(x, 2)
End If
For y = 3 To 14
If Not d2.exists(ar(2, y)) Then
m = m + 1
d2(ar(2, y)) = m
End If
If Len(ar(x, y)) > 0 Then
br(d1(ar(x, 1) & "|" & ar(x, 2) & "|" & x), d2(ar(2, y))) = br(d1(ar(x, 1) & "|" & ar(x, 2) & "|" & x), d2(ar(2, y))) + 1
End If
Next
Next
Next
Sheets("功能表").[q3:ai10000].ClearContents
brr = Sheets("功能表").[p1].CurrentRegion
For x = 3 To UBound(brr)
For y = 2 To UBound(brr, 2)
If dic.exists(brr(x, 1) & "|" & brr(2, y)) Then
brr(x, y) = dic(brr(x, 1) & "|" & brr(2, y))
End If
Next
Next
Sheets("功能表").[p1].CurrentRegion = brr
Sheets("功能表").[af2].Resize(1, d2.Count) = d2.keys
Sheets("功能表").[ad3].Resize(d1.Count, UBound(br, 2)) = br
MsgBox "当月数据已统计完成", 48, "提示:"
End Sub
|
|