Excel VBA 逆引き集 | 実務テンプレ完全版（超再利用部品） – 一括レポート自動作成

Excel VBA
スポンサーリンク

ねらい：複数の相手（顧客・部署・担当者）向けレポートを「テンプレ＋一覧」から一括自動作成する

毎月、顧客ごと・部署ごと・担当者ごとに「ほぼ同じ形のレポート」を量産していませんか。
1件なら手作業でも我慢できますが、10件・50件になってくると “コピペ地獄” になります。

一括レポート自動作成ツールの考え方はとてもシンプルです。

「誰に」「どんな条件で」「どんなテンプレで」「どこに保存するか」を一覧シートに持ち、
VBAでその一覧を上から順に「テンプレコピー → 条件セット → 集計・貼り付け → PDF/Excel保存」を回す。

ここでは、プログラミング初心者でも貼って動くように、

  • 設計（ReportListシートの項目と役割）
  • 共通ユーティリティ（テンプレコピー・PDF保存など）
  • 1件分レポート作成ロジック
  • 一括実行マクロ
  • 実際の例（顧客別売上レポート）

の流れを、コード付きでかみ砕いて説明します。

設計：ReportList シートに「誰向けのレポートをどう作るか」を一覧で持つ

ReportList シートの列構成

まず、以下のようなシートを 1 枚作るところから始めます。名前は「ReportList」とします。

A列: Enable（この行を処理するか。Y/N）
B列: ReportID（レポートID。任意の管理用）
C列: TargetKey（レポートの対象キー。顧客IDや部署コードなど）
D列: TargetName（顧客名や部署名。レポート上の表示用）
E列: TemplateSheet（テンプレートとなるシート名）
F列: OutputSheetName（作成するレポートシート名）
G列: OutputFolder（PDFなどの保存先フォルダ）
H列: OutputFileName（保存ファイル名。拡張子は後で付ける）
I列: PeriodFrom（対象期間の開始日）
J列: PeriodTo（対象期間の終了日）
K列: Status（結果ログ：OK/NG など）
L列: Message（メッセージやエラー詳細）

ポイントは、「どの行をどういう条件でレポートにするか」を全部ここに書いてしまうことです。
VBA側は、この一覧を1行ずつ読んで処理するだけの “機械” にします。

共通基盤：テンプレコピー・PDF保存・フォルダ作成などのユーティリティ

基本ユーティリティ（どのプロジェクトでも使い回せる土台）

' ModReport_Base.bas
Option Explicit

Public Function ReadRegion(ws As Worksheet, Optional topLeft As String = "A1") As Variant
    ReadRegion = ws.Range(topLeft).CurrentRegion.Value
End Function

Public Sub WriteBlock(ws As Worksheet, a As Variant, startCell As String)
    ws.Range(startCell).Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub

Public Sub FormatBlock(ws As Worksheet, startCell As String)
    With ws.Range(startCell).CurrentRegion
        .Columns.AutoFit
        .Borders.LineStyle = xlContinuous
    End With
End Sub

Public Function EnsureFolder(ByVal folderPath As String) As Boolean
    On Error Resume Next
    If Len(folderPath) = 0 Then
        EnsureFolder = False
        Exit Function
    End If
    If Dir(folderPath, vbDirectory) = "" Then
        MkDir folderPath
    End If
    EnsureFolder = (Dir(folderPath, vbDirectory) <> "")
    On Error GoTo 0
End Function

Public Sub ExportSheetToPdf(ByVal ws As Worksheet, ByVal pdfPath As String)
    With ws.PageSetup
        .Orientation = xlPortrait
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .LeftMargin = Application.CentimetersToPoints(1)
        .RightMargin = Application.CentimetersToPoints(1)
        .TopMargin = Application.CentimetersToPoints(1)
        .BottomMargin = Application.CentimetersToPoints(1)
    End With
    
    ws.ExportAsFixedFormat Type:=xlTypePDF, _
                           Filename:=pdfPath, _
                           Quality:=xlQualityStandard, _
                           OpenAfterPublish:=False
End Sub
VB

重要な点を整理します。

