推 lisa830303: 推! 05/20 23:18
Excel如何以名稱及Index函數整理工作表清單
網誌圖文版：
http://www.b88104069.com/archives/category/salary/excel
會計工作有很多例行性的報表，大部份是月結編製一次，不過其中有一個必
定是每天編製，那就是銀行（現金）日報表。因為會計是公司財務大臣，一
切大大小小的金錢進出，都必須經過財務，所以必須很清楚每天的收入記錄
，才能做好資金調度，確保何時該收錢、何時該付錢。通常這個日報表會計
每天編、老闆每天看，因為再怎麼說，會計只是幫忙保管而已，那些錢最終
是老闆的（公司股東）。
每天編的報表，習慣上Excel會弄成一天一個工作表。然而，日編月結，實務
上需要將每天的日報表彙整成月報表，這個如果想透過Excel自動化，節省人
工作業時間，程序比較複雜，主要有三個步驟：首先列出每日工作表清單，
接著抓取每天各項目的金額，最後才將金額彙總到月報表。在此，分享第一
個步驟：
一、簡單版銀行日報表，有幣別、前日餘額、本日收支、本日餘額，通常實
務上還會有各幣別庫存現金、各銀行帳戶等資訊，完整一點的連金融資產及
借款負債都會放上去。
二、如果每天格式都一樣，某個儲存格一直都是某項交易，例如B3都是當天
人民幣收入，那其實期間合計很簡單，一個公式可以搞定：「
=SUM('5.1:5.3'!B3)」意思是將工作表「5.1」到「5.3」的「B3」儲存格加
總。
三、範例很理想，現實很複雜。實務上可能需要加總工作表不同的儲存格，
例如在收入項下又細分成應收帳款、雜項等，但每天狀況不一樣，也許兩者
都有，也許只有一個，導致收入紀錄不會剛剛好在相同的儲存格，這時候如
果要彙總，有個Excel小技巧很實用，那就是列出活頁簿上的工作表清單。「
公式」、「名稱管理員」、「新增」。
四、在跳出來的視窗中，名稱設定為「workbook」，參照到的內容輸入「
=get.workbook(1)」這是一個巨集函數，實際上就是Excel活頁簿工作表清單
，究竟它有何用途，繼續往下操作便知道。
五、新增確定後，回到名稱管理員視窗，可以看到新的名稱已經建立。有時
候拿到別人的Excel檔案，發現裡面有很多奇妙的機關，來這個名稱管理員視
窗看看，也許會發現許多小技巧，能善用名稱功能，是Excel中階運用的功夫
。
六、回到Excel活頁簿，輸入公式：「=INDEX(workbook,ROW(B1))」，表示依
照條件引用資料，在這個，ROW(B1)的值是1，公式往下拉，B2、B3、……的
值依序是2、3、……，配合workbook為活頁簿工作表集合，結果如圖所示。
七、「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),"")」。
以上，當Excel活頁簿有很多工作表，例如銀行日報表、例如成本結算流程、
例如存貨編碼原則，能夠把全部工作表列成清單，是個相當實用小技巧。稍
後，再來談談如何利用清單加總金額。
延伸閱讀（Excel小技巧）：
Excel如何以Index函數查找傳票摘要：
http://www.b88104069.com/archives/3307
Excel如何格式化條件設定組別分類：
http://www.b88104069.com/archives/3888
Excel如何自動填滿ERP報表空格：
http://www.b88104069.com/archives/4057
--
周末，我們繼續Excel：精華區=>21.心得=>5.其他=>3.office
會計人的Excel小教室： https://www.facebook.com/acctexcel
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 183.206.182.127
※ 文章網址: https://www.ptt.cc/bbs/Accounting/M.1463729421.A.83B.html