Excel如何檢查出貨單價(上):樞紐彙總




Excel在會計人實務工作上,除了是編製報表的基本工具,還是檢查資料的一大利器。一家稍具規模的公司,每月進貨出貨可能有好幾千筆,這些拋轉產生的傳票,如果要一筆一筆細細檢查,不但事倍功半,更關鍵是根本不合實際,事實上做不到。針對像這樣的大數據,借助Excel種種小技巧,我們可以輕鬆而完整地檢查某個細項。以下,介紹如何利用Excel檢查出貨單價是否異常:

一、出貨明細表。黃色部份銷貨金額為零,是明擺著的異常,藍色部份是關係人交易,因為有移轉定價並且會合併沖銷,不在檢查範圍內。

出貨明細表

二、常常拿到報表欠缺會某些欄位,例如剛才的出貨明細表,沒有業務人員,不方便作追踪,所以有必要先取得一份業務所負責客戶清單。

有必要先取得一份業務所負責客戶清單

三、利用vlookup函數,在出貨明細表新增欄位,顯示每家客戶所對應的業務,輸入公式:「=VLOOKUP(A2,二!$A$1:$B$5,2,0)」。

=VLOOKUP(A2,二!$A$1:$B$5,2,0)

四、首先把銷貨金額為零的篩選出來,檢查單別是否為樣品類型,然後進一步和業務人員確認。

首先把銷貨金額為零的篩選出來

五、上一步驟是篩選只勾金額為零,比照同樣方式,我們也可以除了零以外,合部都保持勾選,意思是只取非零出貨,接著在這個基礎上,再篩選客戶將關係人去掉。

再篩選客戶將關係人去掉

六、雙重篩選之後,再利用可見儲存格的特殊定位,得到乾淨的非零非關係人出貨明細,這是單價檢查的範圍。

得到乾淨的非零非關係人出貨明細

七、跑樞紐,依照如圖所示安排欄位配置,輸入公式:「=IF(OR(C8=””,C7=””),0,E8-E7)」﹐只要計算結果不是零,表示同一料號同一客戶,竟然有出貨單的單價不同,這便是單價異常,應該進一步追踪業務瞭解情形。

=IF(OR(C8="",C7=""),0,E8-E7)

這篇文章綜合應用Excel的篩選、可見儲存格複製、樞紐分析表三個技巧,因為先前有很多文章有細節講解這些技巧,在這裡不再贅述,有興趣可查閱先前文章。另外,這篇文章跑完樞紐設定好公式之後,以目視方式將異常項目標記醒目的黃色,實務上可能即使樞紐彙總,筆數還是相當多,難以手工目視檢查,下篇文章再介紹較有效率的Excel方法。






當前文章延伸閱讀:
經典函數