Excdel樞紐分析表自動更新:INDIRECT函數建立傳票明細帳

目錄

Excel樞紐分析表可以快速彙總報表,本文以傳票明細分類帳為例,介紹如何以INDIRECT及COUNTA函數建立動態範圍名稱,自動更新樞紐報表資料來源,進一步提高效率。

樞紐分析表為Excel強大的資料統計工具,在工作中有很廣泛的應用,以會計實務而言,可能每個月都要跑樞紐編製管理報表,每個月執行相同的作業流程:下系統報表、整理報表格式、建立樞紐分析表、整理分析表欄位、最後將樞紐資料編製成管理報表。這其中每一道步驟都有自動化的可能性,在此介紹樞紐分析表自動更新的方法,具體如下:

一、傳票明細分類帳

1901及1902簡化版本的明細分類帳。

二、樞紐分析表

建立樞紐分析表,配置報表欄位,列標籤為科目、欄標籤為月份。

三、新月份傳票明細

到了三月份結帳,有新的傳票進來,希望保持資料完整統一,方便分析比較,於是添加到原來的報表上,彙總新建一個工作表「明細分類帳」。

四、變更樞紐分析表資料來源

像這種情況,直覺作法是「變更樞紐分析表資料來源」,詳細作法可參考《會計人的Excel小教室》第四章第二節「樞紐分析表資料更新」。雖然執行上不是太麻煩,但既然每個月都要重覆操作,這一節接下來重點介紹如何將其自動化。

五、定義名稱

上方功能區移到「公式」頁籤,在「已定義之名稱」指令群組中將「定義名稱」下拉,點選「定義名稱」。

六、INDIRECT及COUNTA函數

在「新名稱」對話框中,取名「明細分類帳」,於「參照到」中輸入:「=INDIRECT(“明細分類帳!$A$1:$H$”&COUNTA(明細分類帳!$A:$A))」,然後「確定」。

七、表格作為樞紐資料來源

名稱設定好後,滑鼠游標移到樞紐分析表任何一個位置,上方功能區即會多出一個「樞紐分析表工具」,於「分析」頁籤中的「資料」指令群組,點選「變更資料來源」,在對話框中將「表格/範圍」直接改成為「明細分類帳」,也就是上個步驟所設定的名稱。

八、樞紐分析表更新

樞紐分析表立即更新,多了一欄「1903」,表示已將三月份傳票納入來源資料中。

九、再次結帳更新傳票明細

四月份結帳,多了兩筆傳票,更新工作表「明細分類帳」。

十、輕鬆重新整理樞紐報表

只要在原來的樞紐分析表上,直接右鍵點選「重新整理」,一鍵更新!

讓Excel報表變得聰明的動態範圍

有興趣的讀者,可以試著將三月份傳票刪除,右鍵更新,或者將四月份重覆貼兩次,右鍵更新,會發現所設計的這個名稱樞紐分析表,是很聰明的動態範圍,原始資料一有變化,無論增添或刪減,只要右鍵更新「重新整理」,樞紐分析表馬上跟著更新,相當聰明方便,很適合每個月都會有新資料的會計人。如果不希望累積每個月的資料,也可以直接將新月份的傳票覆蓋原有資料,如此便是更新後的單月份樞紐分析表,應用上靈活且省事。
本文內容取自《會計人的Excel小教室進階篇|報表自動化》,書本仍然是最好的學習方法,今天就買本書吧!
博客來網路書店網址:

相關文章