贊贊小屋

Excel如何將欄位合併進行vlookup比對

先前寫過一篇文章:《Excel如何vlookup兩套帳本傳票核對》,當時所設想範例單純,一筆傳票對應一筆金額,所以是用傳票號vlookup金額兩相比對,實務上所遇到情況,通常會較為複雜。舉例而言,可能兩套帳本傳票金額都一致,但是有借貸方相反的情形;可能一筆傳票兩項分錄,其中一項沒有問題,但是另一項有差異;也有可能同樣一張傳票,這套帳本有兩項分錄,另一套帳本卻有三項分錄,凡此種種,如果想用Excel公式一次查找出差異,必須再進一步考量設計,以下分享作法:

一、A帳中的應收帳款明細分類帳。

A帳中的應收帳款明細分類帳

二、B帳中的應收帳款明細分類帳。標黃色部份是有A帳有差異的傳票分錄,在此想設計Excel公式,自動查找出差異項目。

標黃色部份是有A帳有差異的傳票分錄

三、考量借貸方金額應該有所區別,利用IF判斷函數:「=IF(D7=”借方”,E7,-E7)」,借方為正、貸方為負,如此符合會計一般慣例。

=IF(D7="借方",E7,-E7)

四、A帳中新增核對欄位,直接以傳票號vlookup帶出B帳金額:「=VLOOKUP(A9,B帳1!$A$3:$F$10,6,0)」,「#N/A」表示B帳無此傳票。

=VLOOKUP(A9,B帳1!$A$3:$F$10,6,0)

五、公式稍加修飾:「=G4-IFERROR(VLOOKUP(B4,B帳1!$A$3:$F$10,6,0),0)」。如此一來,資料查找不到,不會出現無法加總的「#N/A」,可以直接顯示兩相比較的差額,並且只要公式結果並非為零,表示有問題,相當一目瞭然。

=G4-IFERROR(VLOOKUP(B4,B帳1!$A$3:$F$10,6,0),0)

標紅色傳票分錄,兩套帳本一致,但還是顯示差額。這是因為vlookup函數特性,它是在範圍內找到的第一筆馬上回傳,所以永遠只會傳回條件相符的第一筆資料。也就是稅帳傳票1407001的第一筆貸方金額-5,000,因此A帳減掉B帳的計算結果是9,000(4,000-(-5,000))。

六、為了突破函數本身限制,有必要將欄位合併,簡單方法為「=A3&D3&E3」,直接將「傳票編號」、「借貸」、「金額」予以合併,或者利用相關函數:「=CONCATENATE(A3,D3,E3)」,兩者結果相同。

=A3&D3&E3

七、所有關鍵欄位合併之後,再次輸入查找公式:「=IFERROR(VLOOKUP(G3,B帳!$G$3:$G$10,1,0),”B帳無”)」。這裡利用了IFERROR的特性,如果查找不到,傳回「B帳無」,使得公式計算結果更易於理解。

=IFERROR(VLOOKUP(G3,B帳!$G$3:$G$10,1,0),"B帳無")

將欄位合併,如果瘋狂一點,把所有欄位都合併,可以準確核對出兩套傳票間的有無差異。但這麼做,首先不符合會計以金額為主的核對原則;再者,以這篇文章的範例來看,B帳傳票1408001有三筆一模一樣的分錄,A帳傳票1408001只有兩筆,像這種重複錯誤的情況,單純vlookup查找函數沒辦法發現。較為完整並且合乎會計思惟的作法,是將兩套帳本依照傳票號碼,彙總成樞紐分析表,然後vlookup比對兩者的金額差異。從這裡可以體會到,設計Excel公式,瞭解資料特性和需求是最重要的第一步。

本網站所有內容皆受版權保護,网站备案编号:苏ICP备14051307号-1