圓本就想學BeLearning.Today
首頁／學習資源／Excel VBA 效率提升／Excel VBA 自動化實戰：合併多個活頁簿與動態薪資報表
回到 Excel VBA 效率提升Excel VBA 效率提升

Excel VBA 自動化實戰：合併多個活頁簿與動態薪資報表

以可直接調整的 VBA 範例，完成多活頁簿合併、多工作表彙整與動態薪資報表，並提供資料筆數、年度與總計驗證方法。

作者：施文華發布：更新：
#Excel VBA 效率提升#合併活頁簿#動態範圍#薪資報表
Excel VBA 自動化實戰：合併多個活頁簿與動態薪資報表
給第一次操作的你

新手跟著做：安全測試 VBA 合併活頁簿

  1. 複製三個小型測試檔到獨立資料夾，不直接使用正式檔案。

  2. 確認每個活頁簿的欄位名稱、順序與資料型別一致。

  3. 在巨集檔設定來源資料夾與輸出工作表。

  4. 逐步執行 VBA，觀察開檔、讀取、寫入及關檔結果。

  5. 加入空資料夾、錯誤檔名、重複資料與格式異常測試。

  6. 核對筆數與薪資計算後，再備份並執行正式資料。

重點摘要

Excel VBA 適合把「選檔、開檔、複製、合併、計算與格式化」串成一次完成的工作流程。本文以逐字稿中的年度銷售檔與 1,443 筆薪資資料為示範，說明兩種活頁簿合併結果，以及如何建立會跟著資料量調整的報表。

多個 Excel 活頁簿可以合併成哪些結果？

開始寫 VBA 前，必須先決定目標是保留每份檔案的工作表，還是把所有明細追加成一張清單。前者方便逐年查看原貌；後者適合篩選、樞紐分析與後續資料模型。

合併方式產出結構適合用途主要檢查點
多檔合成多表一個新活頁簿包含多張工作表保存各年度或各單位原始版面工作表名稱重複、公式外部連結
多檔合成單表一張工作表連續追加所有明細篩選、函數、樞紐分析與 Power Query欄位名稱、欄位順序、重複標題列

假設資料夾內有 2001 年到 2010 年的活頁簿，每個檔案各一張工作表。第一種結果會建立包含 10 張工作表的新活頁簿；第二種結果則把日期、銷售員代碼、產品代號、地區與銷售量追加成一張彙總表。

如何把多個活頁簿合成一個活頁簿的多張工作表？

這種做法會逐一開啟使用者選取的檔案，將來源活頁簿的第一張工作表複製到執行巨集的活頁簿。它保留各表原貌，但工作表名稱、外部連結與來源公式仍需另外管理。

前置條件

VBA 範例

Sub 多檔合成多張工作表()
    Dim selectedFiles As Variant
    Dim fileItem As Variant
    Dim sourceBook As Workbook
    Dim targetBook As Workbook

    Set targetBook = ThisWorkbook

    selectedFiles = Application.GetOpenFilename( _
        FileFilter:="Excel 活頁簿 (*.xlsx;*.xlsm),*.xlsx;*.xlsm", _
        Title:="選取要合併的活頁簿", _
        MultiSelect:=True)

    If VarType(selectedFiles) = vbBoolean Then Exit Sub

    Application.ScreenUpdating = False
    On Error GoTo ErrorHandler

    For Each fileItem In selectedFiles
        Set sourceBook = Workbooks.Open(CStr(fileItem), ReadOnly:=True)
        sourceBook.Worksheets(1).Copy _
            After:=targetBook.Worksheets(targetBook.Worksheets.Count)
        sourceBook.Close SaveChanges:=False
        Set sourceBook = Nothing
    Next fileItem

SafeExit:
    Application.ScreenUpdating = True
    Exit Sub

ErrorHandler:
    If Not sourceBook Is Nothing Then sourceBook.Close SaveChanges:=False
    MsgBox "合併中斷：" & Err.Description, vbExclamation
    Resume SafeExit
