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

Excelで複数のブック（ファイル）にちらばった数値をまとめるマクロを紹介します。

 

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

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

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

 

 

マクロの考え方

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

・指定したフォルダにあるExcelファイル（名前で指定することも可能）を開く
↓
・特定の数値を、集計シートに入れていく
↓
開いたファイルを閉じる

今回集計したいファイルは、次のようなものです。
セルB2に、数値（売上）が入っていて、これを、

このように集計していきます。

大事なのは、すべての集計元のブックで、セル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ファイルに表示し、それをマクロで読み込んでいます。

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

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

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

End Sub

ボックスが表示され、フォルダを指定できます。

ボタンを設定して、マクロを割り当て、「参照」という名前にすれば、より使いやすく、らしくなるのでおすすめです。

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

今回のマクロです。

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枚のシートに集まるので、これを集計して使うことができます。

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

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

ちなみに、今回の事例（12個のExcelファイル）をつくる際にもマクロを使っています。
フォーマットを作って、

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

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新」

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