51福利网
各种薅羊毛福利分享福利网

它是excel中最有用的功能,没有之一。数据透视表入门教程(1~14)

Excel表格里三足鼎立:excel技巧操作千万变化;excel函数以不变应万变;excel VBA无所不能。三者各有长短,互不相下。其中excel技巧阵营中的“老大”非“数据透视表”莫属。下面是兰色编写的Excel数据透视表入门教程,共14集。

第1讲:什么是数据数据透视表?

51福利网

第2讲:制作数据透视表

第3讲:源表的整理

第4讲:数据透视表的结构

第5讲:值的计算方式

第6讲:数据透视表的布局方式

第7讲:添加和删除汇总项

第8讲: 数据透视表的美化

第9讲:修改引用数据源

第10讲:数据透视表的刷新

第11讲:数据透视表的移动和删除

第12讲:筛选数值项

第13讲:固定数字和表格格式

第14讲:选取

第1讲:什么是数据数据透视表?

在学习excel数据透视表之前,我们有必要了解它的概念。什么是数据透视表呢?

理论上讲,数据透视表是excel提供的一种交互式的强大数据分析和汇总工具。兰色把它总结为:数据透视表是可以把一个明细表进行分类汇总,而且可以随意改变汇总模式的一种工具。

明细表?分类汇总?随意改变模式?工具?神马东东?听起来还是有点抽象,还是看图比较真观。

销售明细表:

根据地区和销售员汇总 根据地区、销售员、商品名称汇总 根据月份、地区、销售员、商品名称汇总

好吧!因为篇幅关系只能展示这几种汇总方式。也许你认为我函数水平高超,我会说我一个函数和公式也没用;也许你会说你是不是用了传说中的宏,对不起,我没写一句代码。以上。。。全是excel数据透视表实现的,而且展示的也只是数据透视表最最最。。基础的汇总功能!!

嘿嘿~~是不是动心了呢?你不要怀疑你是否能学会,因为学习数据透视表不需要基础,只要跟着兰色学习,一定可以学会的。

第2讲:制作数据透视表

第一讲中我们简单介绍了什么是excel数据透视表,关于数据透视表的基本概念还有很多,如果现在拿来全讲,估计同学们听起来会象天书一样。兰色想了想,基本的概念我们可以在后面逐步介绍,我想大家现在最想做的,就是象第一讲中,根据数据表自已亲手创建一个数据透视表。

做数据透视表之前,我们需要了解以下两个问题:

1、 什么样的表格需要做数据透视表

数据透视表的用途如果细数会有很多,但最基本、最常用的是“分类汇总”,所以当我们需要对一个流水账式的明细表进行分类汇总时,就需要使用数据透视表。数据透视表的源数据即是“明细表”。如第1讲中的销售明细表。

2、 什么样的格式才能做出数据透视表

在制作数据透视表,需要注意以下几点:

1 空字段名包括空列,无法制作数据透视表
2 相同的字段名,会自动添加序号,以示区别
3 字段所在行有合并单元格,等同于空字段,也无法创建数据透视表
4 如果有空行,会当成空值处理

如果数据源表不规范,需要处理一下才能制作数据透视表,数据表的整理我们将在第3讲介绍。

下面我们就要亲手制作数据透视表了。

原材料:销售明细表(点微信右下角阅读原文 下载示例表格)

制作步骤:

第1步 选取销售明细表,excel2003版 : 数据菜单 – 数据透视表(Excel2010版:插入选项卡 – 数据透视表 )

excel2003版

excel2010版 第2步 执行“数据透视表”命令后,会进入数据透视表创建向导。(excel2003和excel2010稍有差异,但大体选项是一样的)在向导的第一步一般是默认选项,不需要设置,直接点确定即可(excel2003版在第一步点“完成”按钮)。 第3步 进行前两步后,会自动新建一个工作表,且在工作表中会有一个数据透视表空白区域,就象盖楼房地基已打好,需要我们安排具体的房间位置。

添加数据透视表项目

  •  把地区拖动到行标签
  •  把销售量和销售金额拖动到数值区域。

注意:excel2003版和excel2010添加方式不同,是选取字段,直接拖到数据透视表中的相对位置。如下图所示。 拖动完成后,一个数据透视表的雏形已展现在我们的面前,如下图所示。

在欣喜自已成功创建数据透视表之余,同样会有很多疑问伴随而来:什么是行标签?如何修改数据透视表的汇总方式?如何修改数据透视表的格式…呵呵,别急..一口吃不成胖子,以后兰色会一一帮你揭开谜底。

第3讲:源表的整理

制作成数据透视表之前,你需要看看你的明细表是否存在下面的问题:

