外链论坛

 找回密码
 立即注册
搜索
查看: 17|回复: 1

数据透视表的运用办法(入门+进阶共14集)

[复制链接]

2979

主题

312

回帖

9909万

积分

论坛元老

Rank: 8Rank: 8

积分
99099268
发表于 2024-10-1 10:23:30 | 显示全部楼层 |阅读模式

前言:平时咱们看到的都是数据透视表的运用小技巧,很少有系统的讲解数据透视表的知识点。这次兰色把原来发布的数据透视表图文教程整理到一块,共14集,期盼对新手有所帮忙

Excel表格里三足鼎立:excel技巧操作千万变化;excel函数以不变应万变;excel VBA无所不可。三者各有长短,互不相下。其中excel技巧阵营中的“老大”非“数据透视表”莫属。从今天起始,兰色幻想将和朋友一块踏上excel数据透视表的学习旅程。

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

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

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

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

营销明细表

按照地区汇总

按照地区和营销汇总

按照地区、营销员、商品名叫作汇总

按照月份、地区、营销员、商品名叫作汇总

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

嘿嘿~~是不是动心了呢?你不要可疑是不是能学会,由于学习数据透视表不需要基本,只要跟着兰色学习,必定能够学会的。

第2讲:制作数据透视表

第1讲中咱们简单介绍了什么是excel数据透视表,关于数据透视表的基本概念还有非常多倘若此刻拿来全讲,估计朋友们听起来会象天书同样。兰色想了想,基本的概念咱们能够在后面逐步介绍,我想大众此刻最想做的,便是第1讲中,按照数据表自已亲手创建一个数据透视表

做数据透视表之前,咱们需要认识以下两个问题:

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

数据透视表的用途倘若细数会有非常多,但最基本、最常用的是“归类汇总”,因此咱们需要对一个流水账式的明细表进行归类汇总时,就需要运用数据透视表。数据透视表的源数据即是“明细表”。如第1讲中的销售明细表。

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

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

1 空字段名包含空列,没法制作数据透视表

2 相同的字段名,会自动添加序号,以示区别 

3 字段所在行有合并单元格,等同于空字段,没法创建数据透视表 

4 倘若有空行,会当成空值处理

倘若数据源表不规范,需要处理一下才可制作数据透视表,数据表的整理咱们将在第3讲介绍。

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

原材料营销明细表(微X右下角阅读原文 下载示例表格)

制作过程

第1步 选择营销明细表,excel2003版 : 数据菜单 - 数据透视表(Excel2010版:插进选项卡 - 数据透视表 )

excel2003版

excel2010版

第2步 执行“数据透视表”命令后,会进入数据透视表创建向导。(excel2003和excel2010稍有差异,但大体选项是同样的)在向导的第1通常是默认选项,不需要设置,直接点确定就可(excel2003版在第1步点“完成”按钮)。

第3步进行前两步后,会自动新建一个工作表,且在工作表中会有一个数据透视表空白区域,就象盖楼房地基已打好,需要咱们安排详细的房间位置。

添加数据透视表项目

把地区拖动到行标签

营销量和营销金额拖动到数值区域

重视:excel2003版和excel2010添加方式区别,是选择字段,直接拖到数据透视表中的相对位置。如下图所示。

拖动完成后,一个数据透视表的雏形已展此刻我们的面前,如下图所示。

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

第3讲:源表的整理

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

存在空列或行标题

影响:明细表倘若有标题为空,将没法制作数据透视表。

整理: 把标题弥补完整。

存在相同的标题

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

整理尽可能不要存在相同的列标题。

3 存在合并单元格

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

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

4 存在违法日期。

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

整理:转换成excel认可的日期格式。办法选择列 - 分列 - 第3步选日期

5 存在文本型数字

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

整理:转换成数值型数字。办法:选择列 - 分列 - 完成

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

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

今天咱们认识数据透视表的基本结构。

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

字段设置区域。把要表示的字段拖放在相应区域内。相当于数据透视表的加工厂。加工的方式把相应的字段拖入。

数据透视表的表示区域。组合成的数据透视表会在这儿表示

报表筛选区域(excel2003版叫作为页字段)。如上图所示,能够按“月”筛选数据透视表要表示的内容。

行标签(2003版叫作为行字段)。该区域的字段会上下摆列表示,如上图中“营销地区”和“营销员”字段。

列标签(2003版叫作为列字段)。该区域的字段会上下摆列表示。如下图中的“地区”。

数值。要统计的数据必定要放在该栏内才会进行求和等统计。如上图中的“销量”。

字段拖放在区别的区域,就会以区别表示方式表示汇总的结果。况且同一个区域内的次序区别,在数据透视表内汇总的先后层次区别朋友能够拖动转换一下各个区域的字段内容,同期观察数据透视表的变化。

在拖放时会遇到非常多新的问题,明天的微X咱们将介绍字段的设置

第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 选择某行、某列

当你把光标放在数据透视表的某行或某列前面时,你会发掘光标变成为了黑箭头此时你只需要单击就能够选择所有和本行列相关的行或列了。

兰色说文案有点长,能看到最后是学习特别有耐心的朋友,为自已点个大拇指吧。

长按下面二维码照片,点上面”识别图中二维码而后再点关注,每日能够收到一篇兰色最新写的excel教程

回复

使用道具 举报

2991

主题

2万

回帖

9913万

积分

论坛元老

Rank: 8Rank: 8

积分
99139312
发表于 2024-10-14 20:23:47 | 显示全部楼层
大势所趋,用于讽刺一些制作目的就是为了跟风玩梗,博取眼球的作品。
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

站点统计|Archiver|手机版|小黑屋|外链论坛 ( 非经营性网站 )|网站地图

GMT+8, 2024-11-5 14:58 , Processed in 0.078525 second(s), 19 queries .

Powered by Discuz! X3.4

Copyright © 2001-2023, Tencent Cloud.