第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 ・・・新着記事一覧を見る
イータ縮小ラムダ（eta reduced lambda）｜エクセル入門(2023-11-20)
PIVOTBY関数（縦軸と横軸でグループ化して集計）｜エクセル入門(2023-11-19)
GROUPBY関数（縦軸でグループ化して集計）｜エクセル入門(2023-11-18)
PY関数（Pythonコードをセル内で実行）｜エクセル入門(2023-11-17)
画像「セルに配置」のVBAについて（365の新機能）｜VBA技術解説(2023-11-13)
スピルのゴーストの範囲を選択するVBA｜ツイッター出題回答 (2023-10-31)
エクセル試験3：月間の所定労働時間｜エクセル練習問題(2023-10-04)
エクセル試験2：所得税の計算｜エクセル練習問題(2023-10-04)
エクセル試験1：曜日別の平均客単価｜エクセル練習問題(2023-10-04)
列全体を指定する時のRangeとColumnsの違い｜ツイッター出題回答 (2023-09-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.RangeとCellsの使い方｜VBA入門
3.繰り返し処理（For Next)｜VBA入門
4.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
5.変数宣言のDimとデータ型｜VBA入門
6.条件分岐（IF）｜VBA入門
7.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
8.並べ替え（Sort）｜VBA入門
9.マクロとは？VBAとは？VBAでできること｜VBA入門
10.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。