Excel数据透视表视频课程(基础+进阶)
41课，15小时视频，含练习题文件
课程内容：透视表+函数+条件格式+动态图+解决问题思路
128元购买 加微信15910848920
课程目录(基础+进阶)：
一.基础：
01.快速入门
透视表纯小白需要看。有点基础的，请从第2课开始学。
02.基础中的基础
必学要知道的,必学！
00:00 数据源与透视表
00:44 先规划再操作
02:53 透视表+函数 实现1+1>3
04:04 柱饼线图也有数据源
05:42 现有一张合适的表基石
06:31 透视表有功能边界
07:35 搞定数据源
08:43 兼容性
03.刷新数据与Table数据源
首选Table做数据源，数据源变更后需刷新透视表才同步更新
00:00 增改删均需刷新
02:02 区域源的弊端
03:54 Table表做源
09:17 相邻透视表要有足够空间
10:52 不能做另一个透视表的数据源
11:28 一个数据源可生成多个透视表
11:49 全部刷新和只刷新指定数据源
04.数据规范
很多时候规范的数据源并不适合人看
00:00 正确的表头
02:16 命名是良好工作习惯
03:53 源中不能出现的各种情况
10:43 单字段中不能有复合数据
12:00 一行数据不能包含多行数据
13:02 数据源应该是一维表
05.值汇总依据
1 值区域只能显示数字。2 数据源中的数字应该是数字格式。3 日期或时间和数字一样是数值。4 0不等于空单元格。5 合理使用空单元格。6 解决空白。7 透视表字段名称，透视表名称。8 调整数值格式，调整字段顺序，禁止自动调整列。9 非重复计数与WPS不兼容
00:00 值汇总依据 操作1
13:46 排查错误
15:25 值字段设置16:24 值汇总依据 操作2
29:58 新人容易出错的地方
47:06 乘积.良品率.同时发生概率
06.列区域.行区域显示值.筛选.切片
1 字段与项。2 多项字段不适合放入列区域。3 筛选和切片相互联动。4 隐藏切片中无法选择的项名称。5 修改切片名称
00:00 将值名称放到行区域
01:31 90度转置透视表
02:29 项目过多字段不适合在列区域
05:17 什么是字段
06:02 什么是项
07:19 筛选 切片
10:31 筛选和切片的区别
11:58 隐藏切片中无数据的项
07.组合
1 创建删除年季月、时分秒。2 有月季就有年。3 多透视表时不建议使用。4 月份排序异常，数据源增加年月季时分秒。5 周。6 禁用自动组合。7 复杂数字区间
00:00 查看透视表的数据源
00:24 创建年季月
01:52 创建月季时需要年
02:48 时分秒
03:44 多透视表时组合不好用
10:09 周
11:50 开启禁用自动组合
12:28 区间
13:41 销售数量金额区间
17:42 手工调整项顺序
08.值显示方式
1 百分比、占比，本质是分子与分母的灵活调整。2 各种类型的值汇总依据都可以设置值显示方式。3 一个切片控制多透视表(报表连接)，能跨工作表，不能跨数据源。4 切片或筛选后，按筛选后状态重新计算百分比。5 父级汇总的百分比：行区域至少需要3个字段。6 透视表大部分操作可视化所见即所得，尝试操作，观察结果。7 透视表就把以不同视角观看同样数据，不同职位的人关心点不一样视角不一样。8 在计算字段时是按无计算的值显示方式进行计算
00:00 总计的百分比
01:52 值汇总依据可值显示方式
02:35 值字段设置查看值详细信息
03:37 进一步理解总计百分比
06:02 列汇总百分比
08:25 行汇总百分比
10:03 观察视角
12:10 父行汇总百分比
15:18 错误的父列汇总的百分比
16:32 父级汇总百分比
19:11 正确的父列汇总百分比
20:25 与设置单元格格式的区别
23:27 切片或筛选影响值值显示方式
26:32 切片联动多个透视表
09.计算字段
1 乘法(单价×数量)需在数据源用函数完成，除法(毛利率)需通过计算字段完成。2 加减法计算字段或数据源均可。3 计算字段中的公式可以加减乘除固定的数字。4 源字段中只有数字可参与计算字段。5 计算字段不能设置值汇总依据，只能是求和。6 计算字段可以再次参与计算字段。7 计算字段不能添加到筛选、切片、行区域。8 值显示方式对计算字段不起作用，但计算字段可设值显示方式。9 指定错误值的显示方式。10 计算字段改名后，字段列表不同步更新。11 绝对平均单价与加权平均单价。12 应考虑计算字段是否设置值汇总依据
00:00 概述与闭坑
02:33 规划
06:44 创建计算字段
12:47 为什么乘法结果错误
14:40 要点总结
18:39 加权平均单价
10.美化.布局.样式.数据透视表选项
1 美化的目标不是“好看”，目的是“突出重点、方便观看”。2 跨工作表移动数据透视表。3 冻结行列。4 恢复样式。5 如黑白打印，黑白灰配色所见即所得。6 筛选区域水平并排。7 日程表(WPS无)。8 压缩布局时缩进。9 表格布局时合并单元格。10 刷新后保留单元格格式。11 字段列表顺序。12 组合列。13 先设置好一个数据透视表，然后复制。14 显示错误不是坏事，不怕出错，就怕都不知道错了。15 预设布局(WPS无)，ExcelWPS颜色等不兼容
00:00 概述
01:28 移动透视表
03:12 锁定行列的目的
04:21 组合列
05:15 基础调整
07:52 布局调整
10:59 隐藏悬停值区域的上下文提示
11:29 隐藏透蓝线经典透视表布局
12:22 显示值行
12:37 底色线条
15:27 合并单元格
17:30 人工调整优先级高于预设样式
18:17 删除人工调整的颜色
20:33 筛选区域多列显示筛选
21:09 每列报表筛选字段数
21:43 切片样式
22:32 日程表
24:06 切片列数
24:58 显示成传统表格
25:45 压缩样式
26:19 修改压缩布局的缩进
27:19 修改字段列表中字段排序方式
27:34 搜索字段列表
28:12 字段列表移动和改变布局
28:49 延迟布局
29:11 模板技巧
29:54 默认布局
32:01 复制样式到其它透视表
33:21 计算错误时显示指定内容
35:15 ExcelWPS样式不兼容
11.数据透视图
1 需明确知道图中的XY轴，线、柱、饼等分别是什么数值、字段含义，才能做图。2 图的目的是在表的基础上再次突出重点，数据透视表的数值不要太多(一般不超过3个)，轴或系列的项目数也不能过多。3 图的源是表，图的“源表”可以是数据透视表或区域表，数据透视表生成的图叫“数据透视图”，所以数据透视图无法独立存在。4 有一些图，无法用数据透视表生成，需要区域表。5 数据透视图相比普通图的优势是切片联动，和灵活调整源表。6 数据透视表+函数+图表设置+Excel功能设置=复杂仪表盘、数据看板。7 10分钟做表，20分钟做图，生成理想的图比较耗时，突出重点是核心，好看不重要，色系统一即可。8 某些炫酷的图仅限高版本Excel或付费WPS，不兼容低版本和非付费。9 一般情况下时间维度比较：折线或柱；同类比较：柱；占比：饼。10 组合图解决超大与超小数值对比。11 细节操作详见视频
00:00 图的基础逻辑与规划
04:12 案例图的功能介绍
07:41 柱线饼适合表现的数据
09:29 区分透视图和普通图
10:34 创建第1张柱图
18:12 创建切片
19:44 创建饼图
22:57 创建第2张柱图
24:05 切片联动各透视图
25:14 切片和图的位置高宽对齐
27:14 颜色
27:51 色系
28:28 生成不能用透视表做源的图
30:53 图的设置
12.GetPivotData打破透视表固有布局
1 选项沟选GetPivotData，须依托现有数据透视表。2 鼠标点击数据透视表去生成函数更简单。3 被引用的数据透视表单元格不能折叠
00:00 GetPivotData参数说明
03:25 与其它函数结合使用
04:21 折叠字段后可能REF报错
05:31 解决正常的REF报错
07:10 动态标题
09:28 不能和普通函数一样下拉
13.一表拆分多表
1 显示报表筛查页，拆分条件需要显示在表中时，数据源复制拆分条件字段。2 先设置好布局样式后再拆分。3 安全工作表数量254个。4 复制粘贴透视表到其它文件后会自动关联原始数据源
00:00 无法拆分成文件
02:05 复制到其它文件后的源问题
03:48 多条件拆分
05:26 拆分条件在透视表中
14.条件格式
1 条件格式对象：区域对象，透视表值对象。2 数据透视表的动态区域仅限值字段，调整布局或行列字段不影响条件格式设置。3 设置条件格式的单元格数量影响计算效率。4 WPS(12.1.0.25225)没有条件格式动态区域功能
00:00 透视表和普通表对象区域区别
03:29 部门层级的条件格式
08:02 销售层级的条件格式
09:25 商品分类层级的条件格式
12:20 取消条件格式
13:34 WPS不支值对象需自定义解决
15:16 条件格式的目的
15:48 复制透视表模板
二.进阶：
15.筛选+函数计算字段
1 区域筛选通过隐藏行实现，透视表筛选不隐藏行直接改变单元格数据。2 分类汇总或总计，受筛选而变化。3 分为"标签筛选"和"值筛选" 。4 "标签筛选"可理解为对行列区域中字段下的项目筛选，相比切片或筛选区域可更复杂筛选，如对象字段是数值可进行值筛选功能。5 "值筛选"的可理解为对值区域中某个数值的筛选，对汇总依据有效，对值显示方式无效，值可以是计算字段。6 刷新不改变筛选条件，虽然标签筛选可通过不包含"空白"实现不显示"空白"，从数据源规范角度看这不是好点子。7 字段列表中字段右侧漏斗表示该字段当前有筛选状态，如该字段不在透视表中，筛选不生效，连接报表例外见16课。8 计算字段不能添加到筛选区域或行区域或切片。9 同一个行列字段只能有一个值筛选，多值筛选需辅助计算字段函数。10 行列多字段时，值筛选只对选择的行列字段有效。 11 变通：筛选区域为空时在透视表上方创建筛选，可实现灵活筛选。12 记录工作表日志，方便后期维护
00:00 透视表与表的筛选差异
03:02 固定切片位置
03:23 透视表的基础筛选
05:17 标签筛选
06:49 用标签筛选解决(空白)
07:05 标签筛选指定关键词
13:39 标签筛选数字
15:49 注意事项
16:28 字段列表中的小漏斗
18:27 日志与对象
19:40 值筛选
22:25 值筛选影响分类汇总和总计
23:55 值筛选的对象必学在透视表中
24:47 值筛选对值显示方式无效
26:54 无法筛选切片计算字段
27:43 计算字段实现多条件值筛选
34:33 灵活筛选
36:51 隐藏切片中没有的数据项
16.按某一字段汇总.年累计YTD.完成率，活用GETPIVOTDATA
1 基本字段或行字段无论是升序或降序，按某一字段汇总的结果都是升序。2 更多时候按某一字段汇总的行列字段用时间或数量更合适。3 修改透视表字段名称会导致GETPIVOTDATA #REF!。4 用条件格式突出数据重点。5 人工调整字段内项目排序只是暂时的状态。6 隐藏切片页眉
00:00 按某一字段汇总不能直接处理未发生的月
07:26 解决方法
22::03 销售区间
17.帕累托8020
1 值字段必学降序。2 刷新不改变降序状态。3 需组合图，按某一字段汇总的百分比设为次坐标。4 无筛选状态修改主要商品柱颜色，跟踪该商品
00:00 创建基础透视表
07:13 创建图
13:28 显示设置形状格式
13:45 跨工作表切片
14:40 位置调整
15:59 指定商品指定颜色
18.排序-自定义排序-灵活排序筛选
1 对汉字默认使用拼音排序，也能可按笔画排序(姓氏)(不限透视表 )；透视表行列字段默认升序。2 透视表排序对象：字段下的项目，数值(总计和分类汇总)，表头名称。3 排查透视表排名状态，恢复默认排序，开启显示上下文工具提示查看关键信息，通过其他排序选项查看详细排序依据。4 如排序后无箭头图标，需开启每次更新报表时自动排序。5 对数值进行横向排序，适合二维表。6 当行列区域是多字段时，字段或数值排序均需依据，且按组排序。7 条件格式应用到透视表。8 人工调整排序或修改项目名称仅对当前透视表有效。9 刷新后，新数据遵循刷新前设置的排序规则。10 自定义排序设置方法：选项>高级>创建用于排序和填充顺序的列表；增加自定义排序后随文件保存；解决日期分组后月切片排序异常。11 其他排序选项>其他选项>排序依据：等价于列区域有字段时对某一列升降序。12 对透视表进行灵活排序和筛选，函数+Table，VLOOKUP COUNTA IF OFFSET ROW，名称管理器，数组函数缺少灵活性。13 切片排序
00:00 排序的基础概念
05:44 绿表排序规则
07:47 深蓝色表排序规则
09:59 翠绿色表排序规则
13:42 红色表排序规则
15:14 灵活排序介绍
15:40 开启关闭上下文提示
16:10 恢复默认排序
20:00 绿色表设置排序
21:11 取消自定义排序
22:07 切片排序
22:48 隐藏显示切片中的项目
23:19 修改切片颜色
23:29 自定义排序关联文件
23:47 深蓝表排序设置
25:53 组内列最大值-条件格式
29:56 自定义排序 关闭自动排序
31:56 项目改名
36:22 其他排序选项的排序依据
39:04 灵活排序
19.值筛选前n-条件格式百分比
1 条件格式前后n项和值筛选大小n项是4个不同的功能。2 填写百分比只支持2位小数，但计算按实际百分比计算
00:00 概述
01:01 数量前3飘红-条件格式
02:43 排名前30%飘红-条件格式
08:31 只显示销量前3的客户值筛选
09:37 销量占比前30%的客户值筛选
13:18 累计销量最高且大于n值筛选
20.排名名次-区域和自定义条件格式
1 排名名次受分组影响，如何组合分组排名与非分组排名。2 条件格式可作用于值显示方式。3 自定义区域条件格式(后面课程不再讲解)。4 IFERROR VLOOKUP XLOOKUP OFFSET COUNTA MATCH
00:00 统计表功能讲解
01:41 函数
09:14 条件格式
21:15 数组函数方法(新版Excel)
21.差异统计陷阱
1 值显示方式的差异统计：其他数据基于某个数据进行对比，例如所有商品与明星商品对比，或相邻日期的数据对比。2 受分组影响，更受行列区域字段下的项是否完整的影响。3 在条件合适的情况下，可快速创建同比环比。4 差异计算有4种状态，需考虑。5 GETPIVOTDATA打破透视表布局 IFS
00:00 差异统计功能概述
02:22 介绍本课统计表
04:27 制作案例透视表
11:18 特定情境下差异呈现出的问题
25:19 解决方法 差异总结
26:28 普通的同环比统计和潜在问题
22.可视化动态同环比.优化函数，可变值的图.柱线图
1 典型工作方法，数据分层：源>计算>结果；数据源模拟可能性。2 动态下拉菜单。3 动态标题。4 灵活切换折线图数值。5 Ctrl+1图元素属性。6 GETPOIVTDATA陷阱，#N/A隐藏线图，函数 NA T IF IFS AND OR IFERROR OFFSET TEXT MIN MAX。7 条件格式。8 折线图，柱图，散点图
00:00 原理概述
06:05 基础准备工作
11:01 动态下拉菜单
14:37 当年值
23:01 环比百分比
35:04 名称管理器,追踪从属单元格
37:44 环比增减
39:38 同比增减
42:00 环比百分比
46:11 年环比
48:55 条件格式
00:50:26 配色
00:52:48 切换主数值
01:02:18 删除过程函数
01:03:42 图 同比百分比 环比百分比
01:09:22 图 隐藏显示同环比曲线
01:11:42 动态标题
01:21:06 图 曲线显示百分比标签
01:26:53 居中 组合元素
01:27:11 同比增量 同比减少
01:32:28 图 同比增加
01:41:16 图 加月字等首尾
23.跨文件数据源.多数据源合并.函数合并 PowerQuery合并.突破104万行
1 单一数据源，跨文件时在源文件创建透视后剪切复制最简单。2 将一个文件中的多工作表数据合并成一个工作表，将多个文件中指定工作表数据合并成一个工作表，纵向合并。3 合并状态：增量，删除，替换；合并频率；单次合并表数量。4 频率和数量不多时，或源过于不规范时，人工复制粘(简单有效果)。5 函数合并(实用)OFFSET。6 PowerQuery合并(门槛高，WPS不支持)，数据源行数可突破104万。7 需启用外部数据连接。8 Alt+D+P合并(不实用)。9 合并文件多频率底，推荐使用数阁批量合并工具
00:00 单一跨文件
02:42 函数合并多表
13:57 PowerQuery合并
30:02 Alt+D+P合并
24.灵活对比数据找差异
1 项目状态：有，无，不一致；数量状态：一致，不一致。2 COUNTIF
00:00 合并创建源
01:30 创建对比透视
03:07 函数
04:29 筛选对比
25.银行期初期末.用透视表做源，函数合并多表表头不一致
1 计算字段，值汇总方式 按某一字段汇总。2 源要有期初数据行。3 用名称管理器做透视表源时需用OFFSET。4 用透视表做源时需刷新2次。5 使用每月1日的日期表示年月更优。6 Table表 TEXT VSTACK(365\2024) EOMONTH #(365\2024\2021) COUNTA OFFSET YEAR MONTH
00:00 函数合并多表
07:36 获取月首日
08:30 创建透视表offset源
12:24 创建辅助透视
22:36 结果表透视
25:12 用透视表做源的刷新
26.库存.安全库存.判断筛选状态
1 源表中入库出入需2列，需有期初。2 计算字段，值显示方式按某一字段汇总(考虑组问题)。3 条件格式。4 自定义单元格格式，下拉菜单。5 IF TEXT MAXIFS SUMIFS TODAY IFERROR
00:00 库存透视表
06:46 安全库存准备
09:23 库存状态
11:31 最后出入库日期
13:13 近N日出库
15:15 下拉菜单
16:01 自定义最近N天数字格式
17:30 获取电脑当天日期
18:09 判断筛选状态
22:07 表头条件格式
27.求解次数列出公式
1查看透视表的计算计算和计算项
00:00 列出计算字段和计算项的公式
28.多透视表组合
1 排序需保持同规则。 2 列区域使用不经常增加内容的字段 3 相邻透视表预留空间
00:00 概述
00:26 制作过程
02:18 删除隐藏指定字段的分类汇总
02:33 解决移动透视表后的异常显示
03:12 透视表改名
03:23 切片连接多透视表
04:13 使用自定义排序
04:47 修改报表布局
05:05 隐藏显示+-号
05:14 禁用启用双击折叠字段和详单
05:57 让空值显示成0
06:11 预设样式调整
06:16 行标题背景色
06:25 隐藏标题字段
29.复杂统计要有数量字段
有效车次计费。1 灵活使用辅助列。2 利用条件格式隐藏透视表中值。3自定义单元格格式让值区域显示中文。函数：IF COUNTIF FIND
00:00 概述
02:34 创建组序号
06:06 报表布局、颜色样式等调整
07:24 计费车次
09:02 计费状态计算字段逻辑判断
11:03 计费状态显示中文
12:07 条件格式隐藏总计和分组小计
14:54 自定义单元格的限制
30.多表数据源.随日期改变单价
不同日期单价不同。1 用函数关联多表数据源。2 用平均值函数实现多对一。3 用Table表字段名称写函数。4 复杂问题先拆解步骤。函数 VLOOKUP XLOOKUP FILTER COUNTIFS TEXT
00:00 概述
01:05 xlookup vlookup
02:43 获取不同日期单价的逻辑
07:19 vlookup+filter
09:11 多对一
16:16 值汇总依据均值获取业绩积分
16:33 创建透视表
17:12 用Table表字段名称写函数
31.过程透视表与循环透视表
1 用透视表作为辅助表。2 循环透视表解决多对一同样具有局限性。3 最早日期最晚日期。函数 XLOOKUP COUNTIFS IF
00:00 概述
00:27 过程透视表的业务逻辑
01:19 过程透视表数据逻辑
01:53 过程透视操作过程
06:19 循环透视数据逻辑
07:02 循环透视操作过程
32.用透视表做透视表的数据源
1 库存流水号。2 带出入库单价的二维库存表转一维表。3 单价放在行区域还是放在值区域。4 计算字段处理乘法。5 写函数时用Table表字段名称是关联数据源的最佳方法。6 Offset动态区域。7 名称管理器。函数 XLOOKUP ABS COUNTA OFFSET
00:00 创建源透视表
01:50 单价与库存流水号
03:25 在计算字段中处理乘法
04:47 出库负值数量对应正值收入
05:22 计算字段获取绝对值
06:24 嵌套计算字段
06:49 多个数据源表
07:11 用Table字段名称写函数
08:52 offset动态xlookup参数1
12:32 用透视表做数据源的数据逻辑
13:35 用透视表做数据源
16:25 创建用透视表做源的透视表
33.计算字段中的平均值函数
1 计算字段是对透视表同行的各字段或本字段计算，包含分类汇总和总计行。2 逻辑判断函数、数学函数适合计算字段。3 常用函数：IF OR IFS INT MOD ABS ISODD ISEVEN RIGHT LEFT LEN MAX MIN AVERAGE ROUND。4 用计算字段解决可参与计算的平均值。5 浮点运算小数误差
00:00 概述
00:48 能参与计算字段的平均值
02:03 计算字段函数弧度
02:25 浮点运算小数误差
04:29 创建SIN COS TAN
05:21 计算字段的重点
34.业务波动值汇总依据偏差方差
1 偏差或方差都是反应数据波动情况，是看数值与平均值之间差值的大小，不是倍数。2 业务统计、PPT报告，用总体标准偏差即可，不要用方差。函数 STDEV.S STDEV.P VAR.S VAR.P
00:00 最大最小条件格式
01:17 数据解读
03:27 偏差方差的差异
04:34 只能用一维表计算
35.业务重要性显示方式指数
查看销售额对业务分类和部门的相对重要性，交叉对比
00:00 条件格式
01:39 解读指数
02:03 指数计算逻辑
02:47 必须数据交叉的二维表
36.锁定切片和透视表图位置
1 图大屏的透视表应创建在辅助工作表。2 跨工作表移动切片。3 元素的大小与属性。4 锁定切片需勾选“禁用调整大小和移动”，但不能勾选锁定。5 用函数或GETPIVOTDATA从透视表获取数据
00:00 透视表放在辅助工作表
01:13 跨工作表移动切片
01:33 锁定位置
02:02 无法对透视表做到绝对锁定
37.透视表做图源散点图气泡图
1 气泡图数据解读。2 透视表不能直接生成散点图、气泡图、股价图等图。3 用函数关联透视表到图源表。4 图源的数据区域是静态的。5 制作散点图气泡图
00:00 气泡图数据解读
02:24 透视表不能生成散点图
03:28 散点图与透视表的源逻辑
04:47 创建散点图
06:08 添加中文标签
06:40 创建气泡图
08:07 添加切片
08:34 函数关联源
09:20 图源是静态的无法OFFSET
11:02 动态标题
38.单科目成绩统计计算字段进阶
1 行区域多字段的平均值。2 计算字段函数。3 计算字段状态函数。4 自定义单元格格式。5 排名名次突破行区域字段组。6 复杂条件格式。函数 AVERAGE RANK MEDIAN STDEV.P MAX MIN IF
00:00 人数与4次分数
02:48 班级和个人的平均分
04:41 中位值
05:34 查看计算字段公式
05:48 先改值显示方式再改格式
06:18 调整值字段顺序
06:40 状态计算字段IF
07:43 自定义单元格格式
08:14 班内排名名次
08:56 班级排名名次
09:42 年级排名名次
12:00 颜色样式
12:23 条件格式人数
15:23 获取单元格颜色数值
15:32 条件格式班排名
17:32 条件格式总计
39.多科目成绩统计(上一课升级)
1 灵活调整数据源。2 用COUNTIFS实现组内排名 函数 ROW RANK COUNTIFS
00:00 概述
00:34 数据源转格式
01:06 自动填充数字1
01:32 获取平均分
02:41 班内班级排名名次
03:25 组内排名名次函数
40.计算字段与计算项的计算逻辑
同时计算字段和计算项时，优先计算项，只有加减是安全的，乘除结果会出错
00:00 问题概述
01:11 拆解问题
07:16 解决问题
41.单ID多商品退换货
销售ID等ID对于商品销售详单至关重要。函数 TEXTBEFORE
00:00 概述
02:00 整理订单ID
04:59 值筛选的注意事项
05:39 思路