第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)
第28回.月別ブックより部署別シートに担当別に集計するNo4
新着記事NEW ・・・新着記事一覧を見る
TRIMRANGE関数(セル範囲をトリム:端の空白セルを除外)|エクセル入門(2024-08-30)
正規表現関数(REGEXTEST,REGEXREPLACE,REGEXEXTRACT)|エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない|エクセル雑感(2024-04-11)
ブール型(Boolean)のis変数・フラグについて|VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する|VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.RangeとCellsの使い方|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.セルのクリア(Clear,ClearContents)|VBA入門
8.条件分岐(Select Case)|VBA入門
9.メッセージボックス(MsgBox関数)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。