'dim
Dim Subject As String
Dim StudentAll As Integer
Dim student As Integer
Dim i As Integer
Dim j As Integer
Dim item As Integer
Dim Worksheet As Worksheet
Dim totalscore As Integer
Dim rankline As Integer
Dim rank As Integer
Dim databeginline As Integer
Dim classtotalscore As Double
Dim classavgscore As Double
Dim highestscore As Double
Dim lowestscore As Double
Dim temp As Integer
Dim lastrank As Integer
Dim goodpercent As Double
Dim passpercent As Double
Dim head As Integer
Dim tail As Integer
Dim biaozhuncha As Double
Dim biaozhunchasum As Double
' init
Set Worksheet = Worksheets("Sheet2")
StudentAll = 0
student = 0
totalscore = 0
'count StudentAll
i = 4
Do While Worksheet.Cells(i, 1) <> ""
StudentAll = StudentAll + 1
If Worksheet.Cells(i, 3) <> "*" Then
student = student + 1
End If
i = i + 1
Loop
'count totalscore
i = 3
Do While Worksheet.Cells(3, i) <> "" And Worksheet.Cells(3, i) < 60
totalscore = totalscore + Worksheet.Cells(3, i)
i = i + 1
Loop
Worksheet.Cells(3, i).Value = totalscore
item = i - 3
Worksheet.Cells(3 - 1, i).Value = "TotalScore"
i = 4
j = 3
Do While i <= StudentAll + 3
If Worksheet.Cells(i, 3).Value = "*" Then
Worksheet.Cells(i, item + 3).Value = "*"
Else
j = 3
Worksheet.Cells(i, item + 3).Value = totalscore
Do While j <= item + 2
If Worksheet.Cells(i, j).Value <> "" Then
Worksheet.Cells(i, item + 3).Value = Worksheet.Cells(i, item + 3).Value - Worksheet.Cells(i, j).Value
End If
j = j + 1
Loop
End If
i = i + 1
Loop
'count rank
rankline = item + 4
i = 4
j = 4
Do While i <= StudentAll + 3
If Worksheet.Cells(i, 3).Value = "*" Then
Worksheet.Cells(i, rankline).Value = "*"
Else
j = 4
rank = 1
Do While j <= StudentAll + 3
If Worksheet.Cells(j, rankline - 1).Value = "*" Then
Worksheet.Cells(j, rankline).Value = "*"
Else
If Worksheet.Cells(j, rankline - 1).Value > Worksheet.Cells(i, rankline - 1).Value Then
rank = rank + 1
Else
End If
End If
j = j + 1
Loop
End If
Worksheet.Cells(i, rankline).Value = rank
i = i + 1
Loop
Worksheet.Cells(2, rankline).Value = "rank"
'count databeginline *
i = 1
Do While Worksheet.Cells(i, 1).Value <> "*"
i = i + 1
Loop
databeginline = i
'averge
i = 4
classtotalscore = 0
classavgscore = 0
Do While i <= StudentAll + 3
If Worksheet.Cells(i, 3).Value = "*" Then
Else
classtotalscore = classtotalscore + Worksheet.Cells(i, rankline - 1).Value
End If
i = i + 1
Loop
classavgscore = classtotalscore / student
'count biaozhuncha
biaozhunchasum = 0
biaozhuncha = 0
i = 4
Do While i <= StudentAll + 3
If Worksheet.Cells(i, 3).Value = "*" Then
Else
biaozhunchasum = biaozhunchasum + (Worksheet.Cells(i, rankline - 1).Value - classavgscore) * (Worksheet.Cells(i, rankline - 1).Value - classavgscore)
End If
i = i + 1
Loop
biaozhuncha = Sqr(biaozhunchasum / (student - 1))
'highestscore lowestscore
highestscore = 0
lowestscore = 0
temp = 1
i = 4
Do While i <= StudentAll + 3
Select Case Worksheet.Cells(i, rankline).Value
Case "*"
Case "1"
highestscore = Worksheet.Cells(i, rankline - 1).Value
Case Is > temp
temp = Worksheet.Cells(i, rankline).Value
lowestscore = Worksheet.Cells(i, rankline - 1).Value
End Select
i = i + 1
Loop
'goodpercent passpercent
i = 4
goodpercent = 0
passpercent = 0
Do While i <= StudentAll + 3
If Worksheet.Cells(i, 3).Value = "*" Then
Else
Select Case Worksheet.Cells(i, rankline - 1).Value
Case Is >= 80
goodpercent = goodpercent + 1
passpercent = passpercent + 1
Case Is >= 60
passpercent = passpercent + 1
End Select
End If
i = i + 1
Loop
'put data
Worksheet.Cells(databeginline + 2, 1).Value = StudentAll
Worksheet.Cells(databeginline + 2, 2).Value = classtotalscore
Worksheet.Cells(databeginline + 2, 3).Value = highestscore
Worksheet.Cells(databeginline + 2, 4).Value = CStr(goodpercent / student * 100) + "%"
Worksheet.Cells(databeginline + 2, 5).Value = student
Worksheet.Cells(databeginline + 2, 6).Value = classavgscore
Worksheet.Cells(databeginline + 2, 7).Value = lowestscore
Worksheet.Cells(databeginline + 2, 8).Value = CStr(passpercent / student * 100) + "%"
Worksheet.Cells(databeginline + 4, 1).Value = StudentAll - student
Worksheet.Cells(databeginline + 4, 2).Value = biaozhuncha
Worksheet.Cells(databeginline + 4, 3).Value = highestscore - lowestscore
Worksheet.Cells(databeginline + 4, 4).Value = biaozhuncha / classavgscore
'count 100-120 100 90-99.....
Worksheet.Cells(databeginline + 8, 2).Value = 0
Worksheet.Cells(databeginline + 8, 3).Value = 0
Worksheet.Cells(databeginline + 8, 4).Value = 0
Worksheet.Cells(databeginline + 8, 5).Value = 0
Worksheet.Cells(databeginline + 8, 6).Value = 0
Worksheet.Cells(databeginline + 8, 7).Value = 0
Worksheet.Cells(databeginline + 8, 8).Value = 0
Worksheet.Cells(databeginline + 8, 9).Value = 0
Worksheet.Cells(databeginline + 8, 10).Value = 0
Worksheet.Cells(databeginline + 8, 11).Value = 0
i = 4
Do While i <= StudentAll + 3
If Worksheet.Cells(i, 3).Value = "*" Then
Else
Select Case Worksheet.Cells(i, rankline - 1).Value
Case Is > 100
Worksheet.Cells(databeginline + 8, 2).Value = Worksheet.Cells(databeginline + 8, 2).Value + 1
Case 100
Worksheet.Cells(databeginline + 8, 3).Value = Worksheet.Cells(databeginline + 8, 3).Value + 1
Case Is > 90
Worksheet.Cells(databeginline + 8, 4).Value = Worksheet.Cells(databeginline + 8, 4).Value + 1
Case Is > 80
Worksheet.Cells(databeginline + 8, 5).Value = Worksheet.Cells(databeginline + 8, 5).Value + 1
Case Is > 70
Worksheet.Cells(databeginline + 8, 6).Value = Worksheet.Cells(databeginline + 8, 6).Value + 1
Case Is > 60
Worksheet.Cells(databeginline + 8, 7).Value = Worksheet.Cells(databeginline + 8, 7).Value + 1
Case Is > 50
Worksheet.Cells(databeginline + 8, 8).Value = Worksheet.Cells(databeginline + 8, 8).Value + 1
Case Is > 40
Worksheet.Cells(databeginline + 8, 9).Value = Worksheet.Cells(databeginline + 8, 9).Value + 1
Case Is > 30
Worksheet.Cells(databeginline + 8, 10).Value = Worksheet.Cells(databeginline + 8, 10).Value + 1
Case Is < 20
Worksheet.Cells(databeginline + 8, 11).Value = Worksheet.Cells(databeginline + 8, 11).Value + 1
End Select
End If
i = i + 1
Loop
Worksheet.Cells(databeginline + 9, 2).Value = CStr(Worksheet.Cells(databeginline + 8, 2).Value / student * 100) + "%"
Worksheet.Cells(databeginline + 9, 3).Value = CStr(Worksheet.Cells(databeginline + 8, 3).Value / student * 100) + "%"
Worksheet.Cells(databeginline + 9, 4).Value = CStr(Worksheet.Cells(databeginline + 8, 4).Value / student * 100) + "%"
Worksheet.Cells(databeginline + 9, 5).Value = CStr(Worksheet.Cells(databeginline + 8, 5).Value / student * 100) + "%"
Worksheet.Cells(databeginline + 9, 6).Value = CStr(Worksheet.Cells(databeginline + 8, 6).Value / student * 100) + "%"
Worksheet.Cells(databeginline + 9, 7).Value = CStr(Worksheet.Cells(databeginline + 8, 7).Value / student * 100) + "%"
Worksheet.Cells(databeginline + 9, 8).Value = CStr(Worksheet.Cells(databeginline + 8, 8).Value / student * 100) + "%"
Worksheet.Cells(databeginline + 9, 9).Value = CStr(Worksheet.Cells(databeginline + 8, 9).Value / student * 100) + "%"
Worksheet.Cells(databeginline + 9, 10).Value = CStr(Worksheet.Cells(databeginline + 8, 10).Value / student * 100) + "%"
Worksheet.Cells(databeginline + 9, 11).Value = CStr(Worksheet.Cells(databeginline + 8, 11).Value / student * 100) + "%"
'count 100-85 100 84-78.....
Worksheet.Cells(databeginline + 14, 2).Value = 0
Worksheet.Cells(databeginline + 14, 3).Value = 0
Worksheet.Cells(databeginline + 14, 4).Value = 0
Worksheet.Cells(databeginline + 14, 5).Value = 0
i = 4
Do While i <= StudentAll + 3
If Worksheet.Cells(i, 3).Value = "*" Then
Else
Select Case Worksheet.Cells(i, rankline - 1).Value
Case Is > 85
Worksheet.Cells(databeginline + 14, 2).Value = Worksheet.Cells(databeginline + 14, 2).Value + 1
Case Is > 78
Worksheet.Cells(databeginline + 14, 3).Value = Worksheet.Cells(databeginline + 14, 3).Value + 1
Case Is > 60
Worksheet.Cells(databeginline + 14, 4).Value = Worksheet.Cells(databeginline + 14, 4).Value + 1
Case Is < 60
Worksheet.Cells(databeginline + 14, 5).Value = Worksheet.Cells(databeginline + 14, 5).Value + 1
End Select
End If
i = i + 1
Loop
Worksheet.Cells(databeginline + 15, 2).Value = CStr(Worksheet.Cells(databeginline + 14, 2).Value / student * 100) + "%"
Worksheet.Cells(databeginline + 15, 3).Value = CStr(Worksheet.Cells(databeginline + 14, 3).Value / student * 100) + "%"
Worksheet.Cells(databeginline + 15, 4).Value = CStr(Worksheet.Cells(databeginline + 14, 4).Value / student * 100) + "%"
Worksheet.Cells(databeginline + 15, 5).Value = CStr(Worksheet.Cells(databeginline + 14, 5).Value / student * 100) + "%"
'top 5
If student < 5 Then
MsgBox "if you want to know the top 5/last 5 ,You should input more then 5 score"
Else
temp = 1
head = 1
Do While temp <= 5
i = 4
Do While i <= StudentAll + 3
If Worksheet.Cells(i, rankline).Value = head Then
Worksheet.Cells(databeginline + 18 + temp, 1).Value = head
Worksheet.Cells(databeginline + 18 + temp, 2).Value = Worksheet.Cells(i, 2).Value
Worksheet.Cells(databeginline + 18 + temp, 3).Value = Worksheet.Cells(i, rankline - 1).Value
temp = temp + 1
If temp > 5 Then
GoTo labeltop5end
End If
End If
i = i + 1
Loop
head = head + 1
Loop
End If
labeltop5end:
'last 5
If student < 5 Then
MsgBox "if you want to know the top 5/last 5 ,You should input more then 5 score"
Else
temp = 1
tail = student
Do While temp <= 5
i = 4
Do While i <= StudentAll + 3
If Worksheet.Cells(i, rankline).Value = tail Then
Worksheet.Cells(databeginline + 18 + temp, 5).Value = tail
Worksheet.Cells(databeginline + 18 + temp, 6).Value = Worksheet.Cells(i, 2).Value
Worksheet.Cells(databeginline + 18 + temp, 7).Value = Worksheet.Cells(i, rankline - 1).Value
temp = temp + 1
If temp > 5 Then
GoTo labellast5end
End If
End If
i = i + 1
Loop
tail = tail - 1
Loop
End If
labellast5end:
'every question score
i = 3
Do While i <= item + 2
Worksheet.Cells(StudentAll + 4, i).Value = 0
j = 4
Do While j <= StudentAll + 3
If Worksheet.Cells(j, i).Value <> "*" And Worksheet.Cells(j, i).Value <> "" Then
Worksheet.Cells(StudentAll + 4, i).Value = Worksheet.Cells(StudentAll + 4, i).Value + Worksheet.Cells(j, i).Value
End If
j = j + 1
Loop
Worksheet.Cells(StudentAll + 5, i).Value = Worksheet.Cells(3, i).Value * student - Worksheet.Cells(StudentAll + 4, i).Value
Worksheet.Cells(databeginline + 30, i - 1).Value = Worksheet.Cells(3, i).Value * student
Worksheet.Cells(databeginline + 31, i - 1).Value = Worksheet.Cells(3, i).Value * student - Worksheet.Cells(StudentAll + 4, i).Value
Worksheet.Cells(databeginline + 32, i - 1).Value = CStr(Format(Worksheet.Cells(databeginline + 31, i - 1).Value / Worksheet.Cells(databeginline + 30, i - 1).Value * 100, "0.00")) + "%"
Worksheet.Cells(databeginline + 33, i - 1).Value = Worksheet.Cells(3, i).Value * 0.6
i = i + 1
Loop
i = 3
Do While i <= item + 2
Worksheet.Cells(databeginline + 34, i - 1).Value = 0
Worksheet.Cells(databeginline + 35, i - 1).Value = "0%"
j = 4
Do While j <= StudentAll + 3
If Worksheet.Cells(j, i).Value <> "*" And Worksheet.Cells(j, i).Value <> "" Then
If Worksheet.Cells(3, i).Value - Worksheet.Cells(j, i).Value < Worksheet.Cells(3, i).Value * 0.6 Then
Worksheet.Cells(databeginline + 34, i - 1).Value = Worksheet.Cells(databeginline + 34, i - 1).Value + 1
Worksheet.Cells(databeginline + 35, i - 1).Value = CStr(Worksheet.Cells(databeginline + 34, i - 1).Value / student * 100) + "%"
End If
End If
j = j + 1
Loop
i = i + 1
Loop
'support
i = 4
Do While i <= StudentAll + 3
If Worksheet.Cells(i, 3).Value = "*" Then
Else
Worksheet.Cells(i, rankline + 1).Value = Format(classavgscore - ((classtotalscore - Worksheet.Cells(i, rankline - 1).Value) / (student - 1)), "0.00")
End If
i = i + 1
Loop
Worksheet.Cells(2, rankline + 1) = "support"
'''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''
Subject = Worksheets("Sheet2").Cells(1, 1).Value
MsgBox "success"
End Sub
Private Sub CommandButton2_Click()
'dim
Dim i As Integer
Dim j As Integer
'copy
i = 1
j = 1
Do While i < 200
j = 1
Do While j < 50
Worksheets("Sheet2").Cells(i, j).Value = Worksheets("Sheet1").Cells(i, j).Value
j = j + 1
Loop
i = i + 1
Loop
End Sub
''''''''''''''''''''''''''''''''
Sub clear()
'
' clear Macro
' clear sheet2
'
'
Sheets("Sheet2").Select
Cells.Select
Range("L20").Activate
Selection.ClearContents
Range("P2").Select
Selection.copy
Cells.Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Sheets("Sheet1").Select
End Sub
Sub copystyle()
'
' copystyle Macro
' 儅僋儘婰榐擔 : 2011/11/4 儐乕僓乕柤 : huangyiqi
'
'
Cells.Select
Selection.copy
Sheets("Sheet2").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Sheet1").Select
Range("A1:K1").Select
End Sub