Excel銀行日報表彙總:GET.WORKBOOK、INDEX、REPLACE、FIND、INDIRECT應用

目錄

Excel報表經常分散在不同工作表,實務中可能需要彙總不同報表金額。本文以銀行日報表為例,介紹SUM、GET.WORKBOOK、INDEX、ROW、REPLACE、FIND、SUMIF、INDIRECT函數的實務綜合應用。

現金及銀行存款是公司流動性最高的資產,也因此是風險控管絶對不能鬆懈的地方,而且公司所有營業活動最終都是以現金(轉帳)交易,所以蠻多公司都會設置現金及銀行存款日報表,每天即時瞭解進出狀況及帳戶餘額。所謂日記月結,每天的日報表到了月底,習慣性會作個月度總結,然而日報表跟月報表畢竟性質不同,格式上必定會有差異,不是那麼容易處理,在此介結如何將日報彙總成月報:

一、相同結構的銀行日報表

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

二、銀行日報彙總表(SUM函數特殊用法)

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

三、結構不同的銀行日報表

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

四、SUM函數無法直接使用

此時在彙總日報表上,輸入相同結構的公式:「=SUM(‘5.1 (2):5.4 (2)’!B3)」,可想而知,因為匯總的報表錯位了,加總的金額也是錯的。

五、強大的GET.WORKBOOK巨集函數

像這種情況,比較全面性的作法,是將每天的日報表羅列出來,簡便方法是先新增一個名稱,把它叫做「Cash」(此名稱可自行設置),重點是在「參照到」必須輸入:「=GET.WORKBOOK(1)」。

六、INDEX及ROW函數讀取陣列值

在任何一個儲存格輸入公式:「=INDEX(Cash,ROW(A4))」,打開資料編輯列左邊的函數盒子,應該可以理解GET.WORKBOOK(1)名稱再加上INDEX函數,作用便是把當前工作簿上所有工作表編排成一組,參數「ROW(A4)」的計算結果是「4」,意思是取第四個陣列值,也就是第四張工作表(包含工作簿名稱):「[ch6-5_Excel如何彙總多個銀行日報表金額.xls]5.4」,這裡特意用ROW函數,是方便拉公式依序取第一張工作表、第二張工作表、第三張工作表、……。

七、REPLACE及FIND函數綜合應用

利用GET.WORKBOOK(1)名稱和INDEX函數的特性,輸入公式:「=REPLACE(INDEX(Cash,ROW(B3)-2),1,FIND(“]”,INDEX(Cash,ROW(B3)-2)),””)」。和上一個步驟用法相同,由於設計的第一張工作表名稱呈現在「B3」,「ROW(B3)-2」的計算結果是「1」,公式在B欄往下繼續拉,「B4」、「B5」的計算結果是「2」、「3」,等於是一連串的序號,「FIND(“]”,INDEX(Cash,ROW(B3)-2))」顧名思義便是找出工作表名稱([ch6-5_Excel如何彙總多個銀行日報表金額.xls]5.1)中「]」的起始位置,有興趣有可以算看看,計算結果是30。綜合下來,公式最外層結構是REPLACE([ch6-5_Excel如何彙總多個銀行日報表金額.xls]5.1,1,30,””),也就是工作簿加上工作表的名稱,從第一個字元到第30個字元,以空白(「””」)取代,結果是是將工作簿名稱刪除,只剩下工作表名稱(5.1)。

八、SUMIF與INDIRECT函數綜合應用

最後將每天的日報表彙整在一起:「=SUMIF(INDIRECT(“‘”&B3&”‘!A:A”),”收入”,INDIRECT(“‘”&B3&”‘!B:B”))」。其中「INDIRECT(“‘”&B3&”‘!A:A”)」意思是工作表5.1(儲存格B3)的A欄,所以套進去便是「SUMIF(工作表5.1的A欄,”收入”,工作表5.1的B欄)」,實際計算結果是加總5.1的所有收入金額(1,000)。

Excel函數建構一體適用的解決方案

這一節文章範例是比較簡單,用到的Excel技巧相對艱深,也許會有何必搞成如此的疑問。首先,實務上銀行日報表一定是會更為複雜,再者,除了現金及銀行存款之外,在諸如存貨異動、客戶收款、票據收付等其他資產負債,也可能都會設置日記帳,各有各的格式,和整個企業電子報表的設計和會計制度一樣,規劃上應該是一個長長久久的方案,相對應的,在Excel操作上也是追求一體適用的解決方案,如果熟悉這個章節所紹的各種函數方法,會計實務工作在哪個地方一定能夠派上用場。

相關文章