トップ > VBA・VBS > Excel VBA > 集計ボタン:範囲の合計・件数を集計表に書き出す

集計ボタン(合計・件数を集計表に書き出す)

Excel VBA(担当者別・項目別の集計をボタン1つに)

「担当者ごとに金額を合計して」と言われるたびに、並べ替えて、目で追って、電卓を叩く。この集計作業は、ボタン1つにできます。このレシピでは、データシートの行を「担当者名」のような項目ごとにまとめ、合計と件数を集計シートに書き出すマクロを作ります。押すたびに集計シートはまっさらな状態から書き直されるので、古い集計が残って二重に数える事故も起きません。

✅ これができます:ボタン1つで、項目ごとの合計・件数の集計表が最新状態に更新できるようになります

完成形(実行前 → 実行後)

実行前
実行前
実行後
実行後

実行前(左)の明細データを、ボタン1つで項目ごとの合計・件数に集計します(右)。総合計の行も自動で付きます

これをコピペすれば動きます

Option Explicit
 
' ===== 設定エリア:変えるのはここだけ =====
' ① 集計もとになるデータのシート名
Const DATA_SHEET As String = "データ"
' ② 集計結果を書き出すシート名
Const SUMMARY_SHEET As String = "集計"
' ③ グループ分けに使う列の番号(A列=1、B列=2…。例は担当者の列)
Const KEY_COL As Long = 1
' ④ 合計する金額の列の番号
Const VALUE_COL As Long = 3
' ⑤ データの見出し行の数
Const HEADER_ROWS As Long = 1
' ===== ここから下は変えなくてOK =====
 
Sub 集計する()
    Dim wsData As Worksheet, wsSum As Worksheet
    Dim dicSum As Object, dicCnt As Object
    Dim lastRow As Long, r As Long, outRow As Long
    Dim key As String, val As Variant
    Dim totalSum As Double, totalCnt As Long
    Dim k As Variant
 
    If DATA_SHEET = SUMMARY_SHEET Then
        MsgBox "①データと②集計のシート名が同じになっています。変えてください。", vbExclamation, "集計ボタン"
        Exit Sub
    End If
 
    Set dicSum = CreateObject("Scripting.Dictionary")
    Set dicCnt = CreateObject("Scripting.Dictionary")
    Set wsData = ThisWorkbook.Worksheets(DATA_SHEET)
    Set wsSum = シートを用意する(SUMMARY_SHEET)
 
    ' データを1行ずつ読み、項目ごとに合計と件数をためる
    lastRow = wsData.Cells(wsData.Rows.Count, KEY_COL).End(xlUp).Row
    For r = HEADER_ROWS + 1 To lastRow
        key = Trim(CStr(wsData.Cells(r, KEY_COL).Value))
        If key <> "" Then
            val = wsData.Cells(r, VALUE_COL).Value
            If Not IsNumeric(val) Then val = 0
            If Not dicSum.Exists(key) Then
                dicSum.Add key, 0#
                dicCnt.Add key, 0&
            End If
            dicSum(key) = dicSum(key) + CDbl(val)
            dicCnt(key) = dicCnt(key) + 1
        End If
    Next r
 
    ' 集計シートをまっさらにしてから書き出す(押すたびに最新になる)
    wsSum.Cells.Clear
    wsSum.Cells(1, 1).Value = "項目"
    wsSum.Cells(1, 2).Value = "合計"
    wsSum.Cells(1, 3).Value = "件数"
    outRow = 2
    totalSum = 0
    totalCnt = 0
    For Each k In dicSum.Keys
        wsSum.Cells(outRow, 1).Value = k
        wsSum.Cells(outRow, 2).Value = dicSum(k)
        wsSum.Cells(outRow, 3).Value = dicCnt(k)
        totalSum = totalSum + dicSum(k)
        totalCnt = totalCnt + dicCnt(k)
        outRow = outRow + 1
    Next k
    wsSum.Cells(outRow, 1).Value = "総合計"
    wsSum.Cells(outRow, 2).Value = totalSum
    wsSum.Cells(outRow, 3).Value = totalCnt
    wsSum.Activate
    MsgBox "集計が終わりました(" & dicSum.Count & " 項目)。", vbInformation, "集計ボタン"
End Sub
 
Private Function シートを用意する(ByVal sheetName As String) As Worksheet
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = sheetName Then
            Set シートを用意する = ws
            Exit Function
        End If
    Next ws
    Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    ws.Name = sheetName
    Set シートを用意する = ws
End Function

コードの黄色い部分が「設定エリア」です。変えるのは、原則ここだけです。

