第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 ・・・新着記事一覧を見る
シート関数のCOUNTIFS,SUMIFS,MAXIFSと同じ処理｜Power Query（M言語）入門(2023-02-28)
新旧マスタの差異比較｜Power Query（M言語）入門(2023-02-28)
有効な最新単価の取得｜Power Query（M言語）入門(2023-02-26)
有効な最新単価の取得｜Power Query（M言語）入門(2023-02-21)
グルーブ内の最小・最大｜Power Query（M言語）入門(2023-02-17)
2つのテーブルのマージ｜Power Query（M言語）入門(2023-02-15)
「売上」が数値の行のみ取り込む｜Power Query（M言語）入門(2023-02-13)
A列のヘッダー名を変更する｜Power Query（M言語）入門(2023-02-11)
CSVのA列が日付の行だけを取り込む｜Power Query（M言語）入門(2023-02-10)
列数不定のCSVの取り込み｜Power Query（M言語）入門(2023-02-09)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.RangeとCellsの使い方｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.繰り返し処理（For Next)｜VBA入門
5.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
6.マクロって何？VBAって何？｜VBA入門
7.並べ替え（Sort）｜VBA入門
8.エクセルVBAでのシート指定方法｜VBA技術解説
9.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門
10.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。