p align="left">S11 = S11 + S1 s2 = Worksheets("Лист1").Cells(j, 7).Value s12 = s12 + s2 S3 = Worksheets("Лист1").Cells(j, 8).Value s31 = s31 + S3 s4 = Worksheets("Лист1").Cells(j, 9).Value s41 = s41 + s4 s5 = Worksheets("Лист1").Cells(j, 10).Value s15 = s15 + s5 k = k + 1 Worksheets("Лист2").Cells(k, 1).Value = s Worksheets("Лист2").Cells(k, 2).Value = s0 Worksheets("Лист2").Cells(k, 3).Value = S1 & "/" & s2 Worksheets("Лист2").Cells(k, 6).Value = S3 & "(" & s4 & ")" Worksheets("Лист2").Cells(k, 4).Value = s5 If S1 = "" Or s2 = "" Then Worksheets("Лист2").Cells(k, 5).Value = " " Else Worksheets("Лист2").Cells(k, 5).Value = Str(Round(100 * (Val(S1) - Val(s5)) / Val(S1))) & "%" KLASS = KLASS + 1 A = A + Round(100 * (Val(S1) - Val(s5)) / Val(S1)) 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 Макрос «Неуспевающие » Private Sub CommandButton1_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 Worksheets("Лист4").Cells(10 + j9 + a1 + a2, 9).Value = s1 Worksheets("Лист4").Cells(10 + j9 + a1 + a2, 10).Value = s2 Worksheets("Лист4").Cells(10 + j9 + a1 + a2, 11).Value = s3 Case 10 j10 = j10 + 1 q10 = q10 + s2 Worksheets("Лист4").Cells(16 + j10 + a1 + a2 + a3, 1).Value = s1 Worksheets("Лист4").Cells(16 + j10 + a1 + a2 + a3, 2).Value = s2 Worksheets("Лист4").Cells(16 + j10 + a1 + a2 + a3, 3).Value = s3 Case 11 j11 = j11 + 1 q11 = q11 + s2 Worksheets("Лист4").Cells(16 + j11 + a1 + a2 + a3, 5).Value = s1 Worksheets("Лист4").Cells(16 + j11 + a1 + a2 + a3, 6).Value = s2 Worksheets("Лист4").Cells(16 + j11 + a1 + a2 + a3, 7).Value = s3 End Select Next i Worksheets("Лист4").Cells(5 + j1, 1).Value = "Итого" Worksheets("Лист4").Cells(5 + j1, 2).Value = q1 Worksheets("Лист4").Cells(5 + j2, 5).Value = "Итого" Worksheets("Лист4").Cells(5 + j2, 6).Value = q2 Worksheets("Лист4").Cells(5 + j3, 10).Value = q3 Worksheets("Лист4").Cells(8 + j4 + a1, 2).Value = q4 Worksheets("Лист4").Cells(8 + j5 + a1, 6).Value = q5 Worksheets("Лист4").Cells(8 + j6 + a1, 9).Value = "Итого" Worksheets("Лист4").Cells(8 + j6 + a1, 10).Value = q6 Worksheets("Лист4").Cells(11 + j7 + a1 + a2, 1).Value = "Итого" Worksheets("Лист4").Cells(11 + j7 + a1 + a2, 2).Value = q7 Worksheets("Лист4").Cells(11 + j8 + a1 + a2, 5).Value = "Итого" Worksheets("Лист4").Cells(11 + j8 + a1 + a2, 6).Value = q8 Worksheets("Лист4").Cells(11 + j9 + a1 + a2, 9).Value = "Итого" Worksheets("Лист4").Cells(11 + j9 + a1 + a2, 10).Value = q9 Worksheets("Лист4").Cells(17 + j10 + a1 + a2 + a3, 1).Value = "Итого" Worksheets("Лист4").Cells(17 + j10 + a1 + a2 + a3, 2).Value = q10 Worksheets("Лист4").Cells(17 + j11 + a1 + a2 + a3, 5).Value = "Итого" Worksheets("Лист4").Cells(17 + j11 + a1 + a2 + a3, 6).Value = q11 Worksheets("Лист4").Cells(3 + 11 + j11 + a1 + a2 + a3 + a4, 3).Value = "Итого" Worksheets("Лист4").Cells(3 + 11 + j11 + a1 + a2 + a3 + a4, 5).Value = "1 - 4 кл" Worksheets("Лист4").Cells(3 + 12 + j11 + a1 + a2 + a3 + a4, 5).Value = "5 - 9 кл" Worksheets("Лист4").Cells(3 + 13 + j11 + a1 + a2 + a3 + a4, 5).Value = "10 - 11 кл" Worksheets("Лист4").Cells(3 + 11 + j11 + a1 + a2 + a3 + a4, 6).Value = q1 + q2 + q3 + q4 Worksheets("Лист4").Cells(3 + 12 + j11 + a1 + a2 + a3 + a4, 6).Value = q5 + q6 + q7 + q8 + q9 Worksheets("Лист4").Cells(3 + 13 + j11 + a1 + a2 + a3 + a4, 6).Value = q10 + q11 Worksheets("Лист4").Cells(3 + 14 + j11 + a1 + a2 + a3 + a4, 5).Value = "Всего" Worksheets("Лист4").Cells(3 + 14 + j11 + a1 + a2 + a3 + a4, 6).Value = q10 + q11 + q5 + q6 + q7 + q8 + q9 + q1 + q2 + q3 + q4 End Sub Размещено на Allbest.ru
Страницы: 1, 2
|