Excel函數自動生成樞紐彙總 - 會計

Excel函數自動生成樞紐彙總

網誌圖文版：

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

會計Excel函數的最高境界，我覺得是自動編製例行性報表。想當初，我是台
北某公司小會計課長，每個星期五資金預估，那資金預估表看起來複雜，其
實就是把ERP導出來的報表，經過一番整理，將所需要資訊彙總在一起而已。
每個星期，我得重覆一遍相同的步驟，歷時至少半個小時。後來，我把這些
步驟寫成函數，每星期將最新的ERP報表複製貼上，Excel函數就會刷刷刷，
自動幫我編製好報表，歷時五分鐘！我的這個實驗，讓當時Excel功力已達資
料庫的主管，都為之讚賞，以下，分享最基礎的步驟，也就是以函數自動生
成樞紐彙總：

一、ERP跑出來的模擬付款明細表，如圖所示，實際上還有廠商、帳款日期等
欄位，被我省略掉了，而且通常一個星期或一個月的資料筆數，會遠遠多出
此範例很多，我這是簡單說明的刪除版本。

二、會計人一般拿到這種報表，很多時候就是跑樞紐，統計某欄位的金額，
例如，將模擬付款明細表的應付款日彙總，得出之後每個日期應付款總金額
，方便作資金規劃。

三、每個星期的ERP報表筆數不同，要自動整理資料，首先要自動判斷資料筆
數有多少：「=COUNTA(報表!A:A)-1」，函數「COUNTA」會計算範圍中非空值
的儲存格個數，這裡減個1，是把欄位那一列減掉，所以會得出ERP報表的筆
數，也就是「12」。

四、在自動編製報表的Excel檔案中，至少要有兩個工作表，一個是把最新的
ERP報表複製貼上，一個是自動編製的報表（會先把欄位名稱的表頭貼上）。
在判斷出最新ERP報表的資料筆數之後，第一步是自動複製資料，以便於後續
處理：「=IF(ROW(B2)-1>$O$2,"",報表!A2)」，函數「ROW」會傳回引用儲存
格的列數，例如「B2」的列數就是2，「(ROW(B2)-1」表示是減掉第一欄欄位
名稱後的列數，「$O$2」就是上一項用「=COUNTA(報表!A:A)-1」得到的資料
筆數，「$」用意是鎖定儲存格，「=IF(ROW(B2)-1>$O$2,"",報表!A2)」表示
如果列數大於資料筆數了，顯現空白，否則傳回ERP報表A2（第一欄第二列）
資料。

五、只要沒有掛「＄」鎖定，拉儲存格公式時，欄位和列數會自動跳，例如
「B2」儲存格帶「報表!A2」的內容，「C2」會帶「報表!B2」的內容，「B3
」就會帶「報表!A3」的內容，利用這個特性，就可以把整個最新ERP報表的
內容複製生成，而且因為之前有抓ERP報表的資料筆數，所以控制好超出範圍
的會顯示空白，這樣比較美觀。

六、想要依照「應付款日」彙總，所以必須先整理出「應付款日」的內容，
第一步：「=IF(C2="","",SMALL(C:C,ROW(H2)-1))」，「IF(C2="","",」同
樣是控制超出筆數範圍的空白，「SMALL(C:C,ROW(H2)-1)」是將12筆資料的
應付款日，從小排到大，「C:C」是應付款日欄位，「ROW(H2)-1)」的計算值
是1、「ROW(H3)-1)」的計算值是2，這樣便得到一個序列，「
SMALL(C:C,ROW(H2)-1)」表示在C欄中，取第一小的儲存格內容。

七、實際操作中，不需要那麼多相同的應付款日（如「2015/／9／16」），
要再想個辦法整理出不同的應付款日排序：「
=IF(C2="",0,IF(EXACT(H2,H1),0,H2))」，「IF(C2="",0,」如果空白，顯示
數值為0，否則「IF(EXACT(H2,H1),0,H2))」，也就是如果左邊欄位的上下儲
存格相同，同樣顯示數值為0，否則傳回H2，如圖所示，結果便是把第一個出
現不同的應付款日保留，其餘歸零。

八、在上一個步驟的基礎，可以先編個應付款日的序列：「
=COUNTA(I:I)-COUNTIF(I:I,0)-ROW(J2)+2」，「COUNTA(I:I)」為I欄中非空
值的儲存格個數，「COUNTIF(I:I,0)」為I欄中數值為0的儲存格個數，「
COUNTA(I:I)-COUNTIF(I:I,0)」就是I欄中非0的應付款日個數（4），「
ROW(J2)」的數值為2，所以整個公式「
=COUNTA(I:I)-COUNTIF(I:I,0)-ROW(J2)+2」的結果是（4＋2－2）=4，往下
拉，便成了4、3、2、1、0、－1、……的數列排序。

九、有了數列排序，接下來可以排序應付款日了：「
=IF(J2<1,"",LARGE(I:I,J2))」，如果K欄相對的J欄的應付款日小於1，顯示
空白，否則就在I欄中取相對應大的儲存格內容，例如「J2」是4，在「K2」
的公式會傳回J欄中第四大的內容，也就是「2015／9／16」，「K3」會傳回
第三大，I欄總共有五個應付款日，重覆的應付款日都被設定為「1900／1／
0」（0），所以依序排列的結果，如圖所示。

十、最後，再用個「=SUMIF(C:C,K2,F:F)」，成功達到跟樞紐彙總相同的效
果，而且重點是，以後每次要再彙總模擬付款明細表，只要將最新或更新的
ERP報表，整個複製貼上到資料工作表即可，這對於一再重覆的例行性報表，
有很大的自動化作用。


延伸閱讀（SUMIF函數）：

《Excel如何彙總營業成本表項目》：

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

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

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

《Excel如何多條件求和》：

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

--


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

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


--