End Sub

如何確認結果正確？

如何把多個活頁簿追加成一張彙總工作表？

當來源欄位一致、目標是篩選或分析時，可以把第一份檔案的標題與資料寫入彙總表，後續檔案只追加標題列以下的明細。程式應先清空舊結果，避免重複執行後資料加倍。

Sub 多檔合成單一工作表()
    Dim selectedFiles As Variant
    Dim fileItem As Variant
    Dim sourceBook As Workbook
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim sourceLastRow As Long
    Dim sourceLastCol As Long
    Dim targetNextRow As Long
    Dim firstFile As Boolean

    Set targetSheet = ThisWorkbook.Worksheets("彙總")
    targetSheet.Cells.Clear
    firstFile = True

    selectedFiles = Application.GetOpenFilename( _
        FileFilter:="Excel 活頁簿 (*.xlsx;*.xlsm),*.xlsx;*.xlsm", _
        Title:="選取要彙總的活頁簿", _
        MultiSelect:=True)

    If VarType(selectedFiles) = vbBoolean Then Exit Sub

    Application.ScreenUpdating = False
    On Error GoTo ErrorHandler

    For Each fileItem In selectedFiles
        Set sourceBook = Workbooks.Open(CStr(fileItem), ReadOnly:=True)
        Set sourceSheet = sourceBook.Worksheets(1)

        sourceLastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
        sourceLastCol = sourceSheet.Cells(1, sourceSheet.Columns.Count).End(xlToLeft).Column

        If sourceLastRow >= 2 Then
            targetNextRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row
            If firstFile Then
                sourceSheet.Range(sourceSheet.Cells(1, 1), _
                    sourceSheet.Cells(sourceLastRow, sourceLastCol)).Copy _
                    Destination:=targetSheet.Cells(1, 1)
                firstFile = False
            Else
                sourceSheet.Range(sourceSheet.Cells(2, 1), _
                    sourceSheet.Cells(sourceLastRow, sourceLastCol)).Copy _
                    Destination:=targetSheet.Cells(targetNextRow + 1, 1)
            End If
        End If

        sourceBook.Close SaveChanges:=False
        Set sourceBook = Nothing
    Next fileItem

    targetSheet.Columns.AutoFit

SafeExit:
    Application.ScreenUpdating = True
    Exit Sub

ErrorHandler:
    If Not sourceBook Is Nothing Then sourceBook.Close SaveChanges:=False
    MsgBox "彙總中斷：" & Err.Description, vbExclamation
    Resume SafeExit
End Sub

執行步驟

  1. 在目標活頁簿建立名稱為「彙總」的工作表。
  2. 按 Alt + F11 開啟 Visual Basic 編輯器，插入標準模組。
  3. 貼上程式碼並儲存為 .xlsm。
  4. 回到 Excel，從巨集清單執行「多檔合成單一工作表」。
  5. 在檔案選擇視窗多選要合併的年度檔案，按「開啟」。
  6. 完成後使用日期欄篩選，確認 2001 年至 2010 年等來源年度都有資料。

這個範例有哪些適用限制？

如何依資料筆數建立動態薪資報表？

動態報表的關鍵是先找出實際資料最後一列，再針對有資料的範圍填入公式、設定框線與數字格式。這樣即使本月是 1,443 筆、下月資料增加或減少，也不必手動改程式範圍。

假設欄位如下：A 姓名、B 公司別、C 部門、D 職稱、E 薪資、F 獎金、G 加班費、H 總薪資。

Sub 建立動態薪資報表()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim reportRange As Range

    Set ws = ThisWorkbook.Worksheets("薪資資料")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "目前沒有可處理的薪資資料。", vbInformation
        Exit Sub
    End If

    Set reportRange = ws.Range("A1:H" & lastRow)

    ws.Range("H2:H" & lastRow).FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"

    With ws.Range("A1:H1")
        .Font.Bold = True
        .Interior.Color = RGB(189, 215, 238)
        .HorizontalAlignment = xlCenter
    End With

    With reportRange.Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With

    ws.Range("E2:H" & lastRow).NumberFormat = "#,##0"
    reportRange.Columns.AutoFit

    MsgBox "薪資計算與報表格式化已完成。", vbInformation
