エクセルVBA100本ノック。8本目：点数の合否判定

この記事から得るもの

Countifで条件分岐方法。
Ｉｆ文のネスト。

目次
　１　今回のお題
　２　今回のお題の意図
　３　回答
　４　Function（自作関数）を作成した方のコード　
　５　結論　
　６　クラスモジュールで書いてみた

１　今回のお題

「成績表」シートに5教科の成績表があります。
以下の2条件を満たした者が合格となります。
・5教科合計が350点以上
・全ての科目が50点以上
G列に、合格者に対しては「合格」と出力し、不合格は空欄にしてください。

f:id:bimori466:20201105173534p:plain

excel-ubara.com

２　今回のお題の意図

学校、塾関係者の方がよく使いそうな表です。
作成の意図は、If条件分岐のネスト（入れこ）を作ることと思われます。

３　回答

私の最初の回答

Sub ノック8本目_1()

Dim Ws As Worksheet: Set Ws = Worksheets("sheet1")
Dim English, NationalLanguage, Math, Science, Society As Integer
Dim Σ_Score As Integer


For k_Student = 2 To Ws.Cells(Rows.Count, 1).End(xlUp).Row
    English = Ws.Cells(k_Student, 2)
    NationalLanguage = Ws.Cells(k_Student, 3)
    Math = Ws.Cells(k_Student, 4)
    Science = Ws.Cells(k_Student, 5)
    Society = Ws.Cells(k_Student, 6)
    
    Σ_Score = English + NationalLanguage + Math + Science + Society
    
    If Σ_Score >= 350 Then
        If English >= 50 And NationalLanguage >= 50 And Math >= 50 And Science >= 50 And Society >= 50 Then
            Ws.Cells(k_Student, 7) = "合格"
        End If
    End If
Next

End Sub

科目ごとに変数を作成して、変数Σ_Score に合計点数を代入。その後、Ｉｆ文で、合計３５０点以上かつ、全科目が５０点以上か分岐処理を作成しました。

全科目が５０点以上かを判断するＩｆ文が長すぎますね(;^ω^)。
この条件をもっとスマート書いてあるのが以下モジュール。

Sum、Countifを使ったコード

Sub SumCountif()

Dim i As Long

For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    If WorksheetFunction.Sum(Range(Cells(i, 2), Cells(i, 6))) >= 350 And _
        WorksheetFunction.CountIf(Range(Cells(i, 2), Cells(i, 6)), ">=50") = 5 Then
        
        Cells(i, 7) = "合格"
        
    Else
        Cells(i, 7) = ""
        
    End If
Next

End Sub

短い、スマート、かつ分かりやすい。私のように、こまごまと科目ごとの変数など不要なのですね。
特に参考になったのは、「Countifで条件５０点以上が５なら」という部分です。
そういう判断方法があったか～！！と驚きました。

４　Function（自作関数）を作成した方のコード

自作関数のコード

Function 合否(traget_range As Range) As String

If WorksheetFunction.Sum(traget_range) >= 350 Then
    If WorksheetFunction.Min(traget_range) >= 50 Then
        合否 = "合格"
    End If
End If

End Function

お題はクリアされてますね。
しかし、私はセルに数式は入れない派なので使わないですね。でも、Functionの作り方の勉強にはなりました。
これはこれですばらしいです！

５　結論

Sum、Countifを使ったコードに１票。
コードが簡潔で分かりやすいです。人にも教えやすい。

６　クラスモジュールで書いてみた

クラスモジュールの練習のために書いてみました。

標準モジュール

Sub 合否判定()
Dim テスト結果 As TestScore: Set テスト結果 = New TestScore

Dim wb As Worksheet: Set wb = Worksheets("sheet1")

'For i = 2 To wb.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To wb.Range("A1").CurrentRegion.Rows.Count
    テスト結果.英語 = wb.Cells(i, 2)
    テスト結果.国語 = wb.Cells(i, 3)
    テスト結果.数学 = wb.Cells(i, 4)
    テスト結果.理科 = wb.Cells(i, 5)
    テスト結果.社会 = wb.Cells(i, 6)
    
    '合否判定１
    テスト結果.得点合計 = テスト結果.合格判定1(テスト結果.英語, テスト結果.国語, テスト結果.数学, テスト結果.理科, テスト結果.社会)
    If テスト結果.得点合計 >= 350 Then: wb.Cells(i, 7) = "OK"
    
    '合否判定２
    テスト結果.個別得点 = テスト結果.合格判定2(テスト結果.英語, テスト結果.国語, テスト結果.数学, テスト結果.理科, テスト結果.社会)
    If テスト結果.個別得点 = True Then: wb.Cells(i, 8) = "OK"
    
    
    '最終判定
    If wb.Cells(i, 7) = "OK" And wb.Cells(i, 8) = "OK" Then
        wb.Cells(i, 9) = "合格"
    Else
        wb.Cells(i, 9) = ""
    End If
Next

End Sub

クラスモジュール

Public 氏名 As String
Public 英語 As Integer
Public 国語 As Integer
Public 数学 As Integer
Public 理科 As Integer
Public 社会 As Integer
Public 得点合計 As Integer
Public 個別得点 As Boolean

Function 合格判定1(my英語, my国語, my数学, my理科, my社会)


合格判定1 = my英語 + my国語 + my数学 + my理科 + my社会

End Function

Function 合格判定2(my英語, my国語, my数学, my理科, my社会)

If my英語 >= 50 And my国語 >= 50 And my数学 >= 50 And my理科 >= 50 And my社会 >= 50 Then
    合格判定2 = True
Else
    合格判定2 = False
End If

End Function