集めたエクセルを集計したい！マクロ（ExcelVBA）ボタン1つで集計

エクセルの効率化において、もっとも効果を発揮するのが転記や集計作業です。

エクセルでひたすらコピペ作業を繰り返している人には、是非活用して欲しいマクロをご紹介します。

本ブログでは以前、自動集計マクロについて解説しましたが、

このマクロでは、申請書などの予めフォーマットが決まったものを集計することを想定しています。

そのため、集計したいエクセルフォームの行や列が可変するものは対応できません。

つまり、
集めたエクセルファイルの中身が何行あるか分からない、
もしくはエクセルファイルによって行数が異なる
という場合には使用できません。

そこで今回は、集めたエクセルの最終行を取得し、自動集計していくマクロについてご紹介します。

集めたエクセルを集計したい！マクロを使えばボタン1つで集計可能
エクセルを集計するマクロコード
エクセル自動集計マクロの解説
集めたエクセルを集計する方法まとめ

集めたエクセルを集計したい！マクロを使えばボタン1つで集計可能

このページでは、例えば以下のようなExcel集計業務を効率化することが出来ます。

作業手順のイメージとしては
①各社員から集めたExcel申請書を１つのフォルダにすべて格納します
②集計表作成マクロを開きます
③マクロボタンを１回押すとフォルダ内のExcel申請書の内容を集計表に転記してくれます
※この時、集めたエクセルファイルの最終行はバラバラである

エクセルを集計するマクロコード

開発タブ＞Visual Basic＞挿入＞標準モジュール
でExcelマクロを挿入する場所が開きますので、そこに下記のコードを貼り付けてください。
そして、Excel自体を.xlsm形式で保存してください。
ここが分からない方は、まず、超初心者向け「初めてのマクロ」を先にお読みください。

Sub 集計表作成()
  Dim Folder As String
  Dim file As String
  Dim book As Workbook
  Dim i As Integer
  Dim Number As Integer
  Dim RngFrom As Range
  Dim RngTo As Range
 
   With Application.FileDialog(msoFileDialogFolderPicker)
     If .Show = True Then
          Folder = .SelectedItems(1)
     End If
  End With
  
  file = Dir(Folder & "\*.xlsx")
  
  Do While file <> ""
  
    Set book = Workbooks.Open(Folder & "\" & file)
    n = ThisWorkbook.Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row + 1
    Number = book.Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row
    
    book.Worksheets(1).Range(Cells(2, "A"), Cells(Number, "F")).Copy
    Workbooks("集計表作成.xlsm").Worksheets(1).Range("A" & n).PasteSpecial
    file = Dir()
    Application.DisplayAlerts = False
    book.Close
    Application.DisplayAlerts = True
  Loop
  
End Sub

エクセル自動集計マクロの解説

file = Dir(Folder & “\*.xlsx”)までの部分は、以前のコードと全く同じなので説明を割愛します。

詳しくは、以前紹介している自動集計マクロについて解説を読んでください。

今回は、主にDo～Loopの中身について解説していきます。

 Do While file <> ""
    Set book = Workbooks.Open(Folder & "\" & file)
    n = ThisWorkbook.Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row + 1
    Number = book.Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row
    
    book.Worksheets(1).Range(Cells(2, "A"), Cells(Number, "F")).Copy
    Workbooks("集計表作成.xlsm").Worksheets(1).Range("A" & n).PasteSpecial
    file = Dir()
    Application.DisplayAlerts = False
    book.Close
    Application.DisplayAlerts = True
 Loop

Do～Loopの間でフォルダ内のファイルがなくなるまで（file <> “”）繰り返し処理を行っていますね。

具体的に処理の中身を見てみましょう。

Set book = Workbooks.Open(Folder & “\” & file)では

bookというのは、選択したフォルダ内にあるエクセルファイルの事です。

n = ThisWorkbook.Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row + 1

この部分では、nという変数の中に、集計表作成マクロのシート1でA列の最終行数を入れています。

End(xlUp).Rowは、最終行を見つけるときに頻繁に使う重要ポイントです。

例えば、この図でいうと、A列の最終行は2行目ですが、

最終行の2行目に値が入っているので、転記開始は3行目から転記をしたいわけです。

なので、+1と書くことで、nの値を3行目としているけです。

Number = book.Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row

この部分も同じく、Numberという変数の中に、各エクセルファイルの最終行を入れています。

例えば、A店舗のエクセルの場合、最終行は3行目になりますので、Numberの値は3となります。

End(xlUp).Rowは、最終行を表している

book.Worksheets(1).Range(Cells(2, "A"), Cells(Number, "F")).Copy
Workbooks("集計表作成.xlsm").Worksheets(1).Range("A" & n).PasteSpecial

tsen

上記で見てきた通り、n=3、Number＝3でしたね？

これを、マクロコードに実際に当てはめてみましょう。

book.Worksheets(1).Range(Cells(2, “A”), Cells(Number, “F”)).Copy
【シート１の】【セルA2から】【セルF3までを】【コピーして】

Workbooks(“集計表作成.xlsm”).Worksheets(1).Range(“A” & n).PasteSpecial
【集計表作成.xlsmの】【シート１の】【セルA3にペーストする】

と書いてあります。

転記が終わった時点で時を止めると集計表一覧はこのようになっているはずです。