変えるのはここだけ

変える場所を示した図
変えるのは設定エリアの5か所だけ。番号は下の表と対応しています
番号変える場所意味書き方・例
1DATA_SHEET集計もとになるデータのシート名自分のブックのデータシートの名前に書き換えます。例:"売上明細"
2SUMMARY_SHEET集計結果を書き出すシート名無ければ自動で作られます。①と同じ名前にはしないでください(実行前に止まる作りにもなっています)
3KEY_COLグループ分けに使う列の番号A列なら 1、B列なら 2。担当者・店舗・商品区分など、まとめたい項目の列を指定します
4VALUE_COL合計する金額の列の番号金額が C列なら 3。数量を合計したい場合は数量の列番号に変えます
5HEADER_ROWSデータの見出し行の数見出しが1行なら 1。タイトル行+見出し行の2段構えなら 2 にします

組み込み手順

  1. データシートの列構成を確認し、③(グループの列)と④(金額の列)の番号を数えておきます
  2. Alt+F11 でVBAエディタを開き、「挿入」→「標準モジュール」を選びます
  3. このページのコードを貼り付け、設定エリア①〜⑤を自分のブックに書き換えます
  4. ファイルを .xlsm で保存します
  5. データのシート上にボタンを置き、このマクロ(集計する)を登録します(ボタンの作り方は「ボタンを置いてマクロを実行する」参照)。ボタンは②の集計シートには置かないでください(下の注意参照)
  6. ボタンを押し、集計シートに項目ごとの合計・件数が並べば成功です

⚠ VBAエディタの開き方:Alt+F11 で開きます。「開発」タブが出ていない場合は、ファイル→オプション→リボンのユーザー設定で「開発」にチェックを入れると出ます。

⚠ 保存形式:マクロは「Excel マクロ有効ブック(.xlsm)」で保存しないと、次に開いたときに消えています。いつもの .xlsx で保存すると、マクロだけ取り除かれます。

⚠ セキュリティ警告バー:開いたときに黄色いバーが出たら「コンテンツの有効化」を押してください。押すまではマクロは動きません。

⚠ ファイルのブロック解除:ネットから入手したファイルは、開く前に「右クリック→プロパティ→『許可する』にチェック」が先です。これをしないと、有効化のボタン自体が出ないことがあります。

⚠ 会社の設定で無効化されている場合:警告バーも出ずに無反応なときは、会社の設定でマクロが止められている可能性が高いです。設定を突破しようとせず、情シスに「業務でマクロを使いたい」と相談するのが正解です。

動かし方

データシートに置いたボタンを押すだけです。実行のたびに集計シートはまっさらな状態から書き直されるので、データが増えても・減っても、その時点の正しい集計になります。

カスタマイズ例

平均も出したい

合計と件数が出ているので、集計シート側で「合計÷件数」の列を足せば平均になります。マクロを変えなくても、集計結果に数式を組み合わせるのが簡単です。

並び順を金額の大きい順にしたい

書き出し後に集計シートを並べ替える処理を足すカスタマイズになります。まずは登場順(データに現れた順)で運用し、並び替えはExcelの機能で済ませるのも手です。

月別×担当者のような2段の集計にしたい

キーを「月_担当者」のように2つの列を組み合わせた文字列にすると、組み合わせごとの集計になります。より細かい条件集計は、関数の SUMIFS(当サイトの関数レシピ)で表を作る方法も向いています。

つまずきどころ

関連レシピ

次はこれ:条件でデータを別シートに振り分ける
集計の次は、明細そのものを担当者別・月別に分ける振り分けが便利です

よくある質問

Q. 関数(SUMIF)で作る集計表と何が違う?

A. 関数は「項目が決まっている集計」に強く、項目が増えるたびに数式を足す必要があります。この集計ボタンは、データに現れた項目を自動で拾うので、担当者や商品が増えても直すところがありません。

Q. データが何万行あっても大丈夫?

A. 1行ずつ読む作りなので、数万行でも実用範囲です。体感で遅い場合は、画面更新を止める設定を足すカスタマイズが効きます(当サイトの他のレシピでも使っている方法です)。

Q. 集計シートの見出し(項目/合計/件数)を変えたい

A. コード後半の "項目" "合計" "件数" の文字を変えれば、そのまま見出しになります。本文ロジック側ですが、文字を変えるだけなら安全な変更です。

PR(広告)

もっと体系的に学びたい人へ
自動化の幅を広げるなら、講座で体系化するのが近道です。

Udemy:Excelショートカット講座を見る