贊贊小屋

Excel如何以名稱及Index函數整理工作表清單

會計工作有很多例行性的報表,大部份是月結編製一次,不過其中有一個必定是每天編製,那就是銀行(現金)日報表。因為會計是公司財務大臣,一切大大小小的金錢進出,都必須經過財務,所以必須很清楚每天的收入記錄,才能做好資金調度,確保何時該收錢、何時該付錢。通常這個日報表會計每天編、老闆每天看,因為再怎麼說,會計只是幫忙保管而已,那些錢最終是老闆的(公司股東)。

每天編的報表,習慣上Excel會弄成一天一個工作表。然而,日編月結,實務上需要將每天的日報表彙整成月報表,這個如果想透過Excel自動化,節省人工作業時間,程序比較複雜,主要有三個步驟:首先列出每日工作表清單,接著抓取每天各項目的金額,最後才將金額彙總到月報表。在此,分享第一個步驟:

一、簡單版銀行日報表,有幣別、前日餘額、本日收支、本日餘額,通常實務上還會有各幣別庫存現金、各銀行帳戶等資訊,完整一點的連金融資產及借款負債都會放上去。

簡單版銀行日報表

二、如果每天格式都一樣,某個儲存格一直都是某項交易,例如B3都是當天人民幣收入,那其實期間合計很簡單,一個公式可以搞定:「=SUM(‘5.1:5.3’!B3)」意思是將工作表「5.1」到「5.3」的「B3」儲存格加總。

=SUM('5.1:5.3'!B3)

三、範例很理想,現實很複雜。實務上可能需要加總工作表不同的儲存格,例如在收入項下又細分成應收帳款、雜項等,但每天狀況不一樣,也許兩者都有,也許只有一個,導致收入紀錄不會剛剛好在相同的儲存格,這時候如果要彙總,有個Excel小技巧很實用,那就是列出活頁簿上的工作表清單。「公式」、「名稱管理員」、「新增」。

「公式」、「名稱管理員」、「新增」

四、在跳出來的視窗中,名稱設定為「workbook」,參照到的內容輸入「=get.workbook(1)」這是一個巨集函數,實際上就是Excel活頁簿工作表清單,究竟它有何用途,繼續往下操作便知道。

=get.workbook(1)

五、新增確定後,回到名稱管理員視窗,可以看到新的名稱已經建立。有時候拿到別人的Excel檔案,發現裡面有很多奇妙的機關,來這個名稱管理員視窗看看,也許會發現許多小技巧,能善用名稱功能,是Excel中階運用的功夫。

回到名稱管理員視窗

六、回到Excel活頁簿,輸入公式:「=INDEX(workbook,ROW(B1))」,表示依照條件引用資料,在這個,ROW(B1)的值是1,公式往下拉,B2、B3、……的值依序是2、3、……,配合workbook為活頁簿工作表集合,結果如圖所示。

=INDEX(workbook,ROW(B1))

七、「get,workbook」會帶出活頁簿及工作表,但其實我們只需要工作表,所以再加工一下,除了上一步驟的公式一,公式二是:「=FIND(“]”,A2,1)」,表示找出「A2」儲存格中,「]」出現在第幾個字元,結果都是「11」。公式三是:「=REPLACE(A2,1,B2,””)」表示把「A2」中第一字元到第11(B2 )字元以空白(”)替代,結果便是我們要的工作表名稱。公式四是把公式一到三結合在一起:「=REPLACE(INDEX(workbook,ROW(A1)),1,FIND(“]”,INDEX(workbook,ROW(A1)),1),””)」。

=REPLACE(INDEX(workbook,ROW(A1)),1,FIND("]",INDEX(workbook,ROW(A1)),1),"")

以上,當Excel活頁簿有很多工作表,例如銀行日報表、例如成本結算流程、例如存貨編碼原則,能夠把全部工作表列成清單,是個相當實用小技巧。稍後,再來談談如何利用清單加總金額。

本網站所有內容皆受版權保護,网站备案编号:苏ICP备14051307号-1