【第８回Excelマクロ】目的の列の取得と集計【ほぼfor/if文】

サンプルデータを作るため、今回はポケモンGOでピカチュウを複数捕まえてきました。ピカチュウの「CP・HP・重さ・高さ・性別」を表にして集計してみます。

まずはピカチュウを9匹捕まえたので、そのデータを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

下の表ができたら準備OKです。また、「重さ」に１つ空欄があるのは仕様です。

目的の列の取得とその集計

表から性別の行を取得してみましょう。さらに、各性別の件数も集計してみます。表を見ると5列目だと分かりますが、今回は for文を使って取得してみます。以下のコードをコピペして実行してみましょう。

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

下のメッセージが出れば成功です。

コードの解説

以下のコードで 、for文で項目名が入力されている1行目を1列目から最終列までループし、性別という文字が入力された列を取得しています。

For i = 1 To lastCol    'for文で性別の列を検索して取得
    If ws.Cells(1, i).Value = "性別" Then
        targetCol = i
    End If
Next i

その後、1行目は項目名になっているので、データのある2行目から最終行までを for文でループし、if文でオス・メスの場合で処理を分岐しています。

また、性別ごとの件数を求める際の「maleCnt = maleCnt + 1」といったコードの書き方にも注目です。最初に「maleCnt = 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

空白セルを考慮した集計

今度は、重さの平均値を求めてみましょう。平均体重は「体重の合計÷捕まえたピカチュウの数」ですね。

ただし、重さの列に空白がありますね。よって必要なデータは、「体重の合計」と「重さのデータがあるピカチュウの数」になります。今回のように空白セルを考慮したデータ集計は以下のようになります。下のコードを追記して実行してみましょう。

For i = 1 To lastCol    'for文で性別の列を検索して取得
    If ws.Cells(1, i).Value = "重さ" Then
        targetCol = i
    End If
Next i

Dim weightAverage As Single, weightTotal As Single  '平均体重・合計体重をsingle型で宣言
weightTotal = 0
Dim weightCnt As Integer    '体重のあるデータのカウンタ
weightCnt = 0   '体重カウンタの初期値を0に設定

For i = 2 To lastRow    '2行目から最終行までfor文でループ
    If ws.Cells(i, targetCol).Value <> "" Then   'セルが空白ではないとき
        weightTotal = weightTotal + ws.Cells(i, targetCol).Value    '合計体重を集計
        weightCnt = weightCnt + 1   '体重カウンタをカウントアップ
    End If
Next i

weightAvegage = weightTotal / weightCnt '平均を求める
MsgBox "合計体重:" & weightTotal & "、平均体重:" & weightAvegage

以下のメッセージが出れば成功です。また、平均体重を小数第１位を四捨五入してみましょう。

Excelで使える関数はVBAでも使用できます。今回は四捨五入で使う関数「Round」を使ってみましょう。【Application.WorksheetFunction.】と入力すると使える関数が一覧で出てくるので、関数の先頭文字の「r」と入力し、その中から四捨五入する関数【Round】を選択しましょう。以下のコードを追記して実行します。

weightAvegage = Application.WorksheetFunction.Round(weightAvegage, 1)   'ワークシート関数Roundで平均体重を小数第一位を四捨五入
MsgBox "合計体重:" & weightTotal & "、平均体重:" & weightAvegage

無事に小数第１位が四捨五入されました。

Round関数の使い方についてはこちらへ

コードの解説

重さの列を取得した後、for文で2行目から最終行までループをするまでは先ほどと同じですが、「空白ではないとき」というif文を「If ws.Cells(i, targetCol).Value <> “" Then」として追記しています。

For i = 2 To lastRow    '2行目から最終行までfor文でループ
    If ws.Cells(i, targetCol).Value <> "" Then   'セルが空白ではないとき
        weightTotal = weightTotal + ws.Cells(i, targetCol).Value    '合計体重を集計
        weightCnt = weightCnt + 1   '体重カウンタをカウントアップ
    End If
Next i

全コードのまとめ

今回のコードを１つにまとめました。

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 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

For i = 1 To lastCol    'for文で性別の列を検索して取得
    If ws.Cells(1, i).Value = "重さ" Then
        targetCol = i
    End If
Next i

Dim weightAverage As Single, weightTotal As Single  '平均体重・合計体重をsingle型で宣言
weightTotal = 0
Dim weightCnt As Integer    '体重のあるデータのカウンタ
weightCnt = 0   '体重カウンタの初期値を0に設定

For i = 2 To lastRow    '2行目から最終行までfor文でループ
    If ws.Cells(i, targetCol).Value <> "" Then   'セルが空白ではないとき
        weightTotal = weightTotal + ws.Cells(i, targetCol).Value    '合計体重を集計
        weightCnt = weightCnt + 1   '体重カウンタをカウントアップ
    End If
Next i

weightAvegage = weightTotal / weightCnt '平均を求める
MsgBox "合計体重:" & weightTotal & "、平均体重:" & weightAvegage

weightAvegage = Application.WorksheetFunction.Round(weightAvegage, 1)   'ワークシート関数Roundで平均体重を小数第一位を四捨五入

MsgBox "合計体重:" & weightTotal & "、平均体重:" & weightAvegage

最後に

今回のデータは9つしかありませんでしたが、上記のコードをコピペすれば100や1000、10万くらいのデータも処理できるでしょう。学校や職場に似たようなデータがあれば、ぜひ応用して集計してみましょう。

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

ExcelExcelマクロ

Posted by プーレ