【第１０回Excelマクロ】部分一致による集計【ほぼfor/if文】

今回は、文字列に〇〇という文字が入っていたら集計する…といった「部分一致による集計」をしていきます。

今回も以下のデータを準備したので、コピペして実行して下さい。

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

最後に

集計するデータによっては、要らない空白やセル内の改行が入っていることがありますよね。そういった時にこの部分一致による集計が役立ちます。ただし、目的としない列やデータにも同じ文字列が含まれていることもあるので確認が必要です。

最初はコツが要りますが、セル内に含まれる文字列をうまく使って、目的の列やデータを取得してみましょう。

【Excelマクロ】記事のまとめ【ほぼfor/if文】

ExcelExcelマクロ

Posted by プーレ