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

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

網誌圖文版：

http://www.b88104069.com/archives/3735

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

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

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

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

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

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

六、雖然跑樞紐很容易，套數字卻很手工，一筆一筆帶感覺不怎麼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」，把地區客戶幣
別都併在一起，是為了加總金額用的，需要分幣別計算，所以併入參數中，
下一步驟就可以瞭解其作用。

八、自動填寫出貨日的公式：「
=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(s) 函數妙用）：

Excel如何帳齡加權計算合計：

http://www.b88104069.com/archives/2616

Excel如何編製價量分析表：

http://www.b88104069.com/archives/2347

Excel如何多條件求和：

http://www.b88104069.com/archives/1503

--


周末，我們繼續Excel：精華區=>21.心得=>5.其他=>3.office

會計人的Excel小教室： https://www.facebook.com/acctexcel


--