Excel出貨單價檢查(上):VLOOKUP、篩選、樞紐分析表

目錄

Excel報表分析首先是清理資料,不必要部份排除。本文以出貨明細表為例,介紹先VLOOKUP函數帶入業務員,篩選掉金額為零和關係人客戶,最後建立樞紐分析表。

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

一、出貨明細表

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

二、報表欠缺欄位

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

三、VLOOKUP函數

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

四、篩選金額為零

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

五、篩選關係人客戶

上一步驟是篩選只勾金額為零,比照同樣方式,我們也可以除了零以外,其他都保持勾選,意思是只取非零出貨,接著在這個基礎上,再篩選客戶將關係人去掉。(篩選操作,可參考《會計人的Excel小教室增訂版》:3.4 「報表金額為零的隱藏」)

六、可見儲存格定位

雙重篩選之後,再利用可見儲存格的特殊定位,得到乾淨的非零非關係人出貨明細,這是單價檢查的範圍。(特殊定位操作,可參考《會計人的Excel小教室增訂版》:3.5 「報表金額為零的刪除」)

七、建立樞紐分析表

跑樞紐,如圖所示安排欄位配置,輸入公式:「=IF(OR(C8=””,C7=””),0,E8-E7)」﹐只要計算結果不是零,表示同一料號同一客戶,竟然有出貨單的單價不同,這便是單價異常,應該進一步追踪業務瞭解情形。(平均售價操作,可參考《會計人的Excel小教室增訂版》:5.4 「樞紐分析表計算欄位」)

運用Excel如同學習太極劍法

這一節文章綜合應用Excel的篩選、可見儲存格複製、樞紐分析表三個技巧,因為《會計人的Excel小教室》已經細節講解這些技巧,於此不再贅述,有興趣可翻閱參考。這個例子告訴我們,運用Excel如同學習太極劍法,上乘境界是把所有招式(範例)忘光光,隨機應變,存乎一心!另外,這裡跑完樞紐設定好公式之後,以目視方式將異常項目標記黃色,實務上可能即使樞紐彙總,筆數還是相當多,難以手工目視檢查,下一節再介紹較有效率的Excel方法。

相關文章