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回.ファイル一覧を取得する
再度、こちらを見直してください。


・データ収集

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スキルになるわけです。
第22回のコードと見比べてみて下さい。
しっかりと違いを把握を出来れば、VBAコードが理解できているという事です。




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

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

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

スプレッドシートが非常に遅い、高速化するには|Google Apps Script入門(1月17日)
画像のトリミング(PictureFormat,Crop)|ExcelマクロVBAサンプル集(12月27日)
シート保護|Google Apps Script入門(12月24日)
表示の固定|Google Apps Script入門(12月24日)
グラフ|Google Apps Script入門(12月21日)
入力規則|Google Apps Script入門(12月13日)
並べ替え|Google Apps Script入門(12月12日)
メモの挿入・削除と改行文字|Google Apps Script入門(12月6日)
リンクの挿入・編集・削除|Google Apps Script入門(12月6日)
セルに数式を入れる|Google Apps Script入門(12月1日)

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

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



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

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


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

    ↑ PAGE TOP