End If
If S1 = "" Or S3 = "" Then Worksheets("Лист2").Cells(k, 7).Value = " " Else Worksheets("Лист2").Cells(k, 7).Value = Str(Round(100 * Val(S3) / Val(S1))) & "%"
s10 = s10 + Val(S1)
s20 = s20 + Val(s2)
s30 = s30 + Val(S3)
s40 = s40 + Val(s4)
S50 = S50 + Val(s5)
End If
End If
Next j
k = k + 1
Worksheets("Лист2").Cells(k, 2).Value = "Итого"
Worksheets("Лист2").Cells(k, 3).Value = Str(s10) & "/" & Str(s20)
Worksheets("Лист2").Cells(k, 4).Value = Str(S50)
Worksheets("Лист2").Cells(k, 5).Value = Str(Round(100 * (s10 - S50) / s10)) & "%"
Worksheets("Лист2").Cells(k, 6).Value = Str(s30) & "(" & Str(s40) & ")"
Worksheets("Лист2").Cells(k, 7).Value = Str(Round(100 * s30 / s10)) & "%"
k = k + 1
If (i = 4) Or (i = 9) Or (i = 11) Then
Worksheets("Лист2").Cells(k, 2).Font.Bold = True
Worksheets("Лист2").Cells(k, 2).Value = "Итого по ступени"
Worksheets("Лист2").Cells(k, 3).Font.Bold = True
Worksheets("Лист2").Cells(k, 3).Value = S11 & "/" & s12
Worksheets("Лист2").Cells(k, 4).Font.Bold = True
Worksheets("Лист2").Cells(k, 4).Value = s15
Worksheets("Лист2").Cells(k, 5).Font.Bold = True
Worksheets("Лист2").Cells(k, 5).Value = Str(Round(A / KLASS)) & "%"
Worksheets("Лист2").Cells(k, 6).Font.Bold = True
Worksheets("Лист2").Cells(k, 6).Value = Str(s31) & "(" & Str(s41) & ")"
A = 0
KLASS = 0
s15 = 0
Worksheets("Лист2").Cells(k, 7).Font.Bold = True
Worksheets("Лист2").Cells(k, 7).Value = Str(Round(100 * s31 / S11)) & "%"
s31 = 0
s41 = 0
S11 = 0
k = k + 1
End If
s100 = s100 + s10
s200 = s200 + s20
s300 = s300 + s30
s400 = s400 + s40
s500 = s500 + S50
s10 = 0
s20 = 0
s30 = 0
s40 = 0
S50 = 0
Next i
k = k + 1
Worksheets("Лист2").Cells(k, 2).Value = "Итогополицею"
Worksheets("Лист2").Cells(k, 3).Value = Str(s100) & "/" & Str(s200)
Worksheets("Лист2").Cells(k, 4).Value = Str(s500)
Worksheets("Лист2").Cells(k, 5).Value = Str(Round(100 * (s100 - s500) / s100)) & "%"
Worksheets("Лист2").Cells(k, 6).Value = Str(s300) & "(" & Str(s400) & ")"
Worksheets("Лист2").Cells(k, 7).Value = Str(Round(100 * s300 / s100)) & "%"
Worksheets("Лист2").Cells(k + 3, 2).Value = "Директор экономического лицея"
Worksheets("Лист2").Cells(k + 3, 7).Value = "Н.И.Кузенко"
k = 2
Worksheets("Лист4").Cells(k, 2).Value = "Итогополицею"
Worksheets("Лист2").Cells(k, 3).Value = Str(s100) & "/" & Str(s200)
Worksheets("Лист2").Cells(k, 4).Value = Str(s500)
Worksheets("Лист2").Cells(k, 5).Value = Str(Round(100 * (s200 - s500) / s100)) & "%"
Worksheets("Лист2").Cells(k, 6).Value = Str(s300) & "(" & Str(s400) & ")"
Worksheets("Лист2").Cells(k, 7).Value = Str(Round(100 * s300 / s100)) & "%"
End Sub
Макрос «Неуспевающие »
PrivateSubCommandButton1_Click()
Dim s As String
For i = 1 To 4
For j = 8 To 150
Worksheets("Лист3").Cells(j, i).Value = ""
Next j
Next i
k = 1
j = 8
For i = 5 To 76
S1 = Worksheets("Лист1").Cells(i, 4).Value
s2 = Worksheets("Лист1").Cells(i, 5).Value
s = Worksheets("Лист1").Cells(i, 1).Value
If S1 <> "" Then S1 = S1 + ";"
t = 1
While t <= Len(s2)
If Mid(s2, t, 1) <> ":" Then
S1 = S1 + Mid(s2, t, 1)
Else
S1 = S1 + Mid(s2, t, 1) + " н/а "
End If
t = t + 1
Wend
If Len(S1) <> 0 Then
If Mid(S1, Len(S1), 1) <> ";" Then
S1 = S1 + ";"
End If
End If
While InStr(S1, ":") > 0
q = Mid(S1, 1, InStr(S1, ":") - 1)
S1 = Mid(S1, InStr(S1, ":") + 1, Len(S1))
n = Mid(S1, 1, InStr(S1, ";") - 1)
S1 = Mid(S1, InStr(S1, ";") + 1, Len(S1))
If InStr(S1, q) > 0 Then
n = n + ";" + Mid(Mid(Mid(S1, InStr(S1, q) - 1, Len(S1)), InStr(Mid(S1, InStr(S1, q) - 1, Len(S1)), ":") + 1, Len(S1)), 1, InStr(Mid(Mid(S1, InStr(S1, q) - 1, Len(S1)), InStr(Mid(S1, InStr(S1, q) - 1, Len(S1)), ":") + 1, Len(S1)), ";") - 1)
S1 = Mid(S1, 1, InStr(S1, q) - 1) + Mid(Mid(S1, InStr(S1, q), Len(S1)), InStr(Mid(S1, InStr(S1, q), Len(S1)), ";") + 1, Len(S1))
Worksheets("Лист3").Cells(j, 1).Value = k
Worksheets("Лист3").Cells(j, 2).Value = q
Worksheets("Лист3").Cells(j, 3).Value = s
Worksheets("Лист3").Cells(j, 4).Value = n
Else
Worksheets("Лист3").Cells(j, 1).Value = k
Worksheets("Лист3").Cells(j, 2).Value = q
Worksheets("Лист3").Cells(j, 3).Value = s
Worksheets("Лист3").Cells(j, 4).Value = n
End If
k = k + 1
j = j + 1
Wend
Next i
Worksheets("Лист3").Cells(j + 2, 2).Value = "Итого:"
Worksheets("Лист3").Cells(j + 2, 3).Value = Str(k - 1) + "чел."
Worksheets("Лист3").Cells(j + 4, 2).Value = "Директор "
Worksheets("Лист3").Cells(j + 5, 2).Value = "экономического лицея"
Worksheets("Лист3").Cells(j + 5, 4).Value = "Н.И.Кузенко"
End Sub
Макрос «Пропуски»
Private Sub CommandButton1_Click()
Dim s As String
For i = 3 To 150
For j = 1 To 5
Worksheets("Лист4").Cells(i, j).Value = ""
Next j
Next i
k = 3
For nk = 1 To 11
For i = 5 To 76
s = Worksheets("Лист1").Cells(i, 1).Value
If Len(s) <> 0 Then
If Len(s) > 1 Then r = 2 Else r = 1
If Val(Left(s, r)) = nk Then
Worksheets("Лист4").Cells(k, 1).Value = Worksheets("Лист1").Cells(i, 1).Value
Worksheets("Лист4").Cells(k, 2).Value = Worksheets("Лист1").Cells(i, 11).Value
S1 = S1 + Val(Worksheets("Лист1").Cells(i, 11).Value)
Worksheets("Лист4").Cells(k, 3).Value = Worksheets("Лист1").Cells(i, 12).Value
s2 = s2 + Val(Worksheets("Лист1").Cells(i, 12).Value)
Worksheets("Лист4").Cells(k, 4).Value = Worksheets("Лист1").Cells(i, 13).Value
S3 = S3 + Val(Worksheets("Лист1").Cells(i, 13).Value)
Worksheets("Лист4").Cells(k, 5).Value = Worksheets("Лист1").Cells(i, 14).Value
s4 = s4 + Val(Worksheets("Лист1").Cells(i, 14).Value)
k = k + 1
End If
End If
Next i
Worksheets("Лист4").Cells(k, 1).Value = "Итогопопарралели"
Worksheets("Лист4").Cells("a3:a66").Interior.Color = RGB(255, 255, 255)
Worksheets("Лист4").Cells(k, 1).Interior.Color = RGB(255, 255, 0)
Worksheets("Лист4").Cells(k - 1, 2).Interior.Color = RGB(255, 255, 255)
Worksheets("Лист4").Cells(k - 1, 3).Interior.Color = RGB(255, 255, 255)
Worksheets("Лист4").Cells(k - 1, 4).Interior.Color = RGB(255, 255, 255)
Worksheets("Лист4").Cells(k - 1, 5).Interior.Color = RGB(255, 255, 255)
Worksheets("Лист4").Cells(k, 2).Interior.Color = RGB(255, 255, 0)
Worksheets("Лист4").Cells(k, 3).Interior.Color = RGB(255, 255, 0)
Worksheets("Лист4").Cells(k, 4).Interior.Color = RGB(255, 255, 0)
Worksheets("Лист4").Cells(k, 5).Interior.Color = RGB(255, 255, 0)
Worksheets("Лист4").Cells(k, 2).Value = Str(S1)
Worksheets("Лист4").Cells(k, 3).Value = Str(s2)
Worksheets("Лист4").Cells(k, 4).Value = Str(S3)
Worksheets("Лист4").Cells(k, 5).Value = Str(s4)
S11 = S11 + S1
S22 = S22 + s2
S33 = S33 + S3
S44 = S44 + s4
S1 = 0
s2 = 0
S3 = 0
s4 = 0
k = k + 1
If (nk = 4) Or (nk = 9) Or (nk = 11) Then
Worksheets("Лист4").Cells(k, 1).Value = "Итого по ступени"
Worksheets("Лист4").Cells(k, 2).Value = Str(S11)
Worksheets("Лист4").Cells(k, 3).Value = Str(S22)
Worksheets("Лист4").Cells(k, 4).Value = Str(S33)
Worksheets("Лист4").Cells(k, 5).Value = Str(S44)
S11 = 0
S22 = 0
S33 = 0
S44 = 0
k = k + 1
End If
Next nk
End Sub
Приложение 3
Макрос «Выбыли»
Sub выбыли()
Dim s As String
For i = 6 To 76
Worksheets("Лист2").Cells(i, 2).Value = ""
Worksheets("Лист2").Cells(i, 3).Value = ""
Worksheets("Лист2").Cells(i, 4).Value = ""
Next i
j = 5
For i = 3 To 73
s = Worksheets("Лист1").Cells(i, 8).Value
s2 = Worksheets("Лист1").Cells(i, 2).Value
s1 = Trim(s)
If s1 <> "" Then
j = j + 1
While InStr(s1, ";") > 0
Worksheets("Лист2").Cells(j, 3).Value = Trim(Mid(s1, 1, InStr(s1, ";") - 1))
Worksheets("Лист2").Cells(j, 2).Value = j - 5
Worksheets("Лист2").Cells(j, 4).Value = s2
s1 = Mid(s1, InStr(s1, ";") + 1, Len(s1))
j = j + 1
Wend
Worksheets("Лист2").Cells(j, 3).Value = Trim(s1)
Worksheets("Лист2").Cells(j, 2).Value = j - 5
Worksheets("Лист2").Cells(j, 4).Value = s2
End If
Next i
Worksheets("Лист2").Cells(j + 2, 2).Value = "Итого:"
Worksheets("Лист2").Cells(j + 2, 3).Value = Str(j - 5) + "чел."
Worksheets("Лист2").Cells(j + 4, 2).Value = "Директор "
Worksheets("Лист2").Cells(j + 5, 2).Value = "Экономического лицея"
Worksheets("Лист2").Cells(j + 5, 4).Value = "Кузенко Н.И."
End Sub
Макрос «Прибыли»
Sub прибыли()
Dim s As String
For i = 6 To 106
Worksheets("Лист3").Cells(i, 2).Value = ""
Worksheets("Лист3").Cells(i, 3).Value = ""
Worksheets("Лист3").Cells(i, 4).Value = ""
Next i
j = 5
For i = 3 To 73
s = Worksheets("Лист1").Cells(i, 7).Value
s2 = Worksheets("Лист1").Cells(i, 2).Value
s1 = Trim(s)
If s1 <> "" Then
j = j + 1
While InStr(s1, ";") > 0
Worksheets("Лист3").Cells(j, 3).Value = Trim(Mid(s1, 1, InStr(s1, ";") - 1))
Worksheets("Лист3").Cells(j, 2).Value = j - 5
Worksheets("Лист3").Cells(j, 4).Value = s2
s1 = Mid(s1, InStr(s1, ";") + 1, Len(s1))
j = j + 1
Wend
Worksheets("Лист3").Cells(j, 3).Value = Trim(s1)
Worksheets("Лист3").Cells(j, 2).Value = j - 5
Worksheets("Лист3").Cells(j, 4).Value = s2
End If
Next i
Worksheets("Лист3").Cells(j + 2, 2).Value = "Итого:"
Worksheets("Лист3").Cells(j + 2, 3).Value = Str(j - 5) + "чел."
Worksheets("Лист3").Cells(j + 4, 2).Value = "Директор "
Worksheets("Лист3").Cells(j + 5, 2).Value = "Экономического лицея"
Worksheets("Лист3").Cells(j + 5, 4).Value = "Кузенко Н.И."
End Sub
Макрос «Наполняемость»
Sub наполняемость()
a1 = Worksheets("Лист1").Cells(75, 3).Value
a2 = Worksheets("Лист1").Cells(76, 3).Value
a3 = Worksheets("Лист1").Cells(77, 3).Value
If a1 > a2 And a1 > a3 Then a1 = a1
If a2 > a1 And a2 > a3 Then a1 = a2
If a1 > a2 And a1 > a3 Then a1 = a3
a4 = Worksheets("Лист1").Cells(78, 3).Value
a5 = Worksheets("Лист1").Cells(79, 3).Value
a6 = Worksheets("Лист1").Cells(80, 3).Value
If a4 > a5 And a4 > a6 Then a2 = a4
If a5 > a4 And a5 > a6 Then a2 = a5
If a6 > a4 And a6 > a5 Then a2 = a6
a7 = Worksheets("Лист1").Cells(75, 5).Value
a8 = Worksheets("Лист1").Cells(76, 5).Value
a9 = Worksheets("Лист1").Cells(77, 5).Value
If a7 > a8 And a7 > a9 Then a3 = a7
If a8 > a7 And a8 > a9 Then a3 = a8
If a9 > a7 And a9 > a8 Then a3 = a9
a10 = Worksheets("Лист1").Cells(78, 5).Value
a11 = Worksheets("Лист1").Cells(79, 5).Value
If a10 > a11 Then a4 = a10 Else a4 = a11
For i = 3 To 73
Worksheets("Лист4").Cells(i, 1).Value = ""
Worksheets("Лист4").Cells(i, 2).Value = ""
Worksheets("Лист4").Cells(i, 3).Value = ""
Worksheets("Лист4").Cells(i, 4).Value = ""
Worksheets("Лист4").Cells(i, 5).Value = ""
Worksheets("Лист4").Cells(i, 6).Value = ""
Worksheets("Лист4").Cells(i, 7).Value = ""
Worksheets("Лист4").Cells(i, 8).Value = ""
Worksheets("Лист4").Cells(i, 9).Value = ""
Next i
For i = 3 To 73
s = Worksheets("Лист1").Cells(i, 3).Value
s1 = Worksheets("Лист1").Cells(i, 2).Value
s2 = Worksheets("Лист1").Cells(i, 9).Value
s3 = Worksheets("Лист1").Cells(i, 11).Value
If s = "" Then Exit For
Select Case s
Case 1
j1 = j1 + 1
q1 = q1 + s2
Worksheets("Лист4").Cells(4 + j1, 1).Value = s1
Worksheets("Лист4").Cells(4 + j1, 2).Value = s2
Case 2
j2 = j2 + 1
q2 = q2 + s2
Worksheets("Лист4").Cells(4 + j2, 5).Value = s1
Worksheets("Лист4").Cells(4 + j2, 6).Value = s2
Case 3
j3 = j3 + 1
q3 = q3 + s2
Worksheets("Лист4").Cells(4 + j3, 9).Value = s1
Worksheets("Лист4").Cells(4 + j3, 10).Value = s2
Case 4
j4 = j4 + 1
q4 = q4 + s2
Worksheets("Лист4").Cells(7 + a1 + j4, 1).Value = s1
Worksheets("Лист4").Cells(7 + a1 + j4, 2).Value = s2
Case 5
j5 = j5 + 1
q5 = q5 + s2
Worksheets("Лист4").Cells(7 + a1 + j5, 5).Value = s1
Worksheets("Лист4").Cells(7 + a1 + j5, 6).Value = s2
Worksheets("Лист4").Cells(7 + a1 + j5, 7).Value = s3
Case 6
j6 = j6 + 1
q6 = q6 + s2
Worksheets("Лист4").Cells(7 + a1 + j6, 9).Value = s1
Worksheets("Лист4").Cells(7 + a1 + j6, 10).Value = s2
Worksheets("Лист4").Cells(7 + a1 + j6, 11).Value = s3
Case 7
j7 = j7 + 1
q7 = q7 + s2
Worksheets("Лист4").Cells(10 + j7 + a1 + a2, 1).Value = s1
Worksheets("Лист4").Cells(10 + j7 + a1 + a2, 2).Value = s2
Worksheets("Лист4").Cells(10 + j7 + a1 + a2, 3).Value = s3
Case 8
j8 = j8 + 1
q8 = q8 + s2
Worksheets("Лист4").Cells(10 + j8 + a1 + a2, 5).Value = s1
Worksheets("Лист4").Cells(10 + j8 + a1 + a2, 6).Value = s2
Worksheets("Лист4").Cells(10 + j8 + a1 + a2, 7).Value = s3
Case 9
j9 = j9 + 1
q9 = q9 + s2