Excel如何彙總多個銀行日報表金額




先前文章提到如何利用名稱及Index函數,整理出Excel工作表清單。當時列出清單只是手段,最終目的為加總每一天的現金日報表。實務上有兩種情況,第一種比較單純,每一天格式不但固定,而且待加總交易都在相同位置的儲存格,這個在Excel只要一個函數公式即可加總;另一種稍微複雜,每一天格式雖然固定,但因為每天交易不同,待加總的資料不一定在同一個儲存格,這個在Excel操作上,必須多幾個步驟。以下分別介紹兩種情況的作法:

一、如圖所示,四個工作天的銀行日報表,分別編製在四個工作表上,名稱為「5.1、5.2、5.3、5.4」,圖例上把四個表貼在一起,只是方便說明。每天的日報表分成新台幣和美金,欄位依次為前日餘額、收入、支出、本日餘額,黃色部份為新台幣收入、紅色部份為美金支出,四個工作天的收入和支出合計金額,都是10,000。而且仔細看,每種幣別的收入和支出都在同一個位置,如此可以很方便地加總。

四個工作天的收入和支出合計金額

二、新增一個同樣格式的日報表,用意在彙總每天數據。由於每天欄位固定不變,只要輸入公式:「=SUM(‘5.1:5.4’!B3)」,輕鬆將5.1~5.4的新台幣收入和美金支出加總。

=SUM('5.1:5.4'!B3)

三、前述日報表欄位固定不變,實際情況也有可能像圖例所示,5.1和5.2沒有收入只有支出。黃色和紅色部份和先前步驟一樣,仍然代表每日工作表的固定儲存格,但是因為5.1和5.2欄位改變了,顏色(固定儲存格)加總起來的金額,並不是我們所要的。

5.1和5.2沒有收入只有支出

四、在彙總日報表上,輸入相同公式:「=SUM(‘5.1:5.4’!B3)」發現金額變了,這是因為每天的工作表欄位有變化了。像這種情況,可以先把每個工作表的金額羅列出來,然後再加總同一表上,具體方法請繼續往下看。

輸入相同公式:「=SUM('5.1:5.4'!B3)」

五、利用get.workbook,排列出每個工作表名稱,也就是日期:「=REPLACE(INDEX(workbook,ROW(B3)-2),1,FIND(“]”,INDEX(workbook,ROW(B3)-2),1),””)」,然後再加總每個工作表裡,符合某特定條件的儲存格,公式稍微複雜:「
=SUMIF(INDIRECT(“‘”&B3&”‘!A:A”),$H$2,INDIRECT(“‘”&B3&”‘!C:C”))」意思是加總名稱為B3(5.1)的A欄(摘要)中,如果有儲存格內容為H2(支出)的,加總相對應C欄(美金)的值。以後有機會,也許再詳加介紹INDIRECT這個函數的應用。

SUMIF(INDIRECT("'"&B3&"'!A:A"),$H$2,INDIRECT("'"&B3&"'!C:C"))

六、上一個步驟將新台幣和美金的收入及支出都架好了,加總變得很容易,如圖所示,輸入公式:「=SUM(五!D:D)」,加總新台幣收入,其餘如法泡製即可。

=SUM(五!D:D)

這一篇文章所介紹方法,有點繁瑣,步驟也有點多,不過就是依照狀況,一步一步思考解出來的。實務操作上,有些步驟可以簡化,例如工作表名稱,如果是很規律的5.1、5.2、5.3、……,毋須get.workbook,直接5.1往下拉便是。不過話說回來,也就是在這樣解題過程中,熟悉了種種Excel小技巧,這些技巧,有可能在哪些工作場合上,派得上用場呢!






當前文章延伸閱讀:
經典函數