フォルダ作成は EnsureFolder で「なければ作る」ように統一します。これを毎回書かないのが “再利用部品” の良さです。
PDF出力は ExportSheetToPdf に隠し、呼ぶ側は「ワークシート」と「ファイルパス」だけ意識すれば良いようにします。
ReadRegion / WriteBlock / FormatBlock は、他のテンプレ（売上レポートやCSV検証など）とも共通で使える、定番の配列ヘルパーです。

1件分レポート作成ロジック：テンプレに条件を渡して、中身を埋める“心臓部”

テンプレートシートの考え方

レポートのレイアウト（見た目）は、テンプレートシートに作り込んでおきます。
例えば「Report_Template」というシートに、

B2セル: 顧客名表示
B3セル: 期間表示
B5以降: 集計結果を貼るエリア

など、固定のレイアウトを用意しておきます。

VBA 側は、このテンプレをコピーして新しいレポートシートを作り、
そこに「対象の顧客データだけを集計した結果」を貼り付けるイメージです。

例：顧客IDと期間から売上明細を抽出→集計→貼り付け

ここでは、元データを「SalesData」シートに持っている想定で進めます。

SalesData シートの構成例
A列: 日付
B列: 顧客ID
C列: 顧客名
D列: 商品
E列: 数量
F列: 金額

' ModReport_SalesLogic.bas
Option Explicit

Private Function FilterSales(ByVal customerId As String, _
                             ByVal fromDate As Date, _
                             ByVal toDate As Date) As Variant
    Dim ws As Worksheet
    Set ws = Worksheets("SalesData")
    
    Dim a As Variant
    a = ReadRegion(ws)
    
    Dim out() As Variant
    ReDim out(1 To 1, 1 To UBound(a, 2))
    
    Dim r As Long
    Dim rows As Long
    rows = 1
    
    Dim c As Long
    For c = 1 To UBound(a, 2)
        out(1, c) = a(1, c)
    Next
    
    For r = 2 To UBound(a, 1)
        Dim cid As String
        Dim dt As Variant
        
        cid = CStr(a(r, 2))
        dt = a(r, 1)
        
        If CStr(customerId) = cid Then
            If IsDate(dt) Then
                If CDate(dt) >= fromDate And CDate(dt) <= toDate Then
                    rows = rows + 1
                    ReDim Preserve out(1 To rows, 1 To UBound(a, 2))
                    For c = 1 To UBound(a, 2)
                        out(rows, c) = a(r, c)
                    Next
                End If
            End If
        End If
    Next
    
    FilterSales = out
End Function

Private Function AggregateSales(ByVal filtered As Variant) As Variant
    Dim d As Object
    Set d = CreateObject("Scripting.Dictionary")
    
    Dim r As Long
    For r = 2 To UBound(filtered, 1)
        Dim itemName As String
        Dim amount As Double
        
        itemName = CStr(filtered(r, 4))
        amount = 0#
        If IsNumeric(filtered(r, 6)) Then
            amount = CDbl(filtered(r, 6))
        End If
        
        If d.Exists(itemName) Then
            d(itemName) = d(itemName) + amount
        Else
            d(itemName) = amount
        End If
    Next
    
    Dim out() As Variant
    Dim cnt As Long
    cnt = d.Count
    
    ReDim out(1 To cnt + 1, 1 To 2)
    out(1, 1) = "商品"
    out(1, 2) = "金額合計"
    
    Dim i As Long
    i = 2
    Dim key As Variant
    For Each key In d.Keys
        out(i, 1) = key
        out(i, 2) = d(key)
        i = i + 1
    Next
    
    AggregateSales = out
End Function
VB

FilterSales は「顧客ID＋期間」で SalesData から該当の明細だけ抜き出す機能です。
AggregateSales は、その明細から「商品別の金額合計」を Dictionary で集計しています。

ここまでを “データ側のロジック” として独立させておくことで、テンプレートが変わってもロジックは使い回せるようになります。

テンプレートコピー＋データ貼り付け＋ヘッダ情報を書き込む

