レッスン２模擬試験成績表

１・成績の合計と平均、最高、最低を関数で表示

２・評価は成績の合計の200以上は優を175以上は良を以下は可をIF関数で表示します。

３・ランク関数で順位を表示します。

４・平均点より上の人には薄い緑で色分けします。（条件付き書式）

５・優・可にも条件付書式で色分けします。

作り方は赤枠中の１～９を見ながら作ってみてください。

それではコマンドボタンを配置してこれをVBAで実現します。

Private Sub CommandButton1_Click()

For n = 4 To 18

Total = 0

For i = 2 To 4

Total = Total + Cells(n, i)

Next i

Cells(n, 5) = Total

Next n

'----------------------------------------------------------

Dim suN()

wsEnd = 18

'ソート用------------------------------------------------

For p = 2 To 5

ReDim suN(wsEnd, 1)

suN(0, 0) = wsEnd

s0 = 1

For i = 4 To suN(0, 0)

suN(s0, 0) = Cells(i, p)

suN(s0, 1) = i

s0 = s0 + 1

Next i

n = s0 - 1 'ﾃﾞｰﾀ件数

'shell-metzner sort--------------------------------------

510 d = 1

520 d = d + d: If d > n Then GoTo 580 Else GoTo 520

530 For i = 1 To n - d: J = i

540 k = J + d: If suN(k, 0) >= suN(J, 0) Then GoTo 570

550 X = suN(k, 0): suN(k, 0) = suN(J, 0): suN(J, 0) = X

X = suN(k, 1): suN(k, 1) = suN(J, 1): suN(J, 1) = X

560 J = J - d: If J> 0 Then GoTo 540

570 Next i

580 d = Int((d - 1) / 2): If d > 0 Then GoTo 530

'--------------------------------------------------------

Cells(20, p) = suN(n, 0) '最大

Cells(21, p) = suN(1, 0) '最少

'---------順位表示

tate = 4: nCol = 11

s0 = n

For i = 1 To n

Cells(suN(s0, 1), 7) = i

s0 = s0 - 1

Next i

Next p

'平均------------------------

tate = 4: nCol = 2

s0 = 2

For J = 2 To 5

kei = 0

For i = 4 To 18

kei = kei + Cells(i, J)

Next i

Cells(19, J) = Round((kei / 15), 0)

Next J

'----------------------------

tate = 4: nCol = 5

Range("A4:A18").Interior.ColorIndex = 0

For i = 1 To n

If Cells(tate, nCol)< 175 Then

Range(Cells(tate, nCol + 1), Cells(tate, nCol + 1)).Clear

Cells(tate, nCol + 1).Interior.Color = Cells(1, 2).Interior.Color

Cells(tate, nCol + 1) = "可"

End If

If Cells(tate, nCol)> = 175 Then

If Cells(tate, nCol) < 200 Then

Range(Cells(tate, nCol + 1), Cells(tate, nCol + 1)).Clear

Cells(tate, nCol + 1) = "良"

End If

If Cells(tate, nCol)> = 200 Then

Range(Cells(tate, nCol + 1), Cells(tate, nCol + 1)).Clear

Cells(tate, nCol + 1).Interior.Color = Cells(1, 3).Interior.Color

Cells(tate, nCol + 1) = "優"

End If

'--------平均より高い成績者色を付ける

If Cells(tate, nCol)> = Cells(19, nCol) Then

Cells(tate, 1).Interior.Color = Cells(1, 4).Interior.Color

End If

tate = tate + 1

Next i

'表の作成

Range("A3:G21").Borders.LineStyle = xlContinuous

End Sub