【EXCEL：VBA】キー項目を指定して集計したい

この記事では、EXCELでキー項目を指定して、特定の項目を集計して、キー項目ごとの合計を求める方法について説明しています。

EXCELの関数を使えば、いろいろな合計を求めることができますが、数式を変えるのに結構時間もかかるし、ミスをしてしまいます。

今回ご紹介するツールを一度作成しておけば、データを貼り替えて、キー項目と合計したい項目を指定するだけで、何度でも合計を求めることができます。

合計する範囲を指定する必要もないので、範囲指定ミスをすることもありません。

それでは、EXCELでキー項目を指定して集計するツールを作成していきましょう。

困っている女性

毎月、いろいろなデータを集計するのが地味に大変なの・・・、わかる！？

困っている女性

しかも、1つのデータでもいろいろなキー項目で集計しないといけないの。

困っている女性

SUMIF関数を使えば集計できるけど、よく集計範囲を間違えたりするの・・・。

それなら、VBAを使えばボタン1つ押すだけで、キー項目を指定して集計することができるよ。

解決する男性

しかも、キー項目と集計したい項目を簡単に切替えるだけ、いろいろな集計ができるよ。

解決する男性

困っている女性

それそれ、早く教えてよ。

◆合計する関数については、以下の記事を参照してください。

【EXCEL：SUM関数】入力されている数値を合計したい

【EXCEL：SUM関数】単価と数量から小計や合計を計算したい

【EXCEL：SUMIF関数】条件を指定して合計を求めたい

【EXCEL：SUMIFS関数】複数条件を指定して合計を求めたい

キー項目を指定して集計するツールに必要なものを、1つ1つ順番に説明していきます。

１．キー列の指定欄、合計列の指定欄を準備する

まず、キー列を指定する欄と合計列を指定する欄、キー列の項目名を表示する欄と合計列の項目名を表示する欄を作成します。

「データ集計」シートを作成する

データ集計シートを作成する

「データ集計」という名前のシートを作成します。
1行目に「キー列」と「合計列」のタイトル行を作成します。
2行目に「キー列」と「合計列」を入力する欄を作成します。
3行目に「キー列」と「合計列」の項目名を表示する欄を作成します。
4行目以降は、各キー項目と合計結果を表示する欄になります。

２．集計したいデータを準備する

それでは、集計したいデータを準備しましょう。

他のEXCELで作成されているデータなら貼り付けましょう。

「データ」シートを作成する

データシートを準備する

上記の画像サンプルでは、各日付に担当者が売り上げた商品の一覧を用意しました。

例えば、担当者毎の売上金額を求めたり、日付毎の売上金額を求めたり、商品別の売上金額を求めたりシたい場合にこのツールは利用できます。

ここで用意するデータは、あなたが集計したいデータで良いです。

注意点としては、１行目に項目名があり、２行目以降は各列の値が入力されているデータが必要ということです。

３．VBAでキー項目を指定して集計する機能を記入する

「Microsoft Visual Basic for Applications」を起動します。

VBAプログラムを記入する（その１）

VBAプログラムを記入する（その２）

標準モジュールを追加して、以下のソースを書きます。

