Excel如何以樞紐分析表交叉核對報表 - 會計

Freda avatar
By Freda
at 2016-03-21T08:44

Table of Contents


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


--
Tags: 會計

All Comments

Connor avatar
By Connor
at 2016-03-24T03:23
謝謝分享喔
Cara avatar
By Cara
at 2016-03-25T11:03
push

Re: 以部分科目之資格報考105年cpa考試者,請注意期

Isabella avatar
By Isabella
at 2016-03-20T19:53
推 bakona: 請問如果是應屆第一次報考,就沒辦法只報考部分科目而是要 03/19 15:55 → bakona: 報名全部考科的意思嗎~??謝謝!! 03/19 15:55 其實內文是說 專門職業及技術人員高等考試會計師考試規則 第 六 條   ...

105 金永勝審計

Sarah avatar
By Sarah
at 2016-03-20T12:44
如題 希望有含課本講義 意者站內信報價 謝謝~ - ...

AICPA 通過證書/執照 申請流程分享

Faithe avatar
By Faithe
at 2016-03-20T10:29
網誌版 http://goo.gl/Zo2BKH 費盡千辛萬苦,考過了美國會計師,就要開始漫長的申請證書/執照的過程。由於我當初 報名的是關島會計師,也打算在關島申請證書及執照,若想要轉州則需另外申請。通過考 試就能申請證書(Certificate),而若需要申請執照(License),則須符合工作經驗條件 ...

成管會一題一問

Mason avatar
By Mason
at 2016-03-19T21:17
http://i.imgur.com/jxD47PI.jpg 這題我是看著答案寫出來過程的 可是我完全不清楚為何會這樣QQ 我甚至也不確定我寫的對不對.... 還請各位指教一下andgt;andlt; - ...

標題 政府會計範圍

Frederica avatar
By Frederica
at 2016-03-19T21:07
大家好 請問一下 今年的高考會計-政府會計 範圍 政府會計準則公報 是適用---現在主計網站上的--104.1.5修正發布的版本嗎? 感謝各位大大 - ...