如何在 Excel 中對銀行交易進行分類？

作者Siluvia修改日期2025-10-23

管理個人或企業財務時，經常需要檢視詳細的每月銀行交易清單。這些交易記錄可能包含各式各樣的描述，從餐廳、商店到公用事業或服務皆有。若能將每筆交易歸入明確的類別（例如「）Takeout」、「Groceries」、「Utilities」或「Family fee」），將使支出追蹤與分析更加清晰。透過在 Excel 中根據交易描述中的特定關鍵字自動分類，您每月都能更清楚掌握成本結構與消費模式。
如下方截圖所示，假設您在 B 欄中有供應商或服務的原始資料，並希望簡化為一組類別（例如，任何包含「）Mc Donalds」的交易應標示為「Takeout」，而「Walmart」則歸類為「Family fee」），這完全依據您個人或組織的規則。本逐步教學將介紹多種實用解決方案，包括基於公式的做法與進階自動化方法。

分類銀行交易

目錄：
在 Excel 中使用公式對銀行交易進行分類
 VBA 程式碼－根據預先定義的清單，透過巨集自動分類
 其他 Excel 內建方法－使用適用於 Excel 的 Microsoft Power Query 搭配條件式欄位邏輯

在 Excel 中使用公式對銀行交易進行分類

如果您希望採用直接且基於公式的方式在 Excel 中對銀行交易進行分類，請遵循以下步驟。此方法適合需要快速取得結果，且願意維護小型關鍵字與類別對照表的使用者。

太神奇了！在 Excel 中使用高效 Tabs，就像 Chrome、Edge、Firefox 和 Safari 一樣！
每天為您節省 50% 的時間，並減少數千次滑鼠點擊！

首先，在主要交易清單之外建立兩個「輔助欄位」—一欄列出關鍵字（對應交易描述中可能出現的內容），另一欄則列出您希望與每個關鍵字連結的類別。

1. 在此範例中，請將所有要比對的關鍵字（例如「Mc Donalds」、「Walmart」等）填入 A 欄第 30 至 41 列，並將對應的類別名稱（如「Takeout」、「Family fee」等）填入 B 欄第 30 至 41 列。
若您的關鍵字或類別清單較多或經常變動，只需調整範圍以涵蓋全部項目即可。請參閱截圖：

準備資料範例

2. 接下來，請點選目標輸出欄位的第一個儲存格（例如資料最後一列旁的 F3），並輸入下列陣列公式。由於這是陣列公式，請務必按下 Ctrl + Shift + Enter（而非僅按 Enter）來確認。此公式將自動找出描述中首次出現的關鍵字，並傳回對應的類別。

=IFERROR(INDEX(B$30:B$41,MATCH(TRUE,ISNUMBER(SEARCH($A$30:$A$41,B3)),0)),"Other")

在第一列的公式正常運作後，請向下拖曳自動填滿控制點，將分類公式套用至所有剩餘的交易列。

使用公式分類銀行交易

參數說明：

1)B$30:B$41 是查閱表格中與各關鍵字對應的類別標籤清單。

2)$A$30:$A$41 是包含所有待檢查關鍵字的範圍。

3)B3 代表交易清單中的描述欄位；請根據實際資料調整此參照。

4)「Other」是當描述內容未匹配任何關鍵字時的預設結果。

請務必根據實際資料所在位置調整所有範圍與起始儲存格。若您的關鍵字或分類清單擴充，只需在公式中相應擴大範圍即可。若出現「#N/A」或其他錯誤結果，請檢查關鍵字是否有空格問題，或分類值是否遺漏。

優缺點分析：此公式驅動的方法設定快速，若您的分類規則不常變動，維護成本也較低。然而，一旦關鍵字清單變得更複雜，或需頻繁更新類別，管理輔助欄位與公式便可能變得繁瑣，此時可考慮透過 VBA 或適用於 Excel 的 Microsoft Power Query 實現自動化。