Public Sub MainProc()
    Dim shtMain As Worksheet
    Dim shtData As Worksheet
    Dim keyCol As String
    Dim sumCol As String
    Dim nowRow As Long
    Dim i As Long
    
    '①「データ集計」シートを変数に格納する
    Set shtMain = ThisWorkbook.Sheets("データ集計")
    
    '②「データ」シートを変数に格納する
    Set shtData = ThisWorkbook.Sheets("データ")
        
    '③「データ集計」シートの初期化
    shtMain.Range("A3:B10000").ClearContents
        
    '④キー列を変数に格納する
    keyCol = shtMain.Range("A2")
    
    '⑤合計列を変数に格納する
    sumCol = shtMain.Range("B2")
        
    '⑥キー列のヘッダー名をセットする
    shtMain.Range("A3") = shtData.Range(keyCol & "1")
        
    '⑦合計列のヘッダー名をセットする
    shtMain.Range("B3") = shtData.Range(sumCol & "1")
        
    nowRow = 1
    
    Do While True
        
        '⑧「データ」シートの現在行を次の行に変更する
        nowRow = nowRow + 1
        
        '⑨キー列が空なら処理を抜ける
        If shtData.Range(keyCol & nowRow) = "" Then
            Exit Do
        End If
        
        i = 3
        Do While True
            
            '⑩「データ集計」シートの現在行を次の行に変更する
            i = i + 1
        
            '⑪「データ集計」シートのキー列の現在行が空の場合、キーと合計値をセットする
            If shtMain.Range("A" & i) = "" Then
                shtMain.Range("A" & i).NumberFormatLocal = shtData.Range(keyCol & nowRow).NumberFormatLocal
                shtMain.Range("A" & i) = shtData.Range(keyCol & nowRow)
                shtMain.Range("B" & i) = shtData.Range(sumCol & nowRow)
                Exit Do
            End If
            
            '⑫「データ集計」シートのキー列の現在行の値と同じ場合、合計値を加算する
            If shtMain.Range("A" & i) = shtData.Range(keyCol & nowRow) Then
                shtMain.Range("B" & i) = shtMain.Range("B" & i) + shtData.Range(sumCol & nowRow)
                Exit Do
            End If
            
        Loop
    Loop
    
    MsgBox "完了"
End Sub

Public Sub MainProc()

Dim shtMain As Worksheet

Dim shtData As Worksheet

Dim keyCol As String

Dim sumCol As String

Dim nowRow As Long

Dim i As Long

'①「データ集計」シートを変数に格納する

Set shtMain = ThisWorkbook.Sheets("データ集計")

'②「データ」シートを変数に格納する

Set shtData = ThisWorkbook.Sheets("データ")

'③「データ集計」シートの初期化

shtMain.Range("A3:B10000").ClearContents

'④キー列を変数に格納する

keyCol = shtMain.Range("A2")

'⑤合計列を変数に格納する

sumCol = shtMain.Range("B2")

'⑥キー列のヘッダー名をセットする

shtMain.Range("A3") = shtData.Range(keyCol & "1")

'⑦合計列のヘッダー名をセットする

shtMain.Range("B3") = shtData.Range(sumCol & "1")

nowRow = 1

Do While True

'⑧「データ」シートの現在行を次の行に変更する

nowRow = nowRow + 1

'⑨キー列が空なら処理を抜ける

If shtData.Range(keyCol & nowRow) = "" Then

Exit Do

End If

i = 3

Do While True

'⑩「データ集計」シートの現在行を次の行に変更する

i = i + 1

'⑪「データ集計」シートのキー列の現在行が空の場合、キーと合計値をセットする

If shtMain.Range("A" & i) = "" Then

shtMain.Range("A" & i).NumberFormatLocal = shtData.Range(keyCol & nowRow).NumberFormatLocal

shtMain.Range("A" & i) = shtData.Range(keyCol & nowRow)

shtMain.Range("B" & i) = shtData.Range(sumCol & nowRow)

Exit Do

End If

'⑫「データ集計」シートのキー列の現在行の値と同じ場合、合計値を加算する

If shtMain.Range("A" & i) = shtData.Range(keyCol & nowRow) Then

shtMain.Range("B" & i) = shtMain.Range("B" & i) + shtData.Range(sumCol & nowRow)

Exit Do

End If

Loop

MsgBox "完了"

End Sub

４．キー項目を指定して集計する機能の使い方

それでは、キー項目を指定集計する方法を説明します。

まず、A2セルに「データ」シートのキーとなる列を入力します。

以下の画像では、「B」を指定しています。つまり、「データ」シートの担当者を指定しています。

次に、B2セルに「データ」シートの合計したい列を入力します。

以下の画像では、「F」を指定しています。つまり、「データ」シートの金額を指定しています。

集計機能を動かすと、担当者毎の金額合計が集計されます。

