excel数据透视表怎么做?附冷门技能及错误总结

2022年11月27日10:22:22工具教程评论291 views字数 3008阅读模式
作者:小凡说
来源:知乎

数据透视表是Excel/WPS中最实用的功能,常用来完成数据的分类汇总、维度分析,熟练运用,往往比函数更加高效、快捷。

今天我们就从透视表的建立、实用功能、动态更新数据源、常见错误总结和冷门实用技能等五个维度,带领新手小伙伴们从零到一、从入门至精通,快速学习数据透视表。

excel数据透视表怎么做?附冷门技能及错误总结

建立数据透视表

1、插入数据透视表

数据透视表位于【插入】功能区,就是下面这个按钮,它旁边的“推荐的数据透视表”是透视表的弟弟,可以先忽略。

excel数据透视表怎么做?附冷门技能及错误总结

在建立透视表之前,先准备一份数据源,你需要注意以下两点:

1、表头不能存在合并单元格;2、表头不能存在空白单元格。

这里说的表头一般是指数据源的第一行

excel数据透视表怎么做?附冷门技能及错误总结

表头是表格的第一行,也是每列的名称,所以它不能为空,必须有字段,在后续的透视中,它将代表整列。

鼠标选中数据源任意一单元格、或者Ctrl+A全选数据源,之后依次点击【插入】—【数据透视表】,会出现下面这个界面:

excel数据透视表怎么做?附冷门技能及错误总结

第一个框是透视表的区域,经过上面的操作,这里会自动填入数据源区域,所以不用管,第二个选项一般默认新建一个工作表用来展示透视表,所以也不用管,直接点击【确定】,进入透视表界面。

2、字段区域说明

点击确定之后,会在数据源前面生成一个新的Sheet,用于展示透视表。设置界面,一共由两部分组成,左边的报表生成区和右边的字段展示区。将字段展示区中的字段拖动到不同的区域中,报表生成区生成相应的内容。

excel数据透视表怎么做?附冷门技能及错误总结

筛选区域:字段拖入到筛选区域中后,对应字段会出现在透视表的最上方,可以下拉筛选,对透视表的结果进行筛选;

列区域:将字段拖动到此区域中,会以非重复值列的形式展示,出现在透视表的上方,作为新的列标签;一般拖入0-2个字段,太多的列数据会看不过来。

行区域:将字段拖动到此区域中,会以非重复值行的形式展示,出现在透视表的左侧,可拖入多个字段;

值区域:用于展示数据结果,最终展示结果只会是数字(小数),文本字段拖动到此区域默认计数、数值字段拖动到此区域默认求和(如果你的数值字段无法求和,那可能是格式为文本所致)。

下图中透视的结果为:不同城市、不同邮件方式下的销售数量。

excel数据透视表怎么做?附冷门技能及错误总结

3、经典布局

透视表默认展示形式并不符合日常的观看形式,将多个字段拖动到行区域中,它们会以压缩的形式分布在一列中(下图中A列分布了省份、城市、邮寄方式3个字段),如何使每个字段单独占用一列?

excel数据透视表怎么做?附冷门技能及错误总结

点击最上方的【设计】,报表布局依次选择【以表格形式展示】、【重复所有项目标签】,最后取消分类汇总,这样,一张正常的表格就显示出来了。

excel数据透视表怎么做?附冷门技能及错误总结

实用功能演示

1、值汇总依据

上面说到文本字段拖动到值区域默认计数、数值字段拖动到值区域默认求和,除此之外,透视表还提供多种汇总依据。鼠标右键,选择【值汇总依据】,可以看到多种方式。

excel数据透视表怎么做?附冷门技能及错误总结

比如在这里查看每个省份最大一单的销售数量,在汇总依据中选择【最大值】即可。

excel数据透视表怎么做?附冷门技能及错误总结

2、值显示方式

除汇总依据外,透视表的值提供多种显示方式,常用的有列汇总的百分比行汇总的百分比、差异百分比(同环比)。

excel数据透视表怎么做?附冷门技能及错误总结

这里查看各省份的销售数量占比份额,右键选择【列汇总的百分比】,就可以看到每个省份销量占比。

excel数据透视表怎么做?附冷门技能及错误总结

还可以用差异百分比来计算同环比,下图中计算近三年同比数据(无2019年数据,所以2020年同比为空)

excel数据透视表怎么做?附冷门技能及错误总结

3、新建字段

可以通过加减乘除等数学运算,为数据透视表新增一个字段。

点击上方【数据透视表分析】—【字段、项目和集】,选择【计算字段】。

下图中向透视表新增【毛利率】字段。

excel数据透视表怎么做?附冷门技能及错误总结

4、创建组合

针对行区域内容,透视表提供【组合】功能。

下图中A列为明细日期,如果想要展示年月字段,可以通过组合功能。

右键选择【组合】,步长选择“年”和“月”,点击完成设置。

excel数据透视表怎么做?附冷门技能及错误总结