實用提示：若某筆描述中包含多個相符的關鍵字，系統將僅依據清單中第一個找到的關鍵字來判定類別。如欲優先處理特定關鍵字，請將其置於查閱欄位的較上方。

常見問題排除：若得到非預期的結果，請再次檢查查閱範圍，並確保關鍵字拼寫一致且完整。同時，也請檢查交易描述中是否有額外空格或格式差異。

VBA 程式碼－根據預先定義的清單，透過巨集自動將交易描述與類別進行比對

此解決方案運用 VBA 巨集自動化交易與類別的比對流程，提供更強的掌控力與擴充性，特別適合交易量龐大的使用者，或希望讓關鍵字對應類別的參照更具動態彈性、同時減少手動管理公式的需求。

適用情境：當交易清單冗長、資料頻繁更新，或您希望擺脫手動公式維護的困擾時，VBA 巨集能更高效地處理每筆描述，自動指派正確類別，並支援自訂邏輯與提示功能！

操作步驟：

準備一份關鍵字對應類別清單，類似於公式方法中使用的輔助欄位（例如，從第 30 列開始的 A 欄與 B 欄）。
按下 Alt + F11，立即開啟 Visual Basic for Applications 編輯器！在 VBA 視窗中，點選插入 > 模組，即可新增一個模組。

將下列程式碼複製並貼上至模組中：

Sub CategorizeTransactions()
    Dim lastRow As Long
    Dim i As Long
    Dim descCell As Range
    Dim kwRow As Long
    Dim kwRange As Range
    Dim catRange As Range
    Dim kwCount As Long
    Dim catResult As String
    Dim matched As Boolean
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    kwCount = Cells(Rows.Count, "A").End(xlUp).Row - 29
    Set kwRange = Range("A30:A" & 29 + kwCount)
    Set catRange = Range("B30:B" & 29 + kwCount)
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    For i = 3 To lastRow
        Set descCell = Cells(i, "B")
        catResult = "Other"
        matched = False
        
        For kwRow = 1 To kwCount
            If InStr(1, descCell.Value, kwRange.Cells(kwRow, 1).Value, vbTextCompare) > 0 Then
                catResult = catRange.Cells(kwRow, 1).Value
                matched = True
                Exit For
            End If
        Next kwRow
        
        Cells(i, "F").Value = catResult
    Next i
End Sub

使用方式：

在 VBA 編輯器中，點擊執行按鈕，或按下 F5 即可執行巨集。此巨集將自動逐一處理 B 欄中的每筆交易描述，與關鍵字清單比對，並將相符的類別（若無相符項目則標記為「Other」）寫入同一列的 F 欄。
您可以調整巨集內的設置的行數／範圍，以符合您的資料配置—例如變更交易描述的起始位置或輔助清單的位置。請確保您的關鍵字清單中沒有空白儲存格，且類別明確且唯一，以便輕鬆參照。

優缺點分析：VBA 解決方案能靈活應對更複雜的規則，只需更新關鍵字／類別清單，即可隨時重新執行，且無需使用陣列公式。然而，巨集需在 Excel 中啟用自動化功能，可能不適用於所有使用者或環境。

實用提示：請將您的 VBA 巨集儲存在可重複使用的檔案中，並在執行巨集前務必備份資料，以防意外覆寫！

問題排除建議：若類別未如預期更新，請檢查關鍵字與類別清單是否一致，並確認儲存格中沒有隱藏字元。若您使用「vbTextCompare」，VBA 會忽略大小寫，但格式差異仍可能導致比對失敗。

其他 Excel 內建方法－使用適用於 Excel 的 Microsoft Power Query 透過條件式欄位邏輯設定基於規則的分類

如果您偏好現代化且無需編寫程式碼的自動化方式，適用於 Excel 的 Microsoft Power Query 提供了一種強大的銀行交易分類方法。當您從 CSV、線上來源匯入交易資料，或需要管理動態且不斷演進的分類規則時，此方法尤其理想，因為它能集中管理邏輯，並在無需複雜公式或 VBA 指令稿的情況下提供輕鬆更新。

