Excel兩個報表如何以vlookup交叉核對

從事會計工作,常常有需要就兩個報表之間,核對金額是否一致。首先總金額要一樣,總金額如果不同,必須找出到底是哪幾筆有差異,才能進一步瞭解差異原因,看要怎麼修正。例如先前曾經寫過文章分享的,企業有財帳稅帳兩套帳,兩套帳之間核對明細分類帳的差異,又例如企業已導入ERP,依照流程總帳傳票都是由子系統拋轉,子系統跟總帳應該會是一致,但是,沒有確實核對過,難保不會出差錯,做會計的就是要細心又有耐心,魔鬼出在細節裡,要確認帳務品質,應該多設計一些勾稽檢查的流程,如果企業的ERP運作成熟了,其中有一道會計結帳工序,一定是子系統和總帳之間的核對,以下分享:

一、簡簡單單的收入傳票明細帳。

簡簡單單的收入傳票明細帳

二、也是簡簡單單的應收帳款明細,由應收系統裡一筆一筆的應收憑單組成,正常收入傳票皆由應收憑單拋轉到總帳,所以每個帳款編號都有相對應的傳票編號。

簡簡單單的應收帳款明細

三、兩個報表間的連結為傳票編號,所以首先第一步,依據總帳明細裡的傳票,查找在應收系統面是否有相同的傳票編號,如果找不到,代表總帳有子系統沒有,這筆總帳有可能是總帳直接輸入的,這裡使用會計人最常用的查找函數公式:「=VLOOKUP(B2,帳款!$D$2:$D$7,1,0)」。

=VLOOKUP(B2,帳款!$D$2:$D$7,1,0)

四、然後在應收帳款明細,同樣公式可以找出子系統有、總帳系統沒有的傳票:「=VLOOKUP(D7,傳票!$B$2:$B$7,1,0)」,通常這種情形表示有應收帳款沒有拋轉到總帳,也就是遺漏了立帳。

=VLOOKUP(D7,傳票!$B$2:$B$7,1,0)

五、通常這種類似的VLOOKUP公式,只要稍加潤飾,結果顯示就會更加人性化,例如以上個步驟的例子而言:「=IFERROR(VLOOKUP(D7,傳票!$B$2:$B$7,1,0),”此帳款未拋傳票”)」,意思是如果找不到(IFERROR),不要顯示Excel語言(#N/A),改成淺顯易懂的「此帳款未拋傳票」。

=IFERROR(VLOOKUP(D7,傳票!$B$2:$B$7,1,0),"此帳款未拋傳票")

六、先前查找的方式有個盲點,只交叉核對了兩邊傳票的有無情形,對於傳票的金額,卻沒有核對,因為有可能,同樣一個傳票編號,在子系統是一個金額,在總帳卻是另一個金額,所以我們的函數公式必須進化:「=VLOOKUP(D2,傳票!$B$2:$E$7,4,0)-E2」,

=VLOOKUP(D2,傳票!$B$2:$E$7,4,0)-E2

七、上個步驟還有個問題,如果是折讓的應收帳款(單別「SB」),因為是負數應收,傳票會做在貸方,依照這個範例,如果希望一個公式可以拉到底,應該稍加修改:「=IF(LEFT(C6,2)=”SB”,VLOOKUP(D6,傳票!B6:F11,5,0),VLOOKUP(D6,傳票!$B$2:$E$7,4,0))-E6」意思前面加個條件式,如果是折讓應收帳款,要核對的是傳票貸方而非借方。

=IF(LEFT(C6,2)="SB",VLOOKUP(D6,傳票!B6:F11,5,0),VLOOKUP(D6,傳票!$B$2:$E$7,4,0))-E6

Excel整理術 - 入門密技+進階實作 台北場淡江大學台北校區
2017-12-23(星期六) 09:00~16:00

http://smart.businessweekly.com.tw/ecourse/product.aspx?PROD_TYPE_NO=PROD000008934

《會計人的Excel小教室》:每位會計人都應該具備的武功秘笈!

http://www.books.com.tw/products/0010734874

《會計人的Excel VBA小教室》:會計人Excel強化並且VBA入門!

http://www.books.com.tw/products/0010753074