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

  • URLをコピーしました!

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

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

  • URLをコピーしました!