Public Sub BuildOneReport(ByVal customerId As String, _
                          ByVal customerName As String, _
                          ByVal fromDate As Date, _
                          ByVal toDate As Date, _
                          ByVal templateSheet As String, _
                          ByVal outSheetName As String)
    Dim wsTemplate As Worksheet
    Set wsTemplate = Worksheets(templateSheet)
    
    Dim wsOut As Worksheet
    On Error Resume Next
    Set wsOut = Worksheets(outSheetName)
    On Error GoTo 0
    
    If wsOut Is Nothing Then
        wsTemplate.Copy After:=wsTemplate
        Set wsOut = ActiveSheet
        wsOut.Name = outSheetName
    Else
        wsOut.Cells.Clear
        wsTemplate.UsedRange.Copy Destination:=wsOut.Range("A1")
    End If
    
    wsOut.Range("B2").Value = customerName
    wsOut.Range("B3").Value = Format(fromDate, "yyyy-mm-dd") & " ～ " & Format(toDate, "yyyy-mm-dd")
    
    Dim filtered As Variant
    filtered = FilterSales(customerId, fromDate, toDate)
    
    Dim agg As Variant
    agg = AggregateSales(filtered)
    
    Dim startCell As String
    startCell = "B5"
    wsOut.Range(startCell).Resize(UBound(agg, 1), UBound(agg, 2)).Value = agg
    
    With wsOut.Range(startCell).CurrentRegion
        .Columns.AutoFit
        .Borders.LineStyle = xlContinuous
        .Columns(2).NumberFormatLocal = "#,##0"
    End With
End Sub
VB

ここでの大事なポイントを整理します。

テンプレートコピーは、「シートコピー」か「内容コピー」の2種類があります。
一度作ったレポートを上書きしたいケースも多いので、「既にシートがあれば中身だけ上書き」「なければシート新規」という動きをしています。
ヘッダ情報（顧客名、期間）はテンプレ内のセル（B2、B3）に直接書いています。ここはテンプレ設計とセットで決めておきます。
集計結果は B5 から貼り付け。CurrentRegion でまとめて罫線と桁区切り書式をかけることで、見やすいレポートになります。

一括実行：ReportList をループして「作成→PDF保存→ログ」を流す

ReportList を回して、一件ずつ BuildOneReport を呼ぶ

' ModReport_Bulk.bas
Option Explicit

Public Sub Run_BatchReports()
    Dim wsList As Worksheet
    Set wsList = Worksheets("ReportList")
    
    Dim a As Variant
    a = wsList.Range("A1").CurrentRegion.Value
    
    Dim r As Long
    For r = 2 To UBound(a, 1)
        Dim enable As String
        enable = UCase$(Trim$(CStr(a(r, 1))))
        
        If enable = "Y" Then
            Dim reportId As String
            Dim custId As String
            Dim custName As String
            Dim tmpl As String
            Dim outSheet As String
            Dim outFolder As String
            Dim outName As String
            Dim fromDate As Date
            Dim toDate As Date
            
            reportId = CStr(a(r, 2))
            custId = CStr(a(r, 3))
            custName = CStr(a(r, 4))
            tmpl = CStr(a(r, 5))
            outSheet = CStr(a(r, 6))
            outFolder = CStr(a(r, 7))
            outName = CStr(a(r, 8))
            fromDate = CDate(a(r, 9))
            toDate = CDate(a(r, 10))
            
            On Error GoTo ErrHandler
            
            If Not EnsureFolder(outFolder) Then
                wsList.Cells(r, 11).Value = "NG"
                wsList.Cells(r, 12).Value = "フォルダ作成に失敗: " & outFolder
                GoTo NextRow
            End If
            
            Call BuildOneReport(custId, custName, fromDate, toDate, tmpl, outSheet)
            
            Dim pdfPath As String
            pdfPath = outFolder & "\" & outName & ".pdf"
            
            Call ExportSheetToPdf(Worksheets(outSheet), pdfPath)
            
            wsList.Cells(r, 11).Value = "OK"
            wsList.Cells(r, 12).Value = "作成完了: " & pdfPath
            
            GoTo NextRow
ErrHandler:
            wsList.Cells(r, 11).Value = "NG"
            wsList.Cells(r, 12).Value = "エラー: " & Err.Number & " - " & Err.Description
            Err.Clear
        End If
        