1、存在空列或没有行标题。

影响:明细表如果有标题为空,将无法制作数据透视表。

整理: 把标题补充完整。

2、存在相同的标题。

影响:数据透视表会自动添加序号以区分

整理:尽量不要存在相同的列标题。

3、存在合并单元格

影响:合并单元格除第1个格外,其他均作为空值处理。

整理:取消合并单元格,填充完整。

4、存在非法日期。

影响:生成的数据透视表中,无法按日期格式进行年、月、日格式的筛选和组合。

整理:转换成excel认可的日期格式。方法:选取列 – 分列 – 第3步选日期

5、存在文本型数字

影响:文本型数字将无法在数据透视表中正确求和。

整理:转换成数值型数字。方法:选取列 – 分列 – 完成

综上所述,一个无空标题行、无重复行标题、无合并单元格、无非法日期和数字格式的数据表,才是一个标准的数据透视表的数据源。

第4讲:数据透视表的结构

今天我们要了解数据透视表的基本结构。

1 、字段列表。明细表的所有第一行列标题都会显示在“字段列表”中。相当于数据透视表的原材料基地。

2、 字段设置区域。把要显示的字段拖放在相应区域内。相当于数据透视表的加工厂。加工的方式把相应的字段拖入。 3、 数据透视表的显示区域。组合成的数据透视表会在这儿显示。

  • 报表筛选区域(excel2003版称为页字段)。如上图所示,可以按“月”筛选数据透视表要显示的内容。
  • 行标签(2003版称为行字段)。该区域的字段会上下排列显示,如上图中“销售地区”和“销售员”字段。
  • 列标签(2003版称为列字段)。该区域的字段会左右排列显示。如下图中的“地区”。
  •  数值。要统计的数据一定要放在该栏内才会进行求和等统计。如上图中的“销量”。

字段拖放在不同的区域,就会以不同的显示方式显示汇总的结果。而且同一个区域内的顺序不同,在数据透视表内汇总的先后层次也会不同。同学们可以拖动转换一下各个区域的字段内容,同时观察数据透视表的变化。

在拖放时会遇到很多新的问题,明天的微信中我们将介绍字段的设置

第5讲:值的计算方式

上讲学习了数据透视表的基本结构,其中一个重要的组成部分是“数值”区域。我们需要把要求和的数值拖放到“数值”框内进行求和,如下图所示。可有时结果却显示“计数”。

原因:如果把含文本格式内容的列拖放到值区域,统计方式默认“计数”,显示为“计数项:字段名”。本例中,一是误把销售员放在了值区域,二是在销售量列可能含有文本型内容。

处理方法:把销售员从值框中拖出去(删除),然后双击“数量”列标题,在弹出的“值字段设置”窗口中,把“计数”修改为“求和”,如下图所示。

结果如下图

从上图也可以看到,数据透视表值的统计方式,不仅有求和、计数,还可以进行最大值、最小值、标准偏差等计算。

本讲最后介绍一个小技巧,上图中,如果你想把“求和项:数量”改成“数量”会提示无法更改,解决方法是可以在字段名后添加一个空格。即“数量 ”。

第6讲:数据透视表的布局方式

成数据透视表后,如果你是excel2003以上版本,你会看到多列的内容会显在一列中,如下图所示。

如果你想让列分开,就需要调整数据透视表的报表布局。选取数据透视表 – 在顶部找到“设计”选项卡 – 再打开报表布局下拉菜单,会看到5种不同的布局方式:

1、 压缩式布局。如本文开始所示。

2 、大纲式视图。特点标题分列显示。汇总显示在每项的上方

3 、以表格显示形式。特点:标题分列显示。汇总显示在每项的下方 4、 重复所有项目标签。可以填充相同的内容。大家注意下图和上图的区别。

5、 不重复所有项目标答。取消重复。是4的逆操作。

第7讲:添加和删除汇总项

默认情况下,在数据透视表中添加行或列都会自动添加汇总项,如何调整隐藏或显示汇总行/列呢?

1 、总计行

总计行分为行总计和列总计,如下图所示。

控制它隐藏和显示的命令在 数据透视表右键 – 选项 – 如下图所示。(2003版在数据透视表上右键 – 表格选项 – 行和列总计选中)

2、 分类汇总行

分类汇总主要是指行或列标签的分类汇总,如下图所示。

想隐藏汇总,在数据透视表相应的列上右键,取消“分汇汇总..”勾选,如果要恢复操作相同。

第8讲 数据透视表的美化

新创建的数据透视表,就是一个无任何美化效果的表格。今天学习快速给数据透视表穿上美丽的外衣。

