韦德娱乐1946:认知数据透视表,excel表格透视表

2019-09-11 17:22 来源:未知

问题:excel表格透视表怎么做?

(友情提醒,多图,请在wifi下观看!流量壕请无视本条)

1、Excel创建数据透视表:
选中任意一个单元格——插入——数据透视表——右击数据透视表选项--显示——勾选“经典数据透视表”
然后将数据透视表相关字段拖入相应的位置。

回答:

有些朋友后台给小奚留言说很喜欢之前讲的透视表内容,问能不能整理出一个Excel透视表的合集,只要是透视表的内容都能在里面找,今天小奚就给大家带来了这篇文章。

2、Excel更改数据透视表汇总方式 :
在左上角“求和项”上双击,然后更改为合适的计算类型。

style="font-weight: bold;">如果只能学Excel中的一项功能,这项功能就是数据透视表,可见数据透视表在Excel中的地位。

不用写公式,不用手工计算,数据透视表通过简单的拖拽就能完成各个维度你想要的数据分类汇总,可以说是基础的Excel操作里面最简单易上手,最实用,最常用的功能了。

3、查看具体数据:
双击单元格则可以查看具体的数据

数据透视表可以使用户通过简单的拖拽操作,完成复杂的数据分类汇总,可以说是Excel中最实用、最常用的功能,没有之一。

话不多说,上目录。本文内容较长,干货较多,大家可以根据目录挑选自己想看的内容。

4、创建透视表时可以拖入多个行字段,列字段。行字段右侧是左侧的下一级

今天星爷带你见识数据透视表的十八般武艺,零基础让你轻松入门数据透视表。

我是目录,目录是我

5、Excel数据透视表中的组合:
例如:根据日期将数据透视表中的日期汇总为季度
点中一个日期单元格,点击右键——创建组合,按季汇总

001 基本操作

NO.1 创建数据透视表

使用原始数据表进行各销售员对应各产品的销量。

根据你想要呈现的数据报表,透视一下,让你轻松“拖”出来。

韦德娱乐1946 1

韦德娱乐1946 2

看到没?

数据透视表的学习成本非常低,通过简单的拖拽就能完成数据的分类汇总。

很多新手学习数据透视表时会搞不懂字段、区域、筛选、行、列……等等这些乱七八糟的名词之间的关系。

我用一张图告诉你字段在各区域之间的对应关系。

韦德娱乐1946 3

数据透视表字段与区域之间的关系

NO.2 更改数据源

数据透视表的一个优势就是,数据源变更后进行简单的更新就可反映到数据报表中。有两种方式,第一种是数据透视表刷新:如果只是数据源中的数值进行了更新,采用刷新即可。

韦德娱乐1946 4

刷新数据透视表源数据

如果是数据源的结构或布局变更了,这时采用更改数据源的方式。

韦德娱乐1946 5

NO.3 插入计算字段

计算字段极大扩展了数据透视表的计算功能,属于神一般的存在。比如原始数据表中有一列数据为目标值,有一列数据为实际值,那么在数据透视表中可以通过计算字段输入公式=实际值/目标值,来求出汇总的达成率。

韦德娱乐1946 6

NO.4 使用切片器

这是制作动态图表的利器。

韦德娱乐1946 7

上篇:基础操作

6、数据区间的统计:
创建一个数据透视表,将需要统计的字段拖入到列字段和中心区域,
点中列字段中的任意一个单元格,创建组——调整相应的规格即可。

002 布局与格式调整

NO.5 更改值汇总依据

区域是数据透视表的核心部分,通过数据透视表提供的强大数据计算功能,可以使用多种汇总方式和值显示方式来计算值字段数据。

值汇总依据,就是你要用数据透视表对原始数据中的数值进行怎样的计算,比如求和、计数还是求平均值。

韦德娱乐1946 8

NO.6 更改值显示方式

数据透视表不仅可以按照不同的方式汇总数据,它还可以按照不同的方式显示数据,从而更清晰的看出数据之间的关系和逻辑。

①总计的百分比

总计百分比含义是:每一项分类汇总的值占总计的百分比

韦德娱乐1946 9

各项之和为100%

②父级百分比

总计的百分比关注的是个体占整体的情况,往往只有一个维度,除此之外,还可以展示局部百分比,比如求出每个销售员在各地区的销售额占比。

韦德娱乐1946 10

各小组里的项之和为100%

在Excel 2016中,数据透视表中共有14种“值显示方式”。

NO.7 更改数据透视表布局

默认生成的数据透视表布局比较呆板,有多个方面可以设置不同形式的布局。

韦德娱乐1946 11

01 创建数据透视表

7、Excel汇总多列数据 :
eg:
汇总每名学生语文成绩的总分,平均分,最高分以及最低分

