Excel應付帳款帳齡表:樞紐分析表、TODAY、SUMIF函數

Excel建立樞紐分析表彙總資料之後,再設計各種函數公式,可以因應工作需求編製各種管理報表。本文以廠商應付帳款帳齡表為例,介紹TODAY和SUMIF函數的應用。

一、樞紐分析報表

以應付帳款明細整理出來的的樞紐分析表。

二、取消小計

管理目的不同,報表格式也應修改。將游標移到廠商合計處,滑鼠右鍵,將「小計”供貨單位”」取消勾選。

三、報表版面配置

接著游標留在樞紐裡面,上方會多出一塊「樞紐分析表工具」頁籤,移到「設計」、「版面配置」,拉下「報表版面配置」選單,點一下「重覆所有項目標籤」。

四、TODAY函數

計算應付帳款帳齡,亦即計算相差天數:「=TODAY()-B3」,TODAY函數可以抓取系統的當日,和開票日期相減,便是帳齡。

五、SUMIF函數

下一步是把未付總額列出來:「=SUMIF($A$2:$A$8,A3,$F$2:$F$8)」,SUMIF這個函數用在條件式求和,在A2到A8範圍中,所有等於A3的行列,加總F2到F8中相對應的的數值,前後兩個範圍都掛個「$」,往下拉公式時列數不會跟著變動不變,A3沒有掛「$」,所以拉公式時會跟著跳:A4、A5、A6依序變動。

六、帳齡計算

加權帳齡的計算:「=F3/H3*G3」。以會計語言來說,就是以各項帳款佔該廠商總未付金額的比例,加權計算出該項帳款的加權帳齡。

七、廠商帳齡合計

參考第五步驟輸入類似架構的SUMIF函數公式,計算出各廠商加權帳齡的合計:「=SUMIF($A$3:$A$8,A3,$I$3:$I$8)」。

因應管理需求設計函數公式

這一節文章裡的加權帳齡,是將把每個廠商的各筆帳款帳齡,以帳款金額佔廠商總金額的比例作為權數衡量,如此方便瞭解某個廠商帳齡情況是否有惡化或變好的趨勢,也有助比較不同廠商間的信用狀況。每項財務指標皆有其用意及適用情況,如同其它財務指標,這裡的加權帳齡並非十全十美。我們用Excel計算出財務指標,如果要進一步分析數據,必須很清楚所設計的公式,有需要再好把原始明細也調出來,綜合考量評估。
本文內容取自《會計人的Excel VBA小教室》,目前正改版中,敬請期待!
博客來網路書店網址:

贊贊小屋VBA程式設計線上課程:

相關文章