乔山办公网我们一直在努力
您的位置:乔山办公网 > excel表格制作 > 规范Excel数据透视表的数据源,看这一篇就够了!-excel数据透视表

规范Excel数据透视表的数据源,看这一篇就够了!-excel数据透视表

作者:乔山办公网日期:

返回目录:excel表格制作

规范Excel<a href='https://www.qiaoshan022.cn/tags/shujutoushibiao_600_1.html' target='_blank'>数据透视表</a>的数据源,看这一篇就够了!

规范Excel数据透视表的数据源,看这一篇就够了!

数据透视表的威力虽然无比强大,但使用前提是数据源要规范,否则会给后期创建和使用数据透视表带来层层阻碍,甚至无法创建数据透视表。

很多新人由于不懂得如何规范数据源,而对阻挡在数据透视表的大门外,此文帮助大家了解规范数据源的几点要求,以及如何修正不规范的数据源!

1 不能包含多层表头,或记录中多次插入标题行

2 数据记录中不能带空行(正确姿势)

3 原始记录不能和计算行混杂

4 数据源中的文本型数字要转换为数值

5 数据源中不能包含重复记录

6 不要输入不规范日期

7 不要包含合并单元格

8 数值和单位不能放在一个单元格

9 列字段不要重复,名称要唯一

10 能放在一个工作表里的数据不要分散放到多个工作表中

下面把这些规则结合案例展开,便于大家记忆和理解。

1 不能包含多层表头,或记录中多次插入标题行

很多单位喜欢用多层表头,比如工资表,表格的第一行和第二行都是表头信息,这类报表在创建数据透视表之前需要将双层表头合并为一行。

另外还有一种情况是,数据行之间穿插多个标题行......

看看下面这个gif演示,为了让报表查看过程中能随时看到标题行,并且打印时可以每页带上标题行,我见过很多人这么干......

规范Excel数据透视表的数据源,看这一篇就够了!

其实要想随时查看到顶端标题行,冻结窗格即可,点击如下gif动态演示效果

规范Excel数据透视表的数据源,看这一篇就够了!

2 数据记录中不能带空行

下面这个案例,连续的销售记录被空行分隔开。

规范Excel数据透视表的数据源,看这一篇就够了!

这样的报表无法直接利用Excel的分类汇总功能和数据透视表汇总!

给出批量删除多余空行的办法,我的操作演示效果(下图gif动态演示

规范Excel数据透视表的数据源,看这一篇就够了!

3 原始记录不能和计算行混杂

工作中的原始记录和计算行混杂,也是很多人常犯错误之一

规范Excel数据透视表的数据源,看这一篇就够了!

这样导致报表无法批量利用数据透视表汇总,而且当数据源更新时,工作强度很大,还极易出错!

这种情况下,最快捷的办法就是选中全表筛选A列小计,然后批量删除。

4 数据源中的文本型数字要转换为数值

工作中很多系统导出的数据都是文本型数字,这样的数据源会导致数据透视表默认进行计数统计,而不是求和统计,后期处理很麻烦。

其实只要掌握一定技巧,可以快捷的修复这类数据源

下面这个操作就是利用选择性粘贴&lt;Ctrl+Alt+V&gt;将文本数字批量转换为真正的数值。

点击下图gif可查看动态演示过程

规范Excel数据透视表的数据源,看这一篇就够了!

5 数据源中不能包含重复记录

当数据源中包含很多重复值时,我们需要先批量删除重复项,再进行数据透视。

当判定重复的条件不止一个时,手动删除起来非常麻烦,用如下方法,可以几秒内完成多个条件的重复项判定并批量删除重复数据,一劳永逸!

点击下图gif可查看动态演示过程

规范Excel数据透视表的数据源,看这一篇就够了!

6 不要输入不规范日期

不规范的日期数据给工作带来很多困扰,比如无法正确排序,无法提取正确的年月日信息等......

利用下面的技巧,可以帮你批量修复不规范的日期格式

规范Excel数据透视表的数据源,看这一篇就够了!

7 不要包含合并单元格

工作中带合并单元格的报表随处可见,如下:

规范Excel数据透视表的数据源,看这一篇就够了!

这样不但使数据难以直接用数据透视表汇总,连函数计算都受限!

下面给出智能填充合并单元格的正确姿势:

我的操作演示效果(下图gif动态演示)

规范Excel数据透视表的数据源,看这一篇就够了!

8 数值和单位不能放在一个单元格

下图所示C列,既包括数值又包括单位,导致Excel无法直接求和

规范Excel数据透视表的数据源,看这一篇就够了!

处理这个问题的正确姿势,用函数提取对应的信息,分列单独放置

D2=--LEFT(C2,2*LEN(C2)-LENB(C2))

E2=SUBSTITUTE(C2,D2,)

规范Excel数据透视表的数据源,看这一篇就够了!

9列字段不要重复,名称要唯一

这个不用截图表述了,直接文字即可讲明白。

当表格中多列数据使用同一个名称时,会造成数据透视表的字段混淆,后期无法分辨数据属性,所以各列字段名称要保持唯一,不要重复。

10 能放在一个工作表里的数据不要分散放到多个工作表中

如下图,本来连续的同类数据被分散在多个工作表,每个日期单放一个工作表

规范Excel数据透视表的数据源,看这一篇就够了!

这样到了月底,本来很简单的问题月汇总也变得非常麻烦,甚至需要动用vba代码。

其实这些数据应该放置在一个工作表中,直接数据透视即可瞬间搞定!

如果你用的是Excel 2016版,可以利用下面的方法多表合并,很简单,不出一分钟就OK!

规范Excel数据透视表的数据源,看这一篇就够了!

利用追加查询,合并多表数据

规范Excel数据透视表的数据源,看这一篇就够了!

最后关闭并上载

规范Excel数据透视表的数据源,看这一篇就够了!

这样,位于不同工作表中的数据,瞬间已经合并完成啦~

好啦,这些内容仅仅是数据透视表系列教程的第二篇,讲解了规范的数据源的要求,以及如何修正不规范的数据源,让大家能顺利的创建数据透视表!此系列持续更新中,明天见~

如果你觉得有用,就分享给朋友们看看吧~

干货每天都有,如果你想每天第一时间收到,

如下操作,两秒搞定:

新朋友:点页面右上角三个点→查看公众号→关注

老朋友:点页面右上角三个点→查看公众号→置顶公众号

更多的Excel应用技巧,可以点击文尾的“阅读原文”,获取更多教程。

今天就到这里,大家记得收藏此教程,明天见~

规范Excel数据透视表的数据源,看这一篇就够了!

李 锐

微软全球最有价值专家MVP

新浪微博Excel垂直领域第一签约自媒体

百度名家,百度阅读认证作者

每日分享职场办公技巧原创教程

高效工作,快乐生活!

微博 @Excel_函数与公式

微信公众号(ExcelLiRui)

长按识别二维码,关注并置顶公众号

每天都可以收到最新的Excel干货教程

▼点击“阅读原文”,获取更多教程。

相关阅读

  • 快速入门Excel透视表-excel数据透视表

  • 乔山办公网excel表格制作
  • excel数据透视表,能够快速地分析数据,帮助你制定更佳的业务决策。但很多时候不知道从何处下手,尤其是有大量数据时。表格是很棒的数据透视表数据源,因为刷新数据时,添加到表
关键词不能为空
极力推荐

ppt怎么做_excel表格制作_office365_word文档_365办公网