標題:
發問:
I have put data in sheet 1 and make a pivot table in sheet 2 and save the file. Every day, I add data in sheet 1. Can you advise the formula that make the pivot table automatically renew after I add the data? 更新: as I use Chinese window, can you write the icon / tool bar name in English? Is pivot option =表格選項? But I can't find similar word of "Go to Data Tab"...
最佳解答:
For the pivot table , there's a function so whenever you open the file the data in the table will be refreshed automatically or refresh after certain minutes. You can right click the pivot table ->Pivot Table options GO to DATA tab then check the box (Refresh on open) or refresh after X minutes Data will be automatically refresh If you want to refresh data if there's any changes to your raw data in sheet 1, you need to insert a Macro VBA to help you to do this: Press ALT+F11 to lauch VBA editor... then on the left hand side , find your excel file sheet 1 and put the following thing in the box on the right: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:Z10000")) Is Nothing Then Sheets("Sheet2").PivotTables(1).RefreshTable End If End Sub As long as there's any changes within A2:Z10000 , the pivot table will automatically refresh..... I hope that helps. Let me know if there's any problem
其他解答:
此文章來自奇摩知識+如有不便請留言告知
Excel's pivot table發問:
I have put data in sheet 1 and make a pivot table in sheet 2 and save the file. Every day, I add data in sheet 1. Can you advise the formula that make the pivot table automatically renew after I add the data? 更新: as I use Chinese window, can you write the icon / tool bar name in English? Is pivot option =表格選項? But I can't find similar word of "Go to Data Tab"...
最佳解答:
For the pivot table , there's a function so whenever you open the file the data in the table will be refreshed automatically or refresh after certain minutes. You can right click the pivot table ->Pivot Table options GO to DATA tab then check the box (Refresh on open) or refresh after X minutes Data will be automatically refresh If you want to refresh data if there's any changes to your raw data in sheet 1, you need to insert a Macro VBA to help you to do this: Press ALT+F11 to lauch VBA editor... then on the left hand side , find your excel file sheet 1 and put the following thing in the box on the right: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:Z10000")) Is Nothing Then Sheets("Sheet2").PivotTables(1).RefreshTable End If End Sub As long as there's any changes within A2:Z10000 , the pivot table will automatically refresh..... I hope that helps. Let me know if there's any problem
其他解答:
文章標籤
全站熱搜
留言列表