003 数据透视表组合功能

数据透视表中有一个叫做“组合”的功能,一方面它能自动识别日期、数值等可计算的字段,然后按照给定的跨度进行组合,比如组合出按年、季度、月、日,甚至小时、分……的汇总;另一方面,也可通过手动选择的方式,将文本格式的数据按照自定义的方式进行组合,比如组合出一线城市、二线城市等等。

通过分组功能将这些不同数据类型的数据项按多种组合方式进行分组,大大增强了数据表分类汇总的延伸性,方便用户提取满足特定需求的数据子集。

NO.8 按时间维度组合

韦德娱乐1946 12

本图来自网络

NO.9 按数值组合

韦德娱乐1946 13

本图来自网络

时间维度和数值维度的组合,逻辑上都是一样的,都是基于大小进行分组组合,这两者可以成为自动组合,数据透视表还允许对文本类型进行自定义组合。

NO.10 自定义组合

还有一种组合方式叫作自定义组合,比如要组合的对象是文本字段,或者对日期进行非等距的组合。

比如:一线城市和二线城市销售情况分析

按住Ctrl键选择北京、广州、上海、深圳四个城市,然后点击“鼠标右键”选择“创建组”。接着,选中剩下的所有城市,点击“鼠标右键”→选择“创建组”,数据透视表按照我们所选定的内容进行组合。

韦德娱乐1946 14

通过这十个技巧,你掌握数据透视表了么?

style="font-weight: bold;">关注「精进Excel」,如果任意点开三篇文章,没有你想要的知识,算我耍流氓!

回答:

大家好,我是Excel大全,头条原创作者,每日分享实用Excel小技巧。

刚好看见这个问题,我就来分享下我的经验,希望能帮到你!

我将从以下方面回答这个问题:数据透视表怎么做?

  • 一个简单例子,使用透视表,无需任何公式,3步搞定分组统计;

  • 插入透视表的方式;

  • 透视表布局;

  • 分组统计;

  • 计算字段;

  • 切片器;

  • 透视图;

【问】:老板给你公司今年的订单明细,让你告诉他每个销售今年的销售额是多少?

姓名 学号 语文
张三 1 54
李四 2 76
王五 3 76
赵六 4 56
张三 1 54
李四 2 76
王五 3 76
赵六 4 56
张三 1 54
李四 2 76
王五 3 76
赵六 4 56
张三 1 54
李四 2 76
王五 3 76
赵六 4 56
张三 1 54
李四 2 76
王五 3 76
赵六 4 56
张三 1 54
李四 2 76
王五 3 76
赵六 4 56
张三 1 54
李四 2 76
王五 3 76
赵六 4 56

01 简单透视表,3个步骤搞定分组统计

  • 第一步,插入透视表;

  • 第二步,用鼠标拖动字段到透视表区域;

  • 第三步,设置行列标签分组统计方式,搞定!

韦德娱乐1946 15

通过这个例子,先了解下透视表的大致使用方式和透视表的强大之处。

看见了吗?

  1. 创建数据透视表,将姓名和学号都拖入带行字段中
  2. 双击姓名,分类汇总勾选“无”
  3. 将语文成绩拖入到透视表中4次,
  4. 双击表头,修改每一栏的计算类型,点击表头在公式栏中修改表头名。
    演示:

插入透视表的方式

有两种方式,可以插入透视表

  • 法1:菜单选择插入-透视表;

  • 法2:按下<Alt> D,松开,再按P

韦德娱乐1946 16

其中,方法1最常用,一般的统计分析基本上都能搞定;方法2,更多地用于多表合并情况;

创建透视表就是如此简单,通过拖拽,就能立马得到老板想要的结果。

韦德娱乐1946 17

透视表布局

透视表的布局有4个重要区域,行标签、列标签、值区域和筛选页;

韦德娱乐1946 18

看似很复杂一个界面,其实很简单,通过鼠标,将面板中的字段,拖拽到相关的区域,便完成简单的透视表。

若对透视表的布局样式不满意,还可以通过设计菜单中各种功能进行调整,如下所示:

韦德娱乐1946 19

可以直接套用样式,或者手动调整透视表的每个细节,如常用的传统列表的形式:

韦德娱乐1946 20

有很多初次接触数据透视表的同学对透视表的四个字段(筛选器、行、列、值)的意思不太了解,小奚用一张图告诉大家:

演示效果

分组统计、字段设置、计算字段

在行或者列标签字段上,右键鼠标,在菜单中,有一个组合功能,利用这个组合,可以完美地组合出各种统计维度,如,月报、季报、年报、按部门、地区等等;

韦德娱乐1946 21

而在值区域上右键,即可设置统计方式,如,计数,求和,平均等;

