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

If IsEmpty(Cells(4, i)) Then

Columns(i).Hidden = True

End If

Next i

For i = 26 To 32

If IsEmpty(Cells(4, i)) Then

Columns(i).Hidden = True

End If

Next i

For i = 34 To 40

If IsEmpty(Cells(4, i)) Then

Columns(i).Hidden = True

End If

Next i

For i = 42 To 48

If IsEmpty(Cells(4, i)) Then

Columns(i).Hidden = True

End If

Next i

For i = 50 To 56

If IsEmpty(Cells(4, i)) Then

Columns(i).Hidden = True

End If

Next i

CommandButton2.Caption = "Показать скрытые столбцы"

Else

For i = 2 To 8

If Columns(i).Hidden = True Then

Columns(i).Hidden = False

End If

Next i

For i = 9 To 16

If Columns(i).Hidden = True Then

Columns(i).Hidden = False

End If

Next i

For i = 18 To 24

If Columns(i).Hidden = True Then

Columns(i).Hidden = False

End If

Next i

For i = 26 To 32

If Columns(i).Hidden = True Then

Columns(i).Hidden = False

End If

Next i

For i = 34 To 40

If Columns(i).Hidden = True Then

Columns(i).Hidden = False

End If

Next i

For i = 42 To 48

If Columns(i).Hidden = True Then

Columns(i).Hidden = False

End If

Next i

For i = 50 To 56

If Columns(i).Hidden = True Then

Columns(i).Hidden = False

End If

Next i

CommandButton2.Caption = "Скрыть пустые столбцы"

End If

End Sub

Приложение 2

Макрос «Itogi»

Private Sub CommandButton1_Click()

Dim intTarray(1 To 11) As Integer

For i = 5 To 76

s = Worksheets("Лист1").Cells(i, 1).Value

s1 = Left(s, Len(s) - 1)

intTarray(Val(s1)) = intTarray(Val(s1)) + 1

Nexti

For i = 7 To 100

For j = 1 To 7

Worksheets("Лист2").Cells(i, j).Value = ""

Next j

Next i

k = 6

For i = 2 To 11

For j = 5 To 76

s = Worksheets("Лист1").Cells(j, 1).Value

If Len(s) <> 0 Then

If Len(s) > 1 Then r = 2 Else r = 1

If Val(Left(s, r)) = i Then

s0 = Worksheets("Лист1").Cells(j, 2).Value

S1 = Worksheets("Лист1").Cells(j, 6).Value

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 = "Директор "

Страницы: 1, 2, 3, 4