複数のファイル(ブック)の数値をまとめるExcelマクロ

Excelで複数のブック(ファイル)にちらばった数値をまとめるマクロを紹介します。
スクリーンショット 2015 03 23 11 47 12

複数のブックに集計したい数値がある場合

複数のブック、たとえば、月別のブックに集計したい数値がある場合、通常だと、ファイルを開いて、コピペを繰り返すしかありません。
非常にめんどくさいです。
12ヶ月ならまだしも、それ以上のファイルになると、それだけで1日またはそれ以上かかってしまいます。

こういった場合もマクロが便利です。

マクロの考え方

マクロは、次のようなものをつくります。

・指定したフォルダにあるExcelファイル(名前で指定することも可能)を開く

・特定の数値を、集計シートに入れていく

開いたファイルを閉じる

今回集計したいファイルは、次のようなものです。
セルB2に、数値(売上)が入っていて、これを、
スクリーンショット 2015 03 23 12 13 21

このように集計していきます。
スクリーンショット 2015 03 23 12 14 12

大事なのは、すべての集計元のブックで、セルB2に求める数値があることです。
これが、1月がセルB2、2月はセルC2、3月はセルb2・・というのでは正しく集計できません。

Excelファイルをつくるときは、同様のファイルは同様の書式でつくるようにしましょう。


■スポンサードリンク


今回の事例で使う基本的な構文を確認しておきます。
・ブックを開く(○○.xlsx)

Workbooks.Open Filename:=" ○○.xlsx”

・ブックを閉じる

Workbooks(○○.xlsx).Close

・シート「売上」のセルB1に、ブック「コミッション.xlsx」のセルB2を入れる

Worksheets("売上").Range("b" & n).Value = _
Workbooks(コミッション.xlsx).Worksheets("集計").Range("b2").Value

・指定したフォルダからExcelファイルを探す

次のコードで、CドライブにあるExcelファイルを探し出し、メッセージとして表示できます。

Dim Merge_book
Merge_book = Dir("c:\*.xls*")
Do Until Merge_book = ""
    MsgBox (Merge_book)
    Merge_book = Dir()
Loop

Merge_bookは変数です。

Merge_book = Dir(“c:\*.xls*”)
のDirは、()内のファイルを探します。
“c:\*.xls*”は、Cドライブので、「*.xls*」を探すという指定です。
Excelファイルは、.xls、.xlsx、.xlsmという拡張子(ファイル名に後ろにあるファイルの種類を示すコード)ですので、こういった指定にします。
*は、何かがそこに入るという意味です。

Do Until
Loop
は、セットで、「〜でない限り繰り返す」を意味します。

Do Until Merge_book = ""
  ○○○
    Merge_book = Dir()
Loop

この構文で、Merge_book、つまり Dir(“c:\*.xls*”)(Cドライブの中のExcelファイル)を探し、それがなくなる(空欄””でない限り)まで、DOとLoopの間にある処理(○○○)を繰り返します。
すなわち、指定したフォルダ内のExcelファイルすべてに対して処理をおこなうのです。

処理をした後は、次のExcelファイルを探してセットする、
Merge_book = Dir()
を入れます。

複数ブックの数値を集計するマクロ

上記の基本構文を組み合わせて、マクロをつくっていきます。

フォルダを指定するマクロ

まず、「どのフォルダのExcelファイルを集計するか」を指定しておくと便利です。

マクロのコード内に書いてもいいのですが、Excelファイルに表示し、それをマクロで読み込んでいます。
スクリーンショット 2015 03 23 12 50 58

とはいえ、フォルダの場所をExcelに打つのは大変です。
次のマクロを実行すると、

Sub folder() '集計したいファイルがあるフォルダを指定

If Application.FileDialog(msoFileDialogFolderPicker).Show = True Then
Range("f1").Value = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)
End If

End Sub

ボックスが表示され、フォルダを指定できます。
スクリーンショット 2015 03 23 12 52 21

ボタンを設定して、マクロを割り当て、「参照」という名前にすれば、より使いやすく、らしくなるのでおすすめです。
スクリーンショット 2015 03 23 12 53 53

複数のブックから集計するマクロ

今回のマクロです。

Sub shuukei()

'フォルダの場所を変数に入れる
    Dim Folder_path As String
    Folder_path = Range("f1").Value
    
'集計先のシートを指定し、変数に入れる
    Dim w
    Set w = Worksheets("売上")
    
'集計するブックを変数に入れる
    Dim Merge_book As String
    Merge_book = Dir(Folder_path & "\*.xls*")
    

'いったん数値をクリア
    w.Range("a1", "b" & Rows.Count).Clear
    
'集計先のシートの1行からスタート
    Dim n
    n = 1
    
'指定したフォルダから、Excelファイルを探す
    Do Until Merge_book = ""
        Workbooks.Open Filename:=Folder_path & "\" & Merge_book
        
'見つかったら、A列にファイル名、B列に集計値を入れる
        w.Range("a" &n).Value = Merge_book
        w.Range("b" &n).Value = Workbooks(Merge_book).Worksheets("集計").Range("b2").Value
'次の行へ
        n = n + 1
        
'集計するブックを閉じる
        Workbooks(Merge_book).Close

'次のファイルを探しに行く
        Merge_book = Dir()
    Loop
   

End Sub

変数は、後半のコードを読みやすくするために、設定します。

「指定したフォルダから探し出したExcelファイル」の名前を「集計するシート」のセルA1に入れる
と書かずに、

X=「指定したフォルダから探し出したExcelファイル」
Y=「集計するシート」
とし、
Xの名前をYのセルA1に入れる

と書くようなものです。

実行すると、1枚のシートに集まるので、これを集計して使うことができます。
スクリーンショット 2015 03 23 12 59 03

同様の事例があったときに試してみてください。

EX-ITサンプル ブックから集計.xlsm

ちなみに、今回の事例(12個のExcelファイル)をつくる際にもマクロを使っています。
フォーマットを作って、
スクリーンショット 2015 03 23 13 00 25

次のマクロを書き実行しました。

Sub jirei()

    Dim i
    For i = 1 To 12
       Worksheets.Copy
       Range("b2").Value = "=RandBetween(100000, 1000000)"
       Range("b2").Copy
       Range("b2").PasteSpecial xlPasteValues
       
       ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\コミッション" & i & "月.xlsx"
       ActiveWorkbook.Close
       
    Next

End Sub

フォーマットを新しいブックとしてコピーして、セルB2に、100,000から1,000,000までのうちランダムで数値を入れます。
その数値を値のみコピーし、数式を消した後、「コミッション○月.xlsx」という名前で保存し閉じます。

これを1から12まで繰り返したものが事例のファイルです。

マクロは、アイデア次第でいろんなことができます。





【編集後記】
土日の合宿後の筋肉痛が、レース後よりもひどいです。。
坂道バイクを結構やったからかもしれません。
坂道練習をもっとやらなければ・・・。

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

■スポンサードリンク

館山市民プール
富里 道の駅
びわソフトクリーム

■スポンサードリンク



■著書
新版 ひとり社長の経理の基本
毎日定時で帰っても給料が上がる時間のつかい方をお金のプロに聞いてみた!
『ひとり税理士の仕事術』
『フリーランスのための一生仕事に困らない本』
『社長!「経理」がわからないと、あなたの会社潰れますよ』
『そのまま使える経理&会計のためのExcel入門』