End Sub

如何重複練習或重新產生？

可以另寫一個「清除練習結果」巨集，只清除 H 欄公式與 A:H 的格式，不刪除原始資料。清除前應確認哪些欄位屬於輸入、哪些屬於計算結果，避免把薪資明細一併移除。

Sub 清除薪資報表練習結果()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("薪資資料")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow >= 2 Then ws.Range("H2:H" & lastRow).ClearContents
    ws.Range("A1:H" & Application.Max(lastRow, 1)).ClearFormats
End Sub

示範情境：年度銷售資料到管理報表

某公司每年各有一份銷售明細，欄位包含日期、銷售員代碼、產品代號、地區與銷售量。若主管只想保留各年度原貌，可將每份第一張工作表複製到同一活頁簿；若要依日期、地區或產品跨年度分析，則應合成單一清單。

合併完成後，先用日期篩選確認每個年度都有出現，再比較各來源檔案筆數總和與彙總表筆數。這個驗證不能省略，因為欄位錯位、空白列或標題重複都可能讓巨集正常結束，卻留下不完整結果。

常見錯誤與排除方式

找不到「彙總」或「薪資資料」工作表

程式中的名稱必須與工作表標籤完全一致，包括空格與全形字元。可修改程式名稱，或先依指定名稱建立工作表。

合併後重複出現標題列

第一個檔案要從第 1 列複製，後續檔案應從第 2 列開始。如果來源檔案的標題列位置不一致，必須先統一格式或加入額外判斷。

第二次執行後資料重複

若要每次重新產生完整彙總，執行前應清空目標工作表；若需求是追加新資料，則需建立來源檔名或唯一鍵的重複檢查，不能直接沿用清空邏輯。

程式發生錯誤後畫面更新沒有恢復

關閉 ScreenUpdating 後應設計統一的離開與錯誤處理區段，確保發生錯誤時也能重新開啟畫面更新並關閉來源活頁簿。

常見問題

合併多個活頁簿應該用 VBA 還是 Power Query？

來源欄位固定、主要目標是合併與重新整理時，Power Query 通常較容易追蹤轉換步驟；若需要控制檔案、工作表、格式、訊息與其他 Excel 動作，VBA 更有彈性。兩者也可以搭配使用。

VBA 可以一次選取多個檔案嗎？

可以。Application.GetOpenFilename 搭配 MultiSelect:=True 會傳回使用者選取的檔案清單；若按取消，則會傳回布林值，因此程式必須先判斷取消情況。

為什麼要用 A 欄判斷最後一列？

範例假設 A 欄每一筆都有姓名或日期，因此適合作為資料終點依據。如果 A 欄可能空白，應改用一定有值的唯一識別欄，或使用其他方式判斷實際範圍。

合併完成後怎麼確認沒有漏資料？

先比較來源檔案的資料筆數總和與彙總結果，再抽查首尾資料、來源年度與重要欄位。若有唯一鍵，還應檢查是否重複或遺漏。

延伸閱讀

參考來源

---

作者：施文華 企業自動化顧問／講師，專注於 Excel、VBA、Power BI、Power Automate 與企業流程自動化。本文依課程實作逐字稿、Excel 操作經驗與 Microsoft 官方文件整理。 作者介紹：關於施文華老師 最後更新：2026-09-04 適用版本／測試環境：Windows 版 Microsoft 365 Excel；Excel 2016 以上版本可依相同概念操作，介面名稱可能略有差異。

施文華｜企業自動化顧問／講師

專注 Excel、VBA、Power BI、Power Automate 與企業流程自動化，從真實工作問題出發，協助學員建立可持續使用的能力。