韦德娱乐1946 22

透视表中,还有一个计算字段,有了这个计算字段,我们就不需要修改数据源了

韦德娱乐1946 23

字段拖放在不同的区域,就会以不同的显示方式显示汇总的结果,并且同一个区域内的顺序不同,在数据透视表内汇总的先后层次也会不同。这也就是为什么透视表能完成各个维度的数据汇总。

8、Excel在透视表中使用计算:
数据透视表工具——选项——域、项目和集-计算字段-
eg:
名称:利润率
公式: =(合同金额-成本)/合同金额
设置单元格格式-数字-百分比

切片器 图表

切片器 图表=动态图表;

切片器,就类似一个多字段的筛选器

韦德娱乐1946 24

图表,就不用说了吧,一份报告最终的展示方式

韦德娱乐1946 25

那,切片器 图表,便是那些高手手中高大上的动态图表了。

9、怎样消除错误值:
任意位置右键-数据透视表选项--布局和格式-勾选“对于错误值显示” 后面不填写。
在数据透视表中加入公式计算一个字段,建议在表内加入,单击表中任意单元格,选项的公式中有一个计算字段。

透视表的刷新

这个差点给漏掉了,当数据源修改时,数据透视表得刷新下,才能同步更新哦!

韦德娱乐1946 26

02  透视表转普通表格

10、生成数量众多的工作表:
创建数据透视表——将需要分页显示的项目拉入页字段和值字段,
在数据透视表中点选项-点击选项右侧的小三角形———显示报表筛选页-选定要显示的报表筛选页字段。

好了,这个透视表的问题就先到这吧,希望能帮到你!

我是Excel大全,头条原创作者,每日分享Excel实用小技巧。

您的关注、转发、收藏、点赞、评论,都是对我莫大的支持,谢谢!

回答:

  1. 首先,选中原始数据区域,点击-。

    韦德娱乐1946 27

  2. 如果刚才已经选中了数据区域,在中最上面的选择区域框可以看到选中的单元格区域,如果刚才没有选中,也可以在此选取。

    韦德娱乐1946 28

  3. 数据透视表提供了在新工作表创建和在当前工作表创建的选择,如果数据透视表较大,内容很多,建议在新工作表中生成透视表。

    韦德娱乐1946 29

  4. 根据实际情况选择完上述两个设置后,点击按钮。

    韦德娱乐1946 30

  5. 这时,在excel中就出现了一个新工作表。左面是空白的透视表区域,右边是数据透视表字段列表,可以拖动和设置。

    韦德娱乐1946 31

  6. 数据透视表列表字段处显示的字段名称是原始数据区域的抬头,可以拖动到下面的四个框中。

    韦德娱乐1946 32

  7. 我们把字段拖到行标签位置,拖动到位置。这时,我们可以看到左方的数据透视表已经按照不同的人名将数量进行了汇总。

    韦德娱乐1946 33

  8. 行标签处可以插入多个,但是前后顺序要根据自己的需求来选择。比如说,我们要按人员和名称分别看汇总数,可以将字段拖到字段下面,结果如下图所示:

    韦德娱乐1946 34

  9. 上面的结果是2007版透视表的样式,我们可以通过如下图的设置改成2003版数据透视表的样子。显示样式根据自己的习惯决定。

    韦德娱乐1946 35

    韦德娱乐1946 36

  10. 如果不喜欢看到这些汇总数字,可以做数据透视表有汇总字样的单元格上鼠标右键,选择取消汇总数。

    韦德娱乐1946 37

    韦德娱乐1946 38

  11. 数据透视表数据还可以方便的生成数据透视图。

    首先,选中透视表上任意一个单元格,选择-。

    韦德娱乐1946 39

  12. 选择一个合适的图表类型,点击按钮。

    韦德娱乐1946 40

  13. 这时,就可以看到自动生成的数据透视图了,非常方便。

    韦德娱乐1946 41

    韦德娱乐1946 42

  14. 选中数据透视表上任何一个单元格,菜单栏上将激活和两个选项卡,可以对报表布局、样式等进行更多设置。

    韦德娱乐1946 43

    韦德娱乐1946 44

回答:

1 创建数据透视表、字段重命名、排序

韦德娱乐1946 45

2 手工也能排序:选中往下拖

韦德娱乐1946 46

3 必不可少操作:刷新

韦德娱乐1946 47

4 详解数据透视表布局

韦德娱乐1946 48

5 强大的筛选功能:最大,最小,介于都可以

韦德娱乐1946 49

6 怎么让数据动起来:将原数据设置为数据透视表下的“表格”

韦德娱乐1946 50

7 数据可以多次使用:方便各种统计

韦德娱乐1946 51

