唉,我费了老半天的力气用VBA编程实现了这个M值(or R值)的求法。 Sub Mcomputing( )
Dim i, j, k, zero, class_start, class_end, ylesson As Integer
Dim n_stuall, n_lesson, n_class, classstu As Integer
Dim temp As Integer
Dim temp1, temp2 As Double
Dim lvl(5), n_lvl(6) As Integer
Dim srcdata, classdata As Range
Set srcdata = ActiveSheet.Range("A1").CurrentRegion
n_stuall = srcdata.Rows.Count - 1
n_lesson = srcdata.Columns.Count - 3
n_class = Application.WorksheetFunction.Max(Range("a2", Range("a2").Offset(n_stuall - 1, 0)))
lvl(0) = Round(n_stuall * 0.05, 0)
lvl(1) = Round(n_stuall * 0.15, 0)
lvl(2) = Round(n_stuall * 0.35, 0)
lvl(3) = Round(n_stuall * 0.6, 0)
lvl(4) = Round(n_stuall * 0.8, 0)
lvl(5) = Round(n_stuall * 0.9, 0)
For i = 0 To 6
n_lvl(i) = 0
Next i
For i = 1 To n_lesson
Cells(1, ((n_lesson + 3) + i)) = Cells(1, (3 + i)) & "名次 "
Cells(1, (2 * n_lesson + 5 + i)) = Cells(1, (3 + i)) & "R "
Next i Cells(2, n_lesson + 4).Select
ActiveCell.Formula = "=RANK(d2,d$2:d$400)"
Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(n_stuall - 1, 0)), Type:=xlFillDefault
Range(ActiveCell, ActiveCell.Offset(n_stuall - 1, 0)).Select
Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(n_stuall - 1, n_lesson - 1)), Type:=xlFillDefault
For i = 1 To n_class
Cells(i + 1, 2 * n_lesson + 5) = Str(i) + "班"
Next i
class_start = 2
For i = 1 To n_class
classstu = 0
For k = 1 To n_stuall
If Cells(k + 1, 1) = i Then
classstu = classstu + 1
End If
Next k
class_end = class_start + classstu - 1
For j = 1 To n_lesson
For temp = class_start To class_end
If Cells(temp, n_lesson + 3 + j) <= lvl(0) Then
n_lvl(0) = n_lvl(0) + 1
ElseIf Cells(temp, n_lesson + 3 + j) <= lvl(1) Then
n_lvl(1) = n_lvl(1) + 1
ElseIf Cells(temp, n_lesson + 3 + j) <= lvl(2) Then
n_lvl(2) = n_lvl(2) + 1
ElseIf Cells(temp, n_lesson + 3 + j) <= lvl(3) Then
n_lvl(3) = n_lvl(3) + 1
ElseIf Cells(temp, n_lesson + 3 + j) <= lvl(4) Then
n_lvl(4) = n_lvl(4) + 1
ElseIf Cells(temp, n_lesson + 3 + j) <= lvl(5) Then
n_lvl(5) = n_lvl(5) + 1
Else
n_lvl(6) = n_lvl(6) + 1
End If
Next temp
temp1 = n_lvl(0) * 18 + n_lvl(1) * 14 + n_lvl(2) * 10 + n_lvl(3) * 6 + n_lvl(4) * 2 - n_lvl(5) * 8 - n_lvl(6)
Cells(i + 1, (2 * n_lesson + 5 + j)) = temp1 / (n_lvl(0) + n_lvl(1) + n_lvl(2) + n_lvl(3) + n_lvl(4) + n_lvl(5) + n_lvl(6))
For zero = 0 To 6
n_lvl(zero) = 0
Next zero
Next j
class_start = class_end + 1
Next i
Columns(Chr(Asc("A") + 2 * n_lesson + 5) & ":X").Select
Selection.NumberFormatLocal = "0.000"
ActiveSheet.Cells.Select
Selection.Columns.AutoFit
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With
With Selection.Font
.Size = 12
End With
End Sub
|