- Sub test()
- Dim r%, i%
- Dim arr, brr
- Dim d As Object
- Set d = CreateObject("scripting.dictionary")
- With Worksheets("设置")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a2:g" & r)
- vs = [{"专科","本科","重点","前X名","第一名"}]
- End With
- For i = 2 To UBound(arr)
- If Not d.exists(arr(i, 1)) Then
- Set d(arr(i, 1)) = CreateObject("scripting.dictionary")
- End If
- Select Case arr(i, 2)
- Case "物理", "政治"
- km = "物政"
- Case "化学", "历史"
- km = "化历"
- Case "生物", "地理"
- km = "生地"
- Case "理数", "文数"
- km = "数学"
- Case "理综", "文综"
- km = "综合"
- Case Else
- km = arr(i, 2)
- End Select
- d(arr(i, 1))(km) = Array(arr(i, 7), arr(i, 6), arr(i, 5), arr(i, 4), arr(i, 3))
- Next
- With Worksheets("成绩表")
- r = .Cells(.Rows.Count, 3).End(xlUp).Row
- .Range("o4:ag" & r).ClearContents
- arr = .Range("a2:ag" & r)
- For i = 3 To UBound(arr)
- If d.exists(arr(i, 3)) Then
- For j = 7 To 14
- If d(arr(i, 3)).exists(arr(1, j)) Then
- brr = d(arr(i, 3))(arr(1, j))
- n = Application.Match(arr(i, j), brr)
- If Not IsError(n) Then
- If j = 14 Then
- arr(i, 17) = vs(n)
- Else
- arr(i, j * 2 + 7) = vs(n)
- End If
- End If
- End If
- Next
- End If
- Next
- For j = 7 To 14
- d.RemoveAll
- For i = 3 To UBound(arr)
- If Len(arr(i, j)) <> 0 Then
- If Not d.exists(arr(i, 3)) Then
- Set d(arr(i, 3)) = CreateObject("scripting.dictionary")
- End If
- d(arr(i, 3))(arr(i, j)) = d(arr(i, 3))(arr(i, j)) + 1
- End If
- Next
- For Each aa In d.keys
- nn = 1
- kk = d(aa).keys
- For k = 0 To UBound(kk)
- mm = Application.Large(kk, k + 1)
- ss = d(aa)(mm)
- d(aa)(mm) = nn
- nn = nn + ss
- Next
- Next
- For i = 3 To UBound(arr)
- If Len(arr(i, j)) <> 0 Then
- If j = 14 Then
- arr(i, 15) = d(arr(i, 3))(arr(i, j))
- Else
- arr(i, j * 2 + 6) = d(arr(i, 3))(arr(i, j))
- End If
- End If
- Next
- Next
- d.RemoveAll
- For i = 3 To UBound(arr)
- If Len(arr(i, 14)) <> 0 Then
- If Not d.exists(arr(i, 3)) Then
- Set d(arr(i, 3)) = CreateObject("scripting.dictionary")
- End If
- If Not d(arr(i, 3)).exists(arr(i, 5)) Then
- Set d(arr(i, 3))(arr(i, 5)) = CreateObject("scripting.dictionary")
- End If
- d(arr(i, 3))(arr(i, 5))(arr(i, 14)) = d(arr(i, 3))(arr(i, 5))(arr(i, 14)) + 1
- End If
- Next
- For Each aa In d.keys
- For Each bb In d(aa).keys
- nn = 1
- kk = d(aa)(bb).keys
- For k = 0 To UBound(kk)
- mm = Application.Large(kk, k + 1)
- ss = d(aa)(bb)(mm)
- d(aa)(bb)(mm) = nn
- nn = nn + ss
- Next
- Next
- Next
- For i = 3 To UBound(arr)
- If Len(arr(i, 14)) <> 0 Then
- arr(i, 16) = d(arr(i, 3))(arr(i, 5))(arr(i, 14))
- End If
- Next
- .Range("a2").Resize(UBound(arr), UBound(arr, 2)) = arr
- End With
- End Sub
复制代码 |