ExcelマクロVBA再入門 | 第27回.月別ブックより部署別シートに担当別に集計するNo3 | マクロが覚えられないという初心者向けに理屈抜きのやさしい解説



最終更新日: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)で確認する時、
今操作しているシートはどれか
今の行数は何か
これらをしっかりと確認してください。




同じテーマ「ExcelマクロVBA再入門」の記事

第23回.複数のプロシージャーを連続で動かす(Callステートメント)
第24回.マクロの呪文を追加してボタンに登録(ScreenUpdating)
第25回.月別ブックより部署別シートに担当別に集計するNo1
第26回.月別ブックより部署別シートに担当別に集計するNo2
第28回.月別ブックより部署別シートに担当別に集計するNo4
第29回.月別ブックより部署別シートに担当別に集計するNo5
第30回.今後の覚えるべきことについて

新着記事 ・・・新着記事一覧を見る

SUMIFの間違いによるパフォーマンスの低下について|エクセル関数超技(6月17日)
条件式のいろいろな書き方:TrueとFalseの判定とは|ExcelマクロVBA技術解説(6月15日)
空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)
最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)
Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)
数式の参照しているセルを取得する|ExcelマクロVBAサンプル集(3月18日)

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

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.RangeとCellsの使い方|ExcelマクロVBA入門
3.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
4.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
5.変数とデータ型(Dim)|ExcelマクロVBA入門
6.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
7.セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH)|エクセル関数超技
8.ひらがな⇔カタカナの変換|エクセル基本操作
9.定数と型宣言文字(Const)|ExcelマクロVBA入門
10.CSVの読み込み方法|ExcelマクロVBAサンプル集



  • >
  • >
  • >
  • 月別ブックより部署別シートに担当別に集計するNo3

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


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

    ↑ PAGE TOP