推 Qpon: 謝謝分享喔 03/21 10:41
推 popomilk: push 03/21 22:16
Excel如何以樞紐分析表交叉核對報表
網誌圖文版：
http://www.b88104069.com/archives/4019
先前寫過文章分享，如何以vlookup交叉核對兩份報表間的差異情形，當時範
例很簡單，每一筆資料的傳票帳款都不一樣，vlookup函數在查找時，會從上
到下傳回找到的第一筆資料，剛好適用於範例。然而，會計人在工作所遇到
的報表，一張傳票通常有好幾筆分錄，一筆帳款通常有好幾批出貨，甚至也
有可能，幾筆帳款一起拋在同一張傳票，凡此種種，都會使得直接套用
vlookup不切實際，遇到這種情形，我的建議是先跑樞紐分析表作個彙總，第
二步才來交叉核對，以下分享：
一、簡單的傳票明細分類帳，如同一般實務狀況，一張傳票有幾筆分錄，摘
要的部份是簡化了，正常銷貨收入的傳票，還會有諸如客戶、出貨單號數量
等資訊。
二、簡單的應收帳款明細表，每筆帳款編號都不一樣，不過如圖所示，應該
是系統作業方便，有些帳款拋轉成同一張傳票，也有些帳款尚未拋轉傳票。
三、傳票分借貸方兩欄，這對於Excel在整理上很不方便，我習慣套個簡單的
加減：「=E7-F7」，借方為正，貸方為負，也就是例圖上的G欄。然後延續先
前文章所分享方法：「=VLOOKUP(B7,帳款!$D$2:$E$7,2,0)-G7」，可式很快
便發現此路不通，因為兩筆帳款拋轉成一張傳票兩筆分錄，vlookup由上往下
，只要找到第一筆合乎條件的，便會打住，如圖所示，傳票的第一筆分錄（
帳款）核對相符，第二筆分錄（帳款）顯示有差異，但其實我們都知道，其
實是一致的。
四、為了解決一張傳票兩筆帳款的問題，有必要弄個樞紐分析表，依照傳票
或是帳款彙總金額。（關於樞杻分析表，可以參考贊贊小屋《樞紐分析表》
專區喔！）
五、運用Excel的原理相通，可是依照實際狀況的不同，必須有所因應。例如
這裡的帳款明細表，可以發現有銷退負數的問題，就算是已經樞紐彙總了，
如果以vlookup核對，原本已經是一致的資料，還是會顯示有差異，所以我們
必須動些小手腳，像是新增一欄、弄個簡單公式：「
=IF(LEFT(C6,2)="SB",-E6,E6)」，如此一來，類似於將傳票借貸方淨額表達
，我們也將帳款明細表淨額表達了，這是會計人在整理Excel資料時，相當實
用的小技巧。
六、先前已經跑過樞紐了，而我們的明細資料有變動，所以必須更新樞紐，
把游標移到那個樞紐分析表上，選擇上方的功：「樞紐分析表工具」、「變
更資料來源」。
七、在跳出來的視窗，顯示目前資料來源是：「帳款!$B$1:$E$7」，直接在
「表格／範圍」的輸入列中，將E改成F即可：「帳款!$B$1:$F$7」。
八、回到「樞紐分析表欄位清單」，可以看到多了一個「淨額」欄位，把「
金額」取消勾選，把「淨額」打勾。
九、如此，相當精準地核對出有差異的帳款傳票，文章所使用的範例，只有
幾筆，弄了這麼多函數公式，似乎多餘，可是依照我工作經驗，實務上像這
樣的報表都有幾十幾百筆以上，真的遇到必須核對的場合，相信大家會覺得
我這方法值得參考！
延伸閱讀：
贊贊小屋《樞紐分析表》專區：
http://www.b88104069.com/archives/category/salary/excel/pivottable
--
周末，我們繼續Excel：精華區=>21.心得=>5.其他=>3.office
會計人的Excel小教室： https://www.facebook.com/acctexcel
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 58.114.119.147
※ 文章網址: https://www.ptt.cc/bbs/Accounting/M.1458521062.A.ECB.html