Реферат: Система компьютерной обработки данных
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
Страницы: 1, 2, 3, 4 |