Excel数据透视表应用大全 for Excel 365 & Excel 2019

第 1 章 创建数据透视表 ·· 1 1.1 什么是数据透视表 ··· 1 1.2 数据透视表的数据源 1 1.3 自己动手创建**个数据透视表 ··· 1 1.4 使用推荐的数据透视表 4 1.5 数据透视表用途 ·· 6 1.6 何时使用数据透视表分析数据 ·· 6 1.7 数据透视表的结构 ··· 6 1.8 数据透视表字段列表 7 1.8.1 隐藏和显示【数据透视表字段】列表 ·7 1.8.2 在【数据透视表字段】列表中显示更多的字段 ··9 1.8.3 【数据透视表字段列表】对话框中的排序与筛选 10 1.8.4 更改【选择要添加到报表的字段】中字段的显示顺序 ·10 1.8.5 在【数据透视表字段】列表中快速搜索 ···12 1.9 数据透视表中的术语 ·· 12 1.10 【数据透视表工具】··· 13 1.10.1 【数据透视表分析】选项卡的主要 功能 ··13 1.10.2 【设计】选项卡的主要功能15 1.11 创建动态数据透视表 15 1.11.1 定义名称法创建动态数据透视表 ···16 1.11.2 使用“表格”功能创建动态数据透视表 ···17 1.12 创建复合范围的数据透视表 ·· 18 1.12.1 利用多重合并计算数据区域将二维数据列表转换为一维数据列表 ··18 1.13 模型数据透视表 ··· 22 1.13.1 将数据添加到数据模型创建数据透视表 ··22 1.14 普通数据透视表与利用数据模型创建的数据透视表的区别 · 23 1.15 模型数据透视表的局限性 · 26 1.16 将普通数据透视表转化为模型数据透视表 ··· 26 第 2 章 整理好Excel数据源 ·· 27 2.1 数据管理规范 ··· 27 2.1.1 Excel工作簿名称中不能包含非法字符 ···27 2.1.2 数据源不能包含空白数据行或数据列 ·28 2.1.3 数据源字段名称不能重复 28 2.1.4 数据源不能包含合并单元格 29 2.1.5 数据源不能包含多层表头 29 2.1.6 数据源不能包含小计行或总计行 ·29 2.1.7 数据源的数据格式要统一和规范 ·29 2.1.8 数据源不要拆分到多个工作表中 ·29 2.1.9 数据源不要拆分到多个工作簿中 ·29 2.2 对不规范数据的整理技巧 ··· 30 2.2.1 对合并单元格的处理 ···30 2.2.2 对文本型数字的处理 ···31 2.2.3 快速删除重复记录 ···32 2.2.4 对不规范日期的处理 ···33 2.2.5 二维表转换为一维表的技巧 35 2.2.6 将数据源按分隔符分列为多字段数据区域 37 2.2.7 网页复制数据的整理 ···39 第 3 章 改变数据透视表的布局 ·· 45 3.1 改变数据透视表的整体布局 45 3.2 设置数据透视表的默认布局 47 3.3 数据透视表筛选区域的使用 49 3.3.1 显示报表筛选字段的多个数据项 ·49 3.3.2 水平并排显示报表筛选字段 50 3.3.3 垂直并排显示报表筛选字段 52 3.3.4 显示报表筛选页···52 3.4 启用经典数据透视表布局 ··· 54 3.5 整理数据透视表字段 ·· 55 3.5.1 重命名字段 ·55 3.5.2 整理复合字段 ·57 3.5.3 删除字段 ·58 3.5.4 隐藏字段标题 ·59 3.5.5 活动字段的折叠与展开 ···60 3.6 改变数据透视表的报告格式 61 3.6.1 改变数据透视表的报表布局 61 3.6.2 分类汇总的显示方式 ···64 3.6.3 在每项后插入空行 ···66 3.6.4 总计的禁用与启用 ···67 3.6.5 合并且居中排列带标签的单元格 ·69 3.7 清除已删除数据的标题项 ··· 69 3.8 数据透视表的复制和移动 ··· 71 3.8.1 复制数据透视表···71 3.8.2 移动数据透视表···72 3.8.3 删除数据透视表···74 3.9 影子数据透视表 74 3.10 获取数据透视表的数据源信息 ·· 77 3.10.1 显示数据透视表数据源的所有信息 ···77 3.10.2 显示数据透视表某个项目的明细数据 ···78 3.10.3 禁止显示数据源的明细数据 ··78 第 4 章 刷新数据透视表 79 4.1 手动刷新数据透视表 ·· 79 4.2 打开文件时自动刷新 ·· 80 4.3 刷新链接在一起的数据透视表 81 4.4 刷新引用外部数据源的数据透视表 ·· 81 4.5 定时刷新 ··· 82 4.6 使用VBA代码设置自动刷新 ··· 83 4.7 全部刷新数据透视表 ·· 85 4.8 共享数据透视表缓存 ·· 86 4.9 延迟布局更新 ··· 87 4.10 数据透视表的刷新注意事项 ·· 88 4.10.1 海量数据源限制数据透视表的刷新速度 ··88 4.10.2 数据透视表刷新数据丢失 ··88 第 5 章 数据透视表的格式设置 ·· 90 5.1 设置数据透视表的格式 ·· 90 5.1.1 自动套用数据透视表样式 90 5.1.2 自定义数据透视表样式 ···93 5.1.3 自定义数据透视表样式的复制 97 5.1.4 清除数据透视表中已经应用的样式 ·98 5.1.5 数据透视表刷新后如何保持调整好的列宽 99 5.1.6 批量设置数据透视表中某类项目的格式 ··100 5.1.7 修改数据透视表数据的数字格式 ···102 5.1.8 自定义数字格式·107 5.1.9 设置错误值的显示方式 ··109 5.1.10 处理数据透视表中的空白数据项 ·110 5.2 数据透视表与条件格式 112 5.2.1 突出显示数据透视表中的特定数据 ···112 5.2.2 数据透视表与数据条 ·114 5.2.3 数据透视表与图标集 ·115 5.2.4 数据透视表与色阶 ·116 5.2.5 修改数据透视表中应用的条件格式 ···117 第 6 章 在数据透视表中排序和筛选 · 120 6.1 数据透视表排序 ··· 120 6.1.1 默认排序 ···120 6.1.2 手动排序 ···120 6.1.3 自动排序 ···122 6.1.4 使用其他排序选项 ·124 6.1.5 对【筛选区域】的字段排序 133 6.2 数据透视表筛选 ··· 135 6.2.1 利用字段筛选下拉列表进行筛选 ···135 6.2.2 利用标签筛选命令进行筛选 ··136 6.2.3 利用值筛选命令进行筛选 ··137 6.2.4 使用日期筛选 ···138 6.2.5 使用字段筛选列表的搜索框筛选 ···139 6.2.6 在搜索文本框中进行多关键词搜索 ···140 6.2.7 同时对标签和值筛选 ·141 6.2.8 筛选时保持总计值不变 ··142 6.2.9 自动筛选 ···143 6.2.10 自动筛选与值筛选的冲突 144 6.2.11 清除数据透视表中的筛选 147 第 7 章 数据透视表的切片器 ··· 149 7.1 切片器结构 · 150 7.2 在数据透视表中插入切片器 ·· 150 7.3 筛选多个字段项 ··· 152 7.3.1 共享切片器实现多个数据透视表联动 ···152 7.3.2 清除切片器的筛选器 ·154 7.3.3 在加密的工作表中使用切片器 ···154 7.4 更改切片器的前后显示顺序 ·· 155 7.5 切片器字段项排序 ··· 156 7.5.1 对切片器内的字段项进行升序或降序排列 ··157 7.5.2 对切片器内的字段项进行自定义排序 ···158 7.5.3 不显示从数据源删除的项目 ··158 7.5.4 隐藏没有数据的项 ·160 7.6 设置切片器样式 ··· 161 7.6.1 多列显示切片器内的字段项 ··161 7.6.2 更改切片器内字段项的大小 ··161 7.6.3 更改切片器的大小 ·162 7.6.4 切片器自动套用格式 ·163 7.6.5 设置切片器的字体格式 ··163 7.6.6 格式切片器 ···165 7.7 更改切片器的标题 ··· 166 7.8 隐藏切片器 · 167 7.8.1 隐藏切片器的标题 ·167 7.8.2 隐藏切片器 ···167 7.9 删除切片器 · 168 第 8 章 数据透视表的日程表 ··· 169 8.1 插入日程表 · 169 8.2 利用日程表按季度进行筛选数据 ··· 170 8.3 为数据透视表插入两个时间级别的日程表 ··· 171 8.4 日程表的格式化 ··· 173 8.5 每个字段允许多个筛选 173 8.6 共享日程表实现多个数据透视表联动 175 8.7 日程表与创建组的区别 176 第 9 章 数据透视表的项目组合 177 9.1 手动组合数据透视表内的数据项 ··· 177 9.2 自动组合数据透视表内的数值型数据项 · 179 9.2.1 按等距步长组合数值型数据项 ···179 9.2.2 组合数据透视表内的日期型数据项 ···180 9.3 取消项目的自动组合 182 9.3.1 取消项目的自动组合 ·182 9.3.2 取消手动组合 ···183 9.3.3 启用与禁用自动日期组合 ··184 9.4 在项目组合中运用函数辅助处理 ··· 184 9.4.1 按不等距长自动组合数值型数据项 ···184 9.4.2 按条件组合日期型数据项 ··185 9.5 影响自动组合的因素 186 第 10 章 在数据透视表中执行计算 ·· 189 10.1 对同一字段使用多种汇总方式 189 10.2 更改数据透视表默认的字段汇总方式 ·· 192 10.3 非重复计数的值汇总方式 ··· 195 10.4 自定义数据透视表的值显示方式 ·· 198 10.4.1 数据透视表自定义值显示方式描述 ·198 10.4.2 “总计的百分比”值显示方式 ···198 10.4.3 “列汇总的百分比”值显示方式 200 10.4.4 “行汇总的百分比”值显示方式 201 10.4.5 “百分比”值显示方式 ···202 10.4.6 “父行汇总的百分比”数据显示方式 204 10.4.7 “父级汇总的百分比”数据显示方式 205 10.4.8 “差异”值显示方式 ··207 10.4.9 “差异百分比”值显示方式 ···209 10.4.10 “按某一字段汇总”数据显示方式 ··210 10.4.11 “升序排列”值显示方式 ·211 10.4.12 “指数”值显示方式213 10.4.13 修改和删除自定义数据显示方式 ···214 10.5 在数据透视表中使用计算字段和计算项 ··· 214 10.5.1 创建计算字段 ··214 10.5.2 修改数据透视表中的计算字段 ·222 10.5.3 删除数据透视表中的计算字段 ·223 10.5.4 计算字段的局限性···224 10.5.5 创建计算项 ·224 10.5.6 改变计算项的求解次序 233 10.5.7 列示数据透视表计算字段和计算项的公式 235 10.5.8 运用OLAP工具将数据透视表转换为公式 235 10.6 数据透视表函数应用 ·· 238 10.6.1 快速生成数据透视表函数公式 ·238 10.6.2 从多个数据透视表中获取数据 ·240 10.6.3 应用数据透视表函数根据关键字汇总 242 第 11 章 利用多样的数据源创建数据透视表 · 244 11.1 使用文本数据源创建数据透视表 ·· 244 11.2 使用Access数据库创建数据透视表 250 11.3 使用数据库创建数据透视表 251 11.4 使用SQL Server Analysis Services OLAP创建数据透视表 · 254 11.4.1 OLAP多维数据库简介 ·254 11.4.2 基于OLAP创建数据透视表·256 11.4.3 在数据透视表中进行数据钻取 ·259 11.5 使用OData数据源创建数据透视表 ·· 260 11.6 使用“Microsoft Query”和导入外部数据创建透视表 ··· 263 11.6.1 汇总同一工作簿中的多个数据列表 ·264 11.6.2 Microsoft Query创建参数查询数据透视表 ·268 11.7 运用SQL查询技术创建数据透视表 · 271 11.7.1 导入不重复记录创建数据透视表 ·271 11.7.2 汇总关联数据列表中符合关联条件的指定字段部分记录···272 第 12 章 利用Power Query进行数据清洗 · 274 12.1 利用Power Query填充合并单元格 ·· 274 12.2 利用Power Query删除重复记录 · 277 12.3 利用Power Query导入并处理文本文件 ··· 278 12.4 利用Power Query分列、合并数据 ·· 279 12.5 利用Power Query逆透视二维表 · 283 12.6 利用Power Query透视一维数据 · 286 12.7 利用Power Query清洗数据 288 第 13 章 用Power Pivot建立数据模型 · 293 13.1 模型数据透视表 · 293 13.1.1 利用模型数据透视表进行多表关联 ·293 13.2 Power Pivot与数据透视表·· 297 13.2.1 加载Microsoft Power Pivot for Excel ···297 13.2.2 为Power Pivot准备数据 ··298 13.2.3 利用Power Pivot创建数据透视表 ···301 13.2.4 利用Power Pivot创建数据透视图 ···303 13.3 在Power Pivot中使用DAX语言 ·· 304 13.3.1 使用DAX创建计算列 ···304 13.3.2 使用DAX创建度量值 ···308 13.3.3 常用DAX函数应用 ··311 13.4 在Power Pivot中使用层次结构 ··· 346 13.5 创建KPI关键绩效指标 ··· 348 13.6 使用链接回表方式创建计算表 350 第 14 章 利用Power Map创建 3D地图可视化数据 ··· 356 第 15 章 Power BI Desktop入门 · 357 15.1 安装Power BI Desktop应用程序 357 15.2 向Power BI Desktop导入数据 ··· 358 15.2.1 使用Power BI Desktop 获取网页上的汇率数据 ·358 15.2.2 导入包含Power Query和Power Pivot的工作簿 ·361 15.3 在Power BI中创建关系 ·· 364 15.3.1 在模型视图中创建表之间的关系 ·364 15.3.2 使用【管理关系】功能来创建和管理关系 365 15.3.3 表与表之间的关系类型和作用 ·365 15.4 在Power BI中创建可视化报告 ··· 367 15.4.1 报表视图的结构与功能 367 15.4.2 在报告中创建可视化图表 367 15.5 在Power BI中设置交互层次结构 370 15.5.1 可视化之间的交互效果 370 15.5.2 调整可视化图表的交互效果 370 15.6 在Power BI中导入自定义可视化图表 · 372 15.6.1 使用内置商店导入获取自定义可视化 效果 372 15.6.2 导入自定义可视化文件 373 15.7 Power BI报告的常见分享形式 ··· 374 15.7.1 将Power BI 报告导出为PDF分享 ···374 15.7.2 使用Power BI 云服务分享报告 375 第 16 章 数据透视表与VBA 377 16.1 数据透视表对象模型 ·· 377 16.2 在Excel功能区中显示【开发工具】选项卡 · 379 16.3 如何快速获得创建数据透视表的代码 ·· 380 16.4 自动生成数据透视表 ·· 386 16.4.1 使用PivotTableWizard方法创建数据透视表 ·386 16.4.2 利用PivotCache对象创建数据透视表 ·389 16.5 在代码中引用数据透视表 ··· 392 16.6 更改数据透视表中默认的字段汇总方式 ··· 394 16.7 调整值字段的位置 · 398 16.8 清理数据透视表字段下拉列表 400 16.9 利用数据透视表快速汇总多个工作簿 ·· 403 16.10 数据透视表缓存 ··· 406 16.10.1 显示数据透视表的缓存索引和内存使用量 ···406 16.10.2 合并数据透视表缓存 ·408 16.11 保护数据透视表 ··· 409 16.11.1 限制数据透视表字段的下拉选择 ···409 16.11.2 限制更改数据透视表布局 ··410 16.11.3 禁用数据透视表的显示明细数据功能 ···412 16.12 选定工作表中的数据透视表区域 414 16.13 多个数据透视表联动 415 16.14 快速打印据数据透视表 · 418 16.14.1 快速分项打印单筛选字段数据透视表 ···418 16.14.2 快速分项打印多筛选字段数据透视表 ···419 16.15 快速创建PowerPivot和添加切片器 ··· 422 第 17 章 智能数据分析可视化看板 ·· 427 17.1 销售渠道分析看板 · 427 17.2 利用数据透视图进行销售综合分析 ·· 434 17.3 利用PowerPivot for Excel综合分析数据 · 439 17.4 人事数据看板 ··· 446 17.5 指定店铺指定商品促销达成跟进模板 ·· 452 17.6 店铺销售分析看板 · 459 17.7 公司预算达成率跟踪看板 ··· 466 17.8 切片器控制动态TOP X零售分析 · 473 第 18 章 数据透视表常见问题答疑解惑 · 481 18.1 为什么创建数据透视表时提示“数据透视表字段名无效”？ ··481 18.2 为什么在早期版本的Excl中无法正常显示 Excel 2019 版本创建的数据透视表？ ·· 481 18.3 为什么Excel 2019 中的切片器、日程表等 功能不可用，按钮呈灰色状态？ ·· 482 18.4 如何对数据透视表值区域的数据实现筛选？ ·· 483 18.5 为什么刷新数据透视表时会提示“数据 透视表不能覆盖另一个数据透视表”？ 485 18.6 如何将值区域中的数据汇总项目由纵向变为横向显示？ ·485 18.7 如何像引用普通表格数据一样引用数据透视表中的数据？ ···486 18.8 如何像在Excel 2003 版本中一样使用 数据透视表的字段拖曳功能？ 487 18.9 如何使数据透视表中的月份字段显示1 月~12 月的顺序 ·488 18.10 为什么【计算项】功能命令为灰色不可用状态？ 489 18.11 为什么批量选中分类汇总项时失效？ 490 18.12 如何利用现有数据透视表得到原始数据源？ 490 18.13 如何在数据透视表中插入批注？ ··· 491 18.14 为什么在数据透视表中无法显示插入的图片批注？ · 491 18.15 如何不显示数据透视表中的零值？ 492 18.16 同一数据源创建的多个数据透视表， 一个应用组合，其他也同步应用，如何解决？ 493 18.17 如何快速在普通数据透视表中应用非重复计数的值汇总方式？· 494 18.18 如何在数据透视表的左侧显示列字段的分类汇总？ · 496 18.19 如何解决数据透视表添加计算字段后总计值出现的错误？ 497 18.20 如何对数据透视表实施快速钻取？ 498 第 19 章 数据透视表打印技术 ·· 500 19.1 设置数据透视表的打印标题 500 19.1.1 利用数据透视表选项设置打印表头 ·500 19.1.2 “在每一打印页上重复行标签”的应用 502 19.1.3 利用页面设置指定打印标题 503 19.2 为数据透视表的每一分类项目分页打印 ··· 504 19.3 根据报表筛选字段数据项快速分页打印 ··· 505 附录 ··· 507 附录A Excel常用SQL语句解释 · 507 A.1 SELECT查询 ···507 A.2 联合查询 ··513 A.3 多表查询 ··514 A.4 内部联接 ··515 A.5 左外部联接和右外部联接 ·516 A.6 子查询 ·516 A.7 常用函数 ··517 附录B 高效办公**工具——Excel易用宝 521