|
- Sub test()
- Dim x As Integer, arr, m As Integer, brr, yx, lh, jg, bjg, wcs, k
- Set d = CreateObject("scripting.dictionary")
- m = Sheets("高三年级").Range("e65536").End(3).Row
- arr = Sheets("高三年级").Range("e1:z" & m)
- For x = 2 To UBound(arr)
- If arr(x, 1) <> "" Then
- d(arr(x, 1)) = 1
- End If
- Next
- brr = d.keys
- With Sheets("表3")
- .Range("a6").Resize(UBound(brr) + 1, 1) = Application.Transpose(brr)
- crr = .Range("a6:q" & Sheets("表3").Range("a65536").End(3).Row)
- yx = 0
- lh = 0
- jg = 0
- bjg = 0
- wcs = 0
- k = 0
- For x = 1 To UBound(crr)
- For y = 1 To UBound(arr)
- If crr(x, 1) = arr(y, 1) Then
- crr(x, 2) = arr(y, 2)
- crr(x, 3) = arr(y, 3)
- k = k + 1
- If arr(y, 22) = "优秀" Then yx = yx + 1
- If arr(y, 22) = "良好" Then lh = lh + 1
- If arr(y, 22) = "及格" Then jg = jg + 1
- If arr(y, 22) = "不及格" Then bjg = bjg + 1
- If arr(y, 22) = "未测试" Then wcs = wcs + 1
- End If
- Next
- .Cells(x + 5, 1) = crr(x, 1)
- .Cells(x + 5, 2) = crr(x, 2)
- .Cells(x + 5, 3) = crr(x, 3)
- .Cells(x + 5, 4) = k
- .Cells(x + 5, 5) = yx
- .Cells(x + 5, 6) = yx / k
- .Cells(x + 5, 6).NumberFormatLocal = "0.00%"
- .Cells(x + 5, 7) = lh
- .Cells(x + 5, 8) = lh / k
- .Cells(x + 5, 8).NumberFormatLocal = "0.00%"
- .Cells(x + 5, 9) = jg
- .Cells(x + 5, 10) = jg / k
- .Cells(x + 5, 10).NumberFormatLocal = "0.00%"
- .Cells(x + 5, 11) = bjg
- .Cells(x + 5, 12) = bjg / k
- .Cells(x + 5, 12).NumberFormatLocal = "0.00%"
- .Cells(x + 5, 13) = bjg
- .Cells(x + 5, 14) = bjg / k
- .Cells(x + 5, 14).NumberFormatLocal = "0.00%"
- .Cells(x + 5, 15) = yx + lh + jg
- .Cells(x + 5, 16) = (yx + lh + jg) / k
- .Cells(x + 5, 16).NumberFormatLocal = "0.00%"
- yx = 0
- lh = 0
- jg = 0
- bjg = 0
- wcs = 0
- k = 0
- Next
- End With
- End Sub
复制代码 请测试,看看是不是这样?
|
|