EXCEL VBAで一括集計プログラムシート「かんたん一括集計クン」を作る（初心者向け）

この記事はなにか

割と以前書いた記事の閲覧数がいい感じなので、
EXCELでの取りまとめ業務の効率化のために作った一括集計機能のマクロ、
その名もかんたん一括集計クンを記事化したものです。

作成した背景

上から突如降りかかる「これ〇〇までに取りまとめ（ヒアリング）よろしく～」という大号令。しかも大量に。
EXCELという名の集計フォーマットで末端まで行くと数百・数千の取りまとめ先があるものの、少しでも楽をしたい&してもらいたい。
どげんかせんといかんということで週末サンマルクに籠もり、この一括集計プログラムシートを作成。

一括印刷プログラムシートよりは汎用性は低いかもですが、
そのままでも使えますし、色々アレンジしても使えるかと思います。
私と同じ業務効率化が趣味の方、ぜひご活用ください！

使用イメージ

まとめましたので下記ご覧ください。

補足

• シート全体を集計…同じフォーマットを埋め合わせるイメージ（通常の取りまとめ）
• 継ぎ足し…どんどん下に継ぎ足すイメージ（データ加工などでよく使用）

「かんたん一括集計クン」の作り方

【作成手順】

1. Excelファイルを作成（拡張子は*.xlsm）
2. 一括集計ユーザーフォームの作成
3. 一括集計機能起動ボタンの作成
   （尚、MacのExcelではユーザーフォームが使えないと思うのでWindowsでお願いします…）

1. EXCELファイルを作成

マクロ有効ファイルの拡張子（*.xlsm）で作成します。
ファイル名はなんでも大丈夫です！ Ex)一括集計クンプログラムシート.xlsm

2. 一括集計ユーザーフォームの作成

開発タブ→Visual Basic→VBA project(一括集計クンプログラムシート.xlsm)→
右クリック→挿入→UserFormを選択

そうするとユーザーフォームの雛形ができたと思います。
オブジェクト名はそのままUserForm1としてください。

以下の画像の様にユーザーフォームを作成します。必要なのはボタンの部分だけですのでほかは省いても大丈夫です。
逆にボタンCommandButton1（シート全体を集計）、CommandButton2（継ぎ足し集計）は使用するので必ず設置してください。

フォームをダブルクリックするとコードが表示されます。
そこに以下のコードを貼り付けてください。

.vb

