|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Sub test5()
- Dim r%, i%
- Dim arr, brr
- Dim d As Object
- Dim reg As New RegExp
- Set d = CreateObject("scripting.dictionary")
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- With reg
- .Global = False
- .Pattern = "(\d+)′(\d+)"
- End With
- For Each ws In Worksheets(Array("评分标准", "加分标准", "体重标准"))
- Set d(ws.Name) = CreateObject("scripting.dictionary")
- With ws
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- c = .Cells(4, .Columns.Count).End(xlToLeft).Column
- brr = .Range("a1").Resize(r, c)
- d(ws.Name)(1) = brr
- For j = 4 To UBound(brr, 2)
- If Len(brr(1, j)) <> 0 Then
- dx = brr(1, j)
- End If
- If Len(brr(2, j)) <> 0 Then
- xm = brr(2, j)
- End If
- If Len(brr(3, j)) <> 0 Then
- nj = brr(3, j)
- End If
- xx = nj & "+" & xm & "+" & brr(4, j)
- d(ws.Name)(xx) = Array(j, dx)
- Next
- End With
- Next
- With Worksheets("调整后")
- .AutoFilterMode = False
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- .Range("h2:h" & r).ClearContents
- .Range("o2:aj" & r).ClearContents
-
- arr = .Range("a1:aj" & r)
- brr = d("评分标准")(1)
- For i = 2 To UBound(arr)
- arr(i, 8) = Application.Round(arr(i, 7) / (arr(i, 6) / 100) ^ 2, 1)
- sj1 = 0
- sj2 = 0
- Set mh = reg.Execute(arr(i, 13))
- If mh.Count > 0 Then
- sj1 = Val(mh(0).SubMatches(0)) * 60 + Val(mh(0).SubMatches(1))
- End If
- xx = arr(i, 2) & "+长跑+" & arr(i, 5)
- If d("评分标准").exists(xx) Then
- crr = d("评分标准")(xx)
- Set mh = reg.Execute(brr(5, crr(0)))
- If mh.Count > 0 Then
- sj2 = Val(mh(0).SubMatches(0)) * 60 + Val(mh(0).SubMatches(1))
- End If
- If sj2 > sj1 Then
- arr(i, 29) = sj2 - sj1
- End If
- End If
- arr(i, 31) = arr(i, 14)
- Next
- For j = 9 To 14
- For i = 2 To UBound(arr)
- xx = arr(i, 2) & "+" & arr(1, j) & "+" & arr(i, 5)
- If d("评分标准").exists(xx) Then
- crr = d("评分标准")(xx)
- If crr(1) = "大" Then
- For k = 5 To UBound(brr)
- If arr(i, j) >= brr(k, crr(0)) Then
- arr(i, j * 2 - 1) = brr(k, 3)
- Exit For
- End If
- Next
- Else
- For k = 5 To UBound(brr)
- If arr(i, j) <= brr(k, crr(0)) Then
- arr(i, j * 2 - 1) = brr(k, 3)
- Exit For
- End If
- Next
- End If
- End If
- Next
- Next
- brr = d("体重标准")(1)
- For i = 2 To UBound(arr)
- xx = arr(i, 2) & "+体重指数+" & arr(i, 5)
- If d("体重标准").exists(xx) Then
- crr = d("体重标准")(xx)
- For k = 5 To UBound(brr)
- If arr(i, 8) >= brr(k, crr(0)) Then
- arr(i, 15) = brr(k, 3)
- Exit For
- End If
- Next
- End If
- Next
- brr = d("加分标准")(1)
- For j = 29 To 31 Step 2
- For i = 2 To UBound(arr)
- If Len(arr(i, j)) <> 0 And arr(i, j) <> 0 Then
- xx = arr(i, 2) & "+" & arr(1, j) & "+" & arr(i, 5)
- If d("加分标准").exists(xx) Then
- crr = d("加分标准")(xx)
- For k = 5 To UBound(brr)
- If arr(i, j) >= brr(k, crr(0)) Then
- arr(i, j + 1) = brr(k, 3)
- Exit For
- End If
- Next
- End If
- End If
- Next
- Next
- End With
- For i = 2 To UBound(arr)
- arr(i, 33) = arr(i, 15) * 0.15 + arr(i, 17) * 0.15 + arr(i, 19) * 0.2 + arr(i, 21) * 0.1 + arr(i, 23) * 0.1 + arr(i, 25) * 0.2 + arr(i, 27) * 0.1
- arr(i, 34) = arr(i, 30) + arr(i, 32)
- arr(i, 35) = arr(i, 33) + arr(i, 34)
- For Each y In Array(15, 17, 19, 21, 23, 25, 27, 35)
- Select Case arr(i, y)
- Case Is >= 90
- arr(i, y + 1) = "优秀"
- Case Is >= 80
- arr(i, y + 1) = "良好"
- Case Is >= 60
- arr(i, y + 1) = "及格"
- Case Else
- arr(i, y + 1) = "不及格"
- End Select
- Next
- Next
- With Worksheets("调整后")
- .Range("a1").Resize(UBound(arr), UBound(arr, 2)) = arr
- End With
- End Sub
复制代码 |
|