如何在試算表中進行交易對帳（不需手動逐列比對）
核對兩個交易清單意味著要找出四種差異：一側缺失的資料列、另一側重複的資料列、金額不一致，以及同一筆付款落在不同的期間。除此之外，其他工作都只是圍繞這四種差異進行的簿記。
直覺的做法是將兩個檔案並排並開始比對資料列。在資料量大約兩百列以內時，這種方法還行得通，但超過這個數量，就會演變成耗費一整個下午，最後卻得出一個無人能稽核的數字。
本指南將探討為什麼試算表在處理這項特定任務時會顯得吃力、人們常用的三種因應對策，以及每種對策的瓶頸所在。這是一套資料工作流程，而非會計建議。
為什麼在試算表中對帳比想像中更難
試算表比對的是儲存格。而對帳比對的是事件，且同一個事件在兩側的記錄中很少看起來完全相同。
信用卡付款可能在您的帳簿中只出現一次，但在金流商匯出資料中卻出現兩次（被拆分為費用和手續費）。供應商發票在一個系統中的參照編號可能是 INV-0042，而在另一個系統中則是 INV42。在 31 日發起的轉帳可能會在 1 日才入帳，這會打亂整個月份的對帳。
這些都不是資料錯誤。它們是兩個系統記錄同一現實時的正常樣貌，沒有任何一種查閱公式能單獨解決這些問題。
此外，每個月還會有人掉入捨入陷阱。以完整浮點數精度儲存的貨幣值可能會在小數點後第四位出現偏差，因此兩個顯示為 1,204.50 的金額在進行精確相等測試時會判定為不符。
資料量也會改變問題的本質。在五十列時，一個人還能同時記住兩個清單。但在五千列時，這項任務就變成了在大量相符的資料中尋找少數幾個異常值。人類的注意力並不適合處理這種工作。
這會讓您付出什麼代價
月底的收尾拖延。 前百分之九十的資料列在幾分鐘內就能比對完成。但剩下的少數幾列卻需要花費數小時，因為每一列都需要人工判斷它屬於四種差異類型中的哪一種。
無法稽核的結果。 當用肉眼進行比對時，檔案一關閉，工作軌跡就消失了。六週後，沒有人能重建為什麼當初會將這兩列視為同一筆付款。
未被發現的錯誤。 與錯誤對應項比對的重複資料會在總額中抵消，看起來像是完美的對帳。總額一致並不能證明每列資料都一致。
這三種代價會產生加乘效應。漫長的收尾工作會導致疲勞，疲勞會讓人走捷徑，而走捷徑正是將錯誤比對記錄為正確比對的主因。
人們嘗試的因應對策
方案 1：在進行任何比對之前先比較總額
先比較群組總額，而不是逐列比對。使用 SUMIFS 按月份、帳戶或交易對手加總兩側的金額，然後將這兩個欄位並排比較。
這能在您投入時間之前先定位出差異所在。如果十二個月中有十一個月的金額完全一致（精確到分），那麼您只需要核對一個月，而不是一整年。
這確實很有用，而且能及早止步。群組總額只能告訴您不一致之處在哪裡，卻無法告訴您是哪些資料列造成的，而且同一個群組內兩個相互抵消的錯誤會隱形。
方案 2：建立比對鍵並進行查閱
將識別事件的欄位串接成一個鍵值，通常是日期加上金額，再加上整理過的參照編號。然後雙向使用 XLOOKUP，找出存在於一側但在另一側缺失的資料列。
針對同一個鍵值加上 COUNTIFS 以找出重複項，因為查閱只會傳回第一個相符項，並默默忽略第二個。在將金額納入鍵值之前，先用 ROUND 將其四捨五入到小數點後兩位，以消除上述的浮點數不匹配問題。
這是最常用的主力方法，能處理大多數月份。但它的限制在於結構：它需要一個在兩側代表相同意義的鍵值。一旦參照格式不同，或者手續費被拆分到兩列中，這種方法就會立即失效。
方案 3：有針對性地處理四種差異類型
與其進行一次大範圍的比對，不如執行四個更精細的檢查。缺失的資料列來自雙向查閱。重複項來自對鍵值的計數。金額不一致來自僅針對參照編號進行比對，然後比較數值。時間差則來自在日期區間內進行比對，而非精確日期。
如果操作得當，這是最嚴謹的方法，因為每個未比對成功的資料列最終都會歸入明確的類別，而不是堆在未分類的垃圾堆中。
這也是最費工的方法。四次檢查意味著每側需要四個輔助欄，而且只要任一匯出檔案的欄位順序發生變化，整個架構就必須重建。我們的 清理與去重 Excel 資料 指南介紹了此方法所依賴的準備步驟。
共同的瓶頸。 這三種方法都假設一側的一列對應到另一側的一列。但單次結算可能包含四十筆交易。一筆付款可能由一筆費用、一筆手續費和一筆退款組成。在這兩種情況下，鍵值比對都無能為力。這就是一整個下午時間流失的地方。
如何使用 Powerdrill Bloom 核對交易
步驟 1：上傳兩個檔案
同時上傳帳簿匯出資料和對手方對帳單。Powerdrill Bloom 會對兩者進行分析，因此在開始任何比對之前，不匹配的欄位名稱、不同的日期格式和不一致的參照樣式都一目了然。
步驟 2：用自然語言描述對帳需求
直接指明這四個類別。要求找出存在於一個檔案中但在另一個檔案中缺失的資料列，以及重複的參照編號。接著，要求找出超出指定容差的金額不一致項，以及日期相差幾天的分錄。
然後提出解決棘手問題的關鍵：詢問一側的哪些資料列群組加總後等於另一側的單一資料列。這就是鍵值比對無法處理的多對一情況。
步驟 3：匯出圖表、報告或簡報
匯出異常清單、按類別分類的未比對金額摘要，或用於結帳檔案的簡短書面說明。
為什麼這比每個月重建比對架構更好
| 手動方式 | Powerdrill Bloom | |
|---|---|---|
| 參照格式不同 | 先手動清理兩側資料 | 描述差異並直接詢問 |
| 多列對應一列 | 手動分組 | 詢問哪些資料列加總等於對應項 |
| 重複項 | 每側需額外增加計數欄 | 已包含在異常清單中 |
| 下個月 | 重建每個輔助欄 | 直接上傳新的匯出檔案 |
第一列（參照格式不同）是耗費最多時間的地方。清理參照編號以使兩個系統一致是準備工作，其本身不產生任何實質成果。而且每當匯出格式發生變化時，都必須重新做一次。
常見錯誤
將相符的總額視為已完成對帳。 兩個大小相同、方向相反的錯誤會產生完美的總額。請檢查資料列數和未比對成功的金額，而不僅僅是加總。
僅根據金額進行比對。 在任何真實的帳簿中，多筆交易可能具有相同的數值。僅對金額進行查閱會將錯誤的交易配對在一起，而且表面上看起來毫無破綻。
忽略捨入差異。 顯示完全相同的數值仍可能無法通過相等測試。在比較之前，請將兩側四捨五入到相同的精度。
忘記檢查的方向。 單向查閱只能找出第二個檔案中缺失的資料列，而永遠找不到第一個檔案中缺失的資料列。每次都請進行雙向檢查。
邊做邊刪除已比對的資料列。 這看似高效，卻會破壞稽核軌跡。請改用狀態欄來標記資料列，並保持原始資料完整。
在期間結帳前進行對帳。 延遲入帳的分錄會產生時間差，這些時間差會自行解決。在期間中期追查這些差異只是白費工夫。
結論
對帳是一個分類問題，而不是比對問題。將每個未比對成功的資料列歸類為缺失、重複、金額錯誤或期間錯誤，剩下要處理的工作就會變得很少且易於解釋。
讓對帳成本高昂的原因在於每個月都要重建架構，特別是在參照不一致或一筆付款對應到多列資料時。如果這正是您結帳時的痛點，請嘗試在兩個匯出檔案上使用 Powerdrill Bloom。另請參閱我們的 如何在不使用 VLOOKUP 的情況下合併兩個 Excel 檔案 以及 建立預算與實際支出對照報告 指南。費用報告 和 現金流分析 頁面則涵蓋了相關的工作流程。
常見問題
什麼是交易核對（對帳）？
它是指確認同一活動的兩份記錄一致，並解釋所有仍存在的差異。這些解釋分為四類：缺失資料列、重複項、金額不一致和時間差。
Excel 可以自動核對兩個清單嗎？
無法單獨完成。Excel 提供了基本元件（主要是查閱、計數和條件加總），但您必須自己建立比對邏輯，並在任一匯出檔案發生變化時重新建構。
為什麼兩個看起來相同的金額會比對失敗？
通常是因為儲存精度。顯示為兩位小數的數值背後可能帶有更多小數位，因此精確比較會失敗。將兩側四捨五入到相同的精度即可解決此問題。
如何處理顯示為多個資料列的一筆付款？
將較小的資料列進行分組，並將群組總額與單一對應項進行比較。列級別的鍵值比對無法處理這種情況，這也是手動工作最常見的來源。
應該刪除未比對成功的資料列嗎？
不應該。請保留它們，並新增一個狀態欄來記錄類別和原因。刪除會破壞稽核軌跡，而稽核軌跡是日後證明對帳正確性的關鍵。