Система компьютерной обработки данных
Информация - Компьютеры, программирование
Другие материалы по предмету Компьютеры, программирование
; "" 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 = "Итог