数据透视表自动刷新

lee_tao57

使用数据透视表（透视图）创建数据分析报告是人们经常使用的方法，但要保持分析报告所呈现信息或可视化见识的实时性，需要不断与源数据更新同步而刷新数据透视表，一般情况是手动或在打开文件时自动或计时自动来实施其刷新。

• 手动刷新数据透视表。在源数据更改后刷新数据透视表的快速简便方法是手动更新它，右键单击数据透视表中的任何单元格，然后选择“刷新”。或在Excel功能区的“数据”选项卡下“查询和连接”选择“全部刷新”，以刷新Excel工作簿内部数据和连接外部数据更新时的数据透视表。
• 打开文件时自动刷新数据透视表。右键单击数据透视表中的任何单元格，然后选择“数据透视表选项”；在“数据”选项下，将选中“打开文件时刷新数据”标记；然后单击“确定”按钮，保存选项设置。
• 计时自动刷新透视表。若数据源为多个外部连接（“获取和转换数据”连接的数据源），并数据添加到数据模型，可基于其连接中的计时进行刷新。在Excel功能区的“数据”选项卡下单击查询和连接，在“查询和连接”窗格中，单击“查询”或“连接”选项卡；用鼠标右键单击选择“属性”；在“使用状况”选项卡上，选中“刷新频率x分钟”标记，在分钟框中键入数字以设置计时，可根据数据源大小设置刷新时间；最后，单击“确定”，保存选项设置。
  
  

上述三种数据透视表刷新方法，文中不再作详尽描述及演示。本文就使用简单VBA创建自动数据透视表刷新的方法描述如下， VBA脚本已添加在源数据工作表对象中，通过Excel工作表变化事件触发，仅当且当源数据表发生变化时，如日常的数据更新等，数据透视表自动刷新，仅需变更源数据即可演示本自动刷新功能，参阅示例文件当第一行单位列源数据明显变化时，透视表PT1中2017年1月数据所示明显变化，透视表PT2中Florida数据所示明显变化，同理透视表PT3中Product 2数据所示明显变化。

VBA示例文件代码脚本：

PrivateSub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

              For Each pc InThisWorkbook.PivotCaches

                     pc.Refresh

              Next pc

              MsgBox "源数据已被改变" &vbNewLine & vbNewLine & "所有透视表及透视图已更新"_

              & vbNewLine & vbNewLine& "透视表缓存数：" & ActiveWorkbook.PivotCaches.Count, _

              vbOKOnly, "更新信息"

              Application.EnableEvents = True

EndSub

在源数据工作表对象中的VBA脚本中，还包含有代码注释以及其它可替换代码，供参考使用。测试脚本插入了信息框，仅仅起到刷新提示作用，与刷新执行自身并无关联。当源数据发生更改时，代码执行会先弹出”更新信息框“，提示：”源数据已被改变“及”所有透视表及透视图已更新“，并提示了透视表缓存数；然后，将刷新Excel活动工作簿的数据透视表。信息框附图所示。

lee_tao57

附上示例文件

踏破拖鞋

感谢分享  太好用了  谢谢

日月星辰xu

非常的强大。楼主辛苦了。