Excel如何以函數自動生成樞紐彙總




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

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

ERP跑出來的模擬付款明細表

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

將模擬付款明細表的應付款日彙總

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

=COUNTA(報表!A:A)-1

四、在自動編製報表的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(第一欄第二列)資料。

=IF(ROW(B2)-1>$O$2,"",報表!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欄中,取第一小的儲存格內容。

=IF(C2="","",SMALL(C:C,ROW(H2)-1))

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

=IF(C2="",0,IF(EXACT(H2,H1),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、……的數列排序。

=COUNTA(I:I)-COUNTIF(I:I,0)-ROW(J2)+2

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

=IF(J2<1,"",LARGE(I:I,J2))

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

=SUMIF(C:C,K2,F:F)

關於SUNIF函數的用法,可參考先前文章:《Excel如何彙總營業成本表項目》《Excel如何帳齡加權計算合計》《Excel如何多條件求和》




當前文章延伸閱讀:
樞紐分析表