NextRow:
    Next r
    
    MsgBox "一括レポート自動作成が完了しました。", vbInformation
End Sub
VB

重要な部分を少し深掘りします。

Enable列で「実行する／しない」を制御できるようにしておくと、試験運用や一部だけ再実行したい時に便利です。
各行ごとにフォルダをEnsureFolderで準備し、そのままPDF保存します。フォルダが作れない時点でNGログにして次行へ。
エラーが起きてもそこで全体が止まらないように、On Error GoToで行単位のエラー処理にしています。
Status（K列）と Message（L列）に結果を書き込むことで、「どのレポートが成功／失敗したか」が一目で分かります。

例題：顧客別月次レポートを一括作成するシナリオ

想定シナリオ

SalesData に日次の売上明細が溜まっている。
ReportList には「顧客IDごとの月次レポート」の一覧が登録されている。

例えば、ReportListの一行はこうなります。

Enable: Y
ReportID: RPT-202412-001
TargetKey: C00001（顧客ID）
TargetName: 株式会社サンプル
TemplateSheet: Report_Template
OutputSheetName: RPT_C00001_202412
OutputFolder: C:\Reports\2024-12
OutputFileName: RPT_C00001_202412
PeriodFrom: 2024/12/01
PeriodTo: 2024/12/31

この行を Run_BatchReports が拾うと、
C00001 の 12月売上データを元にレポートシートを作り、指定フォルダに PDF を出力します。
同様の行を顧客の数だけ登録しておけば、「ボタン1発で全顧客分レポート作成」が実現します。

落とし穴と対策（重要ポイントの深掘り）

テンプレとロジックの分離が甘いと、後から地獄になる

BuildOneReport の中で「集計ロジック」まで書いてしまうと、テンプレの変更やロジックの変更のたびに全部書き直しになります。
ここでは、FilterSales / AggregateSales を別モジュールに切り出し、「テンプレは見た目」「ロジックはデータ処理」と分けることで、
テンプレ変更とロジック変更を独立して行えるようにしています。

現場でテンプレを微調整したい時に、「VBAを触らなくてもレイアウトだけ変えられる」状態にしておくと、運用が非常に楽になります。

列番号・シート名をベタ書きしすぎると壊れやすい

ReportList の列は「意味」がはっきりしているので、仕様書にきちんとまとめておくことが大切です。
シート名やテンプレセル位置も、コメントや別シートに記載しておくと“迷子”になりません。

もう一段しっかりやるなら、列を「列名で探す」関数（ColumnIndex）を使って、列順を変えても壊れないように拡張するのも良いです。
初心者向けには、まず「列順は固定」と決めてしまう方が理解しやすいので、慣れてきたら“列名方式”にステップアップしていきましょう。

日付の型揺れ・文字列日付で期間判定が狂う

FilterSales 内では IsDate と CDate を使っていますが、そもそも SalesData の日付列が「文字列日付」になっていると、
挙動が怪しくなります。

本番では、日次取り込みのタイミングで「ToDateOrEmpty」などの関数を通し、日付列をきちんとDate型に揃えておくのがおすすめです。
データクレンジングテンプレと組み合わせると、かなり堅牢になります。

大量データで遅くなる問題

今回の FilterSales はループ＋Dictionaryで書いているため、数万行程度なら十分実務で使えますが、
10万行を超えるようなケースでは、
AdvancedFilter や ADO、ピボットテーブルを利用する方法も検討の余地があります。

最初はシンプルなループで動かしてみて、行数が増えてきたら “中身だけ高速版” に差し替える、という段階的な進め方がおすすめです。

まとめ：「誰に」「どんな条件で」「どこへ」作るかを一覧にし、テンプレ＋ロジックで一括生成する

一括レポート自動作成の本質は、
レポートそのものではなく、「レポートの仕様をExcelの1行として表現する」ことにあります。

ReportList の1行が、1件のレポートの“仕様書”。
テンプレシートが、見た目の“ワイヤーフレーム”。
VBAは、それを一件ずつ“組み立ててPDFにする職人”。

この役割分担を意識すると、後から仕様追加やテンプレ変更があっても、驚くほど楽に回せるようになります。

スポンサーリンク
タイトルとURLをコピーしました