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

マクロが覚えられないという初心者向けに理屈抜きのやさしい解説
公開日:2015-12-08 最終更新日: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回.ファイル一覧を取得する
・Dir関数 ・Dir関数の使い方 ・ファイルの一覧を取得するマクロVBAコード ・Do~Loopステートメント ・ExcelマクロVBA入門等の対応ページ
再度、こちらを見直してください。


・データ収集

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回.複数ブックよりデータを集める
・第17回.セルにブック・シートを指定する ・第19回.ブックを開く・閉じる・保存する ・第21回.ファイル一覧を取得する ・複数ブックよりデータを集めるVBA
若干の違いがあるわけですが、この違いを自由に書き換えられるかがVBAスキルになるわけです。
第22回のコードと見比べてみて下さい。
しっかりと違いを把握を出来れば、VBAコードが理解できているという事です。



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

第20回.全てのシートに同じ事をする(For~Worksheets.Count)

・シートの指定方法 ・全シートの「印刷の向き」を「横」に設定します。 ・シート名に"横"と入っているシートだけ「印刷の向き」を「横」に設定します ・シート名に"削除"と入っていたらシートを削除する ・For Eachについて ・ExcelマクロVBA入門の対応ページ
第21回.ファイル一覧を取得する(Do~LoopとDir関数)
・Dir関数 ・Dir関数の使い方 ・ファイルの一覧を取得するマクロVBAコード ・Do~Loopステートメント ・ExcelマクロVBA入門等の対応ページ
第22回.複数ブックよりデータを集める
・第17回.セルにブック・シートを指定する ・第19回.ブックを開く・閉じる・保存する ・第21回.ファイル一覧を取得する ・複数ブックよりデータを集めるVBA
第23回.複数のプロシージャーを連続で動かす(Callステートメント)
・長い1つのプロシージャーを分割する ・プロシージャーを呼び出すCallステートメント ・ExcelマクロVBA入門等の対応ページ
第24回.マクロの呪文を追加してボタンに登録(ScreenUpdating)
・マクロの呪文「Application.ScreenUpdating」を追加 ・ボタンの追加とマクロの登録 ・ExcelマクロVBA入門等の対応ページ
第25回.月別ブックより部署別シートに担当別に集計するNo1
マクロ再入門と題しての連載もいよいよ大詰めです、これまでの総復習として、以下のような処理のマクロを書いてみましょう。■処理概要 月別のデータファイル(ExcelファイルまたはCSV)を読込み、※項目は、日付,部署,担当者,売上数,売上金額 これを、新規ブックを作り、部署別のシートに、担当者・年月ごとの集計値を出力す…
第26回.月別ブックより部署別シートに担当別に集計するNo2
第27回.月別ブックより部署別シートに担当別に集計するNo3
マクロ再入門の課題として、月別ブックより部署別シートに担当別に集計する、最も頭を悩ます、部署別のデータに集計します、少々複雑な処理になります。全体の処理手順は以下になります。■処理手順 ・ファイル一覧 シート「ファイル一覧」に、サブフォルダ「月別データ」内の全Excelファイルの一覧を取得 ・データ収集 シート「デ…
第28回.月別ブックより部署別シートに担当別に集計するNo4
マクロ再入門の課題として、月別ブックより部署別シートに担当別に集計する、部署別のデータに集計する、最も頭を悩ます、少々複雑な処理になります。全体の処理手順は以下になります。■処理手順 ・ファイル一覧 シート「ファイル一覧」に、サブフォルダ「月別データ」内の全Excelファイルの一覧を取得 ・データ収集 シート「デー…
第29回.月別ブックより部署別シートに担当別に集計するNo5
マクロ再入門の課題として、月別ブックより部署別シートに担当別に集計する、総仕上げとして、複数のマクロを連続実行させるようにして、シートにボタンを追加して完成させます。全体の作業手順は以下になります。・連続実行させるマクロを作成 ・ボタンを追加しマクロを登録する ■連続実行させるマクロを作成 Callプロシージャ名 …
第30回.今後の覚えるべきことについて
マクロ再入門と題として、シリーズ全30回の最終回になります、マクロ再入門で学んで、ある程度はマクロが書けるようになったものと信じます。しかし、やりたい事、やるべき事を、自由自在にマクロとして書けるようになるには、道はまだ半ばです。マクロ再入門は、少なくとも2回は復習してください。


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

エクセルが起動しない、Excelが立ち上がらない|エクセル雑感(2024-04-11)
ブール型(Boolean)のis変数・フラグについて|VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する|VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)


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

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




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


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


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