Excel經常要核對兩份報表差異情形,本文以收入傳票及應收帳款明細帳為例,介紹利用VLOOKUP交叉驗證資料的正確性,搭配IFERROR增加可讀性,進而活用IF及LEFT函數。
目錄
Toggle從事會計工作,常常有需要就兩個報表之間,核對金額是否一致。首先總金額要一樣,總金額如果不同,必須找出到底是哪幾筆有差異,才能進一步瞭解差異原因,看要怎麼修正。例如上一節介紹的,企業有財帳稅帳兩套帳,兩套帳之間核對明細分類帳的差異,又例如企業已導入ERP,依照作業流程,總帳傳票都是由子系統拋轉,子系統跟總帳應該都會一致。可是,沒有確實核對過,難保不會出差錯,會計人特質便是細心又有耐心,魔鬼出在細節裡,要保證帳務品質,就要多設計一些勾稽檢查的流程。對於ERP運作成熟的企業,其中有一道會計檢查工序,一定是子系統和總帳之間的核對,以下介紹:
一、收入傳票明細帳
二、應收帳款對帳單
也是簡化的應收帳款明細。由應收系統裡一筆一筆的應收憑單組成,正常收入傳票皆由應收憑單拋轉到總帳,所以每個帳款編號,都有相對應的傳票編號。
三、Excel對帳勾稽
兩個報表間的連結是傳票編號,所以首先第一步,依據總帳明細裡的傳票,查找在應收系統是否有相同的傳票編號,如果找不到,代表總帳有子系統沒有,這筆總帳有可能是總帳直接輸入的。在這裡,使用會計人最常用的查找函數公式:「=VLOOKUP(B2,帳款!$C$2:$C$7,1,0)」。
四、Excel交叉比對
然後在應收帳款明細,同樣公式可以找出子系統有、總帳系統沒有的傳票:「=VLOOKUP(D7,傳票!$A$2:$A$7,1,0)」,通常這種情形表示有應收帳款沒有拋轉到總帳,也就是遺漏了立帳。
五、Excel對帳結果
通常這種類似的VLOOKUP公式,只要稍加潤飾,顯示的結果會具有可閱讀性,以上個步驟為例:「=IFERROR(VLOOKUP(D7,傳票!$A$2:$A$7,1,0),”此帳款未拋轉傳票”)」,意思是如果找不到(IFERROR函數),不要顯示Excel語言(#N/A),改成淺顯易懂的「此帳款未拋傳票」。
六、Excel比對差異
先前查找方式有個盲點,只交叉核對了兩邊傳票的有無情形,對於傳票的金額,卻沒有核對。因為也有可能,同樣一個傳票編號,在子系統是一個金額,在總帳卻是另一個金額,所以函數公式必須修改:「=VLOOKUP(D2,傳票!$A$2:$D$7,4,0)-E2」,如此一來,便可以發現傳票編號「JA-1602002」的金額,在子系統和總帳的金額是不一致的,必須再進一步追查原因。
七、核對資料調整
上個步驟還有個問題,如果是折讓的應收帳款(單別「SB」),因為是負數應收,傳票會做在貸方,依照這個範例,如果希望一套公式拉到底,還要再稍加修改:「=IF(LEFT(C6,2)=”SB”,VLOOKUP(D6,傳票!$A$2:$E$7,5,0),VLOOKUP(D6,傳票!$A$2:$D$7,4,0))-E6」在前面加個條件式,如果是折讓應收帳款(單別為SB),要核對的是傳票貸方而非借方。
Excel對帳與比對資料
這一節和上一節相同,都是在查找兩個報表之間的差異,不過這一節配合較為複雜的範例,多應用了IFERROR函數改變錯誤訊息的提示文字、由查找傳票編號改為更進一步的查找傳票金額、最後還加了一個判斷單別的條件式。凡此種種,都是為了公式偵錯功能更加完整,因為範例筆數少,一看便能抓出錯誤,實務工作上,可能有上百上千筆的資料,如果能因應實際狀況,設置好公式,可以大為提升效率並減少錯誤風險。
每天學習,每天充電:Excel函數文章合集。