原始表格。

操作步骤:

1、 打开 选取数据透视表 – 顶端数据透视表选项 – 设计 – 如下图所示。

2、 点设计 – 其他 – 打开更多数据透视表模块,点击后即套用该模板。 设置后的效果如下图所示。

第9讲:修改引用数据源

如果明细表中行或列数发生了变化,数据透视表一般是不会自动调整数据源的,而需要手工去调整数据源的数据范围。方法是:

excel2010版选取数据透视表 – 最顶端数据透视表选项 – 更改数据源 – 重新选取范围。

excel2003版在数据透视表的右键菜单中,有“数据透视表向导”,打开数据透视表向导后再点“上一步”就可以重新选取数据源了。

开始兰色说“一般”不会自动更改数据源,难道有“二般”的情况??

还真有,方法就是把明细表设置为“数据表”(2003版称为列表),设置步骤是:选取明细表区域,excel2010版插入 – 表格。(excel2003右键菜单中“创建列表”)

设置完成后,再次增加新数据或新列,数据透视表就会自动更新数据源了。

第10讲:数据透视表的刷新

当明细表更新数据后,以该明细表制的数据透视表也需要刷新才可以显示新的汇总数据。

在数据透视表上的左键菜单上,点“刷新”即可完成数据刷新,如下图所示。

如果excel文件中有很多数据透视表,可以添加“全部刷新”按钮。方法是

文件 – Excel选项 – 快捷访问工具栏 – 左侧栏中找到“全部刷新” – 点击“添加”按钮即可添加到“快捷访问工具栏”如下图所示

点击“全部刷新”命令可以刷新本工作薄所有数据透视表,如下图所示。

第11讲:数据透视表的移动和删除

本讲学习数据透视表的移动和复制和删除。

一、 移动和复制

选取包含数据透视表的区域,把光标放在选取区域边缘后,直接拖动到指定的区域放下即可。如果在拖动时按ctrl键。移动变为复制数据透视表。

二、 删除

选取包括所有数据透视表元素的区域,包括透视表筛选区域和行列及数值标签区域,按del键即可删除。

第12讲:筛选数值项

数据透视表中如果需要对值进行筛选,我们却无法在值上面找到筛选按钮,如下图所示

要筛选值,可以借助行标签的筛选值功能。比如筛选数量大于30的行。

步骤:

打开最右一个行标签的按钮,值筛选 – 大于 – 输入30,点确定后即可筛选出我们需要的结果。

设置筛选条件 筛选后的结果如下图所示。

另:选取“求和项:单价”右边单元格 ,执行 数据 – 筛选命令,即可使用excel的自带筛选功能。如下图所示。

第13讲:固定数字和表格格式

在数据透视表中,好不容易设置的数字格式,以及列宽,在刷新后全部恢复了原状,设置的格式全部消失。怎么才能固定格式呢?

解决方法:

在数据透视表上右键 – 数据透视表选项 – 去掉“更新时自动调整列宽”,勾选“更新时保留数字格式”

可能大家发现,即使进行了上面的设置,更新时 设置的数字格式还是会变。比如下图中,设置了广州的数字是“千位分隔符”样式,但地区换成“苏州”时,还会有的数字非千位分隔符样式。

解决上述问题的方法:把地区放在行标签,然后统一设置数字的格式为千位分隔符样式。然后再筛选所有地区,数字格式均为千位分隔符样式。

第14讲:选取

数据透视表有值、行标签等元素构成,怎么能快速选取它们呢?

1、全选数据透视表

光标放在数据透视表上,最上面数据透视表工具 – 布局 – 选择 – 整个数据透视表。(excel2003版右键菜单中就有选择的子菜单)

2、 选取值或行列标签部门。

在第1步中我们发现除了全选外,其他的都是灰色。这是因为当你选取的范围内包括值等元素时,这些才能用,所以要选取这些内容,先全选,然后再选值或行列标签。

3、选取某行、某列

当你把光标放在数据透视表的某行或某列前面时,你会发现光标变成了黑箭头。这时你只需要单击就可以选取所有和本行列有关的行或列了。

兰色说:学会以上内容,恭喜你可以说你会用数据透视表了,但离精通还有一段距离的哦。

赞(0) 打赏
51福利网薅羊毛福利具有时效性,如有失效,请留言
文章名称:《它是excel中最有用的功能,没有之一。数据透视表入门教程(1~14)》-51福利网
文章链接:https://www.51wuditu.com/1861
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!

Hi, 请登录     我要注册

亚青文化·51福利网最新最全薅羊毛,现金红包线报网

51联盟线报群赚钱·合作·帮助

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