Excel数据透视表学习笔记02

三、刷新数据透视表

1.手动刷新数据透视表

方法一、选中数据透视表中任意一个区域（单元格也行），单击鼠标右键，弹出的快捷菜单中选择“刷新”，即可。

方法二、数据透视表工具----分析----数据----刷新

2.设置在打开文件时自动刷新

数据透视表选项----数据----打开文件时自动刷新数据

3.刷新链接在一起的数据透视表

当数据透视表用作其他数据透视表的数据源时，刷新其中任何一张数据透视表，链接在一起的数据透视表都会刷新。

4.刷新引用外部数据的数据透视表

？没找到“外部数据选项”中的“后台查询”复选框。

但是数据透视表工具----分析----数据----更改数据源----连接属性，可以查看引用外部数据的具体信息，也可以更改。

5.定时刷新

对于数据源为外部数据的数据透视表，可以设置固定时间间隔的自动刷新频率。

数据透视表工具----分析----数据----更改数据源----连接属性----使用状况----刷新频率，可以自定义刷新时间间隔。

6.使用VBA代码设置自动刷新

开发工具----代码----Visual Basic，进入如下界面：

找到属于需要刷新的数据透视表的VBA编辑窗口，该例中为Sheet1，在VBA代码窗口中输入如下代码，其中括号中的“数据透视表”应当根据实际情况修改为数据透视表的真实名称，可通过数据透视表工具----分析----数据透视表，查看数据透视表的名称。

Private Sub Worksheet_Activate()
    ActiveSheet.PivotTables("数据透视表").PivotCache.Refresh
End Sub

输入代码后保存该文件，再保存该工作簿，数据透视表就会自动刷新数据了。 

7.批量刷新数据透视表

当一个工作簿中包含了多个数据透视表时，为了方便，用户可以设置一个刷新按钮，设置VBA代码，一旦数据源发生变化，只需单击按钮，所有的数据透视表就会发生相应的变化。 

（1）在数据源工作表中，在数据源以外的任意区域上绘制一个矩形框；

（2）在矩形框中单击右键，选择“指定宏”菜单项，弹出“指定宏”对话框；

（3）单击“新建”按钮，进入VBA编辑窗口，在代码窗口中的代码“Sub矩形1_单击()”的下面输入代码：

ThisWorkbook.RefreshAll

如下所示：

返回工作簿，保存工作簿。可以修改矩形的填充样式等，在矩形内添加文字“批量刷新”，适当美化矩形。示例如下，当数据源变化时，单击该矩形框，即可批量刷新该工作簿中的数据透视表。

 

8.数据透视表的刷新注意事项

（1）海量数据源限制数据透视表的刷新速度

（2）如何清除已删除数据的标题项

当数据源中删除了一些不再需要的数据时 ，数据透视表被刷新后，数据透视表字段的下拉列表中仍然存在着被删除的数据项（一般是行标题或列标题），此时应该删除数据的标题项。

示例：删掉数据透视表中行字段中多余的标题项。

（1）先将所有行字段挨个拖出数据透视表区域；

（2）再选中剩余数据透视表中任意一个单元格，单击鼠标右键，选择“刷新”；

（3）再将所有行字段依次拖回数据透视表的行字段区域。

对于数据透视表字段中出现的空白标题项，这是由于删除了数据源汇总的部分数据，导致数据源中有空白部分被当做了数据透视表的数据源，要想删掉空白标题项，只需更改数据透视表的数据源的引用部分：

数据透视表工具----分析----数据----更改数据源

四、数据透视表的格式设置

1.修改数据透视表的格式

1.1数据透视表的自动套用格式

（1）数据透视表工具----设计----数据透视表样式，可以按需选择多种数据透视表格式。

（2）数据透视表工具----设计----布局----报表布局，可以更改数据透视表的布局，比如以压缩形式显示、以大纲形式显示和以表格形式显示。

图4-1 传统格式的数据透视表

图4-2 以压缩形式显示的数据透视图

图4-3 以大纲形式显示的数据透视表

图4-4 以表格形式展示的数据透视表

（3）“无”格式的数据透视表

即隐去传统数据透视表列区域标题中的“求和项”，以及其上方的“数据”等文字。

？但是我没找到新版Excel中的无格式在哪儿 

 

1.2数据透视表刷新后如何保持调整好的列宽

（1） 通过设置数据透视表选项保持列宽（数据透视表没有应用自动套用格式）

选中数据透视表中任一区域，单击鼠标右键，选择“数据透视表选项”，选择“布局和格式”，取消勾选“更新时自动调整列宽”。

（2）在应用了自动套用格式的数据透视表中保持列宽

只需通过在字段名称的前面或者后面插入多个空格，默认情况下，数据透视表刷新后，列宽会被自动调整为列字段“最合适宽度”。

（3）使用VBA代码控制数据透视表保持列宽

示例：如果要求始终保持数据透视表A至E列的列宽为10磅。

开发工具----代码----Visual Basic，进入Microsoft Visual Basic窗口，找到属于数据透视表的VBA代码窗口，在代码窗口中输入如下代码：

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Columns("A:A").ColumnWidth = 10
    Columns("B:B").ColumnWidth = 10
    Columns("C:C").ColumnWidth = 10
    Columns("D:D").ColumnWidth = 10
    Columns("E:E").ColumnWidth = 10
End Sub

返回工作簿，保存工作簿，数据透视表就会自动将A至E列的列宽设置为10磅。

1.3控制数据透视表的行列总计和分类汇总

数据透视表工具----设计----布局----总计，可选择是否对行、列启用总计

