Excel Home 数据透视表初级班（10118班）小结第四课时

论坛链接：club.excelhome.net

培训中心链接：t.excelhome.net

第一章 开篇

这么快就到了最后一课了，学这课最大的感觉是，终于明白，为什么说，数据透视表结合VBA，才是真正的强大。同时也体会到了，这么简单的刷新动作，原来也包含这么多的内容。

 用VBA，确实可以实现很多，本来很复杂，需要操作很多的动作。越来越喜欢Excel，越来越喜欢透视表，越来越爱VBA了。

第二章 视频知识点

(一) 为什么刷新数据透视表

第一、   源数据表是不断变化的

第二、   数据透视表需要呈现数据的最新状态

(二) 如何刷新数据透视表

1、手工刷新

2003：

1、 数据透视表工具栏：数据透视表工具栏→刷新数据，图1

2、 右键菜单：光标放置在透视表中，右键→刷新数据，图2

2007/2010

1、 光标放置在数据透视表中，功能区会出现数据透视表工具，在选项页选择刷新，图3

2、 同2003 No.2

图1：                                 

图2：         图3：    

Tips:在这里老师给我们介绍了在同一个工作薄中不同的工作表,想要同时显示的小技巧

操作方法：菜单栏单击窗口→新建窗口(会打开一个同样的工作薄)→在窗口中选择需并排的工作表，这样可以同时看到一个工作簿中的不同工作表

在2010中，这些设置在功能区的视图工具栏中，下图，新建窗口以及全部重排。

2、自动刷新

1)    打开时刷新

 在数据透视表中单击右键→表格选项→勾选打开时刷新→确定

2003:

2007/2010

2)    定时刷新:

2003：在数据透视表上单击右键→单击表格选项→设置定时刷新，勾选刷新频率，上图

2007/2010：光标放在数据透视表中，在数据透视表工具栏的选项页→刷新的下拉箭头→选择连接属性，勾选刷新频率。

 

Tips:定时刷新仅用于外部数据源, 当数据表不是基于外部数据源创建的透视表,定时刷新将是灰色状态。

如何创建外部数据源透视表:

菜单栏→数据→导入外部数据→导入数据→选中外部数据源→打开→确定

3)    代码刷新全部数据透视表

Tips: 如果文件中包含有VBA代码，在打开文件的时候，会看到安全警告提示框,然后点击启用宏.如果没有就需要我们启用宏。

操作方法:菜单栏→工具→宏→安全性→设置为中→确定

      i.         激活工作表时刷新

 在作表标签单击右键→查看代码(实际这是打开VBA窗口),或者按ALT+F11,在工程中选中相应的工作表的名称，在其中对象里选择worksheet(右上角)，然后过程处选择Active。这个意思是当当前sheet Active的时候，则会跳用到这里面的代码。

代码:

PrivateSub Worksheet_Activate()

ActiveSheet.PivotTables(1).PivotCache.Refresh

Endsub

     ii.         打开工作簿时刷新

选择ThisWorkBook，然后在其中对象选择WorkBook，过程选择Open，在其中添加如下代码，则刷新第一个sheet的第一个透视表的数据。

代码:

PrivateSub Workbook_Open()

Sheets(1).PivotTables(1).PivotCache.Refresh

Endsub

   iii.         添加批量刷新按钮:

 步骤:工具栏→插入→图片→自选图形→在基本形状中选择矩形框→单击右键→指定宏→确定

以上方法是添加一个图形，指定宏，也可以添加Button，或者图片等，指定宏，然后点击这个图片时，运行相应的代码。

以下代码为刷新当前Active的WorkBook的所有Sheet。

 代码:

SubMacro1()

ActiveWorkbook.RefreshAll

EndSub

   iv.         在工具栏中添加同时刷新按钮

操作步骤:

1)      数据透视表工具栏→工具→自定义→命令选项卡→类别→数据→全部刷新→拖动到数据透视表工具条上

2)      数透透视表工具栏→添加或删除按钮→数据透视表→勾选”全部刷新”

3)      在2007/2010中，则是直接在数据透视表工具栏中，刷新的下拉箭头里有全部刷新。

(三) 数据透视表数据缓存

1.    定义

数据透视表的数据缓存是计算机内存中的一个指定区域用于存储表数据

2.     共享数据缓存

 ⑴基于同一单元格区域或数据连接的两个或多个数据透视表自动共享缓存

 ⑵可以实现同时刷新

 ⑶优势:提高性能的同时减少了工作薄的大小

3.    下列情况不能共享数据缓存

 ⑴不希望所有数据透视表中都显示计算字段和计算项

 ⑵不希望在所有数据透视表中都按同一方式分组字段

 ⑶:不希望同时刷新所有数据透视表

(四) 刷新数据透视表的注意事项

1)      海量数据源将导致刷新速度非常慢

2)      如何清除“垃圾条目”

操作方法1: 将字段列表拖出透视表框→单击刷新按钮→再字段拖回相应位置

操作方法2：使用宏，以下代码的含义是将MissingItemsLimit清空，之后再刷新数据透视表

Sub sx()

    Dimpvt As PivotTable, pvtcache As PivotCache

    Dimsht As Worksheet

    ForEach sht In ActiveWorkbook.Worksheets

       For Each pvt In sht.PivotTables

           pvt.PivotCache.MissingItemsLimit = xlMissingItemsNone

       Next pvt

    Nextsht

    OnError Resume Next

    ForEach pvtcache In ActiveWorkbook.PivotCaches

       pvtcache.Refresh

    Nextpvtcache

    OnError GoTo 0

End Sub

3)      数据源中新增的“行”和“列”无法更新到数据透视表中.

本期课程中还没有讲到怎么处理这种情况，但是更高级的班级里面，应该会解决这个问题吧，之前有提到过一个动态数据源的概念，应该是用来解决这个的。

第三章 学习心得

这一课终于提到了数据缓存，这让我更明白第一次时的那个，复制透视表的时候，新建的文档是在打开文档内新建时，复制的是透视表。而在外面新建时，复制的只有表了。

不过这节课最大的收获，是让我更深刻地体会到了，VBA在透视表中的应用。两者结合起来，真的无比地强大。

之前在看一本叫《你早该这么用Excel》的书的时候，让我意识到Excel中透视表的强大，学了这课之后，我觉得那个作者不会VBA，那他离真正的高手，永远有一段距离。貌似偶在一步一步慢慢向高手迈进哈，真开心。

不得不说，Excel真的是微软开发的最成功的软件了，没有之一。