ExcelマクロVBA再入門
第26回.月別ブックより部署別シートに担当別に集計するNo2

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

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


マクロ再入門の課題として、
月別ブックより部署別シートに担当別に集計する、
これを実現する実際のマクロを順に見ていきます。


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

■処理手順

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

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

・部署別作成
シート「データ」より、
部署別のシートに、担当者・年月ごとの集計値を出力する。


今回は、
・ファイル一覧
・データ収集

この部分の解説になります。



・ファイル一覧



Sub ファイル一覧()
  Dim i As Long
  Dim FileName As String
  Dim PathName As String
  Dim ws As Worksheet
  
  Set ws = Worksheets("ファイル一覧")
  PathName = ThisWorkbook.Path & "\月別データ\"
  ws.Cells.ClearContents
  
  i = 1
  FileName = Dir(PathName & "*.xls*")
  Do While FileName <> ""
    ws.Cells(i, 1) = PathName & FileName
    i = i + 1
    FileName = Dir()
  Loop
End Sub


変数iは、シートにファイル名を書き出す時の行位置に使います。
変数FileNameは、Dir関数で取得したファイル名を入れるのに使います。
変数PathNameは、「月別データ」のフォルダを入れるのに使います。
変数wsは、Worksheets("ファイル一覧")の短い名前として使います。


Setで、変数wsにWorksheets("ファイル一覧")を入れる事で、これ以降はWorksheets("ファイル一覧")と書く代わりに、wsと書くことが出来ます。
変数PathNameに、「月別データ」のフォルダを入れます。
Worksheets("ファイル一覧")に前回出力内容が残っている場合があるので、一旦データ消去します。

Worksheets("ファイル一覧")に出力する行位置として、変数iに最初の1を入れます。
変数FileNameに、Dir関数を使って「月別データ」フォルダからファイル名を取得
Dir関数のファイル名取得が無くなるまで、以下を処理
・Worksheets("ファイル一覧")のi行に、パスとファイル名つなげてフルパスで出力。
・Worksheets("ファイル一覧")に出力する行位置i1進める
Dir関数で次のファイル名を取得

このように、VBAコード1行1行を、しっかりと読めるようにして下さい。
不明な箇所があるようなら、
第21回.ファイル一覧を取得する
マクロVBAで他のブック(ファイル)を扱う時、まず問題となるのがファイル名です、ファイル数が常に同じでファイル名も変化しなければ良いのですが… ファイル数もファイル名も決まっていない場合は、まずはファイルの一覧を取得する必要があります。ファイル名を取得するには、Dir関数を使います。
再度、こちらを見直してください。


・データ収集



Sub データ収集()
  Dim i As Long
  Dim iLast As Long
  Dim wb As Workbook
  Dim ws As Worksheet
  Dim ws一覧 As Worksheet
  Dim wsデータ As Worksheet
  
  Set ws一覧 = Worksheets("ファイル一覧")
  Set wsデータ = Worksheets("データ")
  With wsデータ
    .Cells.Clear
    .Range("A1") = "日付"
    .Range("B1") = "部署"
    .Range("C1") = "担当"
    .Range("D1") = "売上数"
    .Range("E1") = "売上金額"
    .AutoFilterMode = False
  End With
  For i = 1 To ws一覧.Cells(Rows.Count, 1).End(xlUp).Row
    iLast = wsデータ.Cells(Rows.Count, 1).End(xlUp).Row + 1
    Set wb = Workbooks.Open(ws一覧.Cells(i, 1))
    Set ws = wb.Worksheets(1)
    ws.Range("A1").CurrentRegion.Offset(1, 0).Copy Destination:=wsデータ.Cells(iLast, 1)
    wb.Close savechanges:=False
  Next
End Sub


Worksheets("データ")は、全セルを一旦消去して、1行目に見出しを書いています。
あらかじめ1行目には見出しを入れて置き、2行目以降を消去しても良いです。
CurrentRegion.Offset(1, 0).Clear
と言ったコードになりますね、余裕があれば書いてみて下さい。


集めるデータの1行目は見出しなので不要ですので、2行目以降をコピーしています。
ws.Range("A1").CurrentRegion.Offset(1, 0).Copy
この部分ですね。
注意するのは、出力する行位置です。
事前に、
iLast = wsデータ.Cells(Rows.Count, 1).End(xlUp).Row + 1
これで出力してある最終行の次の行を取得してあります。

ほぼ、以下の回で書いたものと同じです。
第22回.複数ブックよりデータを集める
他のブックを開いて指定シートの指定セル範囲のデータを取得する、取得したデータを使って、目的の表を作り上げる、これこそマクロVBAの真価が問われる処理でしょう。これまでにやってきた内容が出来れば大丈夫です。これまでの内容を上手く組み合わせるだけです。
若干の違いがあるわけですが、この違いを自由に書き換えられるかがVBAスキルになるわけです。
第22回のコードと見比べてみて下さい。
しっかりと違いを把握を出来れば、VBAコードが理解できているという事です。



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

全てのシートに同じ事をする(For~Worksheets.Count)
ファイル一覧を取得する(Do~LoopとDir関数)
複数ブックよりデータを集める
複数のプロシージャーを連続で動かす(Callステートメント)
マクロの呪文を追加してボタンに登録(ScreenUpdating)
月別ブックより部署別シートに担当別に集計するNo1
月別ブックより部署別シートに担当別に集計するNo2
月別ブックより部署別シートに担当別に集計するNo3
月別ブックより部署別シートに担当別に集計するNo4
月別ブックより部署別シートに担当別に集計するNo5
今後の覚えるべきことについて


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

CSVの読み込み方法(ジャグ配列)|VBAサンプル集(7月15日)
その他のExcel機能(グループ化、重複の削除、オートフィル等)|VBA入門(7月14日)
オートフィルタ退避回復クラスを複数シート対応させるVBAクラス|VBA技術解説(7月6日)
オートフィルタを退避回復するVBAクラス|VBA技術解説(7月6日)
IfステートメントとIIF関数とMax関数の速度比較|VBA技術解説(6月23日)
Withステートメントの実行速度と注意点|VBA技術解説(6月6日)
VBA+SeleniumBasicで検索順位チェッカー(改)|VBA技術解説(6月2日)
マクロでShift_JIS文字コードか判定する|VBA技術解説(6月1日)
Shift_JISのテキストファイルをUTF-8に一括変換|VBAサンプル集(5月31日)
「VBAによる解析シリーズその2 カッコ」をやってみた|エクセル(5月21日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|ExcelマクロVBA入門
4.変数とデータ型(Dim)|ExcelマクロVBA入門
5.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
6.繰り返し処理(For Next)|ExcelマクロVBA入門
7.マクロって何?VBAって何?|ExcelマクロVBA入門
8.ひらがな⇔カタカナの変換|エクセル基本操作
9.ExcelマクロVBAの基礎を学習する方法|エクセルの神髄
10.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説



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

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


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




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