第５話 成績評価を成績一覧表に加える
前話問題
成績評価マクロ解答例
Private Sub CommandButton1_Click()
Cells(6, 2) = "出席番号"
Cells(6, 3) = "国語"
Cells(6, 4) = "社会"
Cells(6, 5) = "数学"
Cells(6, 6) = "理科"
Cells(6, 7) = "英語"
Cells(6, 8) = "合計"
Cells(6, 9) = "平均"
Cells(6, 10) = "評価"
Dim i As Byte, w As Integer
For i = 1 To 10
Cells(6 + i, 2) = i
Cells(6 + i, 3) = Int(100 * Rnd())
Cells(6 + i, 4) = Int(100 * Rnd())
Cells(6 + i, 5) = Int(100 * Rnd())
Cells(6 + i, 6) = Int(100 * Rnd())
Cells(6 + i, 7) = Int(100 * Rnd())
Next
w = 0
For i = 1 To 10
w = w + Cells(6 + i, 3)
Next
Cells(17, 3) = w
Cells(18, 3) = w / 10
If w / 10 >= 50 Then
Cells(19, 3) = "合格"
Else
Cells(19, 3) = "不合格"
End If
w = 0
For i = 1 To 10
w = w + Cells(6 + i, 4)
Next
Cells(17, 4) = w
Cells(18, 4) = w / 10
If w / 10 >= 50 Then
Cells(19, 4) = "合格"
Else
Cells(19, 4) = "不合格"
End If
w = 0
For i = 1 To 10
w = w + Cells(6 + i, 5)
Next
Cells(17, 5) = w
Cells(18, 5) = w / 10
If w / 10 >= 50 Then
Cells(19, 5) = "合格"
Else
Cells(19, 5) = "不合格"
End If
w = 0
For i = 1 To 10
w = w + Cells(6 + i, 6)
Next
Cells(17, 6) = w
Cells(18, 6) = w / 10
If w / 10 >= 50 Then
Cells(19, 6) = "合格"
Else
Cells(19, 6) = "不合格"
End If
w = 0
For i = 1 To 10
w = w + Cells(6 + i, 7)
Next
Cells(17, 7) = w
Cells(18, 7) = w / 10
If w / 10 >= 50 Then
Cells(19, 7) = "合格"
Else
Cells(19, 7) = "不合格"
End If
w = 0
For i = 1 To 5
w = w + Cells(7, 2 + i)
Next
Cells(7, 8) = w
Cells(7, 9) = w / 5
If w / 5 >= 50 Then
Cells(7, 10) = "合格"
Else
Cells(7, 10) = "不合格"
End If
w = 0
For i = 1 To 5
w = w + Cells(8, 2 + i)
Next
Cells(8, 8) = w
Cells(8, 9) = w / 5
If w / 5 >= 50 Then
Cells(8, 10) = "合格"
Else
Cells(8, 10) = "不合格"
End If
w = 0
For i = 1 To 5
w = w + Cells(9, 2 + i)
Next
Cells(9, 8) = w
Cells(9, 9) = w / 5
If w / 5 >= 50 Then
Cells(9, 10) = "合格"
Else
Cells(9, 10) = "不合格"
End If
w = 0
For i = 1 To 5
w = w + Cells(10, 2 + i)
Next
Cells(10, 8) = w
Cells(10, 9) = w / 5
If w / 5 >= 50 Then
Cells(10, 10) = "合格"
Else
Cells(10, 10) = "不合格"
End If
w = 0
For i = 1 To 5
w = w + Cells(11, 2 + i)
Next
Cells(11, 8) = w
Cells(11, 9) = w / 5
If w / 5 >= 50 Then
Cells(11, 10) = "合格"
Else
Cells(11, 10) = "不合格"
End If
w = 0
For i = 1 To 5
w = w + Cells(12, 2 + i)
Next
Cells(12, 8) = w
Cells(12, 9) = w / 5
If w / 5 >= 50 Then
Cells(12, 10) = "合格"
Else
Cells(12, 10) = "不合格"
End If
w = 0
For i = 1 To 5
w = w + Cells(13, 2 + i)
Next
Cells(13, 8) = w
Cells(13, 9) = w / 5
If w / 5 >= 50 Then
Cells(13, 10) = "合格"
Else
Cells(13, 10) = "不合格"
End If
w = 0
For i = 1 To 5
w = w + Cells(14, 2 + i)
Next
Cells(14, 8) = w
Cells(14, 9) = w / 5
If w / 5 >= 50 Then
Cells(14, 10) = "合格"
Else
Cells(14, 10) = "不合格"
End If
w = 0
For i = 1 To 5
w = w + Cells(15, 2 + i)
Next
Cells(15, 8) = w
Cells(15, 9) = w / 5
If w / 5 >= 50 Then
Cells(15, 10) = "合格"
Else
Cells(15, 10) = "不合格"
End If
w = 0
For i = 1 To 5
w = w + Cells(16, 2 + i)
Next
Cells(16, 8) = w
Cells(16, 9) = w / 5
If w / 5 >= 50 Then
Cells(16, 10) = "合格"
Else
Cells(16, 10) = "不合格"
End If
w = 0
For i = 1 To 10
w = w + Cells(6 + i, 8)
Next
Cells(17, 8) = w
Cells(18, 8) = w / 10
Dim v As Single
v = 0
For i = 1 To 10
v = v + Cells(6 + i, 9)
Next
Cells(17, 9) = v
Cells(18, 9) = v / 10
Cells(17, 2) = "合計"
Cells(18, 2) = "平均"
Cells(19, 2) = "評価"
End Sub
次の学習に入りましょう。
否定付きのもしもボックス（If～Else～文）は入れ子式に使うことによって、
場合分けを３つ以上にすることができます。
例えば、
If p < 30 then
A
Else
If p <50 then
B
Else
C
End If
End If
とすることができます。
これは、pが３０未満ならAを実行し、
３０未満でないなら、
If p <50 then
B
Else
C
End If
を実行しなさいですが、どういう意味であるかおわかりですか。
答えは、３０行下。
答え
If p < 30 then
A
Else
If p <50 then
B
Else
C
End If
End If
３０未満なら、Aを実行して、
３０以上５０未満なら、Bを実行して、
５０以上なら、Cを実行せよです。
解説
pが３０未満ならAを実行します。
そして、そうでないなら
If p <50 then
B
Else
C
End If
を実行するわけですが、３０未満を否定すると３０以上です。
ですから、３０以上の条件の下
If p <50 then
B
Else
C
End If
が実行されます。
そこに条件 p <50 が加わると３０以上５０未満ということになります。
そして、その条件 p <50 が否定されれば５０以上です。
以上から答えの通り、
３０未満なら、Aを実行して、
３０以上５０未満なら、Bを実行して、
５０以上なら、Cを実行せよ。
となるわけです。
では、演習しましょう。
前々話問題マクロ
