今回は、文字列に〇〇という文字が入っていたら集計する…といった「部分一致による集計」をしていきます。
今回も以下のデータを準備したので、コピペして実行して下さい。
Dim ws As Worksheet 'ワークシート1のオブジェクト変数を宣言
Set ws = ThisWorkbook.Sheets("Sheet1")
Dim pikachuArray As Variant 'ピカチュウの配列
pikachuArray = [{"CP","HP","重さ","高さ"," 性別";391,64,6.5,0.4,"bオス";576,81,"",0.32,"メスa";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以下の表が出たら準備OKです。
前回と同じ集計方法だと…
あやしいデータがありますが、前回のコードを使って集計してみましょう。
＊エラーになります
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 targetCol As Integer '目的の列
For i = 1 To lastCol 'for文で性別の列を検索して取得
If ws.Cells(1, i).Value = "性別" Then
targetCol = i
End If
Next i
Dim maleCnt As Integer, femaleCnt As Integer 'オス・メスのカウンタ
maleCnt = 0 'オスカウンタの初期値を0に設定
femaleCnt = 0 'メスカウンタの初期値を0に設定
For i = 2 To lastRow '2行目から最終行までfor文でループ
If ws.Cells(i, targetCol).Value = "オス" Then 'オスの時「maleCnt」をカウントアップ
maleCnt = maleCnt + 1
ElseIf ws.Cells(i, targetCol).Value = "メス" Then 'メスの時「femaleCnt」をカウントアップ
femaleCnt = femaleCnt + 1
End If
Next i
MsgBox "オスの件数：" & maleCnt & "、メスの件数：" & femaleCnt以下のエラーが出てします。原因は「性別」という文字列が見つからずに「targetCol」が取得できなかったためです。
元データの「性別」の文字列を確認します。実は「 性別」となっていて、半角スペースが先頭に入力されています。
これの対応策として「If ws.Cells(1, i).Value = “性別” Then」と完全一致で条件式を組んでいたのを「Like演算子」を使った部分一致による条件式に変更していきます。
部分一致による集計
「Like演算子」を使って以下のコードに書き換えます。部分一致にすることで、「性別」の文字を含む文字列があれば性別の列を取得するので、半角スペースや全角スペースが入っていても取得できます。
For i = 1 To lastCol 'for文で性別の列を検索して取得
If ws.Cells(1, i).Value Like "*性別*" Then
targetCol = i
End If
Next iしかしながら、上のコードで実行しても結果は下のようになり、各性別の件数が少なく表示されます。原因は、オス・メスの文字列の中にもあやしいデータがあるからです。
「ws.Cells(i, targetCol).Value Like “オス” Then」というように、オス・メスの件数を取得する際の条件式も「Like演算子」にしてみます。以下のようにコードを書き替えましょう。
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 targetCol As Integer '目的の列
For i = 1 To lastCol 'for文で性別の列を検索して取得
If ws.Cells(1, i).Value Like "*性別*" Then
targetCol = i
End If
Next i
Dim maleCnt As Integer, femaleCnt As Integer 'オス・メスのカウンタ
maleCnt = 0 'オスカウンタの初期値を0に設定
femaleCnt = 0 'メスカウンタの初期値を0に設定
For i = 2 To lastRow '2行目から最終行までfor文でループ
If ws.Cells(i, targetCol).Value Like "*オス*" Then 'オスの時「maleCnt」をカウントアップ
maleCnt = maleCnt + 1
ElseIf ws.Cells(i, targetCol).Value Like "*メス*" Then 'メスの時「femaleCnt」をカウントアップ
femaleCnt = femaleCnt + 1
End If
Next i
MsgBox "オスの件数：" & maleCnt & "、メスの件数：" & femaleCnt実行すると以下のように表示されます。オス・メスの文字を含むセルが集計できました。
全コードのまとめ
今回使用したコードを以下にまとめます。
Dim ws As Worksheet 'ワークシート1のオブジェクト変数を宣言
Set ws = ThisWorkbook.Sheets("Sheet1")
Dim pikachuArray As Variant 'ピカチュウの配列
pikachuArray = [{"CP","HP","重さ","高さ"," 性別";391,64,6.5,0.4,"bオス";576,81,"",0.32,"メスa";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 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 targetCol As Integer '目的の列
For i = 1 To lastCol 'for文で性別の列を検索して取得
If ws.Cells(1, i).Value Like "*性別*" Then
targetCol = i
End If
Next i
Dim maleCnt As Integer, femaleCnt As Integer 'オス・メスのカウンタ
maleCnt = 0 'オスカウンタの初期値を0に設定
femaleCnt = 0 'メスカウンタの初期値を0に設定
For i = 2 To lastRow '2行目から最終行までfor文でループ
If ws.Cells(i, targetCol).Value Like "*オス*" Then 'オスの時「maleCnt」をカウントアップ
maleCnt = maleCnt + 1
ElseIf ws.Cells(i, targetCol).Value Like "*メス*" Then 'メスの時「femaleCnt」をカウントアップ
femaleCnt = femaleCnt + 1
End If
Next i
MsgBox "オスの件数：" & maleCnt & "、メスの件数：" & femaleCnt最後に
集計するデータによっては、要らない空白やセル内の改行が入っていることがありますよね。そういった時にこの部分一致による集計が役立ちます。ただし、目的としない列やデータにも同じ文字列が含まれていることもあるので確認が必要です。
最初はコツが要りますが、セル内に含まれる文字列をうまく使って、目的の列やデータを取得してみましょう。