8本目　「点数の合否判定」を学ぶ - エクセルマクロを０から独学で極める極意～ITを味方に～

お題　ノック８本目
必要な基礎知識(マクロ)
必要な基礎知識(エクセル関数)
- Sum
- Countif
回答
学んだこと総括

お題　ノック８本目

f:id:Rimux:20210723233342p:plain
VBA100本ノック 8本目：点数の合否判定｜VBA練習問題

f:id:Rimux:20210723112823p:plain

作成したファイル

必要な基礎知識(マクロ)

intersect

複数のセル範囲の共有セル範囲を表すRangeオブジェクトを返します。
[Application.]Intersect(Arg1, Arg2[, Arg3, ・・・, Arg30])
[]内省略可能

Resize

Range.Resize(RowSize, ColumnSize)
サイズ変更後のRangeオブジェクトが返されます。
RowSize,ColumnSizeは省略すると、変更前のサイズが入ります。

略記の仕方
例１）行を３行に、列数はそのままにしたい処理
Resize(3)
例２）行数はそのままに、列数を５にしたい処理
Resize(,5)

For each ~ In - .Rows

行ごとに処理を実行する

With ~ End With

~で書いた内容は
With内で省略して記述できる

例えば、繰り返した記述が必要な際にWithを用いて
.を書くと、With の後の内容が自動挿入される。

With Worksheet("成績表")
　a= .Range("A1")
b= .Range("A2")
.
.
.
End With

WorksheetFunction

エクセル関数をVBに使用できる。
今回使用したエクセル関数は以下の二つ

必要な基礎知識(エクセル関数)

Sum

セル範囲の数値を合計する

Countif

セル範囲から条件に合致したセル個数を取得

回答

回答結果（全文）

まずは回答結果から

Dim ws As Worksheet
Set ws = Worksheets("成績表")

Dim rng As Range

 Set rng = ws.Range("A1").CurrentRegion '表範囲
 Set rng = Intersect(rng, rng.Offset(1, 1)) ' 表データ範囲取得
 rng.Columns(6).ClearContents '合否欄リセット
 
 Dim r As Range
 
 For Each r In rng.Rows
 
    With WorksheetFunction
        'Resize で合否列（G)を範囲から出す
       If .Sum(r.Resize(, 5)) >= 350 And _
            .CountIf(r.Resize.Resize(, 5), ">=50") = 5 Then
            Cells(r.Row, 7) = "合格"
            ce
     End If
    End With
 Next

ワークシート指定

今回はまずワークシート名の指定があるので、「成績表」シートに対して
のみ実行されるマクロを作成します。
成績表のワークシートオブジェクトを作ります。

Dim ws As Worksheet
Set ws = Worksheets("成績表")

表のデータ部のみの抽出

次に見出し行列を除き表データだけとってくるテクです。数値演算の条件式に、文字列の氏名は邪魔になるので。

これは今後もおそらく頻出、大事な箇所だと思います。

まず、この緑範囲を取得するにはどうすればよいか、と考えてみてください。
f:id:Rimux:20210723112535p:plain
・
・
・

この緑範囲は、
「A1のCurrentRegion」（表全体範囲）と
「A1のCurrentRegionをoffset(1,1)」（ずらした範囲）
の交わった重複範囲として捉えられます。
重複範囲は、この上の基礎知識で学びました、「intersect」でしたね！ではまずここまでをコード化します。

f:id:Rimux:20210723112532p:plain

Dim rng As Range

 Set rng = ws.Range("A1").CurrentRegion '表範囲
 Set rng = Intersect(rng, rng.Offset(1, 1)) ' 表データ範囲取得

前回の合否判定入力リセット

rng.Columns(6).ClearContents

私はこれを書いている時、「あれ？これだとG列じゃなくてF列が消えるんじゃないの？大丈夫？」と思いました。しかし、これで問題ありません。

rngの範囲はRange("B2:G12")となるため、６列目というのは「Rangeオブジェクトの範囲内での」６列目です。

For each　ー　In ー　Rows で行ごと判定＆処理

For r In rng.Rows
でrセルが行ごとに処理されます。
ですが、今のままだと処理範囲rngに、合否欄の空白をスコアーに加算しても意味がないので、範囲を削りましょう。
→ Resize(（そのまま）,5列に修正)

お題の要　複数条件式とエクセル関数の適用

WorksheetFunction.Sum(範囲)>= 350
And _
(And:「かつ」という意味です。　スペースアンダーバー：処理がまだ行途中の場合、通常改行するとエラーがでますが、これを使えば改行できます。)
WorksheetFunction.Countif(リサイズ後の範囲,">=50")=5

50点以上という条件を満たすセルが５個だったら、とかいています。
指定範囲がそもそも教科の５セルなので、要は全科目５０点以上だったら、という条件と等価です。

いずれの判定も、いつものi,j as longからのForNext二重ループでぶんぶんまわしでもできるのですが、今回は新たな知識導入のため敢えてWorksheet関数で解いてみました。

With ーEnd With

WorksheetFunction~がくどいですね。Withで略記に変えます。

With WorksheetFunction
　・
　・
　・
End With

実行結果
無事に合否判定ができました。

f:id:Rimux:20210723112529p:plain

学んだこと総括

行ごとのForーIn ーrows文を使えば　i,j２重ループと同じ処理ができた

マクロでもエクセル関数は使える！忘れた時はエクセルシートに打ってみて調べて貼り付けたら早い

With で略記し、コードを読みやすく整形するテク

CurrentRegion /offset/intersect/Resize 組み合わせれば大抵の表範囲は取得できる

お題 ノック８本目