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

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

網誌圖文版：

http://www.b88104069.com/archives/4089

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

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

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

三、考量借貸方金額應該有所區別，利用IF判斷函數：「＝IF（D7＝"借方
",E7,-E7）」，借方為正、貸方為負，如此符合會計一般慣例。

四、A帳中新增核對欄位，直接以傳票號vlookup帶出B帳金額：「＝VLOOKUP
（A9,B帳1!＄A＄3：＄F＄10,6,0）」，「＃N／A」表示B帳無此傳票。

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

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

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

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

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


延伸閱讀（vlookup核對查找）：

Excel如何vlookup兩套帳本傳票核對：

http://www.b88104069.com/archives/1706

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

http://www.b88104069.com/archives/4006

Excel如何資料剖析後vlookup查找：

http://www.b88104069.com/archives/1092

--


周末，我們繼續Excel：精華區=>21.心得=>5.其他=>3.office

會計人的Excel小教室： https://www.facebook.com/acctexcel


--