集計機能の動かし方

次に、「開発」タブの「マクロ」をクリックします。

実行ボタンをクリックする

先程作成した自作関数「MainProc」が一覧に表示されていますので、選択します。

次に、「実行」ボタンをクリックして集計を開始します。

少し待つと、集計結果が４行目以降に表示されます。

担当者毎の合計金額が表示される

どうですか、簡単でしょ。

合計する範囲は指定していません。

キー列と合計列を指定して、マクロを実行しただけです。

それでは、キー列と合計列を変えて、いろいろな集計をやってみます。

次の画像では、日付別の金額合計を求めています。

日付別の金額合計を求める

次の画像では、商品別の合計金額を求めています。

商品別の合計金額を求める

次の画像では、商品別の数量合計を求めています。

商品別の数量合計を求める

いかがですか、キー列と合計列の指定を変えるだけで、いろいろな集計をすることができます。

もっと大量のデータでも、同じく簡単ですのでやってみてください。

５．キー項目を指定して集計するVBAプログラムの説明

それでは、VBAプログラムを説明します。

①「データ集計」シートを変数に格納する

'①「データ集計」シートを変数に格納する
Set shtMain = ThisWorkbook.Sheets("データ集計")

1 2	'①「データ集計」シートを変数に格納する Set shtMain = ThisWorkbook.Sheets("データ集計")

ここでは、このあとの処理で何度も使う「データ集計」シートを変数に格納しています。

そうすることにより、入力するプログラムを短くすることができるからです。

何度も「ThisWorkbook.Sheets(“データ集計”)」と入力するより、「shtMain」と入力するほうが少なくてすむからです。

②「データ」シートを変数に格納する

'②「データ」シートを変数に格納する
Set shtData = ThisWorkbook.Sheets("データ")

1 2	'②「データ」シートを変数に格納する Set shtData = ThisWorkbook.Sheets("データ")

ここでは、このあとの処理で何度も使う「データ」シートを変数に格納しています。

③「データ集計」シートの初期化を行う

'③「データ集計」シートの初期化
shtMain.Range("A3:B10000").ClearContents

1 2	'③「データ集計」シートの初期化 shtMain.Range("A3:B10000").ClearContents

ここでは、「データ集計」シートの集計結果を表示する部分をクリアしています。

そうすることにより、前回実行した結果をキレイに消し去ることができます。

毎回新たな気分で集計ができます。

④キー列を変数に格納する

'④キー列を変数に格納する
keyCol = shtMain.Range("A2")

1 2	'④キー列を変数に格納する keyCol = shtMain.Range("A2")

ここでは、セルA2に入力されているキー列を変数に格納しています。

このあとの処理で、利用するからです。

⑤合計列を変数に格納する

'⑤合計列を変数に格納する
sumCol = shtMain.Range("B2")

1 2	'⑤合計列を変数に格納する sumCol = shtMain.Range("B2")

ここでは、セルB2に入力されている合計列を変数に格納しています。

このあとの処理で、利用するからです。

⑥キー列のヘッダー名をセットする

'⑥キー列のヘッダー名をセットする
shtMain.Range("A3") = shtData.Range(keyCol & "1")

1 2	'⑥キー列のヘッダー名をセットする shtMain.Range("A3") = shtData.Range(keyCol & "1")

ここでは、セルA2に入力されていたキー列を利用して、「データ」シートのキー列の１行目の値（ヘッダー名）を取得し、「データ集計」シートのセルA3にセットしています。

⑦合計列のヘッダー名をセットする

'⑦合計列のヘッダー名をセットする
shtMain.Range("B3") = shtData.Range(sumCol & "1")

1 2	'⑦合計列のヘッダー名をセットする shtMain.Range("B3") = shtData.Range(sumCol & "1")

ここでは、セルB2に入力されていた合計列を利用して、「データ」シートの合計列の１行目の値（ヘッダー名）を取得し、「データ集計」シートのセルB3にセットしています。

⑧「データ」シートの現在行を次の行に変更する