8 值可以排序,汇总数据也可以哦

韦德娱乐1946 52

更多财税职场和管理会计最新资讯,搜索关注管理会计小秘书(微信号:glkjxms)

回答:

Excel数据透视表实际应用之物料汇总

1举例:多个车型配置使用的物料向下排列没有合并用量。
韦德娱乐1946 53

2.鼠标点击右边空白处任意单元格-菜单插入-数据透视。

韦德娱乐1946 54

3.弹出创建数据透视表对话框-框选列标A到F -透视结果放置位置可以选在现有工作表-确定。
韦德娱乐1946 55

4.拖动字段到序号对应位置。
韦德娱乐1946 56

5.注意现在的数值是计数,我们汇总用量是求和,单击计数项-值字段设置-点击求和。
韦德娱乐1946 57

6.鼠标点击下汇总结果-菜单栏设计-分类汇总-不显示分类汇总。
韦德娱乐1946 58

7.设计-报表布局-点击以表格形式显示-点击重复所有项目标签。
韦德娱乐1946 59

8.汇总结果样式。

韦德娱乐1946 60

回答:

关于Excel表格做数据透视可以从以下10个基础入门及超级实战方面来学习。

韦德娱乐1946 61

【问】:小奚啊, 老板只需要最终的汇总结果,不需要把数据明细发给他,那么怎么把透视表转成普通表格发给老板呢?

11、删除因此产生的数据透视表的话:
选定所有报表(按住shift键,选中第一个个最后一个表),在第一个工作表中,复制空白单元格,覆盖数据表,这样就会清除所有数据透视表。

1、数据透视入门

韦德娱乐1946 62

韦德娱乐1946 63

2、随意调整分析角度,数据爱怎么看怎么看

韦德娱乐1946 64

韦德娱乐1946 65

韦德娱乐1946 66

这个很简单,只需要复制数据透视表,粘贴成就行了,如果喜欢透视表的格式还可以像小奚一样粘贴一下格式

