Excel 功能、函數、巨集與 VBA 如何選？從重複操作走向自動化

重點摘要

Excel 功能負責操作資料，函數負責計算，巨集負責重播步驟，VBA 負責加入判斷與動態控制。
錄製巨集會將操作轉成 VBA 程式碼，適合當作學習語法與建立自動化雛形的起點。
錄製前應先拆解流程；資料範圍、筆數變動與例外狀況，通常要在錄製後用 VBA 修正。
不必一開始就從空白畫面寫程式，先組合既有 Excel 能力，再用 VBA 補足重複執行與變動需求。
巨集檔案應儲存為 .xlsm 或 .xlsb，並只啟用來源可信的程式碼。

Excel 自動化不是用 VBA 取代所有功能，而是先運用 Excel 的排序、篩選、格式化與函數完成工作邏輯，再讓巨集或 VBA 重複執行。理解四者分工，能避免把簡單問題寫成複雜程式，也能更快找到值得自動化的流程。

Excel 功能、函數、巨集與 VBA 分別做什麼？

四者不是互相競爭的工具，而是一條由人工操作逐步走向自動化的能力鏈。Excel 功能處理介面操作，函數在儲存格中計算結果，巨集錄下重複步驟，VBA 則讓流程能根據資料量、條件與使用者選擇改變。

工具	主要用途	適合情境	常見限制
Excel 內建功能	排序、篩選、格式化、樞紐分析、資料驗證	單次操作或互動分析	每次更新可能需要重新操作
工作表函數	計算、查找、判斷與文字處理	儲存格結果需要隨資料更新	跨檔案流程與介面操作較不方便
錄製巨集	記錄操作並產生 VBA 程式碼	步驟固定、需要反覆執行	會記錄多餘動作，固定範圍不會自動適應資料量
VBA	控制活頁簿、工作表、範圍、迴圈、判斷與表單	多檔案、動態範圍、條件流程與跨程式整合	需要測試、維護與巨集安全管理

一個常見組合：SUM 函數加上 VBA

假設薪資表的 E、F、G 欄分別是薪資、獎金與加班費，H 欄要計算總薪資。計算邏輯仍可使用 Excel 熟悉的 SUM，VBA 的工作則是找出最後一列，把公式自動填到實際資料範圍。

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).FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
    End If
End Sub

這段程式沒有重新發明加總邏輯，而是把「找出資料終點」與「批次填入公式」自動化。這正是 Excel 函數與 VBA 分工合作的典型方式。

錄製巨集為什麼是學習 VBA 的好起點？

巨集錄製器會把你在 Excel 中執行的多數動作轉成 VBA 程式碼，因此很適合用來觀察「某個操作對應哪一段語法」。Microsoft 也指出，錄製器會記下步驟、儲存格操作與格式設定；若錄錯動作，它同樣會被寫進程式碼。

前置條件

Windows 版桌面 Excel；不同版本的功能區名稱可能略有差異。
建議先顯示「開發人員」索引標籤。
使用測試檔案，不要直接在唯一一份正式資料上練習。
準備一套可以明確重複的操作，例如標題格式、框線或公式填入。

從錄製到修改的操作步驟

先在紙上或文字檔列出完整工作流程。
把流程拆成單一目的，例如「設定標題格式」與「套用資料框線」。
在「開發人員」索引標籤選擇「錄製巨集」，輸入清楚的巨集名稱。
只執行事先規劃的操作，完成後立即停止錄製。
開啟巨集清單，選取巨集後按「編輯」，在 Visual Basic 編輯器檢查程式碼。
刪除不必要的 Select、Selection 與重複格式設定。
將固定範圍改成由 lastRow 等變數決定的動態範圍。
在測試資料上多執行幾次，確認資料增加、減少或為空白時都能合理處理。

如何確認結果正確？

在原始資料新增幾列，再執行巨集，確認新增資料也被處理。
刪除部分資料後重跑，確認格式不會延伸到大量空白列。
使用「逐步執行」或按 F8，一行一行觀察程式作用的位置。
儲存、關閉並重新開啟檔案，確認巨集保留且可以再次執行。

哪些動作適合分開錄製？

當一個流程同時包含資料匯入、計算與美化時，建議先分成數個可獨立測試的小巨集。分開錄製能較快找到錯誤，也方便日後只重跑其中一段。

建議拆分方式

匯入資料：開啟或選取來源檔案，將內容帶入目標活頁簿。
整理資料：刪除空白列、統一欄位與調整資料型態。
建立計算：填入 SUM、IF、查找或其他公式。
美化報表：設定標題、框線、欄寬與數字格式。
輸出結果：另存檔案、建立 PDF 或顯示完成訊息。

錄製時不一定要把「選取哪一段資料」寫死。若資料每月都會增減，先錄製格式與操作，再用 VBA 找出最後一列，通常比直接錄下固定的 A1:H1444 更容易維護。

示範情境：每月薪資資料如何逐步自動化？

假設人資人員每月會收到一張欄位固定、筆數不固定的薪資明細。原本需要手動填入總薪資、設定標題色彩、套用框線與調整數字格式。可先使用 SUM 建立計算邏輯，再錄製報表格式，最後修改 VBA 讓範圍依 A 欄最後一筆資料自動調整。

產出結果是按一次巨集即可完成計算與格式化；但如果來源欄位名稱、順序或工作表名稱改變，程式仍需調整。因此，真正穩定的自動化除了程式碼，也需要固定資料規格與測試案例。

常見錯誤與排除方式

巨集只處理錄製時的固定範圍

原因通常是程式碼出現 Range("A1:H1444")。應先找出最後一列，再組成動態範圍，並確認判斷欄位每筆資料都有值。

執行巨集後跳到不同工作表或儲存格

錄製器可能保留大量 Select 與 Activate。建議使用完整物件限定，例如 ws.Range("H2:H" & lastRow)，直接指定工作表與範圍。

儲存後巨集消失

標準 .xlsx 不會保存 VBA 巨集。請另存為「Excel 啟用巨集的活頁簿（.xlsm）」或其他支援巨集的格式。

巨集被安全性設定封鎖

不要任意降低所有巨集的安全層級。先確認檔案來源、數位簽章或受信任位置，只啟用可信內容。

常見問題

完全不會程式也可以學錄製巨集嗎？

可以。先挑選你已熟悉、每次步驟一致的 Excel 工作，錄製後觀察程式碼。接著只學習如何指定工作表、找出最後一列與設定範圍，就能逐步把固定巨集改成可重複使用的流程。

函數可以完全改寫成 VBA 嗎？

技術上許多計算可以改寫，但不一定值得。若工作表函數能清楚表達邏輯並隨資料即時更新，通常保留函數較容易讓同事理解；VBA 更適合控制批次操作、檔案與流程。

錄製巨集和自己寫 VBA 有什麼差別？

錄製巨集是讓 Excel 把操作轉成程式碼，適合取得語法雛形；自行撰寫 VBA 能加入變數、迴圈、判斷、錯誤處理及動態範圍，較能因應真實工作變化。

Excel 網頁版可以執行 VBA 嗎？

VBA 主要在桌面版 Excel 中使用。若工作流程需要在瀏覽器或雲端執行，應另行評估 Office Scripts、Power Automate 或其他 Microsoft 365 自動化方式。

參考來源

---

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