【第９回Excelマクロ】複数条件のある集計【ほぼfor/if文】
前回に引き続き、複数捕まえたピカチュウのデータを使います。今回は、HP70以下・HP71～80・HP81以上と3つのカテゴリーに分けて、かつそれぞれ性別ごとに何匹いるかを表にして集計してみます。
まずはデータを2次元配列にして出力します。以下のコードをコピペして実行しましょう。
Dim ws As Worksheet 'ワークシート1のオブジェクト変数を宣言
Set ws = ThisWorkbook.Sheets("Sheet1")
Dim pikachuArray As Variant 'ピカチュウの配列
pikachuArray = [{"CP","HP","重さ","高さ","性別";391,64,6.5,0.4,"オス";576,81,"",0.32,"メス";350,60,6.38,0.42,"オス";534,76,7.26,0.39,"オス";424,73,9.76,0.51,"メス";311,60,7.77,0.45,"メス";119,35,5.47,0.43,"オス";501,73,7.41,0.43,"メス";489,72,4.57,0.36,"メス"}]
For i = LBound(pikachuArray, 1) To UBound(pikachuArray, 1) 'wsの1行目の1列目に2次元配列を出力
For j = LBound(pikachuArray, 2) To UBound(pikachuArray, 2)
ws.Cells(i, j).Value = pikachuArray(i, j)
Next j
Next i
Dim ws2 As Worksheet 'ワークシート2のオブジェクト変数を宣言
Set ws2 = ThisWorkbook.Sheets("Sheet2")
Dim resultArray As Variant '性別の各HP件数の配列
resultArray = [{"性別の各HP件数","","","";"HP","～70","71～80","81～";"オス","","","";"メス","","",""}]
For i = LBound(resultArray, 1) To UBound(resultArray, 1) 'ws2の1行目の1列目に2次元配列を出力
For j = LBound(resultArray, 2) To UBound(resultArray, 2)
ws2.Cells(i, j).Value = resultArray(i, j)
Next j
Next i下の表が作成されます。
さらに、今回はシート２に以下の表も作成されます。これで準備OKです。
目的の列の取得
以下のコードをコピペして、HPと性別の列を取得しましょう。内容は前回とほぼ同じですね。
Dim lastRow As Long, lastCol As Long '最終行・最終列をlong型で宣言
lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row '最終行取得
lastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column '最終列取得
Dim seibetsuCol As Integer '性別の列
Dim HPCol As Integer 'HPの列
For i = 1 To lastCol 'for文で性別・HPの列を検索して取得
If ws.Cells(1, i).Value = "性別" Then
seibetsuCol = i
ElseIf ws.Cells(1, i).Value = "HP" Then
HPCol = i
End If
Next iデータを格納する2次元配列の宣言と初期化
HPArrayというInteger型の2次元配列を準備し、表に応じたそれぞれの要素に値を更新していきます。
「Erase HPArray」と記述することで、Integer型の配列は「0」で初期化されます。
Dim HPArray(1 To 2, 1 To 3) As Integer '性別の各HP件数を格納する2次元配列{例：HP(1,1)→オスのHP70以下・HP(2,2)→メスのHP71～80}
Erase HPArray '配列を０で初期化HPArrayの各要素は以下のリスト及び表に対応しています。
- HPArray(1,1)→オスのHP70以下
- HPArray(1,2)→オスのHP71〜80
- HPArray(1,3)→オスのHP81以上
- HPArray(2,1)→メスのHP70以下
- HPArray(2,2)→メスのHP71〜80
- HPArray(2,3)→メスのHP81以上
複数条件
複数条件での集計にはいろいろの方法が考えられますが、タイトル通り if文を使っていきます。
まずは、オスの場合のHPのカテゴリ分けから考えていきましょう。具体的には、「If ws.Cells(i, seibetsuCol).Value = “オス" Then」というオスの場合を記述した if文内にさらに if文をネストし、HPのカテゴリ分けをif,elseif,elseと場合分けしていきます。
For i = 2 To lastRow 'for文で2行目から最終行までループ
If ws.Cells(i, seibetsuCol).Value = "オス" Then 'オスの時の処理
If ws.Cells(i, HPCol).Value <= 70 Then 'HPが70以下の時
HPArray(1, 1) = HPArray(1, 1) + 1 'HPArray(1, 1)をカウントアップ
ElseIf ws.Cells(i, HPCol).Value >= 71 And ws.Cells(i, HPCol).Value <= 80 Then 'HP71～80の時
HPArray(1, 2) = HPArray(1, 2) + 1 'HPArray(1, 2)をカウントアップ
Else 'それ以外の時（HP81以上）
HPArray(1, 3) = HPArray(1, 3) + 1 'HPArray(1, 3)をカウントアップ
End If
End If
Next i
For i = LBound(HPArray, 1) To UBound(HPArray, 1) 'シート２に配列HPArrayを出力
For j = LBound(HPArray, 2) To UBound(HPArray, 2)
ws2.Cells(i + 2, j + 1).Value = HPArray(i, j)
Next j
Next i実行すると以下のように表が埋まります。オスがうまくの集計されていますね。
今度はメスも一緒に集計してみましょう。単純に「ElseIf」文を追記してオスのコードをコピペしてメスに置き換えると簡単ですね。
For i = 2 To lastRow 'for文で2行目から最終行までループ
If ws.Cells(i, seibetsuCol).Value = "オス" Then 'オスの時の処理
If ws.Cells(i, HPCol).Value <= 70 Then 'HPが70以下の時
HPArray(1, 1) = HPArray(1, 1) + 1 'HPArray(1, 1)をカウントアップ
ElseIf ws.Cells(i, HPCol).Value >= 71 And ws.Cells(i, HPCol).Value <= 80 Then 'HP71～80の時
HPArray(1, 2) = HPArray(1, 2) + 1 'HPArray(1, 2)をカウントアップ
Else 'それ以外の時（HP81以上）
HPArray(1, 3) = HPArray(1, 3) + 1 'HPArray(1, 3)をカウントアップ
End If
ElseIf ws.Cells(i, seibetsuCol).Value = "メス" Then 'メスの時も同様に処理
If ws.Cells(i, HPCol).Value <= 70 Then
HPArray(2, 1) = HPArray(2, 1) + 1
ElseIf ws.Cells(i, HPCol).Value >= 71 And ws.Cells(i, HPCol).Value <= 80 Then
HPArray(2, 2) = HPArray(2, 2) + 1
Else
HPArray(2, 3) = HPArray(2, 3) + 1
End If
End If
Next i
For i = LBound(HPArray, 1) To UBound(HPArray, 1) 'シート２に配列HPArrayを出力
For j = LBound(HPArray, 2) To UBound(HPArray, 2)
ws2.Cells(i + 2, j + 1).Value = HPArray(i, j)
Next j
Next i実行すると以下のようになりました。9匹分しっかり集計されています。
全コードのまとめ
今回使用したコードを以下にまとめます。
Dim ws As Worksheet 'ワークシート1のオブジェクト変数を宣言
Set ws = ThisWorkbook.Sheets("Sheet1")
Dim pikachuArray As Variant 'ピカチュウの配列
pikachuArray = [{"CP","HP","重さ","高さ","性別";391,64,6.5,0.4,"オス";576,81,"",0.32,"メス";350,60,6.38,0.42,"オス";534,76,7.26,0.39,"オス";424,73,9.76,0.51,"メス";311,60,7.77,0.45,"メス";119,35,5.47,0.43,"オス";501,73,7.41,0.43,"メス";489,72,4.57,0.36,"メス"}]
For i = LBound(pikachuArray, 1) To UBound(pikachuArray, 1) 'wsの1行目の1列目に2次元配列を出力
For j = LBound(pikachuArray, 2) To UBound(pikachuArray, 2)
ws.Cells(i, j).Value = pikachuArray(i, j)
Next j
Next i
Dim ws2 As Worksheet 'ワークシート2のオブジェクト変数を宣言
Set ws2 = ThisWorkbook.Sheets("Sheet2")
Dim resultArray As Variant '性別の各HP件数の配列
resultArray = [{"性別の各HP件数","","","";"HP","～70","71～80","81～";"オス","","","";"メス","","",""}]
For i = LBound(resultArray, 1) To UBound(resultArray, 1) 'ws2の1行目の1列目に2次元配列を出力
For j = LBound(resultArray, 2) To UBound(resultArray, 2)
ws2.Cells(i, j).Value = resultArray(i, j)
Next j
Next i
Dim lastRow As Long, lastCol As Long '最終行・最終列をlong型で宣言
lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row '最終行取得
lastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column '最終列取得
Dim seibetsuCol As Integer '性別の列
Dim HPCol As Integer 'HPの列
For i = 1 To lastCol 'for文で性別・HPの列を検索して取得
If ws.Cells(1, i).Value = "性別" Then
seibetsuCol = i
ElseIf ws.Cells(1, i).Value = "HP" Then
HPCol = i
End If
Next i
Dim HPArray(1 To 2, 1 To 3) As Integer '性別の各HP件数を格納する2次元配列{例：HP(1,1)→オスのHP70以下・HP(2,2)→メスのHP71～80}
Erase HPArray '配列を０で初期化
For i = 2 To lastRow 'for文で2行目から最終行までループ
If ws.Cells(i, seibetsuCol).Value = "オス" Then 'オスの時の処理
If ws.Cells(i, HPCol).Value <= 70 Then 'HPが70以下の時
HPArray(1, 1) = HPArray(1, 1) + 1 'HPArray(1, 1)をカウントアップ
ElseIf ws.Cells(i, HPCol).Value >= 71 And ws.Cells(i, HPCol).Value <= 80 Then 'HP71～80の時
HPArray(1, 2) = HPArray(1, 2) + 1 'HPArray(1, 2)をカウントアップ
Else 'それ以外の時（HP81以上）
HPArray(1, 3) = HPArray(1, 3) + 1 'HPArray(1, 3)をカウントアップ
End If
ElseIf ws.Cells(i, seibetsuCol).Value = "メス" Then 'メスの時も同様に処理
If ws.Cells(i, HPCol).Value <= 70 Then
HPArray(2, 1) = HPArray(2, 1) + 1
ElseIf ws.Cells(i, HPCol).Value >= 71 And ws.Cells(i, HPCol).Value <= 80 Then
HPArray(2, 2) = HPArray(2, 2) + 1
Else
HPArray(2, 3) = HPArray(2, 3) + 1
End If
End If
Next i
For i = LBound(HPArray, 1) To UBound(HPArray, 1) 'シート２に配列HPArrayを出力
For j = LBound(HPArray, 2) To UBound(HPArray, 2)
ws2.Cells(i + 2, j + 1).Value = HPArray(i, j)
Next j
Next i最後に
今回は複数条件での集計をしてみました。このコードを使えば、例えば以下のようなたくさん捕まえたピカチュウがいても…
以下のように1秒もかからずに集計ができます。
ぜひ、応用していろいろなデータの集計をしてみましょう。ただし、エラー対策(性別の列が取得できない場合等)を全くしていないので、コピペして使う際には注意しましょう。