VBA再入門
月別ブックより部署別シートに担当別に集計するNo3

マクロが覚えられないという初心者向けに理屈抜きのやさしい解説
公開日:2015-12-08 最終更新日:2016-03-16

第27回.月別ブックより部署別シートに担当別に集計するNo3


マクロ再入門の課題として、
月別ブックより部署別シートに担当別に集計する、
最も頭を悩ます、部署別のデータに集計します、少々複雑な処理になります。


全体の処理手順は以下になります。

■処理手順

・ファイル一覧
シート「ファイル一覧」に、
サブフォルダ「月別データ」内の全Excelファイルの一覧を取得

・データ収集
シート「データ」に、
ファイル一覧で取得したExcelファイルの先頭シートのデータ集める。

・部署別作成
シート「データ」より、
部署別のシートに、担当者・年月ごとの集計値を出力する。
方法1
データを、 部署、担当、日付で並べ替える。
データの先頭行から順に処理していき、
部署が変わったらシートを作成
担当が同じ間は集計し、担当が変わったら出力
方法2
データを、 部署、担当、日付で並べ替える。
フィルタの詳細設定を使い、
部署の一覧と、
部署、担当の重複のない一覧を作成
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にする
 ・売上数と売上金額を、それぞれの変数に加算する
・最終行のデータを出力(変数に加算したままで未出力なので)
・新規ブックの先頭シートは不要なので削除
・新規ブックを名前を付けて保存


個別のVBAコードは、既に解説しているものばかりです。
ステップイン(F8)で、変数の中身を確認しつつ、1ステップごとに確認しながら見ていくようにして下さい。

理解しずらい部分は、
・1行上と比較して、担当または年月が変わっていたら
・1行上と比較して、部署が変わっていたら
この部分でしょう。
部署が変わったら、"部署別"シートを新規ブックにコピーし、そのシートを変数wsに入れます。
次の部署になるまでは、変数wsのシートに、担当・年月の合計を出力しています。

ステップイン(F8)で確認する時、
今操作しているシートはどれか
今の行数は何か
これらをしっかりと確認してください。



同じテーマ「マクロ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 ・・・新着記事一覧を見る

WshNetwork(ネットワークドライブの割り当て等)|VBA技術解説(2025-04-09)
TRANSLATE関数(翻訳) DETECTLANGUAGE関数(言語識別)|エクセル入門(2025-04-08)
QRコード、バーコード作成の覚え書き|エクセル関数応用(2025-04-05)
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)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.繰り返し処理(For Next)|VBA入門
4.変数宣言のDimとデータ型|VBA入門
5.RangeとCellsの使い方|VBA入門
6.ひらがな⇔カタカナの変換|エクセル基本操作
7.セルのクリア(Clear,ClearContents)|VBA入門
8.メッセージボックス(MsgBox関数)|VBA入門
9.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
10.条件分岐(Select Case)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