数据透视表是Excel中最快、最灵活、最强大的数据处理分析工具。它可以轻松快速地进行分类汇总、筛选与排序、数据比较分析等各种复杂的数据统计。在数据汇总和分析方面,它比各类求和、求平均、求占比Sumif、Countif、Averageif的函数,效率更高更方便,且不易出错。
实际上它是一个动态汇总报表,可以按照不同的方式分析数据,将无规则的成千上万条的数据按照一定的方式统计汇总。
总之,数据透视表综合了数据排序、筛选和分类汇总的数据分析工具。
一、基本操作
(一)创建数据透视表
1.选择数据源
数据透视表创建之前,要先做好前期数据整理。
避免数据源出现以下的问题:
1、标题不能有空(表头只能有一行;字段不能为空)
2、标题不要重复 (相同字段名会被自动添加序号,进行区别)
3、源数据必须是一维表,不能是二维表。
4、源数据之间不能存在空格、空行、空列(包含空字段,无法制作数据透视表)
5、不能存在合并单元格(字段所在行有合并单元格,除第1个单元格外,其他均作为空值处理。也无法创建数据透视表)
6、不能出现文本型日期和数字格式(如果存在非法日期,生成的数据透视表中,无法按日期格式进行年、月、日格式的筛选和组合;文本型数字,将无法在数据透视表中正确求和。)
7、数据源中不能有多余的合计行
补充:
• 一维表:每个数据只对应一个字段,如果对应多个字段,那么这些字段必须在同一行或同一列上。
• 二维表:每个数据只对应两个字段,这些字段分布在行上或列上。
2.插入数据透视表,选择放置数据透视表的位置
3.拖动字段进行汇总、分析
依需求将字段拖动到字段值,或者勾选要添加到报表的字段前面的复选框。
选择字段的时候要注意,第一个勾选的字段会被默认为行。
主要看我们分析的侧重点在哪,然后决定字段的位置摆放。
从结构上看,数据透视表可以分为4大区域:
行区域、列区域、值区域、报表筛选区域
1、报表筛选字段区域(分页):分页筛选的作用,按指定条件过滤筛选数据进行汇总统计。
2、透视表字段区域
- 行字段区域:该区域的字段会按照上下排列显示。可以有多个行字段,并自由排列顺序
- 列字段区域:该区域的字段会按照左右排列显示。同行,只是排列方式不同
- 值字段区域(汇总方式/显示方式):求和、计数、平均值等汇总方式,显示方式可以显示百分比等。【双击数值可查看明细】
(二)刷新或更改数据源
如果数据源中的数值进行了更新或新增数据行、数据列,此时不需要再重新创建数据透视表。只需简单操作一下,就可以了。
第一种:如果只是数据源中的数值进行了更新,采用刷新即可。(即数据源中的数据更新,但不增加行列。)
方法1:“点击数据透视表任一单元格,右键→刷新”即可。
方法2:选择数据透视表中任一单元格,点击“数据透视表工具”下方的“分析”,点击“刷新”完成。
第二种:如果是数据源的结构或布局变更了,这时需采用更改数据源的方式。(即增加了行或者列,只是刷新是不行的,还需要更改数据源。)
方法1:“Alt+D+P”快捷键,依次按下ALT键、D键、P键(不是同时按,是快速的依次按下),重新选定数据源的区域即可。
方法2:选择数据透视表中任一单元格,点击“数据透视表工具”下方的“分析”,点击“更改数据源”完成。
※ 数据透视表刷新后,变丑了?
一招就能帮你搞定:
【右键】-【数据透视表选项】-【布局和格式选项卡】-取消勾选【更新时自动调整列宽】
(三)插入计算字段
透视表有个强大的功能【计算字段】,涉及到不同字段间的运算时,均可以使用此功能。
当我们需要计算一个新的字段,是由已有的字段公式组成的。在不改变源数据的前提下,操作步骤如下:
1.单击数据透视表中的任意单元格
2.点击“数据透视表工具”下方的【分析】→【字段、项目和集】→【计算字段】
3.在弹出的【计算字段】对话框中,输入字段名称,输入公式:公式中的字段在“字段列表”选择字段插入。
※ 数据透视表使用公式计算,结果不对了?
将普通表格转为数据透视表之后,有时需要再次计算,若是按照普通表格来处理,你会发现你的公式很长,而且下拉填充所得到的结果也不是你想要的。
一招就能帮你搞定:
【文件】-【选项】-【公式】-取消勾选【使用GetPivotData函数获取数据透视表引用】
(四)使用切片器
切片器就像是一个高级筛选器,同我们日常使用的数据报表时的筛选项是一样的,能够使筛选过程更加直观。
操作步骤:
1.选中数据透视表任意单元格
2.点击“数据透视表工具”下方的【分析】--单击【插入切片器】
3.在弹出的【插入切片器】对话框中勾选自己所需切片的字段,即可。
※切片器对象的右上角,有两个按键,左边是多选按钮,右边是取消筛选的按钮。
延伸:
制作动态图表:
根据透视表数据还可以方便的生成数据透视图。交互式图表,使用数据透视图就会很简单。
1.单击数据透视表中的任意单元格
2.点击“数据透视表工具”下方的【分析】-【数据透视图】-选择一个合适的图表类型
或点击“数据透视表工具”下方的【插入】-【推荐的图表】/【数据透视图】-选择一个合适的图表类型
3.点击“数据透视图工具”下方的【设计】,对数据透视图进行快速美化
※ 切片器联动/切片器控制多个透视表?
一招就能帮你搞定:
对于使用同一个数据源生成的透视表,要想两个或多个数据透视表都能控制起来。
(1)先对数据透视表1 插入切片器,此时切片也只对数据透视表1起作用。
(2)只需要选中切片器,点击【选项】--【切片器的报表连接】,这样就能实现切片器的联动效果。
二、布局与格式
通过“分析”和 “设计”选项卡给数据透视表设置合适的格式。
在“分析”选项卡功能区,有“数据透视表选项”、“插入切片器”、“刷新或更改数据源”、“字段、项目和集”、“数据透视图”、“字段列表”、“+/-按钮”、“字段标题”等。
“分析”选项卡
在“设计”选项卡功能区,最左边看到四个功能按钮,分别是“分类汇总”、“总计”、“报表布局”、和“空行”。
(一) 布局
1. 报表布局
(1) 数据透视表的五种布局形式
压缩形式、大纲形式、表格形式、重复所有项目标签、不重复所有项目标签。
- 压缩形式:
EXCEL默认的透视表布局方式。
主要特点是:无论叠加多少个行字段,都只占一列,分项汇总显示在每项的上方。这种样式将所有的行标签都放到了第一列。
- 大纲形式:
主要特点是:标题分列显示,有几个行字段就会占几列,即行字段会并排显示,分项汇总显示在每项的上方。
例如行字段有三个,大纲形式的布局就会占三列,而压缩形式只占一列。
大纲形式与压缩形式最重要的区别就是:大纲形式有几个行字段就会占几列,即行字段会并排显示。另外,大纲形式的分项汇总显示在每项的上方。
- 表格形式:
表格形式的透视表是最常用的一种形式。
主要特点是:标题分列显示。汇总显示在每项的下方.跟大纲形式基本相同,不过如果你选择了显示分类汇总的话,分类汇总的数据始终显示在该类目的底部。
表格形式与大纲形式的联系:
1、与大纲形式相同:行字段会并排显示,有几个行字段会占几列;
2、与大纲形式不同:表格形式是有表格的;表格形式的分项汇总是在每项的下方,而大纲形式是在上方。
- 重复所有项目标签。可以填充相同的内容。
在数据透视表中,当行区域包含多个字段时,有些字段的项目标题只会在第一行中显示,而其他行为空,有时可能需要让每个字段的项目标题每一行中都显示出来,选择数据透视表-设计选项卡-报表布局-重复所有项目标签。
- 不重复所有项目标签。取消重复。是4的逆操作。
总结:
① 压缩式布局是默认布局方式
② 大纲式视图,按列标题分列显示,汇总显示在每项的上方
③ 表格视图,按标题分列显示,汇总显示在每项的下方
④ 重复所有项目标签,可以填充相同的内容。
⑤ 不重复所有项目标签,取消重复,是上一步的逆操作。
※数据透视表通用的调整方法?
第一步:选中透视表任意单元格,【设计】-【报表布局】-【以表格形式显示】,然后继续点击【重复所有项目标签】(“表格形式”且“重复项目标签”)
第二步:【设计】-【分类汇总】-【不显示分类汇总】
第三步:【分析】- 点击取消【+/-按钮】
最终调整的表格是最适合观看的格式。
2.值汇总方式/依据和值显示方式
【值】区域是数据透视表的核心部分,通过数据透视表提供的强大数据计算功能,可以使用多种汇总方式和值显示方式来计算值字段数据。
(1)值汇总方式/依据
值汇总方式/依据:即要用数据透视表对原始数据中的数值进行怎样的计算。
值汇总方式/依据:求和,计数,平均值,最大值,最小值,乘积等
操作:需要设置的时候右键调出或者双击字段列表修改汇总依据。
※对同一个字段的多种汇总方式?
有时候可能需要对同一个字段做多次汇总,分别统计出各类产品的总数、最大值和最小值等。
第一步:首先将【产品类别】拖入【行】区域,并将【数量】三次拖入【值】区域
第二步:在得到的数据透视表中,双击第二个【数量】,在弹出的【值字段设置】对话框中将【计算类型】设置为【最大值】。使用同样的方法将第三个“数量”设置为【最小值】即可
除了这种方法外,还可以在【数量】上右击,在弹出的快捷菜单中选择【值汇总依据】选项,然后在级联菜单中选择需要的汇总类型。
注意:只有【值】区域中可以将同一个字段拖入多次,
而【行】和【列】区域中,一个字段只能拖入一次。
(2)值显示方式
值显示方式:即将汇总出来的结果以某种方式展示,从而更清晰的看出数据之间的关系和逻辑。
默认是无计算,就是怎么汇总的怎么显示,但实际上我们有时候会有特殊的要求。
值显示方式:无计算,总计的百分比,列汇总的百分比,行汇总的百分比,百分比,父行汇总的百分比,父列汇总的百分比,父级汇总的百分比,差异,差异百分比,按某一字段汇总,按某一字段汇总的百分比,升序排列,降序排列,指数等
操作:需要设置的时候右键调出或者双击字段列表修改汇总依据。
数据透视表有多种数据百分比的显示方式,最常用的:总计百分比、行/列汇总百分比和父级汇总百分等。
想要值以百分比的形式显示可以:
双击数值标题,调出【值显示方式】-可选择相应的百分比选项。
或选中数值,右键-【值显示方式】-可选择相应的百分比选项。
- 总计百分比
总计百分比含义是:个体占总体的情况,每一项分类汇总的值占总计的百分比。各项之和为100%。
总计百分比
※ 数据透视表中只有一个100%。
- 行/列汇总百分比
当我们想看某个数据在行字段或者列字段的水平/垂直维度上的占比时,就需要用到行/列汇总百分比。
- 父行/父列汇总百分比
- 父级汇总百分比
个体占局部的情况,局部百分比。各小组里的项之和为100%。
- 差异/差异百分比
差异:就是以某一个值为基准,两个数值的差异(用于计算环比、同比差异)
差异百分比:用于计算环比增长率、同比增长率
- 按某一字段汇总/汇总的百分比:逐渐累加得到百分之百。
- 升序/降序排序: 按照汇总数量大小进行数量排列,用于中国式排名
※ 总计百分比&父级汇总百分比区别:
总计的百分比,是个体占整体的情况,往往只有一个维度。
父级汇总百分比,是个体占局部的情况,局部百分比。
(二)格式
(1)显示/隐藏分类汇总
方法一:点击数据透视表任意单元格,单击【设计】-【分类汇总】-选择合适的分类汇总方式。
方法二:点击数据透视表任意单元格,【右击】,勾选/取消勾选【分类汇总】即可。
(2)展开/折叠字段 & 取消字段前+/-符
A.展开/折叠字段
利用活动字段的折叠或展开功能,可轻松地对透视表中的某一字段进行隐藏。
方法一:点击「+」号,表示【展开字段】,可以批量展开数据。
同样,点击「-」号,表示「折叠字段」,可以批量折叠数据。
方法二:点击数据透视表的任意单元格,右键-【展开/折叠】,选择合适的展开/折叠方式。
B.取消字段前+/-符
方法一:点击数据透视表的任意单元格,单击数据透视表工具上的【分析】选项卡-【+/-按钮】。
方法二:点击数据透视表的任意单元格,右键-【数据透视表选项】-【显示】-【显示展开/折叠按钮】。
(3)合并行标签
这个功能是用来将所有的带“标签”的单元格居中对齐,但是,合并居中行标签的功能只有在表格样式为“表格形式”的时候才有用。
操作步骤:
点击数据透视表的任意单元格,右键,选择【数据透视表选项】-【布局和格式】-勾选【合并且居中排列带标签的单元格】即可~(只对表格形式布局有效)
(注意:合并行标签只对表格形式布局有效,对大纲式和压缩式无效。)
(4)标签项重复显示
操作步骤:
点击数据透视表的任意单元格,单击【设计】选项卡 -【报表布局】-【重复所有项目标签】。
(注意,标签重复项只对大纲式和表格式有效)
(5)插入空行间隔
在数据比较繁多的报表中,我们可以使用空行来区分各项数据,以方便后期查看分析。
但是在日常处理数据的时候,空行会带来一些麻烦,所以处理数据时还是建议大家不插入空行哟。
操作步骤:
方法一:点击数据透视表的任意单元格,单击【设计】选项卡-【空行】-【在每个项目后插入空行】。
方法二:点击数据透视表的行字段任意单元格,右键-【字段设置】-【布局和打印】-勾选【在每个项目标签后插入空行】 。
(6)取消总计
总计的功能有:对行和列禁用,对行和列启用,仅对行启用, 仅对列启用
取消总计操作步骤:
方法一:点击数据透视表的任意单元格,单击【设计】选项卡-【总计】-【对行和列禁用】,就可以取消总计。
方法二:点击数据透视表的任意单元格,右键,选择【数据透视表选项】-【汇总和筛选】-取消勾选【显示行/列总计】。
数据透视表的汇总方式主要有四种:行汇总、列汇总、交叉汇总、交叉分类汇总,根据实际的需求,采取不一样的汇总方式来达到目的。
A.当只需汇总分析一个字段时,采取行汇总和列汇总
行汇总:
列汇总:
B.当需要汇总分析两个字段时,采取交叉汇总
C.当需要汇总分析三个或三个以上字段时,采取交叉分类汇总
三、组合
- 第一大类:自动组合。自动识别日期、数值等可计算的字段,然后按照既定要求的跨度区间进行组合。
例如:按年度、季度、月、日、小时、分......进行组合汇总。
- 第二大类:手动组合。将文本格式的数据按照自定义的方式进行组合。
例如:华东大区、华南大区;一线城市、二线城市等。
实际上,时间维度和数字维度的组合,在逻辑上是一样的,都是基于数值的大小来进行分组组合的,这两者可以成为自动组合;文本型的组合则是进行自定义组合。
(1)日期组合:按年、季度、月、日,甚至小时、分……的汇总;
对于日期类型的字段进行统计时,我们可以使用【组合】功能,对日期进行按年、季度、月等单位来统计:
操作步骤:
方法一:点击数据透视表的行字段任意单元格,单击【分析】选项卡-【分组选择】/【分组字段】/【取消组合】。
方法二:点击数据透视表的行字段任意单元格,右键,选择【组合】/【取消组合】。
起始于、终止于数据会自动根据数据源生成,不用管它。步长选择月份或者季度 点击确定即可。(组合时日期必须为正确日期格式,否则无法组合)
※表格中需要用来进行日期组合列的数据都要是日期类型哦。
(2)数字组合:
对于数字类型的字段,除了对日期进行组合外,还可以对数字进行组合。
如果是统计得分情况或年龄分段情况等数据列数值的分布情况,就需要用到数据透视表的数字分组。
操作步骤:
方法一:点击数据透视表的行字段任意单元格,单击【分析】选项卡-【分组选择】/【分组字段】/【取消组合】。
方法二:点击数据透视表的行字段任意单元格,右键,选择【组合】/【取消组合】。
在组合中可设定好需要的起始值、终止值以及步长即可。
(3)文本组合/文本分组:(比如要组合的对象是文本字段,或者对日期进行不等距的组合。)
如果是按地区统计或者个性化统计需求,可直接在数据透视表里面创建文本分组。
方法一:在需要统计的列中,按住Ctrl键选择要组合的单元格,单击【分析】选项卡-【分组选择】/【取消组合】。
方法二:在需要统计的列中,按住Ctrl键选择要组合的单元格,右键,选择【组合】/【取消组合】。
在编辑栏自行修改组的名称为“华东大区”,其他组依此操作。
四、数据透视表的排序和筛选
(1)排序
A.手动拖动排序
要求:按周数进行排序。
发现周数不是按默认的第一周到第六周,而是按照拼音的先后顺序进行排序。
选择要进行拖动的字段,当出现“移动箭头”的时候,按住鼠标左键不放,将字段拖动到目标位置即可。
B.自动排序(常规排序)
要求:按数量进行排序。
操作步骤:
方法一:选中需要排序列的任意单元格,右键-【排序】-【升序】/【降序】/【其他排序选项】。
方法二:选中需要排序列的任意单元格,单击【开始】选项卡-【排序和筛选】-【升/降序】/【自定义排序】。
方法三:选中需要排序列的任意单元格,单击【数据】选项卡-【排序】-【升/降序】。
C.自定义排序
如果你遇到的排序规则是自己制定的,比如按照级别,分组等进行排序,都可以用自定义排序。
要求:按月份排序。
当月份按升序排序时,是按10、11、12,再1-9月排序。此时我们就可以使用自定义排序。
操作步骤:
方法一:
①添加自定义序列。
点击【文件】→【选项】→【高级】→【编辑自定义列表】→弹出自定义序列对话框,输入自定义序列内容,点【添加】→确定,即可添加自定义序列。
②自定义序列的应用。
点击行字段的【其他排序选项】→在【升序排序】选择月份→【其他选项】→取消勾选【每次更新报表时自动排序】→【在主关键字排序次序】的下拉列表中选择新添加的自定义序列【1月……12月】→【确定】即可。
※自定义序列不可使用,怎么处理?
如果出现添加的自定义序列在透视表中无法使用时,
可将光标放在数据透视表的任一单元格,右键→【数据透视表选项】→【汇总和筛选】→勾选【排序时使用自定义列表】即可使用了。
D.按笔画排序
要求:按名字的笔画升序排列。
默认情况下,Excel按名字的字母来进行排序。但是我们可以设置按照名字的笔画来进行排序。
操作步骤:
第一步:将光标放在行标签任一单元格,右键→【排序】→【其他排序选项】,调出排序窗口。
或还可以通过【开始】-【排序和筛选】-【自定义排序】,调出排序窗口。
或可以通过【数据】-【排序】,调出排序窗口。
第二步:在弹出的排序窗口中选择【升序排序】→【销售人员】→【其他选项】。
第三步:在弹出的其他排序选项窗口,取消勾选【每次更新报表时自动排序】→选择【笔划排序】→【确定】即可。
E.多级排序
要求:先按月份升序排序,再按销售员的销售数量降序排序。
操作步骤:
第一步:按月份排序。
①将光标放在行标签任一【月份】单元格,右键→【排序】→【其他排序选项】,调出排序窗口。
②在弹出的排序窗口中选择【升序排序】→【月份】→【其他选项】。
③在弹出的其他排序选项窗口,取消勾选【每次更新报表时自动排序】→选择【在主关键字排序次序的下拉列表中选择前面添加的自定义月份序列】→【确定】即可。
第二步:按销售员的销售数量降序排序。
将光标放在任一销售员的销售数量【总计】单元格,右键-【排序】→【降序】即可。
F.多关键字段排序
要求:按金额排序,如金额相同的则按数量大小进行排序。
操作步骤:
第一步:要想实现多关键字排序,只需要取消数据透视表中的自动更新排序就行了。
①点击数据透视表的行标签,右键-【排序】-【其他排序选项】。
② 在弹出的排序窗口中,点击“其他选项”,取消“每次更新报表时自动排序”的勾选,确定。
第二步:右击“数量”下方的单元格,排序选“降序”,再右击“金额”下方的单元格,排序选“降序”就可以了。
※由于取消了自动刷新排序的功能,如果数据源有新增或变化,透视表刷新后需要重新进行上面的排序操作。
(2)筛选
A.行列字段列表筛选
数据透视表的行字段和列字段自带筛选按钮(小三角▲),点击筛选按钮会弹出下拉列表,可根据需要勾选需显示的1个或多个类别。
B.标签筛选
除了字段列表筛选,还可利用标签筛选提供的多种选项,轻松完成平时需要公式自定义多条件,或者模糊筛选才能实现的功能。
C.值筛选
数据透视表除了对行列字段列表筛选,标签筛选外,还可以利用值筛选来筛选数值数据。
D.日期筛选
E.模糊筛选
利用通配符【*】和【?】可实现模糊筛选,其中【*】表示多个符,【?】代表1个字符。
※取消筛选
要清除筛选时,必须先选定已进行筛选的字段,否则清除筛选是灰色的,不能操作。
一次性全部取消筛选的方法:【开始】-【排序和筛选】-点击【清除】,即可全部进行清除。
五、技巧
1.快速拆分工作表 / 显示报表筛选页
A.有没有快速的方法,同时在一个工作簿中创建多个工作表并对其进行命名呢?
B.数据透视表就可以实现瞬间一表变N表。
操作步骤:
第一步:插入数据透视表,并将字段拖入筛选器。
第二步:单击数据透视表,点击【分析】-【选项】-【显示报表筛选页】,确定。
第三步:这时,Excel表格下方就会出现,以每个人的名字命名的批量创建的工作表。
第四步:按住Shift键批量选中所有工作表,然后在任意一个工作表中选中表格中不需要的数据,然后用Delete键将单元格清空。
或选择“开始”-“清除”-“全部清除”。
2. 显示数据明细/禁止查看数据明细
(1)显示数据明细
A.打开整张数据透视表的明细数据
双击数据透视表右下角位置,即可生成一张整个数据透视表对应的明细表。
B.打开数据透视表某条明细数据
对数据透视表中任意一项数据,双击鼠标左键,会生成一张新的工作表,可查看该数据的明细条目。
※当你查看以后,不再需要保存时;可以直接删除这张新生成的工作表就好,它不会对透视结果造成任何影响的。
(2)禁止查看数据明细
如果想让别人只看到创建的数据透视表,则仅仅删除数据源工作表是不能完全实现的,还需禁止显示明细数据功能,才能完全让他人无法查看数据透视表的明细数据。
操作步骤:
第一步:点击数据透视表的任意单元格,单击【分析】-【选项】。
第二步:在打开的数据透视表选项窗口中,单击【数据】-取消勾选【启用显示明细数据】功能。这样子就无法通过双击单元格查看数据明细了。
3.套用数据透视表样式
要想快速对创建好的数据透视表进行美化,可直接在已有的数据透视表样式中进行选择。
操作步骤:
选中透视表中的任意一个单元格,点击“数据透视表工具”--“设计”选项卡,可以在“数据透视表样式”列表里选择你喜欢的样式。
4.隐藏字段标题
点击数据透视表的任一单元格,单击【分析】-【字段标题】,即可隐藏字段标题。
5.错误值/空值的处理
对于错误值和空值,我们可以右键-【数据透视表选项】-【布局和格式】中,让它显示为自定义的内容。
6.快速去除求和项
在数据透视表中,只要是数值在值区间就会显示“求和项:”,这时我们可以将求和项批量删除。
操作步骤:
按Ctrl+H调出查找替换对话框,在【查找内容】中输入“求和项”;在【替换为】中敲一个空格,点击确定即可。
7.显示字段列表
方法一:右键单击透视表,点击“显示字段列表”。
方法二:点击数据透视表的任一单元格-【分析】-单击【字段列表】。
※数据透视表必记点:
(1) 数据透视表的快捷键是:AIT+D+P;
(2)双击数据透视表单元格可以调出相关明细数据表;
(3)更改源数据,需要在数据透视表里刷新;
声明:我要去上班所有作品(图文、音视频)均由用户自行上传分享,仅供网友学习交流,版权归原作者安夏Anxia所有,原文出处。若您的权利被侵害,请联系删除。
本文标题:(夫妻怎么插入交互式图表)(男人怎么插入交互式图表)
本文链接:https://www.51qsb.cn/article/dvjuxs.html