贊贊小屋

Excel如何設置樞紐分析表更新

Excel樞紐分析表的重症使用者,例如每個月跑樞紐編製管理報表的會計人,應該每個月都要經歷這個流程,下系統報表、整理報表格式、建立樞紐分析表、整理分析表欄位、然後將樞紐資料編製成管理報表。這其中,每一道步驟都有自動化的可能性,在此介紹樞紐分析表自動更新的方法,具體如下:

一、1701及1702的明細分類帳,簡化版本。

1701及1702的明細分類帳

二、豎的是科目、橫的是月份,彙整成樞紐分析表。

豎的是科目、橫的是月份

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

新建一個工作表「明細分類帳」

四、像這種情況,直覺作法是「變更樞紐分析表資料來源」,可參考《會計人的Excel小教室》:4.2「樞紐分析表資料更新」。但也許有苦命的小會計如我一般,對於這個步驟每月重覆感到有點厭倦,想耍點小聰明、想搞搞Excel。

變更樞紐分析表資料來源

五、「公式」頁籤、「已定義之名稱」區塊、點選「定義名稱」。

定義名稱

六、在「新名稱」對話框中,取名「明細分類帳」,於「參照到」中輸入:「=INDIRECT(“明細分類帳!$A$1:$H$”&COUNTA(明細分類帳!$A:$A))」,然後「確定」(假設已經設好一個工作表,內容是一月及二月的傳票明細)。

在「新名稱」對話框中

七、名稱設定好後,「分析」頁籤、「資料」區塊、點選「變更資料來源」,在對話框中,將「表格/範圍」直接改成「明細分類帳」。

變更資料來源

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

更新工作表「明細分類帳」

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

直接右鍵點選「重新整理」

有興趣的讀者,可以試著將三月份傳票刪除,右鍵更新,或者將四月份重覆貼兩次,右鍵更新,會發現所設計的這個名稱樞紐分析表,是很聰明的動態範圍,原始資料一有變化,無論增添或刪減,只要「重新整理」,樞紐分析表馬上跟著更新,非常方便,很適合每個月都有新資料的會計人。如果不希望累積每個月的資料,也可以直接將新月份的傳票覆蓋原有資料,這樣會是單月更新的樞紐分析表,應用上很靈活而且省事。

本文內容取自《會計人的Excel小教室進階篇|報表自動化》,金石堂網路書店網址:https://www.kingstone.com.tw/basic/2014713574503

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