Реферат: Система компьютерной обработки данных

--PAGE_BREAK--
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("Лист    продолжение
--PAGE_BREAK--