Excel数据透视表入门与实战-(附DVD1张)

目录 Chapter 01 创建和共享数据透视表 1.1　使用数据透视表之前，需知这些.............2 1.1.1 数据透视表是什么....................... 2 1.1.2 数据透视表能做什么.................... 2 1.1.3 什么时候适合使用数据透视表..... 3 1.2　创建数据透视表........................................ 3 1.2.1 常规创建数据透视表.................... 3 1.2.2 指定数据透视表的放置位置......... 6 1.2.3 设置数据透视表布局结构............ 7 1.2.4 推迟布局更新.............................. 9 1.2.5 更改数据透视表数据源................ 9 1.3　共享数据透视表......................................10 1.3.1 在局域网中共享数据透视表....... 10 1.3.2 在OneDrive中共享数据 透视表....................................... 13 1.4　数据透视表的数据源有这些讲究...........14 1.5　数据透视表的组成结构...........................16 1.6　数据透视表的常用术语...........................17 1.7　数据透视表缓存......................................19 1.7.1 共享或不共享数据透视表 缓存.......................................... 19 1.7.2 查看共享缓存个数..................... 22 1.8　数据透视表工具选项卡...........................24 给你支招 如何限制数据透视表数据源的编辑........ 25 创建共享桥梁........................................ 27 如何获取Office账号............................... 28 巧将共享缓存变为不共享缓存............... 29 Chapter 02　更改数据透视表布局 2.1　从整体上更改数据透视表布局...............32 2.1.1 改变数据透视表的报表布局....... 32 2.1.2 分类汇总的显示方式.................. 33 2.1.3 在每项后插入空行..................... 33 2.1.4 总计的禁用与启用..................... 34 2.2　从字段上更改数据透视表布局...............36 2.2.1 移动字段.................................... 36 2.2.2 重命名字段................................ 37 2.2.3 删除字段.................................... 38 2.2.4 隐藏字段标题............................ 39 2.2.5 活动字段的折叠与展开.............. 40 2.2.6 创建复合字段............................ 41 2.3　使用透视表筛选区域..............................42 2.3.1 添加筛选区域............................ 42 2.3.2 改变多个筛选字段排列.............. 44 2.3.3 将筛选结果保存在表中.............. 44 给你支招 拖动字段更改布局................................. 45 找回数据透视表的数据源...................... 47 Chapter 03 　刷新数据透视表数据 3.1　手动刷新数据透视表..............................50 3.1.1　通过快捷菜单命令更新.............. 50 3.1.2 通过功能按钮更新..................... 51 3.2　自动刷新数据透视表..............................51 3.2.1 打开文件时自动刷新.................. 52 3.2.2 刷新外部数据透视表.................. 52 3.3　刷新数据透视表需留意的事项...............54 3.3.1 庞大数据刷新忙......................... 54 3.3.2 已删除数据项依旧存在.............. 55 3.4　刷新数据时保持数据列宽不变...............55 给你支招 轻松禁止数据透视表所有的刷新........... 58 还原连接数据源.................................... 59 Chapter 04 设置数据透视表格式 4.1 　手动设置数据透视表格式......................62 4.1.1 通过选项卡设置格式.................. 62 4.1.2 通过对话框设置格式.................. 63 4.2　自动套用数据透视表样式.......................65 4.2.1 套用数据透视表样式.................. 65 4.2.2 新建数据透视表样式.................. 66 4.3　处理数据透视表单元格...........................69 4.3.1 处理透视表中的空白数据.......... 69 4.3.2　设置错误值的显示方式.............. 70 4.4　按条件设置数据透视表规则...................72 4.4.1 使用数据条展示数据大小.......... 72 4.4.2 使用图标集合展示数据状态....... 73 4.4.3 使用颜色划分数据范围.............. 74 给你支招 让数据透视表中只有数据条.................. 76 在数据透视表中轻松设置同类数据 格式...................................................... 77 为指定数据添加单位............................. 78 Chapter 05　对数据透视表数据进行排序和筛选 5.1　对数据透视表数据进行排序...................80 5.1.1 通过菜单和功能区进行排序....... 80 5.1.2 通过字段筛选器进行排序.......... 80 5.1.3 按笔画进行排序......................... 81 5.1.4 自定义排序................................ 82 5.1.5 按值进行排序............................ 83 5.2　对数据透视表数据进行筛选...................84 5.2.1 对标签进行筛选......................... 84 5.2.2 对数据值进行筛选..................... 87 5.3　使用切片器筛选数据透视表数据...........89 5.3.1 创建切片器................................ 89 5.3.2 美化切片器................................ 90 5.3.3 使用切片器................................ 91 给你支招 在添加数据字段的同时进行排序........... 93 解决切片器样式不够用的问题............... 94 Chapter 06 数据透视表的项目组合 6.1　组合透视表数据项..................................96 6.1.1　手动组合透视表数据项.............. 96 6.1.2　自动组合透视表数据项.............. 97 6.1.3　取消组合.................................. 100 6.2　使用函数辅助透视表项目组合.............102 6.2.1　根据数据特点进行分组............ 102 6.2.2　按不等距步长组合数值项........ 104 给你支招 巧以指定数据进行分组........................ 105 对数据透视表进行保护........................ 106 Chapter 07 在数据透视表中添加计算指标 7.1　更改汇总方式........................................108 7.1.1　更改值汇总依据....................... 108 7.1.2　对同一字段使用多种汇总 方式......................................... 109 7.2　自定义透视表数据值的显示方式.........112 7.2.1　百分比的值显示....................... 112 7.2.2　差异的值显示.......................... 115 7.2.3　差异百分比的值显示................ 115 7.2.4　按某字段汇总.......................... 116 7.2.5　按排序方式显示....................... 118 7.3　使用计算字段........................................121 7.3.1　创建计算字段.......................... 121 7.3.2　删除计算字段.......................... 122 7.3.3　修改计算字段.......................... 123 7.4　使用计算项............................................124 7.4.1　创建计算项.............................. 124 7.4.2　改变计算项求解次序................ 126 给你支招 指定基本项的百分比........................... 127 指定连续日期进度百分比.................... 128 删除计算项.......................................... 129 Chapter 08 创建动态数据透视表 8.1　通过名称创建动态数据透视表.............132 8.1.1　定义指定单元格名称................ 132 8.1.2　批量定义单元格名称................ 133 8.1.3　定义动态单元格名称................ 134 8.1.4　根据名称创建动态数据透 视表......................................... 136 8.2　通过列表创建动态数据透视表.............137 8.2.1　创建列表.................................. 137 8.2.2　通过列表创建动态报表............ 140 给你支招 修改单元格名称.................................. 141 快速对指定的列表区域创建数据 透视表................................................. 142 Chapter 09 　创建复合范围的数据透视表 9.1　创建多重合并计算数据区域透视表.....144 9.1.1　创建单页字段的数据透视表..... 144 9.1.2　创建双页字段的数据透视表..... 147 9.2　创建动态多重合并计算区域透视表.....149 9.2.1　使用定义名称创建数据透 视表......................................... 149 9.2.2　使用列表创建数据透视表........ 153 给你支招 其他打开数据透视表向导的途径......... 156 轻松将外部数据资源作为多重合并 区域数据............................................. 157 Chapter 10　透视分析外部数据 10.1　透视分析网站数据..............................160 10.2　透视分析外部Access数据..................162 10.2.1　导入外部的Access数据 并分析................................... 162 10.2.2　连接外部的Access数据 并分析................................... 164 10.2.3　用向导获取Access数据 并分析................................... 165 10.3　透视分析外部TXT数据.......................168 给你支招 如何将Access文件直接转换为 Excel文件............................................ 170 如何巧妙去除字段中的文字 “求和项”.......................................... 171 Chapter 11 　使用数据透视图 11.1　了解数据透视图..................................174 11.2　创建数据透视图..................................177 11.2.1　根据数据源创建数据透 视图....................................... 177 11.2.2　在数据透视表上创建数据 透视图................................... 178 11.3　移动数据透视图..................................179 11.3.1　在当前工作表位置移动.......... 179 11.3.2　跨工作表位置移动................. 179 11.4　设置数据透视图..................................181 11.4.1　应用图表样式........................ 182 11.4.2　更改透视图数据系列颜色...... 182 11.4.3　应用主题................................ 183 11.4.4　设置图表数据格式................. 184 11.4.5　应用图表布局样式................. 186 11.4.6　更改数据系列类型................. 187 11.4.7　添加次坐标轴........................ 189 11.4.8　添加数据标签........................ 190 11.4.9　添加和设置趋势线................. 192 11.4.10 添加底纹............................... 194 11.4.11 为数据透视图添加立体 效果...................................... 197 11.5　将数据透视图变成静态图表...............197 11.5.1　处理数据透视表来实现.......... 198 11.5.2　处理数据透视图来实现.......... 199 给你支招 巧妙浏览到保存的主题........................ 199 自定义数据透视图的形状样式............. 200 多重数据透视图的创建........................ 201 Chapter 12　宏与VBA 12.1　认识Excel中的宏与VBA.....................204 12.1.1 宏.......................................... 204 12.1.2　VBA....................................... 204 12.2　了解数据透视表对象..........................205 12.2.1 对象浏览器............................ 206 12.2.2 录制宏................................... 206 12.2.3 执行宏................................... 208 12.3　VBA编程环境......................................209 12.4　通过VBA代码创建数据透视表...........210 12.4.1 通过缓存创建数据透视表...... 210 12.4.2 调用向导创建数据透视表...... 215 12.5　数据透视表自动化..............................216 12.5.1 对字段进行操作..................... 217 12.5.2 设置数据透视表的布局 方式....................................... 218 12.5.3 添加计算字段........................ 219 12.5.4 添加汇总字段........................ 221 12.5.5 设置字段显示方式................. 222 12.6　编写代码保护数据透视表...................223 12.6.1 隐藏字段下拉列表的使用...... 223 12.6.2 禁止操作数据透视表字段...... 224 12.6.3 停用显示数据明细功能.......... 224 12.7　自动创建数据透视图..........................225 12.7.1 数据透视图的常用 属性和方法............................ 225 12.7.2 编写代码创建数据透视图...... 226 12.7.3 两种图表的相互转换.............. 228 给你支招 更改宏快捷键...................................... 229 添加受信任的位置............................... 230 为宏或VBA代码添加数字证书............. 231 Chapter 13 　打印数据透视表 13.1　常规打印数据透视表..........................234 13.1.1　设置打印表头........................ 234 13.1.2　设置打印标题........................ 235 13.1.3　按分类项目打印数据透 视表....................................... 237 13.1.4　按筛选字段数据分页打印...... 238 13.1.5　打印报表中指定范围的 数据....................................... 239 13.1.6　让报表字段全部显示在 同页中.................................... 240 13.2　通过VBA快速打印数据透视表...........243 13.2.1　按项目分页打印..................... 243 13.2.2　仅打印所有的图表................. 244 给你支招 轻松解决电脑没有添加打印机的 烦恼.................................................... 245 自定义纸张大小.................................. 247 Chapter 14 　费用使用情况分析 14.1　案例制作效果和思路..........................250 14.2　创建动态数据透视表..........................251 14.2.1　定义单元格名称..................... 251 14.2.2　创建动态的数据透视表.......... 252 14.2.3　添加计算字段........................ 253 14.2.4　更改标签字段名称................. 254 14.3　设置数据透视表显示样式...................254 14.3.1　更改报表样式和布局.............. 254 14.3.2　使用颜色来表示超支严重 程度...................................... 256 14.3.3　取消未超支数据的填充色...... 257 14.4　保护数据透视表..................................258 14.4.1　使用VBA禁止字段操作.......... 258 14.4.2　保护数据源结构不被更改...... 260 14.5　共享和备份数据透视表.......................262 14.5.1　共享数据透视表..................... 262 14.5.2　上传OneDrive备份共享......... 263 14.6　案例制作总结和答疑..........................264 给你支招 避免保护工作表无法正常使用............. 264 程序提示超出下界或越界.................... 264 再次编辑VBA代码............................... 265 Chapter 15　年度销售数据分析 15.1　案例制作效果和思路..........................268 15.2　创建静态数据透视表..........................269 15.2.1　创建数据透视表..................... 269 15.2.2　自定义数据透视表样式.......... 271 15.2.3　设置数据透视表字体格式...... 274 15.2.4　设置数据透视表列宽.............. 275 15.3　对数据透视表进行管理.......................275 15.3.1　拖动字段进行排序................. 275 15.3.2　自定义客户数据顺序.............. 276 15.3.3　通过图标展示数据状态.......... 279 15.3.4　添加计算项............................ 280 15.4　使用切片器和数据透视图...................281 15.4.1　使用切片器............................ 281 15.4.2　使用数据透视图..................... 282 15.5　案例制作总结和答疑..........................283 给你支招 轻松变更数据透视表的默认样式......... 283 轻松仿作数据透视表样式.................... 284 Chapter 16　年度考勤分析 16.1　案例制作效果和思路..........................286 16.2　创建多重合并区域数据透视表...........287 16.2.1　导入Access考勤数据 ............ 287 16.2.2　创建多区域数据透视表 ......... 289 16.2.3　设置主题样式........................ 292 16.3　透视分析员工个体出勤状况...............293 16.3.1　通过缓存创建个体报表.......... 293 16.3.2　制作辅助分析工具................. 294 16.3.3　隐藏行标签............................ 298 16.4　透视分析部门出勤状况.......................299 16.4.1　通过缓存创建部门报表.......... 299 16.4.2　导入部门配置数据................. 299 16.5　案例制作总结和答疑..........................301 给你支招 这样也可以将列表转换为区域............. 301 缓存数据也得挑一挑........................... 302 数据系列名称描述有要求.................... 303