'⑧「データ」シートの現在行を次の行に変更する
nowRow = nowRow + 1

1 2	'⑧「データ」シートの現在行を次の行に変更する nowRow = nowRow + 1

ここでは、変数「nowRow」に格納されている値+1を、変数「nowRow」に格納しています。

つまり、nowRowの値を+1しています。

nowRowには最初1がセットされていますので、+1されて2が格納されます。

「データ」シートの2行目から集計する必要があるので、最初に1をセットしています。

⑨キー列が空なら処理を抜ける

'⑨キー列が空なら処理を抜ける
If shtData.Range(keyCol & nowRow) = "" Then
    Exit Do
End If

'⑨キー列が空なら処理を抜ける

If shtData.Range(keyCol & nowRow) = "" Then

Exit Do

End If

ここでは、キー列の値が入力されているかチェックしています。

２行目から１行ずつ値が入力されていない行まで繰り返しチェックし、空の行がみつかると集計処理を終わらせます。

この処理があるおかげで、データ集計範囲を指定せずに、集計することを実現しています。

⑩「データ集計」シートの現在行を次の行に変更する

'⑩「データ集計」シートの現在行を次の行に変更する
i = i + 1

1 2	'⑩「データ集計」シートの現在行を次の行に変更する i = i + 1

ここでは、変数「i」に格納されている値+1を、変数「i」に格納しています。

つまり、iの値を+1しています。

iには最初3がセットされていますので、+1されて4が格納されます。

「データ集計」シートの4行目から集計結果を書き込む必要があるので、最初に3をセットしています。

⑪「データ集計」シートのキー列の現在行が空の場合、キーと合計値をセットする

'⑪「データ集計」シートのキー列の現在行が空の場合、キーと合計値をセットする
If shtMain.Range("A" & i) = "" Then
    shtMain.Range("A" & i).NumberFormatLocal = shtData.Range(keyCol & nowRow).NumberFormatLocal
    shtMain.Range("A" & i) = shtData.Range(keyCol & nowRow)
    shtMain.Range("B" & i) = shtData.Range(sumCol & nowRow)
    Exit Do
End If

'⑪「データ集計」シートのキー列の現在行が空の場合、キーと合計値をセットする

If shtMain.Range("A" & i) = "" Then

shtMain.Range("A" & i).NumberFormatLocal = shtData.Range(keyCol & nowRow).NumberFormatLocal

shtMain.Range("A" & i) = shtData.Range(keyCol & nowRow)

shtMain.Range("B" & i) = shtData.Range(sumCol & nowRow)

Exit Do

End If

ここでは、「データ集計」シートのキー列を書き込みする現在行のA列が空かチェックしています。

「データ集計」シートの4行目から下に向かってチェックします。

まずセルA4セルを確認して空なら、以下の処理を行います。

「データ」シートの該当行のキー列の表示形式を、「データ集計」シートのセルA4にセット
「データ」シートの該当行のキー列の値を、「データ集計」シートのセルA4にセット
「データ」シートの該当行の合計列の値を、「データ集計」シートのセルB4にセット
ループ処理を抜ける

つまり、「データ集計」シートのキー列をセットする列に、「データ」シートのキー列の値がすでに存在するかチェックしています。

空のセルが見つかるということは、まだ「データ集計」シートにセットされていないということがわかります。

なので、キー列の値と合計列の値を「データ集計」シートにセットします。

また、キー列の表示形式をセットしているのは、キー列の値が文字列でも日付でも正しく表示させるためです。

⑫「データ集計」シートの現在行を次の行に変更する

'⑫「データ集計」シートのキー列の現在行の値と同じ場合、合計値を加算する
If shtMain.Range("A" & i) = shtData.Range(keyCol & nowRow) Then
    shtMain.Range("B" & i) = shtMain.Range("B" & i) + shtData.Range(sumCol & nowRow)
    Exit Do
End If

'⑫「データ集計」シートのキー列の現在行の値と同じ場合、合計値を加算する