组合功能常见于日期、数字格式,其实文本格式也可以创建组合,如下图中,选中待组合的几个字段,右键选择“组合”,即可组合出一个新的字段,相当于新建了一个大类。

excel数据透视表怎么做?附冷门技能及错误总结

5、透视表如何筛选

透视表默认是不可筛选的,选中数据透视表,筛选按钮处于灰色状态,无法筛选。

如果要筛选,可以采取一种欺骗的手段,鼠标选中数据透视表表头旁边的空白单元格,这时筛选按钮处于可用状态,点击即可对数据透视表进行筛选。

excel数据透视表怎么做?附冷门技能及错误总结

6、切片器

切片器主要用于图表联动,制作可视化看板。

excel数据透视表怎么做?附冷门技能及错误总结

在单个透视表中,切片器的功能与筛选项无异。

选中数据透视表,点击上方的【数据透视表分析】—【插入切片器】,在这里表头任意字段都可以作为切片器的选项,随便选中一个字段,点击确定,出现切片器功能。选择不同的数据,透视表内容跟着变化,可以看到基本与筛选功能一致。

excel数据透视表怎么做?附冷门技能及错误总结

当然,切片器肯定要比筛选项好用,在于单个切片器可以联动多个同源数据透视表(注意,是同一个数据源建立的透视表,不同源需要在Power Pivot中建立关联,这里不阐述)

下图中,通过建立报表连接,用一个切片器控制了2个透视表。

excel数据透视表怎么做?附冷门技能及错误总结

动态更改数据源

我们在建立数据透视表的时候,数据源选择是固定的,比如上面的例子选择“A1:J8236”区域作为数据源,如果后续继续往表中添加新的数据行或数据列,由于透视表数据源未重新选择,刷新透视表,内容是不会变化的

那么如何动态更新数据源,数据源变化时,透视表自动变更数据源?

1、超级表

第一种方法是将数据源转换为超级表,选中数据源,同时按下Ctrl+T,点击【确定】,这样,数据增减,透视表的数据源会跟着变动。

excel数据透视表怎么做?附冷门技能及错误总结

2、定义名称+OFFSET

第二种方式是定义名称结合OFFSET函数

依次点击【公式】—【定义名称】,名称可随便输入,这里我们输入"data",引用位置输入公式:

=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

点击确定,接着再插入数据透视表,表/区域输入刚才的名称“data”,这样建立的数据透视表,数据源变化时,透视表数据也会跟着变化。

excel数据透视表怎么做?附冷门技能及错误总结

常见错误总结

1、透视表无法创建

透视表创建不成功主要原因在于表头字段存在空白所致,数据源的第一行不要存在空白单元格。

excel数据透视表怎么做?附冷门技能及错误总结

同时为了保证数据的准确性,整个数据源中,也不要有合并单元格,有合并单元格的记得取消合并、并填充内容,再进行透视操作。

excel数据透视表怎么做?附冷门技能及错误总结

2、数据源修改内容后,透视表刷新无变化

这种情况是因为透视表数据源与原始数据源未对应上。有可能未包含修改后的数据,也有可能链接出错。

不管哪种原因,我们点击【数据透视表分析】—【更改数据源】,重新框选下数据源就好了。

excel数据透视表怎么做?附冷门技能及错误总结

3、透视结果包含空白字段

透视表中某字段出现空白,但是值区域却有数据,这种情况是因为这个字段数据源中包含合并单元格,取消合并单元格,填充即可。

excel数据透视表怎么做?附冷门技能及错误总结

4、日期/数字无法分组

在对日期或数字进行分组的时候,透视表显示【选定区域不能分组】,这种情况是因为数据源日期或数字列为文本格式,通过分列功能,将其转换为日期或数值格式即可。

excel数据透视表怎么做?附冷门技能及错误总结

透视表冷门技能

1、批量合并单元格

右键选择【数据透视表选项】,勾选【合并且居中排列带标签的单元格】,接着用格式刷去刷原始数据源可以完成单元格的批量合并。

excel数据透视表怎么做?附冷门技能及错误总结

2、按字段拆分工作表

待拆分字段拖动到透视表的筛选区域中,接着下拉透视表选项,点击【显示报表筛选页】,点击确定,即可按字段拆分出多个工作表。

excel数据透视表怎么做?附冷门技能及错误总结

3、批量合并工作表

合并多个工作表的内容,点击Alt+D+P三键(依次点击),进入透视表向导界面,勾选【多重合并计算数据区域】,点击下一步,添加所有待合并表格数据之后,点击【确定】,生成一张透视表,将筛选项“页1”拖动到行区域,这样就完成了多表的汇总(调整下格式)

excel数据透视表怎么做?附冷门技能及错误总结

透视用的好,工作没烦恼,以上就是关于数据透视表的技能介绍,希望对你有所帮助~

  • 本站内容整理自互联网,仅提供信息存储空间服务,以方便学习之用。如对文章、图片、字体等版权有疑问,请在下方留言,管理员看到后,将第一时间进行处理。
  • 转载请务必保留本文链接:https://www.cainiaoxueyuan.com/office/30260.html
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定