第27回.月別ブックより部署別シートに担当別に集計するNo3
マクロ再入門の課題として、
月別ブックより部署別シートに担当別に集計する、
最も頭を悩ます、部署別のデータに集計します、少々複雑な処理になります。
シート「ファイル一覧」に、
サブフォルダ「月別データ」内の全Excelファイルの一覧を取得
シート「データ」に、
ファイル一覧で取得したExcelファイルの先頭シートのデータ集める。
シート「データ」より、
部署別のシートに、担当者・年月ごとの集計値を出力する。
方法1
データの先頭行から順に処理していき、
部署が変わったらシートを作成
担当が同じ間は集計し、担当が変わったら出力
フィルタの詳細設定を使い、
部署の一覧と、
部署、担当の重複のない一覧を作成
Sumifs関数を使い部署、担当で集計する。
部署の一覧をもとに、シートを作成しながら、
当該部署で、部署、担当で集計した表をフィルタしてからコピーする。
今回は、
・部署別作成
の
・方法1
この部分の解説になります。
Sub 部署別作成1()
Dim i As Long
Dim i部署 As Long
Dim total数 As Long
Dim total金 As Long
Dim wb As Workbook
Dim ws As Worksheet
Dim wsデータ As Worksheet
Set wsデータ = Worksheets("データ")
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
Worksheets("部署別").Copy
Set wb = ActiveWorkbook
For i = 2 To wsデータ.Cells(wsデータ.Rows.Count, 1).End(xlUp).Row
If wsデータ.Cells(i - 1, 3) <> wsデータ.Cells(i, 3) Or _
Format(wsデータ.Cells(i - 1, 1), "yyyymm") <> Format(wsデータ.Cells(i, 1), "yyyymm") Then
If i > 2 Then
ws.Cells(i部署, 1) = wsデータ.Cells(i - 1, 3)
ws.Cells(i部署, 2) = Format(wsデータ.Cells(i - 1, 1), "yyyymm")
ws.Cells(i部署, 3) = total数
ws.Cells(i部署, 4) = total金
i部署 = i部署 + 1
total数 = 0
total金 = 0
End If
End If
If wsデータ.Cells(i - 1, 2) <> wsデータ.Cells(i, 2) Then
wb.Worksheets(1).Copy after:=wb.Worksheets(wb.Worksheets.Count)
Set ws = wb.ActiveSheet
ws.Name = wsデータ.Cells(i, 2)
i部署 = 2
End If
total数 = total数 + wsデータ.Cells(i, 4)
total金 = total金 + wsデータ.Cells(i, 5)
Next
ws.Cells(i部署, 1) = wsデータ.Cells(i - 1, 3)
ws.Cells(i部署, 2) = Format(wsデータ.Cells(i - 1, 1), "yyyymm")
ws.Cells(i部署, 3) = total数
ws.Cells(i部署, 4) = total金
Application.DisplayAlerts = False
wb.Worksheets(1).Delete
wb.SaveAs ThisWorkbook.Path & "\結果1.xlsx"
wb.Close savechanges:=True
Application.DisplayAlerts = True
End Sub
少々難解なコードになっています。
・部署、担当、日付の昇順で並べ替え
・"部署別"シートをコピーして新規ブックを作成
・2行目から最終行まで処理
・1行上と比較して、担当または年月が変わっていたら
・セルに出力、ただし3行目以降の場合
・出力行位置を1加算
・加算用の変数を0にする
・1行上と比較して、部署が変わっていたら
・"部署別"シートを新規ブックにコピー
・コピーされたシート名を部署名にする
・出力行位置を2にする
・売上数と売上金額を、それぞれの変数に加算する
・最終行のデータを出力（変数に加算したままで未出力なので）
・新規ブックの先頭シートは不要なので削除
・新規ブックを名前を付けて保存
ステップイン(F8)で、変数の中身を確認しつつ、1ステップごとに確認しながら見ていくようにして下さい。
・1行上と比較して、担当または年月が変わっていたら
・1行上と比較して、部署が変わっていたら
この部分でしょう。
部署が変わったら、"部署別"シートを新規ブックにコピーし、そのシートを変数wsに入れます。
次の部署になるまでは、変数wsのシートに、担当・年月の合計を出力しています。
今操作しているシートはどれか
今の行数は何か
これらをしっかりと確認してください。
同じテーマ「マクロ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 ・・・新着記事一覧を見る
新旧マスタの差異比較｜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)
別ブックの最終シートの取り込み｜Power Query（M言語）入門(2023-02-08)
アクセスランキング ・・・ ランキング一覧を見る
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.Excelショートカットキー一覧｜Excelリファレンス
9.エクセルVBAでのシート指定方法｜VBA技術解説
10.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。