Excel善用函數可達到樞紐分析表效果，有時候更方便。本文以ERP模擬付款明細表準備編製資金預估表為例，介紹COUNTA、IF、ROW、SMALL、EXACT、COUNTIF、LARGE函數綜合應用。

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

一、模擬付款明細表

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

二、樞紐分析彙總

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

三、COUNTA函數

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

四、IF及ROW函數

在自動編製報表的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報表的資料筆數，所以控制好超出範圍的會顯示空白，這樣比較美觀。

六、SMALL函數

想要依照「應付款日」彙總，所以必須先整理出「應付款日」的內容，第一步：「=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欄中，取第一小的儲存格內容。

七、EXACT函數

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

八、COUNTIF函數

在上一個步驟的基礎，可以先編個應付款日的序列：「=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、……的數列排序。

九、LARGE函數

有了數列排序，接下來可以排序應付款日了：「=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函數

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

樞紐分析表與函數組合

Excel樞紐分析表可以快速彙總資料，其強大之處主要在於方便切換分析欄位，所以反過來說，如果彙總方式都是固定，例如本篇文章所分享範例，主要是想把ERP模擬付款明細表進行二次加工，得到編製資金預估表所需的付款日期小計，在這種情況下，也許固定的函數公式更為直覺，實務案例提供給讀者參考。

每天學習，每天充電：Excel函數文章合集。