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再入門」の記事

第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 ・・・新着記事一覧を見る

VBA100本ノック 18本目:名前定義の削除|VBA練習問題100(11月6日)
VBA100本ノック 17本目:重複削除(ユニーク化)|VBA練習問題100(11月6日)
VBA100本ノック 16本目:無駄な改行を削除|VBA練習問題100(11月5日)
VBA100本ノック 15本目:シートの並べ替え|VBA練習問題100(11月4日)
VBA100本ノック 14本目:社外秘シート削除|VBA練習問題100(11月3日)
VBA100本ノック 13本目:文字列の部分フォント|VBA練習問題100(11月1日)
VBA100本ノック 12本目:セル結合を解除|VBA練習問題100(10月31日)
VBA100本ノック 11本目:セル結合の警告|VBA練習問題100(10月30日)
VBA100本ノック 10本目:行の削除|VBA練習問題100(10月29日)
VBA100本ノック 9本目:フィルターコピー|VBA練習問題100(10月28日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
5.マクロって何?VBAって何?|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




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


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



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