Vlookup及樞紐分析表對帳教學：Excel交叉核對資料範例

Excel比對資料是實務基本功，因為當資料有問題，報告再怎麼分析也是枉然。本文以應收帳款與會計傳票核對為例，介紹如何綜合應用Vlookup函數及樞紐分析表。

先前寫過文章分享，如何以vlookup交叉核對兩份報表間的差異情形，當時範例很簡單，每一筆資料的傳票帳款都不一樣，vlookup函數在查找時，會從上到下傳回找到的第一筆資料，剛好適用於範例。然而，會計人在工作所遇到的報表，一張傳票通常有好幾筆分錄，一筆帳款通常有好幾批出貨，甚至也有可能，幾筆帳款一起拋在同一張傳票，凡此種種，都會使得直接套用vlookup不切實際，遇到這種情形，我的建議是先跑樞紐分析表作個彙總，第二步才來交叉核對，以下分享：

一、Excel範例資料

簡單的傳票明細分類帳，如同一般實務狀況，一張傳票有幾筆分錄，摘要的部份是簡化了，正常銷貨收入的傳票，還會有諸如客戶、出貨單號數量等資訊。

二、應收帳款明細表

簡單的應收帳款明細表，每筆帳款編號都不一樣，不過如圖所示，應該是系統作業方便，有些帳款拋轉成同一張傳票，也有些帳款尚未拋轉傳票。

三、Vlookup函數

傳票分借貸方兩欄，這對於Excel在整理上很不方便，我習慣套個簡單的加減：「=E7-F7」，借方為正，貸方為負，也就是例圖上的G欄。然後延續先前文章所分享方法：「=VLOOKUP(B7,帳款!$D$2:$E$7,2,0)-G7」，可式很快便發現此路不通，因為兩筆帳款拋轉成一張傳票兩筆分錄，vlookup由上往下，只要找到第一筆合乎條件的，便會打住，如圖所示，傳票的第一筆分錄（帳款）核對相符，第二筆分錄（帳款）顯示有差異，但其實我們都知道，其實是一致的。

四、建立樞紐分析表

為了解決一張傳票兩筆帳款的問題，有必要弄個樞紐分析表，依照傳票或是帳款彙總金額。不過注意到黃色部份，加總金額為「6,000」，但原始資料是一筆是正常銷貨帳款（SA）「5,000」、一筆其實是負數的銷退折讓（SB）「1,000」，以傳票的角度來看，金額應該是5,000－1,000＝4,000。

關於如何建立樞杻分析表，可以參考贊贊小屋相關文章。

五、Excel對帳技巧

運用Excel的原理相通，可是依照實際狀況的不同，必須有所因應。例如這裡的帳款明細表，可以發現有銷退負數的問題，就算是已經樞紐彙總了，如果以vlookup核對，原本已經是一致的資料，還是會顯示有差異，所以我們必須動些小手腳，像是新增一欄、弄個簡單公式：「=IF(LEFT(C6,2)=”SB”,-E6,E6)」，如此一來，類似於將傳票借貸方淨額表達，我們也將帳款明細表淨額表達了，這是會計人在整理Excel資料時，相當實用的小技巧。