トップ > 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
コードの黄色い部分が「設定エリア」です。変えるのは、原則ここだけです。

| 番号 | 変える場所 | 意味 | 書き方・例 |
|---|---|---|---|
| 1 | DATA_SHEET | 集計もとになるデータのシート名 | 自分のブックのデータシートの名前に書き換えます。例:"売上明細" |
| 2 | SUMMARY_SHEET | 集計結果を書き出すシート名 | 無ければ自動で作られます。①と同じ名前にはしないでください(実行前に止まる作りにもなっています) |
| 3 | KEY_COL | グループ分けに使う列の番号 | A列なら 1、B列なら 2。担当者・店舗・商品区分など、まとめたい項目の列を指定します |
| 4 | VALUE_COL | 合計する金額の列の番号 | 金額が C列なら 3。数量を合計したい場合は数量の列番号に変えます |
| 5 | HEADER_ROWS | データの見出し行の数 | 見出しが1行なら 1。タイトル行+見出し行の2段構えなら 2 にします |
⚠ VBAエディタの開き方:Alt+F11 で開きます。「開発」タブが出ていない場合は、ファイル→オプション→リボンのユーザー設定で「開発」にチェックを入れると出ます。
⚠ 保存形式:マクロは「Excel マクロ有効ブック(.xlsm)」で保存しないと、次に開いたときに消えています。いつもの .xlsx で保存すると、マクロだけ取り除かれます。
⚠ セキュリティ警告バー:開いたときに黄色いバーが出たら「コンテンツの有効化」を押してください。押すまではマクロは動きません。
⚠ ファイルのブロック解除:ネットから入手したファイルは、開く前に「右クリック→プロパティ→『許可する』にチェック」が先です。これをしないと、有効化のボタン自体が出ないことがあります。
⚠ 会社の設定で無効化されている場合:警告バーも出ずに無反応なときは、会社の設定でマクロが止められている可能性が高いです。設定を突破しようとせず、情シスに「業務でマクロを使いたい」と相談するのが正解です。
データシートに置いたボタンを押すだけです。実行のたびに集計シートはまっさらな状態から書き直されるので、データが増えても・減っても、その時点の正しい集計になります。
合計と件数が出ているので、集計シート側で「合計÷件数」の列を足せば平均になります。マクロを変えなくても、集計結果に数式を組み合わせるのが簡単です。
書き出し後に集計シートを並べ替える処理を足すカスタマイズになります。まずは登場順(データに現れた順)で運用し、並び替えはExcelの機能で済ませるのも手です。
キーを「月_担当者」のように2つの列を組み合わせた文字列にすると、組み合わせごとの集計になります。より細かい条件集計は、関数の SUMIFS(当サイトの関数レシピ)で表を作る方法も向いています。
次はこれ:条件でデータを別シートに振り分ける
集計の次は、明細そのものを担当者別・月別に分ける振り分けが便利です
A. 関数は「項目が決まっている集計」に強く、項目が増えるたびに数式を足す必要があります。この集計ボタンは、データに現れた項目を自動で拾うので、担当者や商品が増えても直すところがありません。
A. 1行ずつ読む作りなので、数万行でも実用範囲です。体感で遅い場合は、画面更新を止める設定を足すカスタマイズが効きます(当サイトの他のレシピでも使っている方法です)。
A. コード後半の "項目" "合計" "件数" の文字を変えれば、そのまま見出しになります。本文ロジック側ですが、文字を変えるだけなら安全な変更です。