操作步驟：

首先，請確保您的交易資料已格式化為表格或明確的數據區域。選取表格中的任意儲存格，然後前往資料 > 從表格/範圍，即可將資料載入適用於 Excel 的 Microsoft Power Query。
在適用於 Excel 的 Microsoft Power Query 編輯器中，按一下新增欄位 > 條件式欄位，即可建立一個基於規則的新類別欄位。
定義您的比對規則，例如：
- 如果 Description包含「Mc Donalds」，則輸出「Takeout」
- 如果 Description包含「Walmart」，則輸出「Family fee」
- 否則，輸出「Other」
針對每個條件，使用「包含」比較運算子，並輸入對應的關鍵字與類別。
按一下「確定」建立條件式欄位，再選取關閉並載入，即可將分類結果返回 Excel。

優缺點分析：適用於 Excel 的 Microsoft Power Query 在規則變更時具備高度彈性，能無縫整合外部資料來源，並於交易資料或規則更新後快速重新整理結果。此外，處理超大型資料集時，效率遠勝手動公式。然而，Microsoft Power Query for Excel 需進行基本設定，且使用者需熟悉其操作介面，對部分用戶而言可能是一次全新的體驗。

實用提示：在「條件欄位」對話框中，將規則依優先順序由上至下排列；系統會套用第一個符合條件的規則。您可以在適用於 Excel 的 Microsoft Power Query 中輕鬆編輯、新增或刪除規則，而不會影響 Excel 工作表中的主要公式。

錯誤提醒：若您匯入新的交易資料後，結果未如預期更新，請點擊位於 Excel「資料」索引標籤中的重新整理。請務必確認 Microsoft Power Query 中的拼字與關鍵字是否完全相符，因為任何不匹配都可能導致分類無法正確指派。

疑難排解建議：若您的分類未正確顯示，請檢查條件欄位規則是否存在衝突或遺漏的情況。在對完整資料集套用變更前，建議先以少量範例資料進行測試。

總結而言，無論您選擇使用陣列公式、VBA 巨集的彈性，還是適用於 Excel 的 Microsoft Power Query 的簡化自動化功能，Excel 都提供了多種實用方式，讓您高效地為銀行交易資料進行分類。隨著交易資料或分類標準的演變，務必定期檢視分類規則及其結果的一致性。

最佳 Office 生產力工具

🤖	KUTOOLS AI 助手：基於以下內容徹底革新數據分析：智慧執行｜產生程式碼｜建立自訂公式｜分析資料並產生圖表｜呼叫增強函數……
	熱門功能：尋找、醒目提示或標記重複值｜刪除空白行｜合併列或儲存格而不遺失資料｜不使用公式的四捨五入……
	高級 LOOKUP：多重條件 VLookup ｜多重數值 VLookup ｜跨多個工作表 VLookup ｜模糊查找……
	高級下拉列表：快速建立下拉式清單｜相依式下拉式清單｜多選下拉式清單……
	欄位管理員：新增指定數量的欄位｜移動欄位｜切換隱藏欄位的可見狀態｜比較範圍與欄位……
	精選功能：網格聚焦｜設計視圖｜增強編輯欄｜工作簿與工作表管理員｜資源庫（自動文字）｜日期提取｜合併工作表｜加密／解密儲存格｜依清單傳送電子郵件｜超級篩選｜特殊篩選（篩選粗體儲存格／斜體／刪除線……） ......
	頂尖 15 工具組：12 文字工具（添加文本，刪除特定字符，……）｜ 50+ 圖表類型（甘特圖，……）｜ 40+ 實用公式（基於生日計算年齡，……）｜ 19 插入工具（插入二維碼，從路徑插入圖片，……）｜ 12 轉換工具（金額轉大寫，匯率轉換，……）｜ 7 合併和拆分工具（高級合併行，分割儲存格，……）｜……以及更多