Excel如何sumif帳齡加權合計




上次寫過一篇文章,開頭是講帳齡,結果只寫到了怎麼架樞紐表,當時留下個尾巴,這一次要把這個尾巴補起來,分享加權帳齡表的編製(讀者要求格式):

一、上次跑出來的樞紐分析表,我更改部份未付帳款金額,方便說明。

上次跑出來的樞紐分析表

二、那個廠商合計很礙眼,所以在「供貨單位」上右鍵,將「小計”供貨單位”」點掉。

將「小計"供貨單位"」點掉

三、點掉之後,樞紐比較清爽,本來沒必要的東西,別留在報表上。

樞紐比較清爽

四、樞紐有個習慣,相同的列標籤只會保留第一個,其餘空白,這個雖然美觀,但報表資料一多,跳個頁閱讀起來造成困難,況且也不利於資料運算,所以「=IF(B2=””,C1,B2)」,毫不猶豫將空白填滿。

「=IF(B2="",C1,B2)」

五、既然是帳齡,免不了要計算相差天數:「=TODAY()-D2」,「TODAY()」意思是抓取系統的當日,和開票日期相減,便是呼之欲出的帳齡!

「=TODAY()-D2」

六、下一步是把未付總額列出來:「=SUMIF($B$2:$B$7,B2,$F$2:$F$7)」,SUMIF這個函數用在條件式求和,在B2到B7範圍,那些等於B2的,加總F2到F7中相對應的的數值,前後兩個範圍都掛個「$」,往拉公式時範圍不變,B2沒掛「$」,拉公式時會跟著跳:B3、B4、B5等依序下去。

「=SUMIF($B$2:$B$7,B2,$F$2:$F$7)」

七、加權帳齡的計算:「=F2/G2*C2」,以會計人的語言來講,就是以各項帳款佔該廠商總未付金額的比例,加權計算出該項帳款的加權帳齡。這是依照讀者需要設的公式,於此僅作詮釋,不加意見,也不代表本贊贊小屋的立場喔。

「=F2/G2*C2」

八、SUMIF再弄個各廠商加權帳齡的合計:「=SUMIF($B$2:$B$7,B2,$H$2:$H$7)」。

「=SUMIF($B$2:$B$7,B2,$H$2:$H$7)」

九、我除了是函數狂人,還是樞紐金剛,像前面一個步驟的加權帳齡合計,一般我會反射神經跑樞紐,因為樞紐跑完,報表也好了。之所以特別用函數,也是應讀者要求啦﹐在這裡忍不住手癢還是秀一下樞紐。至於前面報表會有資料重覆的問題(加權帳齡合計那一欄),需要小費功夫,有時間再來分享幾種作法。

除了是函數狂人,還是樞紐金剛




當前文章延伸閱讀:
SUMIF