Excel核對資料差異很常使用Iferror Vlookup的搭配用法,本文以應收帳款傳票明細帳為例,介紹這兩個函數的經典用法,另外補充IF、CONCATENATE、ISNA等多欄位合併的實務案例。
目錄
Toggle在《會計人的Excel小教室》:3.1「兩套帳本傳票核對」有介紹如何應用Vlookup函數核對傳票,當時所設想的範例單純,一筆傳票對應一筆金額,所以是用傳票號VLOOKUP金額兩相比對,實務上所遇到情況,通常會較為複雜。舉例而言,可能兩套帳本傳票金額都一致,但是有借貸方相反的情形;可能一筆傳票兩項分錄,其中一項沒有問題,但是另一項有差異;也有可能同樣一張傳票,這套帳本有兩項分錄,另一套帳本卻有三項分錄,凡此種種,如果想用Excel公式一次查找出差異,必須再進一步考量設計,以下介紹具體作法:
一、A帳應收傳票
A帳中的應收帳款明細分類帳。
二、B帳應收傳票
B帳中的應收帳款明細分類帳。標黃色部份是與A帳有差異的傳票分錄,在此想設計Excel公式,自動查找出差異項目。
三、會思考的IF函數
考量借貸方金額應該有所區別,利用IF判斷函數:「=IF(D7=”借方”,E7,-E7)」,借方為正、貸方為負,如此符合會計一般慣例。
四、會偵查的VLOOKUP函數
A帳中新增核對欄位,直接以傳票號VLOOKUP帶出B帳金額:「=VLOOKUP(A9,B帳!$A$3:$F$10,6,0)」,「#N/A」表示B帳無此傳票。
五、處理錯誤的Iferror Vlookup
公式稍加修飾:「=G4-IFERROR(VLOOKUP(B4,B帳!$A$3:$F$10,6,0),0)」。如此一來,資料查找不到,不會出現無法加總的「#N/A」,可以直接顯示兩相比較的差額,並且只要公式結果並非為零,表示有問題,相當一目瞭然。
標紅色傳票分錄,兩套帳本一致,但還是顯示差額。這是因為vlookup函數特性,它是在範圍內找到的第一筆馬上回傳,所以永遠只會傳回條件相符的第一筆資料。也就是稅帳傳票1407001的第一筆貸方金額-5,000,因此A帳減掉B帳的計算結果是9,000(4,000-(-5,000))。
六、把文字串起來的CONCATENATE函數
為了突破函數本身限制,有必要將欄位合併,簡單方法為「=A3&D3&E3」,直接將「傳票編號」、「借貸」、「金額」予以合併,或者利用相關函數:「=CONCATENATE(A3,D3,E3)」,兩者結果相同。
七、是不是NA的ISNA函數
所有關鍵欄位合併之後,再次輸入查找公式:「=IF(ISNA(VLOOKUP(G3,B帳!$G$3:$G$10,1,0)),”A帳B帳一致”,”B帳無”)」。這裡新增了ISNA函數,如果vlookup有結果,無論傳回的值為何,對於ISNA而言是「TRUE」,如果查找不到,那就是「FALSE」,搭配熟悉的IF基本函數,表示vlookup有找到便顯示「A帳B帳一致」,沒找到便顯示「B帳無」,使得公式計算結果更易於理解。
沒有底限的欄位合併查找
將欄位合併,如果瘋狂一點,把所有欄位都合併,可以準確核對出兩套傳票間有無差異。但這麼做,首先不符合會計以金額為主的核對原則;再者,以這一節的範例來看,B帳傳票1408001有三筆一模一樣的分錄,A帳傳票1408001只有兩筆,像這種重複錯誤的情況,單純查找公式沒辦法發現,即使組合Iferror Vlookup也是無濟於事。較為完整並且合乎會計思惟的作法,是將兩套帳本依照傳票號碼,彙總成樞紐分析表,然後以加總後金額比對兩者金額差異。從這裡可以體會到,設計Excel公式,瞭解資料特性和需求是最重要的第一步,接下來,可能每個人熟悉的作法不同,寫的公式也不一樣, 但是多思考、多運用、多學點招式,Excel經驗值總是會逐漸累積上去的。
參考資源:
1.微軟IFERROR函數說明。
2.贊贊小屋Excel教學文章。