数据透视表工具----设计----布局----分类汇总，可选择是否显示分类汇总，以及在组的顶部还是底部显示分类汇总。

1.4控制数据透视表的合并标志

数据透视表选项----布局和格式----布局----合并且居中排列带标签的单元格，就可以将数据透视表的分类项目居中显示。

1.5为数据透视表的每项后面插入空行

数据透视表工具----分析----活动字段---字段设置----布局打印，勾选“在每个项目标签后插入空行”即可。

1.6批量设置数据透视表中某类项目的格式

（1）“选定内容”功能默认是启用的。即光标靠近字段边界时出现的→或者向下箭头图案，则说明了“选定内容”功能启用了。

？没找到如何手动启用。

（2）快速设置数据透视表的分类汇总格式

批量选择单元格区域后，通过以下步骤可以设置格式。

数据透视表工具----设计----样式----单元格样式

（3）在数据透视表中快速选取同类数据

应用“选定内容”功能，可以在行字段的标志项中快速选取同类数据。

这一功能对于查找重复数据或标识同类数据很有帮助。

示例：将箭头光标可以移到“CCS-192”数据项所在单元格左侧，当光标变为→图案时，单击鼠标左键可以同时选作C13单元格与C8单元格相同内容的数据项。

1.7修改数据透视表中数值型数据的格式

（1）通过“选定内容”功能快速选定单元格区域设置格式。

选定单元格区域后，单击鼠标右键，打开“设置单元格格式”对话框，按需设置单元格格式即可。

（2）数据透视表中数值型单元格数据的格式设置

选中字段，单击鼠标右键，选择“值字段设置”，选择“数字格式”，就可以设置该字段的单元格格式。

1.8处理数据透视表中的空白数据项

空白单元格出现在数据透视表的行字段中，会显示会“（空白）”字样；出现在列字段中，会默认显示为空值。

（1）替换行字段中包含“（空白）”字样的数据项

利用“查找和替换”功能，<Ctrl+H>组合键打开“查找和替换”对话框，查找内容中输入“（空白）”，替换内容换一个空格，单击“全部替换”。

（2）填充列字段空白数据项

不能采用“查找和替换”的方法。

数据透视表选项----布局和格式----格式，勾选“对于空单元格，显示”复选框，并在其后面填写需要显示的内容，如“注意，数据不完整！”。

（3）快速去除行字段中的空白数据项

方法一、通过调整字段清楚空白数据项：

（3.1.1）将含有空白的字段调整到首列。

（3.1.2）选中首列空白的单元格区域，点击鼠标右键，选择“筛选”，选择“隐藏所选项目”。

（3.1.3）再将挪到首列的字段拖动调整回原位置。

方法二、应用“选定内容”功能清除空白数据项

（3.2.1）将鼠标左键移动至包含有空白数据项的字段的第一个单元格左侧，当鼠标光标变成→时单击鼠标右键，选中该字段下的所有包含空白数据项。

（3.2.2）单击鼠标右键，选择“筛选”，选择“隐藏所选项目”。

1.9 设置错误值的显示方式

当数据透视表中添加了计算项后，可能会出现错误值，可以设置错误值的显示方式。

数据透视表选项----布局和格式----格式，勾选“对于错误值，显示”复选框，并在后面填入所需要错误值显示的文字、符号等。

1.10在数据透视表中使用自定义数字格式

选中数据透视表中任一单元格区域，点击鼠标右键，选择“值字段设置”，点击“数字格式”，点击“自定义”，在右侧的“类型”中输入自定义的数字格式。

2.数据透视表及条件格式

2.1突出显示数据透视表中的特定数据

？为什么数据源中合同结束日期的格式为“2005/5/15”，创建数据透视表后，将合同结束日期放在行字段处，显示的日期格式却是“5月”，设置单元格格式也没办法改成“2005/5/15”。

（1）选中需要设置条件格式的数据透视表区域；

（2）开始----样式----条件格式

（3）根据需要选择条件格式，设置条件和满足条件所显示的格式，也可根据“其他规则”选项自定义条件格式，例如根据公式设置条件格式。

2.2为数据透视表添加永恒的边框

？书上的方法行不通，暂时没找到解决方法。

3.数据透视表美化实例

（1）数据透视表工具----分析----活动字段----字段设置----分类和汇总，选择“自定义”，选择“求和”和“平均值”，单击“确定”；

（2）选择数据透视表中行字段区域的最右边一个单元格（这个单元格不属于数据透视表区域），单击“数据”----“排序和筛选”----“筛选”，为数据透视表的所有列字段设置自动筛选。

注：数据透视表区域内不能直接设置自动筛选。

注：若只想对几个列字段设置“自动筛选”，？书上的方法不行，暂时没找到解决方法。

（3）利用“查找和替换”将字段中“求和项：”替换为“一个空格”，修改字段名称，使得标题更加整齐规范。

（4）利用自动筛选，只展示“求和”和“平均值”项。

（5）简单美化数据透视表，设置不同底色，设置数字格式为无小数位，去除网格线。

图4-3-1 数据透视表美化实例

？遗留的问题：

（1）如何添加报表表头文字；

（2）数据透视表选项----格式，找不到“合并标志”；

（3）利用条件格式给数据透视表设置永恒的表格线时，添加的公式行不通（或许是因为Excel更新换代，所需公式变了）

（4）数据透视表中的“数据”单元格，利用书上的条件公式同样处理不掉。

4.解除数据透视表的列数限制

试了一下，现在好像没有限制，遇到了再去查书吧。