(另:
1、创建数据透视表 一列是一个字段
2、日期中有空格 无法组合 没有空格 但文本组合 也不可统计组

3、除了求和,还可以做各种计数、平均、百分比等分析

韦德娱乐1946 67

韦德娱乐1946 68

4、切片器——超级便利的数据筛选按钮

韦德娱乐1946 69

韦德娱乐1946 70

03  更改值字段计算方式

5、数据组合——随意地合并统计

韦德娱乐1946 71

【问】:老板说我不仅想看每个销售这一年销售的总金额,还想看他们这一年签了多少笔合同。

6、双击出明细,点哪儿得嘛儿

韦德娱乐1946 72
韦德娱乐1946 73

在前面我们用到的是求和的计算方式,对于这个问题,我们可能就需要用到计数的计算方式了。

7、一键搞定动态数据源

韦德娱乐1946 74
韦德娱乐1946 75

订单编号是文本类型,我们可以看到将订单编号拖到值字段时Excel会自动计算,因为文本类型不能求和,而金额是数值,可以求和,也可以计数。

8、分类汇总很简单

韦德娱乐1946 76

除此之外,值字段的计算方式还有以平均值,最大值,最小值等计算,但是在我们平时的运用中,最常用到的还是计数和求和这两种。

9、行列表头合并居中,规范又美观

韦德娱乐1946 77
韦德娱乐1946 78

10、创新的实战应用:表间数据不规范不统一时应用数据透视极大提升对比效率

小勤:大海,现在公司手工盘点表可麻烦了,老跟系统的数据对不上,每次盘点对帐都得费半天劲儿。他们手工录的表里货品代码就经常少一个横杠、多一个横杠的,有的“文艺”干脆就写成“文”,对起来真的很麻烦。

韦德娱乐1946 79

大海:的确很难避免这种情况,盘点任务经常是分配给不同人去完成的,现场盘点人员水平参差不齐,都是自己手工习惯的表,短时间估计也很难规范统一,数据汇总后就有各种问题,你很难用公式去匹配。这个时候其实可以考虑用数据透视,先对大类,看看哪些大类是对不上的,然后再针对有差异的大类对明细,缩小对照范围。你现在可以试着把2个数据透视都放到一张表里看看。显然,大类的时候“厨具”、“卫生”、“文艺”三类都有差异。

韦德娱乐1946 80

我们再把细类放进来,然后把没有差异的大类通过筛选去掉。

韦德娱乐1946 81

用同样的方式筛选手工盘存数据透视表后进行对比,细类里有差异的地方也就这些了。

韦德娱乐1946 82

小勤:嗯,现在范围很细了,只要核对“锅”、“卷纸”和“相册”就可以了。

大海:对的,这个时候双击出明细就好用了。比如先核对“锅”的。分别双击“锅”的统计数据,生成明细(为避免搞混2个明细表,生成时最好重命名一下):

韦德娱乐1946 83

明细如下:

韦德娱乐1946 84

为方便2表之间的核对,可以新建窗口:

韦德娱乐1946 85

然后重排一下:

韦德娱乐1946 86

在2个垂直并排的窗口中分别选中系统表和手工表进行对比:

韦德娱乐1946 87

显然,差异就是这个了。用同样的方法可以找到其他差异的地方。

小勤:嗯,这样一步步缩小范围,对不规范的数据对比真能省不少功夫。

大海:对的,所以表间的数据对比得看情况。如果数据都很规范,那就简单了,当数据不规范的时候,就要想办法逐步缩小范围。


欢迎关注

私信我即可下载60 基础函数、10 数据透视及新功能Power系列功能汇总训练材料

我是大海,微软认证Excel专家,企业签约Power BI技术顾问

让我们一起学习,共同进步

回答:

要做excel表格透视表,首先从创建数据透视表开始。

04  插入计算字段

创建数据透视表

步骤:选中数据,点击--,选择位置。再直接拖动字段到指定区域。

韦德娱乐1946 88

【问】:老板说,我还想知道每个销售以现在的年销售额他们的提成能拿多少(按千分之七来算)?

增加新字段

如下图,表中已知销量和单价,可增加并计算出销售额。

步骤:点击数据透视表,--,输入,输入,点击添加。韦德娱乐1946 89

这里我们有一种偷懒的做法,直接在透视表外面乘以0.007,对于这种方法会出现两个问题:

刷新数据

原表格数据进行修改,数据透视表可以刷新数据。

步骤:1、选择--,选择位置。

2、修改数据后,点击数据透视表,-。韦德娱乐1946 90

第一,有的同学会发现写完公式下拉后,数字并不会改变,仍然是第一个的计算结果,对于这种情况可以如此操作:【文件】-【选项】-【公式】-取消勾选【使用GetPivotData函数获取数据透视表引用】,如下图所示。

仅对行数据汇总

数据透视表可以仅对行数据汇总。

步骤:如图,对进行求和,数据透视表,将拖动至,出现行与列的总计。点击--。韦德娱乐1946 91

但即使是解决了第一个问题,也会发现一旦改变透视表的结构,我们的计算结果就不能使用了,因为它在透视表外面不会随着透视表的改变而改变。

批量创建工作表

数据透视表可以快速创建多个工作表。

步骤:1、输入需要创建工作表的名称;

2、选择-,勾选之前所输入的工作表名称,拖动到;

3、选择---。韦德娱乐1946 92

回答:

n

{!-- PGC_VIDEO:{"thumb_fingerprint": 7651297475218376071, "vid": "273e3957441f4ab1940901fa83d21f51", "vu": "273e3957441f4ab1940901fa83d21f51", "duration": 4242, "thumb_height": 360, "thumb_neardup_id": 9076112635957273149, "neardup_id": 5382587299326459173, "video_size": {"high": {"duration": 4242, "h": 480, "subjective_score": 0, "w": 640, "file_size": 58412277}, "ultra": {"duration": 4242, "h": 720, "subjective_score": 0, "w": 960, "file_size": 79551849}, "normal": {"duration": 4242, "h": 360, "subjective_score": 0, "w": 480, "file_size": 50413565}}, "vname": "\u7b2c\u516d\u8bb2.wmv", "hash_id": 5382587299326459173, "status": 0, "media_id": 1579335960823821, "thumb_width": 480, "external_covers": [{"mimetype": "webp", "source": "dynpost", "thumb_height": 360, "thumb_url": "5c180000c6346295ea17", "thumb_width": 480}], "item_id": 6514912803189424654, "user_id": 6278328253, "thumb_url": "5c1000141164c2849957", "md5": "3996ff8940b5010a2a166c3805f92d50", "vposter": "", "src_thumb_uri": "5c1000141164c2849957", "sp": "toutiao", "group_id": 6514912803189424654} --}

所以,其实最好的方法是在Excel里面插入计算字段。

创建数据透视表、改汇总方式、汇总多列、自动创建透视表,看完视频全懂了!

私信回复24获取课件,边看边练!

回答:

选择需要做成透视表的数据源区-->进入“插入“-->”数据透视表"-->点击即可完成.

韦德娱乐1946 93

05  透视表排序与筛选

【问】:老板说,把每个销售以销售额降序排列,同时,给我筛选出总销售额排名前10的销售。(老板,您的需求不能一次说完吗?)

降序排列其实比较好办,只需要选中金额所在的地方,右键选择降序排列即可。

如果存在多个字段的情况下,怎么筛选出老板想要的呢?直接使用行标签去筛选是行不通的,解决方法是:将鼠标点在列标签外面一格,然后使用筛选功能,具体操作请看下图。

06  行列百分比汇总

【问】:前面说的都是数值,但是我想看百分比怎么办?

想要值以百分比的形式显示可以:【右键】-【值显示方式】-可选择相应的百分比选项。

数据透视表有多种数据百分比的显示方式,下面我们只挑选最常用的:总计百分比、行/列汇总百分比和父级汇总百分来看。

总计百分比

当我们分析各个项目占总值的百分比时,就可以用总计百分比。例如:老板想看销售1部在服装这个产品占整个公司的销售额的多少。

行/列汇总百分比

当我们想看某个数据在行字段或者列字段的垂直维度上的占比时,就需要用到行/列汇总百分比。

列汇总百分比,例如:老板想看每个销售在单个产品上的占比是多少,我们就需要拉列汇总百分比给他看。

行汇总百分比,例如:老板想知道单个销售在每个产品的上售卖金额占比是多少,我们就需拉行汇总百分比给他看。

父级汇总百分比

当我们不想看某个数据占全部的占比,只是想看它在细分维度的占比的时候就需要用到父级汇总占比。

例如:老板说我想知道王麻子的销售额在他们团队占比是多少。

07  修改行列字段顺序

【问】:小奚,透视表自己出来的行列字段的顺序有的时候并不是我们想要的顺序,是不是只能【右键】-【移动】-【上移/下移/移至开头或结尾】?

其实小奚曾经也是这么干的,当时字段还特别多,小奚拿着鼠标点右键,上移下移了无数次,差点没崩溃。

这时领导从小奚身后飘过,实在看不下去了,说了一句:“我都是直接拖就可以了。”此刻小奚的内心戏是:“领导,我错了,是我学艺不精,你就当我是在练手指的灵活度吧!”

恩,忍住笑,严肃脸。下面来看领导是怎么拖的,上动图(前部分为上下移动的操作方式,后部分为直接拖动的操作方式)。

丢了这么大个人,不扳回一城不是小奚的风格啊,所以小奚又潜心学习,一个关于修改字段的更高阶技能出现啦!

这个技能对于反复使用的字段比较方便,平时若用得少,直接拖动会更方便一些。

【先做一个辅助表】-【文件】-【选项】-【高级】-【常规】-【编辑自定义列表】-【选择最开始建立的辅助表】-以后就只需要排序就能按我们希望的字段顺序出现了。

08  刷新与更改数据源

刷新

【问】:如果我想修改源表的数据,透视表会自动更新吗?

默认是不会自动更新的,需要手动刷新,如果是在原基础上修改,不增加行列的话,我们只需要刷新就可以了,如果有多个数据透视表可以选择全部刷新。如果害怕自己忘记刷新,也可以设置【打开文件时刷新数据】

更改数据源

【问】:我的数据源表修改了,刷新了也没有出现我修改的东西怎么办?

这种情况,就是我上面说的增加了行或者列啦,只是刷新是不行的,还需要更改数据源。

更高级的用法

有的时候我们并不想每次都去修改数据源,那太过于麻烦。又怎么办呢?

把数据源把设置成“表格”就可以解决这个问题。

只要把数据源表设置成“表格”,不管增加行还是列都不需要再去更改数据源,只需要刷新即可(注意,只针对将数据源更改为“表格”之后建立的透视表有效,这也是为什么在动图的例子里小奚要重新建透视表的原因)。

并且通过动图可以看到,设置成“表格”后,如果在首行输入公式都不需要下拉,会自动匹配。

09  透视表的复制与删除

【问】:我想复制或者删除透视表怎么办?删除必须要把整个sheet删除掉吗?

复制透视表的情况其实蛮常见的,因为有的时候选取的数据源是相同的,需要做不同维度的汇总分类,如果不想重新新建sheet,那么复制透视表后在这基础上更改字段是最好的方法。

只需要全选透视表,复制粘贴即可。

删除透视表只需要全选透视表,直接按detele键就能全部删除。

中篇:美观与布局

10  透视表的三种布局

【问】:做为一个有追求的员工,只会Excel透视表默认的呆板展示方式简直不能忍,好吗?小奚呀,为什么别人的透视表和自己的就是长得不一样呢?

那我们就要从透视表的三类展示姿势开始说起了,这三类布局分别是:压缩形式、大纲形式、表格形式。在哪里找到这三类布局呢?

【设计】选项卡-【布局】菜单栏-【报表布局】(注意哟,给透视表穿上美丽外衣的大多数功能都是在【设计】选项卡实现,这里也是我们今天的主要阵地,大家可以自己研究研究这个选项卡的内容噢!)

压缩形式

有眼尖的同学已经发现啦,其实压缩形式就是我们Excel默认的透视表格式,它主要的特点呢就是:

无论叠加多少个行字段,都只占一列。如果对这个概念还不是特别明白,可以多和下面讲到的两个布局方式做对比,相信你很快就能明白啦。

大纲形式

大纲形式与压缩形式最重要的区别就是:大纲形式有几个行字段就会占几列,即行字段会并排显示,就如我们例子中的行字段有三个,那么大纲形式的布局就会占三列而不像压缩形式只占一列。

另外,大纲形式的分项汇总显示在每项的上方。

表格形式

表格形式的透视表是小奚最常用的一种形式。它的主要特点呢是:

1、与大纲形式一样,行字段会并排显示,有几个行字段会占几列;

2、与大纲形式不同的是,表格形式是有表格的(好像在说废话,记得看图找区别噢);

3、与大纲形式第二个不同是表格形式的分项汇总是在每项的下方,而大纲形式是在上方。

以上的三种布局形式的特点,你都了解了吗?

11  显示和隐藏分类汇总

【问】:小奚啊,你的例子里面,我只想看每个销售细分到各省份客户的销售额,并不想看销售汇总的销售额,并且那些销售汇总放在里面看得我眼花,怎么解决呢?

嗯,确实是这样,有的时候我们并不需要看分类汇总,但是透视表会自动显示,有的时候我们甚至都不需要看总计,那么怎么隐藏和显示分类汇总和总计呢?还是在【设计】选项中哦!

看完动图大家应该比较清楚在哪里显示和隐藏分类汇总和总计了吧?

不过细心的同学应该已经发现动图里一个小问题了吧?在这里小奚要考一考大家哟!

【提问】:为什么小奚选择的是在组的顶部显示分类汇总,最后Excel还是在组的底部显示的分类汇总呢?要结合我们前面讲的内容哦,知道答案的同学请大声在留言区告诉我吧!

12  标签项重复显示

【问】:对于大纲形式布局和表格形式布局,因为行字段是并排显示,特别是前面的行字段,常常一个就对应了后面多个字段,让表格不好看,怎么优化呢?

哇!这个问题好,在这里小奚要告诉大家两个优化的方法:第一个是让我们的标签项重复显示,第二个是合并行标签。

先讲让标签项重复显示:【设计】选项卡- 【布局】-【报表布局】-【重复所有项目标签】。(注意,标签重复项只对大纲式和表格式有效,对压缩式无效,想想为什么?)

13  合并行标签

合并行标签也是回答上面的提问,这是另外一个优化行字段并排显示的方式。

当然,大多数的同学可能会对合并行标签更感兴趣一点,因为小奚发现,大多数的同学在操作Excel的时候,对合并单元格尤为热衷。(虽然合并单元格一直被称为Excel处理数据时的一大杀手)

好了,废话不多说,直接上操作:【右键】-【数据透视表选项】-【布局和格式选项卡】-【合并且居中排列带标签的单元格】(注意:合并行标签只对表格形式布局有效,对大纲式和压缩式无效,不信你可以试试哟!)

14  插入空行间隔

【问】:小奚啊,我们公司的透视表数据很多,老板说看得他密集恐惧症都要犯了,该怎么办呀?(同学,你是认真的吗?)

我们在每一项之间插入空行,对于透视表数据很多的情况可能会有所帮助。

具体操作:【设计】选项卡- 【布局】-【空行】-【在每个项目后插入空行】

对于插入空行,在展示的时候会比较好,因为看起来让人不那么累。但是在日常处理数据的时候,空行会带来一些麻烦,所以处理数据时还是建议大家不插入空行哟。

15  取消字段前 /-符

【问】:透视表行标签前面总是有 /-符号,看起来有些丑,可以隐藏吗?

当然可以呀,上操作:【分析】选项卡-【显示】-【 /-按钮】

16  刷新后格式保持不变

【问】:前两天一个同事一脸悲痛地来找我,说透视表的排版布局我都做好了要给领导看,行高和列宽都需要固定,不能变,但是每次我一刷新透视表列宽和格式就全变了,要崩溃了。

不知道工作中你是否也遇到了这样的情况呢?不要心急,一招就能帮你搞定:

【右键】-【数据透视表选项】-【布局和格式选项卡】-取消勾选【更新时自动调整列宽】

17  透视表模板套用

【问】:透视表模板套用是不是让透视表变美观的东西呀?

真聪明,在Excel里面已经内置了一些透视表的模板,我们可以选择自己喜欢的模板,直接套用就行了,还是在【设计】选项卡里面。

最后的最后,关于美观与布局,还可以给透视表更改好看的字体和字号,例如微软雅黑等,再结合以上的内容,立马让你的透视表秒杀同事的呆板透视表。

下篇一:分组

18 文本分组

【问】:小奚,老板除了看一个省的销售情况,还希望看一个区域的情况,比如:想看西区的销售情况(西区包含:贵州、湖北、陕西、四川、云南、重庆),该怎么办呢?

对于这种情况,其实有两种方式,一是在数据源表里面去添加区域的辅助列,二是不添加辅助列,直接在透视表里面创建文本分组啦。

按住Ctrl键选择贵州、湖北、陕西、四川、云南、重庆,然后点击“鼠标右键”选择“创建组”数据透视表按照我们所选定的内容进行组合。接着可以修改组的名称,例如改为西区。

19 日期分组

【问】:既然可以Ctrl键选中想要分组的内容来分组,那么如果对日期来分组是不是也可以这样呢?

严肃地讲是可以这样的,以前领导让小奚拉一个月的数据,小奚选中了这个月的每一天然后创建了分组,但这样做有点傻,因为Excel是很智能的,能够对日期进行识别,对于日期的分组,我们有个简便的方法。

选中日期,右键选择“创建组”,就可以按照我们的需求进行月、季度、年的汇总啦!

怎么样?Excel还是很智能吧?

20 数值分组

【问】:1、小奚,我是一个HR,要对员工进行工龄汇总,5年为一个工龄段,有什么简单的方法吗?

2、小奚,老板需要对销售的金额段分析,该怎么分金额段呢?

那就需要用到透视表的数值分组啦,依然是选中司龄或者金额,右键选择“创建组”,在组合中可自己设置起始和结尾以及步长,如有不清楚,请看动图吧。

司龄分组:

金额段分组:

大家发现了吗?透视表只能按照等距步长来组合哟!比如司龄的步长设置为5,那么只能按照5的间隔来组合。

但是在实际运用中,特别是对于金额段的实际运用,我们常常需要的是不等距组合,比如:1000-2000金额段,2000-5000金额段。对于不等距的组合我们该怎么操作呢?

不等距组合有两种操作方式:

一是按照18分本分组讲的Ctrl选中做组合的方式创建不等距组合,这种方式对于数据量小的时候,还能操作,对于数据量大的时候其实是蛮绝望的。

第二种方法是需要在源表建立辅助列,然后用VLOOKUP的模糊匹配,这个我们会在下一次讲到。

下篇二:数据表出错原因

常常有人来问我为什么我的透视表出问题了呢?其实透视表出错的原因绝大多数都是因为咱们的数据源表出了问题。

21  某些列没有标题

【问】:我无法创建数据透视表,显示透视表字段名无效是什么意思啊?

这种情况是因为我们的数据源表某些列没有标题,这种情况常见于前一列的标题过长,覆盖了没有标题的那一个让我们误以为都是有标题的,具体请看动图。

操作:需要我们把数据源表的标题行补全就可以正常创建透视表啦。

22 存在合并单元格

【问】:我的数据源表中明明没有空白,但是在透视表中却出现了空白是怎么回事呢?

这可能是数据源表中存在合并单元格,Excel会将除了合并单元格的第一格以外的全部当做空值处理。

操作:取消合并单元格,并填充完整,再刷新数据透视表就可以看见没有空白啦。

23 日期非法

【问】:为什么我不能对透视表的日期进行月、季度、年的分组呢?

检查一下源表,看看是否存在非法日期?例如:20170101、2017.1.1等。

操作:回到数据源表,选中日期列-分列-选择日期-完成。就可以看到日期被变成了标准格式,再去透视表刷新,就可以按日期创建组啦!

24  数据源存在文本类型的数字

【问】:小奚,为什么我的源表里面明明是数字,但是透视表里只能计数,求和的结果总是显示0呢?

同样的我们需要去检查一下数据源表,看看是不是用文本形式保存的数字呢?

操作:将以文本形式储存的数字按数值类型储存,方法是:选中-分列-完成,刷新透视表即可。

那我们来总结一下,在创建透视表的时候出问题绝大多数情况下需要我们去检查源表,看看是否:某些列没有标题、存在合并单元格、非法日期以及文本类型存储的数字

学完本文,几乎就能解决Excel透视表绝大多数的问题啦!

还在看什么?赶紧动手操作吧!

如果你觉得本文对你有所帮助,请尽情收藏或转发吧!欢迎转载,另外,若想要本文案例数据源欢迎简信撩我。

END

TAG标签: 韦德娱乐1946
版权声明:本文由韦德娱乐1946_韦德娱乐1946网页版|韦德国际1946官网发布于韦德娱乐1946,转载请注明出处:韦德娱乐1946:认知数据透视表,excel表格透视表