Excel以最早出貨日整理應收帳款,善用VLOOKUP及SUMIF函數

Excel函數可以幫忙整理資料編製報表,本文以地區客戶別的出貨明細表為例,介紹應用樞紐分析表及VLOOKUP、SUMIF等函數公式,完成以最早出貨日為基準的彙總表。

有個會計的實務案例,是依據出貨的應收帳款編製特定格式的管理報表,縱使已經先把明細彙總成樞紐分析表,可是要資料套進管理報表中,還是必須手工一個一個帶數字,項目多,耗費時間。於是利用VLOOKUP加上SUMIF函數,設計出一套組合公式,成功達到想要的效果,以下具體介紹:

一、簡化的應收帳款明細表

應收帳款明細表,實際情況可能有幾百幾千筆,文章範例都是極簡化的,然後這裡已經依照出貨日排序過了。

二、想要這樣的管理報表

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

三、第一個想到樞紐分析表

建立樞紐分析表,依照圖片所示拖曳欄位。

四、沒有穿衣服的原始報表

初始格式的報表,數字雖然都是對的,但佛要金裝,Excel需要美觀。

五、設計樞紐分析表樣式

「樞紐分析表工具」、「設計」、「樞紐分析表樣式」,選單一拉下,有許多Excel預設好的分析表樣式,選一個自己喜歡的,再加上框線,便是一張拿得出手的報表。

六、VLOOKUP查找與IF陣列組合

原來的出貨日是以各個地區各個客戶作劃分,現在希望是以「地區+客戶」的組別作為基準,剛好VLOOKUP函數會帶出匹配相符的頭一筆資料,利用這個特性,可以達到想要的效果。首先,把地區和客戶連在一起:「=A2&B2」(F欄),如此每筆資料可以「地區+客戶」作為條件,查找出明細表中相對應的第一筆出貨日,而且因為出貨日已經事先排序過了,帶出來恰恰就會是最早出貨日,函數公式為:「=VLOOKUP(F2,IF({1,0},$F$2:$F$8,$C$2:$C$8),2,)」(G欄)。仔細把公式拆解,想想EXCEL會怎麼運行這個公式,應該還容易理解的,其中有個「IF({1,0}」,是因為搜尋值在搜尋對象的右邊,所以必須先倒置,這個涉及到陣列的概念,暫不多作解釋,其作用便是左右對調,有需要的情況同樣照樣造句即可。後面H欄公式:「=A2&B2&D2」,把地區客戶幣別都併在一起,這是為了加總金額用的,需要以幣別作為區分計算,所以併入參數中,在下一個步驟會用到。

七、IFERROR判斷與SUMIF條件求和

黃色部份是出貨日的公式,和先前許多文章類似的VLOOKUP函數寫法:「=IFERROR(VLOOKUP(CONCATENATE(B2,C2),六!$F$2:$G$8,2,0),””)」,其中「CONCATENATE(B2,C2)」是組合函數,將兩個字串併在一起,作用等同於「B2&C2」,最外面套個IFERROR函數,是如果有地區客戶是無此資料的,就帶出空白,避免顯示「#N/A」。灰色部份是彙總金額的公式:「=SUMIF(六!$H$2:$H$8,CONCATENATE($B2,$C2,E$1),六!$E$2:$E$8)」,意思是在資料表格的地區客戶幣別欄位(「六!$H$2:$H$8」),如果有管理報表裡的項目 (「CONCATENATE($B2,$C2,E$1)」),就將金額納入加總計算(「($E$2:$E$8) 」),冠個「$」作用是固定住列或欄,以便可以直接將公式往右或往下拉。

精進Excel,因應各種管理需求編製報表

這一節文章的範例老實說不具有普遍性,也許有某間公司需要這樣的管理報表,但同時很多公司並不會特別編製像這樣的報表。財務報表和管理報表本質上便有如此的區別,諸如資產負債表、損益表等財務報表,每家公司都會有,是基本的報表,並且是法規要求的報表。管理報表則是每家公司基於自己管理需要,逐漸發展設計出來的,以應收帳款來說,有比較普遍的的帳齡分析表,也有像這篇文章較為獨特的報表。正因如此,會計人更應該精進Excel,方能將所學應用於自己工作上所遇到的獨特情況,如同這一節範例,運用各種小技巧,輕鬆準確地編製管理報表。

每天學習,每天充電:Excel函數文章合集

加入Line社群,口袋裡的Excel小教室!

最新文章: