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

作者: moodyblue   2016-06-04 21:46:49
Excel如何彙總多個銀行日報表金額
網誌圖文版:
http://www.b88104069.com/archives/4065
先前文章提到如何利用名稱及Index函數,整理出Excel工作表清單。當時列
出清單只是手段,最終目的為加總每一天的現金日報表。實務上有兩種情況
,第一種比較單純,每一天格式不但固定,而且待加總交易都在相同位置的
儲存格,這個在Excel只要一個函數公式即可加總;另一種稍微複雜,每一天
格式雖然固定,但因為每天交易不同,待加總的資料不一定在同一個儲存格
,這個在Excel操作上,必須多幾個步驟。以下分別介紹兩種情況的作法:
一、如圖所示,四個工作天的銀行日報表,分別編製在四個工作表上,名稱
為「5.1、5.2、5.3、5.4」,圖例上把四個表貼在一起,只是方便說明。每
天的日報表分成新台幣和美金,欄位依次為前日餘額、收入、支出、本日餘
額,黃色部份為新台幣收入、紅色部份為美金支出,四個工作天的收入和支
出合計金額,都是10,000。而且仔細看,每種幣別的收入和支出都在同一個
位置,如此可以很方便地加總。
二、新增一個同樣格式的日報表,用意在彙總每天數據。由於每天欄位固定
不變,只要輸入公式:「=SUM('5.1:5.4'!B3)」,輕鬆將5.1~5.4的新台幣
收入和美金支出加總。
三、前述日報表欄位固定不變,實際情況也有可能像圖例所示,5.1和5.2沒
有收入只有支出。黃色和紅色部份和先前步驟一樣,仍然代表每日工作表的
固定儲存格,但是因為5.1和5.2欄位改變了,顏色(固定儲存格)加總起來
的金額,並不是我們所要的。
四、在彙總日報表上,輸入相同公式:「=SUM('5.1:5.4'!B3)」發現金額變
了,這是因為每天的工作表欄位有變化了。像這種情況,可以先把每個工作
表的金額羅列出來,然後再加總同一表上,具體方法請繼續往下看。
五、利用get.workbook,排列出每個工作表名稱,也就是日期:「
=REPLACE(INDEX(workbook,ROW(B3)-2),1,FIND("]",INDEX(workbook,ROW(B
3)-2),1),"")」,然後再加總每個工作表裡,符合某特定條件的儲存格,公
式稍微複雜:「
=SUMIF(INDIRECT("'"&B3&"'!A:A"),$H$2,INDIRECT("'"&B3&"'!C:C"))」意
思是加總名稱為B3(5.1)的A欄(摘要)中,如果有儲存格內容為H2(支出
)的,加總相對應C欄(美金)的值。以後有機會,也許再詳加介紹
INDIRECT這個函數的應用。
六、上一個步驟將新台幣和美金的收入及支出都架好了,加總變得很容易,
如圖所示,輸入公式:「=SUM(五!D:D)」,加總新台幣收入,其餘如法泡製
即可。
這一篇文章所介紹方法,有點繁瑣,步驟也有點多,不過就是依照狀況,一
步一步思考解出來的。實務操作上,有些步驟可以簡化,例如工作表名稱,
如果是很規律的5.1、5.2、5.3、……,毋須get.workbook,直接5.1往下拉
便是。不過話說回來,也就是在這樣解題過程中,熟悉了種種Excel小技巧,
這些技巧,有可能在哪些工作場合上,派得上用場呢!
延伸閱讀:
Excel如何以名稱及index函數整理工作表清單:
http://www.b88104069.com/archives/4060
Excel如何以index函數查找傳票摘要:
http://www.b88104069.com/archives/3307
Excel如何顯示公式計算結果:
http://www.b88104069.com/archives/1915

Links booklink

Contact Us: admin [ a t ] ucptt.com