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

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

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

一、Excel範例資料

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

簡單的傳票明細分類帳

二、應收帳款明細表

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

簡單的應收帳款明細表

三、Vlookup函數

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

VLOOKUP(B7,帳款!$D$2:$E$7,2,0)-G7

四、建立樞紐分析表

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

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

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

 

五、Excel對帳技巧

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

IF(LEFT(C6,2)="SB",-E6,E6)

六、樞紐分析表更新

先前已經跑過樞紐了,而我們的明細資料有變動,所以必須更新樞紐,把游標移到那個樞紐分析表上,選擇上方的功:「樞紐分析表工具」、「變更資料來源」。

樞紐分析表資料如何快速更新,請參考贊贊小屋相關文章

變更資料來源

七、樞紐分析表範圍

在跳出來的視窗,顯示目前資料來源是:「帳款!$B$1:$E$7」,直接在「表格/範圍」的輸入列中,將E改成F即可:「帳款!$B$1:$F$7」。

帳款!$B$1:$F$7

八、設置欄位清單

回到「樞紐分析表欄位清單」,可以看到多了一個「淨額」欄位,把「金額」取消勾選,把「淨額」打勾。

樞紐分析表版面配置如何設定,請參考贊贊小屋相關文章

樞紐分析表欄位清單

九、Excel對帳範例

如此,相當精準地核對出有差異的帳款傳票,文章所使用的範例,只有幾筆,弄了這麼多函數公式,似乎多餘,可是依照我工作經驗,實務上像這樣的報表都有幾十幾百筆以上,真的遇到必須核對的場合,這個方法提供參考。

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

 

事務所對帳實務經驗

贊贊小屋文章都是實務經驗出發,以本篇文章為例,筆者在會計師事務所工作期間,曾經查核上市公司核對關係人交易,由於是重點查核事項,必須清帳整帳確認沒有問題,搞了一個晚上,就是從深夜到隔天清晨,由此可見善用Excel工具是會計人必備基本功。zanzan.tw © 2022

加強學習:贊贊小屋樞紐分析表文章合輯