Private Sub CommandButton1_Click()

    '■ シート全体を集計 ■
    Application.ScreenUpdating = False '画面更新を停止してスピードアップする
        
    Dim i As Long
    Dim bookname As String
    Dim matomesheet As Workbook
    Set matomesheet = ActiveWorkbook
        
    For i = 1 To 500
        
        On Error GoTo myerror
            
        bookname = Dir(ThisWorkbook.Path & "\未処理\")
        Workbooks.Open ThisWorkbook.Path & "\未処理\" & bookname '「未処理」からひとつのファイルを選んで開く
            
        If ActiveSheet.FilterMode = True Then
            ActiveSheet.ShowAllData 'フィルターがかかっていたら解除する
        End If
            
        Cells.Select '全てのセルを選択
        Selection.Copy 'コピーする
        
        matomesheet.Activate  '一括集計クンブックを開く
        Worksheets("まとめシート").Activate  'まとめシートを開く
        Cells.Select  '全てのセルを選択する
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            True, Transpose:=False '貼り付けを行う（コピー元の空欄は無視する）
            
        bookname = Dir(ThisWorkbook.Path & "\未処理\")
        Workbooks.Open ThisWorkbook.Path & "\未処理\" & bookname  '「未処理」からひとつのファイルを選んで開く
            
        ActiveWorkbook.Close savechanges:=True 'シートを保存して閉じる
        Name ThisWorkbook.Path & "\未処理\" & bookname _
        As ThisWorkbook.Path & "\処理済\" & bookname '処理が終わったファイルを「未処理」から「処理済」に移す
                
    Next
         
myerror:
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select
    MsgBox "完了しました"  '「完了しました」と表示
    
End Sub


Private Sub CommandButton2_Click()

    '■ 継ぎ足しで集計 ■
    '★『元シートによって要変更』★という箇所はファイルによって変更ください｡デフォルトは60列まで集計。

Application.ScreenUpdating = False '画面更新を停止してスピードアップする
    
    Dim i As Long
    Dim bookname As String
    Dim rw As Long
    Dim matomesheet As Workbook
    Set matomesheet = ActiveWorkbook
    
    For i = 1 To 500
        
        On Error GoTo myerror
            
        bookname = Dir(ThisWorkbook.Path & "\未処理\")
        Workbooks.Open ThisWorkbook.Path & "\未処理\" & bookname '「未処理」からひとつのファイルを選んで開く
        
        If ActiveSheet.FilterMode = True Then
            ActiveSheet.ShowAllData 'フィルターがかかっていたら解除する
        End If
        
        Rows.Hidden = False '行の非表示を解除
        Columns.Hidden = False '列の非表示を解除
            
        rw = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).row + 1  '一番最後の行の数値を取得する
        Range(Cells(1, 1), Cells(rw, 60)).Select 'A1からAI（60列目）の最終行までの範囲（コピーしたい範囲）を選択★『元シートによって要変更』★
        Selection.Copy '選択した範囲をコピーする
            
        matomesheet.Activate  '一括集計クンブックを開く
        Worksheets("まとめシート").Activate  'まとめシートを開く
            
        rw = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).row + 1  '一番最後の行の数値を取得する
        Cells(rw, 1).Select '記入されている一番最後のセルの一つ下を選択
            
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            True, Transpose:=False '貼り付けを行う（コピー元の空欄は無視する）
            
        bookname = Dir(ThisWorkbook.Path & "\未処理\")
        Workbooks.Open ThisWorkbook.Path & "\未処理\" & bookname  '「未処理」からひとつのファイルを選んで開く
            
        ActiveWorkbook.Close savechanges:=True 'シートを保存して閉じる
        Name ThisWorkbook.Path & "\未処理\" & bookname _
        As ThisWorkbook.Path & "\処理済\" & bookname '処理が終わったファイルを「未処理」から「処理済」に移す
        
    Next
         
myerror:
        MsgBox "完了しました"  '「完了しました」と表示
        
        '★ここからはシート再計算、セル分割、計算式を値に変更のマクロ
        
        ActiveSheet.Calculate
        Cells.Select
        Selection.UnMerge
        Cells.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        Range("A1").Select
    
End Sub

以上でユーザーフォームの設定は終わりです！

3. 一括集計機能起動ボタンの作成

先程ユーザーフォームを作った要領と同じやり方でモジュールを作成します。

作成したモジュールの名前はそのままModule1にしてください。

モジュールをダブルクリックし、コード編集画面を開いて下記コードをコピペします。

.vb

Sub かんたん一括集計クン表示()

    Dim ws As Worksheet
    Dim flag As Boolean
    UserForm1.Show vbModeless
    
    On Error Resume Next
    MkDir (ThisWorkbook.Path & "\未処理\")  '「未処理」フォルダーを作る
    On Error Resume Next
    MkDir (ThisWorkbook.Path & "\処理済\")  '「処理済」フォルダーを作る
    
    For Each ws In Worksheets
    
      If ws.Name = "まとめシート" Then flag = True
    
    Next ws
    
    If flag = True Then
    
        Sheets("まとめシート").Visible = True
        Sheets("まとめシート").Activate
        MsgBox "「まとめシート」にデータが集計されます。" & vbCrLf & "未処理フォルダに集計したいファイルを入れ、" & vbCrLf & "集計したい種類のボタンを押してください。"
    
        Exit Sub
    
    Else
    
        ActiveWorkbook.Sheets.Add
        On Error Resume Next
        ActiveSheet.Name = "まとめシート"
    
        MsgBox "「まとめシート」にデータが集計されます。" & vbCrLf & "未処理フォルダに集計したいファイルを入れ、" & vbCrLf & "集計したい種類のボタンを押してください。"
    
    End If

End Sub

次に、シート上に起動するためのボタンを作成します。シート名はそのままSheet1でも起動シートでも何でもかまいません。
シートに戻り、開発タブ→挿入→左上のボタンのアイコンをクリック

以下の図のようにボタンを設置し、マクロを割り当てます。

以上で完成です！
起動ボタンを押して作成したユーザーフォームが出てくれば完成です！

取りまとめ用のシートはまとめシートという名前で自動で挿入されます。

また、同じフォルダ内に未処理と処理済フォルダが生成されます。
使用する場合は、取りまとめたいファイルを未処理フォルダに入れて処理を実行してください。

所感

EXCELでの集計というのは時に非常に難しく感じます。
WEBアプリとかGoogleフォームで集計できれば良いのでしょうが、それができない場合もあります。
また、フォーマットに制限かけているにもかかわらずかなり自由にアレンジしてくる方もいらっしゃいます。
結局のところ、そのような想定もしっかり行った上で準備をする、
取りまとめる側の事前の発信の方法ですべてが決まってくるのかもしれません。
それだけ責任が大きいものだということですね！

できなかったり、不明点などあればコメント欄などに記載ください。