Excel如何sumif自動加總應收帳款




最近讀者來信,有資料要彙總成管理報表,已經整理成樞紐分析表,但是項目多,手動一個一個帶數字進去,仍然很花時間,看能不能一勞永逸,直接拉公式。經過一番心思,我想出來的方案不是很完美,可是稍微多一點「佈局」,還真的有函數可以套用,這個案例裡面有些值得參考的地方,以下分享:

一、如圖所示,這是原始數據,當然,實際情況可能有幾百幾千筆,文章範例都是極簡化的。

出貨明細表

二、數據想整理成如圖所示的彙總報表,應該還蠻清楚的,其中有個「出貨日」欄位要特別說明,同樣一組地區客戶,可能有多次不同日期的出貨,在統計時,只標出最早的出貨日期即可。另外,這個案例中,每個客戶在同一地區,只會有一種幣別的出貨,不會有兩種幣別同時出貨的情形。

同樣一組地區客戶,可能有多次不同日期的出貨

三、不囉嗦,上樞紐,依照圖片所示拉曳欄位。

依照圖片所示拉曳欄位

四、跑出來的樞紐,幾乎就是理想中的報表,格式上,難免還是要畫龍點睛地修飾,內容上,卡到一個最早出貨日,所圖片中標黃色的部份,其實只要合計數,不需要各出貨日的明細了。

其實只要合計數,不需要各出貨日的明細了

五、將樞紐上有用的數字,帶到設計好的表格裡,便是可以交差的管理報表。

將樞紐上有用的數字,帶到設計好的表格裡

六、雖然跑樞紐很容易,套數字卻很手工,一筆一筆帶感覺不怎麼SMART,來搞個函數吧。首先,要解決最早出貨日的困擾,選取所有資料,將「出貨日」依照「最舊到最新」排序。

將「出貨日」依照「最舊到最新」排序

七、希望把原來的出貨日,以地區客戶作劃分,變更成是各組地區客戶相對應的最早出貨日。剛好函數VLOOKUP會帶出匹配相符的頭一筆資料,利用這個特性,可以達到想要的效果。首先,把地區和客戶連在一起:「=A2&B2」,然後每筆資料以地區客戶作為條件,查找出明細表中相對應的第一筆出貨日,因為出貨日已經事先排序過了,帶出來恰恰就會是最早出貨日,函數公式為:「=VLOOKUP(F2,IF({1,0},$F$2:$F$8,$C$2:$C$8),2,)」。仔細把公式拆解,想想EXCEL會怎麼運行這個公式,應該還容易理解的,其中有個「IF({1,0}」,是因為搜尋值在搜尋對象的右邊,所以必須先倒置,這個涉及到陣列的概念,看起來有點奇怪,但其實作用很簡單,左右對調就是了,有機會我要來寫篇文章專門介紹。後面多加一欄「=A2&B2&D2」,把地區客戶幣別都併在一起,是為了加總金額用的,需要分幣別計算,所以併入參數中,下一步驟就可以瞭解其作用。

=VLOOKUP(F2,IF({1,0},$F$2:$F$8,$C$2:$C$8),2,)

八、自動填寫出貨日的公式:「=IFERROR(VLOOKUP(CONCATENATE(J2,K2),$F$2:$G$8,2,0),””)」,其中「CONCATENATE(J2,K2)」是組合函數,將兩個字串併在一起,作用等同於「J2&K2」,最外面套個「IFERROR(,””)」函數,是如果有地區客戶是無此資料的,就帶出空白,避免顯示難看的「#N/A」。自動填寫金額的公式:「=SUMIF($H$2:$H$8,CONCATENATE($J2,$K2,M$1),$E$2:$E$8)」,意思是在資料表格的地區客戶幣別欄位(「$H$2:$H$8」),如果有管理報表裡的項目(CONCATENATE($J2,$K2,M$1)),就將金額納入加總計算($E$2:$E$8),冠個「$」作用是固定住列或欄,以便可以直接將公式往右或往下拉。

=SUMIF($H$2:$H$8,CONCATENATE($J2,$K2,M$1),$E$2:$E$8)






當前文章延伸閱讀:
SUMIF