複数のファイル(ブック)を1枚のシートに効率よくまとめるマクロ

  • URLをコピーしました!

Excelのファイル(ブック)を1つのシートに効率よくまとめるには、マクロを使うしかありません。
ファイルをまとめるマクロを紹介します。
スクリーンショット 2014 10 26 9 10 35

 

ファイルが分かれていると集計が大変

たとえば、支店ごとにファイルがわかれていると、その集計が大変です。
スクリーンショット 2014 10 26 9 13 22

新宿支店

スクリーンショット 2014 10 26 9 13 33
渋谷支店

スクリーンショット 2014 10 26 9 21 30
池袋支店

スクリーンショット 2014 10 26 9 21 09
この集計を毎月やっていてはいくら時間があっても足りなくなるでしょう。
ファイルを開いて、コピーして、貼り付けて・・・という作業を、ファイルの数だけ繰り返さなければいけません。

こういった場合、マクロを使わなければ効率化できません。

 

 

ファイル(ブック)を1つにするマクロ

マクロはこういったものです。

Sub bookmerge()

    
    Dim b As Workbook  '集計するブック
    Dim b1 As Workbook  '集計先のブック
    
    Dim d  '集計するブック内のシートのデータ数
    Dim d1  '集計先のシートのデータ数
    
    Workbooks.Add
    Set b1 = ActiveWorkbook
    
    For Each b In Workbooks
    
        If b.Name <> b1.Name Then
            Dim i As Long
            For i = 1 To b.Worksheets.Count
            
                d = b.Worksheets(i).Range("a" & Rows.Count).End(xlUp).Row
                d1 = b1.Worksheets(1).Range("a" & Rows.Count).End(xlUp).Row
                
                b.Worksheets(i).Rows("1:" & d).Copy b1.Worksheets(1).Range("a" & d1 + 1)
            Next
        End If
    
    Next

End Sub

それぞれの支店のファイルを、1つのシートにまとめます。
まとめたいファイルはあらかじめ開いておいてください。

実際には、
・特定のフォルダにあるExcelファイルを1つにまとめる
・特定のファイルにあるExcelファイルのうち、特定の名前が含まれるファイルをまとめる
・ファイルのシートすべてをまとめる
・ファイルのシートのうち、指定したもの(月、支店など)のみまとめる
といったことができます。

 

dという変数に、このマクロが入っているファイルのワークシート1のA列で、行をカウントします。
その後、新宿支店のワークシート1の2行目から最後の行をコピーし、このブックのワークシート1のA列で、データがある行の下へ貼り付けます
貼り付け位置は、

 d1 = b1.Worksheets(1).Range("a" & Rows.Count).End(xlUp).Row

と表現しています。
最後の行に1を足した場所、つまり、一番下の行のその下に貼り付けるのです。

スクリーンショット 2014 10 26 16 18 51

マクロを実行後、新しいブックが作られ、そのブックに、3つのファイル(新宿支店、渋谷支店、池袋支店)のデータを集めます。

サンプルはこちらです。
EX-ITサンプル ブック集計マクロ

マクロを有効し、【集計】ボタンを押すと、そのときに開いているブックのデータを、新しいブックに集計します。

理想は、1つのシートにデータを入れること

支店別だけではなく、月別、年別、銀行別など様々なデータで上記のマクロは使えます。

1つのシートにまとめれば、
テーブルにしたり
スクリーンショット 2014 10 26 16 47 20

ピボットテーブルで集計したり、
スクリーンショット 2014 10 26 16 47 43

スクリーンショット 2014 10 26 16 47 57

グラフにしたり、
スクリーンショット 2014 10 26 16 48 33

仕訳データを作って会計ソフトに取り込んだりすることができます。
スクリーンショット 2014 10 26 16 49 26

ただ、こういったマクロを使わなくて済むように、理想は、1枚のシートにデータを入力するのが理想です。
そうはいっても、支店別や部署別、人別に入力する場合もあるかと思いますので、そういったときには今回のようなマクロを使ってみましょう。
マクロの基礎については、こちらの記事を参考にしてください。

 
フォルダにあるファイルを1つにするなら、こちらのマクロがおすすめです。

 

 

 

シートを集計するには、こちらのマクロが使えます。

 

 

 

 





【編集後記】
プライベートスペースが散らかりまくっています。
冬物との入れ替えもあるので、今日片付ける予定です。
ブログに書いておけば、やると思いますので(^_^;)

【昨日の1日1新】
※詳細は→「1日1新」

全3回セミナーの2回目を開催
iPhone「tasukuma」研究

  • URLをコピーしました!