Excel数据透视表学习笔记02 |
您所在的位置:网站首页 › 数据透视表连接属性设置 › 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)替换行字段中包含“(空白)”字样的数据项 利用“查找和替换”功能,组合键打开“查找和替换”对话框,查找内容中输入“(空白)”,替换内容换一个空格,单击“全部替换”。 (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.解除数据透视表的列数限制试了一下,现在好像没有限制,遇到了再去查书吧。 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |