【VBA 自動化教學11】一鍵建立目錄 + 自動產出摘要報表｜整合導覽與分析的全方位工具

在多工作表的 Excel 報表中，使用者常需要快速導覽並同時掌握各工作表的資料摘要。透過 VBA，我們可以建立一個「一鍵整合」功能：自動建立目錄頁，並在目錄中同時產出各工作表的統計摘要（筆數、總和、平均），讓報表更直觀、更專業。

功能概述

這段 VBA 程式碼可實現以下功能：

• 自動建立「目錄」工作表
• 為每張工作表建立超連結，快速導覽
• 自動計算各工作表指定欄位的統計摘要（筆數、總和、平均）
• 將摘要結果整合到目錄頁，形成總覽表格

VBA 程式碼範例

Sub CreateIndexAndSummary()
    Dim wsIndex As Worksheet
    Dim ws As Worksheet
    Dim i As Integer
    Dim LastRow As Long
    Dim CountVal As Long, SumVal As Double, AvgVal As Double

    ' 建立或清空目錄工作表
    On Error Resume Next
    Set wsIndex = ThisWorkbook.Sheets("目錄")
    If wsIndex Is Nothing Then
        Set wsIndex = ThisWorkbook.Sheets.Add(Before:=Sheets(1))
        wsIndex.Name = "目錄"
    Else
        wsIndex.Cells.Clear
    End If
    On Error GoTo 0

    ' 設定標題
    wsIndex.Range("A1").Value = "📋 工作表目錄與摘要"
    wsIndex.Range("A1").Font.Bold = True
    wsIndex.Range("A1").Font.Size = 14

    ' 標題列
    wsIndex.Range("A2").Value = "工作表名稱"
    wsIndex.Range("B2").Value = "筆數"
    wsIndex.Range("C2").Value = "總和"
    wsIndex.Range("D2").Value = "平均"

    i = 3
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> wsIndex.Name Then
            ' 建立目錄超連結
            wsIndex.Hyperlinks.Add Anchor:=wsIndex.Cells(i, 1), _
                Address:="", _
                SubAddress:="'" & ws.Name & "'!A1", _
                TextToDisplay:=ws.Name

            ' 計算摘要（以 B 欄為例）
            LastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
            If LastRow > 1 Then
                CountVal = Application.WorksheetFunction.Count(ws.Range("B2:B" & LastRow))
                SumVal = Application.WorksheetFunction.Sum(ws.Range("B2:B" & LastRow))
                If CountVal > 0 Then
                    AvgVal = SumVal / CountVal
                Else
                    AvgVal = 0
                End If
            Else
                CountVal = 0: SumVal = 0: AvgVal = 0
            End If

            ' 輸出摘要到目錄頁
            wsIndex.Cells(i, 2).Value = CountVal
            wsIndex.Cells(i, 3).Value = SumVal
            wsIndex.Cells(i, 4).Value = AvgVal

            i = i + 1
        End If
    Next ws

    ' 美化表格
    With wsIndex.Range("A2:D" & i - 1)
        .Font.Name = "微軟正黑體"
        .Font.Size = 11
        .Borders.LineStyle = xlContinuous
        .Interior.Color = RGB(240, 248, 255)
    End With

    wsIndex.Columns("A:D").AutoFit
    MsgBox "目錄與摘要報表已建立完成", vbInformation
End Sub

程式碼詳細解說（初學者導向）

• 建立或清空目錄頁： 若已有「目錄」工作表則清空，否則新增一張並命名為「目錄」。
• 設定標題與表頭： 在 A1 顯示「工作表目錄與摘要」，並在 A2:D2 建立表頭（工作表名稱、筆數、總和、平均）。
• 迴圈處理每張工作表：

1. 建立超連結，讓使用者可快速跳轉。
2. 計算 B 欄的統計值（筆數、總和、平均）。
3. 將結果輸出到目錄頁的對應列。

• 美化表格： 設定字型、框線與底色，並自動調整欄寬。
• 提示訊息： 使用 MsgBox 提示使用者目錄與摘要已建立完成。

操作步驟與使用方式

• 將程式碼貼入 Excel 的模組中（例如 Module1）
• 確保各工作表的資料在 B 欄（從第 2 列開始）
• 執行 CreateIndexAndSummary 程式
• 程式會自動建立目錄頁並產出摘要表格

應用場景與延伸建議

• 多部門報表快速導覽並查看各部門統計
• 教學範本或作業集集中管理與分析
• 客戶資料或專案進度表快速彙整

延伸功能建議：

• 支援多欄位摘要（例如 B、C、D 欄）
• 加入「回到目錄」連結至各工作表
• 自動產出圖表（長條圖或圓餅圖）
• 匯出目錄與摘要為 PDF 報表

常見問題 FAQ

Q1：是否可以指定不同欄位進行統計？

可以修改程式碼中的 "B" 為其他欄位代號，例如 "C" 或 "D"。

Q2：是否可以同時統計多欄位？

可以在迴圈中加入多個統計計算，並將結果輸出到目錄頁。

Q3：是否可以在目錄頁加上圖表？

可以使用 Charts.Add 方法建立圖表，將摘要結果視覺化。

結語與延伸閱讀

這段 VBA 程式碼提供了完整的「一鍵建立目錄 + 自動產出摘要報表」功能，結合導覽與分析，讓報表更直觀、更專業。後續你可以加入多欄位統計、圖表產出與 PDF 匯出模組，打造全方位的報表自動化工具。

下一篇將介紹如何「一鍵快速清理並格式化資料」，敬請期待【VBA 自動化教學】第 12 篇~~~