第28回.月別ブックより部署別シートに担当別に集計するNo4
マクロ再入門の課題として、
月別ブックより部署別シートに担当別に集計する、
部署別のデータに集計する、最も頭を悩ます、少々複雑な処理になります。
シート「ファイル一覧」に、
サブフォルダ「月別データ」内の全Excelファイルの一覧を取得
シート「データ」に、
ファイル一覧で取得したExcelファイルの先頭シートのデータ集める。
シート「データ」より、
部署別のシートに、担当者・年月ごとの集計値を出力する。
方法1
データの先頭行から順に処理していき、
部署が変わったらシートを作成
担当が同じ間は集計し、担当が変わったら出力
フィルタの詳細設定を使い、
部署の一覧と、
部署、担当の重複のない一覧を作成
Sumifs関数を使い部署、担当で集計する。
部署の一覧をもとに、シートを作成しながら、
当該部署で、部署、担当で集計した表をフィルタしてからコピーする。
今回は、
・部署別作成
の
・方法2
この部分の解説になります。
Sub 部署別作成2()
Dim i As Long
Dim wsデータ As Worksheet
Dim ws集計 As Worksheet
Dim ws部署別 As Worksheet
Dim wb As Workbook
Set wsデータ = Worksheets("データ")
Set ws集計 = Worksheets("集計")
Set ws部署別 = Worksheets("部署別")
With wsデータ
wsデータ.Range("A1").Sort key1:=wsデータ.Range("B1"), order1:=xlAscending, _
key2:=wsデータ.Range("C1"), order2:=xlAscending, _
key3:=wsデータ.Range("A1"), order3:=xlAscending, _
Header:=xlYes, SortMethod:=xlStroke
.Columns("D").Insert
.Range("D1") = "年月"
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(i, 4) = Format(.Cells(i, 1), "yyyymm")
Next
End With
With ws集計
.Cells.Clear
wsデータ.Columns("B").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("A1"),
Unique:=True
wsデータ.Columns("B:D").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("C1"), Unique:=True
.Range("F1") = "売上数"
.Range("G1") = "売上金額"
For i = 2 To .Cells(.Rows.Count, 3).End(xlUp).Row
.Cells(i, 6) = WorksheetFunction.SumIfs(wsデータ.Columns(5), _
wsデータ.Columns(2), .Cells(i, 3), _
wsデータ.Columns(3), .Cells(i, 4), _
wsデータ.Columns(4), .Cells(i, 5))
.Cells(i, 7) = WorksheetFunction.SumIfs(wsデータ.Columns(6), _
wsデータ.Columns(2), .Cells(i, 3), _
wsデータ.Columns(3), .Cells(i, 4), _
wsデータ.Columns(4), .Cells(i, 5))
Next
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
ws部署別.Range("A1").CurrentRegion.Offset(1).ClearContents
.Range("C1").AutoFilter field:=1, Criteria1:=.Cells(i, 1)
.Range("C1").CurrentRegion.Offset(1, 1).Copy Destination:=ws部署別.Range("A2")
If i = 2 Then
ws部署別.Copy
Set wb = ActiveWorkbook
ActiveSheet.Name = .Cells(i, 1)
Else
ws部署別.Copy after:=wb.Worksheets(Worksheets.Count)
ActiveSheet.Name = .Cells(i, 1)
End If
Next
.AutoFilterMode = False
End With
Application.DisplayAlerts = False
wb.SaveAs ThisWorkbook.Path & "\結果2.xlsx"
wb.Close savechanges:=True
Application.DisplayAlerts = True
End Sub
少々難解なコードではありますが、方法1よりは理解しやすい内容です。
・部署、担当、日付の昇順で並べ替え
・D列に作業列挿入し、「年月」を作成
・AdvancedFilter(フィルタオプションの設定)で部署のユニーク(一意)なデータを"集計"シートのA列に出力
・AdvancedFilter(フィルタオプションの設定)で部署・担当・年月のユニーク(一意)なデータを"集計"シートのC列以降に出力
・"集計"シートのF列とG列に部署・担当・年月で売上数と売上金額を集計
・"集計"シートのA列2行目から最終行まで処理（A列は部署のユニークなデータ）
・"集計"シートのC列以降を、部署でオートフィルタ
・"集計"シートをコピーして部署のシートを作成
2行目の時は、新規ブックにコピー、3行目以降の時は作成済みの新規ブックにコピー
・新規ブックを名前を付けて保存
AdvancedFilter(フィルタオプションの設定)
ここが初めての登場です。
フィルタオプションの設定
リボンの「データ」タブ→フィルタ内の「詳細設定」
コードの詳細は、
マクロVBA入門「第90回.フィルタオプションの設定（AdvancedFilter）」
手動でも、ちょっと面倒な操作です。
ユニークなデータを出力するシートを選択してから、「フィルタオプションの設定」を起動します。
ついつい元データのシートで、「フィルタオプションの設定」を起動してしまいがちですが、それでは上手くできません。
本来は、手動で出来なければマクロは使わない方が良いのですが、
AdvancedFilterに関しては、このように書けば、ユニークなデータが取得できると割り切っても良いかもしれません。
今回のVBAコードは、まさしく手操作をそのままVBAにしたものです。
作業列の追加、ユニークデータの作成、SumIfsでの集計、オートフィルタでの絞り込み、シートのコピー
マクロを1行動かすごとに、これらが見て取れるはずです。
VBAコードと実際のシートの動きを対比させながら、じっくりと読み解いてください。
同じテーマ「マクロVBA再入門」の記事
第20回.全てのシートに同じ事をする（For～Worksheets.Count）
第21回.ファイル一覧を取得する（Do～LoopとDir関数）
第22回.複数ブックよりデータを集める
第23回.複数のプロシージャーを連続で動かす（Callステートメント）
第24回.マクロの呪文を追加してボタンに登録（ScreenUpdating）
第25回.月別ブックより部署別シートに担当別に集計するNo1
第26回.月別ブックより部署別シートに担当別に集計するNo2
第27回.月別ブックより部署別シートに担当別に集計するNo3
第28回.月別ブックより部署別シートに担当別に集計するNo4
第29回.月別ブックより部署別シートに担当別に集計するNo5
第30回.今後の覚えるべきことについて
新着記事NEW ・・・新着記事一覧を見る
第5章：AI×VBAでつまづかない！トラブルシューティングとAIとの付き合い方
｜生成AI活用研究(2025-05-20)
第4章：【事例で学ぶ】AIとVBAでExcel作業を劇的に効率化する！
｜生成AI活用研究(2025-05-20)
第3章：AIを「自分だけのVBA先生」にする！質問・相談の超実践テクニック｜生成AI活用研究(2025-05-19)
第2章 VBAって怖くない！Excelを「言葉で動かす」（超入門）｜生成AI活用研究(2025-05-18)
第1章：AIって一体何？あなたのExcel作業をどう変える？（AI超基本）｜生成AI活用研究(2025-05-18)
AI時代のExcel革命：AI×VBAで“書かない自動化”超入門｜生成AI活用研究(2025-05-17)
Geminiと100本ノック 23本目：シート構成の一致確認｜生成AI活用研究（5月16日）
AIが問う出版の未来は淘汰か進化か：AIと書籍の共存の道とは｜生成AI活用研究(2025-05-16)
Geminiと100本ノック 22本目：FizzBuzz発展問題｜生成AI活用研究（5月15日）
すぐに使える！生成AI プロンプト作成 実践ガイド｜生成AI活用研究(2025-05-15)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.繰り返し処理（For Next)｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
5.ひらがな⇔カタカナの変換｜エクセル基本操作
6.RangeとCellsの使い方｜VBA入門
7.メッセージボックス(MsgBox関数)｜VBA入門
8.セルのクリア（Clear,ClearContents）｜VBA入門
9.FILTER関数（範囲をフィルター処理）｜エクセル入門
10.条件分岐（Select Case）｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。